基于Google Sheets现有示例，如何实现每人可选3个带配额限制的下拉选项？

前置表格布局 #

先搭好基础表格结构：

• 委员会配置区（示例用A1:C2）：
  • A1:C1：填写委员会名称（比如「委员会A」「委员会B」「委员会C」）
  • A2:C2：对应填写各委员会的人数上限（比如3、2、4）
• 学生选择区（示例用E1:G5）：
  • E2:E5：列出所有学生的名字
  • F2:H5：给每位学生留3个单元格，用来选委员会（对应每人选3个的需求）

编写动态选项生成公式 #

找个隐藏的辅助区域（比如单独建个工作表放，避免误改），输入以下公式生成每行的可选委员会列表：

=lambda(students,selections,committees,limits,
  makearray(counta(students),columns(committees),lambda(r,c,
    let(
      current_comm,index(committees,,c),
      total_selected,countif(flatten(selections),current_comm),
      student_selected,countif(index(selections,r,),current_comm),
      if(
        student_selected>0,current_comm,
        if(total_selected<index(limits,,c),current_comm,)
      )
    )
  ))(
    $E$2:$E$5,$F$2:$H$5,$A$1:$C$1,$A$2:$C$2
  )

公式逻辑拆解 #

• 先统计所有学生选每个委员会的总次数，判断是否达上限
• 再统计当前学生是否已经选过该委员会，确保自己选过的选项不会被移除
• 最终规则：自己选过的委员会保留；没选过的，只要总人数没满额就保留，满额就自动隐藏

设置下拉菜单的数据验证 #

给学生选择区的每个单元格（F2:H5）设置数据验证：

• 验证类型选「从范围获取下拉列表」
• 数据范围对应辅助区域的同行范围，比如F2选=$J$2:$L$2，F3选=$J$3:$L$3（批量设置时注意锁定行、不锁定列）

额外优化（可选） #

如果要防止学生重复选同一个委员会，给选择栏加个自定义验证规则：

=countif($F2:$H2,F2)<=1

这样同一学生的3个选择栏里，同一个委员会只能出现一次

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