Как экстраполировать данные в excel (с примером)

Экстраполяция — это процесс использования значений исторических данных для прогнозирования будущих значений.

Самый простой способ экстраполяции данных в Excel — использовать функцию ТЕНДЕНЦИЯ , которая использует следующий синтаксис:

ТРЕНД(известные_y, [известные_x], [новые_x], [const])

Золото:

• known_y’s : диапазон известных значений y.
• известные_x : диапазон известных значений x.
• new_x’s : диапазон новых значений x.
• const : нужно ли приводить константу в уравнении линии тренда к нулю.

Примечание . Функция ТРЕНД предназначена для использования с данными, которые следуют линейному тренду. Для наборов данных с нелинейным трендом необходимо использовать более продвинутые методы .

В следующем пошаговом примере показано, как использовать функцию ТЕНДЕНЦИЯ для практической экстраполяции данных в Excel.

Шаг 1. Создайте набор данных

Сначала введем в Excel значения из следующего набора данных:

Шаг 2. Проверьте, следуют ли данные линейному тренду.

Далее давайте создадим диаграмму рассеяния, чтобы проверить, следуют ли данные линейному тренду.

Для этого выделите диапазон A2:B14 , затем щелкните вкладку «Вставка» на верхней ленте, затем щелкните значок «Scatter» в группе «Графика» :

Появится следующая диаграмма рассеяния:

На графике мы видим, что точки примерно следуют линейному тренду.

Другими словами, точки лежат примерно вдоль прямой диагональной линии.

Итак, мы можем перейти к использованию функции ТРЕНД для экстраполяции будущих значений данных.

Примечание . Если данные демонстрируют нелинейный характер, вместо этого можно попробовать подобрать полиномиальную кривую .

Шаг 3. Используйте функцию ТРЕНД для экстраполяции новых значений.

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

• х = 25
• х = 30
• х = 35

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

 =TREND( $B$2:$B$14 , $A$2:$A$14 , A17 )

Затем мы можем щелкнуть и перетащить эту формулу к каждому из новых значений:

Вот как интерпретировать результат:

• Для значения x = 25 ожидаемое значение y составляет 26,75229 .
• Для значения x = 30 ожидаемое значение y составляет 31,596 .
• Для значения x = 35 ожидаемое значение y равно 36,43971 .

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

 =LINEST( A2:A14 , B2:B14 )

На следующем снимке экрана показано, как использовать эту формулу на практике:

Функция ЛИНЕЙН вычисляет линию линейного тренда для существующих данных, которая имеет вид:

у = 0,968741x + 2,533759

Функция ТРЕНД использует это уравнение для экстраполяции значений y.

Например, для значения x = 25 уравнение рассчитывает следующее:

у = 0,968741*(25) + 2,533759 = 26,75229

Это соответствует значению, рассчитанному функцией ТРЕНД .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как создать прогноз продаж в Excel
Как создать линию наилучшего соответствия в Excel
Как добавить квадратичную линию тренда в Excel