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

建立樞紐分析表

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

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

Step 1. 首先,選取「所有資料的欄位」,選取時可以直接選取一整個欄位,連沒有輸入資料的列,也一併選擇進來,接著再點擊「插入\樞紐分析表」如下圖:
1834-05
Step 2. 跳出「建立樞紐分析表」的對話盒後,可以看到我們所選取的欄位定義資料,另外我們也可以選擇要將樞紐分析表放在哪個位置,一般來說,我們把它放在「新工作表」即可,直接點擊〔確定〕吧。
1834-06
Step 3. 接著就是要來設定樞紐分析表所要顯示的項目了,下圖是完全都還沒有設定的樞紐分析表,而設定的方式,就是直接拖拉右邊上面的「選擇要新增到報表的欄位」清單項目,至右邊下面的「列標籤」、「欄標籤」或是「值」這三個地方,因此,無論是要給文具店老闆的報表,或是給會計的報表,起點都是從這一步開始設定起的。
1834-07
Step 4. 接著我將「分類」及「品名」,分別拖曳進下方的「列標籤」及「值」,我們就可以馬上看到左方的工作表,馬上就起了變化,我們可以一直從右方設定面版中,一直的修改欄位設定,直到左方的資料表是我們所需要的為止。
1834-08
從上圖可以看出,這個表格的資料是有問題的,所以我們要一一的來修正一下。

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

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

Step 5. 在右下方,已經被拖曳進去「列標籤」裡的「分類」,點擊右邊的倒三角形小圖形上,並從選單中點選「欄位設定」,如下圖:
1834-09
Step 6. 接著我們將「小計與篩選」頁籤中的「小計」,設定在「無」的地方,最後再點擊〔確定〕即可。
1834-10
之後我們就會看到,這些文具用品分類的小計,都已經消失了,這樣計數是不是清楚多了。
1834-11

以列表方式顯示項目標籤

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

Step 7. 首先,一樣根據上一步的方式,開啟「欄位設定」對話盒,接著切換到「版面配置與列印」,再勾選「以列表方式顯示項目標籤」,最後再點擊〔確定〕,如下圖:
1834-12
而這兩者之間的變化,你可以比對參考一下,上面的圖和下面的圖,你就會知道這兩種顯示項目標籤的格式了。
1834-13

Advertisement

隱藏空白的欄位

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

Step 8. 點擊「列標籤」右邊的按鈕,再從「選取欄位」中,將「(空白)」前面的「打勾」給取消掉,如下圖:
1834-14
經過這樣的設定,空白這個欄位就會消失了。

修改「數量」的顯示方式

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

Step 9. 請在右下角「值」版面中,點擊在「計數-數量」旁的倒三角形圖示,並在選單中點選「欄位設定」,接著在「值欄位設定」的對話盒中,將「摘要方式」頁籤裡的「摘要值欄位方式」改為「加總」,最後再點擊〔確定〕,如下圖:
1834-15
經過加總的設定,數量的數值即可變為「4」,也就是我們真正想要的數字了。

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

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

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

首先,使用同樣的步驟,從Step 1操作到Step 3,接著將「分類」拉進「列標籤」、「部門」拉進「欄標籤」,而在「值」的部份,則拉進「小計」,且將「小計」的彙整方式改為「加總」,如下圖所示,給會計的表格也能輕鬆完成。
1834-17
從上表,一眼就可以看出,到底是哪個部門花最多錢,如果你把「姓名」也拉進「欄標籤」的話,甚至也能統計出,到底誰是請購文具用品的大戶呢,這強大且充滿彈性的分析能力,正是樞紐分析表好用的地方。

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

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

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

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

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

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

發表迴響

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