[分享]从头细说MMULT

czzqb

近日看见网友hahahah3的求助帖：

[求助]除数组公式外 多条件汇总
 

大意：使用SUM()数组公式进行多条件求和，速度很慢，求助更好的方法。我推荐了MMULT()函数，但是楼主却有很多不明白。为此我翻了一下论坛的老帖，发现有很多的人都对这个函数“打怵”。

所以，我想借用hahaha3朋友的这个帖子做例子，多用点时间，从头细说一下这个让人“双腿发抖”的MMULT。希望能让更多的人明白，能用好这个利器。

看帖入门考试

看帖还要考试？是的。因为MMULT是个比较难以理解的函数。如果没有一定的基础知识，下面说的就不大好理解。

“考试”很简单，就一题：

 

问：公式=A1:A3=B1:C1的结果是几行几列的数组？不要在excel运行而直接写出答案。

答案是三行两列：={FALSE,FALSE;FALSE,FALSE;FALSE,FALSE}

您的答案对吗？特别注意核对一下其中的逗号和分号的位置。

如果您的答案不对，那就先把这个题目好好琢磨一下。

答案正确，请跟我来，开讲了——

---

TRANSPOSE()是干什么的？

说起来很简单，TRANSPOSE()就是转置。帮助中说：“将一行单元格区域转置成一列单元格区域，反之亦然。”

MMULT和TRANSPOSE就像是一对铁哥们，MMULT走到哪里，TRANSPOSE就跟到哪里。它在MMULT里干些什么？

我们从hahaha3朋友做的公式说起。这个公式是：

MMULT(TRANSPOSE('Attendance Record'!B3:B271=B7:B13),('Attendance Record'!E3:E721=J4:L4)*'Attendance Record'!K3:K721)

这个公式的第一部分就错了('Attendance Record'!B3:B271=B7:B13)

错在哪里？

我们举个简化的例子：
  A1:A5=B1:B3
这个公式能得到想要的结果吗？不能。

