給定單元格區(qū)域A1:D5（其中每個單元格中都是整數(shù)，并且在該單元格區(qū)域內(nèi)是唯一的），使用單個公式生成一個數(shù)組，該數(shù)組由該區(qū)域中所有連續(xù)的數(shù)值組成，連續(xù)的數(shù)值至少包含兩個，且返回的數(shù)組中的元素按從小到大的順序排列。
圖1
也就是說，可以返回結(jié)果：
{1;2;3;12;13;14;15;16;17;36;37}
也可以返回結(jié)果：
{1,2,3,12,13,14,15,16,17,36,37}
注意，所給出的公式應(yīng)包含最少的字符；在公式中必須同時包含行列引用，不允許全是列引用（如A:D）或行引用（如1:5）；公式中不允許使用名稱。
先不看答案，自已動手試一試。
公式
公式1：
=MODE.MULT(SMALL(A1:D5,ROW(A1:A20)),IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5))
使用了91個字符。
公式2：
=MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))
使用了103個字符。
嘗試通過同時評估兩個COUNTIF構(gòu)造來縮短此公式構(gòu)造-并消除了對IFERROR子句的需要，但最終比上述公式稍長一些，有105字符，即公式3：
=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),{1,1}*SMALL(A1:D5,ROW(A1:A20))))
或者公式4：
=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),SMALL(A1:D5,ROW(A1:A20))),A1:D5)
公式解析
由于我們想要從所給區(qū)域中返回一個數(shù)組，該數(shù)組由區(qū)域內(nèi)至少兩個連續(xù)的數(shù)值構(gòu)成，因此，執(zhí)行此操作的一種方法是針對區(qū)域內(nèi)的每個值檢查是否在該區(qū)域內(nèi)還會發(fā)現(xiàn)比該值大1或小1的值。
這樣，我們需要查看下面兩種構(gòu)造的結(jié)果：
COUNTIF(A1:D5,A1:D5+1)
和
COUNTIF(A1:D5,A1:D5-1)
由于我們要求對于每個值，其兩個返回值中只有一個為非零值即可，因此可以通過簡單地將上述構(gòu)造加在一起，這等價于OR操作。于是，公式中的：
COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)
轉(zhuǎn)換為：
{0,0,1,0;0,0,0,0;1,1,0,1;1,0,1,1;1,0,0,0}+{0,1,1,0;0,0,0,1;0,1,1,1;1,0,0,0;1,0,0,0}
結(jié)果為：
{0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0}
從中可以看出，元素0表示：在區(qū)域中未找到比要查找的值大1或小1的值。同樣，元素1表示：在區(qū)域中找到了比要查找的值大1或小1的值。值2表示在區(qū)域中找到了比要查找的值大1的值和小1的值。因此：
IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5)
轉(zhuǎn)換為：
IF({0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0},A1:D5)
得到：
{FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE}
現(xiàn)在，我們已經(jīng)生成了所需的值。但是，仍然存在兩項工作要解決：按升序?qū)λ鼈冞M行排序以及刪除任何非數(shù)字元素。
使用SMALL函數(shù)來完成排序操作：
SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))
轉(zhuǎn)換為：
SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},ROW(A1:A20))
轉(zhuǎn)換為：
SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})
結(jié)果為：
{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
通常，我們可能試圖通過構(gòu)造一個合適的數(shù)組傳遞給SMALL函數(shù)作為其參數(shù)k的值來縮小數(shù)組，然而，這里使用了MODE.MULT函數(shù)，更簡潔。注意，這里使用的縮小數(shù)組的技術(shù)，當我們希望返回的元素在被處理的數(shù)組中出現(xiàn)的頻率相等時才合適。此外，該頻率必須至少為兩倍，因為如果沒有一個以上的值出現(xiàn)，那么MODE.MULT不會返回任何值。
因此，先與數(shù)組{1,1}相乘的原因是，當我們將SMALL構(gòu)造（一個20行1列的數(shù)組）與數(shù)組{1,1}（一個1行2列的數(shù)組）相乘時，生成20行2列的數(shù)組，其每行中列的元素都是相同的。這樣：
SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1}
轉(zhuǎn)換為：
{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}*{1,1}
結(jié)果為：
{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!}
可以看出，我們已經(jīng)成功地使每個元素的數(shù)量簡單地翻了一倍，現(xiàn)在可以安全地將其傳遞給MODE.MULT函數(shù)。但是，我們先要消除這些錯誤值。像絕大多數(shù)函數(shù)一樣，MODE.MULT函數(shù)不會處理包含此類錯誤值的數(shù)組。
這樣，公式中的：
IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””)
轉(zhuǎn)換為：
IFERROR({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!},””)
得到：
{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””}
最后，將其傳遞給MODE.MULT函數(shù)：
MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))
轉(zhuǎn)換為：
MODE.MULT({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””})
得到：
{1;2;3;12;13;14;15;16;17;36;37}
小結(jié)
MODE.MULT函數(shù)的妙用！
