我正在尝试编写一个线性回归函数，它可以动态引用列，可以处理 #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/