Excel如何为唯一测试系列创建动态LINEST计算数组？

需求说明 #

有Table1表格，包含5列：

• A、B、C：测试系列组合变量
• D：x值（压力）
• E：y值（长度）

需要为每个唯一的A/B/C系列组合，计算二次回归的LINEST结果，提取m₂（x²系数）、m（x系数）、b（截距）、R²四个值。原单系列公式需手动下拉适配所有唯一行，希望通过BYROW+LAMBDA实现动态数组自动更新。

原公式 #

• 生成唯一系列组合（G1单元格）：

  G1=UNIQUE(Table1[A]:Table1[C])
  

• 单系列回归计算（原K1公式）：

  K1 =LET(a,Table1[A],b,Table1[B],c,Table1[C],d,Table1[D], 
  e,Table1[E],x,FILTER(d,(a=G1)*(b=H1)*(c=I1)),
  y,FILTER(e,(a=G1)*(b=H1)*(c=I1)),
  l,LINEST(y,x^{1,2},,TRUE),
  HSTACK(INDEX(l,1,1),INDEX(l,1,2),INDEX(l,1,3),INDEX(l,3,1)))
  

示例表格 #

动态数组解决方案 #

将K1公式替换为以下内容，即可自动遍历所有唯一系列并生成结果：

K1 =LET(
    unique_series, G1#,
    a, Table1[A], b, Table1[B], c, Table1[C],
    d, Table1[D], e, Table1[E],
    BYROW(unique_series, LAMBDA(row,
        LET(
            x, FILTER(d, (a=INDEX(row,1))*(b=INDEX(row,2))*(c=INDEX(row,3))),
            y, FILTER(e, (a=INDEX(row,1))*(b=INDEX(row,2))*(c=INDEX(row,3))),
            valid, COUNTA(x)>=3,
            result, IF(valid,
                LET(l, LINEST(y, x^{1,2},,TRUE),
                    HSTACK(INDEX(l,1,1), INDEX(l,1,2), INDEX(l,1,3), INDEX(l,3,1))
                ),
                HSTACK(NA(), NA(), NA(), NA())
            ),
            result
        )
    ))
)

关键逻辑说明 #

1. BYROW遍历唯一系列：对G1#的每一行（每个A/B/C组合）执行回归计算
2. INDEX(row,n)提取系列值：从当前行中分别取出A、B、C的值，用于筛选对应的数据
3. 有效性判断：二次回归至少需要3个有效x值，无数据时返回NA()避免公式报错
4. 动态输出：结果为二维数组，自动填充所有系列对应的行，无需手动下拉

内容的提问来源于stack exchange，提问作者Jacob Kopecky