[[404616]]

01寫在前面

為什么要寫【數據分析工具】這個系列文章?還是回到一個最根本的問題上：數據分析到底是干什么的?

數據分析雖然是以業(yè)務為主，工具為輔，業(yè)務為目的，工具為手段，但巧婦難為無米之炊，只有掌握了基礎的工具才能夠進行數據分析。但工具千千萬，Excel、SQL、Python/R、SPSS、SAS、PowerBI/Tableau，到底哪些才是數據分析師日常工作中必備的工具?哪些是沒有必要花費時間精力的花架子?數據分析工具這個系列，我們就一起來看一下哪些是數據分析師真正需要熟練掌握的工具!

Excel是目前最基礎、也是應用最廣的數據分析工具，百萬行級別以下最好用的數據分析工具，沒有之一，不接受反駁!熟練使用Excel，能夠解決數據分析中80%的問題，而且非常靈活、快速和高效，所以Excel作為數據分析師必備的基本技能，重要性不言而喻。

很多想入行數據分析的小伙伴學習Excel沒有經過系統的指引與訓練，而往往是來自于各種東拼西湊的知識，感覺自己很懂Excel，所以簡歷上就非常自信地寫上了“精通Excel”這樣不知天高地厚的字眼，面試一問起來才發(fā)現自己的純真和無知。是的，當你還覺得Excel很low很簡單的時候，說明你還沒有被它震撼和教育過，越是精通Excel的大神越會覺得它的強大和自己的無知。

那么想入行數據分析的同學來說，怎么快速高效地掌握Excel這個數據分析的利器呢?根據筆者多年來的工作經驗，作為數據分析師，提升Excel水平可以按照這樣的學習路徑：

1)基本操作包括數據的簡單處理匯總、圖表制作等，屬于Excel基礎知識，一般大家都能正常使用。

2)熟練掌握常用的函數后，你就可以做簡單的數據統計、分析和數據可視化等工作了。

3)為了進一步從不同維度對關心的指標進行上卷、下鉆分析，還需要非常熟練地掌握數據透視表，這也是Excel最為強大、使用最為頻繁的功能。

4)為了實現復雜的業(yè)務分析，解決不同數據源、海量數據的分析問題，我們就需要掌握PowerQuery和PowerPivot，PowerQuery負責整合多重來源數據，并進行數據轉換，PowerPivot對整合后的規(guī)范化數據進行高效率的透視分析，幾百萬上千萬行數據均不在話下。

5)當然，這個時候可能還不夠，因為平時還有很多任務是需要每天手動處理的，所以PowerBI橫空出世了，PowerBI融合了PowerQuery和PowerPivot的功能，你可以利用PowerBI制作好你想要監(jiān)控的核心報表，以后只要更新數據源，報表就能自動化生成，再也不用每天重復地復制粘貼做表格了!

02Excel常用函數

Excel之所以強大，很大一方面是因為Excel中有大量的函數可以實現各種各樣的功能，精力有限，作為數據分析師，我們不需要也沒有必要學習所有的函數，只要重點學習數據分析中常用的一些函數即可，我已經按照分類列舉如下，并通過實際案例，講解具體的使用方法。熟練掌握這些函數，80%以上的數據分析問題都可以輕松解決。

03關聯匹配類函數

關聯匹配-VLOOKUP

1)功能

在Excel中我們經常會有這樣的場景，在數據A中記錄了各個員工的績效等級，但是并沒有記錄對應的年終獎，而在數據B中只記錄了各個績效等級對應的年終獎，我們想在數據A中增加年終獎這一列信息，這個時候VLOOKUP就排上用場了。

VLOOKUP是Excel第一大難關，也是Excel中使用頻率較高，面試考查較為頻繁的一個知識點，因為涉及的邏輯對新手較復雜，所以也是用來驗證你簡歷上“熟練使用Excel”的重要依據。把它放在第一個來講，是對Vlookup最起碼的尊重。通俗的理解就是用某個值作為中間關聯，找到另外一個值然后黏貼過來。如下圖所示：

2)語法格式

=VLOOKUP(找誰?在哪找?返回的第幾列?你是想精準地找還是粗略地找?)，

這個函數有4個要素，對應如下：

①用誰找：一般是單個單元格的值。

②在哪找：一般是單元格區(qū)域。

③返回第幾列：返回列數從查找區(qū)域算起。

④匹配類型：精確匹配用，0或FALSE，近似匹配用1或TRUE。

3)舉例

如果上面還是不好理解，跟著下面這個視頻一步步操作，你就能明白它的原理了。

但是有一種異常情況，就是我們有時候可能會找不到匹配的值，比如魏延同學今年表現太差，領導決定給他個F績效，但是以前沒有出現過這樣的情況，也不知道F對應的年終獎是多少，所以就出現了#N/A的錯誤，如下圖所示：

這個時候我們用IFERROR判斷一下，如果報錯了就給個處理的方案，這樣#N/A的錯誤就能完美解決，公式如下：

關聯匹配-INDEX+MATCH

1)功能

在Excel中MATCH函數可以返回指定內容所在的位置，而INDEX又可以根據指定位置查詢到位置所對應的數據，兩者結合使用，可以返回指定位置相關聯的數據。而且INDEX+MATCH組合能夠實現反向查找和雙向查找，比VlOOKUP功能更強大更靈活。

2)語法格式

=INDEX(查找的區(qū)域，區(qū)域內第幾行，區(qū)域內第幾列)和Match組合

=MATCH(查找指定的值，查找所在區(qū)域，查找方式的參數)和VLOOKUP類似，但是可以按照指定方式查找，比如大于、小于或等于。返回值所在的位置。

3)舉例

1、反向查找

如下圖所示，要求查找員工姓名為“張飛”的員工編號。

分析:

先利用MATCH函數根據產品名稱在C列查找位置

=MATCH(B25,B17:B22,0)

再用INDEX函數根據查找到的位置從B列取值。完整的公式即為：

=INDEX(A17:A22,MATCH(B25,B17:B22,0))

2、雙向查找

如下圖所示，要求查找員工“張飛”在2018年的年終獎。

分析：

先用MATCH函數查找員工“張飛”在A列的位置

= MATCH(A43,$A$33:$A$39,0)

再用MATCH函數查找"2018年"在第一行中的位置

=MATCH(B43,$B$32:$F$32,0)

最后用INDEX根據行數和列數提取數值

=INDEX(B33:F39,MATCH(A43,$A$33:$A$39,0),MATCH(B43,$B$32:$F$32,0))

04清洗處理類函數

很多數據并不是直接拿來就能用的，在進行數據分析之前，我們需要對數據進行清洗和處理，主要包括數據格式轉換、數據組合、截取等。雖然這個步驟耗費較多的時間精力，但如果能熟練使用一些函數，也能讓我們事半功倍。

轉化為文本-TEXT

功能

將數值/文本/日期轉化為自己想要的文本格式。

語法格式

=TEXT(value,format_text)Value為原始數據。Format_text為希望轉換成的文本格式。

舉例

例如把C列的2020/1/1的日期格式轉換成2020-01-01的文本格式。

合并單元格-CONCAT

功能

將多個單元格的內容進行合并。

語法格式

=CONCAT(單元格1，單元格2……)合并單元格中的內容，還有另一種合并方式是& 。"我"&"很"&"帥" = 我很帥。當需要合并的內容過多時，CONCAT的效率快也優(yōu)雅。

舉例

例如對A、B、D3列的內容進行合并。

替換字符-SUBSTITUTE

功能

對字符串中指定的內容文本進行替換。

語法格式

=SUBSTITUTE(需要替換的文本，舊文本，新文本，第N個舊文本)。

舉例

例如將F列中的"A00"替換成“A-”。

截取字符串-LEFT/RIGHT/MID

功能

從不同的位置對字符串進行截取。

語法格式

=MID(指定字符串，開始位置，截取長度)。從字符串的指定位置，截取指定長度的字符。LEFT/RIGHT(指定字符串，截取長度)。LEFT為從最左端開始截取指定長度，RIGHT為從最右端開始截取指定長度。

舉例

例如分別截取F列中的左4、右3、中間2個字符。

定位文本位置-FIND

功能

對要查找的文本進行定位，以確定其位置。

語法格式

