Excel跨工作表设置辅助列实现两表数据关联筛选方法

• 现有Excel工作簿包含Recipes（配方）、Ingredients（食材）两个体量大、需不定期更新的工作表：
  • Ingredients表存储食材清单及对应属性，每个食材分配唯一编码，标注用量
  • Recipes表通过录入食材编码组装配方，其余字段通过VLOOKUP从Ingredients表匹配取值
• 现存操作痛点：更新配方时需逐个搜索配方内的食材ID才能修改食材属性，直接在Recipes表修改会覆盖VLOOKUP公式，操作效率极低
• 前期尝试：使用IF、VLOOKUP函数实现需求未成功
• 目标需求：在Ingredients工作表新增列，支持按食材所属配方筛选，两种实现形式均可：
  1. 自动汇总每个食材对应的所属配方信息
  2. 设置可输入配方编号的单元格，新增辅助列返回TRUE/FALSE布尔值，支持按该值筛选对应配方包含的食材

方案一：布尔值辅助列筛选（优先推荐，操作效率最高、兼容性最好） #

1. 在Ingredients表的空白固定位置（比如表头上方的K1单元格，可根据自身表格列位置调整）输入文字「待查询配方编号」，下方K2单元格留空作为输入框。
2. 在Ingredients表新增空白辅助列，列名设为「是否属于目标配方」，在第一个食材数据行对应的辅助列单元格（比如F2）输入公式：

   =COUNTIFS(Recipes!$A:$A,$K$2,Recipes!$B:$B,$A2)>0
   

   不管是新版Excel（365/2021及以后）还是旧版Excel（2019及更早）都可以直接用这个公式，不需要特殊数组回车，兼容性极强。

   公式引用替换说明：将Recipes!$A:$A替换为Recipes表存储配方编号的整列，Recipes!$B:$B替换为Recipes表存储食材编码的整列，$A2替换为当前行对应的食材编码单元格（锁列不锁行，方便下拉填充）。

3. 公式输入完成后下拉填充整列即可。使用时在K2单元格输入需要调整的配方编号，辅助列会自动给该配方包含的所有食材返回TRUE，直接点列头筛选TRUE值，就能批量调出对应配方的所有食材，在Ingredients表直接修改属性即可，Recipes表的VLOOKUP会自动同步，不会出现公式被覆盖的问题。

性能提示：如果单表数据量超过2万行，把公式里的整列引用替换为实际数据范围，比如数据最大行是20000行，就把Recipes!$A:$A改成Recipes!$A$2:$A$20000，能大幅降低计算卡顿。

方案二：自动汇总食材所属配方 #

如果需要直观查看每个食材被哪些配方引用，可以在Ingredients表新增列，列名设为「关联配方列表」，在第一个食材数据行输入公式：

• 仅支持Excel 365/2021及以上版本：

=TEXTJOIN("、",TRUE,FILTER(Recipes!$A:$A,Recipes!$B:$B=$A2,"无关联配方"))

引用替换规则和方案一一致，公式输入后会自动溢出填充整列，不需要手动下拉。每个食材对应的所有配方编号会用顿号分隔展示，需要筛选特定配方时，直接在该列搜索对应配方编号即可。

• 旧版Excel不建议用这个方案，没有TEXTJOIN/FILTER函数的情况下需要嵌套多层数组公式，数据量大时卡顿明显，优先用方案一。

内容的提问来源于stack exchange，提问作者Dee Dubs