进行排序和筛选的数组公式 / 四六文摘

数组公式可以用于动态筛选数据区域，其中的一个关键步骤是使用数组公式去排序一些数字。

使用数组公式排序数字

如下图1所示，列A中的单元格区域A1:A10放置了一系列无序的数字，要求在列B中以升序排列该区域的这些数字。

图1

可以在单元格B1中输入公式：

=SMALL($A$1:$A$10,ROW())

向下拖至单元格B10，如下图2所示。

图2

还有一种方法是使用数组公式。选择单元格区域B1:B10，输入公式：

=SMALL(A1:A10,ROW(Z1:Z10))

按下Ctrl+Shift+Enter组合键，结果如下图3所示。

图3

公式中，ROW(Z1:Z10)生成数组{1;2;3;4;5;6;7;8;9;10}，作为索引值，然后依次在单元格区域A1:A10中取值。然而，如果在第1行插入新行，公式会变为：

=SMALL(A2:A11,ROW(Z2:Z11))

将导致结果错误。其中，第2个参数生成的数组变为{2;3;4;5;6;7;8;9;10;11}。

为了提高公式的健壮性，使用INDIRECT函数保持第2个参数生成的索引数组在插入行时保持不变，公式为：

=SMALL(A1:A10,ROW(INDIRECT("1:10")))

结果与上面的相同，如下图4所示。

图4

在公式中使用INDIRECT函数，可使索引数组保持不变，因而可用于排序未知大小的动态区域（该区域的大小可以估计一个最大值）。

作者：[美]EMC教育服务团队（EMC Education Services）

京东

使用数组公式筛选数据

如下图5所示，列A中单元格区域A1:A10有一系列数据，要筛选出这些数据中有7个字符的数据并放置在列B中。

选择单元格区域B1:B10，输入数组公式：

=INDEX(A:A,SMALL(IF(LEN(A1:A10)=7,ROW(A1:A10),99),ROW(INDIRECT("1:10"))),1)&""

图5

公式中：IF(LEN(A1:A10)=7,ROW(A1:A10),99)将得到数组{1;99;99;99;99;6;7;99;99;99}，其中的99表明所有单元格中的数值长度不是7，其他数字则是长度为7的数值所在单元格的行号。

使用本文前面的技术对该数组排序：SMALL(IF(LEN(A1:A10)=7,ROW(A1:A10),99),ROW(INDIRECT("1:10")))，返回数组{1;6;7;99;99;99;99;99;99;99}。

将上面的数组传递给INDEX函数得到结果。

由于单元格A99为空，INDEX函数将返回0，因此在公式末尾添加&””。

对于更大的数据集，将公式中的99和”1:10”进行相应的修改，使其足够大以匹配数据区域的大小。

小结

在前面的示例中，我们假设数据区域从第1行开始。当然，数据区域不一定非得从第1行开始，但INDEX函数的第1个参数必须是包含数据区域的整列。

如果数据区域命名为MyData，那么数组公式为：

=INDEX(dataColumn,SMALL(IF(LEN(MyData)=7,ROW(MyData),ROW(MyData)+ROWS(MyData)),ROW(INDIRECT(“1:1048576”))),1)& “”

公式中的dataColumn，引用：

=INDEX(Sheet1!$1:$1048576,0,COLUMN(MyData))

上述公式可以根据筛选条件进行相应的修改，以筛选出满足条件的数据。