[[404616]]

01寫(xiě)在前面

為什么要寫(xiě)【數(shù)據(jù)分析工具】這個(gè)系列文章?還是回到一個(gè)最根本的問(wèn)題上：數(shù)據(jù)分析到底是干什么的?

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

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

很多想入行數(shù)據(jù)分析的小伙伴學(xué)習(xí)Excel沒(méi)有經(jīng)過(guò)系統(tǒng)的指引與訓(xùn)練，而往往是來(lái)自于各種東拼西湊的知識(shí)，感覺(jué)自己很懂Excel，所以簡(jiǎn)歷上就非常自信地寫(xiě)上了“精通Excel”這樣不知天高地厚的字眼，面試一問(wèn)起來(lái)才發(fā)現(xiàn)自己的純真和無(wú)知。是的，當(dāng)你還覺(jué)得Excel很low很簡(jiǎn)單的時(shí)候，說(shuō)明你還沒(méi)有被它震撼和教育過(guò)，越是精通Excel的大神越會(huì)覺(jué)得它的強(qiáng)大和自己的無(wú)知。

那么想入行數(shù)據(jù)分析的同學(xué)來(lái)說(shuō)，怎么快速高效地掌握Excel這個(gè)數(shù)據(jù)分析的利器呢?根據(jù)筆者多年來(lái)的工作經(jīng)驗(yàn)，作為數(shù)據(jù)分析師，提升Excel水平可以按照這樣的學(xué)習(xí)路徑：

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

2)熟練掌握常用的函數(shù)后，你就可以做簡(jiǎn)單的數(shù)據(jù)統(tǒng)計(jì)、分析和數(shù)據(jù)可視化等工作了。

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

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

5)當(dāng)然，這個(gè)時(shí)候可能還不夠，因?yàn)槠綍r(shí)還有很多任務(wù)是需要每天手動(dòng)處理的，所以PowerBI橫空出世了，PowerBI融合了PowerQuery和PowerPivot的功能，你可以利用PowerBI制作好你想要監(jiān)控的核心報(bào)表，以后只要更新數(shù)據(jù)源，報(bào)表就能自動(dòng)化生成，再也不用每天重復(fù)地復(fù)制粘貼做表格了!

02Excel常用函數(shù)

Excel之所以強(qiáng)大，很大一方面是因?yàn)镋xcel中有大量的函數(shù)可以實(shí)現(xiàn)各種各樣的功能，精力有限，作為數(shù)據(jù)分析師，我們不需要也沒(méi)有必要學(xué)習(xí)所有的函數(shù)，只要重點(diǎn)學(xué)習(xí)數(shù)據(jù)分析中常用的一些函數(shù)即可，我已經(jīng)按照分類(lèi)列舉如下，并通過(guò)實(shí)際案例，講解具體的使用方法。熟練掌握這些函數(shù)，80%以上的數(shù)據(jù)分析問(wèn)題都可以輕松解決。

03關(guān)聯(lián)匹配類(lèi)函數(shù)

關(guān)聯(lián)匹配-VLOOKUP

1)功能

在Excel中我們經(jīng)常會(huì)有這樣的場(chǎng)景，在數(shù)據(jù)A中記錄了各個(gè)員工的績(jī)效等級(jí)，但是并沒(méi)有記錄對(duì)應(yīng)的年終獎(jiǎng)，而在數(shù)據(jù)B中只記錄了各個(gè)績(jī)效等級(jí)對(duì)應(yīng)的年終獎(jiǎng)，我們想在數(shù)據(jù)A中增加年終獎(jiǎng)這一列信息，這個(gè)時(shí)候VLOOKUP就排上用場(chǎng)了。

VLOOKUP是Excel第一大難關(guān)，也是Excel中使用頻率較高，面試考查較為頻繁的一個(gè)知識(shí)點(diǎn)，因?yàn)樯婕暗倪壿媽?duì)新手較復(fù)雜，所以也是用來(lái)驗(yàn)證你簡(jiǎn)歷上“熟練使用Excel”的重要依據(jù)。把它放在第一個(gè)來(lái)講，是對(duì)Vlookup最起碼的尊重。通俗的理解就是用某個(gè)值作為中間關(guān)聯(lián)，找到另外一個(gè)值然后黏貼過(guò)來(lái)。如下圖所示：

2)語(yǔ)法格式

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

這個(gè)函數(shù)有4個(gè)要素，對(duì)應(yīng)如下：

①用誰(shuí)找：一般是單個(gè)單元格的值。

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

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

④匹配類(lèi)型：精確匹配用，0或FALSE，近似匹配用1或TRUE。

3)舉例

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

但是有一種異常情況，就是我們有時(shí)候可能會(huì)找不到匹配的值，比如魏延同學(xué)今年表現(xiàn)太差，領(lǐng)導(dǎo)決定給他個(gè)F績(jī)效，但是以前沒(méi)有出現(xiàn)過(guò)這樣的情況，也不知道F對(duì)應(yīng)的年終獎(jiǎng)是多少，所以就出現(xiàn)了#N/A的錯(cuò)誤，如下圖所示：

這個(gè)時(shí)候我們用IFERROR判斷一下，如果報(bào)錯(cuò)了就給個(gè)處理的方案，這樣#N/A的錯(cuò)誤就能完美解決，公式如下：

關(guān)聯(lián)匹配-INDEX+MATCH

1)功能

在Excel中MATCH函數(shù)可以返回指定內(nèi)容所在的位置，而INDEX又可以根據(jù)指定位置查詢(xún)到位置所對(duì)應(yīng)的數(shù)據(jù)，兩者結(jié)合使用，可以返回指定位置相關(guān)聯(lián)的數(shù)據(jù)。而且INDEX+MATCH組合能夠?qū)崿F(xiàn)反向查找和雙向查找，比VlOOKUP功能更強(qiáng)大更靈活。

