嘻皮客娱乐学习网

标题: 类似自定义筛选（包含）功能的数组公式 [打印本页]

作者: fs267 时间: 2015-4-10 10:04
标题: 类似自定义筛选（包含）功能的数组公式
通常在Excel中的一列数据中找出包含指定文本的数据，可以用自定义自动筛选中的“包含”选项。有时可能也需要用公式来实现这样的功能，这时可用下面的数组公式。例如A2:A101中包含100个三位数字，在D1单元格中输入要查找（包含）的内容，在其下方的单元格中返回结果，如图所示。
(, 下载次数: 61)
在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”。
(, 下载次数: 75)

欢迎光临嘻皮客娱乐学习网 (http://www.xipick.com/)