在 Excel 的工作表中有一组数据清单(参考下图),如何列出列表中含有指定字符的数据?
例如:要找出数据中含有 T、N、R 的清单。
【公式设计与解析】
选取单元格A1:A25,按 Ctrl+Shift+F3 键,勾选「顶端列」,定义名称:资料。
单元格C2:{=IFERROR(OFFSET($A$1,SMALL(IF(SUBSTITUTE(资料,C$1,"")<>数据,ROW(数据),""),ROW(1:1))-1,0),"")}
这是数组公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会自动加上「{}」。
复制单元格C2,贴至单元格C2:E18。
(1) SUBSTITUTE(资料,C$1,"")
利用 SUBSTITUTE 函数将数据数组中每个单元格都以单元格C1的内容用空白取代。
(2) IF(第(1)式<>数据,ROW(数据),"")
对数据数组中的每个单元格判断第(1)式的传回结果,如果取代后的结果和原来的内容不一样,表示含有单元格C1内容,则传该单元格的列号;否则传回空字符串(空白)。(ROW 函数可传单元格的列号)
(3) SMALL(第(2)式,ROW(1:1))
当公式向下各列复制时,将第(2)式传回的列号利用
SMALL 函数由小至大取出。(4) OFFSET($A$1,第(3)式-1,0)
将第(3)式传回的列号代入 OFFSET 函数取得由单元格A1起始所对应的单元格内容。
(5) IFERROR(第(4)式,"")
利用 IFERROR 函数将第(3)可能传回的错误讯转换为空字符串(空白)。
閱讀更多 老徐漫談 的文章