2)語(yǔ)法格式

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

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

3)舉例

1、反向查找

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

分析:

先利用MATCH函數(shù)根據(jù)產(chǎn)品名稱(chēng)在C列查找位置

=MATCH(B25,B17:B22,0)

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

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

2、雙向查找

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

分析：

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

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

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

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

最后用INDEX根據(jù)行數(shù)和列數(shù)提取數(shù)值

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

04清洗處理類(lèi)函數(shù)

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

轉(zhuǎn)化為文本-TEXT

功能

將數(shù)值/文本/日期轉(zhuǎn)化為自己想要的文本格式。

語(yǔ)法格式

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

舉例

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

合并單元格-CONCAT

功能

將多個(gè)單元格的內(nèi)容進(jìn)行合并。

語(yǔ)法格式

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

舉例

例如對(duì)A、B、D3列的內(nèi)容進(jìn)行合并。

替換字符-SUBSTITUTE

功能

對(duì)字符串中指定的內(nèi)容文本進(jìn)行替換。

語(yǔ)法格式

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

舉例

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

截取字符串-LEFT/RIGHT/MID

功能

從不同的位置對(duì)字符串進(jìn)行截取。

語(yǔ)法格式

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

舉例

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

定位文本位置-FIND

功能

對(duì)要查找的文本進(jìn)行定位，以確定其位置。

語(yǔ)法格式

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

舉例

例如查找A列中字符“A”出現(xiàn)的位置。

05時(shí)間日期類(lèi)函數(shù)

專(zhuān)門(mén)用于處理時(shí)間格式以及轉(zhuǎn)換，時(shí)間序列在金融、財(cái)務(wù)等數(shù)據(jù)分析中占有較大比重。時(shí)機(jī)序列的處理函數(shù)比下面列舉的還要復(fù)雜，比如時(shí)區(qū)、分片、復(fù)雜計(jì)算等。這里只做一個(gè)簡(jiǎn)單概述。

獲取年月日-YEAR/MONTH/DAY

功能

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

語(yǔ)法格式

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

舉例

例如求B列中日期對(duì)應(yīng)的年/月/日。

獲取第幾周-WEEKNUM

功能

返回對(duì)應(yīng)日期是一年中的第幾個(gè)星期。

語(yǔ)法格式

=WEEKNUM(指定時(shí)間)。

舉例

例如求B列中日期對(duì)應(yīng)是當(dāng)年的第幾周。

獲取周幾-WEEKDAY

功能

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

語(yǔ)法格式

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

舉例

例如求B列中日期對(duì)應(yīng)是周幾。

06統(tǒng)計(jì)計(jì)算類(lèi)函數(shù)

常用的基礎(chǔ)計(jì)算、分析、統(tǒng)計(jì)函數(shù)，以描述性統(tǒng)計(jì)為準(zhǔn)。主要包括：基礎(chǔ)聚合函數(shù)SUM/COUNT/AVERAGE/MIN/MAX等、以及單條件匯總SUMIF/COUNTIF等，多條件匯總SUMIFS/COUNTIFS等、排名函數(shù)RANK、百分位函數(shù)PERCENTILE/QUARTILE、標(biāo)準(zhǔn)差STDEV等，具體用法下面展開(kāi)。

基本聚合函數(shù)-SUM/COUNT/...

功能

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

語(yǔ)法格式

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

舉例

例如求B列中日期對(duì)應(yīng)的年/月/日。

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

功能

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

語(yǔ)法格式

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

舉例

例如對(duì)符合條件的員工的年收入進(jìn)行計(jì)算。

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

功能

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

語(yǔ)法格式

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

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

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

舉例

例如對(duì)符合條件的員工的年收入進(jìn)行計(jì)算。

排名函數(shù)RANK

功能

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

語(yǔ)法格式

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

舉例

例如對(duì)員工的年收入進(jìn)行排序。

百分比函數(shù)-(QUARTILE/PRECENTILE)

功能

計(jì)算一組數(shù)據(jù)的四分位值/百分位值。

語(yǔ)法格式

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

=PERCENTILE(range，k) 是返回?cái)?shù)組的k百分點(diǎn)值的函數(shù)，功能與QUARTILE類(lèi)似，區(qū)別是QUARTILE只能計(jì)算四分位值，而PERCENTILE可以計(jì)算任意百分位的值，功能比QUARTILE更靈活更強(qiáng)大。

舉例

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

標(biāo)準(zhǔn)差STDEV與變異系數(shù)

功能

求一組數(shù)據(jù)的標(biāo)準(zhǔn)差，一般配合平均值使用，計(jì)算變異系數(shù)，其中變異系數(shù)=標(biāo)準(zhǔn)差/平均值，用于判斷一組數(shù)據(jù)的分散程度，變異系數(shù)越大，數(shù)據(jù)越分散，反之，數(shù)據(jù)越集中。

語(yǔ)法格式

=STDEV(range)。

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

舉例

例如計(jì)算各員工年收入的標(biāo)準(zhǔn)差和變異系數(shù)，看大家的年收入是否差異過(guò)大。

 

以上就是數(shù)據(jù)分析工具—Excel常用函數(shù)部分的內(nèi)容，部分?jǐn)?shù)據(jù)分析工具請(qǐng)翻看歷史文章，更多數(shù)據(jù)分析工具的文章持續(xù)更新中，敬請(qǐng)期待，如果覺(jué)得不錯(cuò)，也歡迎分享、點(diǎn)贊和點(diǎn)在看哈~