Excel桌面版员工合同追踪表：日期重叠多提交检测公式需求

问题场景 #

我正在制作桌面版Excel员工合同安置追踪表，列A至K对应字段如下：

• A: Nurse Name（员工姓名）
• B: Overlap（重叠标记）
• C: Region（区域）
• D: Site（站点）
• E: ID（编号）
• F: Start Date（开始日期）
• G: End Date（结束日期）
• H: Submitted Date（提交日期）
• I: Status（状态）
• J: Special Requests（特殊需求）
• K: Extensions（延期）

需求：扫描员工姓名（列A）、开始日期（列F）和结束日期（列G），若同一员工存在起止日期部分或完全重叠的多条记录，需在对应行的列B显示「Multiple Submissions」，用于提醒团队移除重复提交的安置申请。

此前尝试公式：

=IF(COUNTIFS($A$2:$A$1000,$A2,$F$2:$F$1000,"<="&$G2,$G$2:$G$1000,">="&$F2)>1,"Multiple Submissions","")

未达预期——仅在首条记录的下一行显示提示，像员工Lionel Rivers的3条重叠记录，只有部分行被标记。

解决方案 #

方法1：公式法（无需VBA） #

替换原公式为以下SUMPRODUCT公式，可实现所有重叠行的全量标记：

=IF(SUMPRODUCT(($A$2:$A$1000=$A2)*($F$2:$F$1000<=$G2)*($G$2:$G$1000>=$F2)*($ROW($A$2:$A$1000)<>ROW($A2)))>=1,"Multiple Submissions","")

公式逻辑说明：

• $A$2:$A$1000=$A2：匹配当前行的员工姓名
• $F$2:$F$1000<=$G2 + $G$2:$G$1000>=$F2：判断其他记录的日期区间与当前行是否重叠（部分或完全覆盖都算）
• $ROW($A$2:$A$1000)<>ROW($A2)：排除当前行本身，避免自统计
• SUMPRODUCT统计符合条件的记录数，只要≥1就说明存在其他重叠记录，触发标记

方法2：VBA宏（适合大数据量） #

如果表格数据较多，公式计算较慢，可使用VBA批量处理：

1. 按Alt+F11打开VBA编辑器
2. 右键当前工作簿 → 插入 → 模块
3. 粘贴以下代码：

Sub 标记重叠提交()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    
    ' 指定目标工作表，可替换为你的表名，比如"ThisWorkbook.Worksheets("追踪表")"
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 清空原有标记
    ws.Range("B2:B" & lastRow).ClearContents
    
    ' 遍历每一行检查重叠
    For i = 2 To lastRow
        For j = 2 To lastRow
            If i <> j Then
                ' 匹配姓名+日期重叠条件
                If ws.Cells(i, "A").Value = ws.Cells(j, "A").Value _
                    And ws.Cells(j, "F").Value <= ws.Cells(i, "G").Value _
                    And ws.Cells(j, "G").Value >= ws.Cells(i, "F").Value Then
                    
                    ws.Cells(i, "B").Value = "Multiple Submissions"
                    Exit For ' 找到一条重叠就停止当前行的检查
                End If
            End If
        Next j
    Next i
End Sub

使用：运行宏后，列B会自动为所有重叠行标记提示，也可将宏绑定到工作表按钮，方便重复执行。

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