这是用于匹配 Excel 公式中任何单元格引用的正则表达式吗？

Question

我一直在尝试创建一个正则表达式模式，该模式可以匹配任何 Excel 公式中的任何引用，包括绝对引用、相对引用和外部引用。我需要返回整个参考，包括工作表和工作簿名称。

我无法找到有关 Excel A1 表示法的详尽文档，但经过大量测试，我确定了以下内容：

• 公式前面带有等号“=”
• 公式中的字符串用双引号括起来，需要在查找真正的引用之前删除，否则 =A1&"A1" 会破坏正则
• 表达式 工作表名称的长度最多为 31 个字符，不包括 \ / ? * [ ] :
• 外部引用中的工作表名称必须以感叹号结尾 =Sheet1!A1
• 外部引用中的工作簿名称必须用方括号括起来 =[Book1.xlsx]Sheet1!A1=[Book1.xlsx]Sheet1!A1代码>
• 工作簿路径（如果引用是对封闭工作簿中的范围的引用，Excel 将添加该路径）始终用单引号括起来，并位于工作簿名称 'C:\[Book1.xlsx]Sheet1 的括号左侧'!A1
• 某些字符（例如不间断空格）会导致 Excel 将外部引用中的工作簿和工作表名称括在单引号中，但我不知道具体是哪些字符 ='[Book 1.xlsx]Sheet 1'!A1
• 即使启用了 R1C1 表示法，Range.Formula 仍会以 A1 表示法返回引用。 Range.FormulaR1C1 返回 R1C1 表示法中的引用。
• 3D 参考样式允许一个工作簿上的一系列工作表名称 =SUM([Book5]Sheet1:Sheet3!A1)
• 命名范围可以在公式中指定：
• 名称的第一个字符必须是字母，下划线字符 (_) 或反斜杠 (\)。名称中的其余字符可以是字母、数字、句点和下划线字符。
• 不能使用大写和小写字符“C”、“c”、“R”或“r”作为定义的名称，因为它们都用作在您选择当前选定单元格的行或列时的简写。在“名称”或“转到”文本框中输入它们。
• 名称不能与单元格引用相同，例如 Z$100 或 R1C1。
• 名称中不允许包含空格。
• 名称的长度最多可达 255 个字符。
• 名称可以包含大写和小写字母。 Excel 不区分名称中的大小写字符。

这是我想出的封装在 VBA 程序中进行测试的内容。我也更新了代码来处理名称：

