資料分析必備的43个Excel函式,史上最全!

此文是《10周入門數據分析》系列的第4篇
想了解學習路線,可以先閱讀「10周計劃」

Excel是我們工作中經常使用的一種工具,對於資料分析來說,這也是處理資料最基礎的工具。很多傳統行業的資料分析師甚至只要掌握Excel和SQL即可。

對於初學者,有的時候並不需要急於苦學R語言等專業工具(當然會也是加分項),因為Excel涵蓋的功能足夠多,也有很多統計、分析、視覺化的插件。只不過我們平時處理資料的時候很多函式都不知道怎麼用。
關於Excel的進階學習,主要分為兩塊:一個是資料分析常用的Excel函數,另一個分享用Excel做一個簡單完整的分析。

這篇文章主要介紹資料分析常用的43個Excel函式及用途,實戰分析將在下一篇講解。

關於函式:
Excel的函式實際上就是一些複雜的計算公式,函式把複雜的計算步驟交由程序處理,只要按照函式格式錄入相關參數,就可以得出結果。如求一個區域的和,可以直接用SUM(A1:C100)的形式。
所以對於函式,不用刻意記刻意背,只要知道比如「選取欄位,用Left/Right/Mid」函式,並且需要哪些參數怎麼用就行了,複雜的就交給萬能的google吧。

函式分類:

關聯匹配類
清理處理類
邏輯運算類
計算統計類
時間序列類

一、關聯匹配類

經常性的,需要的資料不在同一個excel表或同一個excel表不同sheet中,資料太多,copy麻煩也不準確,如何整合呢?這類函式就是用於多表關聯或者列欄比對時的場景,而且表越複雜,用得越多。
函式HLOOKUP和VLOOKUP都是用來在表格中查找資料。
1、VLOOKUP
功能:用於查找首列滿足條件的元素。
語法:=VLOOKUP(要查找的值,要在其中查找值的區域,區域中包含返回值的列號,精確匹配或近似匹配 — 指定為 0/FALSE 或 1/TRUE)。

2、HLOOKUP
功能:搜索表的頂行或值的陣列中的值,並在表格或陣列中指定的欄的同一lan中返回一個值。
語法:=VLOOKUP(要查找的值,要在其中查找值的區域,區域中包含返回值的行號,精確匹配或近似匹配 — 指定為 0/FALSE 或 1/TRUE)。
區別:HLOOKUP返回的值與需要查找的值在同一列上,而VLOOKUP返回的值與需要查找的值在同一行上。

3、INDEX
功能:返回表格或區域中的值或引用該值。
語法:= INDEX(要返回值的儲存格區域或陣列,所在列,所在欄)

4、MATCH
功能:用於返回指定內容在指定區域(某列或者某欄)的位置。
語法:= MATCH (要返回值的儲存格區域或陣列,查找的區域,查找方式)

5、RANK
功能:求某一個數值在某一區域內一組數值中的排名。
語法:=RANK(參與排名的數值, 排名的數值區域, 排名方式-0是降序-1是升序-默認為0)。

6、Row
功能:返回儲存格所在的列

7、Column
功能:返回儲存格所在的欄

8、Offset
功能:從指定的基準位置按列欄偏移量返回指定的引用
語法:=Offset(指定點,偏移多少列,偏移多少欄,返回多少列,返回多少欄)

二、清理處理類

資料處理之前,需要對提取的資料進行初步清理,如清除字串空格,合并儲存格、替換、截取字串、查找字串出現的位置等。
清除字串空格:使用Trim/Ltrim/Rtrim
合并儲存格:使用concatenate
截取字串:使用Left/Right/Mid
替換儲存格中內容:Replace/Substitute
查找文本在儲存格中的位置:Find/Search

9、Trim
功能:清除掉字串兩邊的空格

10、Ltrim
功能:清除儲存格右邊的空格

11、Rtrim
功能:清除儲存格左邊的空格

12、Concatenate
語法:=Concatenate(儲存格1,儲存格2……)
合并儲存格中的內容,還有另一種合并方式是&,需要合并的內容過多時,concatenate效率更快。

13、Left
功能:從左截取字串
語法:=Left(值所在儲存格,截取長度)

14、Right
功能:從右截取字串
語法:= Right (值所在儲存格,截取長度)

15、Mid
功能:從中間截取字串
語法:= Mid(指定字串,開始位置,截取長度)

16、Replace
功能:替換掉儲存格的字串
語法:=Replace(指定字串,哪個位置開始替換,替換幾個字元,替換成什麼)

17、Substitute
和replace接近,不同在於Replace根據位置實現替換,需要提供從第幾位開始替換,替換幾位,替換後的新的文本;而Substitute根據文本內容替換,需要提供替換的舊文本和新文本,以及替換第幾個舊文本等。因此Replace實現固定位置的文本替換,Substitute實現固定文本替換。

18、Find
功能:查找文本位置
語法:=Find(要查找字元,指定字串,第幾個字元)

19、Search
功能:返回一個指定字元或文本字串在字串中第一次出現的位置 ,從左到右查找
語法:=search(要查找的字元,字元所在的文本,從第幾個字元開始查找)
Find和Search這兩個函式功能幾乎相同,實現查找字元所在的位置,區別在於Find函式精確查找,區分大小寫;Search函式模糊查找,不區分大小寫。

