查找对单元格的所有引用 [英] Find all references to a cell

问题描述

我有以下数据

    -----------------------------------
.      A     B     C

 1    Data  Total  Left
 2    D1     10     5
 3    D2     20    13
 4    D3     30    21
 5   
 6   
 7    =A2   1     
 8    =A3   2
 9    =A4   3
 10   
 11   =A2   4
 12   =A3   5
 13   =A4   6   

    -----------------------------------

我正在寻找两件事。

有没有办法找到所有参考单元格？

Is there a way to find all references to a cell?

有没有办法增加单元格的字母？即当我找到参考A7（对于A2），我想得到B7的值。

Is there a way to increment the cell letter? i.e. When I find the reference A7(for A2), I want to get the value for B7.

推荐答案

对于第一个问题：

我不认为有办法通过编写Excel公式来查找对单元格的所有引用。有许多查找功能，但它们不在公式中搜索，只能搜索值。所以，您可以搜索D1（A2中您的示例中的值），并确定A7保持D1。例如，= Match（D1，A7：A13）应该给你一个行索引值。这样的搜索方案可能会工作，如果值足够独特，像主键值。

I don't think there is a way to find all references to a cell by writing Excel formulas. There are a number of lookup functions but they don't search within formulas, only values. So, you could search for "D1" (the value in A2 your example above) and identify that A7 holds "D1". For example, =Match("D1", A7:A13) should give you an row index value. Such a search scheme might work if the values are sufficiently unique, like a primary key value.

我甚至会困难，也许不可能找到所有使用VBA的参考，在最一般的情况下。您必须遍历单元格（或观察更改）并从文本中解析其公式以识别引用（yuk！）。如果可以限制具有很多帮助的引用的公式（例如限制简单地复制公式，如= A1或类似的），因为通常，公式可能相当复杂，并且不总是通过名称来确定确切的单元格被引用。以下面的公式为例。这是真的（在某种程度上）引用单元格A8，尽管它提到了A7。

I would even be difficult, and perhaps impossible to find all references using VBA, in the most general case. You'd have to iterate over cells (or watch for changes) and parse their formulas from text to identify references (yuk!). If you could restrict the kind of formulas having references that would help a lot (e.g. restrict to simply copy formula like =A1, or similar), because in general formulas can be quite complex and they don't always identify by name the exact cell being referenced. Take the formula below as an example. It is really (in some sense) referencing cell A8, though it mentions A7.

对于第二个问题， p>

For the second question,

=Offset(A7,0,1)

将获得A7旁边单元格的价值。

Will get you the value in the cell next to A7.

和

=Offset(A7, Match("D1", A7:A11) - 1, 1)

产生与D1匹配的单元格中的值。

yields the value in the cell next to the one that matches "D1".

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