在 Excel 中如果要使用公式以起始字符串来筛选列表中的项目,该如何处理?
参考下图,要以起始字符串(本例:A135790)在一个号码的列表中筛选。
【手动筛选】
如果你使用筛选工具,就可以使用「文字筛选」下的「开始于」功能来筛选。
输入开始的字符串:(本例为A135790)
随之得到筛选结果:
【公式设计与解析】
如何以公式来完成 ?
先选取A1:A35,按 Ctrl+Shift+F3 键,勾选「顶端列」,定义名称:号码。
单元格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(LEFT(号码,LEN($C$2))=$C$2,ROW(号码),""),ROW(1:1))-1,0),"")}
复制单元格D2,贴至单元格D2:D14。
这是数组公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会自动加上「{}」。
(1) IF(LEFT(号码,LEN($C$2))=$C$2,ROW(号码),"")
先以LEN 函数计算单元格C2的文字长度,然后在数组公式中利用 LEFT 函数判断所有号码列表由左边取和单元格C2相同的的文字长度,如果和单元格C2相同者传回其列号(利用 ROW 函数),否则传回空字符串。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函数于第(1)式的传回值由小至大取出列号。公式向下复制时,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($A$1,第(2)式-1,0)
将第(2)式传回的列号代入 OFFSET 函数取得对应的单元格内容。
(4) IFERROR(第(3)式,"")
公式可能传回错误讯息,藉由 IFERROR 函数将错误讯息置换为空字符串。
閱讀更多 老徐漫談 的文章