☰

间接引用另一个工作表的意外结果

tag-icon • tag-icon microsoft-excel-2010 vba microsoft-office
间接引用另一个工作表的意外结果

在以下场景中我没有得到预期的参考结果

有人可以解释一下吗？

创建一个包含 2 个 WorkSheets （Sheet1 和 Sheet2）的工作簿

在 Sheet2 Col A1 Down 中填充以下内容

“标题”、“第 2 行”、“第 3 行”、“第 4 行”、“第 5 行”、“第 6 行”、“第 7 行”、“第 8 行”、“第 9 行”

在 Sheet2 本地创建一个命名范围“Header”，引用 Sheet2!A1

填充 Sheet1 Col A1 至 Row9 （含）以下内容

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0,COUNTA(INDIRECT("Sheet2"&"!$A:$A")​​),1)

填充 Sheet1 B2 至 B9 以下内容

="行是 " & ROW(OFFSET(INDIRECT("Sheet2"&"!$Header"),0,0,COUNTA(INDIRECT("Sheet2"&"!$A:$A")​​),1))

我预期的结果

我预期的结果

我得到的结果

我得到的结果

假设没有使用相对引用，并且 Sheet1 Col B 中的公式返回了“正确/预期”行引用，但在 Sheet1 Col A 中却得到了意外结果；这是怎么回事？

PS：我必须在一张表上进行数据验证，引用其他表上的动态本地引用，并且需要理解上述结果才能使其成功。

答案1

该OFFSET函数返回一个范围。使用您使用的参数，它是一个 9 x 1 范围（高度=9 行，宽度=1 列），引用 Sheet2 中的 A1:A9。

正如您所见，范围的行为有时令人惊讶。它因您使用范围的方式而异。

  • 当您将ROW函数应用于某个范围时，它会返回该范围的顶行的数字 - 无论您从哪里使用该函数。这是Row is 1您在 B 列中得到的重复值。
  • 但当你提到内容范围 - 就像您在 Sheet1 的 A 列中的公式中所做的那样 - 您引用的位置很重要。Excel 将返回范围内单元格的内容在同一高度作为您使用引用的单元格。

对其他范围的引用也是如此。例如，如果你将

=Sheet2!A:A

在 Sheet1 的单元格 D4 中，它将链接到 Sheet2 的单元格 A4：即与您链接到的范围内高度相同的单元格，包含Row4。或者，如果您为单元格块 A1:A9 创建命名范围Alldata，则在单元格 F7 中

=Alldata

将为您提供对 A7 的引用，其中包含Row7。

因此，如果您只想引用顶部的单元格，则可以将其设为单元格引用而不是范围（即 1x1 范围）：

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0,1,1)

或者完全省略高度和宽度尺寸：

=OFFSET(INDIRECT("Sheet2"&"!Header"),0,0)

或者直接链接到单元格：

=INDIRECT("Sheet2"&"!Header")

相关内容