Уравнение регрессии
Постановка задачи:
Имеется фирма, торгующая компьютерами. Даны две наблюдаемые величины x и y за N (N=7) недель работы фирмы: х-отчетная неделя, y-объем реализации за эту неделю.
Необходимо построить линейную модель y=mx+b, наилучшим образом описывающую наблюдаемые значения, т.е. необходимо минимизировать функцию
Последовательность решения:
Способ
1. В ячейки А2:А8 введем значения х={1,2,3,4,5,6,7}, а в ячейки В2:В8 значения y={7,9,12,13,14,17,19} (рис. 5.12).
2. Отведем под переменные m и b ячейки D2 и E2, а диапазон С2:С8 под теоретические значения y.
3. В ячейку F2 введем минимизируемую функцию:
=СУММКВРАЗН(B2:B8; E2+D2*A2:A8).
Рис. 5.12
4. В меню Сервис выбрать Поиск решения:
- целевая ячейка F2;
- минимальное значение;
- изменяемые ячейки D2:E2.
5. Определим теоретические значения наблюдений величины y:
5.1. Выделим диапазон С2:С8.
5.2. Введем формулу =ОКРУГЛ($D$2*A2+$E$2; 0).
5.3. Нажать <Ctrl>-<Enter>.
Способ
1. В ячейки А2:А8 введем значения х={1,2,3,4,5,6,7}, в ячейки В2:В8 значения y={7,9,12,13,14,17,19}, а ячейки С2:С8 отведем под результат.
|
|
2. Выделить ячейки С2:С8.
3. Ввести формулу =ПРЕДСКАЗ(А2; $B$2:$B$8; $A$2:$A$8).
4. Нажать <Ctrl>-<Enter>.
Графическое представление результатов
1. Построить график по диапазону В2:B8 (рис. 5.13).
2. Выделить точки графика щелчком, а затем щелкнуть их правой кнопкой мыши.
3. Выбрать команду Добавить линии тренда.
- тип: линейная;
- окно Параметры:
Ö Показать уравнение на диаграмме;
Ö Поместить на диаграмме величину достоверности аппроксимации.
Рис. 5.13
Замечание: если коэффициент корреляции R2Î[0,9; 1], то данную зависимость можно использовать для предсказания результата.
Варианты для самостоятельной работы
Вариант 1
Неделя | ||||||||
Кол-во копмьют. |
Вариант 2
Неделя | ||||||||
Кол-во копмьют. |
Вариант 3
Неделя | ||||||||||
Кол-во копмьют. |
Вариант 4
Неделя | ||||||||||
Кол-во копмьют. |
Вариант 5
Неделя | ||||||||||
Кол-во копмьют. |