我正在尝试编写一个线性回归函数,它可以动态引用列,可以处理 #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 列数据,因此我尝试使用 ADDRESSROWCOLUMN 使公式完全动态:
=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)

如果您希望这些是动态的,您可以使用 INDEXCOUNTA 来获取动态范围。
=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/

10-11 22:44
查看更多