16 7.57 W czzqb 2007-10-6 10:53

近日看見(jiàn)網(wǎng)友hahahah3的求助帖：

[求助]除數(shù)組公式外 多條件匯總
 

大意：使用SUM()數(shù)組公式進(jìn)行多條件求和，速度很慢，求助更好的方法。我推薦了MMULT()函數(shù)，但是樓主卻有很多不明白。為此我翻了一下論壇的老帖，發(fā)現(xiàn)有很多的人都對(duì)這個(gè)函數(shù)“打怵”。

所以，我想借用hahaha3朋友的這個(gè)帖子做例子，多用點(diǎn)時(shí)間，從頭細(xì)說(shuō)一下這個(gè)讓人“雙腿發(fā)抖”的MMULT。希望能讓更多的人明白，能用好這個(gè)利器。

看帖入門(mén)考試

看帖還要考試？是的。因?yàn)镸MULT是個(gè)比較難以理解的函數(shù)。如果沒(méi)有一定的基礎(chǔ)知識(shí)，下面說(shuō)的就不大好理解。

“考試”很簡(jiǎn)單，就一題：

 

問(wèn)：公式=A1:A3=B1:C1的結(jié)果是幾行幾列的數(shù)組？不要在excel運(yùn)行而直接寫(xiě)出答案。

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

您的答案對(duì)嗎？特別注意核對(duì)一下其中的逗號(hào)和分號(hào)的位置。

如果您的答案不對(duì)，那就先把這個(gè)題目好好琢磨一下。

答案正確，請(qǐng)跟我來(lái)，開(kāi)講了——

---

TRANSPOSE()是干什么的？

說(shuō)起來(lái)很簡(jiǎn)單，TRANSPOSE()就是轉(zhuǎn)置。幫助中說(shuō)：“將一行單元格區(qū)域轉(zhuǎn)置成一列單元格區(qū)域，反之亦然?！?/p>

MMULT和TRANSPOSE就像是一對(duì)鐵哥們，MMULT走到哪里，TRANSPOSE就跟到哪里。它在MMULT里干些什么？

我們從hahaha3朋友做的公式說(shuō)起。這個(gè)公式是：

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

這個(gè)公式的第一部分就錯(cuò)了('Attendance Record'!B3:B271=B7:B13)

錯(cuò)在哪里？

我們舉個(gè)簡(jiǎn)化的例子：
  A1:A5=B1:B3
這個(gè)公式能得到想要的結(jié)果嗎？不能。

