![间接引用另一个工作表的意外结果](https://linux22.com/image/1576998/%E9%97%B4%E6%8E%A5%E5%BC%95%E7%94%A8%E5%8F%A6%E4%B8%80%E4%B8%AA%E5%B7%A5%E4%BD%9C%E8%A1%A8%E7%9A%84%E6%84%8F%E5%A4%96%E7%BB%93%E6%9E%9C.png)
在以下场景中我没有得到预期的参考结果
有人可以解释一下吗?
创建一个包含 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")