GETPIVOTDATA 怎麼用：從透視表按欄位名稱取值，版面改了也不跑掉

今天要來聊聊 GETPIVOTDATA，用欄位名稱和項目名稱，從透視表裡取出某一格的數字，不管那一格現在排在第幾列。

透視表怎麼建、四個區域怎麼拖，在 資料透視表：3 秒完成多維度交叉分析 講過了。拿 GETPIVOTDATA 做儀表板、圖表提取區的整套做法，在 透視表進階（下）。這篇專門給直接搜這個函式的你，講引數怎麼對應，還有透視表動了之後公式什麼時候會壞。

想把透視表的數字拿出來用

我手上有 10 筆手搖飲訂單，放在「訂單」工作表的 A1:C11：

我在新的工作表「透視表」做了一張透視表：列放「品項」、欄放「通路」、值放「杯數」的 SUM。結果長這樣：

圖：這篇用的透視表設定。

我想在別的工作表放一格「蘋果紅茶在門市賣了幾杯」。最直接的是輸入 =，再點透視表裡的 66，但這樣得到的只是一般參照，不是 GETPIVOTDATA，差別下面會說。

GETPIVOTDATA

GETPIVOTDATA 會指定一個值欄位，再用「欄位名稱加項目名稱」一組一組縮小範圍，從透視表取出對應的那個數字。

語法

=GETPIVOTDATA(值名稱, 透視表任一格, [原始欄位1, 項目1, ...])

• 值名稱：要取哪個值欄位，用文字寫，要加雙引號。填「杯數」或「杯數的SUM」都取得到；如果透視表的值欄位有改過自訂標題，就只能用自訂標題。
• 透視表任一格：這張透視表裡的任何一格，作用只是告訴函式「是哪一張透視表」，我習慣用左上角，例如 透視表!$A$1。因為如果透視表縮小到你指定的那一格不存在，公式就會出錯，官方說明也建議用左上角。剪下整張透視表移到別的位置時，公式裡的錨點會自動跟著更新；複製或重建新的透視表，舊錨點則不保證仍有效。
• 原始欄位、項目：選填，一組一組往後接。「原始欄位」是原始資料的欄名，像「品項」；「項目」是那個欄位裡的某個值，像「蘋果紅茶」。

點格子不會產生它，要自己打

在要放結果的儲存格輸入 =，再去點透視表的 66（蘋果紅茶、門市那一格），按 Enter，實機得到的是一般參照，像 ='透視表'!C6（工作表名稱是中文，編輯列會自動補單引號），不是 GETPIVOTDATA。GETPIVOTDATA 要自己打，寫法是：

來看看結果：

自己打的時候，值名稱和項目名稱要跟透視表上的一致，寫錯就會出錯（見後面的 #REF!）。公式寫好之後，把「蘋果紅茶」換成儲存格參照，就能接下拉選單。

Excel 使用者要留意：Excel 預設會在你輸入 = 後點透視表的值時，自動產生 GETPIVOTDATA（可以到「PivotTable 分析」>「選項」關掉「產生 GetPivotData」），Sheets 則是得到一般參照。函式的語法與參數順序，兩邊相同。

引數怎麼對應

下圖把公式的每一段，對到透視表上的位置：

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

圖：同顏色的是一組，品項加蘋果紅茶決定列，通路加門市決定欄。

「原始欄位」寫的是原始資料裡的欄名，列和欄一樣用同一種寫法，不用管這個欄位現在放在透視表的列還是欄。

少給幾組就是小計

只給一組，拿到的是那個項目的小計。蘋果紅茶的外送加門市：

48 加 66 是 114，和透視表「總和」欄上的數字一樣。一組都不給，就是整張透視表的總和：

「外送」的小計一樣寫，只是欄位換成「通路」：=GETPIVOTDATA("杯數", 透視表!$A$1, "通路", "外送")，得到 199。

透視表動了，公式還能用

點格子產生的 ='透視表'!C6 這種參照是記位置，GETPIVOTDATA 是記名稱，所以下面這些變動，原本那條 66 的公式都不會變：

• 排序改了：把列依杯數的 SUM 由大到小排，蘋果紅茶跑到別的列，還是 66。
• 欄位換邊了：把「通路」從欄拖到列、放在「品項」底下，版面整個不一樣，還是 66。
• 新增資料列：在「訂單」工作表第 12 列加一筆「桂花烏龍、外送、21」，資料範圍如果是 A1:C，透視表會自己多一列，總和變成 420，蘋果紅茶在門市還是 66。

位置會變、名稱不變，這就是為什麼儀表板我一律用 GETPIVOTDATA，不點格子。為什麼點格子會出事，在 透視表進階（下） 有完整的重現。

什麼時候會 #REF!

名稱對不上的時候，GETPIVOTDATA 會得到 #REF!。常見的有幾種：

• 項目在透視表裡不存在。例如給透視表加了「通路」篩選器、只留「門市」，那 "通路", "外送" 就查不到。
• 欄位根本沒放進透視表。把「通路」從欄拿掉，公式裡還寫 "通路", "門市" 就找不到。
• 打錯字，或原始資料裡的項目改名了，例如「蘋果紅茶」改成「蘋果紅」。
• 兩個項目的交會處是空的。像「冬瓜檸檬」沒有外送，透視表上那格是空白，公式查不到數字。

這幾種原因不同，但儀表板上看起來都一樣，是一格錯誤碼。我會包一層 IFERROR，讓查不到的格子顯示 0：

不過要記得，0 不一定是真的沒賣，也可能是被篩選器濾掉了，這點在 透視表進階（下） 的「組合不存在時回 #REF!」有講。

要不要用 GETPIVOTDATA

如果只是想要「蘋果紅茶在門市的總杯數」這個數字，不需要透視表，直接在訂單上寫 SUMIFS 就行了：

=SUMIFS(訂單!C2:C11, 訂單!A2:A11, "蘋果紅茶", 訂單!B2:B11, "門市")

也是 66。我的分工是：本來就需要透視表的版面（有排序、有小計、同事會自己改拖拉），再用 GETPIVOTDATA 把數字帶到儀表板；不需要透視表的，就用 SUMIFS，少一層。多條件的 SUMIFS 在 SUMIFS 進階陷阱。

動手練習

試著把自己的透視表換個排序，看看公式有沒有跟著跑掉吧！我是喜特先生，Mr. Sheet，我們下個教學見！