Excel樞紐分析表:快速建立報表,加總值遞減排序

Excel樞紐分析表可以快速匯總資料,本文以銷售排行榜為例,介紹如何將排名轉換成便於評估的分數,建立樞紐分析表進行欄位配置,依照加總值遞減排序。

前兩節介紹使用表格工具分析所取得資料,熟悉Excel的讀者應該知道在這方面還有一個强大工具:樞紐分析表。這一節便跟各位介紹如何快速建立樞紐分析表,執行彙總的排行分析。

一、移除表格篩選器

先前所建立好的表格交叉分析篩選器,這一節用不到了,在篩選器上面滑鼠右鍵,選擇快捷選項中的移除即可,例如這裡是「移除”出版社”」。

Excel樞紐分析表:快速建立報表,加總值遞減排序 1

二、計算排行分數

準備新增一欄。只要在I1儲存格輸入「分數」,因為原有表格是到H欄,I1儲存格是緊靠著原有表格範圍,Excel會自動偵測是需要在表格新增一欄,所以會自動將I欄和表格相同長度的範圍納入表格。

接著於I2儲存格輸入計算公式:「=101-[@排行]」,表示101减掉排行數字,按Enter鍵後會自動將一整個I欄都填滿,這是建立表格方便的地方。分數等於101減掉排行數字,會使得排行1的分數就是100,排行2是99,一直到了排行100的分數便是1。如此是轉換原有排行成為分數,順序剛好相反,分數越高表示暢銷程度越高、排行權重越高。匯總計算時會比較符合一般分析直覺:加總數字和價值比重成正比,很容易直覺地進行分析比較。

Excel樞紐分析表:快速建立報表,加總值遞減排序 3

三、建立樞紐分析表

和本章第二節跟第三節一樣,上方功能區「表格工具>設計>工具」裡的「>以樞紐分析表摘要」,準備建立樞紐分析表,這裡順手將表格名稱修設改為「排行榜分析」。

Excel樞紐分析表:快速建立報表,加總值遞減排序 5

四、分析資料範圍

「建立樞紐分析表」這個對話方塊主要有兩個步驟。分析資料的來源和要放置樞紐分析表的位置。資料來源因為是從表格建立的,在「選取表格或範圍」欄位即是表格名稱。位置部分不做任何修改,保留預設值是在「新工作表」產生。

Excel樞紐分析表:快速建立報表,加總值遞減排序 7

五、樞紐分析表欄位

Excel很快會新增一個建立好樞紐分析表的工作表,一開始樞紐分析表沒有任何內容,點選之後會出現「樞紐分析表欄位」,如圖所示左邊是報表(表格)的欄位清單,直接滑鼠按住拖曳的方式,將「書名」拖曳到右邊的「列」,將「分數」拖曳到右邊的「欄」,工作表上就會看到同步更新的樞紐分析表彙總內容。

Excel樞紐分析表:快速建立報表,加總值遞減排序 9

六、設定排序選項

樞紐分析表上面和本章第二節介紹的表格工具一樣,有個三角形篩選排序的圖標,點選之後,在跳出來的視窗選擇「更多排序選項」。

Excel樞紐分析表:快速建立報表,加總值遞減排序 11

七、加總值遞減排序

「排序選項」視窗點選「遞減(Z到A)方式」,選單下拉選擇「加總-分數」,作用是依照加總分數的欄位資料進行遞減的重新排序。

Excel樞紐分析表:快速建立報表,加總值遞減排序 13

八、排行榜綜合分析

最後呈現的報表,是11月這段期間所有書籍的分數從大排到小,很容易看得出來哪幾本書最暢銷。

Excel樞紐分析表:快速建立報表,加總值遞減排序 15

Excel樞紐分析表強大之處

本章第二、三節所介紹的表格工具雖然方便,但如果要像這一節同樣整理出加權後的匯總排行榜並不是很容易。設計Excel函數是可以類似效果,但公式會過於複雜,而且維護不易。利用這一節介紹的樞紐分析表簡單操作,輕鬆能建立樞紐分析表,並且設定為匯總的排序報表,經過這樣的實際操作,讀者應該可以感受到樞紐分析表强大之處。

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

Excel樞紐分析表:快速建立報表,加總值遞減排序 17
人人都學得會的網路大數據分析入門》