說資料透視表是Excel資料分析中最高頻使用的功能，一點都不為過，甚至可以說Excel資料透視表是資料分析師日常工作中最常用的功能（vs Python）。有人可能會說，Python中pandas.pivot_table也可以做透視表，甚至還有很多優勢，比如能夠處理的資料量更大（超100萬行）、統計函數類型更豐富（可自定義聚合函數），那為什麼還要用Excel呢？
原因就兩個字：靈活。
靈活展現在以下幾個方面：
1、拖拽布局：值、行、列、篩選四個區域調整友善。
2、彙總計算：“值彙總方式”有多種彙總函數類型可選、“值顯示方式”可以切換多種自定義計算方式，還有計算字段和計算項可編輯使用。
3、多項組合：當一個字段有多個取值（Excel中叫做“項”）可以合并為一項時，可以手動組合，而且可以在新組合上繼續組合。
4、即時互動：以上所有操作結果都是即時可見的，這在資料分析中非常好用，因為分析不是一件能提前确定所有分析操作的事，常常需要邊做邊調整。
5、直接輸出：Excel資料透視表的結果就是“表”，可作為一個普通的表直接輸出，基于此可視化或者再透視都是可以的。
當然Excel資料透視表也有一些小問題：
1、文本類型字段隻能計數，不能取文本的min、max。這個問題Excel無解，隻能通過其他工具處理。
2、彙總函數中沒有非重複計數。這個Excel有解，通過将資料源轉換為資料模型就可以使用，後文會講。
3、字段名改名之後可以通過“字段設定”找到源字段名，但字段的項改名之後，無法知道修改之前是什麼。
好了，接下來，我們就以“A商城銷售資料”為案例，把常用功能串起來，并且保證常用的處理方法沒有遺漏。這裡沒講到的大多數用不上，是以有些知識點沒學，也不必覺得有什麼遺憾。與多數講透視表的文章不同的是，我不僅會講具體怎麼操作，還會講清楚為什麼（在什麼場景下）這麼操作，以及我更多會講應該怎麼做，而不是可以怎麼做，把資料透視表的最佳玩法，展現給大家，肯定會涉及到深水區（我目前還沒看到有其他地方有講過），因為這都是我每天反複磨煉的泣血總結，其中不乏諸多巧思在裡面。話不多說，我們開始吧。
選擇資料源插入資料透視表
快速了解将要使用的資料
在Excel底部“自定義狀态欄”右鍵單擊，把統計的那六項全部勾選，以後選中的區域的基本六項統計名額就都會在自定義狀态欄快速統計好了，不需要任何函數或其他操作。
通過選中明确知道不為空的A列（行id），數值計數為9959，我們就知道這份資料總記錄數是9959。選中“銷售額”列，求和為16068954.13，選中“銷量”列，求和為37534。
建議：在“新工作表”放置建立的資料透視表
選擇位置時建議選“新工作表”，這個在Excel官方的學習文檔中也有提到。透視表與原始資料分開放置在不同Sheet的，以免互相影響。
可選：資料源轉換為表格以動态更新資料透視表
若資料源經常需要增加行記錄或者列字段，那麼應該将資料源設定成可動态更新的。有兩種方法，表格法和名稱法，但這裡推薦你用表格法。
表格法就是把普通區域轉成“表”，表的一個特性就是會自動擴充連續區域。
名稱法就是通過名稱管理器使用函數傳回動态區域來定義名稱。但這個方法有個問題是，必須使用完全沒有空值的行和列，這在資料經常更新狀态下是無法徹底保證的。
函數=OFFSET(訂單!$A$1,,,COUNTA(訂單!$A:$A),COUNTA(訂單!$1:$1))
OFFSET 是一個引用函數，第2和第3個參數表示行、列偏移量，這裡是0意味着不發生偏移，第4個參數和第5個參數表示引用的高度和寬度。公式中分别統計A列和第1行的非空單元格的數量作為資料源的高度和寬度。當“銷售明細表”工作表中新增了資料記錄時，這個高度和寬度的值會自動地發生變化,從實作對資料源區域的動态引用。
可選：需要統計不同次元下的“非重複計數”則勾選添加到資料模型
勾選“将此資料添加到資料模型”後，統計函數中就可以使用“非重複計數”了。
後文會有詳細操作。
資料透視表的值
值的放置及顯示
為什麼先放值？通常我們對資料是了解總體情況，而不知細節，是以先放值可以校驗資料結果是否與預期相符。若大數都有差異，那就需要排查取數邏輯是否正确。
tips：當我們往透視表的值區域放了2個以上的彙總字段時，列區域會出來“數值”，這個是可以拖動放置到行區域裡的。
兩種不同擺放位置的結果如下：
調整數字格式
在資料透視表的統計字段上右鍵點選，出現下圖，可以看到有“設定單元格格式”和“數字格式”兩個指令。
單元格格式與數字格式的差別是，“單元格格式”僅設定選中單元格區域的格式，而“數字格式”設定活動值字段的格式。我們希望數字格式是應用在字段上而不是選中的單元格上，這樣在資料透視的布局發生變化時，該字段的所有值格式都是設定好的。這樣看起來貌似設定“數字格式”是更好的選擇，但其實不然。
若一次性選中整列（含總計行）應用“單元格格式”，布局變動也不會導緻設定好的格式在新行上失效。另外使用“單元格格式”還有其他兩個好處：
設定方式較多：這裡的單元格格式，與“開始-數字”功能菜單中的設定按鈕是一緻的，也可以用Ctrl+1快捷調出設定視窗，或者應用快捷鍵，或者右鍵快捷菜單上方的浮動工具欄上的常用格式工具
設定格式更多：除了數字格式外，還可以設定字型、對齊等其他單元格格式。
是以推薦使用“單元格格式”設定資料透視表的字段格式，并且建議在選完名額之後就設定好數字格式，後續看數會更輕松。
資料透視表的數值區域隻能是數值格式，是以源資料的字段以及資料透視表中的公式或計算字段的傳回結果，都隻能是數值或可直接轉換為數值的日期或文本字元。
值的彙總方式
是計數、求和，還是求平均、最大、最小。同一個字段可以放多種不同彙總方式。
這裡的非重複計數選項是置灰的，需要先建資料模型，在資料透視表字段選擇區域中的最下方，點選“更多表格”。
字段篩選區域、值的名稱會變得略有不同外，其他功能沒變。可以看到非重複計數城市數是573個。
計算字段
本案例中我們要計算兩個字段：
1、分項及整體的利潤率是多少？
2、分項及整體的折扣率是多少？
看C5單元格的公式我們知道，剛剛建立的計算字段公式（=利潤/銷售額），其實是sum(利潤)/sum(銷售額)，是整體利潤率，即利潤率的權重平均。
這是因為計算公式是應用在公式中的列總和上，而不是項（單個記錄）上，無論這個列字段在資料透視表中的統計方式是求和還是平均。這就會導緻在求整體的平均折扣率時，會出現問題。
首先直接對折扣字段求平均肯定是錯誤的，因為這沒有考慮到不同金額的權重差異。
正确的整體折扣率公式應該是sum(銷售額*折扣)/sum(銷售額)。但如果計算公式寫成銷售額*折扣/消費額，在計算公式的實際應用其實是=sum(銷售額)*sum(折扣)/sum(消費額)=sum(折扣)，最終的折扣率結果是折扣字段的加總=1059.7，這顯然不對。
推薦的解決方法是，在源資料當中新增一列“折扣額”，計算公式=銷售額*折扣，再在計算字段中新增“折扣率”，計算公式=折扣額/銷售額，最終得到總的折扣率是9.15%。
出個題考考大家，當總計計算公式為每個子項結果的權重平均的場景下，以下哪類計算公式應該先提前在源資料中增加計算字段，而不能在資料透視表中的計算字段中使用呢？
1、(A+B)/C
2、A/(B-C)
3、(A*B)/C
4、A/(B*C)
注：以上四種類型的加減号互相替換、乘除号互相替換的答案不變。
答案是隻有3和4需要。
當總計計算公式為每個子項結果的加總時，則透視表的計算公式中隻能有加減法，不能有乘除，乘除計算需要提前在源資料中處理好。
好的，到這裡，我們資料透視表的上篇就講完了。在下篇中，我會講哪些内容呢？
- 行列次元及次元組合
- 計算項
- 值顯示方式
- 多行多列多值複雜情況下如何布局
- 再透視
- 表樣式
- 自動設定VBA
如果你有其他感興趣的内容想了解，或者已有内容有疑問的，都可以私信我。
前文回顧
Excel資料分析——資料輸入
Excel資料分析——資料處理