您的位置: 首页 > EXCEL技巧 > Excel函数 >

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

时间:2013-12-13 整理:docExcel.net

通常在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”。

类似自定义筛选包含功能的数组公式 问题:类似自定义筛选包含功能的数组公式
回答:...时可能也需要用公式来实现这样的功能,这时可用下面的数组公式。例如A2:A101中包含100个三位数字,在D1单元格中输入要查找(包含)的内容,在其下方的单元格中返回结果,如图所示。 在D3单元格输入数组公式:  =OFFSE...
用数组公式从一列中提取包含指定内容的数据 问题:用数组公式从一列中提取包含指定内容的数据
回答:...字符。如果要用公式来实现这种类似筛选的效果,可以在数组公式中用SEARCH函数来查找所包含的字符。假如在A2:A100区域中包含一些成语,要从中提取包含“一”字的成语。 在B2单元格中输入数组公式: =INDEX($A$2:$A$100,SMALL(IF(I...
用数组公式从一列中提取非空单元格值 问题:用数组公式从一列中提取非空单元格值
回答:...,再复制到其他列中。如果不使用筛选,还可以用下面的数组公式: 假如数据在A2:A20区域中,将提取后的数据放到B列中,在B2单元格中输入数组公式: =INDEX($A$2:$A$20,SMALL(IF($A$2:$A$20= , ,ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A2)-ROW($A$2)+1)) ...
Excel筛选指定尾数手机号码三例 问题:Excel筛选指定尾数手机号码三例
回答:...机号在A2:A125区域中,以B列为辅助列,在B2单元格中输入数组公式: =OR(RIGHT(A2,2)=TEXT(11*(ROW($1:$10)-1), 00 )) 公式输入完毕按Ctrl+Shift+Enter结束,然后向下填充公式。这样,A列中凡是结尾两个数字相同的手机号,在B列中都显示为“TRU...
类似VLOOKUP能返回多个查询值的数组公式 问题:类似VLOOKUP能返回多个查询值的数组公式
回答:在Excel中使用VLOOKUP函数可以在表格数组的首列查找指定的值,然后返回当前行中其他列的值。但表格数组第一列中有两个或多个值与查找值相匹配时,VLOOKUP函数只能返回第一个找到的值。如果要用公式返回与查找值相匹配的多...
Excel中筛选后的条件求和公式 问题:Excel中筛选后的条件求和公式
回答:... ① ROW($F$10:$F$31)-MIN(ROW($F$10:$F$31))返回一个包含22个数值的数组{0;1;2;3;4;5;6;7;8;9;10;1
相关推荐: