Excel類別清單彙總:樞紐分析表重新整理與IF函數

Excel樞紐分析表通常用於統計分析,實務中也可以利用樞紐特性彙總輔助。本文以銷售明細表為例,介紹如何簡單應用樞紐及IF函數,得到地區客戶別清單。

先前有篇文章,分享如何依照客戶地區別,自動加總銷售金額,這裡面有個小麻煩,每個月銷售明細不同,或者明細需要可能更新(增添刪除),如此,客戶地區的組合項目就會變動,每次都要重新抓組合,也是很討厭,那麼高度萬能的Excel,在這方面是否有小撇步?在此分享:

一、銷貨明細表欄位

簡化版的銷貨明細表,用到的欄位有地區、客戶、出貨日、幣別、金額。

簡化版的銷售明細表

二、建立樞紐分析表

「建立樞紐分析表」,將滑鼠移到最上面有英文字母ABCDE的欄位地方,游標從白十字變成黑色向下箭頭,此時可以選取工作表一整個欄位範圍,直接拉A到E欄位:「明細!$A:$E」。

直接拉A到E欄位:「明細!$A:$E」

三、樞紐版面配置

在生成的樞紐分析表,將地區及客戶拉到列標籤,輕輕鬆鬆彙總出地區客戶的組別。關於Excel樞紐分析表版面配置,詳細可參考贊贊小屋相關文章

將地區及客戶拉到列標籤

四、IF函數判斷

樞紐分析表好是好,問題是如果同一地區有不同客戶,第二個以下的客戶便會空白,對此只能弄個公式補起來:「=IF(B3=””,J2,B3)」,有了這個補起來的資料,便可以複製,貼到有需要的管理報表,如果報表格式固定,乾脆直接連結即可,自動更新!

只能弄個公式補起來:「=IF(B3="",J2,B3)」

五、引用整欄資料

在第二步,範圍選擇A到E所有欄位,用意是如果明細倘若有增添或減少,很方便就可以利用樞紐分析表,更新地區客戶組別。例如,原來的明細表刪掉D,增加F。

原來的明細表刪掉D,增加F

六、樞紐重新整理

將滑鼠移到樞紐分析表,右鍵,點擊「重新整理」。

右鍵,點擊「重新整理」

七、IF函數公式更新

可以看到右邊早已設定好的公式,自動更新了,而且就是更新成變動後的地區客戶組別。

更新成變動後的地區客戶組別

八、變更資料來源

除了「重新整理」,還有一個絕招,不是在原來檔案刪刪減減,而是直接重新選取新的明細表:「樞紐分析表」、「選項」、「變更資料來源」。

「樞紐分析表」、「選項」、「變更資料來源」

Excel樞紐分析表快速更新

本文的樞紐分析表範圍設定為一整欄,如此雖然有自動更新的效果,但在樞紐分析表總會產「(空白)」這一個多餘項目,剛好這裡的案例只是將樞紐分析表當作輔助工具,毋須在意。然而如果真的是要作為報告用的樞紐分析表,顯然「(空白)」項必須處理,後續再為讀者介紹如何利用函數公式,將樞紐分析表的來源範圍設定為更加精準的自動更新。

加強學習:贊贊小屋樞紐分析表文章合輯

Excel類別清單彙總:樞紐分析表重新整理與IF函數 1