excel函數應用解析：透視表專有函數GETPIVOTDATA

編按：哈嘍，大家好！今天是部落窩函數課堂的第8課，我們將一起來認識GETPIVOTDATA函數！不知道小夥伴們還記不記得這個函數。沒錯！它就是我們前段時間發布的《受夠加班煎熬，我整理出10條職場人士最常用的透視表技巧！（下篇）》教程中，所提到的透視表的專有函數。

---

GETPIVOTDATA函數的主要功能是返回透視表中的可見數據。需要在「數據透視表工具」欄下的「分析」選項卡下，點擊「選項」，勾選「生成GetPivotData」才能使用GETPIVOTDATA函數。 

先來看一下函數的結構： 

GETPIVOTDATA(data_field,pivot_table,[field1,item1,field2,item2], ...) 

data_field：必須是透視表中的值欄位名稱。格式必須是以成對的英文雙引號輸入的文本字符串或是經轉化為文本類型的單元格引用。 

pivot_table：對數據透視表中任何單元格或單元格區域的引用，該參數主要用於確認要檢索數據的數據透視表。 

[field1,item1,field2,item2,...]：一組或多組的「行/列欄位名稱」和項目名稱。主要用於描述獲取數據的條件，該參數可以是單元格引用或者常量文本字符串。最多可以有126組。 

為了便於大家理解，我們可以根據上述信息，將函數結構翻譯成大白話： 

=GETPIVOTDATA("透視表的值欄位名稱",數據透視表中任意單元格,"透視表的欄位名稱1",條件1,"透視表的欄位名稱2",條件2) 

（注意：除日期、數字和引用單元格外，參數都必須加上英文雙引號） 

看到公式這麼長，估計很多小夥伴都暈了，其實只要在單元格中輸入「=」（等號），然後在數據透視表中單擊包含要返回數據的單元格，即可快速輸入公式。 

怎麼樣？是不是感覺整個人都輕鬆了不少，看起來很容易嘛~

---

說了這麼多，我們還是舉個例子實際操作一下： 

統計下圖中的銷售額。 

在G2單元格中輸入公式： 

=GETPIVOTDATA("銷售額",$A$1,"銷售地區","北京","商品","吹風機") 

當然也可以直接在G2單元格輸入「=」，再點擊C2單元格的值，按回車鍵，同樣可以得到結果。 

接著我們將公式下拉到G4單元格，發現結果出錯了。 

這是怎麼回事呢？ 

觀察上圖可以發現，下拉公式後，數據並沒有隨之變動。這就不得不提到GETPIVOTDATA函數的另一個特性：內容引用。大家都知道引用數據一般分為地址引用和內容引用。區別在於：地址引用時，如果引用的單元格位置發生變動，那麼該值也會隨之變動。 

 而內容引用時，如果引用的單元格位置發生變動，該值不會發生變化。 

雖然內容引用可以在一定程度上保證引用數據的正確性，但以目前來看，我們需要將公式調整為地址引用，才能進行後續的計算。 

在G2單元格中輸入公式：

=GETPIVOTDATA("銷售額",$A$1,$E$1,E2,$F$1,F2) 

我們將原本公式中以文本形式輸入的透視表欄位名稱和條件，以單元格引用的形式輸入，成功得到了結果。 

看到這裡，有的小夥伴要提問了：既然上面的參數可以用單元格引用的形式輸入，那GETPIVOTDATA函數第一參數可不可以呢？ 

我們來測試一下，在G2單元格中輸入公式：

=GETPIVOTDATA(G1,$A$1,$E$1,E2,$F$1,F2) 

結果很明顯，公式報錯了。其實我們在前面解釋參數的時候，也提到過，GETPIVOTDATA函數的第一參數格式必須是以成對的英文雙引號輸入的文本字符串或是經轉化為文本類型的單元格引用。 

也就是說，如果第一參數要引用單元格，那麼需要將其轉換為文本類型的單元格引用。比如將第一參數G1變為""&G1 或 G1&"" ,抑或是TRIM(G1)。 

因為我們需要下拉公式，所以需使用「$」鎖定單元格。 

好了，說了這麼多，相信大家對GETPIVOTDATA函數已經有了一個大致的了解，接下來上「正菜」！

合併單元格絕殺函數！秒殺VLOOKUP、LOOKUP函數！ 

統計下圖中的銷售額。 

又是令人頭大的合併單元格的問題，先來看看大佬級函數VLOOKUP和LOOKUP是怎麼解決問題的！ 

=VLOOKUP(F2,OFFSET($B$1,MATCH(E2,$A$2:$A$17,0),0,COUNTA($B$2:$B$17)-MATCH(E2,$A$2:$A$17,0)+1,2),2,0) 

=LOOKUP(F2,INDIRECT("B"&MATCH(E2,A:A,0)&":C17")) 

上面兩個公式，相信絕大部分同學都看不懂，由於並不是今天的重點，我們就不過多解釋了。 

還有一種稍微簡單一點的方法就是先取消合併單元格，並將空白部分填充為對應的部門，再使用公式： 

=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$17&$B$2:$B$17,$C$2:$C$17),2,0) 

=LOOKUP(1,0/(($E2=$A$2:$A$17)*($F2=$B$2:$B$17)),$C$2:$C$17) 

雖然經過填充空白單元格的操作後，這個問題被當成多條件查詢來處理了，似乎看著比前面直接使用公式的方法更簡單，但是對於才掌握VLOOKUP和LOOKUP函數基礎用法的同學，估計也不太好懂。下面我們就給大家介紹一種更為簡單的方法，相信在座的你，一定能學會！ 

還是需要先取消合併單元格，並將空白部分填充為對應的部門。然後選中數據區域，插入數據透視表。

將「銷售地區」、「商品」拖放在「行」欄位下，「銷售額」拖放到「值」欄位下。接著將建立好的數據透視表變成我們常規的表格樣式。並在「數據透視表工具」欄下的「分析」選項卡下，點擊「選項」，勾選「生成GetPivotData」，啟用GETPIVOTDATA函數。

不熟悉步驟的小夥伴可以查看往期教程《受夠加班煎熬，我整理出10條職場人士最常用的透視表技巧！（上篇）》

做到這一步，小夥伴們有沒有覺得很熟悉呢？沒錯，這就是我們上面用GETPIVOTDATA函數舉的例子，後面的公式，相信大家也都知道啦~就不再重複了。

我們最後總結一下，在使用GETPIVOTDATA函數時，需要注意的問題。

1. GETPIVOTDATA函數第一參數的格式必須是以成對的英文雙引號輸入的文本字符串或是經轉化為文本類型的單元格引用。

2. GETPIVOTDATA函數屬於內容引用，下拉填充公式時，需要將其中的參數修改為地址引用。

3. GETPIVOTDATA函數是透視表專有函數，僅能在透視表中使用。

另外，在使用GETPIVOTDATA函數查找數據時，查找的數據必須在數據透視表中可見。如果數據被摺疊，那函數將會返回#REF!錯誤。

如果要關閉GETPIVOTDATA函數，可以在「數據透視表工具」欄下的「分析」選項卡下，點擊「選項」，取消選中「生成GetPivotData」選項即可。

是不是比VLOOKUP、LOOKUP好理解多了，趕緊動手試一試吧！

＊＊＊＊部落窩教育－excel透視表函數＊＊＊＊

原創：壹仟伍佰萬/部落窩教育（未經同意，請勿轉載）