为什么我的 Excel 表格最后一行的索引超出范围?

为什么我的 Excel 表格最后一行的索引超出范围?

我正在尝试动态引用下表的最后一行:

在此处输入图片描述

我使用以下公式来确定最后一行是什么:=ROW(OFFSET(A2,COUNTA(Table2[Survey ID])-1,0))

在这种情况下,这将在单元格 M30 中返回 25

我试图用这个公式来索引表格的最后一行,即第 25 行,并索引表格最后一行的力量=INDEX(Table2[Force], $M$30)

在这种情况下它应该返回纽卡斯尔但我得到的只是#Ref!

我究竟做错了什么?

答案1

您的第一个公式是获取最后一个条目所在的行。然后,您将该值用作查找的索引值。

不幸的是,您遇到了不匹配的情况:您获得的行值比表中的数据行数大一,因为数据行上方有一个标题行。因此,您获得的是 25,这是最后一个数据所在的行。一切都很好,只是您随后INDEX()在数据行上使用,而不是在数据行加上标题行上使用。因此,当只有 24 行数据时,您会查找第 25 行数据。因此出现错误。

只需从第一个公式的结果中减一即可。以下任一方法:

=ROW(OFFSET(A2,COUNTA(Table2[Survey ID])-1,0)) - 1

或者

=INDEX(Table2[Force], $M$30 - 1)

可以工作,但第一种方法更“干净”,因为它使第一个结果成为完整的结果,因此完全包含在单个单元格中,而不是需要稍后处理的部分结果。(两种概念方法都有效,但这种方法更适合这种情况。如果您对值 25 有几种用途,并且只有一个或几个需要将其减少到 24,而其他人需要原始的 25,那么第二种方法可能会更好。或者不是,取决于这些用途。无论如何......)

按照“或者您可以用这种方式来做,然后讨论哪种方式更好,就像我在上一段中所做的那样”的思路……您可以以不同的方式更改查找公式,以便表格引用包含标题行,然后您的 25 就可以了:

=INDEX(Table2[[#All],[Force]], $M$30)

天哪,我很喜欢使用表格让一切变得如此简单……再也不会出现问题了。尽情享受炒作吧……撇开过去 15 年 MS 和帮助网站的胡言乱语不谈,表格引用与直接单元格引用一样微妙和复杂,唯一真正的价值是 MS 选择通过表格修复范围动态化问题,而不是通过简单范围提供简单的解决方案。因此,在使用表格时,请密切注意您生成的引用,因为并非每个公式失败都会导致像这里这样的明显错误。有些只是返回了看似合理但非常错误的值,并且可以持续数年。

答案2

表名: tbl --> K1:K7

n K 列 M 列
1 阿里克斯
2 4 7
3 6
4 1
5 2
6 8
7 20
Excel 365: 结果 细胞
=take(row(tbl[Arrx]),-1) 7
=take(tbl[Arrx],-1) 20
其他版本
=row(偏移量(K2,COUNT(tbl[Arrx])-1,0)) 7 M2
=INDEX(tbl[Arrx],$M$2- 1) 20

相关内容