給定單元格區域A1:D5（其中每個單元格中都是整數，并且在該單元格區域內是唯一的），使用單個公式生成一個數組，該數組由該區域中所有連續的數值組成，連續的數值至少包含兩個，且返回的數組中的元素按從小到大的順序排列。
圖1
也就是說，可以返回結果：
{1;2;3;12;13;14;15;16;17;36;37}
也可以返回結果：
{1,2,3,12,13,14,15,16,17,36,37}
注意，所給出的公式應包含最少的字符；在公式中必須同時包含行列引用，不允許全是列引用（如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構造來縮短此公式構造-并消除了對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)
公式解析
由于我們想要從所給區域中返回一個數組，該數組由區域內至少兩個連續的數值構成，因此，執行此操作的一種方法是針對區域內的每個值檢查是否在該區域內還會發現比該值大1或小1的值。
這樣，我們需要查看下面兩種構造的結果：
COUNTIF(A1:D5,A1:D5+1)
和
COUNTIF(A1:D5,A1:D5-1)
由于我們要求對于每個值，其兩個返回值中只有一個為非零值即可，因此可以通過簡單地將上述構造加在一起，這等價于OR操作。于是，公式中的：
COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)
轉換為：
{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}
結果為：
{0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0}
從中可以看出，元素0表示：在區域中未找到比要查找的值大1或小1的值。同樣，元素1表示：在區域中找到了比要查找的值大1或小1的值。值2表示在區域中找到了比要查找的值大1的值和小1的值。因此：
IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5)
轉換為：
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}
現在，我們已經生成了所需的值。但是，仍然存在兩項工作要解決：按升序對它們進行排序以及刪除任何非數字元素。
使用SMALL函數來完成排序操作：
SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))
轉換為：
SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},ROW(A1:A20))
轉換為：
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})
結果為：
{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
通常，我們可能試圖通過構造一個合適的數組傳遞給SMALL函數作為其參數k的值來縮小數組，然而，這里使用了MODE.MULT函數，更簡潔。注意，這里使用的縮小數組的技術，當我們希望返回的元素在被處理的數組中出現的頻率相等時才合適。此外，該頻率必須至少為兩倍，因為如果沒有一個以上的值出現，那么MODE.MULT不會返回任何值。
因此，先與數組{1,1}相乘的原因是，當我們將SMALL構造（一個20行1列的數組）與數組{1,1}（一個1行2列的數組）相乘時，生成20行2列的數組，其每行中列的元素都是相同的。這樣：
SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1}
轉換為：
{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}*{1,1}
結果為：
{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!}
可以看出，我們已經成功地使每個元素的數量簡單地翻了一倍，現在可以安全地將其傳遞給MODE.MULT函數。但是，我們先要消除這些錯誤值。像絕大多數函數一樣，MODE.MULT函數不會處理包含此類錯誤值的數組。
這樣，公式中的：
IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””)
轉換為：
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函數：
MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))
轉換為：
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}
小結
MODE.MULT函數的妙用！
