資料透視表：3 秒完成多維度交叉分析

有人問你「各城市各產品的銷售額是多少」，而你手上是一張流水帳。用 SUMIFS 的話，3 個城市乘上 2 種產品就要寫 6 個公式，再多一個維度就再翻倍。今天來看資料透視表（Pivot Table），拖幾下就能把這張表轉成交叉分析表。

這篇專門講操作：選單在哪、編輯器的四個區域怎麼放、值怎麼算。用 GETPIVOTDATA 做儀表板的部分，放在 透視表進階（下）。

---

先準備一份流水帳

這篇用同一份 6 筆銷售資料，從 A1 貼起：

如果要手動算「台北 A產品」，要在城市欄找台北、再對產品欄找 A產品、最後把銷售額加起來。6 個組合就要來回找 6 次，資料有 1000 列的話想都不用想。

透視表吃的是這種「一列一筆交易」的流水帳。資料裡如果有合併儲存格、手動加的「台北小計」列，這些都會被當成正式資料算進去，數字就錯了，而且不會有任何提示。

---

插入資料透視表

1. 點資料範圍內任一個儲存格，或直接選取 A1:E7。
2. 點「插入」>「資料透視表」。
3. 在跳出的視窗確認資料範圍，再選要放在「新的工作表」還是「現有工作表」。
4. 按「建立」。

圖：建立位置我一律選「新的工作表」，不會蓋到原始資料。

選「現有工作表」的話，可以自己指定從哪一格開始長，不過透視表長高長寬都會往右下擠，旁邊有東西就容易被擋住。我個人幾乎都選新的工作表。

建立後右邊會跳出「資料透視表編輯器」。

---

資料透視表編輯器

編輯器上方有一排「建議」，Google 試算表會依資料特性先猜幾種做法，點下去就直接生成。猜得準不準看資料，我通常會看一眼，然後自己拉。

編輯器的主體是四個區域：

• 列：資料往下長，每個值一列。
• 欄：資料往右長，每個值一欄。
• 值：要計算的數字，預設是加總。
• 篩選器：只留下你勾選的項目，不會出現在表上。

← 左右滑動看完整的圖，點圖可放大

圖：四個欄位各放進一個區域，右邊就是長出來的表。

加欄位有兩種方式：按區域旁邊的「新增」再選欄位，或直接從編輯器上方的欄位清單拖進去。要拿掉的話，按項目右上角的 X。

---

列、欄、值

先把「城市」放進「列」，「銷售額」放進「值」。

圖：列放城市、欄放產品、值放銷售額，就是下面這張交叉表。

只放這兩個，會得到各城市的總銷售額。再把「產品」放進「欄」：

6 個 SUMIFS 的結果，就這樣一次排好。

想知道某一格是怎麼算出來的，在那格上點兩下，會冒出一張新表，列出組成這個數字的原始資料。例如點兩下「台北、A產品」那格，只會看到 1/15 那一筆。

資料往下長的順序可以改：點「列」裡的「城市」，用「排序依據」選「銷售額的 SUM」、順序選「遞減」，城市就會照銷售額由大到小排，高雄 95000、台北 74000、台中 73000。

---

值要怎麼算

「值」裡面每個欄位都有「匯總依據」的下拉選單，預設是 SUM。

圖：同一個欄位換匯總依據，就是換一種算法。

常用的幾個：

• SUM：總和。
• COUNTA：非空白儲存格的個數。
• COUNT：只算數字儲存格的個數。
• COUNTUNIQUE：不重複的項目有幾種。
• AVERAGE：平均。
• MAX、MIN：最大、最小值。
• MEDIAN：中位數。

同一個欄位可以放進「值」好幾次，每次選不同的匯總依據。把「銷售額」放三次，分別選 SUM、COUNT、AVERAGE，列放城市：

「總和」那一列的平均是 242000 ÷ 6，約 40333。

顯示成百分比

如果想看的是佔比，在值欄位的「顯示方式」選「佔總計的百分比」。數字會從 74000 變成 74000 ÷ 242000：

三個加起來是 100%，顯示的小數位數不同時，四捨五入後可能差 0.01。

這邊有件事要先說，Google 試算表的「顯示方式」沒有 Excel 那種「累計百分比」。要做累計，後面帕雷托那一節用輔助欄算。

---

篩選器

只想看「門市」的銷售？不用刪資料，在編輯器的「篩選器」區按「新增」選「通路」，再取消勾選「網路」。

圖：篩選器勾選門市，表上只剩門市那三筆交易。

結果會是：

台中沒有任何門市交易，所以整列不見了。

要注意的是，篩選器的設定不會顯示在表面上。你把報表寄出去，看的人不知道這是「只有門市」的數字。寄出之前我會回編輯器看一下，確認篩選器是你要的樣子。

---

日期分組

這一節先用同一份 A1:E7 新建一張沒有篩選器的透視表，避免沿用剛才只看門市的狀態。若沿用上一張，先移除「通路」篩選器，或重新勾選全部通路；也移除城市、產品與多餘的值欄位，把銷售額的匯總依據設回 SUM、顯示方式設回預設。下表包含門市與網路全部六筆，所以 1 月是 135000、2 月是 107000。

日期欄直接放進「列」，每個不同日期各佔一列，資料多時看不出月份差異，這時再把日期併成月或年。

1. 把「日期」放進「列」，「銷售額」放進「值」。
2. 在透視表上對任一個日期按右鍵，選「建立資料透視表日期群組」，再選「年-月」。

圖：日期欄按右鍵，選分組的單位。

我們的資料只有 1 月和 2 月，分組後是：

選項裡除了年-月，還有只看月、只看季之類的組合。

如果右鍵沒有「建立資料透視表日期群組」，通常是日期被存成文字了。文字的日期會靠左，真正的日期靠右。轉換的方法放在 透視表進階（下） 的除錯那節。

---

帕雷托分析：找出佔 80% 的品項

帕雷托分析是看「哪幾個品項貢獻了大部分業績」，要用累計百分比。前面說過透視表沒有這個選項，我的做法是先用透視表算出各品項的銷售額，把結果複製、以「只貼上值」放到別處，再自己加一欄累計。

假設換成另一份有五個產品的資料，複製出來的結果已經由大到小排好，放在 A1:C6，C 欄是累計百分比：

C2 的公式是：

$B$2 前面的 $ 把起點鎖住，往下複製時，終點 B2 會變成 B3、B4，累計的範圍就一路拉長。儲存格格式要設成百分比、小數 1 位，才會看到 46.7%。

累計百分比第一次超過 80% 的那一列，在這個例子是產品C（88.9%）。也就是前三個品項貢獻了將近九成業績。

圖：長條看大小，折線看累計，折線越過 80% 的位置就是主力品項的界線。

---

動手練習

---

資料透視表只負責算，要拿它的結果做儀表板，GETPIVOTDATA 和資料範圍的幾個陷阱放在 透視表進階（下）。計算欄位和進階分組，則在 透視表進階（上）。

我是喜特先生，Mr. Sheet，我們下個教學見！