文書處理教學

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

EXCEL的精髓「樞紐分析表」

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

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

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

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

複雜的文具用品採購流程

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

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

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

excel_pivot_table-1.jpg

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

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

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

excel_pivot_table-6.jpg

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

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

excel_pivot_table-7.jpg

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

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

整個採購流程的優化

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

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

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

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

建立基本的資料表

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

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

excel_pivot_table-2.jpg

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

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

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

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

建立樞紐分析表

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

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

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

excel_pivot_table-3.jpg

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

excel_pivot_table-4.jpg

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

excel_pivot_table-5.jpg

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

excel_pivot_vendor-1.jpg

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

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

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

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

excel_pivot_vendor-2.jpg

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

excel_pivot_vendor-3.jpg

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

excel_pivot_vendor-4.jpg

以列表方式顯示項目標籤

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

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

excel_pivot_vendor-5.jpg

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

excel_pivot_vendor-6.jpg

隱藏空白的欄位

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

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

excel_pivot_vendor-7.jpg

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

修改「數量」的顯示方式

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

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

excel_pivot_vendor-8.jpg

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

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

excel_pivot_vendor-9.jpg

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

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

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

excel_pivot_account-1.jpg

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

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

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

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

excel_report_format-1.jpg

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

excel_report_format-2.jpg

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

你可能對這些內容也有興趣:

37 thoughts on “EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」