人工服务 离线下载

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

数组公式可以用于动态筛选数据区域,其中的一个关键步骤是使用数组公式去排序一些数字。
 
使用数组公式排序数字
如下图1所示,列A中的单元格区域A1:A10放置了一系列无序的数字,要求在列B中以升序排列该区域的这些数字。
乱序排序
 
可以在单元格B1中输入公式:
=SMALL($A$1:$A$10,ROW())
向下拖至单元格B10,如下图所示。
small排序
 
还有一种方法是使用数组公式。选择单元格区域B1:B10,输入公式:
=SMALL(A1:A10,ROW(Z1:Z10))
按下Ctrl+Shift+Enter组合键,结果如下图3所示。
small排序

公式中,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}。
 
small排序

为了提高公式的健壮性,使用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)&""
index获取指定字符数内容
 
公式中: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))
 
上述公式可以根据筛选条件进行相应的修改,以筛选出满足条件的数据。