excel图表是如何按顺序排列的

给定单元格区域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:

=模式。MULT(SMALL(A1:D5，ROW(A1:A20))、IF(COUNTIF(A1:D5，A1:D5 1) COUNTIF(A1:D5，A1:D5-1)，A1:D5))

使用了91个字符。

等式2:

=模式。MULT(IF error(SMALL(IF(COUNTIF(A1:D5，A1:D5 1) COUNTIF(A1:D5，A1:D5-1)，a 1:d 5)，ROW(A1:A20))*{1，1}，""))

使用了103个字符。

尝试通过同时评估两个COUNTIF构造来缩短这个公式构造，并且不需要IFERROR子句，但是它比上面的公式稍长，有105个字符，即公式3:

=模式。MULT(IF(MMULT(COUNTIF(a 1:d 5，SMALL(A1:D5，ROW(A1:A20))-{1，-1})，{ 1；1})，{1，1}*SMALL(A1:D5，ROW(A1:A20)))

或者公式4:

=模式。MULT(IF(MMULT(COUNTIF(a 1:d 5，SMALL(A1:D5，ROW(A1:A20))-{1，-1})，{ 1；1})、小(A1:D5、行(A1:A20)))、A1:D5)

公式分析

由于我们希望从给定区域返回一个数组，该数组由该区域中至少两个连续的值组成，因此执行此操作的一种方法是检查该区域中的每个值是否会比该区域中的值多一个或少一个。

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

COUNTIF(A1:D5，A1:D5 1)

和

COUNTIF(A1:D5，A1:D5-1)

因为我们要求每个值的两个返回值中只有一个是非零值，所以我们可以简单地将上述结构加在一起，这相当于or运算。然后，在公式中：

COUNTIF(A1:D5，A1:D5-1)COUNTIF(a 1:d 5，a 1:d 5-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的值。值2表示在该区域中发现了大于1的值和小于1的值。因此：

中频(计数中频(A1:D5，A1:D5 1)计数中频(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；假，假，假，37；12,15,3,2;13，FALSE，1，36；16，假，假，假}

现在，我们已经生成了所需的值。但是，仍然有两个任务需要解决：按升序排序和删除任何非数字元素。

使用“小”功能完成排序操作：

小(IF(COUNTIF(A1:D5，A1:D5 1) COUNTIF(A1:D5，A1:D5-1)，A1:D5)，ROW(A1:A20))

转换为：

SMALL({FALSE，17，14，FALSE；假，假，假，37；12,15,3,2;13，FALSE，1，36；16，假，假，假}，行(A1:A20))

转换为：

SMALL({FALSE，17，14，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！}

一般来说，我们可以通过构造一个合适的数组，并将其作为参数k的值传递给SMALL函数来减少数组。这里用的是MULT函数，比较简洁。请注意，当我们希望返回的元素以相同的频率出现在处理过的数组中时，这里使用的减少数组的技术是合适的。此外，频率必须至少为两倍，因为如果出现的值不超过一个，则为模式。MULT不会返回任何值。

因此，先用数组{1，1}相乘的原因是，当我们将SMALL结构(20行1列的数组)与array {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函数。然而，我们需要首先消除这些错误的价值观。像大多数功能一样，模式。MULT函数不处理包含这种错误值的数组。

这样，在公式中：

IFERROR(SMALL(IF(COUNTIF(A1:D5，A1:D5 1) COUNTIF(A1:D5，A1:D5-1)，a 1:d 5)，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;"","";"","";"","";"","";"","";"","";"","";"","";"",""}

最后，将其传递给模式。MULT函数：

模式。MULT(IF error(SMALL(IF(COUNTIF(A1:D5，A1:D5 1) COUNTIF(A1:D5，A1:D5-1)，a 1:d 5)，ROW(A1:A20))*{1，1}，""))

转换为：

模式。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函数！