你可以用F9来检验一下（假定A1=B1,A2=B2,A3=B3），得到的结果将是：={TRUE;TRUE;TRUE;#N/A;#N/A}
注意后面有两个#N/A。为什么？原来这个公式是这样比较的：

A1=B1
     A2=B2
     A3=B3
     A4=?
     A5=?

就是说两个数组(A1:A5和B1:B3)如果同样是行数组（EXCEL帮助中说的垂直数组），那么它的比较是第一数组的第一个元素和第二数组的第一个元素比较，第二个和第二个比较……问题是，第4个和第5个和谁比较？你既然没告诉它，它也就只好告诉你#N/A了。

如果两个数组都是列数组（水平数组），比较也是类似这样来进行的。

如果我们想比较的两个数组，一个是行数组，一个是列数组，那就不一样了。看这个：

A1:A3=B1:C1（假定A1=B1,A2=C1,A3<>B1,A3<>C1）

这个公式会有什么结果？按F9看看：={TRUE,FALSE;FALSE,TRUE;FALSE,FALSE}

没有一个错误值，只有TRUE和FALSE，说明它们都比较过了。怎么比较的呢？3×2个数怎么出来6个结果的（或者说比较了6次）？

原来比较是这样进行的：

A1=B1
    A1=C1
    A2=B1
    A2=C1
    A3=B1
    A3=C1

：    

好了，现在我们知道了：由于'Attendance Record'!B3:B271和B7:B13都是行数组，直接比较是比较不出结果来的。怎么办？要把其中的一个“转”过来（转置），就是用TRANSPOSE函数把行数组变成列数组，这样一个行数组和一个列数组就能比较了：

'Attendance Record'!B3:B271=TRANSPOSE(B7:B13)

原公式的后面一部分倒是对的：'Attendance Record'!E3:E721=J4:L4，等号前面是行数组，后面是列数组。

这就是TRANSPOSE的作用。

当然，反过来比较，公式也是成立的：TRANSPOSE('Attendance Record'!B3:B271)=B7:B13

那么，在MMULT公式中，TRANSPOSE应该怎么安排？ 到底应该把TRANSPOSE放在哪里？这和MMULT有密切关系。这就需要从头说MMUTL了

下面就来看MMULT()函数

MMULT()函数是怎样工作的

先看MMULT()函数的帮助。

从帮助中，我们可以得到函数的以下特性：

QUOTE:

1,这是个矩阵乘法；

2,MMULT()函数的两个参数都必须是数组，而且均为数值；

3,数组1的列数必须与数组2的行数相同；

4,得到的结果也是一个矩阵，这个矩阵的行数＝数组1的行数，矩阵的列数＝数组2的列数；

5,不满足以上2-4条件的公式返回错误值#VALUE!

那么什么是矩阵乘法？这是高等数学里线性代数的一个概念。我们试试通过一个简单的例子来理解它：

         

 就是说数组1的第一个元素和数组2的第一个元素相乘，生成新矩阵的第(1,1)个元素；
                  数组1的第一个元素和数组2的第二个元素相乘，生成新矩阵的第(1,2)个元素；
                  ……

这也是帮助中的那个复杂的公式的含义：

         

QUOTE:

特别感谢qygszlb朋友给出的这个完整的多行多列矩阵乘法的说明图：

 

从上面的例子里，我们知道：

1,为什么两个参数都必须是数值（特性2）？因为MMULT实际是在做乘法运算，而文本是不能相乘的。就连文本型数字和逻辑值也不行。其他一些情况下，EXCEL会把文本型数字和逻辑值变成数字参与运算，比如这两个公式="4"*3=12, =TRUE+1=2, 但MMULT()不会做这样的转换。

2,帮助中的这个公式值得我们牢记：

      

这个公式实际上就是上面说到的MMULT特性3,4的数学表达方式。如果能读懂、记住这个公式，MMULT的使用就很方便了。

我们希望得到什么？希望得到a(i,j)，就是一个i行j列的矩阵；要求是什么？它要求MMULT第一个参数是i行的，而第二个参数是j列的；而且第一参数的列数和第二参数的行数相等(K)

或者，我们从另一个角度来简单地理解并记住：先看看我们要得到什么？如果我们希望得到 i 行 j 列的结果，那就要构造两个矩阵，第一个要 i 行的，第二个要 j 列的

这样说，是不是还显得太抽象了？那下面我们就结合一个实际的多条件求和的例子来看。看例子的时候，我们还会反复说到上面提到的这些。

[此贴子已经被作者于2007-10-29 13:04:44编辑过]

czzqb

用MMULT做多条件求和

在看实例之前，我们先记住这一条：用MMULT求解多条件求和问题，一定要使用多单元格数组公式。也就是说，它的结果必须是连续的多个，越多越好。如果只是单个的，或者虽然是多个却不连续、无法使用多单元格数组公式的情况，那就千万不要用MMULT！为什么？先别问，记住它，以后再来说为什么。

看这个例子（见四海飘零的《多条件求和的新方法》第二个工作表http://club.excelhome.net/viewthread.php?tid=170501&replyID=&skin=0）

 

左边是源数据，要求在右边的表格里得出对应XX和YY的所有汇总结果
用SUMPRODUCT（或SUM数组公式），我们都会写了：

G2=SUMPRODUCT(($A$2:$A$19=E2)*($B$2:$B$19=F2)*($C$2:$C$19))，下拉

那么，用MMULT该怎么做？
首先要做的就是，判断一下是否符合使用MMULT的基本条件？我们要求的结果是在G2:G22是一个连续的区域。这就有戏了！

现在我们就来开始写。

最简单的办法是利用上面的SUMPRODUCT公式。第一步，把公式的内容提取出来：

==> ($A$2:$A$19=E2)*($B$2:$B$19=F2)*($C$2:$C$19)

先把其中的$去掉（既然我们要使用多单元格数组公式，就没有必要用$了。虽然$留着也不算错）

==> (A2:A19=E2)*(B2:B19=F2)*(C2:C19)

作为多单元格数组公式，就不能使用一个单一的条件E2/F2 ，而应该是一串：E2:E22/F2:F22。把它写进去：

==>(A2:A19=E2:E22)*(B2:B19=F2:F22)*(C2:C19)

这样的公式当然不对，这一点，我们开始的时候就说过了。A2:A19和E2:E22是同一个方向的，都是行数组，这不行。改为：

==>(A2:A19=TRANSPOSE(E2:E22))*(B2:B19=TRANSPOSE(F2:F22))*(C2:C19)

下面我们该给它拆开成两个，为MMULT做准备了。

首先分析一下，这里的三个数组各是几行几列的？

(A2:A19=TRANSPOSE(E2:E22))：18行21列

(B2:B19=TRANSPOSE(F2:F22))：18行21列

(C2:C19):18行1列

前面我们说过，“如果我们希望得到i行j列的结果，那就要构造两个矩阵，一个要 i 行，一个要 j 列”

注意，我们想得到的是21行1列的新数组（G2:G22)，也就是i=21, j=1

