合同列表中日期重叠与缺失问题的排查工具咨询

假设你的数据结构为每行对应一份合同，包含地点标识「合同开始日期」「合同结束日期」三个核心字段，且已按「地点分组+同地点内按开始日期升序」完成排序，以下是针对两类问题的识别方法：

一、识别合同日期重叠（Excel公式） #

新增一列（如D列），输入以下公式后下拉填充：

=IF(COUNTIFS(A:A,A2,B:B,"<="&C2,C:C,">="&B2)>1,"存在重叠","")

逻辑说明 #

用COUNTIFS统计同一地点中，满足「其他合同开始日期 ≤ 当前合同结束日期」且「其他合同结束日期 ≥ 当前合同开始日期」的合同数量，数量大于1则说明当前合同与同地点至少一份合同存在时间重叠。

若需定位重叠的具体合同行号，可使用数组公式（Excel 365直接回车，旧版本需按Ctrl+Shift+Enter确认）：

=TEXTJOIN(", ",TRUE,IF((A:A=A2)*(B:B<=C2)*(C:C>=B2)*(ROW(A:A)<>ROW(A2)),ROW(A:A),""))

二、识别服务断档（月份空白） #

从同地点的第二份合同开始（排序后首份合同为行2，第二份为行3），新增一列（如E列）输入公式并下拉：

=IF(B3>C2+1,"存在断档","")

逻辑说明 #

判断下一份合同的开始日期是否晚于上一份合同结束日期的次日，若是则说明中间存在至少1天的服务空白（自然覆盖月份断档场景）。

如果仅需判断月份级断档，可改用：

=IF(DATEDIF(C2,B3,"m")>0,"存在断档","")

三、超大量数据的Python批量处理方案 #

若数据量过万，用Python的pandas库效率更高：

import pandas as pd

# 读取合同数据
df = pd.read_excel("合同数据.xlsx")
# 按地点和开始日期排序
df = df.sort_values(["地点", "合同开始日期"])

# 标记重叠合同
df["重叠标记"] = df.groupby("地点").apply(
    lambda x: x.apply(
        lambda row: any((x["合同开始日期"] <= row["合同结束日期"]) & 
                       (x["合同结束日期"] >= row["合同开始日期"]) & 
                       (x.index != row.name)),
        axis=1
    )
).reset_index(drop=True)

# 标记断档合同
df["断档标记"] = df.groupby("地点")["合同结束日期"].shift(1) < df["合同开始日期"] - pd.Timedelta(days=1)

# 导出结果
df.to_excel("合同异常结果.xlsx", index=False)

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