Excel中匹配员工ID、生效日期与序号批量填充结束日期的需求

Hey there! Let's work through your Excel task together. You need to adjust the End Date column based on groups of matching Employee ID, Effective Date, and Sequence number—specifically setting the last (highest) sequence's end date to 31/12/2018, while keeping the rest of the group's end dates the same as their Effective Date. Here are three practical approaches to get this done:

方法1：Excel公式法（适合快速手动处理） #

This is great if you want a no-code solution and your dataset isn't massive.

1. 假设你的数据从第2行开始（第1行是表头），在单元格D2输入以下公式：

   =IF(C2=MAXIFS(C:C,A:A,A2,B:B,B2),DATE(2018,12,31),B2)
   

2. 按回车后，下拉填充公式到所有行。

公式解释： #

• MAXIFS(C:C,A:A,A2,B:B,B2): 找到当前员工ID和生效日期对应的最大序号
• IF(...): 判断当前行的序号是否等于该最大值。如果是，返回31/12/2018（用DATE(2018,12,31)避免区域日期格式问题）；如果不是，直接引用B列的生效日期。

针对旧版Excel（2016及更早）：

如果你的Excel没有MAXIFS函数，使用以下数组公式（输入时需按Ctrl+Shift+Enter激活）：

=IF(C2=MAX(IF((A:A=A2)*(B:B=B2),C:C)),DATE(2018,12,31),B2)

方法2：VBA宏法（适合批量/重复处理） #

Perfect if you have a large dataset or need to run this task regularly.

1. 按Alt+F11打开VBA编辑器
2. 在项目资源管理器中右键你的工作簿 > 插入 > 模块
3. 将以下代码粘贴到模块中：

Sub SetEndDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim maxSeq As Long
    Dim currentID As String, currentDate As Date
    
    ' 替换成你的工作表名称，比如Sheets("员工数据")
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 先按ID、生效日期、序号排序，确保组内顺序正确
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("A:A"), Order:=xlAscending
    ws.Sort.SortFields.Add Key:=Range("B:B"), Order:=xlAscending
    ws.Sort.SortFields.Add Key:=Range("C:C"), Order:=xlAscending
    With ws.Sort
        .SetRange Range("A1:D" & lastRow)
        .Header = xlYes ' 如果没有表头，改成xlNo
        .Apply
    End With
    
    ' 遍历数据设置结束日期
    i = 2 ' 从第二行开始（假设第一行是表头）
    Do While i <= lastRow
        currentID = ws.Cells(i, "A").Value
        currentDate = ws.Cells(i, "B").Value
        
        ' 找到当前组的最后一行（最大序号）
        maxSeq = ws.Cells(i, "C").Value
        Do While i + 1 <= lastRow And ws.Cells(i + 1, "A") = currentID And ws.Cells(i + 1, "B") = currentDate
            maxSeq = ws.Cells(i + 1, "C").Value
            i = i + 1
        Loop
        
        ' 设置最后一行的结束日期为31/12/2018
        ws.Cells(i, "D").Value = DateSerial(2018, 12, 31)
        
        ' 回溯设置组内其他行的结束日期为生效日期
        Dim startRow As Long
        startRow = i
        Do While startRow > 1 And ws.Cells(startRow - 1, "A") = currentID And ws.Cells(startRow - 1, "B") = currentDate
            ws.Cells(startRow - 1, "D").Value = currentDate
            startRow = startRow - 1
        Loop
        
        i = i + 1
    Loop
    
    MsgBox "结束日期已批量设置完成！", vbInformation
End Sub

4. 修改代码中的工作表名称（如果需要），按F5运行宏即可。

方法3：Power Query法（适合动态数据，可重复刷新） #

Ideal if your data gets updated regularly and you want a reusable solution.

1. 选中你的数据区域，点击数据选项卡 > 从表格/区域（Excel 2016及以上可用），确认勾选“我的表格有标题”。
2. 在Power Query编辑器中：
   • 点击开始 > 分组依据，分组列选择Employee ID和Effective Date，新列名设为MaxSequence，操作选“最大值”，列选Sequence number，点击确定。
   • 点击开始 > 合并查询 > 合并作为新查询，选择原表和分组表，匹配列选Employee ID和Effective Date，连接类型选“左外部”，点击确定。
   • 展开合并后的列，只保留MaxSequence列。
   • 点击添加列 > 自定义列，输入公式：

     = if [Sequence number] = [MaxSequence] then #date(2018,12,31) else [Effective Date]
     

   • 将自定义列重命名为End Date，删除原来的End Date列和MaxSequence列。
3. 点击关闭并上载，将处理后的数据放回Excel。下次数据更新时，右键点击表格 > 刷新即可自动重新处理。

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