第4章
高级函数：Excel函数高级用法

4.1　矩阵乘积函数

公式：MMULT(array1,array2)

作用：实现数组array1的每一行单元格与数组array2的每一列与之对应的单元格相乘再相加计算。

要注意的知识点

要点1：数组array1的列数要与数组array2的行数相等。

要点2：返回的结果是一个新的数组，这个新数组的行数与数组array1的行数一样，列数与数组array2的列数一样。

要点3：这两个参数不支持逻辑值运算。

要点4：单元格区域引用可以作为参数。

要点5：不支持文本运算，如果是空单元格，则要加“- -”，让其转换为0。

案例221　使用MMULT函数求各科成绩总和

案例及公式如下图所示。这里要求各科成绩总和。

公式

=MMULT(A5:C7,E2:E4)

公式解释

公式的计算顺序是：单元格区域A5:C7的第1行——单元格A5，与单元格E2相乘；单元格B5与单元格E3相乘；单元格C5与单元格E4相乘，然后求和，即60×1+70×1+80×1，最后得到的结果为210。接着是单元格区域A5:C7的第2行——单元格A6，与单元格E2相乘；单元格B6与单元格E3相乘；单元格C6与单元格E4相乘，然后求和，也就是50×1+60×1+70×1，最后得到的结果为180。单元格区域A5:C7的第3行——单元格A7，与单元格E2相乘；单元格B7与单元格E3相乘；单元格C7与单元格E4相乘，然后求和，也就是80×1+90×1+100×1，最后得到的结果为270。

返回的结果是3行1列的单元格区域，即返回的结果是一个新的数组，这个新数组的行数与数组array1的行数一样，列数与数组array2的列数一样。

案例222　使用MMULT函数求每一个人的总分

案例及公式如下图所示。这里要求每一个人的总分。

公式

=MMULT(B7:D7,B2:E4)

公式解释

要求每一个人的总分，需要用到以下方法：

将单元格区域B7:D7这1行与单元格区域B2:E4的第1列相乘再相加，即B7×B2+C7×B3+D7×B4，结果为1×60+1×70+1×80=210，得到张三的总分；

将单元格区域B7:D7这1行与单元格区域B2:E4的第2列相乘再相加，也就是B7× C2+C7×C3+D7×C4，结果为1×50+1×60+1×70=180，得到李四的总分；

将单元格区域B7:D7这1行与单元格区域B2:E4的第3列相乘再相加，也就是B7× D2+C7×D3+D7×D4，结果为1×80+1×90+1×100=270，得到王五的总分；

将单元格区域B7:D7这1行与单元格区域B2:E4的第4列相乘再相加，也就是B7× E2+C7×E3+D7×E4，结果为1×30+1×40+1×50=120，得到钱六的总分。

在此公式中，第1参数为3列，第2参数为3行4列，符合MMULT函数的第1参数的列数要与第2参数的行数相等的要求。

返回的结果是1行4列的数组，也就是得到4个结果，即返回的结果是一个新的数组，这个新的数组的行数与数组array1的行数一样为1行，列数与数组array2的列数一样为4列。

案例223　使用MMULT函数单条件求和

案例及公式如下图所示。这里要根据姓名求对应的数量总和。

公式

=MMULT(TRANSPOSE(N($A$2:$A$7=D2)),$B$2:$B$7)

公式解释

如上图所示，A列和B列都是纵向的，所以在判断A2:A7单元格区域之后，返回的是一串由FALSE和TRUE组成的布尔值。由于MMULT函数不直接支持布尔值运算，所以这里通过N函数进行转换，把TRUE转换为1，把FALSE转换为0，然后再作为MMULT函数的第1参数。这样就符合MMULT函数的第1参数的列数要和第2参数的行数相等的要求。当然，这里最好使用SUMIF函数，没有必要用MMULT函数。我们这里用它是为了让读者学习MMULT函数的用法。

案例224　使用MMULT函数实现多行多列查找

案例及公式如下图所示。这里要根据姓名计算对应的数量总和。

公式

