在透视表函数中运用内存数组.xls

数据 透视表 aa 总计 数据 金额 数量 日期 品种 品种 分公司 A产品 上海分公司 C产品 D产品 南京分公司 B产品 海口分公司 A产品 C产品 D产品 B产品 分公司 海口分公司 南京分公司 上海分公司 海口分公司 汇总 南京分公司 汇总 上海分公司 汇总 　数量 日期 A产品 汇总 B产品 汇总 C产品 汇总 D产品 汇总 一、定义名称：aa 二、计算要求及结果： 金额 =IF(ISNUMBER(GETPIVOTDATA(T($D$2),透视表!$A$1,$A$2,透视表!$A$3:$A$34)),GETPIVOTDATA(T($D$2),透视表!$A$1,$A$2,透视表!$A$3:$A$34)) 1、销售量最大的分公司 2、销售量最小的分公司 3、销售量最大的分公司C产品的销售金额 4、销售量最小的分公司2008年10月1日D产品的销售价格 上面的定义名称中透视表函数的第三个参数使用了区域引用，结果生成内在数组：{9200;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;10500;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;21500;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!;#REF!} 再使用IF函数和ISNUMBER函数去除错误值后，就可以得到内在数组：{9200;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;10500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;21500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} 接下来可以通过使用名称AA进行需要的查询与统计 ’=LOOKUP(2,1/(MAX(aa)=aa),$A$3:$A$34) ‘=LOOKUP(2,1/(MIN(aa)=aa),$A$3:$A$34) ’=GETPIVOTDATA(T(E2),$A$1,$B$2,C产品,$A$2,LOOKUP(2,1/(MAX(aa)=aa),$A$3:$A$34)) ‘=GETPIVOTDATA(T($E$2),$A$1,$B$2,D产品,$A$2,LOOKUP(2,1/(MIN(aa)=aa),$A$3:$A$34),$C$2,DATE(2008,10,1))/GETPIVOTDATA(T($D$2),$A$1,$B$2,D产品,$A$2,LOOKUP(2,1/(MIN(aa)=aa),$A$3:$A$34),$C$2,DATE(2008,10,1)) 公式 特：本用法能够根据明细数据实时更新而动态变化，但只有在透视表布局结构保持不变时，函数公式才能正确返回结果，否则将出现错误 26160.00 ?2008?/?10?/?1 ￥4,800.00 51496.00 ?2008?/?10?/?1 ￥8,200.00 6640.00 ?2008?/?10?/?1 ￥1,000.00 14715.00 ?2008?/?10?/?2 ￥2,700.00 27004.00 ?2008?/?10?/?2 ￥4,300.00 3320.00 ?2008?/?10?/?2 ￥500.00 14170.00 ?2008?/?10?/?1 ￥2,600.00 2965.00 ?2008?/?10?/?1 ￥500.00 17584.00 ?2008?/?10?/?1 ￥2,800.00 13625.00 ?2008?/?10?/?2 ￥2,500.00 1779.00 ?2008?/?10?/?2 ￥300.00 11304.00 ?2008?/?10?/?2 ￥1,800.00 3558.00 ?2008?/?10?/?1 ￥600.00 22608.00 ?2008?/?10?/?1 ￥3,600.00 5976.00 ?2008?/?10?/?1 ￥900.00 4905.00 ?2008?/?10?/?2 ￥900.00 593.00 ?2008?/?10?/?2 ￥100.00 15072.00 ?2008?/?10?/?2 ￥2,400.00 4648.00 ?2008?/?10?/?2 ￥700.00 ?2008?/?10?/?2 900.00 ￥4,905.00 900.00 ￥4,905.00 ?2008?/?10?/?1 600.00 ￥3,558.00 ?2008?/?10?/?2 100