在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。 
  

解决方案

此函数返回一个逗号分隔的源单元格列表（先例）：

函数引用（rngSource As Range）As Variant
Dim rngRef As Range
Dim strTemp As String
On Error Resume Next
对于每个rngRef在rngSource.Precedents.Cells
strTemp = strTemp&，&rngRef.Address（False，False）
下一个
如果Len（strTemp）0然后strTemp = Mid（strTemp，3）
参考= strTemp
结束函数

但是，请注意，您不能在工作表中将其用作UDF，因为 rngRef.Address 不幸导致循环引用。但是，您可以在一小步骤中使用它来填充另一列，例如。

 
 Sub ShowPrecedents（）
 Dim rng As Range 
'将A1：A6的先例粘贴到D1：D6 
对于每个rng范围（D1：D6）
 rng.Value =引用（rng.Offset（，-3））
下一个
 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 function returns you a comma separated list of source cells (precedents):

Function References(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next
    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp)  0 Then strTemp = Mid(strTemp, 3)
    References = strTemp
End Function

However, please note that you cannot use this as a UDF in the worksheet, as rngRef.Address unfortunately causes a circular reference. However, you can use it in a small procedure to populate another column, e.g.

Sub ShowPrecedents()
    Dim rng As Range
    'Will paste precedents of A1:A6 into D1:D6
    For Each rng In Range("D1:D6")
        rng.Value = References(rng.Offset(, -3))
    Next
End Sub

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