Excel 從入門到表格分析（三）

查找與引用函數(shù)

這一節(jié)我們主要關(guān)注在查找與引用函數(shù)中使用次數(shù)非常多的VLOOKUP函數(shù)韩玩。VLOOKUP函數(shù)的主要場(chǎng)景是先匹配再引用垒玲。這樣的場(chǎng)景其實(shí)和數(shù)據(jù)庫領(lǐng)域中的JOIN（連接）函數(shù)是十分類似的。在數(shù)據(jù)庫領(lǐng)域中找颓，JOIN操作主要是將兩個(gè)表或者多個(gè)表的對(duì)應(yīng)key（鍵值）進(jìn)行關(guān)聯(lián)合愈，然后合并兩個(gè)表的屬性從而形成一個(gè)大表。VLOOKUP函數(shù)是針對(duì)某一單元格的值與選中的參考列中所有的值進(jìn)行匹配击狮，如果匹配成功想暗，則填充指定第i列中對(duì)應(yīng)行的值（相當(dāng)于將第i列合并）。VLOOKUP的參數(shù)是VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])帘不。其中,lookup_value即為想要匹配的key说莫，table_array即為想要JOIN的表，JOIN的主鍵為table_array的第一列寞焙，col_index_num為如果匹配成功則輸出table_array中第col_index_num列的值储狭，[range_lookup]表示是使用模糊查詢還是精準(zhǔn)查詢。注意捣郊，這里的模糊查詢并非傳統(tǒng)字符串意義上的模糊查詢辽狈，而是搜索過程從精準(zhǔn)查詢的逐個(gè)遍歷改成二分查找，如果匹配的列并非排序后的值呛牲，或者并非能找到匹配的值刮萌，模糊查找的結(jié)果有可能是錯(cuò)誤的，所以建議使用精確查找：range_lookup=0娘扩。

下面我們舉一個(gè)數(shù)據(jù)庫中常見的例子着茸，來看看如何使用VLOOKUP來實(shí)現(xiàn)類似JOIN的功能。假設(shè)我們有兩個(gè)數(shù)據(jù)表琐旁，CUST表存有用戶ID和用戶名涮阔，另一個(gè)表是銀行賬戶表ACC，存有賬戶ID灰殴，用戶ID敬特，和余額，具體如下圖：

image.png

現(xiàn)在的需求是要將用戶名填充至第二個(gè)表牺陶，用來展示每個(gè)用戶名和其對(duì)應(yīng)賬戶的存款伟阔。如果使用數(shù)據(jù)庫SQL語言，可以輸入：SELECT ACC.acc_id, CUST.name, ACC.balance FROM CUST, ACC WHERE CUST.cust_id = ACC.cust_id;掰伸，這里的SQL使用兩個(gè)表的用戶ID作為JOIN的key皱炉，從而實(shí)現(xiàn)關(guān)聯(lián)。如果使用VLOOKUP實(shí)現(xiàn)用戶名加入到ACC表中的功能碱工，lookup_value為ACC表中CUST_ID的每一行;table_array為想要合并的參考表CUST的單元格位置娃承，包含列CUST_ID和Name (注意，在參考表CUST中JOIN的主鍵一定是第一列怕篷，這里我們想要JOIN的主鍵是CUST_ID历筝，所以CUST表符合VLOOKUP的要求。否則廊谓，我們需要將CUST_ID移動(dòng)至第一列梳猪。); col_index_num為2，因?yàn)橄胍喜⒌膶傩?code>Name在CUST表中的第二列蒸痹；range_lookup為0春弥，使用精確查詢。我們首先填寫ACC表中Name的第一行叠荠，然后可以使用EXCEL自動(dòng)填充完成Name的其他行匿沛。第一行的VLOOKUP函數(shù)應(yīng)該為VLOOKUP(N18,J17:K20,2,0)。這里榛鼎，N18指ACC表中CUST_ID的第一行逃呼，為ACC表中的JOIN主鍵；J17:K20是指CUST表的單元格位置者娱；2是指要合并的Name屬性在CUST表的第二列；0為精確查詢。然后吆寨，我們使用自動(dòng)填充功能完成Name一列的填充：

image.png

image.png

image.png

數(shù)據(jù)透視表

