Example 07製作樞紐分析表

學習目標 建立樞紐分析表 產生樞紐分析表的資料 樞紐分析表的使用 交叉分析篩選器的使用 調整樞紐分析表 分頁顯示報表 套用樞紐分析表樣式 製作樞紐分析圖

建立樞紐分析表

建立樞紐分析表 當我們運用Excel輸入了許多流水帳的資料， 是很難從這些資料中，一下子就分析出這些資 料所代表的意義。所以Excel提供了一個資料 分析的利器─「樞紐分析表」。 「樞紐分析表」是一種可以量身訂作的表格， 我們只需拖曳幾個欄位，就能夠將大筆的資料 自動分類，同時顯示分類後的小計資訊，而它 根據各種不同的需求，能隨便改變欄位位置， 進而即時顯示出不同的訊息。

建立樞紐分析表 點取工作表中的任一個儲存格，再點選「插入 →表格→樞紐分析表」指令按鈕，於選單中選 擇「樞紐分析表」，進行設定的動作。

建立樞紐分析表 Excel就會自動新增一個「工作表1」，並於工 作表中顯示一個樞紐分析表的提示，而在工作 表的右邊則會有「樞紐分析表欄位清單」。

產生樞紐分析表的資料 加入欄位 移除欄位

加入欄位 報表篩選：限制下方的欄位只能顯示指定資 料。 列標籤：用來將資料分類的項目。 欄標籤：用來將資料分類的項目。 Σ值：用來放置要被分析的資料，也就是直欄 與橫列項目相交所對應的資料，通常是數值資 料。

加入欄位 選取樞紐分析表欄位清單中的「門市」欄位， 將它拖曳到「報表篩選」區域中。

加入欄位 從樞紐分析表中可以看出各廠牌產品的銷售數 量及業績。

移除欄位 若要移除某個欄位時， 直接在欄位上按一下滑 鼠左鍵， 於選單中選擇「移除欄位」，即可將 欄位從區域中移除，而此欄位的資料也會從工 作表中消失。

樞紐分析表的使用 隱藏明細資料 隱藏所有明細資料 資料的篩選 設定標籤群組 更新樞紐分析表

隱藏明細資料 「樞紐分析表」對於資料的分析很有幫助，但 有時分析表中過多的欄位反而使人無所適從， 因此必須適時隱藏暫時不必要出現的欄位。 例如：樞紐分析表詳細列出各個廠牌中所有型 號的銷售資料。假若現在只想察看各廠牌間的 銷售差異，那麼其下所細分的各家「型號」資 料反而就不是分析重點了。 此時，應該將有關「型號」的明細資料暫時隱 藏起來，只檢視「廠牌」標籤的資料。

隱藏明細資料

隱藏所有明細資料 因為各家品牌眾多，如果要一個一個設定隱 藏，恐怕要花上一點時間。還好Excel提供了 「一次搞定」的功能，如果想要一次隱藏所有 「廠牌」明細資料的話。 將作用儲存格移至廠牌欄位中，再點選「樞紐 分析表工具→選項→作用中欄位→摺疊整個欄 位」指令按鈕，點選後，所有的型號資料都隱 藏起來了。

隱藏所有明細資料

資料的篩選 樞紐分析表中的每個欄位旁邊都有「選單 鈕」，它是用來設定篩選項目的。當按下任何 一個欄位的「選單鈕」，從選單中選擇想要顯 示的資料項目，即可完成篩選的動作。

資料的篩選

設定標籤群組 在目前的樞紐分析表中，將一整年的銷售明細 逐日列出，但這對資料分析並無任何助益。 若要看出時間軸與銷售情況的影響，可以將較 瑣碎的列標籤設定群組，例如：將「銷售日 期」分成以每一「季」或每一「月」分組，以 呈現資料之中所隱藏的意義。 選取「銷售日期」欄位，點選「樞紐分析表工 具→選項→群組→群組欄位」指令按鈕，開啟 「數列群組」對話方塊，進行設定。

設定標籤群組

設定標籤群組 回到工作表中， 原先逐日列出的「銷售日期」 便改以「季」與「月份」呈現了。

更新樞紐分析表 當來源資料有更新時，請點選「樞紐分析表工 具→選項→資料→重新整理」指令按鈕，或按 下「Alt+F5」快速鍵。 若要全部更新的話，按下「重新整理」指令按 鈕的下半部按鈕，於選單中點選「全部重新整 理」，或按下「Ctrl+Alt+F5」快速鍵，即可 更新樞紐分析表內的資料。

更新樞紐分析表

交叉分析篩選器的使用 插入交叉分析篩選器 刪除交叉分析篩選器 美化交叉分析篩選器

