今天介紹Excel中的一個(gè)非常特殊的函數(shù)，GETPIVOTDATA。
一般在基礎(chǔ)性的培訓(xùn)課上，我都不會(huì)講這個(gè)函數(shù)，即使有人問(wèn)起，我也會(huì)說(shuō)不用關(guān)心它，對(duì)于普通人來(lái)說(shuō)，用處不大，完全可以不學(xué)它。
不過(guò)，在有些特殊場(chǎng)合，這個(gè)公式可以起到很大的作用，比如在DASHBOARD中，或者數(shù)據(jù)報(bào)告里。
看完本文的介紹，相信你會(huì)發(fā)現(xiàn)這個(gè)函數(shù)的用處的??
本文內(nèi)容：
認(rèn)識(shí)GETPIVOTDATA
使用GETPIVOTDATA的8個(gè)例子和注意事項(xiàng)
為什么要使用GETPIVOTDATA函數(shù)
GETPIVOTDATA的問(wèn)題
關(guān)閉GETPIVOTDATA的自動(dòng)生成
01
大部分使用Excel的朋友對(duì)這個(gè)函數(shù)都相當(dāng)陌生。但是，我相信很多人都見(jiàn)過(guò)這個(gè)函數(shù)，大多數(shù)人第一次見(jiàn)到這個(gè)函數(shù)是如下的場(chǎng)景：
假設(shè)我們有這個(gè)一個(gè)透視表：
我們希望引用一月份的總銷(xiāo)售額然后求產(chǎn)品的平均值，由于1月份的銷(xiāo)售額合計(jì)在F5單元格，因此，我們希望的公式是：=F5/5（5個(gè)產(chǎn)品）。于是，你像輸入其他公式一樣，在G5單元格中輸入一個(gè)：=，然后鼠標(biāo)點(diǎn)擊F5單元格，出乎你的意料，你沒(méi)有得到：=F5，卻得到了一長(zhǎng)串內(nèi)容：
很多人可能會(huì)把這一長(zhǎng)串內(nèi)容“嚇著”，趕緊就按Esc鍵，取消輸入，甚至都沒(méi)有來(lái)得及看看這一長(zhǎng)串內(nèi)容寫(xiě)的是什么??。
這一長(zhǎng)串內(nèi)容就是在公式引用透視表的單元格時(shí)自動(dòng)生成的GETPIVOTDATA函數(shù)。
這個(gè)函數(shù)其實(shí)很簡(jiǎn)單的。
先來(lái)看名字：
從名字上看，這個(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í)際上，就是通過(guò)這些參數(shù)唯一限定了透視表區(qū)域的一個(gè)單元格。
盡管簡(jiǎn)單，這個(gè)公式在使用中還是有一些需要注意的地方，下面我們結(jié)合例子來(lái)詳細(xì)介紹一下。
02
例1 最簡(jiǎn)單的GETPIVOTDATA公式
這個(gè)公式中不是除了前面兩個(gè)參數(shù)外，其余的參數(shù)都不是必須的。因此，我們可以寫(xiě)這個(gè)一個(gè)公式：
=GETPIVOTDATA('數(shù)量',$A$3)
這個(gè)公式的意思是獲得A3單元格所在的透視表中的數(shù)量。對(duì)照這個(gè)透視表，你能夠指出到底是哪一個(gè)值：
這里沒(méi)有通過(guò)行字段和列字段指定范圍，那么在這個(gè)透視表上用“數(shù)量”唯一能夠確定的就是右下角那個(gè)單元格F8，即1082279。
例2 只指定列字段
理解了上一個(gè)例子的，下面這個(gè)公式就很容易理解了：
=GETPIVOTDATA('數(shù)量',$A$3,'區(qū)域','西區(qū)')
這個(gè)值只要取得透視表中區(qū)域?yàn)槲鲄^(qū)的數(shù)量值，由于沒(méi)有行字段的限定，因此，只能是西區(qū)的合計(jì)值，就E8單元格的值。
同樣，可以只指定行字段，而不要列字段。
例3 多個(gè)行標(biāo)簽（或列標(biāo)簽的情況）
如果透視表上行標(biāo)簽不止一個(gè)，那么要唯一確定透視表的單元格就必須用多個(gè)行標(biāo)簽來(lái)限定，參數(shù)就會(huì)更多。
例如這個(gè)公式：
=GETPIVOTDATA('數(shù)量',$A$3,'日期',2,'區(qū)域','東區(qū)','產(chǎn)品','芬達(dá)橙味200','月',1)
其中，取得值字段是“數(shù)量”，列標(biāo)簽是“區(qū)域”，項(xiàng)目是“東區(qū)”，但是行標(biāo)簽是通過(guò)月份，日期，產(chǎn)品3個(gè)標(biāo)簽來(lái)限定的。
從這個(gè)例子也可以看出，除了前兩個(gè)參數(shù)外，其他的參數(shù)順序無(wú)所謂，只要能唯一限定一個(gè)單元格就好了。
例4 這個(gè)公式本質(zhì)上是個(gè)查找
如果你換一個(gè)角度看這個(gè)公式，實(shí)際上這個(gè)公式就是個(gè)多條件3D的查找公式。
3D指三個(gè)維度：
值字段——第一個(gè)參數(shù)
行字段——行字段名稱(chēng)和行字段項(xiàng)目
列字段-列字段名稱(chēng)和列字段項(xiàng)目
這個(gè)公式根據(jù)這個(gè)3個(gè)維度唯一確定一個(gè)值。
還是上面的公式，如果我們把透視表變成表格格式，就看得更加清楚了：
實(shí)際上，這個(gè)公式是在這個(gè)表格中查找滿(mǎn)足行列條件的那個(gè)單元格的值。
例5 透視表布局會(huì)影響結(jié)果。
由于上面這個(gè)原因，不同的透視表布局會(huì)影響同一個(gè)公式的結(jié)果。例如：
現(xiàn)在這個(gè)公式的值是1082279。如果我們將篩選改成1月：
公式?jīng)]變，結(jié)果卻變了。這個(gè)例子再次說(shuō)明這個(gè)公式的機(jī)制就是在透視表表格中查找，它并不負(fù)責(zé)根據(jù)源數(shù)據(jù)計(jì)算相應(yīng)的結(jié)果。
例6 如果公式中指定的標(biāo)簽在透視表上不存在怎么辦？
假設(shè)，原來(lái)的公式是：
=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)
可以寫(xiě)成：
=GETPIVOTDATA('數(shù)量',$A$3,'日期',2,'區(qū)域','東區(qū)','產(chǎn)品','芬達(dá)橙味200','月',G1)
在G1中輸入不同月份，就可以取出對(duì)應(yīng)月份的數(shù)量。
這樣我們就可以很靈活的控制我們需要的內(nèi)容了。
03
大部分在了解了這個(gè)函數(shù)后，下一個(gè)必然的問(wèn)題就是：既然都使用了透視表了，為什么還需要使用這個(gè)函數(shù)？
根據(jù)我的經(jīng)驗(yàn)，有2個(gè)原因：
報(bào)告布局的要求
在我們做報(bào)告或者Dashboard時(shí)，對(duì)版式布局的要求比較嚴(yán)格。而透視表很多時(shí)候不方便進(jìn)行布局，又可能多出了很多我們不需要展示的數(shù)據(jù)。但是我們又需要通過(guò)透視表來(lái)分析匯總數(shù)據(jù)。這時(shí)，我們就可以通過(guò)這個(gè)函數(shù)來(lái)取出我們想要的數(shù)據(jù)按照我們的布局要求放在最終的結(jié)果表上。這篇文章介紹了一個(gè)這么使用的例子：【Excel模板】年底了，贈(zèng)送九宮人才盤(pán)點(diǎn)模板及模板制作方法。
速度的要求
這些值通過(guò)函數(shù)可以根據(jù)源數(shù)據(jù)算出來(lái)，但是會(huì)造成計(jì)算速度過(guò)慢等問(wèn)題，此時(shí)，就可以使用透視表把數(shù)據(jù)計(jì)算出來(lái)，然后通過(guò)GETPIVOTDATA獲得想要的數(shù)據(jù)，從而提高計(jì)算速度。
04
看到這里，相信有很多朋友會(huì)發(fā)現(xiàn)這個(gè)函數(shù)有一個(gè)問(wèn)題：
想使用GETPIVOTDATA取得相應(yīng)的數(shù)值，必須保證有一個(gè)透視表存在，并且該透視表的布局必須保證要取得的單元格是存在的。
如果你有多個(gè)需求，很可能這些需求不能在一個(gè)透視表布局上得到滿(mǎn)足，就需要做多個(gè)透視表。這會(huì)給后續(xù)的維護(hù)帶來(lái)相當(dāng)大的復(fù)雜性。
這是GETPIVOTDATA這個(gè)函數(shù)的機(jī)制造成的，沒(méi)有辦法避免。如果想規(guī)避這種情況，又想利用GETPIVVOTDATA這種方法，可以使用CUBE函數(shù)，我會(huì)在以后的文章中為大家詳細(xì)介紹。
05
很多人不知道在透視表中這個(gè)函數(shù)可以關(guān)閉“自動(dòng)生成”這個(gè)函數(shù)的功能。選中透視表任意單元格，在“分析”選項(xiàng)卡中，點(diǎn)擊最左邊的選項(xiàng)，然后去掉“生成GetPivotData”的勾選：
這樣，你再采取本文一開(kāi)始的方法，在公式中點(diǎn)擊透視表的數(shù)值單元格時(shí)，就不會(huì)生成GETPIVOTDATA函數(shù)，而是直接引用單元格了。
需要說(shuō)明的是，這個(gè)操作并沒(méi)有“關(guān)閉”這個(gè)公式的使用，你仍然可以在單元格中直接輸入：=GETPIVOTDATA來(lái)使用這個(gè)函數(shù)。
聯(lián)系客服
