大家好,又見面了，今天說說數(shù)組公式有關(guān)的內(nèi)容。

眾所周知，數(shù)組公式是需要以ctrl+shift+enter三鍵結(jié)束公式，自動在公式兩側(cè)自動生成一組{ }表明公式進(jìn)行的是數(shù)組運算。

論壇和群里經(jīng)常有問題大佬們已經(jīng)寫出正確公式，但是由于題主不會數(shù)組三鍵造成不能正常返回結(jié)果的，這個遭遇你有沒有碰到過呢?

數(shù)組三鍵是新手寫公式的一個重要門檻。所以由于公式的操作難度，適用性等需求，總能在群里看到這種問題:“有沒有不要三鍵的去重公式”,“有沒有不要三鍵的一對多公式”等等。

此時通常大伙會告訴你，數(shù)組公式都是要三鍵的，不過，其實在excel里有些函數(shù)本身是支持?jǐn)?shù)組運算的，只要善于應(yīng)用這類函數(shù)，很多數(shù)組公式是可以在不使用三鍵時正確返回結(jié)果的，這篇就是介紹其中的一些常見案例。

本篇的公式雖然全部都可以不使用數(shù)組三鍵,但都屬于數(shù)組運算,數(shù)據(jù)量大時不推薦公式解決,僅作為函數(shù)學(xué)習(xí)的一類拓展應(yīng)用。

1,系統(tǒng)導(dǎo)出的數(shù)據(jù)求和

很多軟件導(dǎo)出的數(shù)字并不是數(shù)值型而是文本型，而且可能摻雜一些非打印字符。

對這些干擾運算的字符,通?？梢杂梅至?替換等功能先處理再進(jìn)行求和，如果不想處理數(shù)據(jù)或者不清楚如何處理,可以直接借鑒這個公式快速求和。

=SUMPRODUCT(NUMBERVALUE(B2:B8))

Sumproduct不用多說了吧,本身支持?jǐn)?shù)組運算的,2013及以上版本可以使用：

=SUMPRODUCT(NUMBERVALUE(求和區(qū)域))

NUMBERVALUE函數(shù)可以直接清除數(shù)字兩端的不可見字符而且能把空格與空文本等直接轉(zhuǎn)化為0計算。

2,多條件匹配

這個公式是針對VLOOKUP+IF({1,0}結(jié)構(gòu)的逆向查詢的,使用這個解法運算量大且新手很難理解且需要三鍵

因此這類問題現(xiàn)在一般都是推薦使用LOOKUP(1,0/(條件1)/(條件2)……的方式解決,咱們的公眾號里已經(jīng)介紹過很多次這個函數(shù)了,所以這里不再詳細(xì)介紹。

3,提取不重復(fù)內(nèi)容

這是這類需求里最常見的,前兩個解法只是在常規(guī)公式里加了個函數(shù),就能使整個公式不用三鍵即可運行。

解法1   

=INDEX(A:A,MATCH(0,INDEX(COUNTIF(C$1:C1,A$2:A13),0,1),0)+1)&''  

解法2   

=INDEX(A:A,MATCH(0,MMULT(COUNTIF(C$1:C1,A$2:A13),1),0)+1)&''   

解法3   

=LOOKUP(1,0/FREQUENCY(1,--ISNA(MATCH(A$2:A13,C$1:C1,0))),A$2:A13)&''   

解法4  

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$13)/(MATCH(A$2:A$13,A$2:A$13,0)=(ROW($2:$15)-1)),ROW(A1))),'') 

4,一對多查找

第3個案例已經(jīng)出現(xiàn)了一個函數(shù)mmult,在這類需求里你可以把這函數(shù)看成這類問題最強的催化劑。

所謂最強,是因為以萬金油為例,index+small+if+row(/column) 組合里我試過很多方式,唯有row函數(shù)無法被替代,因為row可以返回指定的序列數(shù)的內(nèi)存數(shù)組，但前提是使用數(shù)組三鍵。

數(shù)組運算里row(/column)經(jīng)常是整個數(shù)組運算需要三鍵的重要因素，因此,很多公式核心就是使row在不三鍵前提下成功返回內(nèi)存數(shù)組，而這個需求,使用mmult即可做到。

即很多時候由于row限制必須使用三鍵才能正確返回的公式，我們只需要在row(/column)外面加一個mmult就能正確返回結(jié)果，而正是這類公式下如果添加這個函數(shù)就正確,不添加就錯誤的特性，我才把這類函數(shù)稱之為數(shù)組運算的'催化劑'。

借助這個函數(shù)列個不用三鍵的萬金油一對多解法：

利用MMULT的變種萬金油很容易實現(xiàn)不使用三鍵的一對多公式：

=INDEX(B:B,SMALL(--TEXT(MMULT((A$8:A$17=D$8)*ROW($8:$17),1),'0;;99'),ROW(A1)))&''

當(dāng)然之前介紹的AGGREGATE函數(shù)也是可以的：

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($8:$17)/(A$8:A$17=D$8),ROW(A1))),'') 

5,合并單元格區(qū)域?qū)?yīng)最大值

這類合并單元格的函數(shù)問題,才是這類不使用三鍵函數(shù)公式應(yīng)用的最大原因，因為合并單元格結(jié)構(gòu)是不能直接三鍵的。

而函數(shù)愛好者的目的之一，就是把各種看似不可能直接實現(xiàn)的函數(shù)問題用一個公式直接實現(xiàn)：

解法1   

=MAX(OFFSET(A2,,,MODE((A3:B11='')%+ROW($3:$11)-2)))

解法1變種   

=MAX(A2:INDEX(A:A,MODE((A3:B11='')%+ROW(2:10)))) 

解法2   

=MAX(OFFSET(A2,,,MATCH(1,FREQUENCY(1,N(B3:B12<>'')),0))) 

解法3   

=MAX(OFFSET(A2,,,MDETERM(MATCH(1=0,B3:B$12='',-1)))

解法4

=MAX(OFFSET(A2,,,IFERROR(MATCH(1,MMULT(N(B3:B11<>''),1),0),ROWS(2:$11))))

解法4為最常規(guī)思路,因為合并單元格結(jié)構(gòu)的難點為定位最后1個區(qū)域的大小,這里直接match錯位定位后用iferror處理最后1個區(qū)域，如果下方無其他數(shù)據(jù),rows可以直接簡化為一個足夠大的數(shù)9/99等等,區(qū)域固定的話直接為最后1個合并區(qū)域單元格數(shù)量即可。

而這里的mmult也是整個公式不需要三鍵的最大功臣，解法2-解法4只引用公式所在列,不需要其他列配合。

注意這些公式，只要把max換成avregare，median等就能計算對應(yīng)合并區(qū)域的平均值，中位數(shù)等等。

今天列舉的公式都是不需要三鍵但都屬于數(shù)組運算的,希望對你有所幫助。