偏移函数返回#VALUE！与小数据集 [英] Offset function returns #VALUE! with small dataset

问题描述

我有一些excel中的数据，并希望使用偏移量函数访问某些行，但是该函数为我的小数据集返回 #VALUE！。

使用这个 OFFSET（A2; 0; 0; COUNTA（A2：A99999）; 1）我得到 #VALUE！。将公式更改为 OFFSET（A2; 0; 0; 100; 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函数）就像说 = $ A $ 2：$ A $ 11 ，然后将其放入第13行的单元格（第11行）。

Addndum：

在相关注释中， INDEX函数和 MATCH函数是非易失性的，比 OFFSET功能，同时提供相同的单元格范围引用使用OFFSET实现的目标以及 COUNTA功能。

 '列
 = $ A $ 2：INDEX（$ A：$ A，MATCH（zzz，$ A：$ A））
 
'用于列中的数字
 = $ A $ 2：INDEX（$ A：$ A，MATCH（1e99，$ A：$ A））
 
'列中未知的
 = $ A $ 2：INDEX（$ A：$ A，IFERROR（MATCH（1e99，$ A：$ A），MATCH（zzz，$ A：$ A）））
 
'用于组合列
 = $ A $ 2：INDEX（$ A：$ A，MAX（MATCH（1e99，$ A：$ A）），MATCH（zzz，$ A：$ A）））
 
'I不喜欢COUNTA，但只要没有空格就可以工作
 = $ A $ 2：INDEX（$ A：$ A，COUNTA（$ A：$ A））
  

此方法有很多变体。也许您可以使用上述之一为您的目的。

I have some data in excel and want to access certain rows with the offset function, however the function returns #VALUE! for my small dataset.

Using this, OFFSET(A2;0;0;COUNTA(A2:A99999);1) I get #VALUE!. When changing the formula to OFFSET(A2;0;0;100;1) I get a value.

Any suggestions, what I am doing wrong?

I appreciate your replies!

解决方案

If =OFFSET(A2;0;0;COUNTA(A2:A99999);1) is your full formula you are getting a result with a hard-coded number due to Inferred Position (I hope that's the right term). This means that when you are referencing a range but are not processing the full range the operative cell is the one relative to the current position.

If you put =$A$2:$A$11 in D2 and fill down you will get processive results according to your row position that match column A. Keep filling down and you will get the #VALUE! error when you go past row 11.

This is why =OFFSET(A2; 0; 0; 100; 1) returns a zero and =OFFSET(A2; 0; 0; COUNTA(A2:A99999); 1) returns a #VALUE! error. The first formula is the same as saying =$A$2:$A$101 and it is returning zero because you put the formula in a cell that was above row 101 (referencing a blank cell in column A that returned a value of 0). The second (with the COUNTA function) is like saying =$A$2:$A$11 and you are putting it into a cell in row 13 (below row 11).

Addndum:

On a related note, these variations of the INDEX function and MATCH function are non-volatile and more functional than the OFFSET function while providing a cell range reference identical to what you were achieving with OFFSET and the 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))

There are many variations on this method. Perhaps you can use one of the above for your purposes.

这篇关于偏移函数返回#VALUE！与小数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！