如何在Google表格中创建带剩余数量限制的下拉列表？

完全可以实现这个需求，结合Google表格的数据验证功能和简单函数（或脚本）就能完成，具体操作步骤如下：

一、搭建基础数据结构 #

1. 新建Google表格，命名为「儿子年终派对菜品筹备」
2. 第一个工作表命名为菜品上限，按以下格式填写数据：

   Foods	Maximum Quantity
   Sandwiches	60
   Pizza	4
   Savory pies	10
   Desserts	10
   Drinks	30

3. 第二个工作表命名为提交记录，设置表头：姓名、菜品、提交数量、提交时间（时间列可选），用来统一记录所有人的提交数据。

二、创建用户填写入口并设置限制 #

1. 新建第三个工作表命名为填写入口，设置表头：你的姓名、选择菜品、提交数量
2. 设置菜品下拉列表：
   • 选中「选择菜品」列（如B2及以下单元格），点击顶部菜单栏「数据」→「数据验证」
   • 条件选择「列表从范围」，输入菜品上限!A2:A6，勾选「显示下拉箭头」，点击「保存」
3. 设置提交数量的动态限制：
   • 选中「提交数量」列（如C2及以下单元格），再次打开「数据验证」
   • 条件选择「数字」→「小于或等于」，在输入框中粘贴以下公式：

     =XLOOKUP(B2, 菜品上限!A:A, 菜品上限!B:B) - SUMIF(提交记录!B:B, B2, 提交记录!C:C)
     

   • 勾选「拒绝输入不符合条件的数据」，可以在「输入时显示帮助文本」中填写"请输入不超过剩余数量的数值"，点击「保存」
4. （可选）添加剩余数量显示：在D列添加表头剩余数量，D2单元格粘贴上述相同公式，方便用户直观看到当前菜品还能提交多少。

三、自动同步提交记录（可选优化） #

如果需要让用户填写后自动把数据同步到「提交记录」表，避免手动复制，可以用Google Apps Script实现：

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

   function onEdit(e) {
     const activeSheet = e.source.getActiveSheet();
     const editRange = e.range;
     // 仅处理「填写入口」表的提交数量列（C列）
     if (activeSheet.getName() === "填写入口" && editRange.getColumn() === 3 && editRange.getValue() > 0) {
       const row = editRange.getRow();
       const name = activeSheet.getRange(row, 1).getValue();
       const food = activeSheet.getRange(row, 2).getValue();
       const quantity = editRange.getValue();
       // 验证姓名和菜品已填写
       if (name && food) {
         const recordSheet = e.source.getSheetByName("提交记录");
         // 新增记录到提交表末尾
         recordSheet.appendRow([name, food, quantity, new Date()]);
         // 清空当前行填写内容，方便下一位用户使用
         activeSheet.getRange(row, 1, 1, 3).clearContent();
       }
     }
   }
   

3. 点击保存按钮，命名脚本为SubmitPartyFood，然后运行一次脚本完成权限授权（首次运行会提示授权，按指引操作即可）

四、测试验证 #

• 首次填写：选择Sandwiches，输入30，提交后数据会自动同步到「提交记录」表
• 再次填写Sandwiches：此时剩余数量为30，输入31会被系统拒绝，输入30可以正常提交
• 当Sandwiches的提交总量达到60后，再尝试输入任何大于0的数值都会被拒绝

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