透視表進階（下）：儀表板數字跑掉、新資料沒進來的原因

你做了一張儀表板，公式直接點透視表的儲存格，像是 =透視表!B2。隔天同事把透視表換了排序，儀表板上寫著「台中」的那格，數字變成別的城市的，而且沒有任何錯誤訊息。這一篇講透視表接到儀表板時會踩到的幾個坑，從這個最常見的開始。

透視表的建立和欄位拖拉，在 資料透視表：3 秒完成多維度交叉分析，這邊沿用同一份 6 筆銷售資料。

---

直接點儲存格，排序後就錯了

先重現一次。我把透視表放在名為「透視表」的工作表，列放「城市」、值放「銷售額」的 SUM，並設定依銷售額由小到大排序：

儀表板的「台中」旁邊寫 =透視表!B2，正確地顯示 73000。這時有人把排序改成由大到小，透視表變成高雄、台北、台中，B2 變成高雄的 95000。儀表板上還是寫著「台中」，數字卻是 95000。

原因很單純：公式記的是「第 2 列第 B 欄」這個位置，跟「台中」兩個字沒有關係。透視表只要排序、篩選、多加一個欄位，位置就會動。

改用 GETPIVOTDATA

GETPIVOTDATA 不看位置，看名稱：

• 第 1 個參數：指定要取的值欄位，本例只有一個值欄位，用原始欄名「銷售額」即可；有多個值欄位時，要用表上顯示的完整名稱（例如「銷售額的SUM」）。若自行改過名稱，請先用一個已知小計核對。
• 第 2 個參數：透視表裡任何一格，我習慣點左上角的 $A$1。
• 第 3、4 個參數：一組「維度名稱、條件值」。

這個公式不管台中跑到第幾列，都回 73000。

要同時指定兩個維度，就多給一組。前提是「產品」也有放進透視表的列或欄：

結果是 45000。只給一組維度，拿到的是該維度的小計，例如只指定城市「台北」是 74000（A產品 45000 加 B產品 29000）。

要注意，在儀表板的儲存格輸入 =，再直接點透視表裡的數字，實機得到的是一般參照（像 ='透視表'!B2），不會自動產生 GETPIVOTDATA，這條公式要自己打。

條件換成儲存格，就能搭配下拉選單：

E1 選「高雄」，結果是 95000。

---

組合不存在時回 #REF!

如果有人在透視表加了「通路」篩選器、只留「門市」，台中沒有任何門市交易，台中那列就從透視表消失。這時：

會得到 #REF!。維度名稱打錯、選單選到不存在的組合，也是同樣的錯誤。

儀表板裡我會全部包一層 IFERROR，查不到時顯示 0：

篩選器有勾選的話，0 不一定代表真的沒有銷售，只是被篩掉了，這點要自己記得。

---

固定項目的圖表指向提取區

如果圖表需要固定顯示特定項目，直接選透視表當資料範圍，排序一換，項目位置也會跟著動。我的做法是中間加一層：

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

圖：圖表只看提取區，透視表怎麼動都不影響。

提取區是儀表板工作表上一塊固定的小表，每格都是 GETPIVOTDATA。以產品為例（透視表的列要放「產品」），A2 寫「A產品」、A3 寫「B產品」，B2 是：

選取這塊（含標題）插入圖表，資料範圍就是提取區。

圖：圖表的資料範圍是提取區，不是透視表。

想再加一個月份下拉選單，原始資料要先有「月份」欄。我在 F1 填「月份」，F 欄用 =MONTH(A2) 往下填（儲存格格式要設成數字，否則會沿用日期格式，顯示成奇怪的日期），資料範圍改成 A1:F7，透視表的欄放「月份」。J1 用「資料」>「資料驗證」做成 1、2 的下拉選單，提取區的公式變成：

J1 選 1，A產品 103000、B產品 32000；選 2，A產品 41000、B產品 66000。圖表跟著切換，不用動它。下拉選單的做法在 資料驗證與資料品質。

---

新增的資料沒有進透視表

常見的狀況是：新增了一列，透視表沒有變。

先說一件事：Google 試算表的透視表會自己更新，沒有「重新整理」的按鈕，右鍵也沒有。原始資料格子內容一改，透視表就跟著動。

沒有動的原因，幾乎都是資料範圍。建立時選的是 A1:E7，之後在第 8 列新增：