可是这三个数组不论怎么安排，都没有21行的。问题出在哪里？出在TRANSPOSE()的安排上。原来是前面把转置写错地方了。换一下看看：

(TRANSPOSE(A2:A19)=E2:E22) ：21行18列

(TRANSPOSE(B2:B19)=F2:F22) ：21行18列

这样不是就符合要求了吗？所以整个公式就是

==>(TRANSPOSE(A2:A19)=E2:E22)*(TRANSPOSE(B2:B19)=F2:F22),(C2:C19):

这里用逗号把三个数组“裁”为两截：前一截为21行18列；后一截为18行1列

这才符合了MMULT的要求：新的结果数组为21行1列     ，而且数组1是18列，数组2是18行，二者正好匹配符合特性2:“数组1的列数必须与数组2的行数相同”。

套上MMULT，完成这一个公式：

==>MMUL((TRANSPOSE(A2:A19)=E2:E22)*(TRANSPOSE(B2:B19)=F2:F22),(C2:C19))

＝＝＝＝＝＝＝＝＝

下面再来看另一个例子。这次是hahaha3附件的第一部分（链接地址在本帖一楼开头，看其中的3楼，我给出的附件）。可以不看原帖，只需看原先给出的SUM数组公式，我们就在此基础上改写：（那么多英文，看着费劲，'Attendance Record'!改成"R"表吧：）

=SUM(('R'!$B$3:$B$721=$B7)*('R'!$H$3:$H$721=$C$1)*('R'!$E$3:$E$721=$E$4)*'R'!$K$3:$K$721)

根据楼主的要求，其中B7要改为B7:B13，E4要改为E4:G4

好了，我们开始：

脱胎==>('R'!$B$3:$B$721=$B7)*('R'!$H$3:$H$721=$C$1)*('R'!$E$3:$E$721=$E$4)*'R'!$K$3:$K$721

换骨==>(TRANSPOSE('R'!B3:B721)=B7:B13)*(TRANSPOSE('R'!H3:H721)=C1)*('R'!E3:E721=E4:G4)*'R'!K3:K721

裁为两截==>(TRANSPOSE('R'!B3:B721)=B7:B13)*(TRANSPOSE('R'!H3:H721)=C1),('R'!E3:E721=E4:G4)*'R'!K3:K721

穿新衣==>=MMULT((TRANSPOSE('R'!B3:B721)=B7:B13)*(TRANSPOSE('R'!H3:H721)=C1),('R'!E3:E721=E4:G4)*'R'!K3:K721)

OK!

＝＝＝＝＝＝＝＝＝

