Лабораторная работа № 2
Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel
Задание 1. Построение аналитической группировки для выявления
корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков
Задание 2. Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа
Задание 3. Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного уравнения регрессии
Порядок выполнения лабораторной работы
Для выполнения Лабораторной работы №2 выделяется Лист 2 рабочего файла, сформированного в персональной папке студента при выполнении Лабораторной работы №1, и используется следующая информация из Лабораторной работы №1:
исходные данные – Таблица 1, полученная после удаления аномальных значений (А4:С33);
интервальный ряд распределения единиц совокупности по факторному признаку Х – Среднегодовая стоимость основных производственных фондов из табл. 7 (А102:В106);
диаграмма рассеяния, расположенная начиная с ячейки F4.
I. Подготовительный этап
На данном этапе студент должен скопировать необходимую информацию из Лабораторной работы №1 на Лист 2 рабочего файла персональной папки ФИО.
На Листе 2 рабочего файла персональной папки студента заготовлены макеты таблиц, используемые при выполнении Лабораторной работы №2.
Для записи необходимой информации в рабочий и отчетный файлы персональной папки необходимо скопировать данные из Листа 1 в Лист 2 рабочего файла в соответствии с нижеследующей таблицей:
Лист 1 Лист 2
Расположение макетов результативных таблиц в рабочем файле на Листе 2 персональной папки студента




II. Этап выполнения статистических расчетов.
Задание 1
Построение аналитической группировки для выявления корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков
Выполнение Задания 1 заключается в решении трех задач:
Построение аналитической группировки предприятий по факторному признаку Среднегодовая стоимость основных производственных фондов.
Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения.
Оценка тесноты связи изучаемых признаков на основе линейного коэффициента корреляции (в предположении, что взаимосвязь признаков линейная).
Алгоритмы выполнения Задания 1
Задача 1. Построение аналитической группировки предприятий по признаку Среднегодовая стоимость основных производственных фондов.
Этап 1. Ранжирование единиц совокупности по возрастанию факторного признака Среднегодовая стоимость основных производственных фондов.
Алгоритм 1.1. Ранжирование исходных данных.
Выделить исходные данные (вместе с заголовком) табл. 2.1 (А3:С33);
Данные => Сортировка;
Сортировать по <= заголовок столбца (Среднегодовая стоимость основных производственных фондов), по которому выполняется сортировка;
по возрастанию/по убыванию – устанавливается в положение по возрастанию;
Затем и В последнюю очередь по – не активизируются;
Идентифицировать поля по подписям/обозначениям столбцов листа – устанавливается в положение подписям;
ОК.
В результате указанных действий в таблице 2.1 размещаются данные, ранжированные по возрастанию признака Среднегодовая стоимость основных производственных фондов.
Этап 2. Распределение предприятий по группам.
Алгоритм 1.2. Выделение групп предприятий с помощью заливки контрастным цветом.
Из всего диапазона отсортированных данных A4:C33 выделить мышью диапазон ячеек первой группы, для чего необходимо отсчитать в ранжированном ряду количество строк, соответствующее числу предприятий первой группы (графа 3 табл.2.2),
Нажать на панели инструментов кнопку ;
Выбрать цвет по собственному усмотрению;
Выполнить действия 1–3 для всех групп, выбирая контрастные цвета для цветовой заливки очередной группы.
Результат работы алгоритмов 1.1 и 1.2 для демонстрационного примера дан в табл.2.1–ДП.
Таблица 2.1–ДП
Исходные данные
Этап 3. Расчет суммарных и средних групповых значений результативного признака Y – Выпуск продукции.
Алгоритм 1.3. Расчет суммарных групповых значений результативного признака.
В ячейке (D41), выделенной для суммарного значения результативного признака Выпуск продукции первой группы, перед формулой поставить знак равенства «=»;
В качестве аргумента функции указать диапазон ячеек из табл. 2.1 с результативными значениями уi первой группы (визуально легко определяется по цвету заливки диапазона);
Enter;
Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
Алгоритм 1.4. Расчет средних групповых значений результативного признака.
В таблице 2.2 приведены формулы для расчета средних групповых значений результативного признака Выпуск продукции.
Для выполнения вычислений следует перед формулой поставить знак равенства «=».
Для расчета итоговых сумм в табл. 2.2 (в ячейках C46, D46 и E46) перед формулами необходимо поставить знак равенства «=».
Результат работы алгоритмов 1.3 и 1.4 для демонстрационного примера дан в табл. 2.2–ДП.
Таблица 2.2–ДП
Зависимость выпуска продукции от среднегодовой стоимости основных фондов
Задача 2. Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения.
Алгоритм 2.1. Расчет внутригрупповых дисперсий результативного признака.
В ячейке, выделенной для внутригрупповых дисперсий первой группы (D52), перед формулой поставить знак равенства «=»;
В качестве аргумента функции указать диапазон ячеек из табл. 2.1 со значениями yi первой группы – визуально легко определяется по цвету заливки диапазона;
Enter;
Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
Для расчета итоговой суммы в табл. 2.3 (в ячейке C57) перед формулой необходимо поставить знак равенства «=».
Результат работы алгоритма 2.1 для демонстрационного примера дан в табл.2.3–ДП.
Таблица 2.3–ДП
Показатели внутригрупповой вариации
Алгоритм 2.2. Расчет общей, средней из внутригрупповых и факторной дисперсий
В ячейках для общей дисперсии (А63), для средней из внутригрупповых дисперсий (В63) и для значения факторной дисперсии (С63) перед формулами необходимо поставить знак равенства «=».
Примечание. В случае, если вычисления в ячейке В63 не выполняются то знак «,» между диапазонами заменить на знак «;».
Алгоритм 2.3. Расчет эмпирического корреляционного отношения.
В ячейке, выделенной для эмпирического корреляционного отношения (D63), перед формулой поставить знак равенства «=».
В результате работы алгоритмов 2.2 – 2.3 Excel осуществляет вывод результатов расчета показателей (для демонстрационного примера табл.2.4–ДП).
Таблица 2.4–ДП
Показатели дисперсии и эмпирического корреляционного отношения
Задача 3. Оценка тесноты связи изучаемых признаков на основе линейного коэффициента корреляции.
Алгоритм 3.1. Расчет линейного коэффициента корреляции.
Сервис => Анализ данных => Корреляция => ОК;
Входной интервал <= диапазон ячеек табл. 2.1 со значениями факторного и результативного признаков (В4:С33);
Группирование – по столбцам;
Метки в первой строке – не активизировать;
Выходной интервал – адрес ячейки заголовка первого столбца выходной табл. 2.5 (А68);
Новый рабочий лист и Новая рабочая книга – не активизировать;
ОК.
В результате работы алгоритма 3.1 Excel выдает оценку тесноты связи факторного и результативного признаков (для демонстрационного примера табл. 2.5–ДП).
Таблица 2.5–ДП
Линейный коэффициент корреляции признаков
Задание 2
Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа
Алгоритмы выполнения Задания 2
Алгоритм 1. Расчет параметров уравнения линейной регрессии и проверка его адекватности фактическим данным.
Сервис => Анализ данных => Регрессия => ОК;
Входной интервал Y <= диапазон ячеек таблицы со значениями признака Y – Выпуск продукции (С4:С33);
Входной интервал X – диапазон ячеек таблицы со значениями признака X – Стоимость основных фондов (В4:В33);
Метки в первой строке/Метки в первом столбце – не активизировать;
Уровень надежности <= 68,3;
Константа–ноль – не активизировать;
Выходной интервал <= адрес ячейки заголовка первого столбца первой выходной результативной таблицы (А75);
Новый рабочий лист и Новая рабочая книга – не активизировать;
Остатки – активизировать;
Стандартизованные остатки – не активизировать;
График остатков – не активизировать;
График подбора – активизировать;
График нормальной вероятности – не активизировать;
ОК.
Полученный график необходимо расположить после выходных таблиц.
В результате указанных действий осуществляется вывод в заданный диапазон рабочего файла четырех выходных таблиц и одного графика, начиная с ячейки, указанной в поле Выходной интервал (для демонстрационного примера они имеют следующий вид).

