 16 7.57 W czzqb 2007-10-6 10:53 近日看見網(wǎng)友hahahah3的求助帖： 大意：使用SUM()數(shù)組公式進(jìn)行多條件求和，速度很慢，求助更好的方法。我推薦了MMULT()函數(shù)，但是樓主卻有很多不明白。為此我翻了一下論壇的老帖，發(fā)現(xiàn)有很多的人都對這個函數(shù)“打怵”。 所以，我想借用hahaha3朋友的這個帖子做例子，多用點(diǎn)時間，從頭細(xì)說一下這個讓人“雙腿發(fā)抖”的MMULT。希望能讓更多的人明白，能用好這個利器。 看帖入門考試 看帖還要考試？是的。因為MMULT是個比較難以理解的函數(shù)。如果沒有一定的基礎(chǔ)知識，下面說的就不大好理解。 “考試”很簡單，就一題： 問：公式=A1:A3=B1:C1的結(jié)果是幾行幾列的數(shù)組？不要在excel運(yùn)行而直接寫出答案。 答案是三行兩列：={FALSE,FALSE;FALSE,FALSE;FALSE,FALSE} 您的答案對嗎？特別注意核對一下其中的逗號和分號的位置。 如果您的答案不對，那就先把這個題目好好琢磨一下。 答案正確，請跟我來，開講了—— TRANSPOSE()是干什么的？ 說起來很簡單，TRANSPOSE()就是轉(zhuǎn)置。幫助中說：“將一行單元格區(qū)域轉(zhuǎn)置成一列單元格區(qū)域，反之亦然?！?/p> 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) 錯在哪里？ 我們舉個簡化的例子： 你可以用F9來檢驗一下（假定A1=B1,A2=B2,A3=B3），得到的結(jié)果將是：={TRUE;TRUE;TRUE;#N/A;#N/A} A1=B1 就是說兩個數(shù)組(A1:A5和B1:B3)如果同樣是行數(shù)組（EXCEL幫助中說的垂直數(shù)組），那么它的比較是第一數(shù)組的第一個元素和第二數(shù)組的第一個元素比較，第二個和第二個比較……問題是，第4個和第5個和誰比較？你既然沒告訴它，它也就只好告訴你#N/A了。 如果兩個數(shù)組都是列數(shù)組（水平數(shù)組），比較也是類似這樣來進(jìn)行的。 如果我們想比較的兩個數(shù)組，一個是行數(shù)組，一個是列數(shù)組，那就不一樣了?？催@個： A1:A3=B1:C1（假定A1=B1,A2=C1,A3<>B1,A3<>C1） 這個公式會有什么結(jié)果？按F9看看：={TRUE,FALSE;FALSE,TRUE;FALSE,FALSE} 沒有一個錯誤值，只有TRUE和FALSE，說明它們都比較過了。怎么比較的呢？3×2個數(shù)怎么出來6個結(jié)果的（或者說比較了6次）？ 原來比較是這樣進(jìn)行的： A1=B1 ： 好了，現(xiàn)在我們知道了：由于'Attendance Record'!B3:B271和B7:B13都是行數(shù)組，直接比較是比較不出結(jié)果來的。怎么辦？要把其中的一個“轉(zhuǎn)”過來（轉(zhuǎn)置），就是用TRANSPOSE函數(shù)把行數(shù)組變成列數(shù)組，這樣一個行數(shù)組和一個列數(shù)組就能比較了：'Attendance Record'!B3:B271=TRANSPOSE(B7:B13) 原公式的后面一部分倒是對的：'Attendance Record'!E3:E721=J4:L4，等號前面是行數(shù)組，后面是列數(shù)組。 這就是TRANSPOSE的作用。 當(dāng)然，反過來比較，公式也是成立的：TRANSPOSE('Attendance Record'!B3:B271)=B7:B13 那么，在MMULT公式中，TRANSPOSE應(yīng)該怎么安排？ 到底應(yīng)該把TRANSPOSE放在哪里？這和MMULT有密切關(guān)系。這就需要從頭說MMUTL了 下面就來看MMULT()函數(shù) MMULT()函數(shù)是怎樣工作的 先看MMULT()函數(shù)的幫助。 從幫助中，我們可以得到函數(shù)的以下特性： QUOTE: 1,這是個矩陣乘法； 2,MMULT()函數(shù)的兩個參數(shù)都必須是數(shù)組，而且均為數(shù)值； 3,數(shù)組1的列數(shù)必須與數(shù)組2的行數(shù)相同； 4,得到的結(jié)果也是一個矩陣，這個矩陣的行數(shù)＝數(shù)組1的行數(shù)，矩陣的列數(shù)＝數(shù)組2的列數(shù)； 5,不滿足以上2-4條件的公式返回錯誤值#VALUE!
就是說數(shù)組1的第一個元素和數(shù)組2的第一個元素相乘，生成新矩陣的第(1,1)個元素； 這也是幫助中的那個復(fù)雜的公式的含義： QUOTE: 特別感謝qygszlb朋友給出的這個完整的多行多列矩陣乘法的說明圖：
從上面的例子里，我們知道： 1,為什么兩個參數(shù)都必須是數(shù)值（特性2）？因為MMULT實際是在做乘法運(yùn)算，而文本是不能相乘的。就連文本型數(shù)字和邏輯值也不行。其他一些情況下，EXCEL會把文本型數(shù)字和邏輯值變成數(shù)字參與運(yùn)算，比如這兩個公式='4'*3=12, =TRUE+1=2, 但MMULT()不會做這樣的轉(zhuǎn)換。 2,幫助中的這個公式值得我們牢記： 這個公式實際上就是上面說到的MMULT特性3,4的數(shù)學(xué)表達(dá)方式。如果能讀懂、記住這個公式，MMULT的使用就很方便了。 我們希望得到什么？希望得到a(i,j)，就是一個i行j列的矩陣；要求是什么？它要求MMULT第一個參數(shù)是i行的，而第二個參數(shù)是j列的；而且第一參數(shù)的列數(shù)和第二參數(shù)的行數(shù)相等(K) 或者，我們從另一個角度來簡單地理解并記?。合瓤纯次覀円玫绞裁矗咳绻覀兿Ｍ玫?i 行 j 列的結(jié)果，那就要構(gòu)造兩個矩陣，第一個要 i 行的，第二個要 j 列的 這樣說，是不是還顯得太抽象了？那下面我們就結(jié)合一個實際的多條件求和的例子來看?？蠢拥臅r候，我們還會反復(fù)說到上面提到的這些。 [此貼子已經(jīng)被作者于2007-10-29 13:04:44編輯過]  分享到新浪微博  相關(guān)帖子 L 2樓 czzqb 2007-10-6 10:54 用MMULT做多條件求和 在看實例之前，我們先記住這一條：用MMULT求解多條件求和問題，一定要使用多單元格數(shù)組公式。也就是說，它的結(jié)果必須是連續(xù)的多個，越多越好。如果只是單個的，或者雖然是多個卻不連續(xù)、無法使用多單元格數(shù)組公式的情況，那就千萬不要用MMULT！為什么？先別問，記住它，以后再來說為什么。 看這個例子（見四海飄零的《多條件求和的新方法》第二個工作表http://club./viewthread.php?tid=170501&replyID=&skin=0） 左邊是源數(shù)據(jù)，要求在右邊的表格里得出對應(yīng)XX和YY的所有匯總結(jié)果
那么，用MMULT該怎么做？ 現(xiàn)在我們就來開始寫。 最簡單的辦法是利用上面的SUMPRODUCT公式。第一步，把公式的內(nèi)容提取出來： ==> ($A$2:$A$19=E2)*($B$2:$B$19=F2)*($C$2:$C$19) 先把其中的$去掉（既然我們要使用多單元格數(shù)組公式，就沒有必要用$了。雖然$留著也不算錯） ==> (A2:A19=E2)*(B2:B19=F2)*(C2:C19) 作為多單元格數(shù)組公式，就不能使用一個單一的條件E2/F2 ，而應(yīng)該是一串：E2:E22/F2:F22。把它寫進(jìn)去： ==>(A2:A19=E2:E22)*(B2:B19=F2:F22)*(C2:C19) 這樣的公式當(dāng)然不對，這一點(diǎn)，我們開始的時候就說過了。A2:A19和E2:E22是同一個方向的，都是行數(shù)組，這不行。改為： ==>(A2:A19=TRANSPOSE(E2:E22))*(B2:B19=TRANSPOSE(F2:F22))*(C2:C19) 下面我們該給它拆開成兩個，為MMULT做準(zhǔn)備了。 首先分析一下，這里的三個數(shù)組各是幾行幾列的？ (A2:A19=TRANSPOSE(E2:E22))：18行21列 (B2:B19=TRANSPOSE(F2:F22))：18行21列 (C2:C19):18行1列 前面我們說過，“如果我們希望得到i行j列的結(jié)果，那就要構(gòu)造兩個矩陣，一個要 i 行，一個要 j 列” 注意，我們想得到的是21行1列的新數(shù)組（G2:G22)，也就是i=21, j=1 可是這三個數(shù)組不論怎么安排，都沒有21行的。問題出在哪里？出在TRANSPOSE()的安排上。原來是前面把轉(zhuǎn)置寫錯地方了。換一下看看： (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): 這里用逗號把三個數(shù)組“裁”為兩截：前一截為21行18列；后一截為18行1列 這才符合了MMULT的要求：新的結(jié)果數(shù)組為21行1列 ，而且數(shù)組1是18列，數(shù)組2是18行，二者正好匹配符合特性2:“數(shù)組1的列數(shù)必須與數(shù)組2的行數(shù)相同”。 套上MMULT，完成這一個公式： ==>MMUL((TRANSPOSE(A2:A19)=E2:E22)*(TRANSPOSE(B2:B19)=F2:F22),(C2:C19)) ＝＝＝＝＝＝＝＝＝ 下面再來看另一個例子。這次是hahaha3附件的第一部分（鏈接地址在本帖一樓開頭，看其中的3樓，我給出的附件）?？梢圆豢丛恍杩丛冉o出的SUM數(shù)組公式，我們就在此基礎(chǔ)上改寫：（那么多英文，看著費(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 好了，我們開始： 脫胎==>('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! ＝＝＝＝＝＝＝＝＝ 說了那么多了，這一節(jié)該結(jié)束了。相信大家通過上面的解說，應(yīng)該也會做了吧？那就來測試一下。 測試的題目在下面這個帖子的三樓，我給出的附件中：http://club./viewthread.php?tid=269993&px=0 這次要做的就是樓主沒作對的那個，在附件中J7:L13 下面是答案。用鼠標(biāo)選中下面這個區(qū)域就能看到。不過你最好先不要看，你自己做完后再來對一下答案。 我的答案是： =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)) [此貼子已經(jīng)被作者于2007-10-7 14:11:22編輯過] L 3樓 czzqb 2007-10-6 10:55 MMULT真的很快嗎？ 做多條件求和，MMULT公式是不是比用SUM()數(shù)組公式速度快？相信用過的人心里都有數(shù)。 為什么會快？按說，MMULT的計算過程比SUMPRODUCT要復(fù)雜，步驟要多，再加上大多數(shù)時候還要請一個TRANSPOSE來做幫手，速度應(yīng)該更慢些，可實際并不是這樣。 在四海飄零的帖子中曾經(jīng)說到：“對于單個單元格數(shù)組公式，EXCEL將對每個公式進(jìn)行一次運(yùn)算（不管這些單元格的公式多么相同）；……而多單元格數(shù)組公式就不同了，系統(tǒng)只進(jìn)行一次運(yùn)算，就將所有結(jié)果一次賦值給各單元格。” ——這就是MMULT公式比SUM數(shù)組公式快的秘密所在。所以我在前面說了，對于單個單元格或者不能組成連續(xù)區(qū)域的單元格，也就是不能使用多單元格數(shù)組公式的地方，千萬不要使用MMULT，它反而比SUM數(shù)組公式更慢，也許慢的多！ 實際上，單元格的數(shù)量如果不多，用MMULT也有可能得不償失。 那多少才合適？能不能給出一個臨界點(diǎn)？據(jù)我所知，目前還不能。它和你的數(shù)據(jù)源大小有關(guān)，也和你的公式數(shù)據(jù)有關(guān)。 那怎么辦？ 試驗。 作出兩種公式來，對比試驗。哪種快，你就用哪個。 MMULT的限制 MMULT的公式哪里都能用嗎？不是。他有很多的限制： 1, 前面說了，不能組成多單元格數(shù)組公式的，不要用； 2, 前面還說了，單元格少的，也不好用； 3, 單元格太多了，也不能用。這是EXCEL本身的限制。MMULT()公式結(jié)果的單元格數(shù)量不能超過5461個。 4, MMULT不能用在非數(shù)值的場合。 有這么多的限制，MMULT不是太……了？ 不是的。我在這個帖子里只是講到用MMULT來解決多條件求和的問題，因為這個問題比較好理解，而且有SUM數(shù)組公式可以做參照來改寫MMULT公式，容易上手一些。其實，MMULT還有很多的用途—— MMULT其他用途 在山菊花超版的帖子《初識MMULT》里，給出了很多的有關(guān)MMULT的鏈接帖子。 大家可以看看那些帖子，有興趣的，把那些帖子里的問題一個一個做一下（有人把這個叫做“爬帖”）。 ＝＝＝＝＝＝ 好了，終于寫完了。剩下的，自己去看這些帖子吧—— 參考資料 山菊花： 初識MMULT hbhfgh4310： MMULT簡介 四海飄零： 多條件求和新方法 論壇著作《EXCEL實戰(zhàn)技巧精粹》技巧271 . [此貼子已經(jīng)被作者于2007-10-29 23:07:18編輯過] 7 7樓 ＺＨＹＺ 2007-10-6 12:27 哈哈,頂一下, czzqb:注冊日期：2002年12月10日,老資格了,看了你的資料,我們年齡相仿, 佩服你的精神,不少高手已作壁上觀了,可惜!!! M 9樓 little-key 2007-10-6 12:46 那什么時候完工啊，我好保存一份備份哦 |
|
|
