電癮院

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

Category: 文書處理教學 (Page 2 of 8)

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

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

Excel深度教學:VLOOKUP查表函數及比對中文字串時發生錯誤的問題

利用VLOOKUP查表公式,尋找其它欄位的數值

當我們利用「資料驗證」製作好文具用品分類的下拉式選單,也製作好第二層「品名」的動態下拉式選單之後,請參考「Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定」,再來就是要傷腦筋每一項文具產品所對應的「單位」及「單價」了,總不能製作好便利的下拉式選單之後,卻要自己逐一的輸入各文具產品的「單位」及「單價」吧?

因此在這邊就要介紹一個在Excel中,非常好用的高階函式,也是「VLOOKUP」。

excel_vlookup-1.jpg

以下是微軟官方網站「如何使用 VLOOKUP 或 HLOOKUP 尋找完全相同的值」,對於VLOOKUP的參數資料,而括號內的文字是我的註解:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  • Lookup_value:在陣列第一欄中所要尋找的值。(要用來比對的文字)
  • Table_array:查閱資料中的資訊表格。(用來「被」比對的資料庫)
  • col_index:相符數值應該傳回的 table_array 應傳回相符的值。(比對後,要傳回資料庫的哪個欄位,從1開始)
  • Range_lookup:無論您想要尋找完全相符或近似的相符值,其為指定的邏輯值。如果是 TRUE 或是省略,就會傳回近似的相符值;也就是說,如果沒有找到完全相符的值,就會傳回下一個小於 Lookup_value 的最大值。如果是 FALSE,VLOOKUP 則會尋找完全相符值。如果找不到完全相同的值,就會傳回 #N/A 錯誤值。

看完了這些微軟的拉拉喳喳說明後,總之就是「要查什麼?從哪裡查?傳回什麼資料?」,接下來直接做個實例應用給大家看,就可以知道這個公式要如何運用了。

「VLOOKUP」函數範例說明

首先在使用VLOOKUP之前,我們要先把要查表的「表格」給準備好,這是一定的,因為您想要查表,總是要先有一個「表」來查吧?

Step 1. 以我們一直以來的文具用品的例子來說,我另外建立了一個「售價表」的工作表,如下圖所示,而我們所有使用的查表範圍即為紅色框框的地方,你知道為什麼要省略「分類」及「流水號」嗎?

excel_vlookup-2.jpg

這是因為VLOOKUP的函式規定,所要查詢的表格第一欄,就是我們所要比對的數值,因此我們就要把「品名」這個將要用來比對的資料,安排在第一欄的位置。

Step 2. 接下來就要在要輸入的表格中,設定VLOOKUP的公式了,請點擊「單位」下面的儲存格,接著輸入「=VLOOKUP(C2,售價表!C1:E10, 2, FALSE)」,公式中的說明如下:

C2 => 為參考值,在這為「鉛筆」,我們就是要利用這個字串,來查詢「表格」對應該欄位資料。
售價表!C1:E10 => 為資料庫的表格,要查詢的資料,都是從這邊來查詢的,也就是在上一步中的表格資料範圍。
2 => 是要回傳「表格」的欄位。(1為品名、2為單位、3是單價),因此這邊會回傳「單位」這個欄位的值。
FALSE => 比對時,必需要完全相同,才回傳資料。

excel_vlookup-3.jpg

而您應該會注意到,我所輸入的公式,好像和我剛才說的不太一樣:

  • =VLOOKUP(C2,售價表!C1:E10, 2, FALSE)」 <= 我剛才所說的公式<=”” li=””>
  • =VLOOKUP(C2,售價表!C$1:E$10, 2, FALSE)」 <= 我實際輸入的公式<=”” li=””>
    </=>
  • </=>

這兩個差別在於「$錢號」,稍微懂Excel的朋友應該知道,「$」在拖曳儲存格複製公式時,可以「固定」行號或列號,這我們等一下再示範。

比對中文字會發生錯誤的問題

在這裡我要提出一個問題:

「=VLOOKUP(C2,售價表!C1:E10, 2)」,若您在輸入公式時,少掉「FALSE」時,會發生什麼狀況呢?

由於少了FALSE,在比對時,就會以相似的比率來決定回傳的數值,因此這在比對「中文字」時,就會出現問題,例如下圖,當我將公式的FALSE拿掉之後,居然單位出現的是「包」,而不是正確的「支」了。

excel_vlookup-4.jpg

其實,為了要百分百的抓出正確的數值,把「FALSE」加上去就對了。

Step 3. 回到我們輸入資料的地方,當我們把「$」放進欄位的地方,在使用「上、下」拖曳複製公式時,C2這個儲存格的位置就會自動的遞增,第三格變C3,以此類推,所以下面的儲存格,也能快速的填好資料,如下圖:

excel_vlookup-5.jpg

而以同樣的方法來填入「單價」的公式,如下:

  • =VLOOKUP(C2,售價表!C$1:E$10, 3, FALSE)

和之前「單位」的差別只差在回傳的欄位值,回傳「單價」時,要回傳第3欄,這是為什麼這裡要放「3」的原因。

