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

查找與引用函數(shù)

這一節(jié)我們主要關(guān)注在查找與引用函數(shù)中使用次數(shù)非常多的VLOOKUP函數(shù)。VLOOKUP函數(shù)的主要場景是先匹配再引用。這樣的場景其實(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ù)值，加上簡單的拖動(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ù)大小的圖表類型，稱為比較型圖表?；谝詴r(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