=INDEX(A1:F3,MATCH(1,MMULT(N(A1:F3=B6),TRANSPOSE(COLUMN(A1:F1)^0)),0),MATCH (1,MMULT(TRANSPOSE(N(A1:F3=B6)),ROW(A1:A3)^0),0)+1)

公式解释

MMULT(N(A1:F3=B6),TRANSPOSE(COLUMN(A1:F1)^0))：得到的是3行1列的一维数组{0;0;1}。

TRANSPOSE(COLUMN(A1:F1)^0)：返回的是{1;1;1;1;1;1}，即6行1列的一维数组。

MATCH(1,MMULT(N(A1:F3=B6),TRANSPOSE(COLUMN(A1:F1)^0)),0)：用MATCH函数找到1的位置，也就是定位“曹丽”在第几行，即第3行。

TRANSPOSE(N(A1:F3=B6))：用于返回6行3列的数组，即{0,0,0;0,0,0;0,0,1;0,0,0;0,0,0;0, 0,0}，作为MMULT函数的第1参数，因此，MMULT函数的第2参数一定要是3行数组。ROW(A1:A3)^0返回的是{1;1;1}。MMULT函数的第1参数的列数（3列）与第2参数的行数（3行）相等，第1参数和第2参数运算之后返回6行1列的一维数组：{0;0;1;0;0;0}。

之后再用MATCH函数定位1的位置，也就是定位“曹丽”所在那一列，作为INDEX函数的第3参数。

案例225　找出每个销售员销量最大的4个数值

案例及公式如下图所示。这里要找出每个销售员的销量最大的4个数值。

公式

=MAX(MMULT(MOD(LARGE(C3:L7+ROW(C3:C7)/1%%%,(7-ROW(C3:C7))*10+COLUMN(A1:D1)),10^6),{1;1;1;1}))

公式解释

LARGE(C3:L7+ROW(C3:C7)/1%%%,(7-ROW(C3:C7))*10+COLUMN(A1:D1))：这里对LARGE函数的第1参数进行加权，把每一行数据的行号放大1000000倍（除以1%%%就是相当于乘以1000000），然后加上它们本身的值。这样加权处理之后，下一行比上一行扩大1000000倍，目的是拉大它们之间的数量级差别。

这里的LARGE函数的第2参数用了数组，(7-ROW(C3:C7))*10+COLUMN(A1:D1)返回的结果为{41,42,43,44; 31,32,33,34; 21,22,23,24;11,12,13,14;1,2,3,4}。意思是每一行，也就是每一个姓名对应的销量最大的4个数值，然后又通过加权还原回来，通过MOD函数取余，得到一个5行4列的二维数组{10,9,9,6;9,8,7,7;9,8,8,7; 9,6,5,5;10,10,10,9}作为MMULT函数的第1参数，它的列数是4列，这样我们就给MMULT函数第2参数构建4行1列的数组，得到{1;1;1;1}。最后MMULT函数返回的结果是每个姓名对应的销量最大的4个数值的和{34;31;32;25;39}，再用MAX函数提取最大值39。

案例226　按数量生成姓名

案例及公式如下图所示。这里要根据A列的姓名，按照B列中的数量，在C列生成姓名。

公式

=LOOKUP(ROW(A1)-1,MMULT(N(ROW($A$1:A5)>TRANSPOSE(ROW($A$1:A5))),--$B$2:B6),$A$2:A6)&""

公式解释

此公式的巧妙之处在于N(ROW($A$1:A5)>TRANSPOSE(ROW($A$1:A5)))，其构建了数组{0,0, 0,0,0;1,0,0,0,0;1,1,0,0,0;1,1,1,0,0;1,1,1,1,0}，并作为MMULT函数的第1参数，这样就可以把B列的数据从0开始进行累加。累加之后得到一维数组{0;3;4;6;7}，并作为LOOKUP函数的第2参数，且是升序排序。LOOKUP函数的第1参数为ROW(A1)-1，即从0开始查找。

为什么这里使用--$B$2:B6，要多加一个空单元格（B6）而且还要加两个负号--？空单元格表示结束了。由于B6是空单元格，MMULT函数不支持空值运算，所以要加--，把空单元格转换为0。