Прогнозирование с использованием многофакторных моделей
Для зависимостей со многими неизвестными подбор формул можно выполнить несколькими способами:
- с помощью функций из группы Статистические - ЛИНЕЙН и ЛГРФПРИБЛ.
- функции ТЕНДЕНЦИЯ и РОСТ – для вычисления значений аппроксимирующей функции в диапазоне наблюдения;
- инструмент для подбора формул со многими неизвестными Регрессия, входящий в Пакет анализа (Данные – Анализ данных…);
- матричными вычислениями по методу наименьших квадратов.
Функции ЛИНЕЙН и ТЕНДЕНЦИЯ применяют для аппроксимации экспериментальных данных линейные зависимости вида y = b + a1x1 + a2x2 + … + anxn.
Функции
ЛГРФПРИБЛ и РОСТ применяют для
аппроксимации экспериментальных данных
нелинейные (показательные) зависимости
вида 
Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают массив с т.н. регрессионной статистикой, который содержит вычисленные значения параметров (b, a1, a2, …, an), коэффициент детерминации R2 и другие характеристики аппроксимирующей функции.
Рассмотрим пример оценивания значений функции y по трем переменным: х1, х2, х3, предполагая, что между каждой переменной х1, х2, х3 и зависимой переменной y существует линейная зависимость. Полученные в результате опыта (эксперимента) данные занесены в таблицу:
x1 |
x2 |
x3 |
y |
35 |
8 |
2 |
30 |
35 |
8 |
10 |
28 |
38 |
8,5 |
2 |
32 |
38 |
8,5 |
10 |
31 |
38 |
9 |
2 |
33 |
38 |
9 |
10 |
32 |
40 |
10 |
2 |
36 |
40 |
10 |
10 |
34 |
45 |
12 |
2 |
40 |
45 |
12 |
10 |
39 |
50 |
12 |
2 |
50 |
50 |
12 |
10 |
49 |
50 |
15 |
2 |
52 |
Необходимо подобрать формулу для вычисления эмпирических (теоретических) значений y и вычислить прогнозное значение y с данными: х1 = 42, х2 = 11, х3 = 5.
Порядок решения задачи:
Заведите приведенную таблицу в ячейки A1:D14. Результаты ввода:
Выделите диапазон ячеек B17:E21 (рис.2) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов.
Вызовите статистическую функцию ЛИНЕЙН и установите параметры, как на рис.1. Параметр Изв_знач_y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1 – для получения дополнительной статистики.
Рис. 1
После нажатия ОК встаньте на строку формул (или нажмите 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
Рис. 2
С использованием полученной формулы вычислите теоретические значения yтеор и прогнозное значение функции yпрогн при х1 = 42, х2 = 11, х3 = 5, записав самостоятельно в любую ячейку формулу для автоматического расчета. Результат расчета: yпрогн = 37,9.
Использование функции ТЕНДЕНЦИЯ рассмотрим на этом же примере для расчета значений y при других различных вариантах данных X.
Новые значения X |
||
x1 |
x2 |
x3 |
33 |
8 |
1 |
35 |
8 |
20 |
36 |
8,5 |
1 |
38 |
8,5 |
20 |
38 |
9 |
1 |
39 |
9 |
20 |
42 |
12 |
1 |
40 |
10 |
20 |
45 |
12 |
1 |
45 |
12 |
20 |
50 |
12 |
1 |
50 |
12 |
20 |
55 |
15 |
1 |
Новые значения Х, для которых надо рассчитать y, введите в ячейки F2:H14.
Выделите диапазон I2:I14 для записи в него рассчитываемых значений y.
Вызовите функцию ТЕНДЕНЦИЯ. Параметры функции заполните как на рис.3. Параметр Нов_знач_х содержит диапазон F2:H14, т.е. новые значения x.
После нажатия ОК встаньте на строку формул (или нажмите клавишу F2) и нажмите Ctrl+Shift+Enter. В результате диапазон I2:I14 будет заполнен рассчитанными значениями y (рис.4).
Рис. 3
Рис. 4