在Google Sheets中筛选非唯一数据集的方法咨询

需求说明 #

• 食谱列表：A2:B7（含食谱名称及对应信息）
• 售出菜单列表：D2:D7（存在重复项，需按重复次数匹配输出食谱）
• 期望结果：按售出菜单的重复频次，批量输出对应的完整食谱条目（如示例中F2:F13格式）

最优公式方案（支持自动扩展） #

直接在目标单元格输入以下ARRAYFORMULA公式，数据集扩大时无需手动下拉：

=ARRAYFORMULA(IFERROR(VLOOKUP(FLATTEN(SPLIT(TEXTJOIN(",",TRUE,REPT(D2:D&",",COUNTIF(D2:D,D2:D))),",")),A2:B7,2,FALSE)))

公式逻辑拆解 #

• COUNTIF(D2:D,D2:D)：统计售出菜单中每个菜品的重复次数
• REPT(D2:D&",",...)：将每个菜品按重复次数重复并拼接分隔符
• TEXTJOIN(",",TRUE,...)：把所有重复后的菜品合并为单个字符串
• SPLIT(...,",")：拆分字符串为一维菜品数组
• FLATTEN(...)：确保数组结构适配后续匹配
• VLOOKUP(...,A2:B7,2,FALSE)：匹配食谱列表，输出对应内容
• IFERROR(...)：屏蔽匹配错误导致的#N/A显示

替代方案（QUERY+辅助列思路） #

若偏好使用QUERY函数，可搭配辅助列实现：

1. 新增辅助列（如E列），输入公式生成带唯一标记的菜品：

=ARRAYFORMULA(IF(D2:D="","",D2:D&"|"&SEQUENCE(ROWS(D2:D),1,1)))

2. 用QUERY+VLOOKUP组合生成结果：

=ARRAYFORMULA(IFERROR(VLOOKUP(LEFT(FLATTEN(SPLIT(TEXTJOIN(",",TRUE,REPT(E2:E&",",COUNTIF(D2:D,D2:D))),",")),FIND("|",FLATTEN(SPLIT(TEXTJOIN(",",TRUE,REPT(E2:E&",",COUNTIF(D2:D,D2:D))),",")))-1),A2:B7,2,FALSE)))

该方案逻辑更繁琐，优先推荐第一种简洁方案。

内容的提问来源于stack exchange，提问作者Randy Adikara