如何用Excel数据验证实现跨工作表多列条件校验与限制

一、工作表结构说明 #

• Sheet1：包含列ID、TYPE、VALUE 1、VALUE 2，同一ID+TYPE组合唯一，对应一组数值。
• Sheet2：包含列ID、TYPE、YEAR、VALUE (SUM)，需保证同一ID+TYPE的所有VALUE (SUM)之和，等于Sheet1中对应组合的VALUE 1+VALUE 2之和。

二、数据验证设置步骤 #

1. 先给Sheet1添加辅助列计算总数值 #

在Sheet1新增一列（比如E列，表头设为总数值），在E2单元格输入公式：

=SUM(C2:D2)

下拉填充到所有数据行，用于快速引用每个ID+TYPE组合的数值总和。

2. 给Sheet2的VALUE (SUM)列设置数据验证 #

选中Sheet2中VALUE (SUM)列的可输入区域（比如从D2开始的所有单元格），打开「数据验证」（顶部菜单栏→数据→数据验证）：

• 「允许」选项选择自定义
• 「公式」框输入以下内容：

=SUMIFS(Sheet2!$D:$D,Sheet2!$A:$A,$A2,Sheet2!$B:$B,$B2)=VLOOKUP($A2&$B2,CHOOSE({1,2},Sheet1!$A:$A&Sheet1!$B:$B,Sheet1!$E:$E),2,FALSE)

• 切换到「出错警告」标签：
  • 样式选停止
  • 标题填「总和不匹配」
  • 错误信息填「当前ID+TYPE的VALUE(SUM)总和与Sheet1对应数值不符，请检查！」

3. 公式逻辑说明 #

• SUMIFS(Sheet2!$D:$D,Sheet2!$A:$A,$A2,Sheet2!$B:$B,$B2)：计算Sheet2中当前行ID+TYPE组合的所有VALUE (SUM)总和
• VLOOKUP($A2&$B2,CHOOSE({1,2},Sheet1!$A:$A&Sheet1!$B:$B,Sheet1!$E:$E),2,FALSE)：通过拼接ID+TYPE值，匹配Sheet1中对应组合的总数值（即辅助列E的值）
• 公式判断两者是否相等，不相等时直接触发错误提示

额外注意事项 #

• 如果Sheet1中无对应ID+TYPE组合，公式会返回错误，可嵌套IFERROR处理（表示无对应组合时总和需为0）：

=SUMIFS(Sheet2!$D:$D,Sheet2!$A:$A,$A2,Sheet2!$B:$B,$B2)=IFERROR(VLOOKUP($A2&$B2,CHOOSE({1,2},Sheet1!$A:$A&Sheet1!$B:$B,Sheet1!$E:$E),2,FALSE),0)

• 建议给Sheet1的ID+TYPE列设置重复值验证，避免同一组合出现多行数据导致VLOOKUP匹配错误。

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