Excel GETPIVOTDATA 函數

 

Excel在數據透視表中,當我們引用的時候,其實使用的就是GETPIVOTDATA函數
但是你可能沒有特別明白這個函數的意義所在
如何你第一次嘗試單獨使用GETPIVOTDATA可能結果並不滿意
這是可以理解的，因為它的默認形式是相當不靈活的。

然而，與常規的單元格引用相比，使用GETPIVOTDATA的好處是巨大的，它可以減少很多工作量.

為什麼?數據透視表報告通常會隨著你向數據源添加新數據和改變篩選等而更新或改變欄位選擇。
如果您連結到數據透視表中具有常規單元格引用特性，然後由於刷新、應用篩選器或切片器而更改數據的位置，那麼公式會突然返回錯誤的信息。

GETPIVOTDATA不是這樣。如果你的數據的位置移動了，那麼GETPIVOTDATA仍然會返回原本的數據，前提是它仍然在你的數據透視表的某個地方可見。

因此，本教程的目標是將您從一個懵懂於GETPIVOTDATA的人轉變為一個喜歡GETPIVOTDATA的人!

利用GETPIVOTDATA功能的訣竅是用嵌套公式替換硬性參數，從而使GETPIVOTDATA公式變得動態。聽起來很複雜，但事實並非如此。看看下面就知道了

例如，下面用紅色標出的是「硬性」參數:

=GETPIVOTDATA("銷售額",$A$2,"訂購日期",1,"年",2002)

開始GETPIVOTDATA

首先，為了讓Excel生成GETPIVOTDATA公式，必須打開首選項。如果你不小心關閉了它，你可以在Ribbon的數據透視表選項卡中重新打開它:
 

Excel GETPIVOTDATA 函數例子

讓我們看一個簡單的場景。你可以在下面看到我的數據透視表。當我在單元格E5中輸入一個等號，然後點擊數據透視表中的單元格D5, Excel會自動輸入這個公式:

=GETPIVOTDATA("銷售額",$A$2,"訂購日期",1,"年",2002)
GETPIVOTDATA公式讀取，返回2002年1月的銷售總額。
 

對於通常=D5來說，這似乎是一個很大的公式，但是請記住，使用GETPIVOTDATA有很大的優勢。
注意:上面GETPIVOTDATA公式中對單元格A2的引用只是數據透視表的位置，它告訴Excel要從哪個數據透視表返回值。理論上，這可以是數據透視表中的任何單元格，但最安全的做法是選擇一個始終存在的單元格，而不考慮數據透視表大小的任何變化。

Excel GETPIVOTDATA 用法

GETPIVOTDATA的煩人之處在於，當您複製並粘貼公式時，例如向下的列E，引用不是相對的。也就是說，它們不像普通的單元格引用那樣更新以獲取範圍內的下一個單元格。所以你得到了一列相同的值就像E列中的這些:

 

使得 GETPIVOTDATA 函數自己動起來

讓我們從更新公式開始，這樣當複製到列中時，它就會每個月更新一次。這裡的技巧是知道數據透視表如何表示日期;對於Excel GETPIVOTDATA函數來說，這些月份的名稱似乎是1月、2月、3月…，但實際上它們是數字1、2、3…到12。
因此，我們需要將GETPIVOTDATA公式中的月份參數替換為當我們將公式複製到列中時自動計數的參數。對於這個我們可以使用行函數。

ROW函數

ROW函數隻返回引用的行號。因此，公式ROW(A1)將返回1，ROW(A2)將返回2，以此類推。
我們可以把ROW放入E4單元格的公式中，像這樣:

=GETPIVOTDATA("銷售額",$A$2,"訂購日期",ROW(A1),"年",2002)
計算結果是:

=GETPIVOTDATA("銷售額",$A$2,"訂購日期",1,"年",2002)

現在，當你將單元格E5公式複製到列下時，行函數中對單元格A1的相對引用會動態地增加1。我已經寫出了E在F中的公式，如下圖所示:

 

這個方法我們只是是這個函數稍微的變成了一個動態,但是這只是第一步,後面會告訴你們怎麼使用這樣的小型動態來做一些其他的事情.

小貼士: 

•通過這種辦法也可以是年變成動態的顯示
•這樣的辦法還可以使用在不僅僅是ROW()函數還可以使用在COLUMN()函數上,道理是一樣的.
•需要強調的這個ROW函數和COLUMN函數也使用在其他地方不僅僅是數據透視表,和其他的公式搭配使用,也可以達到動態的效果,最簡單的就是自動變化的序號.

Excel GETPIVOTDATA 函數配合數據驗證

如果我們使用GETPIVOTDATA搭配數據驗證序列會怎麼樣?
一步一步來
 

我們的湖南總銷售額的公式是這樣的:
=GETPIVOTDATA("銷售額",$A$2,"收貨省份","湖南")

我們可以將」湖南」改成E2單元格的內容,E2單元格,咱們做成兩個省份的序列數據驗證

=GETPIVOTDATA("銷售額",$A$2,"收貨省份",E2)

現在，當您從數據驗證列表中選擇一個不同的省份時，GETPIVOTDATA公式將動態更新。

這個很簡單,但是你要掌握這些小技巧,小巧多了,你才能做一個屬於你自己的大型管理表格.

Excel GETPIVOTDATA 函數總結

•一般使用這個公式,首先我會在一個需要返回或者引用的值後直接打一個等於號,然後引用這個數據,需要改的話,直接在公式裡進行修改.從頭開始單獨使用這個公式會讓你記不起很多數據透視表中定義過的表頭名稱

•你可以是所有的參數都變成動態的,然後搭配名稱管理器來使用,這也是很多人做DASHboard的慣用手法.

•你可以使用GETPIVOTDATA這個函數來引用值,但是不能查詢數據源本身.

•你可以用它來返回計算值.

•就像其他的函數一樣,函數都是可以進行計算的,如果你的數據需要進行引用之後的計算,也是可以的,比如: =GETPIVOTDATA("銷售額",$A$2,"收貨省份",E2)*2.3 意思就是湖南或者新疆地區的總銷售額乘以2.3 當然這個比喻並不恰當,但是在其他的邏輯表裡可以用這樣的方法去算.

•如果參數描述的值不在數據透視表中，它將返回#REF!錯誤。

希望你能學會這個函數

課件地址:https://share.weiyun.com/53v8SpE