excel_vlookup-6.jpg

同樣將「單價」下方的儲存格,也複製好公式,就可以完成我們所要製作的表格了。

利用「名稱管理員」可以將VLOOKUP做的更好

以上述的方式來使用「VLOOKUP函式」已經是非常好了,同樣的公式,還可以寫的更簡潔、更好維護,也就利用前幾篇Excel教學文章中所提供的「名稱管理員」。

只要您新增定義「售價表」這個「名稱」之後,如下圖:

excel_vlookup-7.jpg

在輸入VLOOKUP公式的表格範圍時,就不需輸入又臭又長的表格定義了,不僅如此,將來若是查表的「表格」資料有異動,您都只需要去修正「名稱管理員」的資料定義範圍即可,而不需要大工程的修正輸入工作表的所有儲存格,因此在使用VLOOKUP時,強烈建議一定要使用「名稱」來輔助。

excel_vlookup-8.jpg

若不曉得名稱要如何定義的話,請參考「Excel深度教學:使用「資料驗證」來製作「下拉式選單」」這篇文章。

Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定

Excel的多階層選單

在前一篇Excel的教學文中,我介紹了使用「資料驗證」的方式,來製作「下拉式選單」( Excel深度教學:使用「資料驗證」來製作「下拉式選單」),若您還不曉得這是什麼,您應該先看完那篇之後,再回過頭來看這篇。

而本篇要說明的是,有時我們的清單資料,是有「階層」的分類,尤其是產品很多的公司,一定都會把數量龐大的產品分門別類,要不然將所有的產品都設定成單一個「下拉式選單」,你可能也很難找的到。

例如下圖,是我簡單製作的文具用品清單,在每個「分類」中,都有屬於它自己的文具用品項目,因此在「品名」出現的下拉式選單,才不會一次出現所有的文具用品,而讓你選的眼花繚亂。

excel_step_combo-1.jpg

那問題就來了,「分類」還不是什麼問題,就直接設定「清單」的來源就好了,因為它的清單項目都是「固定」的,但「品名」這一欄就不一樣了,因為這一欄的「清單」,是根據「分類」下去改變,是不是將「清單來源」給設死的,因此這邊就有個小技巧來分享給各位,教大家如何來處理這種狀況。

Excel二層下拉式選單設定教學

Step 1. 首先,在其它的工作表(例如:Sheet2)製作好一個有階層的資料清單,在下面的例如中,我簡單製作了一個文具用品的階層資料,所有的文具用品分為三類,分別是「書寫用品」、「辦公用品」及「紙製品」,並將分類的資料,填入第一列中,而該分類中的文具用品項目,就分別填入每個欄位裡,例如「書寫用品」裡有「原子筆、筆芯、鉛筆」等項目,如下圖:

excel_step_combo-2.jpg

Step 2. 製作好分類的資料庫清單後,接著新增「文具用品分類」及每個分類下的文具用品的清單「名稱」,總共會新增四個「名稱」出來,如下圖名稱管理員所示,若您對於新增「名稱」的方法有問題的話,請參考「Excel深度教學:使用「資料驗證」來製作「下拉式選單」」這篇文章。

excel_step_combo-3.jpg

Step 3. 接著先將「分類」的資料驗證,設定為「清單」,並將來源設定為「名稱」中的「文具用品分類」。

excel_step_combo-4.jpg

若您能設定成功,我們就能在分類中,利用「下拉式選單」來選取文具用品的大分類項目了,如下圖:

excel_step_combo-5.jpg

Step 4. 接著就是本篇文章的重點所在,就是「品名」欄位的「資料驗證」如何設定,因為在這個欄位中的清單是「變動」的,也就是隨著我們選擇的「分類」,而在「品名」欄位中,出現該分類的文具用品項目。

首先,先選取整個「品名」欄位,也就是「欄位C」,接著在來源中輸入「=INDIRECT(B1)」,最主要就是利用「INDIRECT」這個函數,而儲存格B1就是分類的那一欄位,因此這邊要根據您的情況來做設定。

這邊你也許會有個問題,就是我怎麼會將這一整個C欄位,都設定「=INDIRECT(B1)」呢?第二列不應該要設定成「=INDIRECT(B2)」,而第三列應該是「=INDIRECT(B3)」嗎?

沒錯,您的疑問是對的,不過Excel真的蠻聰明的,它會主動幫我們做跳號的動作,厲害吧?

excel_step_combo-6.jpg

而由於我們也將「標題」列也設定「資料驗證」了,因此會有底下這個「來源 目前評估為錯誤。您要繼續嗎?」的提示對話盒出現,這個地方不用管它,直接點擊〔是〕即可。

excel_step_combo-7.jpg

Step 5. 最後再來到我們輸入資料的表單中,你就會發現到「品名」這欄位所有的儲存格,已經能根據該列的「分類」來產生出不同的「外拉式選單」了。

excel_step_combo-8.jpg

如果您要設定「三層」的下拉式選單,第三層的作法也和第二層是一樣的。

Page 2 of 8

Powered by WordPress & Theme by Anders Norén