Excel樞紐分析表(PivotTable)概念介紹

Excel樞紐分析表(PivotTable)是進階使用者的必備技能，只要清楚樞紐分析表的概念，就會發現它其實很簡單及功能強大。本篇文章將從Pivot一詞之解釋，逐步帶入Excel樞紐分析表的概念及SQL、Power Query裡Pivot與Unpivot之資料處理程序。

什麼是Pivot？ Excel樞紐分析表(PivotTable)概念介紹 1.使用目的：資料分析及製作儀錶板 2.樞紐分析的視角：類別與深度 3.樞紐分析摘要值的數據與方式 Power Query的Pivot與Unpivot功能簡介 總結與補充

什麼是Pivot？

Pivot的中文翻譯：名詞是樞紐、樞軸、支點；動詞為轉動、在樞軸上轉動。

Pivot一詞於業界資料處理領域裡，不管是前端、後端皆有使用到此名詞。

前端Excel軟體中設有樞紐分析表(PivotTable)功能，讓用戶可將資料欄位放置於列(Rows)或欄(Columns)，及將要分析的數據置於值(Values)，快速地進行資料總結，Excel稱為摘要值(Summarize)；

後端資料庫SQL設有Pivot及Unpivot的語法，可用來轉換資料表的檢視方式為(列->欄，或欄->列)，或是利用Pivot及Unpivot功能去進行其他資料處理程序。另Excel中的Power Query編輯器亦設有Pivot及Unpivot的功能，用法如同SQL(Power Query功能介面如下圖)。

Excel樞紐分析表(PivotTable)概念介紹：

Excel樞紐分析表是一個很強大的資料分析功能，但依小編觀察於業界中使用頻率並不太高，大多為財務、會計部門才會使用，且都僅使用基本功能。Excel樞紐分析表的設計介面可讓使用者以拖拉置入(Drag and Drop)的方式，快速地進行不同分析視角下的數據計算。(如下動圖)

此段介紹將不會敘述Excel樞紐分析表的操作流程，而是從現實中辦公室資料分析的情境為出法點，帶著大家逐步了解Excel樞紐分析表中資料分析視角與數據摘要值的概念。

1.使用目的：資料分析及製作儀錶板

Excel樞紐分析表使用目的主為進行資料分析及製作儀錶板(Dashboard)，資料分析一詞聽起來有點籠統，小編利用以下場景對話來告訴讀者現實中是以什麼出發點來開始分析資料的。

假設一個場景，某一天辦公室主管把下屬叫去他的辦公室…

主管說：「阿勇，請幫忙分析一下這個資料。」

下屬回話：「請問是分析哪一個資料表呢？」

主管說：「是銷售資料表。」

下屬回話：「那請問要分析什麼數據呢？」

主管說：「請分析2022年的各產品的總銷售量。」

下屬回話：「收到，馬上進行分析。」

2.樞紐分析的視角：類別與深度

在切入樞紐分析的視角主題之前，先讓我們從上段對話中來搞清楚什麼是資料表、什麼是資料分析的視角、什麼是摘要值的數據及摘要值的方式。因為在做資料分析前若不清楚這幾個項目，那恐會造成資料分析方向錯誤。

資料表：銷售資料表

分析的視角：時間(2022年)及產品種類

摘要值的數據：產品銷售量

摘要值的方式：加總(SUM)

樞紐分析的視角類別的選擇即為資料分析者想要以哪一種方式來看待資料表的數據，例如同一個銷售資料表的銷售量數據，業務部門可能會以客戶(Customer)的角度來分析資料、生產部門可能會以產品種類(Product Category)的角度來分析資料。(如下兩圖)

樞紐分析的視角類別與深度其實是一體兩面，一個視角類別於資料表中就代表著一個階層深度(如同文章的大綱階層一般)，以下用時間視角來說明其各視角類別的階層(深度)：

階層一(Level 1)：年(Year)，深度最淺

階層二(Level 2)：季(Quarter)

階層三(Level 3)：月(Month)

階層四(Level 4)：日(Day)，深度最深

範例如下表：

從上表範例中可看出年小計>季小計>月小計，係因越上層(深度淺)的視角所看的資料範圍越廣，理所當然地加總值會越大，另最底層(深度深)的視角往往就是原始數據(Raw Data)的資料。

於樞紐分析中的視角深度選擇會取決於使用者的需求，來選取適當的視角深度來進行資料分析，舉另一個公司組織中各層級主管看待損益的例子來說明：公司總經理會以公司的視角來看待損益，部門主管則以部門的視角來看待損益，課主管則以課的視角來看待損益。

