Этап выполнения статистических расчетов
Лабораторная работа № 2
Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel
Задание 1. Построение аналитической группировки для выявления корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков.
Задание 2. Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа.
Задание 3. Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного нелинейного уравнения регрессии.
Для выполнения ЛР-2 выделяется Лист 2 Рабочего файла, сформированного в персональной папке студента при выполнении ЛР-1, и используется следующая информация из ЛР-1 (Лист 1 Рабочего файла):
· исходные данные – Таблица 1 (А4:С33), полученная после удаления аномальных значений признаков);
· интервальный ряд распределения единиц совокупности по факторному признаку Х – Среднегодовая стоимость основных производственных фондов, представленный табл. 7(А102:В106);
· диаграмма рассеяния признаков, расположенная начиная с ячейкиF4.
Компьютерное выполнение ЛР-2 включает два этапа:
1. Подготовительный этап.
2. Этап выполнения статистических расчетов.
Подготовительный этап
На Листе 2Рабочего файлаперсональной папкистудента при выполнении ЛР-1 были заготовлены макеты результативных таблиц, используемые в ЛР-2.
На данном этапе студент должен скопировать необходимую информацию ЛР-1 из Листа 1 в Лист 2Рабочего файлаперсональной папки в соответствии с нижеследующей таблицей:
Лист 1 Лист 2
| Номер таблицы | Содержимое таблицы | Адресация содержимого | Номер таблицы | Содержимое таблицы | Адресация содержимого | |
| Табл. 1 | Исходные данные | A4 : С33 | Табл. 2.1 | Исходные данные | A4 : С33 | |
| – | Диаграмма рассеяния (копируется дважды) | Начиная с ячейки F4 | Копировать в  | – | Диаграмма рассеяния | Начиная с ячейки Е4 и Е20 |
| Табл. 7 | Интервальный ряд распределения факторного признака Х | А102 : В106 |  | Табл. 2.2 и табл. 2.3 | Интервальный ряд распределения факторного признака Х | B41 : C45 и B52 : C56 |
►Внимание!Табл.7 и диаграмма рассеяния копируются на Лист 2 Рабочего файла дважды!
По окончании копирования информации ЛР-1 необходимо ознакомиться с распределением памяти Листа 2, выделенной для результативных таблиц с тем, чтобы систематически проверять на этапах ЛР-2 правильность адресации получаемых результативных таблиц. При этом во избежание ошибок содержание каждой получаемой результативной таблицы следует сопоставлять с соответствующими таблицами демонстрационного примера.
Структура Листа 2 Рабочего файла stat_lab.xls
(распределение памяти для результативных таблиц)
| A | B | C | D | E | |
| Таблица 2.1 | |||||
| Исходные данные | |||||
| Номер предприятия | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. | |||
| A | B | C | D | E | |
| Таблица 2.2 | |||||
| Зависимость выпуска продукции от среднегодовой стоимости основных фондов | |||||
| Номер группы | Группы предприятий попризнаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий | Выпуск продукции, млн руб. | ||
| Всего | В среднем на одно предприятие | ||||
| СУММ() | D41/C41 | ||||
| СУММ() | D42/C42 | ||||
| СУММ() | D43/C43 | ||||
| СУММ() | D44/C44 | ||||
| СУММ() | D45/C45 | ||||
| Итого | СУММ(C41:C45) | СУММ(D41:D45) | D46/C46 | ||
| Таблица 2.3 | |||||
| Показатели внутригрупповой дисперсии | |||||
| Номер группы | Группы предприятий попризнаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий | Внутригрупповая дисперсия | ||
| ДИСПР() | |||||
| ДИСПР() | |||||
| ДИСПР() | |||||
| ДИСПР() | |||||
| ДИСПР() | |||||
| Итого | СУММ(C52:C56) | ||||
| Таблица 2.4 | |||||
| Показатели дисперсий и эмпирического корреляционного отношения | |||||
Общая дисперсия  | Средняя из внутригрупповых дисперсий  | Межгрупповая дисперсия  | Эмпирическое корреляционное отношение η | ||
| ДИСПР (C4:C33) | СУММПРОИЗВ (D52:D56,C52:C56)/C46 | A63-B63 | КОРЕНЬ (C63/A63) | ||
| A | B | C | D | E | F | |
| Выходные таблицы | ||||||
| ВЫВОД ИТОГОВ | ||||||
| Таблица 2.5 | ||||||
| Регрессионная статистика | ||||||
| Множественный R | ||||||
| R-квадрат | ||||||
| Нормированный R-квадрат | ||||||
| Стандартная ошибка | ||||||
| Наблюдения | ||||||
| Таблица 2.6 | ||||||
| Дисперсионный анализ | ||||||
| df | SS | MS | F | Значимость F | ||
| Регрессия | ||||||
| Остаток | ||||||
| Итого |
| A | B | C | D | E | F | G | H | I | |
| Таблица 2.7 | |||||||||
| Коэффициенты | Стандартная ошибка | t-статис-тика | P- зна-чение | Нижние 95% | Верхние 95% | Нижние 68,3% | Верхние 68,3% | ||
| Y-пересечение | |||||||||
| Переменная X1 | |||||||||
| Таблица 2.8 | |||||||||
| ВЫВОД ОСТАТКА | |||||||||
| Наблюдение | Предсказанное Y | Остатки | |||||||
| … |
Этап выполнения статистических расчетов
Задание 1