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函數是可以類似效果,但公式會過於複雜,而且維護不易。利用這一節介紹的樞紐分析表簡單操作,輕鬆能建立樞紐分析表,並且設定為匯總的排序報表,經過這樣的實際操作,讀者應該可以感受到樞紐分析表强大之處。

每天學習,每天充電:VBA爬蟲專案系列文章

Excel樞紐分析表:快速建立報表,加總值遞減排序 17
人人都學得會的網路大數據分析入門》
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.