[原创]EXCEL2007新增函数在发放统计的应用

流星

问题：某公司仓库的用品发放表，每个部门一张工作表，分别为Sheet1、Sheet2、Sheet3、Sheet4。
在工作表中储存了发放人员和发放的的物品及金额数。
如何在查询表中根据发放人员及物品得到人次数及金额总计及这4张表中平均金额的最大值？

源数据表（Sheet1、Sheet2、Sheet3、Sheet4）的格式和示例数据如下：
A列    B列 C列
人员 货物 返回金额
乙     A     121.24
甲     A      2324.812
甲     B      568.24
丙     A      62.35
甲     B      283.4
乙     A      12.88
丁     A       90.23
乙     B      2.0369
甲     A      3.014
丙     A      1.001
丙     A      9
丁     B      5
戊     A      23.0156
戊     B      2.301
丙     B      45
甲     B     6758
甲     A     58788

目的表格式如下：
A列           B列          C列      D列        E列
人员名称 货物清单 人次数 金额总计 平均返回最大金额
甲             A
甲             B
乙             A
乙             B
丙             A
丙             B
丁             A
丁             B
戊             A
戊             B
己             A
己             B
庚             A
庚             B
辛             A
辛             B

 

zjxia889

谢

bin_yang168

好东东，收藏，谢谢！

流星

答：
     可以使用数据库函数来做，但是数据库函数却有标题名称引用的问题，即条件区域的标题名称必须

与源数据区域的标题名称相同，同时对于多个查询项时，还要减去上一项的取值结果。
     而使用EXCEL2007新增的SUMIFS,COUNTIFS,AVERAGEIFS可以轻松解决上述问题。解答如下：

人次数取值 C2 单元格公式 如下：

=SUMPRODUCT(COUNTIFS(INDIRECT("Sheet"&ROW($1:$4)&"!A1:A100"),$A2,INDIRECT("Sheet"&ROW

($1:$4)&"!B1:B100"),$B2))

公式解释:

      由于表名是有规律性的，所以通过 INDIRECT("Sheet"&ROW($1:$4)&"!A1:A100") 及INDIRECT

("Sheet"&ROW($1:$4)&"!B1:B100") 来返回 Sheet1 至 Sheet4的各个表格的A1:A100和B2:B100的单元格

区域引用，Countifs的语法为 COUNTIFS(range1, criteria1,range2, criteria2…)，第二个参数及第四

个参数分别是条件值。由于是多维引用计算，COUNTIFS返回的是一个数组，最后通过SUMPRODUCT函数将它

们汇总求和。得到值 6 。
可以将C2的单元格下拉复到到C列其它的单元格中进行求值。

全部总计取值 D2 的单元格公式 如下：

=SUMPRODUCT(SUMIFS(INDIRECT("Sheet"&ROW($1:$4)&"!C1:C100"),INDIRECT("Sheet"&ROW($1:$4)&"!

A1:A100"),$A2,INDIRECT("Sheet"&ROW($1:$4)&"!B1:B100"),$B2))

公式解释：

SUMIFS函数的语法是：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
通过使用INDIRECT()来返回Sheet1 至 Sheet4的各个表格的对应区域，对各个表格区域内满足多重条件的

单元格求和。由于是多维引用计算，SUMIFS返回的是一个数组，最后通过SUMPRODUCT函数将它们汇总求和

。得到值 61200.826 。
可以将D2的单元格下拉复到到D列其它的单元格中进行求值。

平均返回最大金额 取值 E2单元格公式 如下：

=MAX(IFERROR(AVERAGEIFS(INDIRECT("Sheet"&ROW($1:$4)&"!C1:C100"),INDIRECT("Sheet"&ROW($1:$4)

&"!A1:A100"),$A2,INDIRECT("Sheet"&ROW($1:$4)&"!B1:B100"),$B2),0))

公式解释：

AVERAGEIFS函数的语法是：
AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)
通过使用INDIRECT()来返回Sheet1 至 Sheet4的各个表格的对应区域，最后返回各个表格区域内满足多重

条件的所有单元格的算术平均值，由于会出现没有符合条件的单元格存在的情况，所以使用IFERROR函数

来避免#DIV/0!错误值出现，由于是多维引用计算，AVERAGEIFS返回的是一个数组，最后通过MAX函数取它

们的最大值。得到值 20371.942 。
可以将E2的单元格下拉复到到E列其它的单元格中进行求值。

由于SUMIFS和COUNTIFS及AVERAGEIFS可以不借助条件区域的引用，所以可以直接生成内存数组。这样可以

将criteria修改为一个单元格区域的引用，如A2修改为A2:A17,最后通过MMULT()函数可以生成内存数组,

加快数据的运算。

同时补充一点。如果表格没有规律时，可以使用宏表函数GET.WORKBOOK(1)定义为一个名称，利用函数取

出除“查询表”外的EXCEL表名，这样同以可以通过使用INDIRECT()来返回对应的单元格区域引用。

yiqun_zhao

非常好，支持。

zz5151353

非常有用
多謝流星超級版主提供 !!!

 

[em23][em23][em23][em24][em24][em24][em27][em27][em27]

世纪浪人

看不懂，函数嵌套一多我的头就大

knifefox

汗，还好是2007不然把我出的题答案给公布了。[em03]

lvyanfu

[em23][em23]

豆浆

把公式开刀 解剖开讲解 这好！对我菜鸟来说收获不少。