你可以用F9來(lái)檢驗(yàn)一下（假定A1=B1,A2=B2,A3=B3），得到的結(jié)果將是：={TRUE;TRUE;TRUE;#N/A;#N/A}
注意后面有兩個(gè)#N/A。為什么？原來(lái)這個(gè)公式是這樣比較的：

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

就是說(shuō)兩個(gè)數(shù)組(A1:A5和B1:B3)如果同樣是行數(shù)組（EXCEL幫助中說(shuō)的垂直數(shù)組），那么它的比較是第一數(shù)組的第一個(gè)元素和第二數(shù)組的第一個(gè)元素比較，第二個(gè)和第二個(gè)比較……問(wèn)題是，第4個(gè)和第5個(gè)和誰(shuí)比較？你既然沒(méi)告訴它，它也就只好告訴你#N/A了。

如果兩個(gè)數(shù)組都是列數(shù)組（水平數(shù)組），比較也是類似這樣來(lái)進(jìn)行的。

如果我們想比較的兩個(gè)數(shù)組，一個(gè)是行數(shù)組，一個(gè)是列數(shù)組，那就不一樣了。看這個(gè)：

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

這個(gè)公式會(huì)有什么結(jié)果？按F9看看：={TRUE,FALSE;FALSE,TRUE;FALSE,FALSE}

沒(méi)有一個(gè)錯(cuò)誤值，只有TRUE和FALSE，說(shuō)明它們都比較過(guò)了。怎么比較的呢？3×2個(gè)數(shù)怎么出來(lái)6個(gè)結(jié)果的（或者說(shuō)比較了6次）？

原來(lái)比較是這樣進(jìn)行的：

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

：    

好了，現(xiàn)在我們知道了：由于'Attendance Record'!B3:B271和B7:B13都是行數(shù)組，直接比較是比較不出結(jié)果來(lái)的。怎么辦？要把其中的一個(gè)“轉(zhuǎn)”過(guò)來(lái)（轉(zhuǎn)置），就是用TRANSPOSE函數(shù)把行數(shù)組變成列數(shù)組，這樣一個(gè)行數(shù)組和一個(gè)列數(shù)組就能比較了：

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

原公式的后面一部分倒是對(duì)的：'Attendance Record'!E3:E721=J4:L4，等號(hào)前面是行數(shù)組，后面是列數(shù)組。

這就是TRANSPOSE的作用。

當(dāng)然，反過(guò)來(lái)比較，公式也是成立的：TRANSPOSE('Attendance Record'!B3:B271)=B7:B13

那么，在MMULT公式中，TRANSPOSE應(yīng)該怎么安排？ 到底應(yīng)該把TRANSPOSE放在哪里？這和MMULT有密切關(guān)系。這就需要從頭說(shuō)MMUTL了

下面就來(lái)看MMULT()函數(shù)

MMULT()函數(shù)是怎樣工作的

先看MMULT()函數(shù)的幫助。

從幫助中，我們可以得到函數(shù)的以下特性：

那么什么是矩陣乘法？這是高等數(shù)學(xué)里線性代數(shù)的一個(gè)概念。我們?cè)囋囃ㄟ^(guò)一個(gè)簡(jiǎn)單的例子來(lái)理解它：

         

 就是說(shuō)數(shù)組1的第一個(gè)元素和數(shù)組2的第一個(gè)元素相乘，生成新矩陣的第(1,1)個(gè)元素；
                  數(shù)組1的第一個(gè)元素和數(shù)組2的第二個(gè)元素相乘，生成新矩陣的第(1,2)個(gè)元素；
                  ……

這也是幫助中的那個(gè)復(fù)雜的公式的含義：

         

QUOTE:

特別感謝qygszlb朋友給出的這個(gè)完整的多行多列矩陣乘法的說(shuō)明圖：

 

從上面的例子里，我們知道：

1,為什么兩個(gè)參數(shù)都必須是數(shù)值（特性2）？因?yàn)镸MULT實(shí)際是在做乘法運(yùn)算，而文本是不能相乘的。就連文本型數(shù)字和邏輯值也不行。其他一些情況下，EXCEL會(huì)把文本型數(shù)字和邏輯值變成數(shù)字參與運(yùn)算，比如這兩個(gè)公式='4'*3=12, =TRUE+1=2, 但MMULT()不會(huì)做這樣的轉(zhuǎn)換。

2,幫助中的這個(gè)公式值得我們牢記：

      

這個(gè)公式實(shí)際上就是上面說(shuō)到的MMULT特性3,4的數(shù)學(xué)表達(dá)方式。如果能讀懂、記住這個(gè)公式，MMULT的使用就很方便了。

我們希望得到什么？希望得到a(i,j)，就是一個(gè)i行j列的矩陣；要求是什么？它要求MMULT第一個(gè)參數(shù)是i行的，而第二個(gè)參數(shù)是j列的；而且第一參數(shù)的列數(shù)和第二參數(shù)的行數(shù)相等(K)

或者，我們從另一個(gè)角度來(lái)簡(jiǎn)單地理解并記?。合瓤纯次覀円玫绞裁?？如果我們希望得到 i 行 j 列的結(jié)果，那就要構(gòu)造兩個(gè)矩陣，第一個(gè)要 i 行的，第二個(gè)要 j 列的

這樣說(shuō)，是不是還顯得太抽象了？那下面我們就結(jié)合一個(gè)實(shí)際的多條件求和的例子來(lái)看?？蠢拥臅r(shí)候，我們還會(huì)反復(fù)說(shuō)到上面提到的這些。

分享到新浪微博

L 2樓 czzqb 2007-10-6 10:54

用MMULT做多條件求和

在看實(shí)例之前，我們先記住這一條：用MMULT求解多條件求和問(wèn)題，一定要使用多單元格數(shù)組公式。也就是說(shuō)，它的結(jié)果必須是連續(xù)的多個(gè)，越多越好。如果只是單個(gè)的，或者雖然是多個(gè)卻不連續(xù)、無(wú)法使用多單元格數(shù)組公式的情況，那就千萬(wàn)不要用MMULT！為什么？先別問(wèn)，記住它，以后再來(lái)說(shuō)為什么。

