Excel可深可浅，遇到这个公式，咱的智商还够用不？

如何按月分别汇总每个销售员的销售额

在工作中处理各种数据报表时，经常需要对明细数据进行分类汇总。如图 90?1所示为某企业2009年销售数据明细表，现要求按月汇总各个销售员的销售额数据，该如何操作呢？

图90?1销售数据明细表

→ 解决方案：

使用MMULT函数多单元格数组公式进行双条件汇总数据。

→ 操作方法

选择F3:J14单元格区域，输入下列公式，按<Ctrl+Shift+Enter>组合键结束形成多单元格数组公式。

{=MMULT(--(E3:E14=TRANSPOSE(MONTH(B3:B100)&'月')),(A3:A100=F2:J2)*C3:C100)}

→ 原理分析

MMULT函数双条件求和

本例中共有5名销售员，需要按12个月分别汇总销售额数据，而汇总表中以月份为行字段，以销售员为列字段，因此，销售额的汇总结果区域是一个12行5列的矩阵区域，即MMULT函数返回的结果矩阵应该是一个12行5列的数组。

1．根据MMULT函数结果矩阵的行数与array1 的行数相同特性，首先以月份为条件构造一个12行n列的矩阵作为array1参数，其中n由销售明细表数据行数决定，即：

--(E3:E14=TRANSPOSE(MONTH(B3:B100)&'月'))

2．根据MMULT函数结果矩阵的列数与array2 的列数相同，以及Array1的列数与 array2 的行数相同的特性，以销售员为条件构造一个n行5列的矩阵，然后乘以C列销售额数据，作为array2参数，即：

(A3:A100=F2:J2)*C3:C100

3．最后使用MMULT函数运算，返回一个12行5列的结果矩阵，并使用多单元格数组公式方式存放多个计算结果：

{229,154,40,28,109;144,0,128,0,321;196,35,112,25,0;184,116,42,79,182;74,38,275,56,85;0,188,0,200,128;331,95,106,110,59;121,0,202,0,236;154,76,128,186,41;151,68,68,0,39;149,78,226,116,102;0,100,0,234,194}

其中，MMULT函数用于返回两个数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同，矩阵的列数与 array2 的列数相同。语法如下：

MMULT(array1, array2)

参数array1、array2为要进行矩阵乘法运算的两个数组，可以是单元格区域、数组常量或引用。Array1 的列数必须与 array2 的行数相同，而且两个数组中都只能包含数值，因此本例中array1使用减负运算将条件判断返回的逻辑值转换为数值。

→ 知识扩展

本例还可以使用SUMPRODUCT函数进行分类汇总，如在F3单元格输入下列公式，向右、向下填充至J14单元格：

=SUMPRODUCT(($A$3:$A$100=F$2)*(MONTH($B$3:$B$100)&'月'=$E3)*$C$3:$C$100)

相比之下，由于MMULT函数使用了多单元格数组公式，其运算效率要高于SUMPRODUCT函数构成的多个公式。

版权所有 转载须经Excel技巧网许可