EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」 – 電癮院

EXCEL的精髓「樞紐分析表」

若說「合併列印」為Word的必殺技，能融會貫通學會此招，等同學會Word最上乘之武功，那什麼是另一位Office大將「Excel」的精髓呢？想當然非「樞紐分析表」莫屬了。

在前三篇Excel的下拉式選單功能(資料驗證)及VLOOKUP的教學，其實是為了這一篇「樞紐分析表」所鋪的梗，也可以說是使用樞紐分析表的蹲馬步基本功，為什麼這麼說呢？這是因為在使用樞紐分析表分析資料時，最怕的是輸入錯誤的資料，而造成統計的不準確，若是能使用下拉式選單來輸入一些固定的資料，就能大大的減少這部份的錯誤。

接著我以一個實例，來讓大家充份了解樞紐分析表這個東西，是要在什麼樣的情況下可以使用。

而若是您對Word必殺技「合併列印」有興趣的話，則可以參考這一系列的教學文章：

• 每個人都要學的Word實用技巧教學-「合併列印」
• Word合併列印教學之二：同一頁套印兩筆不同的紀錄Next Record標記
• Word合併列印教學之三：「合併列印」結合Microsoft Outlook寄出電子郵件

複雜的文具用品採購流程

以前我在一家做電子材料的中小企業公司，當過網管一段時間，雖然是當網管，但也要負責公司一件攸關每個人的大事，就是訂購公司的「文具用品」，在公司裡，我的重要性大概只僅次於每天要統計吃便當人數的會計而已。

話說採購文具用品本身這件事，雖是只是小事，就連國中生也能勝任，但要做的好、做的快、做的準確，就真的要靠本事了。因為說簡單，其實還挺煩雜的，我來說說原本我們公司這整個流程要怎麼做吧。

首先，各部門都有一位負責統計自己部門所需文具用品的人，他們的工作就是拿著一張制式「文具用品需求表」，詢問自己部門的同事，有沒有文具用品的需求，並於最後統計完之後，將此訂購表送交給我，而這樣的文具訂購表，大概如下圖所示：

而部門別，大概就有製造部、財務部、開發部、業務部、品管部等。

接著我就要過濾在各部門的需求表中，有沒有寫不清楚的品項，像是有人會寫「原子筆一支」，那誰知道是要哪個牌子的原子筆？水性？油性？顏色？等等的問題，或是會有買太多的狀況，最後將這些部門給的資料整理好之後，就要根據這些資料，產出二個表格，一個是給文具店老闆的訂購單，另一個則是給會計的文具採購報表。

給文具店老闆的清單，要有品項、單價及數量，如下表：

文具店老闆只管您要訂什麼東西，不需要管您公司哪個部門或哪個人各訂了哪些東西。

然後給會計的報表，要有每個部門所採購的文具用品項目、金額的彙整表，像是如下表：

因此有這樣的需求時，就要開始在Excel上打表格，然後再用計算機，把每個部門所採購的東西加總起來，光是加總這些資料，大概就要昏了，而且非常的花時間，萬一又碰到有人要加加減減的，更是讓人悲「憤」萬分啊!

好不容易把表格都弄好後，就先把訂購單傳真文具店老闆，然後等老闆來送貨時，點交文具用品之後，就把各部門所訂購的文具，發送到各部門去，最後再把收據發票，連同之前彙整好的報表交給會計，這大概就是整個文具用品訂購的流程。

整個採購流程的優化

而這整個文具訂購下單的流程中，最花時間及傷神的就是在收到各部門的文具清單之後，要彙整出給文具店及會計的報表，因為要是算錯數量而訂錯東西，不只訂購該文具用品的同事會不高興之外，還要跟文具店老闆退貨，總之就是會很麻煩。

此外，要是給會計的報表，訂單數量金額對不起來，光是找出問題的地方，就一個頭兩個大了，由於前輩是女生，她在做這些統計這些文具可能比較細心，但我一個大男人，常常在手動計算之後，都會有誤差出現，真的很讓人抓狂。

