如何创建Excel宏：比对两工作表列并替换学生ID为连续编号

嘿，这需求我经常帮人解决，给你一套靠谱的实现方案——核心思路是先把两个工作表里所有的学生ID收集起来，给每个唯一ID分配一个从1开始的连续编号，再批量替换回两个表中。下面一步步来操作：

一、提前准备 #

• 先确认你的两个工作表名称（比如默认的Sheet1和Sheet2，如果名字不一样，后面代码里要对应修改）
• 确定学生ID所在的列（我这里默认是A列，如果是其他列，代码里的列标识要调整）
• 把文件保存为.xlsm格式（因为要启用宏，普通.xlsx不支持宏）

二、编写宏代码 #

1. 打开Excel，按下Alt + F11打开VBA编辑器
2. 右键点击左侧你的工作簿名称，选择「插入」→「模块」
3. 把下面的代码粘贴到模块窗口中：

Sub ReplaceStudentIDWithSerialNumber()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim idDict As Object
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, serialNum As Long
    Dim currentID As String
    
    ' 请根据你的实际工作表名称修改这里
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    ' 创建字典对象，用来存储唯一ID和对应的连续编号
    Set idDict = CreateObject("Scripting.Dictionary")
    
    ' 第一步：遍历第一个工作表，收集所有唯一学生ID
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow1 ' 假设第一行是表头，从第二行开始读取ID；如果没有表头，改成i=1
        currentID = Trim(ws1.Cells(i, "A").Value)
        ' 跳过空值，且只添加未记录过的ID
        If currentID <> "" And Not idDict.Exists(currentID) Then
            serialNum = serialNum + 1
            idDict.Add currentID, serialNum
        End If
    Next i
    
    ' 第二步：遍历第二个工作表，收集剩余的唯一学生ID
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2 ' 同样，无表头就改成i=1
        currentID = Trim(ws2.Cells(i, "A").Value)
        If currentID <> "" And Not idDict.Exists(currentID) Then
            serialNum = serialNum + 1
            idDict.Add currentID, serialNum
        End If
    Next i
    
    ' 第三步：替换第一个工作表中的原ID为新编号
    For i = 2 To lastRow1
        currentID = Trim(ws1.Cells(i, "A").Value)
        If idDict.Exists(currentID) Then
            ws1.Cells(i, "A").Value = idDict(currentID)
        End If
    Next i
    
    ' 第四步：替换第二个工作表中的原ID为新编号
    For i = 2 To lastRow2
        currentID = Trim(ws2.Cells(i, "A").Value)
        If idDict.Exists(currentID) Then
            ws2.Cells(i, "A").Value = idDict(currentID)
        End If
    Next i
    
    ' 执行完成后弹出提示
    MsgBox "学生ID替换完成！共识别到" & serialNum & "个唯一学生ID", vbInformation
    
    ' 释放占用的对象资源
    Set idDict = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

三、关键细节调整 #

• 如果你的学生ID不在A列：把代码里所有的"A"改成对应的列字母（比如"B"代表第二列）
• 如果没有表头，ID从第一行开始：把所有For i = 2 To ...改成For i = 1 To ...
• 工作表名称不对：修改代码开头Set ws1 = ...和Set ws2 = ...里的工作表名称

四、运行宏的方式 #

• 回到Excel界面，按下Alt + F8，在弹出的窗口中选择ReplaceStudentIDWithSerialNumber，点击「执行」即可
• 要是你需要经常用这个功能，可以添加一个按钮：点击「开发工具」→「插入」→「按钮（表单控件）」，然后关联这个宏，以后点按钮就能直接运行

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