2016年10月7日 星期五

Excel-樞紐分析表應用與練習

在 Excel 中如下圖左的資料表共有 1000 筆,其中包含了類別、項目和數量的資料。其中:類別內容為『甲/乙/丙/丁/戊/己』,項目內容為『子/丑/寅/卯/辰/巳』。要如何產出「各個類別中各個項目前三名數量總和的前四名」(如下圖右)?並且希望類別和項目都依由大到小遞減排序該如何處理?
例如:在下圖右中各類別的前四名是:甲→戊→丙→己。而甲的前三名是:卯→丑→子。
Excel-樞紐分析表應用與練習
這時候『樞紐分析表』工具就可以派上用場了!參考以下的步驟來練習:
1. 選取資料清單中的任一個儲存格,點選[插入/表格]功能表中的「樞紐分析表」。
Excel-樞紐分析表應用與練習
2. 在[建立樞紐分析表]對話框中確認分析的資料範圍後,接著顯示樞紐分析表欄位選項。
Excel-樞紐分析表應用與練習
3. 將「類別」欄位新增至「列」中,接著將「項目」欄位新增至「列」中。(注意:要將「項目」欄位置於「類別」欄位之下)。再將「數量」欄位新增至「值」中。
Excel-樞紐分析表應用與練習
4. 將「加總 - 數量」修改名稱為「小計」。
Excel-樞紐分析表應用與練習
Excel-樞紐分析表應用與練習
5. 點選 「類別」欄位的下拉式清單中,選取「值篩選/前10項」。
Excel-樞紐分析表應用與練習
6. 設定「類別」欄位要藉由「小計」欄位篩選最前 4 項(前四名)。
Excel-樞紐分析表應用與練習
7. 仿照步驟5和步驟6的做法,將「項目」欄位設定要藉由「小計」欄位篩選最前 3 項(前三名)。結果如下圖:
Excel-樞紐分析表應用與練習
8. 將儲存格A3中的「列標籤」修改為「類別」(即修改欄位名稱為「類別」)。
9. 選取儲存格A4(其中一個類別),然後點選「分析/作用中欄位」功能表區中的「欄位設定」。
10. 在[版面配置與列印]標籤下選取「以列表方式顯示項目標籤」,並勾選「重複項目標籤」。
Excel-樞紐分析表應用與練習
結果如下:
Excel-樞紐分析表應用與練習
11. 選取「小計」欄位中的任一個儲存格(本例:儲存格C5),再選取[編輯/排序與篩選]功能的「從最大到最小排序」。
Excel-樞紐分析表應用與練習
結果如下:
Excel-樞紐分析表應用與練習
12. 點選「類別」欄位中的篩選圖示,再選取「更多排序選項」。
Excel-樞紐分析表應用與練習
13. 選取「遞減(Z到A)方式」,再選取「小計」。(即以「小計」欄位由大到小排序)
Excel-樞紐分析表應用與練習
結果如下:
Excel-樞紐分析表應用與練習
複製樞紐分析結果,即為所求。
如果你不想要顯示每個類別的小計,則可以在[欄位設定]對話框中的「小計與篩選」標籤下,指定小計:無。
Excel-樞紐分析表應用與練習
最後,附上所有類別和項目的小計結果供檢驗樞紐分析結果是否正確。
Excel-樞紐分析表應用與練習
這樣的範例,你可以用在例如「業績報表」,像是業務員:甲、乙、丙、丁、戊、己,產品:子、丑、寅、卯、辰、巳。由業績前四名業務員來看其前三名的產品銷售業績。(參考下圖)
Excel-樞紐分析表應用與練習

沒有留言:

張貼留言

檢視其他文章

好康東東