20、Len
功能:文本字串的字元個數

21、Lenb
功能:返迴文本中所包含的字元數

三、邏輯運算類

22、IF
功能:使用邏輯函式 IF 函式時,如果條件為真,該函式將返回一個值;如果條件為假,函式將返回另一個值。
語法:=IF(條件, true時返回值, false返回值)

23、AND
功能:邏輯判斷,相當於「並」。
語法:全部參數為True,則返回True,經常用於多條件判斷。

24、OR
功能:邏輯判斷,相當於「或」。
語法:只要參數有一個True,則返回Ture,經常用於多條件判斷。

四、計算統計類

在利用excel表格統計資料時,常常需要使用各種excel自帶的公式,也是最常使用的一類。重要性不言而喻。不過excel都自帶快捷功能。
MIN函式:找到某區域中的最小值
MAX函式:找到某區域中的最大值
AVERAGE函式:計算某區域中的平均值
COUNT函式: 計算某區域中包含數字的儲存格的數目
COUNTIF函式:計算某個區域中滿足給定條件的儲存格數目
COUNTIFS函式:統計一組給定條件所指定的儲存格數
SUM函式:計算單元格區域中所有數值的和
SUMIF函式:對滿足條件的儲存格求和
SUMIFS函式:對一組滿足條件指定的儲存格求和
SUMPRODUCT函式:返回相應的陣列或區域乘積的和

25、MIN
功能:找到某區域中的最小值

26、MAX函式
功能:找到某區域中的最大值

27、AVERAGE
功能:計算某區域中的平均值

28、COUNT
功能:計算含有數字的儲存格的個數。

29、COUNTIF
功能:計算某個區域中滿足給定條件的儲存格數目
語法:=COUNTIF(儲存格1: 儲存格2 ,條件)
比如=COUNTIF(Table1!A1:Table1!C100, 「YES」 ) 計算Table1中A1到C100區域儲存格中值為」YES」的儲存格個數

30、COUNTIFS
功能:統計一組給定條件所指定的儲存格數
語法:=COUNTIFS(第一個條件區域,第一個對應的條件,第二個條件區域,第二個對應的條件,第N個條件區域,第N個對應的條件)
比如:=COUNTIFS(Table1!A1: Table1!A100, 「YES」,Table1!C1: Table1!C100, 「NO」 ) 計算Table1中A1到A100區域儲存格中值為」YES」,而且同時C區域值為」NO」的儲存格個數

31、SUM
計算儲存格區域中所有數值的和

32、SUMIF
功能:求滿足條件的儲存格和
語法:=SUMIF(儲存格1: 儲存格2 ,條件,儲存格3: 儲存格4)

32、SUMIFS
功能:對一組滿足條件指定的儲存格求和
語法:=SUMIFS(實際求和區域,第一個條件區域,第一個對應的求和條件,第二個條件區域,第二個對應的求和條件,第N個條件區域,第N個對應的求和條件)
比如=SUMIFS(Table1!C1:Table1!C100,Table1!A1: Table1!A100, 「YES」 ,Table1!B1:Table1B100, 「NO」 ) 計算Table1中C1到C100區域,同時相應行A列值為」YES」,而且對應B列值為」NO」的儲存格的和。

33、SUMPRODUCT
功能:返回相應的陣列或區域乘積的和
語法: =SUMPRODUCT(儲存格1: 儲存格2 ,儲存格3: 儲存格4)
比如:=SUMPRODUCT(Table1!A1:Table1!A100, Table2!B1Table2!B100) 計算表格1的A1到A100與表格2的B1到B100的乘積和,即A1*B1+A2*B2+A3*B3+…

34、Stdev
統計型函式,求標準差。

35、Substotal
語法:=Substotal(引用區域,參數)
匯總型函式,將平均值、計數、最大最小、相乘、標準差、求和、方差等參數化,換言之,只要會了這個函式,上面的都可以拋棄掉了。

36、Int/Round
取整函式,int向下取整,round按小數位取數。
round(3.1415,2)=3.14 ;
round(3.1415,1)=3.1

五、時間序列類

專門用於處理時間格式以及轉換。
37、TODAY
返回今天的日期,動態函式。

38、NOW
返回當前的時間,動態函式。

39、YEAR
功能:返回日期的年份。

40、MONTH
功能:返回日期的月份。

41、DAY
功能:返回以序列數表示的某日期的天數。

42、WEEKDAY
功能:返回對應於某個日期的一周中的第幾天。 默認情況下,天數是 1(星期日)到 7(星期六)範圍內的整數。
語法:=Weekday(指定時間,參數)

43、Datedif
功能:計算兩個日期之間相隔的天數、月數或年數。
語法:=Datedif(開始日期,結束日期,參數)

往期內容:
學習計劃 | 10周入門資料分析
4招教你煉就資料分析的思維
數據分析慣用的5種思維方法

可以戳下「了解更多」關注小編主頁。

想瞭解更多的資料分析知識,請關注我的Facebook, 期待你與我互動起來啦~

數據分析不是個事

分享數據人必備的知識!

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store