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⑴),A1:D5))

使用了91个字符。

公式2：

=MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5⑴),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}*SMALL(A1:D5,ROW(A1:A20))))

或公式4：

=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,⑴}),{1;1}),SMALL(A1:D5,ROW(A1:A20))),A1:D5)

公式解析

由于我们想要从所给区域中返回一个数组，该数组由区域内最少两个连续的数值构成，因此，这种情况下，履行此操作的一种方法是针对区域内的每一个值检查是不是在该区域内还会发现比该值大1或小1的值。

这样，我们需要查看下面两类构造的结果：

COUNTIF(A1:D5,A1:D5+1)

和

COUNTIF(A1:D5,A1:D5⑴)

由于我们要求对每一个值，其两个返回值中只有一个为非零值便可，因此可以经过简单地将上述构造加在一起，这等价于OR操作。因而，公式中的：

COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5⑴)

转换为：

{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⑴),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⑴),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⑴),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⑴),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⑴),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函数的妙用！