首页 > Excel专区 > Excel函数 >

excel类似自定义筛选(包含)功能的数组公式

Excel函数 2023-01-13

通常在Excel中的一列数据中找出包含指定文本的数据,可以用自定义自动筛选中的“包含”选项。有时可能也需要用公式来实现这样的功能,这时可用下面的数组公式。例如A2:A101中包含100个三位数字,在D1单元格中输入要查找(包含)的内容,在其下方的单元格中返回结果,如图所示。

在D3单元格输入数组公式:

=OFFSET($A$1,SMALL(IF(ISERROR(SEARCH($D$1,$A$2:$A$101)),65535,ROW($1:$100)),ROW(A1)),)&""

公式输入完毕后按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充公式。

说明:该公式先用SEARCH函数判断A列中是否包含指定的文本,如果A列某个单元格包含指定文本就返回该单元格的在A2:A101中的位置,否则返回“65535”。然后用SMALL和OFFSET函数按先后顺序返回包含指定文本的单元格值,对于不包含指定文本的单元格,公式会在D列的后面返回A65536这个几乎用不到的空单元格值。

例如要查找A列包含“12”的数据,在D1中输入“12”即可。

由于用到了SEARCH函数,因而可以支持通配符。如要返回A列中结尾为“8”的数字,只需在D1中输入“??8”。


Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.