剛發(fā)現(xiàn)，這里也能發(fā)文章。正好，最近事情有點(diǎn)多，微信公眾號(hào)那邊來不及更新新文章，就發(fā)了一篇老文章，順便發(fā)在這里吧。這篇文章詳細(xì)介紹了Excel中的GETPIVOTDATA函數(shù)，正好跟接下來一段時(shí)間計(jì)劃發(fā)的一系列文章有關(guān)，發(fā)在這里算是一個(gè)“溫習(xí)”和“預(yù)熱”吧。??

認(rèn)識(shí)GETPIVOTDATA

大部分使用Excel的朋友對(duì)這個(gè)函數(shù)都相當(dāng)陌生。但是，我相信很多人都見過這個(gè)函數(shù)，大多數(shù)人第一次見到這個(gè)函數(shù)是如下的場(chǎng)景：

假設(shè)我們有這個(gè)一個(gè)透視表：

我們希望引用一月份的總銷售額然后求產(chǎn)品的平均值，由于1月份的銷售額合計(jì)在F5單元格，因此，我們希望的公式是：=F5/5（5個(gè)產(chǎn)品）。于是，你像輸入其他公式一樣，在G5單元格中輸入一個(gè)：=，然后鼠標(biāo)點(diǎn)擊F5單元格，出乎你的意料，你沒有得到：=F5，卻得到了一長(zhǎng)串內(nèi)容：

很多人可能會(huì)把這一長(zhǎng)串內(nèi)容“嚇著”，趕緊就按Esc鍵，取消輸入，甚至都沒有來得及看看這一長(zhǎng)串內(nèi)容寫的是什么??。

這一長(zhǎng)串內(nèi)容就是在公式引用透視表的單元格時(shí)自動(dòng)生成的GETPIVOTDATA函數(shù)。

這個(gè)函數(shù)其實(shí)很簡(jiǎn)單的。

先來看名字：

從名字上看，這個(gè)公式的作用就是從透視表中獲得數(shù)據(jù)。

它的使用也比較簡(jiǎn)單。我們看下面的例子：

這里這個(gè)公式=GETPIVOTDATA("數(shù)量",$A$3,"區(qū)域","西區(qū)","月",1)使用了6個(gè)參數(shù)，很簡(jiǎn)單就可以看出是什么意思：

這個(gè)公式的意思就是：

取得——A3單元格所在的透視表中，列字段“區(qū)域”中項(xiàng)目為“西區(qū)”，行字段“月份”中項(xiàng)目為“1” 的“數(shù)量”

實(shí)際上，就是通過這些參數(shù)唯一限定了透視表區(qū)域的一個(gè)單元格。

盡管簡(jiǎn)單，這個(gè)公式在使用中還是有一些需要注意的地方，下面我們結(jié)合例子來詳細(xì)介紹一下。

GETPIVOTDATA例子和注意事項(xiàng)

例1 最簡(jiǎn)單的GETPIVOTDATA公式

這個(gè)公式中不是除了前面兩個(gè)參數(shù)外，其余的參數(shù)都不是必須的。因此，我們可以寫這個(gè)一個(gè)公式：

=GETPIVOTDATA("數(shù)量",$A$3)

這個(gè)公式的意思是獲得A3單元格所在的透視表中的數(shù)量。對(duì)照這個(gè)透視表，你能夠指出到底是哪一個(gè)值：

這里沒有通過行字段和列字段指定范圍，那么在這個(gè)透視表上用“數(shù)量”唯一能夠確定的就是右下角那個(gè)單元格F8，即1082279。

例2 只指定列字段

理解了上一個(gè)例子的，下面這個(gè)公式就很容易理解了：

=GETPIVOTDATA("數(shù)量",$A$3,"區(qū)域","西區(qū)")

這個(gè)值只要取得透視表中區(qū)域?yàn)槲鲄^(qū)的數(shù)量值，由于沒有行字段的限定，因此，只能是西區(qū)的合計(jì)值，就E8單元格的值。

同樣，可以只指定行字段，而不要列字段。

例3?多個(gè)行標(biāo)簽（或列標(biāo)簽的情況）

如果透視表上行標(biāo)簽不止一個(gè)，那么要唯一確定透視表的單元格就必須用多個(gè)行標(biāo)簽來限定，參數(shù)就會(huì)更多。

例如這個(gè)公式：

=GETPIVOTDATA("數(shù)量",$A$3,"日期",2,"區(qū)域","東區(qū)","產(chǎn)品","芬達(dá)橙味200","月",1)

其中，取得值字段是“數(shù)量”，列標(biāo)簽是“區(qū)域”，項(xiàng)目是“東區(qū)”，但是行標(biāo)簽是通過月份，日期，產(chǎn)品3個(gè)標(biāo)簽來限定的。

從這個(gè)例子也可以看出，除了前兩個(gè)參數(shù)外，其他的參數(shù)順序無所謂，只要能唯一限定一個(gè)單元格就好了。

例4?這個(gè)公式本質(zhì)上是個(gè)查找

如果你換一個(gè)角度看這個(gè)公式，實(shí)際上這個(gè)公式就是個(gè)多條件3D的查找公式。

3D指三個(gè)維度：

1. ?值字段——第一個(gè)參數(shù)

2. 行字段——行字段名稱和行字段項(xiàng)目

