如何在制服订购表格单元格中结合下拉列表与自定义公式实现数据验证？

嘿，这个问题我之前帮同事处理过，其实完全不用在下拉列表和自定义验证之间二选一——通过动态名称+组合数据验证规则就能同时满足你的两个要求，具体操作步骤如下：

1. 先规整你的尺码工作表（假设名为「尺码库」） #

先把尺码数据整理成清晰的结构，方便后续引用：

• A列：标记性别（比如「男」「女」）
• B列：对应性别的可用尺码

建议把同性别尺码放在连续区域，比如A2:A10全是「男」，对应B2:B10是男款尺码；A11:A20全是「女」，对应B11:B20是女款尺码

2. 定义动态名称（自动匹配不同性别尺码） #

我们需要给不同性别的尺码定义动态名称，这样下拉列表会自动更新：

• 打开「公式」选项卡 → 点击「定义名称」
• 定义名称男码，引用位置输入：

  =OFFSET(尺码库!$B$2,0,0,COUNTIF(尺码库!$A:$A,"男"),1)
  

  这个公式会自动统计所有男性尺码的行数，后续新增/删除男码时，下拉列表会同步更新
• 同理定义名称女码，引用位置输入：

  =OFFSET(尺码库!$B$11,0,0,COUNTIF(尺码库!$A:$A,"女"),1)
  

3. 设置动态下拉列表（实现「只能选可用尺码」规则） #

回到你的订购表（假设A列是「性别」列，已经设置了性别下拉选择）：

• 选中需要输入尺码的单元格区域（比如B2:B100），打开「数据验证」
• 允许类型选「序列」，来源输入：

  =INDIRECT(A2)
  

  这样当A2选「男」时，B2的下拉列表会自动加载男码的所有选项；选「女」则加载女码选项

4. 添加自定义验证规则（实现「禁止跨性别输入」规则） #

虽然下拉已经限制了可选范围，但如果有人手动输入不属于当前性别的尺码，还是会生效，所以要加一层拦截：

• 保持选中B2:B100，再次打开「数据验证」（Excel点击「添加」新增规则；Google Sheets直接添加新验证规则）
• 允许类型选「自定义」，公式输入：

  =COUNTIF(INDIRECT(A2),B2)>0
  

  这个公式会检查当前单元格的尺码是否存在于对应性别的尺码列表中，不存在则触发验证错误

5. 配置错误提示（提升用户体验） #

在「出错警告」选项卡，设置样式为「停止」，输入提示文本比如：请选择当前性别对应的尺码，禁止跨性别输入！，这样用户输入错误时会直接阻止操作，避免无效数据。

这样设置完成后，既可以通过下拉快速选对应性别的尺码，又能彻底禁止手动输入其他性别的尺码，完美解决你的问题！

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