В нескольких версиях 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 г.

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

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 по 2103 

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

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 версии 2014 или более поздней

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

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 ) )

Примечание: У полиномиальных линий тренда с заданными отсекаемыми отрезками числовых ошибок точности больше, чем у других типов линии тренда. 

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединение к программе предварительной оценки Майкрософт

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×