如何基于另一工作表映射表替换Sheet1 G列中的URL路径内容

方案一：Excel公式法（无需代码，适合新手） #

适用场景：单周数据量在万行以内，操作门槛低

• 确认Sheet2映射表结构：旧后缀存放在A列、新后缀存放在B列，第1行为表头，数据从第2行开始
• 切换到Sheet1，在G列旁边的空白列（比如H列）的H2单元格输入以下公式：

=SUBSTITUTE(G2, LOOKUP(2^15, FIND(Sheet2!A$2:A$1000, G2), Sheet2!A$2:A$1000), LOOKUP(2^15, FIND(Sheet2!A$2:A$1000, G2), Sheet2!B$2:B$1000))

• 把H2单元格的公式向下拉到所有有数据的行，就能自动生成替换后的URL
• 公式里的A$1000可以根据Sheet2映射的最大行数调整，比如预计最多有2000条映射就修改为A$2000
• 需要覆盖原G列内容的话，选中H列所有结果，右键复制→选中G列对应位置→右键选择性粘贴为值即可

方案二：VBA宏方案（自动适配动态行数，适合高频操作） #

适用场景：每周需要重复操作、数据量较大，想要一键完成处理

• 打开Excel文件后按Alt + F11调出VBA编辑器，在左侧工程栏右键当前工作簿名称，选择「插入→模块」
• 把以下代码粘贴到弹出的模块窗口中：

Sub 批量替换URL后缀()
    Dim sht1 As Worksheet, sht2 As Worksheet
    Dim dict As Object
    Dim lastRow1 As Long, lastRow2 As Long, i As Long
    Dim oldStr As String, newStr As String, url As String
    
    ' 绑定对应工作表
    Set sht1 = ThisWorkbook.Worksheets("Sheet1")
    Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 读取Sheet2的映射规则存入字典
    lastRow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2
        oldStr = sht2.Cells(i, "A").Value
        newStr = sht2.Cells(i, "B").Value
        If oldStr <> "" And Not dict.exists(oldStr) Then
            dict.Add oldStr, newStr
        End If
    Next i
    
    ' 批量替换Sheet1 G列的URL后缀
    lastRow1 = sht1.Cells(sht1.Rows.Count, "G").End(xlUp).Row
    For i = 2 To lastRow1
        url = sht1.Cells(i, "G").Value
        If url <> "" Then
            For Each oldStr In dict.keys
                If InStr(url, oldStr) > 0 Then
                    sht1.Cells(i, "G").Value = Replace(url, oldStr, dict(oldStr))
                    Exit For ' 匹配到规则后跳出，避免重复替换
                End If
            Next oldStr
        End If
    Next i
    
    ' 释放内存
    Set dict = Nothing
    Set sht1 = Nothing
    Set sht2 = Nothing
    MsgBox "替换完成！"
End Sub

• 按F5即可直接运行，自动识别两个工作表的有效行数完成替换，也可以在Excel界面添加按钮关联这个宏，每周点击一次就能完成所有操作
• 如果你的Sheet2映射列不是A/B列、或者表头不在第一行，修改代码中对应的列号和起始行号即可

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