因此像這樣的雜事，在離職的前輩教我做一次之後，第二次我自己做時，流程中某些彙整的作法，就已經被我整個修改過了，網管這工作，本來就應該是個爽缺，怎可浪費在這些雜事上面呢？

最後原本容易出錯，且要搞一整天的工作，現在大概只要20分鐘，而且都是電腦在計算，因此完全不會出錯，而這整個優化的訣竅就是使用Excel的「樞紐分析表(Pivot Tables)」。

建立基本的資料表

這一段，我主要說明如何將各部門所收集來的調查表，輸入到Excel，以便用來轉換成樞紐分析表所需的資料表。

建立供樞紐分析表使用的資料表，算是相當重要的準備工作，所需要的資料欄位，根據您所需要分析的詳細程度而定，接著就是「資料的平整化」，如下圖所示：

每個人所訂購的文具用品，都需要獨立成一列，以第一~三列來說，管理部就會有三列，因此就必需填入三次的「管理部」文字，這點是不能偷懶的，同理在「分類」欄位也是一樣。

當然也不要因為要填入這麼多同樣的文字而感到退卻，你是不是覺得自己有可能會填錯了？

其實這部份的問題，就需要利用「資料驗證」與「VLOOKUP公式」來解決了，請您將「部門、姓名」及「分類、品名、單位、單價」分別使用以下文章的教學，來製作出更好用的下拉式選單，並且帶入相對應的資料。

• Excel深度教學：使用「資料驗證」來製作「下拉式選單」
• Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定
• Excel深度教學：VLOOKUP查表函數及比對中文字串時發生錯誤的問題

而接著，就是將「文具用品需求表」需入到資料表裡了，在這一步中，是千萬不能輸入錯誤了，要不然之後所分析出來的資料就會有錯誤，因此要特別的細心。

建立樞紐分析表

當我們己經輸入好所需的資料表之後，接著就是要來產生「樞紐分析表」了，產生的方式非常的簡單，首先，我先以給文具店老闆的文具清單為示範。

給文具店老闆的「文具用品採購清單」

Step 1. 首先，選取「所有資料的欄位」，選取時可以直接選取一整個欄位，連沒有輸入資料的列，也一併選擇進來，接著再點擊「插入\樞紐分析表」如下圖：

Step 2. 跳出「建立樞紐分析表」的對話盒後，可以看到我們所選取的欄位定義資料，另外我們也可以選擇要將樞紐分析表放在哪個位置，一般來說，我們把它放在「新工作表」即可，直接點擊〔確定〕吧。

Step 3. 接著就是要來設定樞紐分析表所要顯示的項目了，下圖是完全都還沒有設定的樞紐分析表，而設定的方式，就是直接拖拉右邊上面的「選擇要新增到報表的欄位」清單項目，至右邊下面的「列標籤」、「欄標籤」或是「值」這三個地方，因此，無論是要給文具店老闆的報表，或是給會計的報表，起點都是從這一步開始設定起的。

Step 4. 接著我將「分類」及「品名」，分別拖曳進下方的「列標籤」及「值」，我們就可以馬上看到左方的工作表，馬上就起了變化，我們可以一直從右方設定面版中，一直的修改欄位設定，直到左方的資料表是我們所需要的為止。

從上圖可以看出，這個表格的資料是有問題的，所以我們要一一的來修正一下。

取消「文具用品分類」的小計

在這一步中，不曉得您有沒有覺得「書寫用品」後的計數「3」，以及「紙製品」、「辦公用品」後的計數，會和文具用品細項裡的計數混亂，因此首先，我就要先把這個不必要，且沒有意義的小計給取消掉。

Step 5. 在右下方，已經被拖曳進去「列標籤」裡的「分類」，點擊右邊的倒三角形小圖形上，並從選單中點選「欄位設定」，如下圖：

Step 6. 接著我們將「小計與篩選」頁籤中的「小計」，設定在「無」的地方，最後再點擊〔確定〕即可。

之後我們就會看到，這些文具用品分類的小計，都已經消失了，這樣計數是不是清楚多了。

以列表方式顯示項目標籤