上一節(jié)我們介紹了常用的查找與引用函數(shù)VLOOKUP叶圃，通過特定條件匹配和提取對(duì)應(yīng)的數(shù)據(jù)袄膏。這一節(jié)中我們將要展示EXCEL更強(qiáng)大的功能——數(shù)據(jù)透視表。什么是數(shù)據(jù)透視表呢掺冠？在整個(gè)數(shù)據(jù)分析流程中沉馆，首先需要按目標(biāo)確定（也就是問題的定義），然后進(jìn)行數(shù)據(jù)的獲取與清洗德崭，從而可以進(jìn)入描述性分析的階段斥黑。描述性分析主要是針對(duì)數(shù)據(jù)的計(jì)算、相關(guān)性分析眉厨，為洞察結(jié)論锌奴、提出解決方案作出鋪墊。比如憾股，我們想要統(tǒng)計(jì)招聘崗位的城市分布缨叫，我們需要針對(duì)不同類型、不同工作地點(diǎn)的招聘崗位荔燎，進(jìn)行計(jì)次的統(tǒng)計(jì)耻姥。那么如何快速實(shí)現(xiàn)數(shù)據(jù)的分類匯總呢？數(shù)據(jù)透視表可以幫助我們作出這樣的多維度的分類匯總（其實(shí)有咨，這種多維度的匯總分析與數(shù)據(jù)倉庫中的多維度數(shù)立方CUBE的概念十分類似）琐簇。

數(shù)據(jù)透視表(Pivot Table)是一個(gè)可以通過拖、拉座享、拽等圖形化界面婉商，快速完成自動(dòng)化的數(shù)據(jù)匯總的功能。在使用數(shù)據(jù)透視表時(shí)應(yīng)該注意渣叛，數(shù)據(jù)不能有任何空行或空列丈秩，而且標(biāo)題只有一行。下面我們用微軟官方文檔給出的例子來展示如何使用數(shù)據(jù)透視表淳衙。假設(shè)我們現(xiàn)在有以下這張表：

image.png

這張表有幾個(gè)維度：日期蘑秽、購買者、類型箫攀、金額肠牲。一般我們使用數(shù)據(jù)透視表做多維度分析，對(duì)于數(shù)值型數(shù)據(jù)靴跛，我們經(jīng)常會(huì)使用各種數(shù)值聚合（統(tǒng)計(jì)）函數(shù)缀雳，比如求和、平均等梢睛。對(duì)于非數(shù)值型數(shù)據(jù)（類別數(shù)據(jù)）肥印，我們經(jīng)常使用計(jì)次運(yùn)算來統(tǒng)計(jì)對(duì)應(yīng)的分布识椰。在插入數(shù)據(jù)透視表以前，我們首先選中表中的一個(gè)單元格深碱，然后在插入選項(xiàng)中選擇數(shù)據(jù)透視表（Pivot Table）腹鹉。在創(chuàng)建數(shù)據(jù)透視表時(shí)，需要選擇表格的區(qū)間范圍莹痢，同時(shí)需要選擇將數(shù)據(jù)透視表創(chuàng)建在新的工作表還是當(dāng)前工作表的某個(gè)位置。為了方便查看墓赴，我們將數(shù)據(jù)透視表創(chuàng)建在當(dāng)前表格中表格的右邊竞膳。

image.png

image.png

可以生成以下數(shù)據(jù)透視表：

image.png

image.png

image.png

考慮我們之前使用的招聘數(shù)據(jù)休偶，如果我們想要統(tǒng)計(jì)不同地區(qū)的職位數(shù)量梁厉，之前的章節(jié)是使用SUMIFS或者COUNTIF完成，其實(shí)我們可以使用數(shù)據(jù)透視表來完成踏兜。我們選擇城市作為行指標(biāo)词顾，數(shù)值指標(biāo)為城市的計(jì)次（COUNT）：

image.png

數(shù)據(jù)透視表會(huì)展示出每個(gè)城市的職位數(shù)量：

image.png

image.png

可以看出疹尾，北京上忍、上海、廣州纳本、深圳這四大一線城市提供的數(shù)據(jù)分析相關(guān)的工作的職位數(shù)比其他城市要多睡雇，其中，北京的職位數(shù)最多饮醇。

綜上它抱，通過靈活地選擇對(duì)應(yīng)的維度和所要計(jì)算的數(shù)值，加上簡(jiǎn)單的拖動(dòng)操作朴艰，我們就可以快速生成不同維度下的統(tǒng)計(jì)指標(biāo)观蓄，從而提升了分析效率混移。如果數(shù)據(jù)發(fā)生改變，我們需要通過數(shù)據(jù)透視表中的刷新功能侮穿，重新計(jì)算數(shù)據(jù)透視表歌径。

認(rèn)識(shí)圖表