说了那么多了，这一节该结束了。相信大家通过上面的解说，应该也会做了吧？那就来测试一下。

测试的题目在下面这个帖子的三楼，我给出的附件中：http://club.excelhome.net/viewthread.php?tid=269993&px=0

这次要做的就是楼主没作对的那个，在附件中J7:L13

下面是答案。用鼠标选中下面这个区域就能看到。不过你最好先不要看，你自己做完后再来对一下答案。

我的答案是：

 =MMULT(--(TRANSPOSE('R'!B3:B91)=B7:B13),('R'!E3:E91=J4:L4)*'R'!K3:K91)

或 =MMULT((TRANSPOSE('R'!B3:B91)=B7:B13)*TRANSPOSE('R'!K3:K91),--('R'!E3:E91=J4:L4))

[此贴子已经被作者于2007-10-7 14:11:22编辑过]

czzqb

MMULT真的很快吗？

做多条件求和，MMULT公式是不是比用SUM()数组公式速度快？相信用过的人心里都有数。

为什么会快？按说，MMULT的计算过程比SUMPRODUCT要复杂，步骤要多，再加上大多数时候还要请一个TRANSPOSE来做帮手，速度应该更慢些，可实际并不是这样。

在四海飘零的帖子中曾经说到：“对于单个单元格数组公式，EXCEL将对每个公式进行一次运算（不管这些单元格的公式多么相同）；……而多单元格数组公式就不同了，系统只进行一次运算，就将所有结果一次赋值给各单元格。”

——这就是MMULT公式比SUM数组公式快的秘密所在。所以我在前面说了，对于单个单元格或者不能组成连续区域的单元格，也就是不能使用多单元格数组公式的地方，千万不要使用MMULT，它反而比SUM数组公式更慢，也许慢的多！

实际上，单元格的数量如果不多，用MMULT也有可能得不偿失。

那多少才合适？能不能给出一个临界点？据我所知，目前还不能。它和你的数据源大小有关，也和你的公式数据有关。

那怎么办？

试验。

作出两种公式来，对比试验。哪种快，你就用哪个。

MMULT的限制

MMULT的公式哪里都能用吗？不是。他有很多的限制：

1, 前面说了，不能组成多单元格数组公式的，不要用；

2, 前面还说了，单元格少的，也不好用；

3, 单元格太多了，也不能用。这是EXCEL本身的限制。MMULT()公式结果的单元格数量不能超过5461个。

4, MMULT不能用在非数值的场合。

有这么多的限制，MMULT不是太……了？

不是的。我在这个帖子里只是讲到用MMULT来解决多条件求和的问题，因为这个问题比较好理解，而且有SUM数组公式可以做参照来改写MMULT公式，容易上手一些。其实，MMULT还有很多的用途——

MMULT其他用途

在山菊花超版的帖子《初识MMULT》里，给出了很多的有关MMULT的链接帖子。

大家可以看看那些帖子，有兴趣的，把那些帖子里的问题一个一个做一下（有人把这个叫做“爬帖”）。

＝＝＝＝＝＝

好了，终于写完了。剩下的，自己去看这些帖子吧——

参考资料

山菊花： 初识MMULT

hbhfgh4310：  MMULT简介

四海飘零： 多条件求和新方法

论坛著作《EXCEL实战技巧精粹》技巧271

                                                                                                                                                                                                                                                         .

[此贴子已经被作者于2007-10-29 23:07:18编辑过]

daf2003

waiting

数码电视

不错不错，期待ING

老姜

这个一定要学学

ＺＨＹＺ

哈哈,顶一下,

czzqb:注册日期：2002年12月10日,老资格了,看了你的资料,我们年龄相仿,

佩服你的精神,不少高手已作壁上观了,可惜!!!

山菊花

支持。

little-key

那什么时候完工啊，我好保存一份备份哦

hahaha3

谢楼主的援助，由于资历尚浅，还在＂回味＂中．

在着顶一下！！！