編按：

哈嘍，大家好！今天是部落窩函數(shù)課堂的第8課，我們將一起來(lái)認(rèn)識(shí)GETPIVOTDATA函數(shù)！不知道小伙伴們還記不記得這個(gè)函數(shù)。沒(méi)錯(cuò)！它就是我們前段時(shí)間發(fā)布的《受夠加班煎熬，我整理出10條職場(chǎng)人士最常用的透視表技巧！（下篇）》教程中，所提到的透視表的專有函數(shù)。

GETPIVOTDATA函數(shù)的主要功能是返回透視表中的可見(jiàn)數(shù)據(jù)。需要在“數(shù)據(jù)透視表工具”欄下的“分析”選項(xiàng)卡下，點(diǎn)擊“選項(xiàng)”，勾選“生成GetPivotData”才能使用GETPIVOTDATA函數(shù)。

先來(lái)看一下函數(shù)的結(jié)構(gòu)：

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

data_field：必須是透視表中的值字段名稱。格式必須是以成對(duì)的英文雙引號(hào)輸入的文本字符串或是經(jīng)轉(zhuǎn)化為文本類型的單元格引用。

pivot_table：對(duì)數(shù)據(jù)透視表中任何單元格或單元格區(qū)域的引用，該參數(shù)主要用于確認(rèn)要檢索數(shù)據(jù)的數(shù)據(jù)透視表。

[field1,item1,field2,item2,...]：一組或多組的“行/列字段名稱”和項(xiàng)目名稱。主要用于描述獲取數(shù)據(jù)的條件，該參數(shù)可以是單元格引用或者常量文本字符串。最多可以有126組。

為了便于大家理解，我們可以根據(jù)上述信息，將函數(shù)結(jié)構(gòu)翻譯成大白話：

=GETPIVOTDATA("透視表的值字段名稱",數(shù)據(jù)透視表中任意單元格,"透視表的字段名稱1",條件1,"透視表的字段名稱2",條件2)

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

看到公式這么長(zhǎng)，估計(jì)很多小伙伴都暈了，其實(shí)只要在單元格中輸入“=”（等號(hào)），然后在數(shù)據(jù)透視表中單擊包含要返回?cái)?shù)據(jù)的單元格，即可快速輸入公式。

怎么樣？是不是感覺(jué)整個(gè)人都輕松了不少，看起來(lái)很容易嘛~

說(shuō)了這么多，我們還是舉個(gè)例子實(shí)際操作一下：

統(tǒng)計(jì)下圖中的銷售額。

在G2單元格中輸入公式：

=GETPIVOTDATA("銷售額",$A$1,"銷售地區(qū)","北京","商品","吹風(fēng)機(jī)")

當(dāng)然也可以直接在G2單元格輸入“=”，再點(diǎn)擊C2單元格的值，按回車鍵，同樣可以得到結(jié)果。

接著我們將公式下拉到G4單元格，發(fā)現(xiàn)結(jié)果出錯(cuò)了。

這是怎么回事呢？

觀察上圖可以發(fā)現(xiàn)，下拉公式后，數(shù)據(jù)并沒(méi)有隨之變動(dòng)。這就不得不提到GETPIVOTDATA函數(shù)的另一個(gè)特性：內(nèi)容引用。大家都知道引用數(shù)據(jù)一般分為地址引用和內(nèi)容引用。區(qū)別在于：地址引用時(shí)，如果引用的單元格位置發(fā)生變動(dòng)，那么該值也會(huì)隨之變動(dòng)。

而內(nèi)容引用時(shí)，如果引用的單元格位置發(fā)生變動(dòng)，該值不會(huì)發(fā)生變化。

雖然內(nèi)容引用可以在一定程度上保證引用數(shù)據(jù)的正確性，但以目前來(lái)看，我們需要將公式調(diào)整為地址引用，才能進(jìn)行后續(xù)的計(jì)算。

在G2單元格中輸入公式：

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

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

看到這里，有的小伙伴要提問(wèn)了：既然上面的參數(shù)可以用單元格引用的形式輸入，那GetPivotData函數(shù)第一參數(shù)可不可以呢？

我們來(lái)測(cè)試一下，在G2單元格中輸入公式：

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

結(jié)果很明顯，公式報(bào)錯(cuò)了。其實(shí)我們?cè)谇懊娼忉寘?shù)的時(shí)候，也提到過(guò)，GetPivotData函數(shù)的第一參數(shù)格式必須是以成對(duì)的英文雙引號(hào)輸入的文本字符串或是經(jīng)轉(zhuǎn)化為文本類型的單元格引用。