=Find(要查找的字符串，指定字符串，第幾次出現)查找指定的字符串在要查找的字符串中出現的位置，可以指定為第幾次出現，與Left/Right/Mid結合能完成簡單的文本提取。

舉例

例如查找A列中字符“A”出現的位置。

05時間日期類函數

專門用于處理時間格式以及轉換，時間序列在金融、財務等數據分析中占有較大比重。時機序列的處理函數比下面列舉的還要復雜，比如時區(qū)、分片、復雜計算等。這里只做一個簡單概述。

獲取年月日-YEAR/MONTH/DAY

功能

返回日期中的年/月/日。

語法格式

=YEAR(日期)/MONTH(日期)/DAY(日期)。

舉例

例如求B列中日期對應的年/月/日。

獲取第幾周-WEEKNUM

功能

返回對應日期是一年中的第幾個星期。

語法格式

=WEEKNUM(指定時間)。

舉例

例如求B列中日期對應是當年的第幾周。

獲取周幾-WEEKDAY

功能

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

語法格式

=WEEKDAY(指定時間，參數)。

舉例

例如求B列中日期對應是周幾。

06統計計算類函數

常用的基礎計算、分析、統計函數，以描述性統計為準。主要包括：基礎聚合函數SUM/COUNT/AVERAGE/MIN/MAX等、以及單條件匯總SUMIF/COUNTIF等，多條件匯總SUMIFS/COUNTIFS等、排名函數RANK、百分位函數PERCENTILE/QUARTILE、標準差STDEV等，具體用法下面展開。

基本聚合函數-SUM/COUNT/...

功能

對所選單元格進行SUM(求和)/COUNT(計數)/AVERAGE(平均值)/MIN(最小值)/MAX(最大值)。

語法格式

=SUM/COUNT/AVERAGE/MIN/MAX(range)。

舉例

例如求B列中日期對應的年/月/日。

單條件匯總-(SUMIF/COUNTIF/...)

功能

對區(qū)域中符合指定條件的數值求和/計數/求平均值/...。

語法格式

=SUMIF(range, criteria)，range為計算區(qū)域， criteria 以數字、表達式、單元格參考、文本或函數的形式來定義將選擇哪些單元格。例如，criteria可以表示為 32、“>32”、B5、“3?”等。

舉例

例如對符合條件的員工的年收入進行計算。

多條件匯總(SUMIFS/COUNTIFS/...)

功能

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

語法格式

除COUNTIFS外，所有xIFS函數都使用相同的語法。以下是SUMIFS和COUNTIFS的語法示例：

SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]，...)

COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]，...)。

舉例

例如對符合條件的員工的年收入進行計算。

排名函數RANK

功能

對一組數據進行排序，返回指定值在引用區(qū)域的排名，重復值同一排名。

語法格式

=RANK(value,range)。其中value是需要確定位次的數據，range表示數據范圍，返回的是排名。

舉例

例如對員工的年收入進行排序。

百分比函數-(QUARTILE/PRECENTILE)

功能

計算一組數據的四分位值/百分位值。

語法格式

=QUARTILE(range，n)計算四分位數，n=0~4，其中0代表最小值，4代表最大值，1~3分別對應1/4、1/2(中位數)、3/4分位數。

=PERCENTILE(range，k) 是返回數組的k百分點值的函數，功能與QUARTILE類似，區(qū)別是QUARTILE只能計算四分位值，而PERCENTILE可以計算任意百分位的值，功能比QUARTILE更靈活更強大。

舉例

例如計算員工年收入的1/4、1/2(中位數)、3/4分位數。

標準差STDEV與變異系數

功能

求一組數據的標準差，一般配合平均值使用，計算變異系數，其中變異系數=標準差/平均值，用于判斷一組數據的分散程度，變異系數越大，數據越分散，反之，數據越集中。

語法格式

=STDEV(range)。

其中變異系數=標注差/平均值=STDEV(range)/AVERAGE(range)。

舉例

例如計算各員工年收入的標準差和變異系數，看大家的年收入是否差異過大。

 

以上就是數據分析工具—Excel常用函數部分的內容，部分數據分析工具請翻看歷史文章，更多數據分析工具的文章持續(xù)更新中，敬請期待，如果覺得不錯，也歡迎分享、點贊和點在看哈~