Excel为成对值集生成散点图。它还提供了生成最佳拟合趋势线和趋势线公式的选项。它还会生成气泡图，其中会考虑每个值提供的权重。但是，权重对趋势线或公式没有影响。这是一组示例值，以及它们的映射和权重。
Value Map Weight
0 1 10
1 2 10
2 5 10
3 5 20
4 6 20
5 1 1
使用Excel的趋势线，值5的映射对公式的影响太大。有什么方法可以生成反射(reflect)各个权重的公式？
作为帮助，我引入了五个连续值的加权平均值。但是他们是更好的方法吗？
最佳答案
根据标准加权最小二乘公式，使用A2:C7中的数据，您可以尝试:
=LINEST(B2:B7*C2:C7^0.5,IF({1,0},1,A2:A7)*C2:C7^0.5,0)
在E2:F2或任何2x1范围内使用CTRL + SHIFT + ENTER输入。这也会返回{1.1353,1.4412}。
对于Rsquared，您可以输入:
=INDEX(LINEST((B2:B7-SUM(B2:B7*C2:C7)/SUM(C2:C7))*C2:C7^0.5,IF({1,0},1,A2:A7)*C2:C7^0.5,0,1),3,1)
公式的解释
首先考虑使用LINEST对X进行y的正态回归。如果const = TRUE，则回归矩阵是由1的一列后跟回归列构成的扩充矩阵，即X'=(1，X)。如果const = FALSE，则回归矩阵只是X，因此使用包含一列的回归进行运行与使用不包含一列并设置const = TRUE进行回归的估计相同。
现在考虑加权最小二乘回归。在WX'=(W1，WX)上，回归现在为Wy，其中W是由权重的平方根组成的对角矩阵。由于没有一列，所以我们必须设置const = FALSE并在回归矩阵中使用两列。
Rsquared计算
在第三行和第五行中获得的第一个公式的LINEST输出中，将stats设置为TRUE:
SSres = 59.76
SSreg(u) = 1461.24
SSTot(u) = 1521
Rsq(u) = 1 - 59.76/1521 = 0.9607
请注意，这些值是非居中版本(u)，因为const = FALSE(有关更多信息，请参阅LINEST上的MS帮助)。对于居中版本(c)，我们需要减去加权平均值，如下所示:
SSTot(c) =SUMPRODUCT(C2:C7*(B2:B7-SUM(B2:B7*C2:C7)/SUM(C2:C7))^2) = 244.93
Rsq(c) = 1 - 59.76/244.93 = 0.756
关于excel - 加权趋势线，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11087773/