當前位置:成語大全網 - 新華字典 - EXCEL怎麽批量替換,不同數據對應成壹個相同的值

EXCEL怎麽批量替換,不同數據對應成壹個相同的值

壹般的函數很難完成這個替換,需要用VBA 來執行,利用字典和替換字符函數來完成,代碼很簡單,如下:

Sub 替換()

Dim Dic As Variant

n = Cells(Rows.Count, 2).End(xlUp).Row

Set Dic = CreateObject("Scripting.Dictionary")

For i = 2 To n

Dic(Cells(i, 2).Value) = "中國"

Next

arr = Dic.keys

n = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To n

For j = 0 To UBound(arr)

If Application.IsError(Application.Find(arr(j), Cells(i, 1))) = False Then

Cells(i, "a") = Application.Substitute(Cells(i, 1), arr(j), "中國")

Exit For

End If

Next

Next

End Sub