本文介绍了在Excel公式中查找所有使用的引用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下是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公式中查找所有使用的引用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!