excel - 偏移函数返回#VALUE!数据集较小

Question

我在 Excel 中有一些数据，想要使用偏移函数访问某些行，但是该函数为我的小数据集返回 #VALUE!。

使用这个，OFFSET(A2;0;0;COUNTA(A2:A99999);1)我得到#VALUE!。将公式更改为 OFFSET(A2;0;0;100;1) 时，我得到一个值。

有什么建议吗？我做错了什么？

感谢您的回复!

Answer 1

如果 =OFFSET(A2;0;0;COUNTA(A2:A99999);1) 是您的完整公式，则由于推断，您将得到带有硬编码数字的结果位置(我希望这是正确的术语)。这意味着当您引用一个范围但未处理整个范围时，操作单元格是相对于当前位置的单元格。

如果您将=$A$2:$A$11放入D2中并向下填充，您将根据与A列匹配的行位置得到处理结果。继续向下填充，您将得到#VALUE! 当您越过第 11 行时出错。

这就是为什么 =OFFSET(A2; 0; 0; 100; 1) 返回零而 =OFFSET(A2; 0; 0; COUNTA(A2:A99999); 1 ) 返回 #VALUE! 错误。第一个公式与 =$A$2:$A$101 相同，它返回零，因为您将公式放入第 101 行上方的单元格中(引用 A 列中的空白单元格)返回值0)。第二个(使用 COUNTA function )就像说 =$A$2:$A$11 并且您将其放入第 13 行(第 11 行下方)的单元格中。

附录:

相关说明中， INDEX function 的这些变体和 MATCH function是非 volatile 的并且比 OFFSET function 更实用同时提供与您使用 OFFSET 和 COUNTA function 实现的相同的单元格范围引用.

'for text in a column
=$A$2:INDEX($A:$A, MATCH("zzz", $A:$A))

'for numbers in a column
=$A$2:INDEX($A:$A, MATCH(1e99, $A:$A))

'for unknown in a column
=$A$2:INDEX($A:$A, IFERROR(MATCH(1e99, $A:$A), MATCH("zzz", $A:$A)))

'for combined in a column
=$A$2:INDEX($A:$A, MAX(MATCH(1e99, $A:$A), MATCH("zzz", $A:$A)))

'I don't like COUNTA but it works as long as there are no blanks
=$A$2:INDEX($A:$A, COUNTA($A:$A))

此方法有许多变体。也许您可以使用上述其中一种来达到您的目的。