如上所述，樞紐分析的視角選擇會影響到數據摘要值的小計值，但不會影響數據摘要值的合計值，以下三張圖分別以產品種類視角(Product Category)、客戶視角(Customer)、時間視角(Year)來分析同一個銷售資料表，大家可以發現在銷售量合計欄位(Grand Total)中，每個視角的數據皆是相同的(1,614,275,882)。

另外，於Excel樞紐分析表中提供了2個方式來呈現分析視角：列(Rows)與欄(Columns)。

視角的選擇(列或欄)沒有絕對，但若要進行不同視角下數據的交叉比對，一般會使用列與欄兩個視角來呈現數據。

下圖小編利用列與欄的視角來呈現產品種類與時間的銷售量加總，此方式可較下下圖僅用列的視角呈現數據的方式更讓人易於進行數據的解讀與比較。

下圖以列與欄的視角來呈現數據。

下圖僅以列的視角呈現數據，較難進行不同視角下數據的交叉比對。

3.樞紐分析摘要值的數據與方式

樞紐分析摘要值(Summarize)的數據與摘要值的方式係為你想要傳達資料表中哪一個項目的數據給你的聽眾，以及此數據要進行什麼樣的摘要值，如上述範例中，摘要值數據項目為銷售量，摘要值的方式為加總(SUM)。

摘要值的方式還包含了平均值(Average)、最大值(MAX)、最小值(MIN)、計數(Count)等等。

一般摘要值的數據大多為數字，若將文字數據進行摘要值，Excel會自動將摘要值方式改為計數(Count)如下圖。

Power Query的Pivot與Unpivot功能簡介：

Power Query為一數據處理(準備)的編輯器，運用於Excel、Power BI及雲端軟體中，尚未了解的讀者可先參考先前的文章：Power Query入門介紹

於Power Query中具備著Pivot及UnPivot的數據處理(準備)程序，其功能目的包含：
(1)同Excel樞紐分析表一般，創造出兩種資料分析視角給使用者，進行進一步的資料分析與數據比對。
(2)為了轉換資料表的組成結構，以利進行進一步的資料處裡(準備)程序，例如若某資料來源不具備資料庫型態，而是樞紐分析表型態，則可利用Power Query將此資料來源進行Unpivot轉換成資料庫型態資料表。

資料庫型態資料表的組成大致說明如下，詳細將另寫文章介紹。

1. 資料是以資料列為單位進行排列儲存
2. 只有一個表頭(Header)
3. 資料欄與欄、列與列之間沒有空行
4. 沒有合併儲存格(若資料來源於Excel中)

以下將舉一個簡單的範例給各位參考，某工廠生管人員以Excel自製了一個生產數據資料庫，為了方便檢視及記錄，他以樞紐分析表的型態製作(有兩個視角，列的視角為日期，欄的視角為生產數據項目)。

因Excel樞紐分析表無法直接以此種資料來源進行數據摘要值，故可利用Power Query的Unpivot功能將其轉換為資料庫型態的資料表，Power Query操作畫面如下圖。

經轉換後已變為資料庫型態的資料表(如下圖)。

同樣的，Power Query中也有Pivot的功能，可將資料庫型態資料表轉換為樞紐分析表型態，Power Query操作畫面如下圖。

經轉換後已變為樞紐分析表型態的資料表(如下圖)。

總結及補充：

要學好Excel樞紐分析，需先搞清楚你要分析哪一個資料表、以哪一個視角分析、分析哪一個數據、及進行什麼樣的摘要值；以不同的視角來分析資料表的同一個數據只會影響到小計(Total)，不會影響到合計(Grand Total)。

讀完本篇文章，相信大家已對Excel樞紐分析表的概念清楚不少，但可能很多人還是對於「樞紐分析表是一個很強大的資料分析功能」打個問號，故小編最後再補充一下：
Excel樞紐分析表有別於一般的Excel儲存格，需手工設定公式及有易於被修改數據的風險，樞紐分析表可將資料來源做整體的資料分析，以不同的分析視角進行數據的摘要值，且沒有被刪除數據的風險(除非是移除整個分析視角欄位)，較安全、公式統一及計算快速，及具備了互動式出表的特點。

如果各位已了解Excel樞紐分析表的原理及基本操作，其實就可以開始學習撰寫量值(Measures，即DAX語言)，小編有寫了一篇文章詳細介紹DAX語言，大家看懂後，會對於樞紐分析表的觀念加強不少，請參考連結：DAX語言入門介紹。

另進階使用者可以開始學習Excel Power Pivot(資料模型)及Power BI，它們皆是從樞紐分析表的原理去進行開發出來的功能及軟體。對於Excel資料模型還不了解的讀者可以先參考之前的文章：Excel資料模型入門介紹。