Excel合并拆分公式

看过我直播Excel实战在线答疑的朋友应该知道，对于知友的问题，我一般都会提供Excel VBA和Excel公式两种解决方案，在一次针对于将日期字符串转化为特地维度的日期时，通过Excel 公式先获取每一个维度的时间，最后再将公式进行组装的时候，替换引用公式一直因为替换错误导致公式错误，最终不得不使用Excel VBA自定义函数来实现。

在那次”失败”的直播演示后，我在思考，在Excel使用公式的时候，我们通过每一个细分的公式来帮助我们梳理需求，最后再拼接公式是一个最正常不过且十分适用的功能，如果因为最后组装公式的时候一直报错，那是很影响效率和心情的。

所以实现了一个自定义的函数来帮助我们自动组装公式，具体的实现逻辑是通过遍历公式的每一层单元格引用，将引用的单元格替换从而得到最终的公式

示例函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
'知乎@米可爱分享
'https://www.zhihu.com/people/chi-ke-ai-chang-da-de-ai-xian-nu-88
'将Excel单元格的公式合并
Function LoopFormulaFun(rng As Range)
    Debug.Print (LoopFormula(rng.FormulaLocal))
    LoopFormulaFun = LoopFormula(rng.FormulaLocal)
End Function
Function LoopFormula(formula As String)
    Dim Replace_Str As String
    Dim regexObject As RegExp
    Dim result As String
    Dim tempRange As Range
    Dim tempRangeFormula As String
    Set regexObject = New RegExp
    With regexObject
        .Pattern = "\$?[A-Z]+\$?\d+"
        .Global = True
    End With
    result = formula
    For Each v In Split(formula, ",")
        If InStr(v, ":") = 0 Then
            Set matches = regexObject.Execute(v)
            For Each Match In matches
              Debug.Print Match.Value 'Result: Helo Hello Hellllo
              Set tempRange = Range(Match.Value)
              If tempRange.Text <> tempRange.FormulaLocal Then
                    tempRangeFormula = tempRange.FormulaLocal
                    If Left(tempRange.FormulaLocal, 1) = "=" Then
                        tempRangeFormula = Right(tempRange.FormulaLocal, Len(tempRange.FormulaLocal) - 1)
                    End If
                    midFormula = LoopFormula(tempRangeFormula)
                    result = Replace(result, Match.Value, midFormula)
              End If
            Next Match
        End If
    Next
    
    LoopFormula = result
End Function

FAQ

  • Dim regexObject As RegExp User-defined type not defined
    添加引用Microsoft VBScript Regular Expressions x.x