Appendix 4. Excel 資料透視表

4.1 資料透視表分析流程

完成資料透視表的各步驟如下：

1. 插入資料透視表：點選「插入 ⭢ 樞紐分析表」便能進入以下視窗，上半部選擇資料範圍、下半部選擇樞紐分析表放置的位置。此外也可以直接選擇「插入 ⭢ 建議的樞紐分析表」，其功能類比 Google Sheets 編輯器中的「建議使用」。
   
2. 選取要分析的欄位、值以及其他細節：Excel 的介面與 Google Sheets 相似，如下圖，右方的視窗稱為「欄位清單」。在點選樞紐分析表任一儲存格時，上方工具列會出現「樞紐分析表分析」與「設計」，可在各項工具中設定樞紐分析表的各項細節。以下針對幾個「樞紐分析表分析」中實用的功能 do 介紹：
   • 展開 / 摺疊欄位：在多層的欄 / 列中可以用來一次展開 / 摺疊。
     
   • +/− 按鈕：在樞紐分析表中可以展開 / 摺疊欄位用，若沒有 +/− 按鈕則可在樞紐分析表的列點兩下便能展開 / 摺疊欄位。
     
   • 將選取項目組成群組：選擇想要組成群組的項目後點選，可另外增加一層該欄位的列，但群組中的各項目要是同一個欄位，例如「新北市 & 台北市」可以組成群組「雙北」、但「信義區 & 新北市」因為層級不一樣所以不能組成群組。
     

   而在「設計」中，主要是針對樞紐分析表的外觀進行設定，包括小計、總計、報表版面配置、空白資料列等，以及儲存格顏色等設定，如下圖。各項功能皆非常直觀，因此在此不贅述，請大家自行研究。
   

3. 此外可以點選任一一一個值並點選「樞紐分析表選項...」進行其他細節調整，後續小節會說明部分內容的內容。
   
4. 查看分析結果、明細：與 Google Sheets 相同，點兩下任何一個值的結果，便能查看該資料的所有組成明細。

4.2 資料透視表分析方式 — 基本篇

本節將介紹以下各功能在 Excel 的用法差異：

• 將欄位放到各項分析方式中：一律將欄位拖曳到視窗中即可，若要刪除則可使用以下方法移除：
  • 在欄位清單上半部取消勾選。
  • 將欄位按住並移出欄位清單下半部，或移到其他欄 / 列 / 篩選器 / 個值。
  • 在欄位清單下半部在特定欄位按右鍵並點選「移除欄位」。

• 根據特定的欄位進行排序：在樞紐分析表最左上角有篩選與排序的箭頭，點選後便能根據指定欄位排序與篩選，如下圖
  
• 選擇不同的彙總依據：可以使用以下兩種方式進入「欄位設定」視窗
  • 在欄位清單下半部在特定欄位按右鍵並點選「欄位設定」。
  • 在樞紐分析表的欄位中按右鍵點選「值欄位設定...」。

基本上接下來說明的各項用法也都能直接在樞紐分析表中點選欄 / 列設定，因此以下不贅述。而進入欄位設定視窗後如下圖，可設定欄位名稱、數字格式 (數字...)、彙總方式 (摘要方式:)、顯示方式 (顯示資料為)，其中顯示方式可選擇不同的百分比、也能選擇與某個列的工作表中的資料進行合併，以進行下一步的分析。

• 多個列 / 欄的排序方式與操作：基本上作法與 Google Sheets 相同。
• 使用篩選器初步過濾部分資料：與 Google Sheets 一樣在欄位清單中篩選，但 Excel 樞紐分析表在篩選後會樞紐分析表上方出現該篩選器，因此報表使用者可自由篩選，也能看出目前的資料包含那些篩選器的資料，這點是目前 Google Sheets 無法直接顯示的部分。
  

  ---

4.3 資料透視表分析方式 — 進階篇

本節將介紹以下各功能在 Excel 的用法差異：

• 建立日期群組 (日期資料)：將日期欄位放到列 / 欄時會自動建立具有 +/− 符號的日期群組，如下圖，可將日期群組或日期從列中移除。
  

而若要更改日期群組的顆粒度，可以在樞紐分析表的任一列按右鍵點選「群組...」，會進入下圖的視窗，可在在此調整起迄時間、日期顆粒度、天數等設定選項，其中日期顆粒度可以按住 ⌘ / Ctrl 選取多個顆粒度 。

• 建立元素分組規則 (數字資料)：和日期資料相同，在樞紐分析表的任一列按右鍵點選「群組...」會進入下圖的視窗，可調整開始、結束點以及間距值。
  
• 在「值」中使用計算結果欄位：點選「樞紐分析表分析 ⭢ 欄位、項目、合集 ⭢ 計算欄位」便可插入欄位，如下圖：
  
  若要查看所有自訂欄位的公式，可點選「樞紐分析表分析 ⭢ 欄位、項目、合集 ⭢ 顯示公式」，Excel 會自動新增一張工作表並放入所有計算欄位及其求解順序，如下圖。而目前無法將彙總依據選擇為「自訂」，因此無法使用公式「=SUMPRODUCT('Unit Price', Quantity)」，建議在原始資料中先新增一個欄位計算結果，再將該欄位納入樞紐分析表中計算。
  

4.4 篩選器控制項

在 Excel 中篩選器控制項稱為「交叉分析篩選器 / 時間表」，可點選「樞紐分析表分析 ⭢ 插入交叉分析篩選器 / 插入時間表」 ⭢ 選擇要交叉分析的欄位即可。如下圖，Product line / Date 分別為交叉分析篩選器與時間表。

若點選交叉分析篩選器 / 時間表，上方工具列會出現「交叉分析篩選器 / 時間表」，可在該工具列中調整相關的細節，例如「報表連線」可以選擇要適用於幾張樞紐分析表、選擇格式、在工作表中的圖層順序等，操作相對直觀，因此在此不一一贅述，各位使用者可自行嘗試。此外，兩者會同步連動樞紐分析表的篩選器與交叉分析篩選器若有同一個欄位的篩選器時，兩者會同步連動。

而交叉分析篩選器除了在資料透視表中使用外，也可以用於「表格」中 (範圍不行)，因此要在 Raw Data 中使用交叉分析篩選器，只要先照 Appendix 2.3 說明的「常用 ⭢ 格式化為表格」轉為表格即可插入，如下圖：

4.5 使用 GETPIVOTDATA 取得資料透視表之分析結果

使用方式與 Google Sheets 完全相同，且可以使用快捷的方式生成公式，只要在空白的儲存格輸入「=」並點選樞紐分析表的其中一個值，Excel 會自動使用 GETPIVOTDATA 而非使用「= 特定儲存格」輸出值，如下圖，因此值會隨著樞紐分析表的值更迭而更迭。