如何基于Google Sheets单元格剩余数值实现递减式下拉菜单

核心需求回顾 #

• 团队产能总上限为单元格B1（修正后产能，可固定为14或动态调整）
• 7个任务主题对应输入单元格（示例为C1:I1，可自行调整范围）
• 数据验证规则：
  1. 未选择任何值时，所有下拉菜单可选范围为1-14
  2. 选中数值后，剩余可选额度为总上限减去已选总和，单个任务的选取值不得超过剩余额度
  3. 总选取值达到上限时，剩余下拉菜单仅能选择0且无法输入其他值

实现步骤 #

1. 定义输入单元格范围 #

确定7个任务主题的输入单元格，示例中使用C1至I1，请根据实际表格结构调整。

2. 设置动态数据验证 #

对每个任务单元格（如C1）执行以下操作：

• 点击菜单栏「数据」>「数据验证」
• 在「允许」下拉框中选择「序列」
• 在「来源」输入框中粘贴以下自定义公式（注意替换单元格范围为你的实际范围）：

  =IF(SUM($C$1:$I$1)=$B$1, {0}, SEQUENCE(1, $B$1 - (SUM($C$1:$I$1)-$C$1), 1))
  

• 勾选「显示下拉列表」，确保单元格显示可选项
• 在「无效数据处理」中选择「拒绝输入」，阻止不符合规则的手动输入
• 点击「保存」后，将此验证规则批量应用到其他6个任务单元格（可通过格式刷复制）

公式说明 #

• SUM($C$1:$I$1)=$B$1：判断已选任务的总数量是否达到产能上限
  • 若满足条件，序列仅返回{0}，下拉菜单仅显示0，且无法输入其他值
• SEQUENCE(1, $B$1 - (SUM($C$1:$I$1)-$C$1), 1)：生成从1开始的连续数字序列，长度为剩余可用额度
  • SUM($C$1:$I$1)-$C$1：计算除当前单元格外，其他已选任务的总数量
  • $B$1 - 上述结果：得到当前单元格可选择的最大数值，序列自动生成1到该数值的所有选项

3. 补充：自动填充0（可选） #

如果希望总数量达到上限时，未填写的单元格自动填充为0，可添加以下脚本：

1. 点击菜单栏「扩展」>「Apps Script」
2. 删除默认代码，粘贴以下脚本：

   function onEdit(e) {
     const sheet = e.source.getActiveSheet();
     const targetRange = sheet.getRange("C1:I1"); // 替换为你的任务单元格范围
     const capacityCell = sheet.getRange("B1"); // 替换为产能上限单元格
     const capacity = capacityCell.getValue();
     const values = targetRange.getValues()[0];
     const total = values.reduce((a, b) => a + b, 0);
   
     if (total === capacity) {
       values.forEach((val, index) => {
         if (val === "" || val === 0) {
           targetRange.getCell(1, index + 1).setValue(0);
         }
       });
     }
   }
   

3. 保存脚本并命名（如CapacityAutoFill），之后每当编辑单元格时，脚本会自动检查总数量，达到上限则将空单元格设为0

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