如何对比Google Sheets重复列数值范围，重叠时返回错误？

实现思路 #

• 先匹配所有和当前行属于同类科目的条目
• 用范围重叠判断逻辑校验同类条目的数值区间是否存在交叉：两个区间[s1,e1]、[s2,e2]发生重叠的充分必要条件是 s1 <= e2 且 s2 <= e1
• 对满足同科目+范围重叠的条目计数，计数大于1（排除当前行自身）时返回错误提示

可用公式 #

基础全表校验公式 #

假设你的表格结构为：A列=科目，B列=区间起始值，C列=区间结束值，校验结果放在D列，表头占第1行，数据从第2行开始。
在D2单元格输入以下公式，下拉填充到所有数据行即可：

=IF(SUMPRODUCT(--(A:A=A2), --(B:B<=C2), --(C:C>=B2))>1, "错误：同科目范围重叠", "正常")

仅校验新录入行的公式 #

如果不需要校验历史数据，只检查每次新录入的行是否和已有数据冲突，假设新录入的行是第N行，公式如下：

=IF(SUMPRODUCT(--(A$2:A$(N-1)=A(N)), --(B$2:B$(N-1)<=C(N)), --(C$2:C$(N-1)>=B(N)))>=1, "错误：同科目范围重叠", "正常")

补充说明 #

• 如果你的范围是存储在同一列的文本格式（例如8-10），可以先用两个辅助列拆分出数值：
  • 提取起始值：=VALUE(LEFT(范围单元格,FIND("-",范围单元格)-1))
  • 提取结束值：=VALUE(RIGHT(范围单元格,LEN(范围单元格)-FIND("-",范围单元格)))
• 数据量较大时建议把公式中的整列引用（如A:A）替换为实际的数据范围（如A$2:A$1000），可大幅提升计算效率。

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