?
SEMer必備常見的 Excel 函數有哪些? 2018-06-11 09:41:58 點擊瀏覽:513次 來源:快乐8 www.hzsrs.com

世界上的數據分析師分為兩類,使用 Excel 的分析師,和其他分析師。


每一個推廣新人/數據新人的入門工具都離不開 Excel。因為 Excel 涵蓋的功能足夠多。


很多傳統行業的數據分析師只要求掌握 Excel 即可,會 SPSS/SAS 是加分項。即使在“ 挖掘滿街走,Python 不如狗 ”的互聯網數據分析界,Excel 也是不可替代的。


Excel 有很多強大的函數,本篇主要分享的各種函數的用途。實戰會后續文章講解。


函數可以被我們想象成一個盒子,專門負責將輸入轉換成輸出,不同的函數對應不同的輸出。


=Vlookup( lookup_value ,table_array,col_index_num,[range_lookup] )


上文的 Vlookup 就是一個經典函數。函數中包含參數,括號里的部分都是參數。我們可以把參數想象成盒子上的開關。


vlookup 就有四個開關,不同開關組合決定了函數的輸入和輸出。


=Vlookup( 參數 1,參數 2,參數 3,參數 4)


復雜的原理不需要了解,這篇文章是常用函數匯總。甚至你不需要特別記憶怎么使用函數,應用 Excel 函數最重要的能力是學會搜索。因為絕大部分函數網上已經有相應的解釋,圖文結合,非常詳盡。


學會將遇到的問題轉換成搜索語句,在我還是新人時并不會 vlookup,我遇到的第一個問題就是關聯多張表的數據,我在網上搜索:excel 怎么匹配多張表的數據。


于是就學會了,這里推薦使用百度,因為前三行的結果基本是百度經驗,對新人學習很友好。(后續圖片均引用自百度經驗)


在理解函數的基礎上,我會適當引入高層次的內容,SQL 和 Python(內建函數)。將其和 Excel 結合學習,如果大家吃透了 Excel 的函數,那么后續學習會輕松不少。


一、清洗處理類


主要是文本、格式以及臟數據的清洗和轉換。很多數據并不是直接拿來就能用的,需要經過數據分析人員的清理。數據越多,這個步驟花費的時間越長。


001 | Trim


清除掉字符串兩邊的空格。


MySQL 有同名函數,Python 有近似函數 strip。




002 | Concatenate


=Concatenate(單元格 1,單元格 2……)


合并單元格中的內容,還有另一種合并方式是& 。"我"&"很"&"帥" = 我很帥。當需要合并的內容過多時,concatenate 的效率快也優雅。


MySQL 有近似函數 concat。




003 | Replace


=Replace(指定字符串,哪個位置開始替換,替換幾個字符,替換成什么)


替換掉單元格的字符串,清洗使用較多。


MySQL 中有同名函數,Python 中有同名函數。




004 | Substitute


和 replace 接近,區別是替換為全局替換,沒有起始位置的概念。




005 | Left/Right/Mid


=Mid(指定字符串,開始位置,截取長度)


截取字符串中的字符。Left/Right(指定字符串,截取長度)。left 為從左,right 為從右,mid 如上文示意。


MySQL 中有同名函數。




006 | Len/Lenb


返回字符串的長度,在 len 中,中文計算為一個,在 lenb 中,中文計算為兩個。


MySQL 中有同名函數,Python 中有同名函數。




007 | Find


=Find(要查找字符,指定字符串,第幾個字符)


查找某字符串出現的位置,可以指定為第幾次出現,與 Left/Right/Mid 結合能完成簡單的文本提取


MySQL 中有近似函數 find_in_set,Python 中有同名函數。




008 | Search


和 Find 類似,區別是 Search 大小寫不敏感,但支持*通配符。




009 | Text


將數值轉化為指定的文本格式,可以和時間序列函數一起看。


二、關聯匹配類


在進行多表關聯或者行列比對時用到的函數,越復雜的表用得越多。多說一句,良好的表習慣可以減少這類函數的使用。


010 | Lookup


=Lookup(查找的值,值所在的位置,返回相應位置的值)


最被忽略的函數,功能性和 Vlookup 一樣,但是引申有數組匹配和二分法。




011 | Vlookup


=Vlookup(查找的值,哪里找,找哪個位置的值,是否精準匹配)


Excel 第一大難關,因為涉及的邏輯對新手較復雜,通俗的理解是查找到某個值然后黏貼過來。




012 | Index


=Index(查找的區域,區域內第幾行,區域內第幾列)


和 Match 組合,媲美 Vlookup,但是功能更強大。




013 | Match


=Match(查找指定的值,查找所在區域,查找方式的參數)


和 Lookup 類似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。




014 | Row


返回單元格所在的行。




015 | Column


返回單元格所在的列。




016 | Offset


=Offset(指定點,偏移多少行,偏移多少列,返回多少行,返回多少列)


建立坐標系,以坐標系為原點,返回距離原點的值或者區域。正數代表向下或向左,負數則相反。