看這個(gè)例子（見(jiàn)四海飄零的《多條件求和的新方法》第二個(gè)工作表http://club./viewthread.php?tid=170501&replyID=&skin=0）

 

左邊是源數(shù)據(jù)，要求在右邊的表格里得出對(duì)應(yīng)XX和YY的所有匯總結(jié)果
用SUMPRODUCT（或SUM數(shù)組公式），我們都會(huì)寫(xiě)了：

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

那么，用MMULT該怎么做？
首先要做的就是，判斷一下是否符合使用MMULT的基本條件？我們要求的結(jié)果是在G2:G22是一個(gè)連續(xù)的區(qū)域。這就有戲了！

現(xiàn)在我們就來(lái)開(kāi)始寫(xiě)。

最簡(jiǎn)單的辦法是利用上面的SUMPRODUCT公式。第一步，把公式的內(nèi)容提取出來(lái)：

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

先把其中的$去掉（既然我們要使用多單元格數(shù)組公式，就沒(méi)有必要用$了。雖然$留著也不算錯(cuò)）

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

作為多單元格數(shù)組公式，就不能使用一個(gè)單一的條件E2/F2 ，而應(yīng)該是一串：E2:E22/F2:F22。把它寫(xiě)進(jìn)去：

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

這樣的公式當(dāng)然不對(duì)，這一點(diǎn)，我們開(kāi)始的時(shí)候就說(shuō)過(guò)了。A2:A19和E2:E22是同一個(gè)方向的，都是行數(shù)組，這不行。改為：

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

下面我們?cè)摻o它拆開(kāi)成兩個(gè)，為MMULT做準(zhǔn)備了。

首先分析一下，這里的三個(gè)數(shù)組各是幾行幾列的？

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

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

(C2:C19):18行1列

前面我們說(shuō)過(guò)，“如果我們希望得到i行j列的結(jié)果，那就要構(gòu)造兩個(gè)矩陣，一個(gè)要 i 行，一個(gè)要 j 列”

注意，我們想得到的是21行1列的新數(shù)組（G2:G22)，也就是i=21, j=1

可是這三個(gè)數(shù)組不論怎么安排，都沒(méi)有21行的。問(wèn)題出在哪里？出在TRANSPOSE()的安排上。原來(lái)是前面把轉(zhuǎn)置寫(xiě)錯(cuò)地方了。換一下看看：

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

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

這樣不是就符合要求了嗎？所以整個(gè)公式就是

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

這里用逗號(hào)把三個(gè)數(shù)組“裁”為兩截：前一截為21行18列；后一截為18行1列

這才符合了MMULT的要求：新的結(jié)果數(shù)組為21行1列     ，而且數(shù)組1是18列，數(shù)組2是18行，二者正好匹配符合特性2:“數(shù)組1的列數(shù)必須與數(shù)組2的行數(shù)相同”。

套上MMULT，完成這一個(gè)公式：

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

＝＝＝＝＝＝＝＝＝

下面再來(lái)看另一個(gè)例子。這次是hahaha3附件的第一部分（鏈接地址在本帖一樓開(kāi)頭，看其中的3樓，我給出的附件）?？梢圆豢丛?，只需看原先給出的SUM數(shù)組公式，我們就在此基礎(chǔ)上改寫(xiě)：（那么多英文，看著費(fèi)勁，'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)

根據(jù)樓主的要求，其中B7要改為B7:B13，E4要改為E4:G4

好了，我們開(kāi)始：

脫胎==>('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!

＝＝＝＝＝＝＝＝＝

說(shuō)了那么多了，這一節(jié)該結(jié)束了。相信大家通過(guò)上面的解說(shuō)，應(yīng)該也會(huì)做了吧？那就來(lái)測(cè)試一下。

測(cè)試的題目在下面這個(gè)帖子的三樓，我給出的附件中：http://club./viewthread.php?tid=269993&px=0

這次要做的就是樓主沒(méi)作對(duì)的那個(gè)，在附件中J7:L13

下面是答案。用鼠標(biāo)選中下面這個(gè)區(qū)域就能看到。不過(guò)你最好先不要看，你自己做完后再來(lái)對(duì)一下答案。

我的答案是：

 =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))

