Этап 3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.
Алгоритм 3.1. Преобразование выходной таблицы в результативную
1. Строку 102, содержащую термин «Карман», выделить мышью (вместе с ее номером);
2. Правка => Удалить;
3. Строку 107, содержащую термин «Еще», выделить мышью и очистить, нажав клавишу [Delete];
4. Строки первого столбца привести к виду «нижняя граница интервала - верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала (нижняя граница первого интервала равна хmin первого признака из табл.3 – Описательные статистики – Термин "Минимум").
5. Добавить и заполнить итоговую строку 108 (ячейки А108:В108).
Для демонстрационного примера Excel-формат результативной таблицы выглядит следующим образом.
| A | B | C | |
| Таблица 7–ДП | |||
| Интервальный ряд распределения предприятий по стоимости основных производственных фондов | |||
| Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий в группе | Накопленная частость группы, % | |
| 94 - 134,8 | 10,005 | ||
| 134,8 - 175,6 | 30,00% | ||
| 175,6 - 216,4 | 66,67% | ||
| 216,4 - 257,2 | 86,67% | ||
| 257,2 - 298 | 100,00% | ||
| Итого |
Алгоритм 3.2. Преобразование столбиковой диаграммы в гистограмму
1. Осуществив «захват мышью», переместить график, расположив его вслед за табл.7.
2. Исключить зазоры, выполнив следующие действия:
1. Нажать правую кнопку мыши на одном из столбиков диаграммы;
2. Формат рядов данных=>Параметры;
3. Ширина зазора<= 0;
4. ОК;
3. Подвести курсор на угол поля графика и, используя прием "захват мышью", установить визуально соотношение ширины и высоты фигуры гистограммы в пропорции 1 : 0,6.
Внимание! Здесь возможна ошибочная установка указанной пропорции для размеров поля графика, а не для самой геометрической фигуры гистограммы. Необходимо проконтролировать правильность установки пропорции ширины и высоты фигуры гистограммы.
По окончании работы алгоритма 3.2 выполнить настройку диаграммы:
1. Изменить название осей(алгоритм 3.3);
2. Изменить текст легенды(алгоритм 3.4).
Алгоритм 3.3. Изменение названия осей
1. Выделить мышью построенную диаграмму;
2. Диаграмма => Параметры диаграммы;
3. В появившемся диалоговом окне Параметры диаграммывыбрать вкладку Заголовкии в поле Ось Хввести заголовок оси – "Среднегодовая стоимость основных производственных фондов", а в поле Ось Y – "Число предприятий в группе";
4. ОК.
Алгоритм 3.4. Изменение текста легенды
1. Выделить мышью построенную диаграмму;
2. Диаграмма => Исходные данные;
3. В появившемся диалоговом окне Исходные данные выбрать вкладку Ряд, в поле Рядвыбрать заголовок "Интегральный %" и в поле Имя ввести – "Накопленная частость ";
4. ОК.
Для демонстрационного примера гистограмма и кумулята выглядят следующим образом
Лабораторная работа № 2
Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel
Построение аналитической группировки для выявления корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков.
Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа.
Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного нелинейного уравнения регрессии.
Для выполнения ЛР-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 |
| Табл. 7 | Интервальный ряд распределения факторного признака Х | А102 : В106 |  | Табл. 2.2 и табл. 2.3 | Интервальный ряд распределения факторного признака Х | B41 : C45 и B52 : C56 |
►Внимание!Табл.7 копируется на Лист 2 Рабочего файла дважды!
По окончании копирования информации ЛР-1 необходимо ознакомиться с распределением памяти Листа 2, выделенной для результативных таблиц с тем, чтобы систематически проверять на этапах ЛР-2 правильность адресации получаемых результативных таблиц. При этом во избежание ошибок содержание каждой получаемой результативной таблицы следует сопоставлять с соответствующими таблицами демонстрационного примера.
Структура Листа 2 Рабочего файла stat_lab.xls
(распределение памяти для результативных таблиц)
| A | B | C | |
| Таблица 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) |
| A | B | C | D | |
| Таблица 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 | Остатки | |||||||
| … |