由於我不喜歡以這樣的「階層」方式來排列，因此我要將「顯示項目標籤」的方式，由「大綱模式」改成「列表方式」。

Step 7. 首先，一樣根據上一步的方式，開啟「欄位設定」對話盒，接著切換到「版面配置與列印」，再勾選「以列表方式顯示項目標籤」，最後再點擊〔確定〕，如下圖：

而這兩者之間的變化，你可以比對參考一下，上面的圖和下面的圖，你就會知道這兩種顯示項目標籤的格式了。

隱藏空白的欄位

由於我們在一開始選擇要給樞紐分析表分析的資料表範圍時，是連同「空白」的資料一起選擇進去，因此你會發現到「列標籤」裡的文具用品分類，居然有個「(空白)」，要是文具店老闆看到這個項目時，會不會一頭霧水呢？因此，我們先把這個「空白」的欄位給顯藏起來吧，以免被誤會了。

Step 8. 點擊「列標籤」右邊的按鈕，再從「選取欄位」中，將「(空白)」前面的「打勾」給取消掉，如下圖：

經過這樣的設定，空白這個欄位就會消失了。

修改「數量」的顯示方式

若您有仔細看清楚「數量」欄位數值的話，你會發現到有點異常，「筆芯」這個訂購數量應該是「4」個才對，為何這顯示「1」呢？你放心，這並不是Excel的問題，而是因為Excel在計算合併這些文具數量時，預設是採用「計數」的方式，因此才會顯示「1」筆資料，因此我們要將「計數」改為「加總」，才是符合我們想要的數字。

Step 9. 請在右下角「值」版面中，點擊在「計數-數量」旁的倒三角形圖示，並在選單中點選「欄位設定」，接著在「值欄位設定」的對話盒中，將「摘要方式」頁籤裡的「摘要值欄位方式」改為「加總」，最後再點擊〔確定〕，如下圖：

經過加總的設定，數量的數值即可變為「4」，也就是我們真正想要的數字了。

Step 10. 為了報表的完整性，我們也可以再「值」的版面，再加入「小計」的「加總」，目的是為了知道該文具用品的小計是多少元，底下就是給文具店老闆完整的報表。

給會計的「各部門採購金額彙整表」

若您有實際練習過給文具店老闆報表的話，製作給會計的報表一定也難不倒您的。

首先，使用同樣的步驟，從Step 1操作到Step 3，接著將「分類」拉進「列標籤」、「部門」拉進「欄標籤」，而在「值」的部份，則拉進「小計」，且將「小計」的彙整方式改為「加總」，如下圖所示，給會計的表格也能輕鬆完成。

從上表，一眼就可以看出，到底是哪個部門花最多錢，如果你把「姓名」也拉進「欄標籤」的話，甚至也能統計出，到底誰是請購文具用品的大戶呢，這強大且充滿彈性的分析能力，正是樞紐分析表好用的地方。

Excel教學相關文章

• EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」
• Excel深度教學：使用「資料驗證」來製作「下拉式選單」
• Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定
• Excel深度教學：VLOOKUP查表函數及比對中文字串時發生錯誤的問題
• 讓Excel自動檢查儲存格中是否有輸入重複的資料

網友Ariel：整個表格都設為「以列表方式顯示項目標籤」

先謝謝Ariel問了一個這麼好的問題，這個純粹以教學而教學的文章，所以若真實際要用到時，確實真的會造成這樣的不方便，因此，我找了一下微軟的資料，雖然沒有找到直接修改預設的方式，但卻可以一次將整個「樞紐分析表」，看要整個修改成「大綱模式」或是「列表方式」都可以，操作如下：

首先，先切換到您的樞紐分析表的工作頁，然後點擊一下「樞紐分析表」的任何位置，此時在上方的工具列，就會出現「樞紐分析表工具」的功能，如下圖：

接著點擊「樞紐分析表工具」的「設計」頁籤，然後再點擊「報表版面配置」的下拉選單，最後再點選「以列表方式顯示」，如下圖：

同理，如果您想要切換到「以大綱模式顯示」也是用同樣的步驟就可以了。