說(shuō)數(shù)據(jù)透視表是Excel數(shù)據(jù)分析中最高頻使用的功能，一點(diǎn)都不為過(guò)，甚至可以說(shuō)Excel數(shù)據(jù)透視表是數(shù)據(jù)分析師日常工作中最常用的功能（vs Python）。有人可能會(huì)說(shuō)，Python中pandas.pivot_table也可以做透視表，甚至還有很多優(yōu)勢(shì)，比如能夠處理的數(shù)據(jù)量更大（超100萬(wàn)行）、統(tǒng)計(jì)函數(shù)類型更豐富（可自定義聚合函數(shù)），那為什么還要用Excel呢？

原因就兩個(gè)字：靈活。

靈活體現(xiàn)在以下幾個(gè)方面：

1、拖拽布局：值、行、列、篩選四個(gè)區(qū)域調(diào)整方便。

2、匯總計(jì)算：“值匯總方式”有多種匯總函數(shù)類型可選、“值顯示方式”可以切換多種自定義計(jì)算方式，還有計(jì)算字段和計(jì)算項(xiàng)可編輯使用。

3、多項(xiàng)組合：當(dāng)一個(gè)字段有多個(gè)取值（Excel中叫做“項(xiàng)”）可以合并為一項(xiàng)時(shí)，可以手動(dòng)組合，而且可以在新組合上繼續(xù)組合。

4、即時(shí)交互：以上所有操作結(jié)果都是即時(shí)可見(jiàn)的，這在數(shù)據(jù)分析中非常好用，因?yàn)榉治霾皇且患芴崆按_定所有分析操作的事，常常需要邊做邊調(diào)整。

5、直接輸出：Excel數(shù)據(jù)透視表的結(jié)果就是“表”，可作為一個(gè)普通的表直接輸出，基于此可視化或者再透視都是可以的。

當(dāng)然Excel數(shù)據(jù)透視表也有一些小問(wèn)題：

1、文本類型字段只能計(jì)數(shù)，不能取文本的min、max。這個(gè)問(wèn)題Excel無(wú)解，只能通過(guò)其他工具處理。

2、匯總函數(shù)中沒(méi)有非重復(fù)計(jì)數(shù)。這個(gè)Excel有解，通過(guò)將數(shù)據(jù)源轉(zhuǎn)換為數(shù)據(jù)模型就可以使用，后文會(huì)講。

3、字段名改名之后可以通過(guò)“字段設(shè)置”找到源字段名，但字段的項(xiàng)改名之后，無(wú)法知道修改之前是什么。

好了，接下來(lái)，我們就以“A商城銷售數(shù)據(jù)”為案例，把常用功能串起來(lái)，并且保證常用的處理方法沒(méi)有遺漏。這里沒(méi)講到的大多數(shù)用不上，所以有些知識(shí)點(diǎn)沒(méi)學(xué)，也不必覺(jué)得有什么遺憾。與多數(shù)講透視表的文章不同的是，我不僅會(huì)講具體怎么操作，還會(huì)講清楚為什么（在什么場(chǎng)景下）這么操作，以及我更多會(huì)講應(yīng)該怎么做，而不是可以怎么做，把數(shù)據(jù)透視表的最佳玩法，展現(xiàn)給大家，肯定會(huì)涉及到深水區(qū)（我目前還沒(méi)看到有其他地方有講過(guò)），因?yàn)檫@都是我每天反復(fù)磨煉的泣血總結(jié)，其中不乏諸多巧思在里面。話不多說(shuō)，我們開(kāi)始吧。

選擇數(shù)據(jù)源插入數(shù)據(jù)透視表

快速了解將要使用的數(shù)據(jù)

在Excel底部“自定義狀態(tài)欄”右鍵單擊，把統(tǒng)計(jì)的那六項(xiàng)全部勾選，以后選中的區(qū)域的基本六項(xiàng)統(tǒng)計(jì)指標(biāo)就都會(huì)在自定義狀態(tài)欄快速統(tǒng)計(jì)好了，不需要任何函數(shù)或其他操作。

通過(guò)選中明確知道不為空的A列（行id），數(shù)值計(jì)數(shù)為9959，我們就知道這份數(shù)據(jù)總記錄數(shù)是9959。選中“銷售額”列，求和為16068954.13，選中“銷量”列，求和為37534。

建議：在“新工作表”放置新建的數(shù)據(jù)透視表

