我正在尝试编写一个线性回归函数,它可以动态引用列,可以处理 #N/A
值,并且会随着时间的推移添加额外的行。这是一个示例数据集:
Date Value 1 Value 2
1/2/1991 #N/A #N/A
2/4/2002 276.36 346.31
1/7/2003 252 350
1/21/2004 232 345.5
1/6/2005 257 368
2/1/2006 278.24 390.11
2/23/2007 #N/A 380.46
2/11/2008 326.34 383.04
2/12/2009 #N/A 399.9
2/17/2009 334.39 #N/A
1/29/2010 344.24 400.83
1/27/2011 342.88 404.52
2/7/2012 379 417.91
1/23/2013 #N/A 433.35
这是我迄今为止基于 this forum post 开发的函数。它计算值 1 的线性回归。
=TRANSPOSE(
LINEST(
N(
OFFSET(
INDIRECT("B2" & ":B" & COUNTA(B:B)),
SMALL(
IF(
ISNUMBER(
INDIRECT("A2:A" & COUNTA($A:$A)) *
INDIRECT("B2" & ":B" & COUNTA(B:B))),
ROW(INDIRECT("B2:B" & COUNTA(B:B))) - ROW(B2)),
ROW(INDIRECT("1:" & MIN(
COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
COUNT(INDIRECT("B2:B" & COUNTA(B:B))))))), 0, 1)),
N(
OFFSET(
INDIRECT("A2:A" & COUNTA($A:$A)),
SMALL(
IF(
ISNUMBER(
INDIRECT("A2:A" & COUNTA($A:$A)) *
INDIRECT("B2:B" & COUNTA(B:B))),
ROW(INDIRECT("B2:B" & COUNTA(B:B))) - ROW(B2)),
ROW(INDIRECT("1:" & MIN(
COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
COUNT(INDIRECT("B2:B" & COUNTA(B:B))))))), 0, 1)),
TRUE, FALSE))
按照当前的编写方式,将数组向右拖动以求解 Value 2 需要对公式进行一些手动更新。
INDIRECT
公式中引号中的所有内容都必须手动从 B
更改为 C
。不过,我有 40 列数据,因此我尝试使用 ADDRESS
、 ROW
和 COLUMN
使公式完全动态:=TRANSPOSE(
LINEST(
N(
OFFSET(
INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2))),
SMALL(
IF(
ISNUMBER(
INDIRECT("A2:A" & COUNTA($A:$A)) *
INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))),
ROW(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))) - ROW(B2)),
ROW(INDIRECT("1:" & MIN(
COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
COUNT(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))))))), 0, 1)),
N(
OFFSET(
INDIRECT("A2:A" & COUNTA($A:$A)),
SMALL(
IF(
ISNUMBER(
INDIRECT("A2:A" & COUNTA($A:$A)) *
INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))),
ROW(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))) - ROW(B2)),
ROW(INDIRECT("1:" & MIN(
COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
COUNT(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))))))), 0, 1)),
TRUE, FALSE))
这给了我
#REF!
。当我对公式进行逐步评估时,看起来问题是在 Excel 评估 COLUMN
时出现的。它在公式中引入了大括号,它通过 INDIRECT
评估的其余部分传播。这是一个快速比较:原始公式:
INDIRECT("B2:B15")
动态公式:
INDIRECT({"$B$2:$B$15"})
这评估为
#VALUE
,此时公式的其余部分被破坏。有没有办法强制 Excel 在此评估中不使用大括号,或者是否有更好的方法进行此计算? 最佳答案
你只是想从线性回归中得到 SLOPE
吗?如果是这样,您可以在将 SLOPE
转换为空白后使用 #N/A
函数(在公式中使用 IFERROR
)。 SLOPE
然后将扔掉空白。如果您还需要截距,请使用下面相同的公式并将 INTERCEPT
替换为 SLOPE
。
范围图片
公式 是数组公式(使用 CTRL+SHIFT+ENTER)并复制过来。鉴于这种安排,简单的公式(非动态)将是:
=SLOPE(IFERROR(B2:B15,""),$A$2:$A$15)
如果您希望这些是动态的,您可以使用
INDEX
和 COUNTA
来获取动态范围。=SLOPE(IFERROR(B2:INDEX(B:B,COUNTA(B:B)),""),$A$2:INDEX($A:$A,COUNTA($A:$A)))
使用
Table
代替 更好的是,您可以在
Table
中定义这些数据,然后使用标题来拉入整列。这个公式看起来不错,而且很容易复制。这里仍然使用数组公式,但唯一的变量是用于查看
Table1
的列标题。这将更能抵抗数据中的空白,这会破坏上面使用的 COUNTA
。=SLOPE(IFERROR(INDEX(Table1,,MATCH(M1,Table1[#Headers])),""),Table1[Date])
关于excel - 在 Excel 中评估数组中的 INDIRECT,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30580691/