3. 列字段-列字段名稱和列字段項(xiàng)目

這個(gè)公式根據(jù)這個(gè)3個(gè)維度唯一確定一個(gè)值。

還是上面的公式，如果我們把透視表變成表格格式，就看得更加清楚了：

實(shí)際上，這個(gè)公式是在這個(gè)表格中查找滿足行列條件的那個(gè)單元格的值。

例5 透視表布局會(huì)影響結(jié)果。

由于上面這個(gè)原因，不同的透視表布局會(huì)影響同一個(gè)公式的結(jié)果。例如：

現(xiàn)在這個(gè)公式的值是1082279。如果我們將篩選改成1月：

公式?jīng)]變，結(jié)果卻變了。這個(gè)例子再次說明這個(gè)公式的機(jī)制就是在透視表表格中查找，它并不負(fù)責(zé)根據(jù)源數(shù)據(jù)計(jì)算相應(yīng)的結(jié)果。

例6 如果公式中指定的標(biāo)簽在透視表上不存在怎么辦？

假設(shè)，原來的公式是：

=GETPIVOTDATA("數(shù)量",$A$3,"日期",2,"區(qū)域","東區(qū)","產(chǎn)品","芬達(dá)橙味200","月",1)

現(xiàn)在，我們將透視表中產(chǎn)品從行標(biāo)簽中去掉：

可以看到，現(xiàn)在公式返回了一個(gè)錯(cuò)誤值，表示引用的區(qū)域不存在了！

例8 公式中可以引用單元格

這個(gè)很容易理解。例如，公式：

=GETPIVOTDATA("數(shù)量",$A$3,"日期",2,"區(qū)域","東區(qū)","產(chǎn)品","芬達(dá)橙味200","月",1)

可以寫成：

=GETPIVOTDATA("數(shù)量",$A$3,"日期",2,"區(qū)域","東區(qū)","產(chǎn)品","芬達(dá)橙味200","月",G1)

在G1中輸入不同月份，就可以取出對(duì)應(yīng)月份的數(shù)量。

這樣我們就可以很靈活的控制我們需要的內(nèi)容了。

為什么要使用GETPIVOTDATA函數(shù)

大部分在了解了這個(gè)函數(shù)后，下一個(gè)必然的問題就是：既然都使用了透視表了，為什么還需要使用這個(gè)函數(shù)？

根據(jù)我的經(jīng)驗(yàn)，有2個(gè)原因：

1. 報(bào)告布局的要求
   在我們做報(bào)告或者Dashboard時(shí)，對(duì)版式布局的要求比較嚴(yán)格。而透視表很多時(shí)候不方便進(jìn)行布局，又可能多出了很多我們不需要展示的數(shù)據(jù)。但是我們又需要通過透視表來分析匯總數(shù)據(jù)。這時(shí)，我們就可以通過這個(gè)函數(shù)來取出我們想要的數(shù)據(jù)按照我們的布局要求放在最終的結(jié)果表上。這篇文章介紹了一個(gè)這么使用的例子：【Excel模板】年底了，贈(zèng)送九宮人才盤點(diǎn)模板及模板制作方法。

2. 速度的要求
   這些值通過函數(shù)可以根據(jù)源數(shù)據(jù)算出來，但是會(huì)造成計(jì)算速度過慢等問題，此時(shí)，就可以使用透視表把數(shù)據(jù)計(jì)算出來，然后通過GETPIVOTDATA獲得想要的數(shù)據(jù)，從而提高計(jì)算速度。

GETPIVOTDATA的問題

看到這里，相信有很多朋友會(huì)發(fā)現(xiàn)這個(gè)函數(shù)有一個(gè)問題：

想使用GETPIVOTDATA取得相應(yīng)的數(shù)值，必須保證有一個(gè)透視表存在，并且該透視表的布局必須保證要取得的單元格是存在的。

如果你有多個(gè)需求，很可能這些需求不能在一個(gè)透視表布局上得到滿足，就需要做多個(gè)透視表。這會(huì)給后續(xù)的維護(hù)帶來相當(dāng)大的復(fù)雜性。

這是GETPIVOTDATA這個(gè)函數(shù)的機(jī)制造成的，沒有辦法避免。如果想規(guī)避這種情況，又想利用GETPIVVOTDATA這種方法，可以使用CUBE函數(shù)。接下來我會(huì)安排一系列視頻介紹這個(gè)函數(shù)結(jié)合數(shù)據(jù)模型的應(yīng)用。

關(guān)閉GETPIVOTDATA函數(shù)

很多人不知道在透視表中這個(gè)函數(shù)可以關(guān)閉“自動(dòng)生成”這個(gè)函數(shù)的功能。選中透視表任意單元格，在“分析”選項(xiàng)卡中，點(diǎn)擊最左邊的選項(xiàng)，然后去掉“生成GetPivotData”的勾選：

這樣，你再采取本文一開始的方法，在公式中點(diǎn)擊透視表的數(shù)值單元格時(shí)，就不會(huì)生成GETPIVOTDATA函數(shù)，而是直接引用單元格了。

需要說明的是，這個(gè)操作并沒有“關(guān)閉”這個(gè)公式的使用，你仍然可以在單元格中直接輸入：=GETPIVOTDATA來使用這個(gè)函數(shù)。