参考中使用TREND时Excel OFFSET功能错误 [英] Excel OFFSET function error when using TREND in reference
问题描述
考虑具有以下值的Excel工作表:
AB
--------
1 | 1 5
2 | 2 8
3 | 3 11
输入数组公式 = TREND(B1:B3,A1: A3,A1:A3)
在单元格C1:C3中返回 {5; 8; 11}
。输入 = OFFSET(C1:C3,1,0)
返回 {8; 11; 0}
但是,如果我尝试输入 = ROWS(OFFSET(TREND(B1:B3,A1:A3,A1:A3))1, 0))
,我收到消息,您键入的公式包含错误。我不能做任何事情来接受公式。
接下来,我使用公式 = Sheet1!$ C $ 1:$ C $ 3
和 = TREND(Sheet1!$ B $ 1:$ B $ 3,Sheet1!$ A $ 1:$ A $ 3,Sheet1! $ A $ 1:$ A $ 3)
。
再次, = OFFSET(TrendRange,1,0)
给出正确的结果,但 = OFFSET(TrendFormula,1,0)
不(它导致 #VALUE! / code>)。
有一个简单的测试,似乎总是识别OFFSET何时会出现这个问题。如果 = CELL(address,xxx)
或 = AREAS(xxx)
#VALUE!,那么xxx部分无法在OFFSET中使用。
有没有办法呢?我试过使用LINEST和SLOPE / INTERCEPT而不是TREND,但是我得到相同的结果。
OFFSET需要一个参考(一个单元格或一个范围作为第一个参数),它不会接受一个数组,所以因为一个公式,如
= TREND(B1:B3,A1:A3,A1:A3)
总是返回一个数组,你不能使用它作为OFFSET的第一个参数。这与您以前的问题有关吗?
Consider an Excel sheet with the following values:
A B
--------
1| 1 5
2| 2 8
3| 3 11
Entering the array formula =TREND(B1:B3,A1:A3,A1:A3)
in cells C1:C3 returns {5;8;11}
, as expected. Entering =OFFSET(C1:C3,1,0)
returns {8;11;0}
, also as expected.
However, if I try entering =ROWS(OFFSET(TREND(B1:B3,A1:A3,A1:A3),1,0))
, I get the message, "The formula you typed contains an error". I can't do anything to get it to accept the formula.
Next I created the named ranges "TrendRange" and "TrendFormula" with the formulae =Sheet1!$C$1:$C$3
and =TREND(Sheet1!$B$1:$B$3,Sheet1!$A$1:$A$3,Sheet1!$A$1:$A$3)
, respectively.
Again, =OFFSET(TrendRange,1,0)
gives the correct result but =OFFSET(TrendFormula,1,0)
does not (it results in #VALUE!
).
There is a simple test that seems to always identify when OFFSET will have this issue. If =CELL("address", xxx)
or =AREAS(xxx)
result in #VALUE!
, then the xxx section can't be used in OFFSET.
Is there any way around this? I've tried using LINEST and SLOPE/INTERCEPT instead of TREND, but I get the same result.
OFFSET requires a reference (a cell or a range as the first argument), it won't accept an array, so because a formula like
=TREND(B1:B3,A1:A3,A1:A3)
always returns an array, you can't use it as the first argument of OFFSET. Is this related to your previous question?
这篇关于参考中使用TREND时Excel OFFSET功能错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!