選擇位置時(shí)建議選“新工作表”，這個(gè)在Excel官方的學(xué)習(xí)文檔中也有提到。透視表與原始數(shù)據(jù)分開(kāi)放置在不同Sheet的，以免相互影響。

可選：數(shù)據(jù)源轉(zhuǎn)換為表格以動(dòng)態(tài)更新數(shù)據(jù)透視表

若數(shù)據(jù)源經(jīng)常需要增加行記錄或者列字段，那么應(yīng)該將數(shù)據(jù)源設(shè)置成可動(dòng)態(tài)更新的。有兩種方法，表格法和名稱法，但這里推薦你用表格法。

表格法就是把普通區(qū)域轉(zhuǎn)成“表”，表的一個(gè)特性就是會(huì)自動(dòng)擴(kuò)展連續(xù)區(qū)域。

名稱法就是通過(guò)名稱管理器使用函數(shù)返回動(dòng)態(tài)區(qū)域來(lái)定義名稱。但這個(gè)方法有個(gè)問(wèn)題是，必須使用完全沒(méi)有空值的行和列，這在數(shù)據(jù)經(jīng)常更新?tīng)顟B(tài)下是無(wú)法徹底保證的。

函數(shù)=OFFSET(訂單!$A$1,,,COUNTA(訂單!$A:$A),COUNTA(訂單!$1:$1))

OFFSET 是一個(gè)引用函數(shù)，第2和第3個(gè)參數(shù)表示行、列偏移量，這里是0意味著不發(fā)生偏移，第4個(gè)參數(shù)和第5個(gè)參數(shù)表示引用的高度和寬度。公式中分別統(tǒng)計(jì)A列和第1行的非空單元格的數(shù)量作為數(shù)據(jù)源的高度和寬度。當(dāng)“銷售明細(xì)表”工作表中新增了數(shù)據(jù)記錄時(shí)，這個(gè)高度和寬度的值會(huì)自動(dòng)地發(fā)生變化,從實(shí)現(xiàn)對(duì)數(shù)據(jù)源區(qū)域的動(dòng)態(tài)引用。

可選：需要統(tǒng)計(jì)不同維度下的“非重復(fù)計(jì)數(shù)”則勾選添加到數(shù)據(jù)模型

勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”后，統(tǒng)計(jì)函數(shù)中就可以使用“非重復(fù)計(jì)數(shù)”了。

后文會(huì)有詳細(xì)操作。

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

值的放置及顯示

為什么先放值？通常我們對(duì)數(shù)據(jù)是了解總體情況，而不知細(xì)節(jié)，所以先放值可以校驗(yàn)數(shù)據(jù)結(jié)果是否與預(yù)期相符。若大數(shù)都有差異，那就需要排查取數(shù)邏輯是否正確。

tips：當(dāng)我們往透視表的值區(qū)域放了2個(gè)以上的匯總字段時(shí)，列區(qū)域會(huì)出來(lái)“數(shù)值”，這個(gè)是可以拖動(dòng)放置到行區(qū)域里的。

兩種不同擺放位置的結(jié)果如下：

調(diào)整數(shù)字格式

在數(shù)據(jù)透視表的統(tǒng)計(jì)字段上右鍵點(diǎn)擊，出現(xiàn)下圖，可以看到有“設(shè)置單元格格式”和“數(shù)字格式”兩個(gè)指令。

單元格格式與數(shù)字格式的區(qū)別是，“單元格格式”僅設(shè)置選中單元格區(qū)域的格式，而“數(shù)字格式”設(shè)置活動(dòng)值字段的格式。我們希望數(shù)字格式是應(yīng)用在字段上而不是選中的單元格上，這樣在數(shù)據(jù)透視的布局發(fā)生變化時(shí)，該字段的所有值格式都是設(shè)置好的。這樣看起來(lái)貌似設(shè)置“數(shù)字格式”是更好的選擇，但其實(shí)不然。

若一次性選中整列（含總計(jì)行）應(yīng)用“單元格格式”，布局變動(dòng)也不會(huì)導(dǎo)致設(shè)置好的格式在新行上失效。另外使用“單元格格式”還有其他兩個(gè)好處：

設(shè)置方式較多：這里的單元格格式，與“開(kāi)始-數(shù)字”功能菜單中的設(shè)置按鈕是一致的，也可以用Ctrl+1快捷調(diào)出設(shè)置窗口，或者應(yīng)用快捷鍵，或者右鍵快捷菜單上方的浮動(dòng)工具欄上的常用格式工具

