嗨,大家好,我是假期宅在家里刷群看 Excel,學習看 Excel,娛樂玩 Excel 的小爽~
最近我在看問答專欄的時候,無意中看到這樣的一個問題:怎么用切片器填數(shù)據(jù)?
后面我將需求大致梳理了一下:
這位同學是想要利用透視表的切片器,將透視表中的數(shù)據(jù)填入右側表格中。
這應該怎么做呢?看起來好像好難的樣子~
悄咪咪的告訴你,解決方法其實很簡單,就是利用 getpivotdata 函數(shù)!
這個問題應該怎么做呢?下面跟我一起來看看吧~
解決方法
上面我們提到 getpivotdata 函數(shù),大家肯定會特別好奇,這到底是什么函數(shù)?
我們先來看一下它的公式長什么樣,后面我們再通過公式來解析一下這個函數(shù)公式的原理。
=GETPIVOTDATA('銷售實際',$A$3,'銷售地區(qū)',H6)
▲ 左右滑動查看
?點擊透視表中 B4 單元格,此時出現(xiàn) getpivotdata 函數(shù);=GETPIVOTDATA('銷售實際',$A$3,'銷售地區(qū)','北京')
? 將參數(shù)中的北京改成單元格引用 H6;
? 下拉數(shù)據(jù)。
常見問題:
? 引用數(shù)據(jù)透視表的時候,未出現(xiàn) getpivotdata 函數(shù)。
數(shù)據(jù)透視表分析中,點擊選項,勾選生成 getpivotdata。
若想要默認勾選 getpivotdata 函數(shù),可以在【文件】-【選項】-【公式】中勾選。前面我們介紹了 getpivotdata 函數(shù)的做法。下面我們來看看 getpivotdata 函數(shù)的語法規(guī)則~getpivotdata 函數(shù)是用來返回數(shù)據(jù)透視表中的可見數(shù)據(jù),也就是這個函數(shù)只能用在數(shù)據(jù)透視表上。GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
=GETPIVOTDATA(求什么字段, 哪個透視表里找, [字段類別 1, 條件 1, field2, item2], ...)
=GETPIVOTDATA('銷售實際',$A$3,'銷售地區(qū)','北京')
▲左右滑動查看
▌第一參數(shù):求什么-求什么字段
在透視表中,我們求銷售實際的總和,所以第一參數(shù)是「銷售實際」,當然第一參數(shù)寫成「求和項:銷售實際」也是可以的。=GETPIVOTDATA('銷售實際'/'求和項:銷售實際'
▲左右滑動查看
▌第二參數(shù):哪里求-在哪個透視表找
由于我們的工作表中可能存在多個透視表,所以需要告訴 getpivotdata 函數(shù)是在哪個透視表上進行返回數(shù)據(jù)。=GETPIVOTDATA('銷售實際',$A$3
=GETPIVOTDATA('銷售實際',$A$3,'銷售地區(qū)',
▌第四參數(shù):具體定位-找哪個類別下的哪個條件=GETPIVOTDATA('銷售實際',$A$3,'銷售地區(qū)','北京')
這里的含義就是,找銷售地區(qū)下北京的銷售實際的總和。看到這里,是不是覺得 getpivotdata 函數(shù)很簡單呢?所以公式中「北京」改成單元格引用 H6,也就是引用北京的銷售實際和,下拉也就是 H7,廣州的銷售實際和……
最后插入透視表的切片器,切片器篩選后,數(shù)據(jù)也會隨著改變!對于為什么要使用 getpivotdata 函數(shù),我們或許會有一些疑惑。? 透視表中,切片器就可以直接篩選數(shù)據(jù),為什么還要將數(shù)據(jù)填寫在匯總表中?? 這樣借助透視表進行數(shù)據(jù)匯總有什么好處?
透視表中,切片器就可以直接篩選數(shù)據(jù),為什么要將數(shù)據(jù)填寫在匯總表中?下圖中我們可以直接通過數(shù)據(jù)透視表的切片器,篩選出我們需要的數(shù)據(jù)。比如說,財務表格的模板是固定的,領導要求我們必須按照這個模板來進行匯總。雖然說我們直接用別的函數(shù)公式也可以做到,但是編寫函數(shù)公式很復雜,后續(xù)修改也不方便。那有沒有辦法可以將數(shù)據(jù)引用出來?答案是有的,這就需要使用 getpivotdata 函數(shù)。案例中我們做出來的效果是這樣的,當然還可以對表格進行進一步的美化。這樣借助透視表進行數(shù)據(jù)匯總有什么好處?如果我們直接用常規(guī)的公式,一般就是利用 sumifs 等函數(shù),公式區(qū)域是直接引用數(shù)據(jù)源。
而這次借助透視表用 getpivotdata 函數(shù)直接引用,公式簡單,效率也更快。
? 透視表刷新后,表格的數(shù)據(jù)也有會自動更新。因為我們引用的數(shù)據(jù)是生成的透視表,如果透視表更新,那引用的數(shù)據(jù)也會跟著更新。關于動態(tài)更新數(shù)據(jù),戳它:你以為 getpivotdata 函數(shù)只有這些功能?它還可以結合數(shù)據(jù)有效性,求別的字段的匯總值。=GETPIVOTDATA($F$5&$F$6&'',$A$3,'銷售地區(qū)',H6)
到這里,是不是覺得 getpivotdata 刷新了自己的知識儲備呢?
好啦,本文就到這里啦!如果小伙伴們在使用過程中遇到困難,歡迎在留言區(qū)討論交流~
還想詳細了解其他兩種用法,也可以在評論區(qū)告訴小 E~