excel公式技巧： 获取指定区域中2个及以上连续数值并按顺序排列

给定单元格区域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函数的妙用！