透視表進階（上）：計算欄位與進階分組

主管看了各城市的銷售額，接著問：「那利潤率呢？」另外還想看每一季的數字、每筆訂單落在哪個金額帶、北中南各賣多少。這些都不用動原始資料，在資料透視表裡面就能做，靠的是兩個功能：計算欄位，跟分組。

資料透視表怎麼建、四個區域怎麼拖，在 資料透視表：3 秒完成多維度交叉分析 講過了，這篇直接從已經建好的透視表開始。用 GETPIVOTDATA 把結果接到儀表板的部分，在 透視表進階（下）。

---

先把資料補齊

前兩篇用的是 6 筆銷售資料。這篇要算利潤，所以多一欄「成本」，另外要看「季」跟「區域」，我再補了 4～6 月的 6 筆，一共 12 筆，從 A1 貼起：

前 6 列跟前兩篇一模一樣（只多了成本欄），所以 1 到 3 月的銷售額總和還是 242000。後 6 列是新增的，多了新北跟台南兩個城市。

要練習的話，從 A1 貼進一個空白的工作表，再用「插入」>「資料透視表」建一個，後面每一步都對得上。

---

計算欄位

利潤要用「銷售額 − 成本」。如果在原始資料多加一欄 G，每一列寫 =E2-F2，也可以，但那張表本來只記錄一筆一筆的交易，每多一個分析指標就多一欄，我個人不太想這樣養。計算欄位把公式放在透視表裡面，原始資料一個字都不用動。

新增計算欄位

1. 點透視表，打開右邊的資料透視表編輯器。
2. 在「值」旁邊按「新增」，選「計算結果欄位」，開啟「新增計算結果欄位」對話框。
3. 在「計算結果欄位名稱」填「利潤」。
4. 在「公式」欄寫公式。
5. 「匯總依據」保持 SUM，按「新增」。

圖：欄名、公式與匯總依據分開設定。

這個例子以原始欄位名稱引用銷售額與成本，不用儲存格位置。利潤的公式是：

先把「城市」放進列、依銷售額由大到小排好，值放「銷售額」「成本」的 SUM，再加上「利潤」，結果是：

先心算驗證一下台中：120500 − 76900 = 43600，對得上。

利潤率：把分子、分母寫清楚

利潤率是「合計利潤 ÷ 合計銷售額」。用本篇完整 12 筆交易比較兩種設定：

• 公式 =(銷售額-成本)/銷售額，匯總依據選 SUM。
• 下方明確寫 SUM() 的公式，匯總依據選「自訂」。

兩欄的每個城市與總和都相同。匯總依據選 SUM 時，是用加總後的銷售額與成本去算比率，不是把每筆交易的利潤率加起來。

台北是 (107500 − 70100) ÷ 107500，約 34.79%；全表是 158600 ÷ 447500，約 35.44%。表格按前面的銷售額順序整理，實際排列可由列欄位的排序設定調整。

圖：匯總依據選自訂，公式明確寫出加總銷售額與成本。

我仍建議把計算意圖直接寫出來：名稱「利潤率」，匯總依據「自訂」，公式：

這樣交接時比較容易看出分子與分母各自如何加總。

建好後，選取利潤率的結果儲存格，把數字格式設為百分比、小數 2 位，才會把 0.347906… 顯示為 34.79%。格式只改顯示，分母為零仍需另外處理。

計算欄位不能做的事

• 不能引用儲存格：寫 =B2-C2 或 =Sheet1!E2 都不行，只認欄位名稱。
• 不能引用其他計算欄位：利潤率沒辦法寫成 =利潤/銷售額，因為「利潤」本身也是計算欄位，所以上面的利潤率才要從銷售額與成本重算一遍。
• 可以用函式：像 IF 這類基本函式可以放進公式。要避免分母是 0，可以寫成：

原始 12 筆都是正銷售額，防護公式與前兩種寫法的結果一致。如果某個群組的銷售額加總是 0，沒有防護的利潤率公式會顯示 #DIV/0!，IF 防護版本則顯示 0。