L 3樓 czzqb 2007-10-6 10:55

MMULT真的很快嗎？

做多條件求和，MMULT公式是不是比用SUM()數(shù)組公式速度快？相信用過(guò)的人心里都有數(shù)。

為什么會(huì)快？按說(shuō)，MMULT的計(jì)算過(guò)程比SUMPRODUCT要復(fù)雜，步驟要多，再加上大多數(shù)時(shí)候還要請(qǐng)一個(gè)TRANSPOSE來(lái)做幫手，速度應(yīng)該更慢些，可實(shí)際并不是這樣。

在四海飄零的帖子中曾經(jīng)說(shuō)到：“對(duì)于單個(gè)單元格數(shù)組公式，EXCEL將對(duì)每個(gè)公式進(jìn)行一次運(yùn)算（不管這些單元格的公式多么相同）；……而多單元格數(shù)組公式就不同了，系統(tǒng)只進(jìn)行一次運(yùn)算，就將所有結(jié)果一次賦值給各單元格?！?/p>

——這就是MMULT公式比SUM數(shù)組公式快的秘密所在。所以我在前面說(shuō)了，對(duì)于單個(gè)單元格或者不能組成連續(xù)區(qū)域的單元格，也就是不能使用多單元格數(shù)組公式的地方，千萬(wàn)不要使用MMULT，它反而比SUM數(shù)組公式更慢，也許慢的多！

實(shí)際上，單元格的數(shù)量如果不多，用MMULT也有可能得不償失。

那多少才合適？能不能給出一個(gè)臨界點(diǎn)？據(jù)我所知，目前還不能。它和你的數(shù)據(jù)源大小有關(guān)，也和你的公式數(shù)據(jù)有關(guān)。

那怎么辦？

試驗(yàn)。

作出兩種公式來(lái)，對(duì)比試驗(yàn)。哪種快，你就用哪個(gè)。

MMULT的限制

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

1, 前面說(shuō)了，不能組成多單元格數(shù)組公式的，不要用；

2, 前面還說(shuō)了，單元格少的，也不好用；

3, 單元格太多了，也不能用。這是EXCEL本身的限制。MMULT()公式結(jié)果的單元格數(shù)量不能超過(guò)5461個(gè)。

4, MMULT不能用在非數(shù)值的場(chǎng)合。

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

不是的。我在這個(gè)帖子里只是講到用MMULT來(lái)解決多條件求和的問(wèn)題，因?yàn)檫@個(gè)問(wèn)題比較好理解，而且有SUM數(shù)組公式可以做參照來(lái)改寫(xiě)MMULT公式，容易上手一些。其實(shí)，MMULT還有很多的用途——

MMULT其他用途

在山菊花超版的帖子《初識(shí)MMULT》里，給出了很多的有關(guān)MMULT的鏈接帖子。

大家可以看看那些帖子，有興趣的，把那些帖子里的問(wèn)題一個(gè)一個(gè)做一下（有人把這個(gè)叫做“爬帖”）。

＝＝＝＝＝＝

好了，終于寫(xiě)完了。剩下的，自己去看這些帖子吧——

參考資料

山菊花： 初識(shí)MMULT

hbhfgh4310：  MMULT簡(jiǎn)介

四海飄零： 多條件求和新方法

論壇著作《EXCEL實(shí)戰(zhàn)技巧精粹》技巧271

                                                                                                                                                                                                                                                         .

2 4樓 daf2003 2007-10-6 11:35

回復(fù)：（czzqb）占位

waiting

2 5樓 數(shù)碼電視 2007-10-6 12:06

不錯(cuò)不錯(cuò)，期待ING

2 6樓 老姜 2007-10-6 12:25

這個(gè)一定要學(xué)學(xué)

7 7樓 ＺＨＹＺ 2007-10-6 12:27

哈哈,頂一下,

M 8樓 山菊花 2007-10-6 12:34

支持。

M 9樓 little-key 2007-10-6 12:46

那什么時(shí)候完工啊，我好保存一份備份哦

2 10樓 hahaha3 2007-10-6 12:55

謝樓主的援助，由于資歷尚淺，還在＂回味＂中．

在著頂一下?。?！