EMBED Excel.Chart.8 \s
Задание 3
Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного уравнения регрессии
Алгоритмы выполнения Задания 3
Алгоритм 1. Построение уравнений регрессионных моделей для различных видов зависимости признаков с использованием средств инструмента Мастер диаграмм.
Выделить мышью диаграмму рассеяния, расположенную начиная с ячейки Е4, и увеличить масштаб диаграммы на весь экран;
Диаграмма => Добавить линию тренда;
Выбрать вкладку Тип, задать вид регрессионной модели – полином 2-го порядка;
Выбрать вкладку Параметры и выполнить действия:
Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение автоматическое;
Поле Прогноз вперед на – не активизировать;
Поле Прогноз назад на – не активизировать;
Флажок Пересечение кривой с осью Y в точке – не активировать;
Флажок Показывать уравнение на диаграмме – активизировать;
Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – активизировать;
ОК;
Установить курсор на линию регрессии и щелкнуть правой клавишей мыши;
В появившемся диалоговом окне Формат линии тренда выбрать тип, цвет и толщину линии;
ОК;
Вынести уравнение и индекс детерминации R2 за корреляционное поле. При необходимости уменьшить размер шрифта.
Действия 2 – 4 (в п.4 –шаги 1–11) выполнить поочередно для следующих видов регрессионных моделей:
– полином 3-го порядка,– степенная.
Переместить Диаграмму 2.1 в конец рабочего файла.
В результате указанных действий для выбранных видов моделей регрессии осуществляется вывод на диаграмму рассеяния 4-х уравнений регрессии, их графиков и значений соответствующих индексов детерминации R2 (для демонстрационного примера Диаграмма 2.1–ДП приведена на Рис. 2.1).

EMBED Excel.Chart.8 \s Рис 2.1. Уравнения регрессии и их графики
Алгоритм 2. Построение наиболее адекватного уравнения регрессии.
Путем визуального анализа значений R2 выбрать по максимальной величине R2 наиболее адекватное уравнение регрессии;
Выделить диаграмму рассеяния, расположенную с ячейки Е20;
Диаграмма => Добавить линию тренда;
Выбрать вкладку Тип и задать вид наиболее адекватной нелинейной регрессионной модели;
Выбрать вкладку Параметры:
Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение автоматическое;
Поле Прогноз вперед на – не активизировать;
Поле Прогноз назад на – не активизировать;
Флажок Пересечение кривой с осью Y в точке – не активировать;
Флажок Показывать уравнение на диаграмме – активизировать;
Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – активизировать;
ОК.
Переместить Диаграмму 2.2 в конец рабочего файла.
В результате указанных действий осуществляется вывод на диаграмму рассеяния уравнения регрессии для выбранной наиболее адекватной модели регрессии, ее графика и значения индекса детерминации R2 (для демонстрационного примера Диаграмма 2.2–ДП приведена на Рис. 2.2).
EMBED Excel.Chart.8 \s Рис.2.2. Наиболее адекватное уравнение регрессии и его график