Изменения внутренних вычислений линий тренда на диаграмме

В нескольких версиях Excel встроил ряд изменений во внутренние вычисления, чтобы исправить результаты вычисления линий тренда диаграммы там, где их пересечение с осями координат задано равным нулю (0). Эти изменения фактически не изменяют ни линию, ни ее внешний вид, а только вычисление R², если вы нанесли эту пометку на диаграмму. Вычисление происходит каждый раз, когда открывается книга Excel. Следовательно, одна и та же книга может показывать различные вычисления в зависимости от используемой версии Excel. 

Где были применены эти изменения вычислений

Это относится к данным на диаграмме, представляющей собой последовательность чисел фиксированной длины, нанесенные на график в виде пар X и Y:

X = { x_1,x_2,…,x_N }

Y = { y_1,y_2,…,y_N }

Линия тренда данных — это уравнение, основанное на значениях, которые выражены как Z. Чтобы вычислить R², значения Z линии тренда оцениваются по всем одинаковым значениям X:

Z = { z_1,z_2,…,z_N }

Например, если уравнение линии тренда:

Z(x) = 2*e^(4x)

, то множество Z вычисляется для каждого значения X:

Z = { Z(x_1), Z(x_2), …, Z(x_N) }

Где:

sum(y) = Сумма от i=1 до N значений y_i в наборе Y.

sum(z*²) = Сумма от i=1 до N, значение z_i²* в наборе Z.

sum²(x)= ( sum(x) )²

ln(x) = натуральный логарифм x

ln²(x) = (ln(x) )²

Mean(X) = sum(x) / N

Mean(ln(x)) = sum( ln(x) ) / N

Изменения конкретной версии и как они влияют на линии тренда

С учетом этих двух последовательностей чисел, Y и Z, Excel вычисляет R² следующими способами:

Версии Excel до 2005 г. (май 2020 г.)

Для полиномиальных, линейных и логарифмических линий тренда:

R²(Z,Y) = ( 2 N sum(yz) - N sum(z²) - сумма²(y) ) / ( N sum(y²) - сумма²(y) )

Для экспоненциальных и степенных линий тренда:

R²(Z,Y) = ( 2 N sum(ln(y) ln(z)) - N sum(ln²(z)) - сумма²(ln(y)) ) / ( N sum(ln²(y)) - сумма²(ln(y)) )

Версии Excel с 2005 г. (май 2020 г.) по 2103 (март 2021 г.)

Для полиномиальных и логарифмических линий тренда и линейных линий тренда без указания отсекаемого отрезка:

R²(Z,Y) = ( 2 N sum(yz) - N sum(z²) - сумма²(y) ) / ( N sum(y²) - сумма²(y) )

Для экспоненциальных и степенных линий тренда без указания отсекаемого отрезка:

R²(Z,Y) = ( 2 N sum(ln(y) ln(z)) - N sum(ln²(z)) - сумма²(ln(y)) ) / ( N sum(ln²(y)) - сумма²(ln(y)) )

Для линейных линий тренда с отсекаемым отрезком, не равным нулю:

R²(Z,Y) = sum²( ( y - Mean(Y) )( z - Mean(Z) ) ) / ( sum( ( z - Mean(Z) )² ) sum( ( y - Mean(Y) )² )

Для линейных линий тренда с отсекаемым отрезком, равным нулю:

R²(Z,Y) = sum(z²) / sum(y²)

Для экспоненциальных линий тренда с отсекаемым отрезком, не равным единице:

R²(Z,Y) = sum²( ( ln(y) - Mean(ln(y)) )( ln(z) - Mean(ln(z)) ) ) / ( sum( ( ln(z) - Mean(ln(z)) )² ) sum( ( ln(y) - Mean(ln(y)) )² ) ) )

Для экспоненциальных линий тренда с отсекаемым отрезком, равным единице:

R²(Z,Y) = sum( ln²(z) ) / sum( ln²(y) )

Excel версии 2104 (апрель 2021 г.) или более поздней

Для линейных линий тренда с отсекаемым отрезком, равным нулю:

R²(Z,Y) = sum(z²) / sum(y²)

  

Для линейных линий тренда без заданного отсекаемого отрезка, линейных линий тренда с заданным отсекаемым отрезком, не равным нулю, полиномиальных, логарифмических, экспоненциальных и степенных линий тренда:

R²(Z,Y) = sum²( ( y - Mean(Y) )( z - Mean(Z) ) ) / ( sum( ( z - Mean(Z) )² ) sum( ( y - Mean(Y) )² )