設(shè)置格式更多：除了數(shù)字格式外，還可以設(shè)置字體、對(duì)齊等其他單元格格式。

所以推薦使用“單元格格式”設(shè)置數(shù)據(jù)透視表的字段格式，并且建議在選完指標(biāo)之后就設(shè)置好數(shù)字格式，后續(xù)看數(shù)會(huì)更輕松。

數(shù)據(jù)透視表的數(shù)值區(qū)域只能是數(shù)值格式，所以源數(shù)據(jù)的字段以及數(shù)據(jù)透視表中的公式或計(jì)算字段的返回結(jié)果，都只能是數(shù)值或可直接轉(zhuǎn)換為數(shù)值的日期或文本字符。

值的匯總方式

是計(jì)數(shù)、求和，還是求平均、最大、最小。同一個(gè)字段可以放多種不同匯總方式。

這里的非重復(fù)計(jì)數(shù)選項(xiàng)是置灰的，需要先建數(shù)據(jù)模型，在數(shù)據(jù)透視表字段選擇區(qū)域中的最下方，點(diǎn)擊“更多表格”。

字段篩選區(qū)域、值的名稱會(huì)變得略有不同外，其他功能沒(méi)變?？梢钥吹椒侵貜?fù)計(jì)數(shù)城市數(shù)是573個(gè)。

計(jì)算字段

本案例中我們要計(jì)算兩個(gè)字段：

1、分項(xiàng)及整體的利潤(rùn)率是多少？

2、分項(xiàng)及整體的折扣率是多少？

看C5單元格的公式我們知道，剛剛創(chuàng)建的計(jì)算字段公式（=利潤(rùn)/銷售額），其實(shí)是sum(利潤(rùn))/sum(銷售額)，是整體利潤(rùn)率，即利潤(rùn)率的加權(quán)平均。

這是因?yàn)橛?jì)算公式是應(yīng)用在公式中的列總和上，而不是項(xiàng)（單個(gè)記錄）上，無(wú)論這個(gè)列字段在數(shù)據(jù)透視表中的統(tǒng)計(jì)方式是求和還是平均。這就會(huì)導(dǎo)致在求整體的平均折扣率時(shí)，會(huì)出現(xiàn)問(wèn)題。

首先直接對(duì)折扣字段求平均肯定是錯(cuò)誤的，因?yàn)檫@沒(méi)有考慮到不同金額的權(quán)重差異。

正確的整體折扣率公式應(yīng)該是sum(銷售額*折扣)/sum(銷售額)。但如果計(jì)算公式寫成銷售額*折扣/消費(fèi)額，在計(jì)算公式的實(shí)際應(yīng)用其實(shí)是=sum(銷售額)*sum(折扣)/sum(消費(fèi)額)=sum(折扣)，最終的折扣率結(jié)果是折扣字段的加總=1059.7，這顯然不對(duì)。

推薦的解決方法是，在源數(shù)據(jù)當(dāng)中新增一列“折扣額”，計(jì)算公式=銷售額*折扣，再在計(jì)算字段中新增“折扣率”，計(jì)算公式=折扣額/銷售額，最終得到總的折扣率是9.15%。

出個(gè)題考考大家，當(dāng)總計(jì)計(jì)算公式為每個(gè)子項(xiàng)結(jié)果的加權(quán)平均的場(chǎng)景下，以下哪類計(jì)算公式應(yīng)該先提前在源數(shù)據(jù)中增加計(jì)算字段，而不能在數(shù)據(jù)透視表中的計(jì)算字段中使用呢？

1、(A+B)/C

2、A/(B-C)

3、(A*B)/C

4、A/(B*C)

注：以上四種類型的加減號(hào)相互替換、乘除號(hào)相互替換的答案不變。

答案是只有3和4需要。

當(dāng)總計(jì)計(jì)算公式為每個(gè)子項(xiàng)結(jié)果的加總時(shí)，則透視表的計(jì)算公式中只能有加減法，不能有乘除，乘除計(jì)算需要提前在源數(shù)據(jù)中處理好。

好的，到這里，我們數(shù)據(jù)透視表的上篇就講完了。在下篇中，我會(huì)講哪些內(nèi)容呢？

• 行列維度及維度組合
• 計(jì)算項(xiàng)
• 值顯示方式
• 多行多列多值復(fù)雜情況下如何布局
• 再透視
• 表樣式
• 自動(dòng)設(shè)置VBA