【求助】根据对应关系替换表达式中的元素

Stone_00x

需求说明:
根据对应关系替换原表达式中的元素,得到新表达式
原表达式：A+AB-C*10
新表达式：Z1+Z2-Z3*10

大灰狼1976

减号怎么要变成加号吗

limonet

关键字：字典+replace

limonet

Sub limonet()
    Dim Dic As Object, i%, S$
    Set Dic = CreateObject("scripting.dictionary")
    For i = 2 To 4
        Dic(Cells(i, "A").Value) = Cells(i, "B")
    Next i
    S = Range("D2")
    For i = 0 To Dic.Count - 1
        S = Replace(S, Dic.keys()(i), Dic.items()(i))
    Next i
    Range("F2") = S
End Sub

Stone_00x

大灰狼1976 发表于 2024-1-25 08:55
减号怎么要变成加号吗

搞错了，应该是Z1+Z2-Z3*10  

Stone_00x

limonet 发表于 2024-1-25 09:06
关键字：字典+replace

这个方法我试过，但是替换A的时候会把AB中的A给替换掉，你是倒着替换的，先把AB替换掉了，所以避免了这种问题，这是我模拟的数据，实际情况这种方法行不通

limonet

那就顺序吧。

limonet

Sub limonet()
    Dim Dic As Object, i%, S$, Reg As Object
    Set Dic = CreateObject("scripting.dictionary")
    Set Reg = CreateObject("vbscript.regexp")
    For i = 2 To 4
        Dic(Cells(i, "A").Value) = Cells(i, "B")
    Next i
    S = Range("D2")
    For i = 0 To Dic.Count - 1
        Reg.Pattern = Dic.keys()(i) & "([\+\-\*])"
        S = Reg.Replace(S, Dic.items()(i) & "$1")
    Next i
    Range("F2") = S
End Sub

Stone_00x

limonet 发表于 2024-1-25 09:58
Sub limonet()
    Dim Dic As Object, i%, S$, Reg As Object
    Set Dic = CreateObject("scripting.d ...

您好,当对应关系中存在小括号的时候,替换就失效了,例如 "实收资本(或股本)" 对应 "Z1"
以下是GPT的回答,我想知道还有其他方法吗,谢谢!

1. Sub myDemo()
   
2. 
   
3.     Dim dic As Object, reg As Object
   
4.     Set dic = CreateObject("Scripting.Dictionary")
   
5.     Set reg = CreateObject("VBScript.RegExp")
   
6.    
   
7.     Dim i As Long
   
8.     For i = 2 To 5
   
9.         dic(Cells(i, "A").Value) = Cells(i, "B")
   
10.     Next i
    
11.    
    
12.     Dim S As String
    
13.     S = Range("D2") & "+"
    
14.     For i = 0 To dic.Count - 1
    
15.         reg.Pattern = Replace(dic.Keys()(i), "(", "\(")
    
16.         reg.Pattern = Replace(reg.Pattern, ")", "\)")
    
17.         reg.Pattern = reg.Pattern & "([+\-*\/])"
    
18.         S = reg.Replace(S, dic.Items()(i) & "$1")
    
19.     Next i
    
20.    
    
21.     Range("F2").Value = Left(S, Len(S) - 1)
    
22. 
    
23. End Sub

复制代码

limonet

replace+反义