加权趋势线

Question

Excel为成对值集生成散点图。它还提供了为趋势线生成最佳拟合趋势线和公式的选项。它还生成气泡图，其中考虑了每个值提供的权重。但是，权重对趋势线或公式没有影响。下面是一组值的示例，以及它们的映射和权重。

    Value Map     Weight
    0       1      10
    1       2      10
    2       5      10
    3       5      20
    4       6      20
    5       1      1

对于Excel的趋势线，值5的映射对公式的影响太大了。有没有办法产生一个反映各自权重的公式？

作为帮助，我引入了五个连续值的加权平均值。但是他们是更好的方法吗？

Answer 1

对于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的y在X上的正态回归。如果const = TRUE，则回归矩阵是增广矩阵，该增广矩阵由一列后面跟着回归列组成，即X'=(1，X)。如果const = FALSE，则回归矩阵仅为X，因此运行包含一列1的回归会给出与不包含一列1并设置const=TRUE相同的估计值。

现在考虑加权最小二乘回归。回归现在是WX'=(W1，WX)上的Wy，其中W是由权重的平方根组成的对角矩阵。由于没有一列1，我们必须设置const = FALSE，并在回归矩阵中使用两列。

R平方计算

在第三行和第五行得到的第一个公式的LINEST输出中将stats设置为TRUE：

SSres = 59.76
SSreg(u) = 1461.24
SSTot(u) = 1521
Rsq(u) = 1 - 59.76/1521 = 0.9607 

注意:这些值是自const=FALSE以来的未输入版本(u) (有关详细信息，请参阅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

Answer 2

更新

基于您有数万行的附加信息，下面是将执行此工作的VBA UDF (包括r2)

根据下面的屏幕截图，它提供了与我的扩展数据集在原始答案中相同的m、x和r2值

​

​

Public Function LinestWeighted(xRng As Range, yRng As Range, wRng As Range, bInt As Boolean, bStat As Boolean) As Variant
    Dim x As Variant
    Dim y As Variant
    Dim W As Variant
    Dim TotX As Variant
    Dim TotY As Variant
    Dim lngRow As Long
    Dim strDelim As String
    Dim strX As String
    Dim strY As String
    Dim NewSeries As Variant

    x = Application.Transpose(xRng)
    y = Application.Transpose(yRng)
    W = Application.Transpose(wRng)
    strDelim = ","

    If (UBound(x, 1) = UBound(y, 1)) And (UBound(x, 1) = UBound(W, 1)) Then
        For lngRow = 1 To UBound(W)
            strX = strX & Application.WorksheetFunction.Rept(x(lngRow) & strDelim, W(lngRow))
            strY = strY & Application.WorksheetFunction.Rept(y(lngRow) & strDelim, W(lngRow))
        Next lngRow
        TotX = Split(Left$(strX, Len(strX) - 1), strDelim)
        TotY = Split(Left$(strY, Len(strY) - 1), strDelim)
        ReDim NewSeries(1 To UBound(TotX) + 1, 1 To 2)
        For lngRow = 0 To UBound(TotX)
            NewSeries(lngRow + 1, 1) = CDbl(TotX(lngRow))
            NewSeries(lngRow + 1, 2) = CDbl(TotY(lngRow))
        Next
        With Application
            LinestWeighted = .WorksheetFunction.LinEst(.Index(.Transpose(NewSeries), 2), .Index(.Transpose(NewSeries), 1), bInt, bStat)
        End With
    Else
        LinestWeighted = "input ranges must be equal in length"
        Exit Function
    End If
End Function

初始答案

只需按权重因子展开数据序列即可

因此，与其尝试绘制6对图，不如使用最高与最低的比率来重复这些点

即图

0       1     `10 times`  
1       2     `10 times`    
...
5       1     `once`    

​

​