Sub ReturnFormulaReferences()

    Dim objRegExp As New VBScript_RegExp_55.RegExp
    Dim objCell As Range
    Dim objStringMatches As Object
    Dim objReferenceMatches As Object
    Dim objMatch As Object
    Dim intReferenceCount As Integer
    Dim intIndex As Integer
    Dim booIsReference As Boolean
    Dim objName As Name
    Dim booNameFound As Boolean

    With objRegExp
        .MultiLine = True
        .Global = True
        .IgnoreCase = True
    End With

    For Each objCell In Selection.Cells
        If Left(objCell.Formula, 1) = "=" Then

            objRegExp.Pattern = "\"".*\"""
            Set objStringMatches = objRegExp.Execute(objCell.Formula)

            objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
            & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
            & "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
            & "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
            & "|[a-z]{1,3}\:[a-z]{1,3}" _
            & "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
            & "|[0-9]{1,7}\:[0-9]{1,7}" _
            & "|[a-z_\\][a-z0-9_\.]{0,254})"
            Set objReferenceMatches = objRegExp.Execute(objCell.Formula)

            intReferenceCount = 0
            For Each objMatch In objReferenceMatches
                intReferenceCount = intReferenceCount + 1
            Next

            Debug.Print objCell.Formula
            For intIndex = intReferenceCount - 1 To 0 Step -1
                booIsReference = True
                For Each objMatch In objStringMatches
                    If objReferenceMatches(intIndex).FirstIndex > objMatch.FirstIndex _
                    And objReferenceMatches(intIndex).FirstIndex < objMatch.FirstIndex + objMatch.Length Then
                        booIsReference = False
                        Exit For
                    End If
                Next

                If booIsReference Then
                    objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                    & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
                    & "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
                    & "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
                    & "|[a-z]{1,3}\:[a-z]{1,3}" _
                    & "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
                    & "|[0-9]{1,7}\:[0-9]{1,7})"
                    If Not objRegExp.Test(objReferenceMatches(intIndex).Value) Then 'reference is not A1
                        objRegExp.Pattern = "^(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                        & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)" _
                        & "[a-z_\\][a-z0-9_\.]{0,254}$"
                        If Not objRegExp.Test(objReferenceMatches(intIndex).Value) Then 'name is not external
                            booNameFound = False
                            For Each objName In objCell.Worksheet.Parent.Names
                                If objReferenceMatches(intIndex).Value = objName.Name Then
                                    booNameFound = True
                                    Exit For
                                End If
                            Next
                            If Not booNameFound Then
                                objRegExp.Pattern = "^(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
                                & "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)"
                                For Each objName In objCell.Worksheet.Names
                                    If objReferenceMatches(intIndex).Value = objRegExp.Replace(objName.Name, "") Then
                                        booNameFound = True
                                        Exit For
                                    End If
                                Next
                            End If
                            booIsReference = booNameFound
                        End If
                    End If
                End If

                If booIsReference Then
                    Debug.Print "  " & objReferenceMatches(intIndex).Value _
                    & " (" & objReferenceMatches(intIndex).FirstIndex & ", " _
                    & objReferenceMatches(intIndex).Length & ")"
                End If
            Next intIndex
            Debug.Print

        End If
    Next

    Set objRegExp = Nothing
    Set objStringMatches = Nothing
    Set objReferenceMatches = Nothing
    Set objMatch = Nothing
    Set objCell = Nothing
    Set objName = Nothing

End Sub

任何人都可以打破或改进这个吗？如果没有有关 Excel 公式语法的详尽文档，很难知道这是否正确。

谢谢！

Answer 1

我来晚了几年，但我一直在寻找类似的东西，所以对此进行了深入研究。您使用的主要模式是这样的：

objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
& "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
& "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
& "|\$[a-z]{1,3}\:\$[a-z]{1,3}" _
& "|[a-z]{1,3}\:[a-z]{1,3}" _
& "|\$[0-9]{1,7}\:\$[0-9]{1,7}" _
& "|[0-9]{1,7}\:[0-9]{1,7}" _
& "|[a-z_\\][a-z0-9_\.]{0,254})"

基本上，您有六个用于范围引用的替代方案（第 3-8 行），其中任何一个都将自行生成匹配项，还有两个用于可选文件名/工作表名称前缀的替代方案（第 1-2 行） ）。

对于两个前缀替代方案，唯一的区别是第一个前缀用单引号引起来，在初始引号后有一个额外的点星。这些单引号主要在工作表名称中存在空格时出现。点星的目的是匹配初始单引号后的不受约束的文本，这一点尚不清楚，而且似乎会产生问题。我将在下面讨论这些问题。除此之外，两个替代前缀是相同的，我将它们统称为可选外部前缀 (OEP)。

OEP 有自己的两个可选前缀（任一选项都相同）。第一个是工作簿名称，括号中是一个开放式点星。

(\[.*\])? 

第二个是“3D”单元格引用，两个工作表名称用冒号分隔；它是包含冒号的初始工作表名称。这里的模式是一个否定字符类，允许最多 31 个字符，除了正斜杠、反斜杠、问号、星号、方括号或冒号，后跟冒号：

([^\:\\\/\?\*\[\]]{1,31}\:)?

最后，对于 OEP 来说，它是唯一必需的部分：工作表名称，与可选工作表名称相同，但不带冒号。效果是（如果这些都正常工作）所需的工作表名称将尽可能匹配，然后仅当存在 3d 参考或其他先前的括号文本时，其可选前缀也会匹配。

工作簿/工作表名称前缀的问题：首先，第一行开头的点星包含过多。同样，工作表名称的否定字符类似乎需要其他字符，包括括号、逗号、加号、减号、等于和感叹号。否则，额外的材料将被解释为图纸名称的一部分。在我的测试中，这种过度包含发生在以下任何一个中：

=SUM(Sheet1!A1,Sheet2!A2)
=Sheet1!A1+Sheet2!A2
=Sheet1!A1-Sheet2!A2

工作表名称可以包含其中一些字符，因此考虑到这一点需要一些额外的措施。例如，一个工作表可以命名为“(Sheet1)”，给出一个奇怪的公式，例如：

=SUM('(Sheet1)'!A1:A2)

您希望获得带有工作表名称的内部括号，而不是外部括号。 Excel 在该单引号上添加单引号，就像在工作表名称中使用空格一样。然后，您可以在非单引号版本中排除括号，因为在单引号内是可以的。但要注意 Excel 似乎甚至允许在工作表名称中使用单引号。将这些命名怪癖发挥到极致，我刚刚成功地将一个工作表命名为“Hi'Sheet1'SUM('Sheet2'!A1,A2)!”。这很荒谬，但它指出了可能发生的事情。我在这样做时了解到，如果我在工作表名称中包含单引号，则公式会使用第二个单引号转义单引号。因此，引用我刚刚创建的工作表的 SUM(A1:A2) 最终看起来像这样：

=SUM('Hi''Sheet1''SUM(''Sheet2''!A1,A2)!'!A1:A2)

这实际上确实让我们对 Excel 解析器本身有了一些了解。我怀疑要充分处理这个问题，您可能需要单独（在正则表达式之外）将潜在的工作表名称或工作簿名称与实际的工作表名称进行比较，就像您对命名范围所做的那样。

这导致正则表达式中允许使用六种形式的单元格引用（其中任何一种，如果满足，都将产生匹配）：

1.) 具有行和列的单单元格或多单元格范围

"(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?"

此处的左括号已关闭在6个选项的最后。否则，此行允许在多单元格区域中进行“$A$1”、“A1”、“$A1”、“A$1”或这些类型的任意组合的基本单元格引用（“$A1:A$2 “， ETC。）。

2.) 仅具有绝对引用的全列或多列范围

"|\$[a-z]{1,3}\:\$[a-z]{1,3}"

此范围允许“$A:$B”类型的单元格引用，并且两者上都带有美元符号。请注意，仅一侧的美元符号将不匹配。

3.) 仅具有相对引用的全列或多列范围

"|[a-z]{1,3}\:[a-z]{1,3}"

此行与最后一行类似，但仅匹配没有美元符号的情况。请注意，仅一侧的美元符号此处也不匹配。

4.) 仅具有绝对引用的全行或多行范围

"|\$[0-9]{1,7}\:\$[0-9]{1,7}"

此行允许“$1:$2”类型的单元格引用，并且两者上都带有美元符号。

5.) 仅具有相对引用的全行或多行范围

"|[0-9]{1,7}\:[0-9]{1,7}" 

此版本与上一个版本类似，但仅匹配没有美元符号的情况。

6.) 可以是命名范围的其他文本

 "|[a-z_\\][a-z0-9_\.]{0,254})"

最后，第六个选项允许文本。该文本稍后将与子中的实际命名范围进行比较。

我在这里看到的主要遗漏是具有绝对和相对引用的范围，类型为“A:$A”或“1:$1”。虽然 $A:A 由于包含“A:A”而被捕获，但不会捕获“A:$A”。您可以通过组合 2 和 3 以及将 4 和 5 与可选的美元符号组合来解决这个问题并简化正则表达式：

objRegExp.Pattern = "(\'.*(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\'\!" _
& "|(\[.*\])?([^\:\\\/\?\*\[\]]{1,31}\:)?[^\:\\\/\?\*\[\]]{1,31}\!)?" _
& "(\$?[a-z]{1,3}\$?[0-9]{1,7}(\:\$?[a-z]{1,3}\$?[0-9]{1,7})?" _
& "|\$?[a-z]{1,3}\:\$?[a-z]{1,3}" _
& "|\$?[0-9]{1,7}\:\$?[0-9]{1,7}" _
& "|[a-z_\\][a-z0-9_\.]{0,254})"

进一步组合这些似乎会遇到一切都是可选的问题。

另一个问题是匹配字符串的初始正则表达式模式，您可以使用该模式来删除落在带引号的字符串内的潜在范围：
objRegExp.Pattern = "\"".*\"""
当我在公式开头和结尾都有字符串的公式上测试这一点时，点星的贪婪捕获了从初始引用到最终引用的所有内容（换句话说，它将整个公式解释为一个大引用字符串，即使中间有非绳材料）。看来您可以通过使点星变得懒惰（在其后添加问号）来解决此问题。这引发了关于引号内引号的问题，但它们可能不是问题。例如，我测试了这个公式：

="John loves his A1 steak sauce, but said the ""good A1 steak sauce price"" is $" & A2+A3 & " less than the ""bad price"" of $" & A4 & "."

插入单元格值后，该公式的计算结果为：

约翰喜欢他的 A1 牛排酱，但他说“好的 A1 牛排酱”
价格”比“糟糕价格”8 美元低 5 美元。

将惰性修饰符添加到字符串模式后，上面的两个版本“A1”都被识别为出现在字符串中，因此被删除，而 A2、A3 和 A4 则被删除。 我确信我上面的

一些语言存在一些技术问题，但希望分析仍然有用。

Answer 2

jtolle 引导我走向正确的方向。据我所知，这就是我试图做的事情。我一直在测试，它似乎有效。

stringOriginFormula = rangeOrigin.Formula
rangeOrigin.Cut rangeDestination
rangeOrigin.Formula = stringOriginFormula

谢谢杰托勒！

Answer 3

谢谢 Ben（我是新来的，尽管 Stackoverflow 多年来因其高质量的技术内容而引起了我的注意，所以我不确定我是否正确地为作者 J 阅读了此页面）

我尝试了发布的解决方案（测试，测试更新，以及使用 range.precendents 的测试（正确指出，不涵盖对其他工作表或其他工作簿的引用）并发现一个小缺陷：仅当外部工作表名称包含在“单引号”中时它是一个数字；如果它包含空格（可能还包含原始帖子中列出的 Ben (?) 等其他字符），只需在正则表达式中添加一个简单的内容（打开 [）即可纠正（添加“[”，请参阅下面的代码）此外，出于我自己的目的，我将 sub 转换为一个函数，该函数将返回一个删除重复项的逗号分隔列表（注意，这仅删除相同的引用符号，而不是包含在多个范围中的单元格）：

Public Function CellReflist(Optional r As Range)  ' single cell
Dim result As Object: Dim testExpression As String: Dim objRegEx As Object
If r Is Nothing Then Set r = ActiveCell ' Cells(1, 2)  ' INPUT THE CELL HERE , e.g.    RANGE("A1")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True: objRegEx.Global = True: objRegEx.Pattern = """.*?"""    ' remove expressions
testExpression = CStr(r.Formula)
testExpression = objRegEx.Replace(testExpression, "")
'objRegEx.Pattern = "(([A-Z])+(\d)+)"  'grab the address

objRegEx.Pattern = "(['\[].*?['!])?([[A-Z0-9_]+[!])?(\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?|\$?[A-Z]+:\$?[A-Z]+|(\$?[A-Z]+\$?(\d)+))"
If objRegEx.Test(testExpression) Then
    Set result = objRegEx.Execute(testExpression)
    If result.Count > 0 Then CellReflist = result(0).Value
    If result.Count > 1 Then
        For i = 1 To result.Count - 1 'Each Match In result
            dbl = False ' poistetaan tuplaesiintymiset
            For j = 0 To i - 1
                If result(i).Value = result(j).Value Then dbl = True
            Next j
            If Not dbl Then CellReflist = CellReflist & "," & result(i).Value 'Match.Value
        Next i 'Match
    End If
End If

End Function

Answer 4

我在 Google 表格中解决了类似的问题。

以下内容从公式中添加/减去行引用。因为我只需要更新行引用，而不是提取公式，所以我只是使用此 /((?<=[A-Za-z\$:\!])\d+( ?![A-Za-z\(!]))|(\d+(?=[:]))/

String.prototype.replaceAt = function(index, replacement, diff = 0) {
    let end = this.substr(index + replacement.length + diff)
    if((this.length - 1) === index) end = ""
    return this.substr(0, index) + replacement + end;
}
// Ref: https://stackoverflow.com/a/1431113/2319414

/**
 * @param row - positive integer to add, negative to subtract rows.
 */
function updateRowReference(formula, row){

  let masked = formula
  const mask = "#"

  // masking double quotes in string literals
  let exp = /""/g
  let result;
  while((result = exp.exec(masked)) !== null){
    masked = masked.replaceAt(result.index, new Array(result[0].length).fill(mask).join(""))
  }
  // masking string literals
  exp = /\"([^\\\"]|\\.)*\"/g
  // Ref: https://stackoverflow.com/a/9260547
  while((result = exp.exec(masked)) !== null){
    masked = masked.replaceAt(result.index, new Array(result[0].length).fill(mask).join(""))
  }

  // updating row references
  const sRow = row.toString()
  // The magic is happening here
  // Just matching a number which is part of range address
  exp = /((?<=[A-Za-z\$:\!])\d+(?![A-Za-z\(!]))|(\d+(?=[:]))/g
  while((result = exp.exec(masked)) !== null){
    const oldRow = Number(result[0])
    // adding/subtracting rows
    const newRow = (row + oldRow).toString()
    
    // preserving formula string length integrity if number of digits of new row is different than old row
    const diff = result[0].length - newRow.length
    masked = masked.replaceAt(result.index, newRow, diff)
    formula = formula.replaceAt(result.index, newRow, diff)
    exp.lastIndex -= diff
  }

  let updated = masked;

  // revert mask
  const array = formula.split("")
  while((result = updated.search(mask)) !== -1){
    updated = updated.replaceAt(result, array[result])
  }

  return updated
}

function test(){
  const cases = [
    "=$A$1", 
    "=A1", 
    "=$A1", 
    "=A$1", 
    "=$A1:B$1", 
    "=1:1", 
    "=Sheet1!1:1", 
    "=Sheet1!$A1:B$1", 
    "=Sheet1!A$1",
    '=IF(AND($C6 <> ""; NOT(ISBLANK(B$6))); IF(SUM(FILTER($F$6:$F$7;$C$6:$C$7 = $C6)) < $G6; 1; IF($E6 = 0; 1; 0)); 0)',
    "=$A$111", "=A111", "=$A111", "=A$111", "=$A111:B$111", 
    "=111:111", 
    "=Sheet1!111:111", 
    "=Sheet1!$A111:B$111", 
    "=Sheet1!A$111",
    '=IF(AND($C111 <> ""; NOT(ISBLANK(B$111))); IF(SUM(FILTER($F$111:$F$112;$C$111:$C$112 = $C111)) < $G111; 1; IF($E111 = 0; 1; 0)); 0)',

    // if string literals have addresses they shouldn't be affected
    '=IF(AND($C111 <> "A1 $A1 $A1:B$1";$C111 <> "Sheet1!1:1";$C111 <> "Sheet1!$A1:B$1"); 1 , 0)'
  ]

  const expectedAdd = [
    '=$A$16',
    '=A16',
    '=$A16',
    '=A$16',
    '=$A16:B$16',
    '=16:16',
    '=Sheet1!16:16',
    '=Sheet1!$A16:B$16',
    '=Sheet1!A$16',
    '=IF(AND($C21 <> ""; NOT(ISBLANK(B$21))); IF(SUM(FILTER($F$21:$F$22;$C$21:$C$22 = $C21)) < $G21; 1; IF($E21 = 0; 1; 0)); 0)',
    '=$A$126',
    '=A126',
    '=$A126',
    '=A$126',
    '=$A126:B$126',
    '=126:126',
    '=Sheet1!126:126',
    '=Sheet1!$A126:B$126',
    '=Sheet1!A$126',
    '=IF(AND($C126 <> ""; NOT(ISBLANK(B$126))); IF(SUM(FILTER($F$126:$F$127;$C$126:$C$127 = $C126)) < $G126; 1; IF($E126 = 0; 1; 0)); 0)',
    '=IF(AND($C126 <> "A1 $A1 $A1:B$1";$C126 <> "Sheet1!1:1";$C126 <> "Sheet1!$A1:B$1"); 1 , 0)' 
    ]

  let results = cases.map(_case => updateRowReference(_case, 15))

  console.log('Test Add')
  console.log(results.every((result, i) => result === expectedAdd[i]))

  console.log('Test Subtract')
  results = results.map(_case => updateRowReference(_case, -15))
  console.log(results.every((result, i) => result === cases[i]))
}

test()

地址为字符串的“INDIRECT”函数将不会更新