三、邏輯運算類


數據分析中不得不用到邏輯運算,邏輯運算返回的均是布爾類型,True 和 False。很多復雜的數據分析會牽扯到較多的邏輯運算


017 | IF


經典的如果但是,在后期的 Python 中,也會經常用到,當然會有許多更優雅的寫法。也有 ifs 用法,取代if(and())的寫法。


MySQL 中有同名函數,Python 中有同名函數。




018 | And


全部參數為 True,則返回 True,經常用于多條件判斷。


MySQL 中有同名函數,Python 中有同名函數。




019 | Or


只要參數有一個 True,則返回 Ture,經常用于多條件判斷。


MySQL 中有同名函數,Python 中有同名函數。




020 | IS 系列


常用判斷檢驗,返回的都是布爾數值 True 和 False。常用 ISERR,ISERROR,ISNA,ISTEXT,可以和 IF 嵌套使用。


四、計算統計類


常用的基礎計算、分析、統計函數,以描述性統計為準。具體含義在后續的統計章節再展開。


021 | Sum/Sumif/Sumifs


統計滿足條件的單元格總和,SQL 有中同名函數。


MySQL 中有同名函數,Python 中有同名函數。




022 | Sumproduct


統計總和相關,如果有兩列數據銷量和單價,現在要求賣出增加,用 sumproduct 是最方便的。


MySQL 中有同名函數。




023 | Count/Countif/Countifs


統計滿足條件的字符串個數


MySQL 中有同名函數,Python 中有同名函數。




024 | Max


返回數組或引用區域的最大值。


MySQL 中有同名函數,Python 中有同名函數。




025 | Min


返回數組或引用區域的最小值


MySQL 中有同名函數,Python 中有同名函數。




026 | Rank


排序,返回指定值在引用區域的排名,重復值同一排名。


SQL 中有近似函數 row_number() 。




027 | Rand/Randbetween


常用隨機抽樣,前者返回 0~1 之間的隨機值,后者可以指定范圍。


MySQL 中有同名函數。




028 | Averagea


求平均值,也有 Averageaif,Averageaifs


MySQL 中有同名函數,python 有近似函數 mean。




029 | Quartile


=Quartile(指定區域,分位參數)


計算四分位數,比如 1~100 的數字中,25 分位就是按從小到大排列,在 25%位置的數字,即 25。參數 0 代表最


小值,參數 4 代表最大值,1~3 對應 25、50(中位數)、75 分位。




030 | Stdev


求標準差,統計型函數,后續數據分析再講到。




031 | Substotal


=Substotal(引用區域,參數)


匯總型函數,將平均值、計數、最大最小、相乘、標準差、求和、方差等參數化,換言之,只要會了這個函數,上面的都可以拋棄掉了。




032 | Int/Round


取整函數,int 向下取整,round 按小數位取數。


round(3.1415,2) =3.14 ;


round(3.1415,1)=3.1


五、時間序列類


專門用于處理時間格式以及轉換,時間序列在金融、財務等數據分析中占有較大比重。時機序列的處理函數比


我列舉了還要復雜,比如時區、分片、復雜計算等。這里只做一個簡單概述。



033 | Year


返回日期中的年。


MySQL 中有同名函數。




034 | Month


返回日期中的月。


MySQL 中有同名函數。




035 | Weekday


=Weekday(指定時間,參數)


返回指定時間為一周中的第幾天,參數為 1 代表從星期日開始算作第一天,參數為 2 代表從星期一開始算作第一天(中西方差異)。我們中國用 2 為參數即可。


MySQL 中有同名函數。




036 | Weeknum


=Weeknum(指定時間,參數)


返回一年中的第幾個星期,后面的參數類同 weekday,意思是從周日算還是周一。


MySQL 中有近似函數 week。




037 | Day


返回日期中的日(第幾號)


MySQL 中有同名函數。




038 | Date


=Date(年,月,日)


時間轉換函數,等于將 year(),month(),day()合并


MySQL 中有近似函數 date_format。




039 | Now


返回當前時間戳,動態函數。


MySQL 中有同名函數。




040 | Today


返回今天的日期,動態函數


MySQL 中有同名函數。




041 | Datedif


=Datedif(開始日期,結束日期,參數)


日期計算函數,計算兩日期的差。參數決定返回的是年還是月等。


MySQL 中有近似函數 DateDiff。


(來源:艾奇學院)

上一篇:線下發展遭遇瓶頸,教育培訓突破口在哪?

下一篇:怎么利用新聞源讓關鍵詞有好的排名

?

在線
客服

在線客服服務時間:9:00-20:00 服務熱線:15937388775

選擇下列產品馬上在線溝通:

在線
咨詢

15937388775
7*24小時在線答疑

關注
微信

添加微信咨詢

在線
咨詢

頂部
免費咨詢熱線 立即與易企正項目顧問通話 提供您的電話號碼,易企正項目顧問將致電聯系您。等待時間:5分鐘以內 信息?;ぶ星敕判奶钚?/span>
{ganrao}