插入交叉分析篩選器 使用「交叉分析篩選器」可以將樞紐分析表內 的資料做更進一步的交叉分析，例如： 想要知道「台北」門市「Canon」廠牌的銷售數 量及銷售金額為何？ 想要知道「台北」門市「Canon」及「SONY」 廠牌在「第三季」的銷售數量及銷售金額為何？ 此時，便可使用「交叉分析篩選器」來快速統 計出我們想要的資料。

插入交叉分析篩選器 點選「樞紐分析表工具→選項→排序與篩選→ 插入交叉分析篩選器」指令按鈕，開啟「插入 交叉分析篩選器」對話方塊。 選擇要分析的欄位，這裡請勾選「門市」、 「廠牌」及「季」等欄位。勾選好後按下「確 定」按鈕。

插入交叉分析篩選器

插入交叉分析篩選器 回到工作表後，便會出現我們所選擇的交叉分 析篩選器。

插入交叉分析篩選器 交叉分析篩選器加入後，將滑鼠游標移至篩選 器上，按下滑鼠左鍵不放並拖曳滑鼠，即可調 整篩選器的位置。

插入交叉分析篩選器 將滑鼠游標移至篩選器的邊框上，按下滑鼠左 鍵不放並拖曳滑鼠，即可調整篩選器的大小。

插入交叉分析篩選器 想要知道「台北門市Canon廠牌的銷售數量及 銷售金額為何？」。只要在「門市」篩選器上 點選「台北」；在「廠牌」篩選器上點選 「Canon」，即可馬上看到分析結果。

插入交叉分析篩選器 想要知道「台北門市Canon及SONY廠牌在第 三季的銷售數量及銷售金額為何？」。 只要在「門市」篩選器上點選「台北」。 在「季」篩選器上點選「第三季」，即可馬上 看到分析結果。

插入交叉分析篩選器

刪除交叉分析篩選器 點選交叉分析篩選器後，再按下鍵盤上的 「Delete」鍵，即可刪除；或是在交叉分析篩 選器上，按下滑鼠右鍵，於選單中點選「移 除」選項，即可刪除。

美化交叉分析篩選器 在「交叉分析篩選器工具→選項」功能中，可 以進行樣式、排列、大小等美化工作。 更換樣式 選取要更換樣式的交叉分析篩選器，在「交叉 分析篩選器工具→選項→交叉分析篩選器樣 式」群組中，即可選擇要套用的樣式。

美化交叉分析篩選器

美化交叉分析篩選器 欄位數設定 選取要設定的交叉分析篩選器，在「交叉分析 篩選器工具→選項→按鈕→欄」指令按鈕中， 輸入要設定的欄數，即可調整交叉分析篩選器 的欄位數。

美化交叉分析篩選器

調整樞紐分析表 修改欄位名稱 以百分比顯示資料 改變資料欄位的摘要方式 資料排序 單季銷售小季 設定樞紐分析表選項

修改欄位名稱 當建立樞紐分析表時，樞紐分析內的欄位名稱 是Excel自動命名的，但有時這些命名方式並 不符合需求，所以將修改欄位名稱。 點選「樞紐分析表工具→選項→作用中欄位→ 欄位設定」指令按鈕。 開啟「值欄位設定...」對話方塊，於「自訂名 稱」欄位中輸入「銷售總數」文字，輸入好後 按下「確定」按鈕。

修改欄位名稱

修改欄位名稱 點選「樞紐分析表工具→選項→作用中欄位→ 欄位設定」指令按鈕，按下「數值格式」按 鈕，進行格式的設定。

以百分比顯示資料 雖然原始銷售資料是以數值表示，但在樞紐分 析表中，不僅可以呈現數值，還可以將這些數 值轉換成百分比格式，如此一來，我們就可以 直接看出這些數值所佔的比例了。 點選「樞紐分析表工具→選項→作用中欄位→ 欄位設定」指令按鈕，開啟「值欄位設定...」 對話方塊。點選「值的顯示方式」標籤，在 「值的顯示方式」選單中，選擇「欄總和百分 比」選項，選擇好後按下「確定」按鈕。

以百分比顯示資料

改變資料欄位的摘要方式 點選「樞紐分析表工具→選項→作用中欄位→ 欄位設定」指令按鈕，開啟「值欄位設定」對 話方塊，選擇要計算的類型。

資料排序 在樞紐分析表中，也可以使用「排序」功能， 將資料進行排序的動作，這裡要將各廠牌的數 位相機以年度銷售額由多至少排序。 點選欄標籤的選單鈕，於選單中點選「更多排 序選項」，開啟「排序(廠牌)」對話方塊。

資料排序 點選「遞減(Z到A)方式」，並於選單中選擇 「銷售總額」，我們要依銷售總額來遞減排序 廠牌，選擇好後按下「確定」按鈕。

資料排序 廠牌的分類就會以整年度的銷售額，由多至少 排序。

