☰

如何在Excel中获取公式返回的地址

tag-icon • tag-icon microsoft-excel worksheet-function hyperlink
如何在Excel中获取公式返回的地址

我有一张工作表，其中显示的数据基于组合框选择。这些值使用如下公式计算：

INDEX(INDIRECT($Q61),SUMPRODUCT((INDIRECT($O$5)=$B$1)*(INDIRECT($O$6)=$B$2)*(INDIRECT($O$3)=$B$3)*(INDIRECT($O$7)=B$4)*ROW(INDIRECT($O$8)))-5),"")

Q61 是不同工作表中的范围：“数据”！$AO$6：$AO$104000 SUMPRODUCT 根据组合框选择匹配其他四个条件。

公式应计算范围内的行并返回一个值。VBA 中是否有方法可以确定该公式找到的地址。它应该是类似 'Data'!$AO$90 的内容。我必须获取该地址，因为我正在该地址中查找超链接。

INDEX 公式正确返回“数据”中的值！$AO$90。我想恢复超链接。

这是工作表的图像。每个值都使用如上所示的 INDEX 公式计算。一些值应该有超链接。我必须在“数据”中获取原始地址！工作表，以便在用户进行新选择时使用 VBA 将它们放回原处。

在此处输入图片描述

答案1

从'Data'!$AO$6:$AO$104000范围中，您可以获取中的值'Data'!$AO$90。这就是标准 index() 函数的作用。

由于您只需要的地址，'Data'!$AO$90因此可以通过二分用于获取它的 index() 函数来获取它。这个想法是……因为index(<range>,<RowNumber>,<ColNumber>)，并且在这种情况下让 sat 为 85。所以您需要的“地址”是

= "'Data'!$AO$"&(<RowNumber>+5)

尝试一下，希望有帮助。（：

答案2

INDEX()可以返回单元格引用（地址）而不是值。

（这听起来就像您会设置一个参数并获取单元格中的值或单元格的地址。但很遗憾，事实并非如此。您需要做的是在上下文中使用该函数，该上下文只有在返回地址而不是其中的值时才有意义。然后 Excel 就会这样做。当两种方式对它都有意义时，并且只有值才有意义时，它才会默认返回一个值。）

有很多方法可以做到这一点。但最简单的方法是使用函数CELL()，请求地址：

=CELL("address",INDEX(range,row,column))

这将返回准确的单元格地址。该地址是文本，但与某些此类返回不同，INDIRECT()它将很好地识别它并返回那里的值。这基本上看起来毫无意义！但这意味着几乎任何函数都应该直接接受它，因此任何用作文本的行为都应该是有效的。

（我知道这是在评论中，但是因为我想扩展这个主题，解释它而不是只让“提及”停留在表面，并将它与 Excel 公式世界中的其他一些“点”联系起来，所以我做出了这个回答。）

与返回文本的函数相比，看起来是这样，但它实际上是一个数组常量，从中选择第一个元素进行返回。（因此，它真正处理的是 `{“a”,2;“HH”,298;} 等。干净的返回更容易处理，因为它是围绕该点工作的另一个函数的输入。无论如何……这是一个干净的返回，这有所不同。）

由于范围未锚定在 A1 中，因此无需偏移。它是实际的单元格地址，而不是范围内的相对地址。因此它简化了很多事情。当然，我想有时你会想要这些偏移，但你可以轻松地将它们添加回来。

我们曾经使用 - 返回的单元格地址来实现FILTER()类似结果，INDEX()从而得到一系列行。想象一组按日期整齐排列的数据。（以这种方式输入，排序，无所谓。）使用INDEX/MATCH查找具有某个日期（可能是 2/15）的第一个单元格的地址。这样可以得到所需的行，并且您知道数据中使用的第一列（或者可以轻松找出它是动态的还是在命名范围内），因此您可以轻松生成所需范围内的第一个单元格，即您正在创建的地址。然后使用INDEX/MATCH下一个日期（本例中为 2/16）但使用一个巧妙的技巧：使用另一个巧妙的技巧（过去INDEX()没有）反转查找数组，将一个递减的值数组传递给它。这允许使用匹配类型“大于”，因此如果不是完全匹配，它会找到下一个最高日期。这会给您一个高一行的地址，因此您可以获得该行并减去一个。您知道（或可以轻松找到...）使用的最后一列，因此您已经为具有所需日期的数据范围创建了结束地址。SEQUENCE()MATCH()

巧妙的技巧：

=4-MATCH(H1+1,INDEX(G1:G3,4-ROW(1:3),),-1)

对于简单的 1 列 3 行表格 G1:G3，您可以使用ROW()通常的方式创建一个数组常量，{1,2,3}这本身并没有什么用，但如果从比函数中最高行高一个的值中减去（3 是最高行，所以是 4），就会得到表格，{3,2,1}并且表格以相反的顺序显示，在我的示例中是“降序”，因此您可以在函数中使用“大于”匹配类型MATCH()。这样会得到倒置表格中的行，因此您可以从相同的 4 中减去该行，以获得“真实”表格中的行。巧妙的技巧，不是吗？

您有范围的这两个部分，只需在公式的两半之间放置一个“：”，如下所示：

=INDEX(first half of the desired range address):INDEX(second half of the desired range address)

就像这样。实际上，两个计算之间只需一个冒号，甚至不需要双引号或与号。

FILTER()显然是更可取的，但您可以看到该功能的使用方式。通过在公式的两部分之间放置冒号，您可以让 Excel 完全明白需要的是地址，而不是值，并且它会很乐意为您提供地址（引用）。

多年来我一直被困在思维中，认为我只是不知道一个参数，关于这个主题的文档和互联网帮助都是垃圾，因为它们没有告诉我哪些小事不应该做。（这些都是垃圾……真的，你知道……因为只有在你已经知道了“一件小事”之后才会清楚，这对我没有任何好处，所以：垃圾。有点像终极的“人群”事物。）

一旦我意识到它从来都不在我们的直接控制之下，也永远不会，没有“一件小事”需要知道和去做，一切都清楚了。这也适用于微软资源和互联网上大量其他不切实际的帮助。它能帮你做的事情远不止这些。

相关内容