說(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ì)算公式寫(xiě)成銷售額*折扣/消費(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