Home 電腦硬體與軟體文書處理教學 EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」

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

by 唐先生
Published: Updated:

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

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

Excel教學相關文章

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

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

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

excel_report_format-1.jpg

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

excel_report_format-2.jpg

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

也許您會喜歡

37 comments

changyang319 2017-02-02 - 08:40:19

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

Reply
Coco Liang520 2016-12-23 - 09:44:48

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

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

Reply
changyang319 2016-10-23 - 18:27:04

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

Reply
深受excel所苦的人 2016-10-10 - 00:50:39

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

Reply
changyang319 2016-03-13 - 11:17:55

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

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

Reply
黃小寶 2016-03-02 - 17:21:53

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

Reply
changyang319 2016-01-29 - 09:57:52

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

Reply
訪客 2016-01-13 - 21:42:23

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

Reply
changyang319 2015-11-22 - 23:44:35

謝謝您喔。

Reply
Apple Hu 2015-11-05 - 17:43:07

寫得超讚的教學文!

Reply
Purpoe 2015-10-18 - 20:58:15

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

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

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

Reply
changyang319 2015-09-10 - 18:14:22

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

Reply
vera 2015-09-09 - 18:42:16

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

Reply
Kayla 2015-09-06 - 13:58:04

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

Reply
changyang319 2015-04-23 - 15:22:48

謝謝您提供的資訊。

Reply
leo 2015-04-16 - 10:46:30

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

Reply
Jimmy 2015-03-01 - 13:50:18

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

Reply
changyang319 2015-01-25 - 14:59:37

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

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

Reply
林鋒 2015-01-21 - 07:26:08

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

Reply
changyang319 2014-12-01 - 17:53:30

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

Reply
kiki 2014-11-21 - 14:06:53

很棒的教學
謝謝分享

Reply
稻香 2014-10-30 - 15:28:53

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

Reply
稻香 2014-10-19 - 17:14:48

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

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

Reply
changyang319 2014-06-24 - 18:39:05

謝謝您的誇獎哦。

Reply
33 2014-06-22 - 20:32:01

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

Reply
changyang319 2014-05-23 - 06:26:19

不客氣,謝謝您的造訪。

Reply
2014-05-20 - 13:49:03

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

Reply
changyang319 2014-05-20 - 11:46:01

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

Reply
LAYA 2014-05-17 - 16:33:04

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

Reply
changyang319 2014-01-17 - 10:28:28

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

Reply
Ariel 2014-01-16 - 14:42:35

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

Reply
changyang319 2013-12-25 - 18:40:53

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

Reply
Betty 2013-12-25 - 15:21:58

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

Reply
changyang319 2013-11-22 - 15:12:31

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

Reply
沐恩 2013-11-21 - 09:50:33

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

Reply

Leave a Comment

這個網站使用cookies來改善您的瀏覽體驗。我們預設您同意此設定,但如果您不希望使用cookies,您有權選擇退出。 接受 詳細內容