|
一、案例 如下图所示，B6:D15为各销售部门业务员销售额数据。要求对D列“销售额”数据进行筛选时，分别统计销售1部、销售2部、销售3部在筛选条件下的销售额合计。
例如，当未对销售额筛选时，C2：D4统计的是各销售部所有业务员销售额合计。
当筛选销售额大于500的数据时，C2：D4统计的是各销售部销售额超过500的业务员销售额合计。
二、解决方法 在C2单元格输入公式 =SUMPRODUCT(($B$7:$B$15=B2)*($D$7:$D$15)*SUBTOTAL(102,OFFSET($D$7,ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)),0))) 拖动填充柄向下复制公式。
公式解析： （1）($B$7:$B$15=B2)用于判断B7:B15中的部门数据是否等于“销售1部”，返回的结果为 {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}。返回的True和False逻辑值会在乘法运算时转为1和0。 （2）($B$7:$B$15=B2)*($D$7:$D$15)用于将不同部门的销售额数据区分开。当B7:B15中的部门是“销售1部”时，返回D7：D15中对应的销售额；反之返回0。 （3）SUBTOTAL函数用于判断D7:D15中的销售额数据是否在筛选范围之内。如果D7:D15的数据被筛选掉，SUBTOTAL函数返回0；如果D7:D17的数据未被筛选掉，SUBTOTAL函数返回1。 （4）SUBTOTAL的语法为SUBTOTAL(function_num,ref1,[ref2],...)。本例中，参数function_num为102，统计区域中包含数字的单元格的个数；参数ref1为OFFSET函数返回的引用。 （5）ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)) 返回数组{0;1;2;3;4;5;6;7;8}。 （6）OFFSET($D$7,ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)),0)即 OFFSET($D$7,{0;1;2;3;4;5;6;7;8},0)。指以D7单元格为起点，分别向下移动0、1、2…8行。 （7）SUBTOTAL函数判断OFFSET返回的每个引用是否被筛选出来。当筛选销售额大于500的数据时，SUBTOTAL返回的结果为{0;0;1;0;0;1;1;0;1}，“0”表示该行的数据被筛选掉，“1”表示该行数据未被筛选掉。 |
|
|