這個 0 是本例明訂的顯示選擇，不表示零銷售額時仍有可解讀的利潤率。

---

日期分組：依月、依季

日期直接放在列，每個日期各佔一列，12 筆就 12 列。前一篇用「建立資料透視表日期群組」併成「年-月」，這篇看看月跟季。

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

圖：同一個日期欄，右鍵換不同的分組單位。

分成月之後：

3 月沒有任何交易，所以整列不會出現，圖表如果要補 0，得另外處理。

改看季度時，在日期項目按右鍵，從同一個日期群組選單選「季」，得到：

利潤率那欄是上一節的計算欄位，放在分組後的季上面，公式會用各季加總過的銷售額與成本重算，不用再改。你可以看到 Q2 的銷售額比 Q1 少，利潤率只掉了不到 1 個百分點。

右鍵沒有「建立資料透視表日期群組」，通常是日期被存成文字，轉換方法在 透視表進階（下） 的「數字靠左，日期不能分組」那節。

---

數值分組：把金額切成區間

銷售額如果直接放在列，12 筆交易就是 12 列，看不出「哪個金額帶的訂單最多」。數值分組可以把連續的數字切成等寬的區間。

1. 把「銷售額」放進列，值放「銷售額」的 COUNTA 與 SUM。
2. 在列裡任一個銷售額上按右鍵，選「建立資料透視表元素分組規則」。
3. 在「分組規則」填最小值 20000、最大值 59999、間隔大小 10000，按「確定」。

圖：三格名稱為最小值、最大值與間隔大小。

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

示意圖：12 筆依金額分成四個區間。

分完之後：

四組筆數依序 4、3、3、2，合計 12；銷售額合計仍為 447500。需要改顯示順序時，可以選取 50000 - 59999 的標籤格，從選取框邊緣拖到第一個群組之前，側邊欄排序會顯示「自訂」。只在儲存格內部拖曳可能只是改選取範圍，不是重排。

數值分組只能做等寬的區間。想要「0 到 30000 算小單、30000 到 50000 算中單」這種不等寬，就要在原始資料加一欄，用 IFS 預先判斷，再把那一欄放進列。（IFS 的用法在 IFS 與 SWITCH 實戰。）

---

手動分組：把城市歸成區域

文字沒有「間距」可以用。想把台北、新北歸成「北部」，台中是「中部」，高雄、台南是「南部」，就用手動分組。

1. 把「城市」放進列。
2. Windows 按住 Ctrl，Mac 按住 ⌘，同時點選台北跟新北。
3. 按右鍵，選「建立資料透視表元素群組」。
4. 把產生的群組名稱改成「北部」。
5. 要完成三區時，可再把台中整理為中部、高雄與台南整理為南部。

圖：選好要合併的城市再按右鍵。

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

示意圖：三區的整理方式。

按這個三區分類合併後，各區結果如下：

北部是 (155800 − 102400) ÷ 155800 = 34.27%。這邊也是用整個群組的銷售額與成本重算，不是把台北跟新北的利潤率平均。

選取台北、新北並命名為「北部」後，銷售額 155800、成本 102400、利潤 53400、利潤率約 34.27%。如果從列區移除群組欄位，再從編輯器的「已組成『城市』」加回，台北與新北的北部分組仍會保留，不需要重新選一次。

保留既有群組設定，與替新城市自動歸類是不同問題：不保證新增的城市會自動歸入北部。資料會持續新增城市時，建議在原始資料維護「區域」欄，再把「區域」放進列，讓分類依據可以逐筆檢查。2026 年新功能見透視表新功能。

---

透視表會自己更新

這篇的數字都不用按任何「重新整理」，Google 試算表的透視表沒有那個按鈕。原始資料改了，計算欄位跟分組的結果會跟著變。新增的資料如果沒有進來，要檢查的是資料範圍，在 透視表進階（下） 的「新增的資料沒有進透視表」那節。

---

動手練習

---

分組做完，要把這些數字接到儀表板的話，下一篇是 透視表進階（下）。如果想用函式而不是透視表算同樣的東西，可以看 SUMIFS 多條件加總。

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