這一列在 A1:E7 外面，透視表看不到。台北還是 74000，總和還是 242000。

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

圖：把資料範圍的結尾列號拿掉，改成 A1:E。

修法是打開編輯器，把資料範圍從 A1:E7 改成 A1:E。之後新增的列都會進來，台北變成 86400，總和 254400。

要注意，A1:E 會吃到 E 欄往下的所有東西。你如果在資料下面放了備註或合計，它們也會被算進去，所以資料下方要保持乾淨。

另一個做法是把原始資料轉成表格：選取資料，點「格式」>「轉換為表格」。快捷鍵在 Windows 是 Ctrl+Alt+T、Mac 是 ⌘+選項+T（在 Chrome）。表格會隨新增的列自動長大，透視表的資料範圍如果選這張表，就不用自己改範圍。

Ctrl+T 不是轉成表格的快捷鍵，在 Chrome 那是開新分頁，不要按錯了。

---

數字靠左，日期不能分組

這兩個問題長得很像，原因都是資料其實是文字。

銷售額如果是文字，會靠左對齊，透視表的 SUM 會是 0（文字不會被加總）。日期如果是文字，右鍵不會出現「建立資料透視表日期群組」。

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

圖：左邊的數字靠左，被當成文字；右邊靠右，才是數字。

假設 B2:B4 是文字的 45000、32000、58000，用 VALUE 轉成數字：

結果是 45000、32000、58000。日期同理，假設 A2:A4 是文字的 2024/1/15、2024/1/16、2024/1/18，用 DATEVALUE 轉成日期：

轉出來的是日期序號，把儲存格格式設成日期才看得懂。轉好之後，把結果複製、以「只貼上值」貼回原欄（Windows 是 Ctrl+Shift+V，Mac 是 ⌘+Shift+V，在 Chrome），透視表會自己更新。

---

透視表變慢的時候

大量透視表放在同一個工作表，可能讓試算表變慢。我沒有量過具體數字，所以不給門檻，只講我的處理順序：

1. 先看有沒有重複的透視表，同一份資料能用一個透視表加幾組 GETPIVOTDATA 取不同的值，就不要多建一個。
2. 計算欄位裡的複雜算式，我會改成在原始資料多加一欄輔助欄。原始資料的格子好檢查，也不用受計算欄位的限制。
3. 資料量真的大、而且資料每天新增，我會改用 QUERY 取代透視表，在 QUERY 資料分析實戰 有講怎麼換。

---

Slicer 讓透視表與圖表一起篩

切片器（Slicer）在繁體中文介面叫「篩選器控制項」。想讓看報表的人自己選城市，選取來源範圍，點「資料」>「新增篩選器控制項」，再到右側指定資料範圍與資料欄，確認「套用到資料透視表」已勾選。

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

圖：控制項設定的是原始資料範圍，不是透視表的輸出範圍。

控制項可以直接套資料範圍，也能影響同一張工作表中使用相同資料集的透視表與圖表。如果要同時篩城市與通路，就各建一個，兩個控制項的來源範圍要一致，結果會同時符合這兩個條件。

我在 互動式報表的 Slicer 範例 用門市銷量做了完整對照：全部 527 杯，選信義變 237，再選店內變 203，接在透視表上的圖表也跟著變。直接讀原始資料的 SUM 與 QUERY 則仍然是 527，控制項不會替公式加條件。

上面用提取區的做法，適合圖表需要固定項目與位置；這一節要讓圖表呈現目前透視表篩出的項目，就可以直接接透視表，並排除總計列。不要把兩種圖表來源混在一起，以為控制項會改寫提取區裡的公式條件。

另外，來源資料被篩掉時會隱藏整列，透視表若剛好放在右邊同一列，也可能看不見。我會把練習報表放到來源範圍下方，並確認報表沒有被包含在來源內。新增資料後沒變，先一起核對透視表與所有控制項的資料範圍。

依 Google 官方說明，一般篩選選擇是私人的；要讓共用對象使用同一個初始畫面，設好條件後，在控制項的「更多」選單選「將目前的篩選器設為預設值」。完整操作與兩個控制項的結果表，見 互動式報表。

---

動手練習

---

下一步可以去看 Google 試算表圖表完全指南，把提取區做成好看的圖表。

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