Excel FILTER函数Include字段使用数组匹配的技术咨询

当metadata表分类列（3000+行）与K7起始的动态分类列表（1-20行）数组大小不匹配时，核心是要判断metadata的每个分类是否存在于动态列表中，你可以用ISNUMBER+MATCH组合来构建FILTER的Include参数：

适用于Excel 365/2021（支持动态数组）的公式 #

假设metadata是结构化表格，metadata[分类]为分类列，metadata[名称]和metadata[描述]为需要返回的列，K7的动态列表用K7#引用其溢出范围，完整公式如下：

=FILTER(metadata[[名称]:[描述]], ISNUMBER(MATCH(metadata[分类], K7#, 0)))

公式原理 #

• MATCH(metadata[分类], K7#, 0)：逐个检查metadata的分类是否在动态列表中，找到返回对应位置序号，找不到返回错误值
• ISNUMBER(...)：将序号转换为TRUE，错误值转换为FALSE，生成一个与metadata行数完全匹配的布尔数组，满足FILTER函数对Include参数的要求

旧版Excel（不支持动态数组）的兼容写法 #

如果无法使用K7#引用动态范围，可通过COUNTA动态计算列表行数，公式调整为：

=FILTER(metadata[[名称]:[描述]], ISNUMBER(MATCH(metadata[分类], OFFSET(K7,0,0,COUNTA(K7:K26),1), 0)))

这里K7:K26是预设的最大范围（对应1-20行），COUNTA(K7:K26)会自动计算实际有内容的行数。

内容的提问来源于stack exchange，提问作者Samer Yamak