进行排序和筛选的数组公式

数组公式可以用于动态筛选数据区域，其中的一个关键步骤是使用数组公式去排序一些数字。
 
使用数组公式排序数字
如下图1所示，列A中的单元格区域A1:A10放置了一系列无序的数字，要求在列B中以升序排列该区域的这些数字。

 
可以在单元格B1中输入公式：
=SMALL($A$1:$A$10,ROW())
向下拖至单元格B10，如下图所示。

 
还有一种方法是使用数组公式。选择单元格区域B1:B10，输入公式：
=SMALL(A1:A10,ROW(Z1:Z10))
按下Ctrl+Shift+Enter组合键，结果如下图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")))
结果与上面的相同，如下图所示。
在公式中使用INDIRECT函数，可使索引数组保持不变，因而可用于排序未知大小的动态区域（该区域的大小可以估计一个最大值）。

 
使用数组公式筛选数据
如下图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)&""

 
公式中：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))
 
上述公式可以根据筛选条件进行相应的修改，以筛选出满足条件的数据。