05. Решение типовых задач с помощью ППП «Excel»

1. Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии у=а+bх. Порядок вычисления следующий:

1) введите исходные данные или откройте существующий файл, содержащий анализируемые данные;

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

3) активизируйте Мастер функций;

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

Рис. 3. Диалоговое окно «Мастер функций».

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

Известные_значения_y – диапазон, содержащий данные результативного признака;

Известные_значения_x – диапазон, содержащий данные факторов независимого признака;

Константа – логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении; если Константа = 1, то свободный член присутствует в модели и рассчитывается по методу наименьших квадратов; если Константа = 0, то свободный член отсутствует в уравнении регрессии.

Статистика – логическое значение, которое указывает о выводе дополнительной информации по регрессионному анализу; если Статистика=1, то дополнительная информация выводится на лист; если Статистика=0, то выводятся только оценки параметров уравнения.

Щелкните по кнопке ОК;

Рис. 4. Диалоговое окно ввода аргументов функции ЛИНЕЙН.

6) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите клавишу <F2>, а затем – комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.

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

Значение коэффициента B	Значение коэффициента A
Среднеквадратическое отклонение b	Среднеквадратическое отклонение A
Коэффициент детерминации R2	Среднеквадратическое отклонение Y
F-статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Для данных из задачи 1.1 результат вычисления функции ЛИНЕЙН представлен на рис. 5.

Рис. 5. Результат вычисления функции ЛИНЕЙН.

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

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

Рис. 6. Подключение надстройки Пакет анализА.

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

3) заполните диалоговое окно ввода данных и параметров вывода (рис. 7):

Входной интервал Y – диапазон, содержащий данные результативного признака;

Входной интервал X – диапазон, содержащий данные независимого признака или переменных факторов;

Метки – флажок, который указывает на содержание в первой строке названий столбцов или нет;

Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – любое свободное место на листе, на которое будут выведены результаты регрессии;

Новый рабочий лист – результаты расчета регрессии будут выведены на новый рабочий лист;

Если необходимо получить информацию по остаткам и Графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.

Рис. 7. Диалоговое окно ввода параметров инструмента Регрессия.

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

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

< Предыдущая		Следующая >