ДЛЯ РЕГРЕССИОННО-КОРРЕЛЯЦИОННОГО АНАЛИЗА

ПОСЛЕДОВАТЕЛЬНОСТЬ ОБРАБОТКИ ДАННЫХ

 

Для статистической обработки данных в пакете Excel имеется встроенный модуль «Пакет анализа», который открывается следующим образом: в меню выбираем пункт «Сервис | Анализ данных».

В том случае, когда модуль «Анализ данных» отсутствует, нужно выбрать пункт «Надстройки» в меню «Сервис» и пометить в окне надстроек строку «Пакет анализа».

 

1. Предварительная настройка.

Вся информация разместится на трёх листах, поэтому на каждом из них нужно установить стандартный формат текста: Times New Roman, 14 пунктов.

2. Вводим исходные данные.

На листе 1 заполняем исходными данными таблицу, располагая столбцы независимых переменных (Х) подряд, без пропуска. Столбец зависимой переменной (У) можно расположить как в начале, так и в конце таблицы (только не между столбцами значений Х). Пример – таблица 1 на листе 1.

По данным этой таблицы будут рассчитаны показатели описательной статистики и показатели регрессионно-корреляционного анализа.

3. Рассчитываем показатели описательной статистики для столбца У.

Таблицу результатов можно разместить на листе 1, под исходными данными, поэтому, в ячейку столбца А1, отступив 2-3 строки от таблицы 1, вводим название «Таблица 2 – Описательная статистика».

Для расчёта показателей открываем модуль «Анализ данных» и в его окне выбираем строку «Описательная статистика».

Настройки в окне «Описа­тельная статистика».

В разделе «Входные данные» указать входной интервал – выделить все числа в столбце У исходной таблицы (только числа, текст не выделять!).

В разделе «Параметры вывода» указать выходной интервал, ячейку в столбце А под таблицей 2, и пометить строку «Итоговая статистика».

Отформатировать таблицу 2, увеличив ширину столбца А настолько, чтобы был виден весь текст в нём.

4. Рассчитываем показатели для регрессионно-корреляционного анализа.

Эти данные размещаем на листе 2.

Для расчёта показателей в меню снова открываем модуль «Анализ данных» и в его окне выбираем строку «Регрессия».

Настройки в окне «Регрессия».

В разделе «Входные данные» указываем входной интервал У, для чего выделяем все числа в столбце У. Далее указываем входной интервал Х, выделив одним блоком числа во всех столбцах Х.

В разделе «Параметры вывода» отмечаем строку «Выходной интервал» и в качестве адреса этого интервала указываем ячейку А1 листа 2. Вместо этого можно отметить строку «Новый рабочий лист». Тогда результаты будут выведены на дополнительном листе (например, листе 4), который добавится слева от листа 1, и стандартное форматирование текста на нём будет отсутствовать.

В разделе «Остатки» пометить строку «остатки».

После этого нажимаем кнопку ОК.

Примечание. Если появилось сообщение об ошибке, нужно проверить правильность выделения исходных данных и отсутствие в таблице 1 пустых столбцов между столбцами с исходными данными.

5. Форматирование листа 2.

­ Таблицу «Вывод остатка» переносим на лист 3.

­ Используя режим предварительного просмотра, изменить ориентацию листа с книжной на альбомную, поскольку последняя таблица не вмещается по ширине на стандартный лист.

­ Расширить столбец А таким образом, чтобы был виден весь текст в каждой из таблиц листа.

­ В третьей таблице настроить шапку на размещение текста по горизонтали и вертикали по центру и перенос слов по слогам и увеличить её высоту, чтобы было видно полностью название всех столбцов.

­ Числа в таблицах округлить с точностью до 4-5 знаков после запятой.

­ Используя режим предварительного просмотра сделать настройку на печать информации на одной странице.

6. Записываем уравнение регрессии.

Уравнение регрессии имеет следующий вид:

У = а₀ + а₁*Х₁ + а₂*Х₂ + а₃*Х₃ + …,

где а₀, а_1, а₂, а₃ … - числа столбца «Коэффициенты» из третьей таблицы листа 2. (Коэффициент а₀ берётся из строки «У-пересечение»).

Уравнение помещаем под таблицами на листе 2.

7. Оформляем таблицу «Вывод остатка» и строим по ней график.

­ Перед столбцом «Предсказанное У» вставляем пустой столбец. Называем его «Фактическое У» и копируем данные столбца У из таблицы 1 на листе 1.

­ Округляем числа во всех столбцах кроме первого, ориентируясь на точность представления фактических данных.

­ По данным столбцов «Фактическое У» и «Предсказанное У» строим график с названием «График фактических и расчётных значений …» и со всеми необходимыми параметрами.

Далее приведён пример оформления результатов статистической обработки.

Лист 1