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