Решение задачи с помощью MS Excel

1.2.1 Параметры линейной регрессии можно определить с помощью встроенной статистической функции ЛИНЕЙН MS Excel. Порядок вычисления следующий:

1) ввожу исходные данные (рисунок 1).

Рисунок 1 Ввод данных для корреляционно-регрессионного анализа

2) выделяю область пустых ячеек 5´2 (5 строк, 2 столбца) с целью вывода результатов регрессионной статистики или область 1´2 – для получения только оценок коэффициентов регрессии;

3) активизирую Мастер функций любым из способов:

а) в главном меню выбираю Вставка / Функция;

б) на панели инструментов Стандартная щелкаю по кнопке Вставка функции;

4) в окне «Категория» выбераю Статистические, в окне «Функция» – ЛИНЕЙН. Щелкаю по кнопке ОК (рисунок 2);

Рисунок 2 Диалоговое окно Мастер функций

5) заполняю аргументы функции (рисунок 3):

Щелкаю по кнопке ОК;

6) в левой верхней ячейке выделенной области появился первый элемент итоговой таблицы.

Чтобы раскрыть всю таблицу, нажимаю на клавишу < F2>, а затем – на комбинацию клавиш < CTRL> + <SHIFT> + <ENTER>.

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

Значение коэффициента b	Значение коэффициента a
Среднеквадратическое отклонение b	Среднеквадратическое отклонение a
Коэффициент детерминации R^2	Среднеквадратическое отклонение y
F – статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Результаты вычислений функции ЛИНЕЙН представлены на рисунке 4.

Рисунок 4 Результаты вычислений функции ЛИНЕЙН

1.2.2 С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Порядок действий следующий:

1) проверяю доступ к пакету анализа. В главном меню последовательно выбераю Сервис / Настройки. Устанавливаю флажок Пакет анализа;

2) в главном меню выбираю Сервис / Анализ данных / Регрессия. Щелкаю по кнопке ОК;

3) после вызова режима Регрессия на экране появляется диалоговое окно (рисунок 5), в котором задаются следующие параметры:

Или так:

1) Нажимаю левую часть мышки на значке «Офис» верхнем левом углу и в открывающемся окне нажимаю «Параметры Excel»

2) Далее «надстройки» - «Надстройки Excel» и на «Перейти»

3) В открывающемся окне ставлю галочку на «Пакет анализа»

4) В главном меню нажимаю на «Данные» и на «Анализ данных»

5) В открывающемся окне выбираю нужную функцию «Регрессия» ОК

Рисунок 5. Диалоговое окно режима Регрессия

Результаты регрессионного анализа для исходных данных представлены на рисунке 6.

Рисунок 6. Результат применения инструмента Регрессия.

Таким образом, уравнение регрессии имеет вид:

= - 0,08531 + 0,084061*X

Параметр b= 0,084061коэффициент эластичности, который показывает, что с ростом величины доходов на 1% ожидаемый расход увеличится в среднем на 0,08406%.

Дата добавления: 2015-05-09