Прогнозирование с использованием многофакторных моделей

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

- с помощью функций из группы Статистические - ЛИНЕЙН и ЛГРФПРИБЛ.

- функции ТЕНДЕНЦИЯ и РОСТ – для вычисления значений аппроксимирующей функции в диапазоне наблюдения;

- инструмент для подбора формул со многими неизвестными Регрессия, входящий в Пакет анализа (Данные – Анализ данных…);

- матричными вычислениями по методу наименьших квадратов.

Функции ЛИНЕЙН и ТЕНДЕНЦИЯ применяют для аппроксимации экспериментальных данных линейные зависимости вида y = b + a1x1 + a2x2 + … + anxn.

Функции ЛГРФПРИБЛ и РОСТ применяют для аппроксимации экспериментальных данных нелинейные (показательные) зависимости вида Прогнозирование с использованием многофакторных моделей - student2.ru

Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают массив с т.н. регрессионной статистикой, который содержит вычисленные значения параметров (b, a1, a2, …, an), коэффициент детерминации R2 и другие характеристики аппроксимирующей функции.

Рассмотрим пример оценивания значений функции y по трем переменным: х1, х2, х3, предполагая, что между каждой переменной х1, х2, х3 и зависимой переменной y существует линейная зависимость. Полученные в результате опыта (эксперимента) данные занесены в таблицу:

x1 x2 x3 y
8,5
8,5

Необходимо подобрать формулу для вычисления эмпирических (теоретических) значений y и вычислить прогнозное значение y с данными: х1 = 42, х2 = 11, х3 = 5.

Порядок решения задачи:

1. Заведите приведенную таблицу в ячейки A1:D14. Результаты ввода:

Прогнозирование с использованием многофакторных моделей - student2.ru

2. Выделите диапазон ячеек B17:E21 (рис.2) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов.

3. Вызовите статистическую функцию ЛИНЕЙН и установите параметры, как на рис.1. Параметр Изв_знач_y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1 – для получения дополнительной статистики.

Прогнозирование с использованием многофакторных моделей - student2.ru

Рис. 1

4. После нажатия ОК встаньте на строку формул (или нажмите F2) и нажмите Ctrl+Shift+Enter. В результате должен получиться массив значений, показанный на рис.2.

На рисунке искомые коэффициенты b, a1, a2, a3 выделены (подробнее см. справку F1). Коэффициент детерминации R2=0.9725 вполне удовлетворителен (близок к единице).

Таким образом, аппроксимирующая формула y=b+a1x1+a2x2+a3x3 имеет вид:

Y = 1,36*х1 + 0,1*х2 – 0,21*х3 – 19,27

Прогнозирование с использованием многофакторных моделей - student2.ru

Рис. 2

5. С использованием полученной формулы вычислите теоретические значения yтеор и прогнозное значение функции yпрогн при х1 = 42, х2 = 11, х3 = 5, записав самостоятельно в любую ячейку формулу для автоматического расчета. Результат расчета: yпрогн= 37,9.

Использование функции ТЕНДЕНЦИЯ рассмотрим на этом же примере для расчета значений y при других различных вариантах данных X.

Новые значения X
x1 x2 x3
8,5
8,5

Новые значения Х, для которых надо рассчитать y, введите в ячейки F2:H14.

Выделите диапазон I2:I14 для записи в него рассчитываемых значений y.

Вызовите функцию ТЕНДЕНЦИЯ. Параметры функции заполните как на рис.3. Параметр Нов_знач_х содержит диапазон F2:H14, т.е. новые значения x.

После нажатия ОК встаньте на строку формул (или нажмите клавишу F2) и нажмите Ctrl+Shift+Enter. В результате диапазон I2:I14 будет заполнен рассчитанными значениями y (рис.4).

Прогнозирование с использованием многофакторных моделей - student2.ru

Рис. 3

Прогнозирование с использованием многофакторных моделей - student2.ru

Рис. 4

Наши рекомендации