制作勾选复选框时自动汇总对应配方食材量的Excel表格

核心需求 #

• 每个饼干配方单独存为Excel工作表
• 在「Shopping List」工作表通过复选框选择要制作的饼干，自动汇总食材总量到购物清单
• 「Quick Chart」工作表自动同步选中饼干的食材用量、杂项食材及所需批次
• 操作简单，无需手动修改公式，适合技术水平较低的使用者

已完成的基础设置 #

• 所有配方工作表格式统一，跨表引用单元格规则一致
• 「Shopping List Calculator」工作表J3单元格输入礼品袋数量后，各配方工作表通过公式=CEILING(('Shopping List Calculator'!J3/H4),1)自动计算所需批次（H4为单批可装礼品袋数）
• 已掌握手动汇总公式：([Recipe Name]![Ingredient Quantity] * [Recipe Name]!Batches Needed) + ...，但操作繁琐
• 「Quick Chart」已实现手动计算单种饼干食材量='[Recipe Name]'![Ingredient Quantity]*'[Recipe Name]'!Batches Needed，以及手动汇总杂项食材=TEXTJOIN(", ", TRUE, '[Recipe Name]'!A20:A49)

自动实现步骤（结合复选框+函数） #

1. 给复选框绑定控制单元格 #

在「Shopping List」工作表中：

• 给每个饼干名称旁添加复选框，右键复选框→设置控件格式→控制→单元格链接，选择同一行的隐藏单元格（比如B列，可将B列设为隐藏）
• 勾选复选框时，对应链接单元格显示TRUE，未勾选显示FALSE

2. 创建配方名称-工作表映射表 #

新建隐藏工作表（命名为「Recipe Mapping」）：

• A列：输入所有饼干的名称（与「Shopping List」中的名称完全一致）
• B列：输入对应饼干配方的工作表名称（比如「巧克力曲奇」对应工作表名「ChocolateChip」）

3. 购物清单自动汇总食材总量 #

在「Shopping List」的食材总量单元格（比如面粉对应D2），输入以下公式：

=SUMPRODUCT(--($B$2:$B$10), INDIRECT("'"&'Recipe Mapping'!$B$2:$B$10&"'!$C$2")*INDIRECT("'"&'Recipe Mapping'!$B$2:$B$10&"'!$B$1"))

• 说明：
  • --($B$2:$B$10)：将复选框的TRUE/FALSE转换为1/0，仅选中的配方参与计算
  • INDIRECT("'"&'Recipe Mapping'!$B$2:$B$10&"'!$C$2")：引用对应配方工作表的食材数量（假设C2是该食材的单批用量）
  • INDIRECT("'"&'Recipe Mapping'!$B$2:$B$10&"'!$B$1")：引用对应配方工作表的所需批次（假设B1是批次计算结果）
  • 按需调整单元格范围（$B$2:$B$10）和食材引用位置（$C$2）

4. 「Quick Chart」自动更新内容 #

（1）单种选中饼干的食材用量

在图表对应的食材单元格输入：

=IF('Shopping List'!B2, INDIRECT("'"&'Recipe Mapping'!B2&"'!C2")*INDIRECT("'"&'Recipe Mapping'!B2&"'!B1"), "")

下拉填充即可自动显示选中饼干的食材用量，未选中的显示空白

（2）自动汇总杂项食材

输入数组公式（旧版Excel需按Ctrl+Shift+Enter，新版直接回车）：

=TEXTJOIN(", ", TRUE, IF('Shopping List'!$B$2:$B$10, INDIRECT("'"&'Recipe Mapping'!$B$2:$B$10&"'!$A$20:$A$49"), ""))

• 说明：自动提取所有选中配方工作表中A20:A49区域的杂项食材，用逗号分隔

（3）自动汇总所需批次

输入公式：

=SUMPRODUCT(--('Shopping List'!$B$2:$B$10), INDIRECT("'"&'Recipe Mapping'!$B$2:$B$10&"'!$B$1"))

自动计算所有选中配方的批次总和

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