在 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-樞紐分析表應用與練習

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()