上一節(jié)我們介紹了十分常用的數(shù)據(jù)匯總工具——數(shù)據(jù)透視表。使用數(shù)據(jù)透視表只能夠提供數(shù)據(jù)匯總的表格亲茅，但表格本身不如圖形更加直觀回铛。這一節(jié)我們主要關(guān)注在數(shù)據(jù)可視化中有哪些常見的圖表類型。

我們經(jīng)常需要對(duì)數(shù)據(jù)做對(duì)比分析克锣，即分析數(shù)據(jù)項(xiàng)之間的大小關(guān)系茵肃。在這種情境下，通常我們會(huì)給出折線圖或者直方圖袭祟。比如验残，我們想要了解在一年12月份中，數(shù)據(jù)分析崗位數(shù)目的變化趨勢(shì)巾乳。那么我們可以繪制一個(gè)折線圖您没，橫軸是1月到12月，縱軸是每個(gè)月的職位數(shù)目胆绊。這樣氨鹏，我們可以清楚地看到職位數(shù)目隨著月份的變化的走勢(shì)。同時(shí)压状，為了對(duì)比不同城市的職位數(shù)目喻犁，我們可以畫出以橫軸為城市，縱軸為職位數(shù)目的柱狀圖何缓。這一類突出體現(xiàn)數(shù)據(jù)變化程度肢础，或者是比較數(shù)據(jù)大小的圖表類型，稱為比較型圖表碌廓〈洌基于以時(shí)間線為橫軸（比如，1月-12月）的折線圖谷婆，可以稱為時(shí)序數(shù)據(jù)慨蛙。

在進(jìn)行對(duì)比分析后，我們可能會(huì)發(fā)現(xiàn)一些變量之間可能存在某種相關(guān)性纪挎。為了描述這樣的相關(guān)性期贫，一般會(huì)將可能相關(guān)的兩個(gè)變量畫成散點(diǎn)圖，如果散點(diǎn)圖的趨勢(shì)用直線擬合的斜率為正值异袄，則大致為正相關(guān)通砍，反之則為負(fù)相關(guān)。我們稱能反映變量之間的相關(guān)性的圖表為關(guān)系型圖表。

我們還是用招聘數(shù)據(jù)作為例子來看城市和職位數(shù)量之間的相關(guān)性封孙。由于城市是類別數(shù)據(jù)迹冤，我們使用城市的GDP作為代表城市的一個(gè)指標(biāo)，從而得到城市g(shù)dp與職位數(shù)量的表格：

image.png

image.png

如果想要體現(xiàn)數(shù)據(jù)分布的密集程度奏瞬，我們可以使用常見的直方圖。直方圖中的柱體高度代表出現(xiàn)的頻率泉孩，每個(gè)柱體頂部連成一條線硼端，可以得出大致的數(shù)據(jù)分布。對(duì)于文字型數(shù)據(jù)寓搬，我們也可以采用新型的詞云圖珍昨，出現(xiàn)的詞或字越大，說明這個(gè)詞或字在數(shù)據(jù)集中的出現(xiàn)頻率高句喷。這兩種可以反映數(shù)據(jù)分布密集程度的圖表镣典，我們稱為分布型圖表。

如果想要知道某一類別占比總類別的大小唾琼，我們可以使用常見的餅圖兄春。根據(jù)不同城市的招聘職位數(shù)目，我們可以畫出如下餅圖：

image.png

這幅圖可以清楚地展示出北京锡溯、上海赶舆、深圳的職位數(shù)目占比較高，北京的占比最高祭饭。如果想要展示出餅圖內(nèi)每個(gè)模塊的占比芜茵，可以使用圖表的添加圖表元素（Add Chart Element）中的數(shù)據(jù)標(biāo)簽（Data Labels），然后選擇對(duì)應(yīng)的樣式倡蝙。但是打印出數(shù)據(jù)標(biāo)簽為每個(gè)模塊的頻數(shù)九串，而不是頻率。如果我們想得到對(duì)應(yīng)的頻率（百分比）寺鸥，我們可以點(diǎn)擊對(duì)應(yīng)的數(shù)據(jù)標(biāo)簽蒸辆，然后右鍵選擇設(shè)置數(shù)據(jù)標(biāo)簽格式征炼，在彈出的屬性欄中選擇百分比，并取消顯示“值”躬贡。最后我們可以得出比較直觀的餅圖：

image.png

綜上谆奥，我們可以總結(jié)一下常見的數(shù)據(jù)圖表，折線圖拂玻、直方圖酸些、散點(diǎn)圖、餅圖的用法：

image.png