電癮院

以初學者的角度,來看待電腦教學這回事。

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

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

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

Previous

Android手機尋找程式「Wheres My Droid」,立即定位手機所在位置,遺失手機不用慌?

Next

SHARP FU-W43T、HITACHI RD-12CS使用心得分享

37 Comments

  1. changyang319

    抱歉,這比較複雜,我應該是沒有辦法。

  2. Coco Liang520

    版主您好, 請教問題如下~
    就此範例之樞紐分析表每月都有製作, 那是否可以就每月的樞紐分析表再會整成年度的報表如下:

    文具用品分類/部門/Q1/Q2/Q3/Q4/合計

  3. changyang319

    能幫上您的忙,太高興了。

  4. 深受excel所苦的人

    真的太太太感謝您哦
    我搞了一個晚上的excel 終於在您的blog找到您的教學
    講解得很仔細又清楚 非常的感謝您

  5. changyang319

    我剛有測試過,就算它的欄位數量是零,也是會顯示出來。

    只要是你有列在報表裡的項目,樞紐分析表都一定會列出來,不曉得你說的想要列出0來,是怎麼樣的一個報表,可以寄你的檔案來給我看一下嗎?

  6. 黃小寶

    您好;想來請教您一個問題,就是如何讓樞紐分析表顯示出沒有的值?舉例來說:在這個範例裡面,如果書寫用具有數量的(筆芯4、鉛筆6)有顯示出來,但我還想要顯示出沒有數量的(原子筆0) 要怎麼樣呢?
    謝謝您~

  7. changyang319

    其實我不太懂你說的「資料拖移值」是什麼。

  8. 訪客

    資料拖移值的欄位請問一下值的欄位有比筆數限定嗎

  9. changyang319

    謝謝您喔。

  10. Apple Hu

    寫得超讚的教學文!

  11. Purpoe

    唐先生好,
    我想請教,如何讓每列標籤填滿儲存格,例如,"書寫文具"的標籤列:

    [ 類別] [ 業務] [價格]
    -書寫文具 -A 100
    100
    -B 100
    -塗改用品 -C
    ———————————————-
    顯示成:
    [ 類別] [ 購買客戶] [價格]
    -書寫文具 -A 100
    書寫文具 A 100
    書寫文具 -B 100
    -塗改用品 -C 80

    我的版本是2007,不知有無功能可選,以自動將標籤填滿空白格(此為用於我後端countif須用類別作銷售數統計而做的名稱填滿)而複製貼上無法更動樞紐。
    "-"非減號,想表示展開的意思。
    感謝!

  12. changyang319

    其實您這樣說明,我還是不懂你想要怎麼做,如果動作比較複雜,在網路上真的不好說明。

  13. vera

    你好,想請問個問題,我現在手上有個檔案裏面有兩個工作表(A,B),基本上標題資料是一樣的,現在我們發現A工作表跟B工作表資料有重複,現在想要把兩個工作表合併,並且把資料當中寄出日期較早的整列資料移除,我該用甚麼方法

  14. Kayla

    您好~謝謝您的教學,真的幫助我很大!!
    想要請問,如果用下拉選項選擇了不同類別,是否有辦法將同一類別的數值計算在一起,然後使用圓餅圖或長條圖來顯示他們的比率呢??
    ex.每日花費項目分類A.B.C…然後一次計算出本月A類別、B類別總共個佔本月花費多少…
    Excel是可以這樣做的嗎??因為他每次都直接幫我分成好多個A跟B….Orz

  15. changyang319

    謝謝您提供的資訊。

  16. leo

    excel處理少量數據還是很不錯的工具,但是遇到數據量大且複雜的時候就力不從心了,而且數據是靜態的,不能及時的響應變化,週報、月報、年報每次都要重複製表,不如使用報表軟體來做web報表時事獲取數據進行展示、採用模板,一次製表不需要重複,有需求的可以試試finereport,設計界面和excel一樣,對初學者來說輕鬆上手

  17. Jimmy

    您好,請教一下
    若我在樞紐的A1 設了一個報表篩選,假設是"月份" 好了,舉例一月樞紐會在A12跑出一個總計的欄位,以計算A4到A11的數值個數加總。當我篩選到二月時,原來一月加總的A12欄位就不是A12了,可能是A13 或A14。 因為我需要畫直方圖,橫軸會來自不同月份的"總計" 列的數值,因為我每變換月份,相對應到的"總計"列也會有所不同,可能是A12/A13/A14…..。因此數字就會跑掉,因為我需要在旁邊再畫個直方圖之類的。有沒有什麼公式,當我在變動月份時,可以自動幫我加總"總計"那列的個數,但我在篩選月份時,不會本來對應的是a12到f12, 又變成了a14到f14, 感謝!

  18. changyang319

    這個我好像也沒有解決的辦法,這是vlookup的規則,有重複的話,應該會取第一個吧?

    想要解決的方式,就是改成用資料庫的形式,用VBA使用SQL指令的方式來取回資料。

  19. 林鋒

    格主您好
    小弟在工作上運用此技巧時,在vlookup的階段有點問題
    希望格主能為小弟解惑
    如下列所示 因同一個貨號會有複數的負責人 因此在vlookup時會產生錯誤
    請問是否有辦法解決?
    A B
    貨號 負責人
    123 Andy
    123 Jack
    321 Tim
    432 Ann

  20. changyang319

    不客氣,也謝謝您的造訪喔。

  21. kiki

    很棒的教學
    謝謝分享

  22. 稻香

    謝謝您的示範, 但您這個案例應該僅是針對"一個工作表", 如果目前是"兩個工作表", 但其表格抬頭(欄位名稱)都一樣, 僅僅是數據上的差異, 請問該怎樣在一個樞紐表中彙總數據? Thanks!

  23. 稻香

    您好! 請教您一個關於excel 2013 樞紐分析表的使用問題:

    當我想要在同一個檔案的兩個不同工作表(但欄位均相同), 以樞紐分析表取抓取資料, 請問該如何設定資料抓取範圍? 目前似乎只能以一個工作表為之。感謝您!

  24. changyang319

    謝謝您的誇獎哦。

  25. 33

    謝謝格主的教學,造福大眾~樞紐分析表真的是excel裡很實用的技巧!

  26. changyang319

    不客氣,謝謝您的造訪。

  27. 謝謝你詳細耐心的教學。然我能夠迅速了解樞紐分析的精髓。

  28. changyang319

    謝謝您的誇獎喔。
    我也很喜歡吃您們家的早餐。(LAYA…)

  29. LAYA

    很棒的教學
    一次學到三招(要花些時間操作)
    讚啦^^

  30. changyang319

    我認真看了您的敘述,終於懂了您的意思,這真的是一個非常好的問題,我已補充在文末的地方,若我有理解錯誤的地方,請再跟我說謝謝。

  31. Ariel

    板大您好:謝謝您的教學文 ^ ^
    有個問題想請您幫忙解惑-若我有許多欄位放到列標籤,由於如您所說的版面不便悅讀,我只能一項項調欄位設定嗎?有沒有辦法讓它預設就是"以列表方式顯示項目標籤"呢?謝謝您 ^ ^

  32. changyang319

    把彙整好的樞紐分析表,先「選取」表格式後,再「複製」,再到另一個空白的資料表中,在「貼上」的下方,點擊「貼上值」,這樣就只會去複製「數值」的部份了。

  33. Betty

    您好!謝謝您提供好多文書處理的好方法.
    可否請問一下.Excell"合併彙算"出來的結果資料,要如何複製到其他工作表?
    我只須要那些彙算出來的數字,(不要那些+號底下的數字)
    是用選擇性貼上嗎?
    謝謝你! 我真的急需處理一個報表~

  34. changyang319

    其實沐恩這個應該也很強的,沐恩你來教不是更好?

  35. 沐恩

    最近我妹就在做這個
    我可以拿給他參考了XD

發佈留言

Powered by WordPress & Theme by Anders Norén