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

EXCEL的精髓「樞紐分析表」

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

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

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

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

複雜的文具用品採購流程

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

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

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

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

給文具店老闆的清單,要有品項、單價及數量,如下表:
1834-02
文具店老闆只管您要訂什麼東西,不需要管您公司哪個部門或哪個人各訂了哪些東西。

Advertisement

然後給會計的報表,要有每個部門所採購的文具用品項目、金額的彙整表,像是如下表:
1834-03
因此有這樣的需求時,就要開始在Excel上打表格,然後再用計算機,把每個部門所採購的東西加總起來,光是加總這些資料,大概就要昏了,而且非常的花時間,萬一又碰到有人要加加減減的,更是讓人悲「憤」萬分啊!

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

整個採購流程的優化

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

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

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

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

建立基本的資料表

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

建立供樞紐分析表使用的資料表,算是相當重要的準備工作,所需要的資料欄位,根據您所需要分析的詳細程度而定,接著就是「資料的平整化」,如下圖所示:
1834-04
每個人所訂購的文具用品,都需要獨立成一列,以第一~三列來說,管理部就會有三列,因此就必需填入三次的「管理部」文字,這點是不能偷懶的,同理在「分類」欄位也是一樣。

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

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

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

1 關於 “EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」” 的評論

發表迴響

以經常會遇到的問題及「初學者」的角度,來看待「電腦教學」這回事。