Взвешенная линия тренда

Excel создает точечные диаграммы для наборов парных значений. Это также дает возможность создать наиболее подходящую линию тренда и формулу для линии тренда. Он также создает пузырьковые диаграммы, которые учитывают вес, предоставленный каждому значению. Однако вес не влияет на линию тренда или формулу. Вот пример набора значений с их отображениями и весами.

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

С трендовой линией Excel отображение значения 5 слишком сильно влияет на формулу. Есть ли способ получить формулу, которая отражает соответствующие веса?

В качестве справки я ввел средневзвешенное значение для пяти последовательных значений. Но лучше ли это?

Источник

2 ответа

Решение

С данными в A2:C7, основанными на стандартной формуле взвешенных наименьших квадратов, вы можете попробовать:

=LINEST(B2:B7*C2:C7^0.5,IF({1,0},1,A2:A7)*C2:C7^0.5,0)

ввод с помощью CTRL+SHIFT+ ВВОД в E2:F2 или любом диапазоне 2x1. Это также возвращает {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)

Пояснение формулы

Сначала рассмотрим нормальную регрессию y на X с использованием LINEST. Если const = TRUE, матрица регрессора является расширенной матрицей, состоящей из столбца единиц, за которым следуют столбцы регрессора, то есть X'=(1,X). Если const = FALSE, то матрица регрессора - это просто X, поэтому выполнение регрессии со включенным столбцом дает те же оценки, что и выполнение без столбца единиц и установка const=TRUE.

Теперь рассмотрим взвешенную регрессию наименьших квадратов. Регрессия теперь Wy на WX'=(W1,WX), где W - диагональная матрица, состоящая из квадратного корня из весов. Поскольку столбец из них не представлен, мы должны установить const = FALSE и использовать два столбца в матрице регрессора.

Rsquared Расчет

Установив stats в значение TRUE в выводе LINEST первой формулы, мы получаем в третьей и пятой строках:

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

Обратите внимание, что эти значения являются нецентрированными версиями (u), поскольку const=FALSE (для получения дополнительной информации обратитесь к справке MS по LINEST.) Для центрированных версий (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
Источник

Обновить
На основе дополнительной информации о том, что у вас есть десятки тысяч строк, вот UDF VBA, который выполнит эту работу (включая 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`

Источник
Другие вопросы по тегам