單季銷售小計 雖然目前的樞紐分析表上已經將銷售日期群組成 每季及每月，但在資料欄位中仍然只顯示各月總 和，並無法計算出每一季的單季總和。若要比對 單季的加總值，就需要另外設定「小計」功能。 先點選列標籤中的「第一季」，再點選「樞紐分 析表工具→選項→作用中欄位→欄位設定」指令 按鈕，開啟「欄位設定」對話方塊。 在「欄位設定」對話方塊中的「小計」選項，選 擇「自訂」。由於要計算各季的加總值，所以點 選「加總」選項，選擇好後按下「確定」按鈕。

單季銷售小計

設定樞紐分析表選項 在樞紐分析表的最右側和最下方，總是會有個 「總計」欄位，這是Excel自動產生的，用來 顯示每一欄和每一列加總的結果。 如果不需要這兩個部分，要如何修改？另外， 樞紐分析表中空白的部分表示沒有資料，不妨 加上一個破折號，表示該欄位沒有資料。 以上這些需求，都可以在樞紐分析表的「樞紐 分析表選項」對話方塊中修改。

設定樞紐分析表選項

設定樞紐分析表選項 點選「樞紐分析表工具→選項→樞紐分析表→ 選項」指令按鈕，於選單中選擇「選項」，開 啟「樞紐分析表選項」對話方塊。

設定樞紐分析表選項 勾選「若為空白 儲存格，顯示」 選項，在欄位裡 輸入「─」，則 沒有資料的欄 位，會顯示一個 「─」破折號。

設定樞紐分析表選項 點選「總計與篩 選」標籤，將 「顯示列的總 計」和「顯示欄 的總計」勾選取 消，設定好後按 下「確定」按 鈕。

設定樞紐分析表選項 回到工作表後，沒資料的儲存格會加上 「─」，而在樞紐分析表中的最右邊和最下面 就不會出現總計資訊。

分頁顯示報表

分頁顯示報表 利用報表篩選欄位指定下方資料的範圍時，一 次只能選擇一個項目，但如果使用「顯示報表 篩選頁面」功能，就可以將每個項目的資料， 顯示在不同的工作表上。 點選「門市」欄位，再點選「樞紐分析表工具 →選項→樞紐分析表→選項」指令按鈕，於選 單中選擇「顯示報表篩選頁面」。 在「顯示報表篩選頁面」對話方塊中，選擇要 使用的欄位。本例剛好只有一個，點選「門 市」，選擇好之後，按下「確定」按鈕。

分頁顯示報表

分頁顯示報表 可以看到每一家門市的銷售紀錄都分別呈現在 新的工作表中，而原先的樞紐分析表並不會因 此受到影響。

套用樞紐分析表樣式

套用樞紐分析表樣式 Excel提供了樞紐分析表樣式，讓我們可以直 接套用於樞紐分析表中，而不必自行設定樞紐 分析表的格式。 點選「樞紐分析表工具→設計」索引標籤，在 「樞紐分析表樣式」群組中就有許多不同的樣 式，直接點選想要使用的樣式即可。

套用樞紐分析表樣式

套用樞紐分析表樣式

製作樞紐分析圖

製作樞紐分析圖 將樞紐分析表的概念延伸，使用拖曳欄位的方 式，也可以產生樞紐分析圖。要建立樞紐分析 圖時，可以使用以下的方法。 點選「樞紐分析表工具→選項→工具→樞紐分 析圖」指令按鈕，開啟「插入圖表」對話方 塊，選擇要使用的圖表類型。 選擇好後按下「確定」按鈕，在工作表中就會 產生樞紐分析圖。

製作樞紐分析圖

製作樞紐分析圖

製作樞紐分析圖 點選「樞紐分析圖工具→設計→位置→移動圖 表」指令按鈕，開啟「移動圖表」對話方塊， 點選「新工作表」，並將工作表命名為「樞紐 分析圖」，設定好後按下「確定」按鈕，即可 將樞紐分析圖移至新的工作表中。

製作樞紐分析圖

製作樞紐分析圖 按下「廠牌」欄位選單鈕，於選單中選擇 Canon、Nikon及SONY三個廠牌的每月銷售 紀錄，選擇好後按下「確定」按鈕。

製作樞紐分析圖 圖表就只會顯示Canon、Nikon及SONY三個 廠牌的每月銷售紀錄。

製作樞紐分析圖 樞紐分析圖就如同樞紐分析表的概念與操作方 式。

製作樞紐分析圖 在圖表中顯示了各種欄位按鈕， 若要隱藏這些 欄位按鈕時， 可以點選「樞紐分析圖工具→分 析→顯示/隱藏→欄位按鈕」指令按鈕，即可 將圖表中的欄位按鈕全部隱藏；或是按下選單 鈕，選擇要隱藏或顯示的欄位按鈕。

製作樞紐分析圖 點選「樞紐分析圖工具→設計」索引標籤中的 功能，可以設定變更圖表類型、設定圖表的版 面配置、更換圖表的樣式等。