當(dāng)我們?cè)趩卧裰休斎搿?”,然后單擊數(shù)據(jù)透視表中的某個(gè)數(shù)據(jù)時(shí),可能會(huì)看到Excel自動(dòng)幫我們生成一個(gè)很長(zhǎng)的公式。例如下圖: 這里出現(xiàn)的getpivotdata函數(shù),是單擊數(shù)據(jù)透視表中的數(shù)據(jù)時(shí),在公式中自動(dòng)生成的。 在引用數(shù)據(jù)透視表中的數(shù)據(jù)時(shí),如果不想看到這一長(zhǎng)串公式,可以關(guān)閉getpivotdata。與直接的單元格引用相比,getpivotdata函數(shù)比較長(zhǎng),看上去比較復(fù)雜,但當(dāng)數(shù)據(jù)透視表布局變化時(shí),getpivotdat函數(shù)仍然能按照指定條件正確取值。 1、getpivotdata函數(shù)使用示例 getpivotdata函數(shù)的功能是,從數(shù)據(jù)透視表中按條件取值。 (1)在B11單元格輸入“=”后,單擊B7單元格,自動(dòng)生成公式: =GETPIVOTDATA("金額",$A$1,"月份","5月","商品","打印機(jī)") 從數(shù)據(jù)透視表取值,必須要告訴excel從哪個(gè)數(shù)據(jù)透視表取值,以及取哪個(gè)字段的值。本例中數(shù)據(jù)透視表的數(shù)據(jù)分布在A1:E9區(qū)域,在getpivotdata函數(shù)中,“$A$1”足以指明數(shù)據(jù)透視表的位置。該公式的含義是,在A1單元格的數(shù)據(jù)透視表中,提取“月份”為“5月”,“商品”為“打印機(jī)”的“金額”字段數(shù)值。 (2)在B11單元格輸入“=”后,單擊E7單元格,自動(dòng)生成公式: =GETPIVOTDATA("金額",$A$1,"月份","5月") E7單元格的數(shù)據(jù)是5月份各商品的銷(xiāo)售總額。getpivotdata函數(shù)提取值的條件只有一個(gè),即“月份”為“5月”。 (3)在B11單元格輸入“=”后,單擊B9單元格,自動(dòng)生成公式: =GETPIVOTDATA("金額",$A$1,"商品","打印機(jī)") B9單元格的數(shù)據(jù)是“打印機(jī)”的銷(xiāo)售總額。getpivotdata函數(shù)提取值的條件只有一個(gè),即“商品”為“打印機(jī)”。 (4)在B11單元格輸入“=”后,單擊E9單元格,自動(dòng)生成公式: =GETPIVOTDATA("金額",$A$1) getpivotdata函數(shù)提取的是所有商品在所有月份的銷(xiāo)售總額,沒(méi)有條件限制。 (5)從上面的(1)~(4)示例中可以看到,自動(dòng)生成的getpivotdata公式中的條件不是單元格引用,而是雙引號(hào)括起來(lái)的文本。這意味如果向下或向右復(fù)制公式時(shí),getpivotdata返回的值是固定的。例如下圖中,返回的始終是“1月”“手機(jī)”的銷(xiāo)售額。 因此,在自動(dòng)生成的getpivotdata公式中需要做些修改。如下圖所示,將公式中的條件1、條件2修改為單元格引用$B12、C$11。 2、關(guān)閉getpivotdata函數(shù) 單擊數(shù)據(jù)透視表中的數(shù)據(jù)時(shí),在公式中自動(dòng)生成的getpivotdata函數(shù)。如果不想看到這一長(zhǎng)串公式,可以關(guān)閉getpivotdata。 選中數(shù)據(jù)透視表中的任意一個(gè)單元格,會(huì)出現(xiàn)【數(shù)據(jù)透視表分析】選項(xiàng)卡。 如果【數(shù)據(jù)透視表分析】-【選項(xiàng)】下拉列表中的“生成GetPivotData”被勾選,公式中就會(huì)自動(dòng)出現(xiàn)getpivotdata函數(shù)。 單擊“生成GetPivotData”,取消勾選,再單擊數(shù)據(jù)透視表中的數(shù)據(jù)時(shí),就是普通的單元格引用形式。 勾選該選項(xiàng)前后,公式的變化: 3、與直接單元格引用比較 如下圖所示,要求從數(shù)據(jù)透視表獲取“5月”銷(xiāo)售的“打印機(jī)”的銷(xiāo)售額。 C11單元格使用getpivotdata函數(shù),C12單元格使用簡(jiǎn)單的公式“=B7”。 C11、C12單元格的值都能夠獲取5月打印機(jī)銷(xiāo)售額“960”。但C11單元格使用getpivotdata函數(shù),公式比較長(zhǎng)。這樣看來(lái)似乎沒(méi)有必要使用getpivotdata函數(shù),使用普通的單元格引用就可以。 如果我們把數(shù)據(jù)透視表按“總計(jì)”升序排序,就會(huì)發(fā)現(xiàn)C11單元格的getpivotdata函數(shù)仍然返回5月打印機(jī)銷(xiāo)售額“960”,而C12單元格返回值變成1月打印機(jī)銷(xiāo)售額“0”。 這是getpivotdata函數(shù)與直接單元格引用想比的優(yōu)勢(shì)。當(dāng)數(shù)據(jù)透視表的布局變化時(shí),getpivotdata函數(shù)仍然能依照指定條件正確取值。 點(diǎn)個(gè)在看你最好看 |
|
來(lái)自: 初風(fēng)Excel教學(xué) > 《待分類》