也就是說(shuō)，如果第一參數(shù)要引用單元格，那么需要將其轉(zhuǎn)換為文本類型的單元格引用。比如將第一參數(shù)G1變?yōu)?span>""&G1 或 G1&"" ,抑或是trim(G1)。

因?yàn)槲覀冃枰吕?，所以需使用?span>$”鎖定單元格。

好了，說(shuō)了這么多，相信大家對(duì)GetPivotData函數(shù)已經(jīng)有了一個(gè)大致的了解，接下來(lái)上“正菜”！

合并單元格絕殺函數(shù)！秒殺VLOOKUP、LOOKUP函數(shù)！

統(tǒng)計(jì)下圖中的銷售額。

又是令人頭大的合并單元格的問(wèn)題，先來(lái)看看大佬級(jí)函數(shù)VLOOKUP和LOOKUP是怎么解決問(wèn)題的！

=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"))

上面兩個(gè)公式，相信絕大部分同學(xué)都看不懂，由于并不是今天的重點(diǎn)，我們就不過(guò)多解釋了。

還有一種稍微簡(jiǎn)單一點(diǎn)的方法就是先取消合并單元格，并將空白部分填充為對(duì)應(yīng)的部門，再使用公式：

=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)

雖然經(jīng)過(guò)填充空白單元格的操作后，這個(gè)問(wèn)題被當(dāng)成多條件查詢來(lái)處理了，似乎看著比前面直接使用公式的方法更簡(jiǎn)單，但是對(duì)于才掌握VLOOKUP和LOOKUP函數(shù)基礎(chǔ)用法的同學(xué)，估計(jì)也不太好懂。下面我們就給大家介紹一種更為簡(jiǎn)單的方法，相信在座的你，一定能學(xué)會(huì)！

還是需要先取消合并單元格，并將空白部分填充為對(duì)應(yīng)的部門。然后選中數(shù)據(jù)區(qū)域，插入數(shù)據(jù)透視表。

將“銷售地區(qū)”、“商品”拖放在“行”字段下，“銷售額”拖放到“值”字段下。接著將建立好的數(shù)據(jù)透視表變成我們常規(guī)的表格樣式。并在“數(shù)據(jù)透視表工具”欄下的“分析”選項(xiàng)卡下，點(diǎn)擊“選項(xiàng)”，勾選“生成GetPivotData”，啟用GETPIVOTDATA函數(shù)。

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

做到這一步，小伙伴們有沒(méi)有覺(jué)得很熟悉呢？沒(méi)錯(cuò)，這就是我們上面用GETPIVOTDATA函數(shù)舉的例子，后面的公式，相信大家也都知道啦~就不再重復(fù)了。

我們最后總結(jié)一下，在使用GETPIVOTDATA函數(shù)時(shí)，需要注意的問(wèn)題。

1. GETPIVOTDATA函數(shù)第一參數(shù)的格式必須是以成對(duì)的英文雙引號(hào)輸入的文本字符串或是經(jīng)轉(zhuǎn)化為文本類型的單元格引用。

2. GETPIVOTDATA函數(shù)屬于內(nèi)容引用，下拉填充公式時(shí)，需要將其中的參數(shù)修改為地址引用。

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

另外，在使用GETPIVOTDATA函數(shù)查找數(shù)據(jù)時(shí)，查找的數(shù)據(jù)必須在數(shù)據(jù)透視表中可見(jiàn)。如果數(shù)據(jù)被折疊，那函數(shù)將會(huì)返回#REF!錯(cuò)誤。

如果要關(guān)閉GETPIVOTDATA函數(shù)，可以在“數(shù)據(jù)透視表工具”欄下的“分析”選項(xiàng)卡下，點(diǎn)擊“選項(xiàng)”，取消選中“生成GetPivotData”選項(xiàng)即可。

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

本文配套的練習(xí)課件請(qǐng)加入QQ群：747953401下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

透視表技巧《受夠加班煎熬，我整理出10條職場(chǎng)人士最常用的透視表技巧?。ㄏ缕?/span>》

快速查找合并單元格中的數(shù)據(jù)《大膽合并吧！VLOOKUP坐字法專做單元格合并查找》

逆向查詢《VLOOKUP&LOOKUP雙雄戰(zhàn)（四）：在橫向和逆向查詢上的血拼！》