В нескольких версиях Excel встроил ряд изменений во внутренние вычисления, чтобы исправить результаты вычисления линий тренда диаграммы там, где их пересечение с осями координат задано равным нулю (0). Эти изменения фактически не изменяют ни линию, ни ее внешний вид, а только вычисление R2, если вы нанесли эту пометку на диаграмму. Вычисление происходит каждый раз, когда открывается книга Excel. Следовательно, одна и та же книга может показывать различные вычисления в зависимости от используемой версии Excel.
Это относится к данным на диаграмме, представляющей собой последовательность чисел фиксированной длины, нанесенные на график в виде пар X и Y:
X = { x_1,x_2,…,x_N }
Y = { y_1,y_2,…,y_N }
Линия тренда данных — это уравнение, основанное на значениях, которые выражены как Z. Чтобы вычислить R2, значения 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(z2) = Сумма от i=1 до N значений z_i2 в наборе Z.
sum2(x)= ( sum(x) )2
ln(x) = натуральный логарифм x
ln2(x) = ( ln(x) )2
Mean(X) = sum(x) / N
Mean(ln(x)) = sum( ln(x) ) / N
С учетом этих двух последовательностей чисел, Y и Z, Excel вычисляет R2 следующими способами:
Версии Excel до 2005 г. (май 2020 г.)
Для полиномиальных, линейных и логарифмических линий тренда:
R2(Z,Y) = ( 2 N sum(yz) - N sum(z2) - sum2(y) ) / ( N sum(y2) - sum2(y) )
Для экспоненциальных и степенных линий тренда:
R2(Z,Y) = ( 2 N sum(ln(y) ln(z)) - N sum(ln2(z)) - sum2(ln(y)) ) / ( N sum(ln2(y)) - sum2(ln(y)) )
Версии Excel с 2005 г. (май 2020 г.) по 2103 г. (март 2021 г.)
Для полиномиальных и логарифмических линий тренда и линейных линий тренда без указания отсекаемого отрезка:
R2(Z,Y) = ( 2 N sum(yz) - N sum(z2) - sum2(y) ) / ( N sum(y2) - sum2(y) )
Для экспоненциальных и степенных линий тренда без указания отсекаемого отрезка:
R2(Z,Y) = ( 2 N sum(ln(y) ln(z)) - N sum(ln2(z)) - sum2(ln(y)) ) / ( N sum(ln2(y)) - sum2(ln(y)) )
Для линейных линий тренда с отсекаемым отрезком, не равным нулю:
R2(Z,Y) = sum2( ( y - Mean(Y) )( z - Mean(Z) ) ) / ( sum( ( z - Mean(Z) )2 ) sum( ( y - Mean(Y) )2 ) )
Для линейных линий тренда с отсекаемым отрезком, равным нулю:
R2(Z,Y) = sum(z2) / sum(y2)
Для экспоненциальных линий тренда с отсекаемым отрезком, не равным единице:
R2(Z,Y) = sum2( ( ln(y) - Mean(ln(y)) )( ln(z) - Mean(ln(z)) ) ) / ( sum( ( ln(z) - Mean(ln(z)) )2 ) sum( ( ln(y) - Mean(ln(y)) )2 ) )
Для экспоненциальных линий тренда с отсекаемым отрезком, равным единице:
R2(Z,Y) = sum( ln2(z) ) / sum( ln2(y) )
Excel версии 2104 (апрель 2021 г.) или более поздней
Для линейных линий тренда с отсекаемым отрезком, равным нулю:
R2(Z,Y) = sum(z2) / sum(y2)
Для линейных линий тренда без заданного отсекаемого отрезка, линейных линий тренда с заданным отсекаемым отрезком, не равным нулю, полиномиальных, логарифмических, экспоненциальных и степенных линий тренда:
R2(Z,Y) = sum2( ( y - Mean(Y) )( z - Mean(Z) ) ) / ( sum( ( z - Mean(Z) )2 ) sum( ( y - Mean(Y) )2 ) )
Примечание: У полиномиальных линий тренда с заданными отсекаемыми отрезками числовых ошибок точности больше, чем у других типов линии тренда.
