如何用Excel函数匹配SKU与New List列返回对应Assortment值？

问题根源分析 #

你之前用FILTER+UNIQUE+SUBSTITUTE在虚拟数据中生效，实际大数字数据失效，核心原因有两个：

1. SUBSTITUTE的局限性：该函数仅对文本生效，实际数据中SKU/New List为纯数字时，SUBSTITUTE无法修改数字内容，直接破坏了匹配逻辑。
2. 大数据量的效率问题：嵌套文本处理函数在数万行数据中会占用大量内存，导致公式计算超时或报错。

针对需求的有效公式 #

假设你的数据结构为：

• SKU列：A2:A[总行数]
• New List列：B2:B[总行数]
• 待筛选的指定数字列（用户提及的C列）：C2:C[总行数]
• 10个指定数字存储在：F2:F11
• Assortment列：D2:D[总行数]

方案1（Excel 365/2021 推荐） #

使用XMATCH实现高效匹配，结合FILTER和UNIQUE完成筛选去重：

=UNIQUE(FILTER(D2:D10000, (ISNUMBER(XMATCH(A2:A10000, B2:B10000)))*(ISNUMBER(XMATCH(C2:C10000, F2:F11)))))

公式拆解：

• XMATCH(A2:A10000, B2:B10000)：精准匹配SKU与New List列的数字，返回匹配位置（不匹配则返回错误）
• ISNUMBER(...)：将匹配结果转换为布尔值（匹配=TRUE，不匹配=FALSE）
• 两个条件相乘：实现“同时满足SKU匹配New List、C列值属于指定数字”的逻辑
• FILTER(D2:D10000, ...)：筛选出符合条件的Assortment值
• UNIQUE(...)：去除重复的Assortment编号

方案2（兼容旧版Excel） #

如果没有XMATCH，用COUNTIF替代：

=UNIQUE(FILTER(D2:D10000, (COUNTIF(B2:B10000, A2:A10000)>0)*(COUNTIF(F2:F11, C2:C10000)>0)))

• COUNTIF(B:B, A2)：判断当前SKU是否存在于New List列
• 其余逻辑与方案1一致

额外优化建议 #

1. 统一数字格式：检查SKU/New List列是否存在文本格式的数字（用=ISTEXT(A2)验证），若有，用=VALUE(A2)批量转换为纯数字，避免匹配失效。
2. 缩小引用范围：不要用整列引用（如A:A），改用实际数据的精确范围（如A2:A10000），大幅降低计算负载。
3. 大数据量备选方案：若数据量超过10万行，建议用Power Query处理：
   • 选中数据区域，点击「数据」选项卡→「从表格/区域」导入Power Query
   • 添加筛选：保留C列值在指定10个数字内的行
   • 添加合并查询：匹配SKU列与New List列，保留匹配成功的行
   • 对Assortment列去重，最后点击「关闭并上载」将结果导出到Excel

内容的提问来源于stack exchange，提问作者Jake Christensen