|
說數據透視表是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快捷調出設置窗口，或者應用快捷鍵，或者右鍵快捷菜單上方的浮動工具欄上的常用格式工具 設置格式更多：除了數字格式外，還可以設置字體、對齊等其他單元格格式。 所以推薦使用“單元格格式”設置數據透視表的字段格式，并且建議在選完指標之后就設置好數字格式，后續看數會更輕松。  數據透視表的數值區域只能是數值格式，所以源數據的字段以及數據透視表中的公式或計算字段的返回結果，都只能是數值或可直接轉換為數值的日期或文本字符。 值的匯總方式是計數、求和，還是求平均、最大、最小。同一個字段可以放多種不同匯總方式。  這里的非重復計數選項是置灰的，需要先建數據模型，在數據透視表字段選擇區域中的最下方，點擊“更多表格”。  字段篩選區域、值的名稱會變得略有不同外，其他功能沒變?？梢钥吹椒侵貜陀嫈党鞘袛凳?73個。  計算字段本案例中我們要計算兩個字段： 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需要。 當總計計算公式為每個子項結果的加總時，則透視表的計算公式中只能有加減法，不能有乘除，乘除計算需要提前在源數據中處理好。 好的，到這里，我們數據透視表的上篇就講完了。在下篇中，我會講哪些內容呢？
|
|
|
