如何使用偏移函数在excel中获得不同数据集的斜率

Question

我有一个数据集，其结构使得表示年份的x值从1到18，但该数据集包含500多个从1到2018的年份实例，每个实例的y值不同。

x_values    y_values
1             0.10
2             0.20
3             0.25
.             .
.             .
.             .
18            16.7
1             0.13
2             0.18
3             0.22
.             .
.             .
.             .
18            17.1

其中该模式重复超过500次。

我想使用斜率函数计算每个实例的斜率。

在之前的场景中，我想找到每个实例的平均y值，并使用了以下公式：

=AVERAGE(OFFSET($B$2,(ROW()-ROW($C$2))*18,,18,))

其中$B$2是我的参考单元格，指示第一个实例中的第一个y值。

我想用一个类似的公式来计算斜率，但到目前为止，当我使用这个公式时，我得到了一个#N/a错误：

=SLOPE(OFFSET($B$2,(ROW()-ROW($D$2))*18,,18,),$A$2:$A$19)

其中范围$A$2:$A$19表示18个x值。

如有任何关于为什么会出现此错误以及如何修复的帮助，我们将不胜感激。

Answer 1

如果您使用Evaluate formula遍历此公式，您将看到行号周围有花括号，因此row函数提供了一个单一元素数组。您可以使用Index:将它们制作成标量

=SLOPE(OFFSET($B$2,INDEX((ROW()-ROW($D$2))*18,1),0,18,1),$A$2:$A$19)

注1:

最好使用Index，避免使用像Offset:这样的不稳定函数

=SLOPE(INDEX(B:B,(ROW()-ROW($B$2))*18+2):INDEX(B:B,(ROW()-ROW($B$2))*18+19),$A$2:$A$19)

注2:

我不能完全解释为什么你会用Slope得到这种行为，但Average是可以的。看起来Average可以处理这样的数组：

=SUMPRODUCT(AVERAGE(OFFSET(A2,{1,2},0,1,1)))

而Slope不能，但它仍然不能完全解释你在逐步了解公式时看到的内容。