在Excel公式中查找所有使用的引用 [英] Find all used references in Excel formula

问题描述

以下是Excel中设置的示例，

  [column1] [column2] 
 
 A1 = C3-C5 
 
 A2 =（（C4-C6）/ C6）
 
 A3 = C4 * C3 
 
 A4 = C6 / C7 
 
 A5 = C6 * C4 * C3 
  

我需要提取使用的引用在公式中

例如，

 为A1，我只需要得到C3和C5。 
为A2，我需要得到C4和C6。 
  

解决方案

这是一个更新到：

将适用于本地表单引用，但不适用于偏移表。 - brettdj 5月14日14日11:55

通过使用Larrys方法，只需将objRegEx.Pattern更改为：

（[[A-Z0-9 _] + [！]）？（\\（$）$ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ ？\\ $ [AZ] + \ $（\d）+（？：\ $ [AZ] + \ $（\d）+？）| \ $ [AZ] +？：\\ \\ $？[AZ] + |（\ $？[AZ] + \ $？（\d）+））

这将：

1. 搜索可选的外部链接：（[']。 *？['！]）？


2. 搜索可选的表格参考：（[[A-Z0-9 _] + [ ！]）？


3. 按优先顺序执行以下步骤：


4. 搜索带行号的范围（和可选$）： \ $？[AZ] + \ $？（\d）+（：\ $？[AZ] + \ $？（\d）+）？


5. 搜索没有行号（和可选$）的范围： \ $？[AZ] +：\ $？[AZ ] +


6. 搜索1单元格引用（和可选$）：（\ $？[AZ] + \ $ ？（\d）+）

导致以下情况：

 子测试（）
 Dim result As Object 
 Dim r As Range 
 Dim testExpression As String 
 Dim objRegEx As Object 
 
设置r =单元格（1，2）'输入单元格，例如RANGE（A1）
设置objRegEx = CreateObject（VBScript.RegExp）
 objRegEx.IgnoreCase = True 
 objRegEx.Global = True 
 objRegEx.Pattern = 
 $ b testExpression = CStr（r.Formula）
 testExpression = objRegEx.Replace（testExpression，）
 objRegEx.Pattern =（（[AZ] ）+（\d）+）'抓住地址
 
 objRegEx.Pattern =（[']。*？['！]）？（[[A-Z0-9 _] + [！]？（？\ $ [AZ] + \ $（\d））+？（？：？\ $ [AZ] + \ $（\d）+）| \ $ ？[AZ] +：\ $？[AZ] + |（\ $？[AZ] + \ $？（\d）+））
如果objRegEx.test（testExpression） b $ b设置result = objRegEx.Execute（testExpression）
如果result.Count> 0然后
对于每个匹配结果
 Debug.Print Match.Value 
下一个匹配
 End If 
 End If 
 End Sub 
  

这样做，会给你所有可能的参考的价值，我可以想到。 （更新这篇文章，因为我需要解决问题）。

Below is the example set in Excel,

[column1] [column2]

A1  =C3-C5

A2  =((C4-C6)/C6)

A3  =C4*C3

A4  =C6/C7

A5  =C6*C4*C3

I need to extract the used references in formulas

For example,

for "A1", I simply need to get the C3 and C5.
for A2, I need to get the C4 and C6.

解决方案

This is an update to:

Will work for local sheet references, but not for references off-sheet. – brettdj May 14 '14 at 11:55

By Using Larrys method, just change the objRegEx.Pattern to:

(['].*?['!])?([[A-Z0-9_]+[!])?(\$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?|\$?[A-Z]+:\$?[A-Z]+|(\$?[A-Z]+\$?(\d)+))

This will:

1. Search for optional External links: (['].*?['!])?
2. Search for optional Sheet-reference: ([[A-Z0-9_]+[!])?
3. Do the following steps in prioritized order:
4. Search for ranges with row numbers (And optional $): \$?[A-Z]+\$?(\d)+(:\$?[A-Z]+\$?(\d)+)?
5. Search for ranges without row numbers (And optional $): \$?[A-Z]+:\$?[A-Z]+
6. Search for 1-cell references (And optional $): (\$?[A-Z]+\$?(\d)+)

Resulting in this:

Sub testing()
Dim result As Object
Dim r As Range
Dim testExpression As String
Dim objRegEx As Object

Set r = 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
        For Each Match In result
            Debug.Print Match.Value
        Next Match
    End If
End If
End Sub

Doing this, will give you the values of all possible references, I could think of. (Updated this post, because I needed the problem solved).

这篇关于在Excel公式中查找所有使用的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！