Лабораторная работа Автоматизированный априорный анализ статистической совокупности в среде MS Excel 4
Работа добавлена на сайт bukvasha.net: 2015-10-29Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
ВСЕРОССИЙСКИЙ ЗАОЧНЫЙ ФИНАНСОВО-ЭКОНОМИЧЕСКИЙ ИНСТИТУТ
КАФЕДРА СТАТИСТИКИ
Инструкция
по выполнению лабораторных работ
Москва,
Лабораторная работа №1
Автоматизированный априорный анализ статистической совокупности в среде
MS
Excel
Задание 1. Выявление и удаление из выборки аномальных единиц наблюдения.
Задание 2. Оценка описательных статистических параметров совокупности.
Задание 3. Построение и графическое изображение интервального вариационного ряда распределения единиц совокупности по признаку Среднегодовая стоимость основных производственных фондов.
1. Подготовительный этап
На данном этапе студент должен проделать следующие обязательные действия, связанные с организацией индивидуальной рабочей среды:
· установить форматы чисел на компьютере
· подготовить персональную папку с Рабочим и Отчетным файлами;
· сформировать индивидуальный вариант исходных данных и записать его в Отчетный файл;
1. 1. Установка форматов чисел на компьютере
Перед началом работы следует убедиться, что компьютер настроен на формат дробных чисел и разделителей, применяемый в алгоритмах лабораторной работы, а именно:
· дробная часть числа должна отделяться от целой части знаком «запятая» (,);
· аргументы функций (разделители списков) должны отделяться знаком «точка с запятой» (;).
Иная настройка форматов чисел на компьютере (например, дробная часть отделяется от целой знаком «точка» (.) или же аргументы функции (разделители списков) перечисляются через запятую) будет приводить к ошибкам при вводе в электронные таблицы Excel формул, указанных в алгоритмах Задания 2.
Установить в компьютере совместимый с текстами алгоритмов формат чисел можно следующим образом:
1. Пуск=>Настройка=>Панель управления=>Язык и региональные стандарты;
2. Региональные параметры=>Настройка=>Числа;
3. В поле Разделитель целой и дробной части ввести символ «,»;
4. В поле Разделитель элементов списка ввести символ «;».
1.2. Подготовка персональной папки студента
с Рабочим и Отчетным файлами
Для выполнения расчетов обобщающих показателей и подготовки отчета по лабораторной работе студент формирует персональную папку с именем ФИО, содержащую два файла: Рабочий с именем Лаб.хls и Отчетный с именем Отчет.doc.
Для этого необходимо выполнить следующие действия:
а) создание персональной папки и Рабочего файла:
1. Загрузить файл с исходными данными и макетами таблиц по следующему алгоритму:
1. На рабочем столе активизировать Мой компьютер;
2. В диалоговом окне выбрать файл
Datadisk на “primary” (E:)\Преподаватели\Кафедра статистики\ Лабораторные работы\stat_lab.xls
2. Сохранить файл с исходными данными в качестве Рабочего файла по алгоритму:
1. Файл=>Сохранить как…;
2. В диалоговом окне Сохранение документа выбрать путь: Datadisk на “primary” (Е:)\Статистика\ Работы студентов\<Специальности>\ <Номер группы>;
3. Выбрать пиктограмму «Создать папку»
4. Сохранить файл в созданной папке под именем Лаб. хls.
б) создание Отчетного файла:
1. Загрузить файл, содержащий стандартизированный формат отчета из директории
Datadisk на “primary” (E:)\Преподаватели\Кафедра статистики\ Лабораторные работы \ Формат отчета.doc
2. Сохранить файл по алгоритму:
1. Файл=>Сохранить как…;
2. В диалоговом окне Сохранение документа выбрать путь:
Datadisk на “primary” (Е:)\Статистика\ Работы студентов\ <Специальности>\ <Номер группы>\<Ф.И.О.>;
3. Сохранить файл в указанной папке под именем Отчет.doc
1.3. Формирование индивидуальных исходных данных
Номер варианта исходных данных соответствует номеру компьютера, на котором работает студент.
Для того, чтобы сформировать индивидуальные исходные данные, необходимо ввести номер варианта в ячейку E2 созданного Рабочего файла Лаб.хls, в результате чего Excel сформирует исходные данные варианта.
Структура Листа 1
Рабочего файла
stat
_
lab
.
xls
(распределение памяти для результативных таблиц)
| | A | B | C |
| 1 | Таблица 2.1 | ||
| 2 | Исходные данные | ||
| 3 | Номер предприятия | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. |
| 4 | | | |
| 5 | | | |
| 6 | | | |
…
| 3 3 | | | |
| 3 4 | | | |
| 3 5 | | | |
| | A | B | C | D | |
| 37 | | | Таблица 2 | | |
| 38 | Аномальные единицы наблюдения | | |||
| 39 | Номер предприятия | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. | | |
| 40 | | | | | |
| 41 | | | | | |
| 42 | | | | | |
| 43 | | | | Таблица 3 | |
| 44 | | Описательные статистики | | ||
| 45 | По признаку Среднегодовая стоимость основных производственных фондов, млн руб. | По признаку Выпуск продукции, млн руб. | |||
| 46 | Столбец1 | Столбец2 | |||
| 47 | | | | | |
| 48 | Среднее | | Среднее | | |
| 49 | Стандартная ошибка | | Стандартная ошибка | | |
| 50 | Медиана | | Медиана | | |
| 51 | Мода | | Мода | | |
| 52 | Стандартное отклонение | | Стандартное отклонение | | |
| 53 | Дисперсия выборки | | Дисперсия выборки | | |
| 54 | Эксцесс | | Эксцесс | | |
| 55 | Асимметричность | | Асимметричность | | |
| 56 | Интервал | | Интервал | | |
| 57 | Минимум | | Минимум | | |
| 58 | Максимум | | Максимум | | |
| 59 | Сумма | | Сумма | | |
| 60 | Счет | | Счет | | |
| 61 | Уровень надежности (95,4%) | | Уровень Надежности (95,4%) | | |
| 62 | | | | | |
| 63 | | | | | |
| 64 | | | | Таблица 4 | |
| 65 | | Предельные ошибки выборки | | ||
| 66 | По признаку Среднегодовая стоимость основных производственных фондов, млн руб. | По признаку Выпуск продукции, млн руб. | |||
| 67 | Столбец1 | Столбец2 | |||
| 68 | | | | | |
| 69 | Уровень Надежности (68,3%) | | Уровень Надежности (68,3%) | | |
| 70 | | | | | |
| 71 | | | | | |
| A | B | C | D | |
| 80 | | | | Таблица 5 |
| 81 | Выборочные показатели вариации | |||
| 82 | По признаку Среднегодовая стоимость основных производственных фондов, млн руб. | По признаку Выпуск продукции, млн руб. | ||
| 83 | Стандартное отклонение | СТАНДОТКЛОНП(B4:B33) | Стандартное отклонение | СТАНДОТКЛОНП(C4:C33) |
| 84 | Дисперсия | ДИСПР(B4:B33) | Дисперсия | ДИСПР(C4:C33) |
| 85 | Коэффициент вариации, % | B83/B48*100 | Коэффициент вариации, % | D83/D48*100 |
| 86 | | | | |
| 87 | | | | |
| 88 | | | | |
| 89 | | Таблица 6 | | |
| 90 | Карман | Частота | | |
| 91 | | | | |
| 92 | | | | |
| 93 | | | | |
| 94 | | | | |
| 95 | | | | |
| 96 | | | | |
| 97 | | | | |
| 98 | | | | |
| 99 | | | Таблица 7 | |
| 100 | Интервальный ряд распределения предприятий по стоимости основных производственных фондов | | ||
| 101 | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий в группе | Накопленная частость группы, % | |
| 102 | Карман | Частота | Интегральный % | |
| 103 | | | | |
| 104 | | | | |
| 104 | | | | |
| 106 | | | | |
| 107 | | | | |
2. Этап выполнения статистических расчетов
Задание 1
Выявление и удаление из выборки аномальных единиц наблюдения
Первичные данные выборочной совокупности могут содержать аномальные значения изучаемых признаков (см. в методических указаниях [1] п. 2 раздела II – «Теоретические основы лабораторной работы»). Задание 1 заключается в их выявлении и исключении из дальнейшего рассмотрения с целью обеспечения устойчивости данных статистического исследования.
Выполнение Задания 1 заключается в решении двух задач:
1. Построение диаграммы рассеяния изучаемых признаков.
2. .Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из первичных данных.
Алгоритмы выполнения Задания 1
Задача 1.
Построение диаграммы рассеяния изучаемых признаков.
Алгоритм 1.1.
Построение диаграммы рассеяния изучаемых признаков
1. Выделить мышью исходные данные (B4:C35);
2. Вставка=>Диаграмма=>Точечная=>Готово.
В результате выполнения указанных действий появляется диаграмма рассеяния исследуемых признаков.
Рис. 1. Аномальные значения признаков
на диаграмме рассеяния.
Задача 2.
Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из первичных данных.
Алгоритм 2.
Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из первичных данных
1. Найти на графике точку, соответствующую аномальному наблюдению. Если таких точек нет, то перейти к действию 7, если есть – к действиям 2–6.
2. Подвести курсор к точке на диаграмме рассеяния, соответствующей аномальному наблюдению. После непродолжительного времени возле точки автоматически появится надпись, содержащая значения признаков (xi
,
yi) этого наблюдения.
Для демонстрационного примера такая надпись выглядит следующим образом:
3. В исходных данных визуально найти в табл.1 строку, соответствующую выявленной аномальной единице наблюдения (предприятию) и скопировать её в табл.2.
4. Выделить мышью всю адресную строку (вместе с ее номером) с данными, подлежащими удалению.
Для демонстрационного примера это адресная строка с номером 34, содержащая значения 31, 330 и 53:
| | А | В | С | D | E |
| 32 | 29 | 167 | 114 | | |
| | 30 | 205 | 133 | | |
| 34 | 31 | 330 | 53 | | |
| 35 | | | | | |
3. Правка=>Удалить.
4. Выполнять действия 1–5 до полного удаления всех аномальных значений признаков.
5. Выделить диаграмму рассеяния и переместить ее, используя прием "захват мышью", в область ячеек, начиная с ячейки F4.
Для демонстрационного примера табл.2, содержащая две единицы наблюдения с аномальными значениями признаков, имеет следующий вид.
| | | Таблица 2-ДП | ||
| Аномальные единицы наблюдения | ||||
| Номер предприятия | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. | ||
| 12 | 50,00 | 150,00 | ||
| 31 | 330,00 | 53,00 | ||
Задание 2
Оценка описательных статистических параметров
совокупности
Обобщающие статистические показатели совокупности исчисляются на основе анализа вариационных рядов распределения (см. в методических указаниях [1] п.3 раздела II – «Теоретические основы лабораторной работы»). Однако пакет Excel позволяет рассчитать многие из этих показателей непосредственно по первичным данным наблюдения, используя инструмент Описательная статистика надстройки Пакет анализа, а также статистические функции инструмента Мастер функций.
Выполнение Задания 2 заключается в автоматизированном решении двух статистических задач:
1. Расчет описательных показателей выборочной и генеральной совокупностей по несгруппированным выборочным данным с использованием инструментов Описательная статистика и Мастер функций.
2. Оценка средней и предельной ошибок выборки для средней величины признака, а также границ, в которых эта средняя будет находиться в генеральной совокупности при заданных уровнях надежности.
Алгоритмы выполнения Задания 2
Выполнение задания включает три этапа:
1. Расчет описательных параметров выборочной и генеральной совокупностей с использованием инструмента Описательная статистика.
2. Оценка предельных ошибок выборки для различных уровней надежности в режиме Описательная статистика.
3. Расчет описательных параметров выборочной совокупности с использованием инструмента Мастер функций.
Этап 1. Расчет описательных параметров выборочной и генеральной совокупностей с использованием инструмента Описательная статистика
Алгоритм 1.1. Расчет описательных статистик
1. Сервис=>Анализ данных=>Описательная статистика=>
OK
;
2. Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаковСтоимость основных фондовиВыпуск продукции (B
4:С33);
3. Группирование =>по столбцам;
4. Итоговая статистика - Активизировать;
5. Уровень надежности - Активизировать;
6. Уровень надежности <= 95,4 (или 95.4);
7. Выходной интервал <= адрес ячейки заголовка первого столбца табл.3 (А46);
8. OK;
9. При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
Результат работы алгоритма 2.1 для демонстрационного примера представлен в табл.3–ДП.
| | A | B | C | D |
| 43 | | | | Таблица 3-ДП |
| 44 | | Описательные статистики | | |
| 45 | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. | ||
| 46 | Столбец1 | Столбец2 | ||
| 47 | | | | |
| 48 | Среднее | 203,2333333 | Среднее | 144,6666667 |
| 49 | Стандартная ошибка | 8,804737927 | Стандартная ошибка | 7,071772174 |
| 50 | Медиана | 206,5 | Медиана | 148,5 |
| 51 | Мода | 167 | Мода | 114 |
| 52 | Стандартное отклонение | 48,22553575 | Стандартное отклонение | 38,73369141 |
| 53 | Дисперсия выборки | 2325,702299 | Дисперсия выборки | 1500,298851 |
| 54 | Эксцесс | 0,18281271 | Эксцесс | -0,602481285 |
| 55 | Асимметричность | -0,185105228 | Асимметричность | 0,218561586 |
| 56 | Интервал | 204 | Интервал | 144 |
| 57 | Минимум | 94 | Минимум | 80 |
| 58 | Максимум | 298 | Максимум | 224 |
| 59 | Сумма | 6097 | Сумма | 4340 |
| 60 | Счет | 30 | Счет | 30 |
| 61 | Уровень надежности (95,4%) | 18,356223 | Уровень надежности (95,4%) | 14,74331526 |
Этап 2. Оценка предельных ошибок выборки для различных уровней надежности в режиме
Описательная статистика
.
Алгоритм 2.1. Расчет предельной ошибки выборки при
P
=0,683
1.
Сервис=>Анализ данных=>Описательная статистика=>
OK
;
2. Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаковСтоимость основных фондовиВыпуск продукции (В4:С33);
3. Итоговая статистика – НЕ активизировать (снять флажок);
4. Уровень надежности – Активизировать;
5. Уровень надежности<= 68,3 (или 68.3);
6. Выходной интервал <= адрес ячейки, выделенной для предельной ошибки выборки при P=0,683 (А67);
7. OK;
8. При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
Результат работы алгоритма 2.1 для демонстрационного примера представлен в табл.4–ДП.
| | A | B | C | D |
| 64 | | | | Таблица 4-ДП |
| 65 | Предельные ошибки выборки | | ||
| 66 | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. | ||
| 67 | Столбец1 | Столбец2 | ||
| 68 | | | | |
| 69 | Уровень надежности (68,3%) | 8,965032289 | Уровень надежности (68,3%) | 7,200517087 |
Этап 3. Расчет описательных параметров выборочной совокупности с использованием инструмента
Мастер функций
Алгоритм 3.1. Расчет значений выборочных параметров
Вычисление показателей для обоих признаков осуществляется с использованием соответствующих статистических функций СТАНДОТКЛОНП, ДИСПР, СРОТКЛ инструмента Мастер функций. В макете табл.5. приведены их имена вместе с адресами аргументов.
1. В ячейках (В83 – В84), выделенных для значений выборочных показателей признака Среднегодовая стоимость основных производственных фондов:
1.1. Перед именами функций поставить знак равенства «=»;
1.2. Enter;
2. В ячейках (D
83 –
D
84), выделенных для значений выборочных показателей признака Выпуск продукции:
2.1. Перед именами функций поставить знак равенства «=»;
2.2. Enter;
Алгоритм 3.2. Расчет коэффициентов вариации
В макете табл.5. приведены расчетные формулы коэффициента вариации
1. В ячейке В85, выделенной для значений коэффициента вариации по признаку Среднегодовая стоимость основных производственных фондов, перед формулой поставить знак равенства «=»;
2. Enter;
3. В ячейке D
85, выделенной для значений коэффициента вариации по признаку Выпуск продукции, перед формулой поставить знак равенства «=»;
4. Enter.
В результате работы алгоритмов 3.1-3.2 осуществляется вывод выборочных параметров
| | A | B | C | D | |
| 80 | | | | Таблица 5-ДП | |
| 81 | Выборочные показатели вариации | | |||
| 82 | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. | |||
| 83 | Стандартное отклонение | 47,41496482 | Стандартное отклонение | 38,08265864 | |
| 84 | Дисперсия | 2248,178889 | Дисперсия | 1450,288889 | |
| 85 | Коэффициент вариации, % | 23,33030908 | Коэффициент вариации, % | 26,32441841 | |
Задание
Построение и графическое изображение интервального вариационного ряда распределения единиц совокупности по признаку
Среднегодовая стоимость основных производственных фондов
Для того чтобы выявить структуру совокупности и тип закономерности распределения ее единиц по варьирующему признаку, строят и анализируют интервальный вариационный ряд распределения и его гистограмму (см. в методических указаниях [1] п. 3 раздела II – Теоретические основы лабораторной работы).
Выполнение Задания 3 заключается в решении двух статистических задач:
1. Построение интервального ряда распределения единиц выборочной совокупности по признаку Среднегодовая стоимость основных производственных фондов.
2. Построение гистограммы и кумуляты сформированного интервального ряда.
Алгоритмы выполнения Задания 3
Выполнение задания осуществляется в три этапа:
1. Построение промежуточной таблицы.
2. Генерация выходной таблицы и графиков.
3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.
Этап 1. Построение промежуточной таблицы.
Алгоритм 1.1. Расчет нижних границ интервалов
1. Сервис=>Анализ данных=>Гистограмма=>ОК;
2. Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);
Внимание!
Здесь возможен ошибочный захват мышью столбца второго признака. Необходимо проконтролировать правильность задания входных данных!
3. Интервал карманов оставить незаполненным;
4. Выходной интервал <= адрес заголовка первого столбца первичной промежуточной табл.6 (А90);
5.
OK.
Алгоритм 1.2. Переход от нижних границ к верхним
1. Выделить курсором верхнюю левую ячейку табл.6 (A
91) и нажать клавишу [Delete];
2. Ввести в последнюю ячейку табл.6 (A
96) вместо "Еще" значение хmax первого признака из табл.3 – Описательные статистики (Термин "Максимум").
Для демонстрационного примера построение промежуточной таблицы (алгоритмы 1.1 – 1.2) приведено на рис.2:
| | Таблица 6 | | | Таблица 6 |
| 90 | Карман | | 90 | Карман |
| 91 | 94,0 | | 91 | |
| 92 | 134,8 | Преобразуется в | 92 | 134,8 |
| 93 | 175,6 | | 93 | 175,6 |
| 94 | 216,4 | | 94 | 216,4 |
| 95 | 257,2 | | 95 | 257,2 |
| 96 | Ещё | | 96 | 298 |
| | | | | |
| а) первичная | | б) итоговая | ||
Рис.2. Схема перехода от нижних границ интервалов к верхним
Этап 2. Генерация выходной таблицы и графиков
Алгоритм 2.1. Построение выходной таблицы, столбиковой диаграммы и кумуляты
1.
Сервис=>Анализ данных=>Гистограмма=>ОК;
2.
Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);
Внимание! Здесь возможен ошибочный захват мышью столбца второго признака. Необходимо проконтролировать правильность задания входных данных!
3. Интервал карманов <= диапазон карманов итоговой промежуточной табл.6 с верхними границами (А92:А96);
4. Выходной интервал <= адрес заголовка («Карман») первого столбца выходной табл.7 (А102);
5.
Интегральный процент – Активизировать;
6.
Вывод графика – Активизировать;
7.
ОК;
8. При появлении сообщения о наложении данных – ОК.
Для демонстрационного примера выходная таблица имеет следующий вид:
| | A | B | C |
| 99 | | | Таблица 7-ДП |
| 100 | Интервальный ряд распределения предприятий по признаку Среднегодовая стоимость основных производственных фондов | ||
| 101 | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий в группе | Накопленная частость группы, % |
| 102 | Карман | Частота | Интегральный % |
| 103 | 134,8 | 3 | 10,00% |
| 104 | 175,6 | 6 | 30,00% |
| 105 | 216,4 | 11 | 66,67% |
| 106 | 257,2 | 6 | 86,67% |
| 107 | 298 | 4 | 100,00% |
| 108 | Ещё | 0 | 100% |
Столбиковая диаграмма и кумулята приведены ниже:
Этап 3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.
Алгоритм 3.1. Преобразование выходной таблицы в результативную
1. Строку 102, содержащую термин «Карман», выделить мышью (вместе с ее номером);
2. Правка
=>
Удалить;
3. Строку 107, содержащую термин «Еще», выделить мышью и очистить, нажав клавишу [Delete];
4. Строки первого столбца привести к виду «нижняя граница интервала - верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала (нижняя граница первого интервала равна хmin первого признака из табл.3 – Описательные статистики – Термин "Минимум").
5. Добавить и заполнить итоговую строку 108 (ячейки А108:В108).
Для демонстрационного примера Excel-формат результативной таблицы выглядит следующим образом.
| | A | B | C |
| 99 | | | Таблица 7–ДП |
| 100 | Интервальный ряд распределения предприятий по стоимости основных производственных фондов | ||
| 101 | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий в группе | Накопленная частость группы, % |
| 102 | 94 - 134,8 | 3 | 10,005 |
| 103 | 134,8 - 175,6 | 6 | 30,00% |
| 104 | 175,6 - 216,4 | 11 | 66,67% |
| 104 | 216,4 - 257,2 | 6 | 86,67% |
| 106 | 257,2 - 298 | 4 | 100,00% |
| 107 | | | |
| 108 | Итого | 30 | |
Алгоритм 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. Этап выполнения статистических расчетов.
1. Подготовительный этап
На Листе 2 Рабочего файла персональной папки студента при выполнении ЛР-1 были заготовлены макеты результативных таблиц, используемые в ЛР-2.
На данном этапе студент должен скопировать необходимую информацию ЛР-1 из Листа 1 в Лист 2 Рабочего файла персональной папки в соответствии с нижеследующей таблицей:
Лист 1 Лист 2
| Номер таблицы | Содержимое таблицы | Адресация содержимого | | Номер таблицы | Содержимое таблицы | Адресация содержимого |
| Табл. 1 | Исходные данные | A 4 : С33 | | Табл. 2.1 | Исходные данные | A 4 : С33 |
| – | Диаграмма рассеяния | Начиная с ячейки F 4 | Копировать в | – | Диаграмма рассеяния | Начиная с ячейки Е4 |
| Табл. 7 | Интервальный ряд распределения факторного признака Х | А102 : В106 | | Табл. 2.2 и табл. 2.3 | Интервальный ряд распределения факторного признака Х | B 41 : C 45 и B 52 : C 56 |
►Внимание!
Табл.7 копируется на Лист 2 Рабочего файла дважды!
По окончании копирования информации ЛР-1 необходимо ознакомиться с распределением памяти Листа 2, выделенной для результативных таблиц с тем, чтобы систематически проверять на этапах ЛР-2 правильность адресации получаемых результативных таблиц. При этом во избежание ошибок содержание каждой получаемой результативной таблицы следует сопоставлять с соответствующими таблицами демонстрационного примера.
Структура
Листа 2
Рабочего файла
stat
_
lab
.
xls
(распределение памяти для результативных таблиц)
| | A | B | C |
| 1 | Таблица 2.1 | ||
| 2 | Исходные данные | ||
| 3 | Номер предприятия | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. |
| 4 | | | |
| 5 | | | |
| 6 | | | |
…
| 31 | | | |
| 32 | | | |
| 33 | | | |
| | A | B | C | D | E |
| 37 | Таблица 2.2 | ||||
| 38 | Зависимость выпуска продукции от среднегодовой стоимости основных фондов | ||||
| 39 | Номер группы | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий | Выпуск продукции, млн руб. | |
| 40 | Всего | В среднем на одно предприятие | |||
| 41 | 1 | | | СУММ() | D41/C41 |
| 42 | 2 | | | СУММ() | D42/C42 |
| 43 | 3 | | | СУММ() | D43/C43 |
| 44 | 4 | | | СУММ() | D44/C44 |
| 45 | 5 | | | СУММ() | D45/C45 |
| 46 | Итого | | СУММ(C41:C45) | СУММ(D41:D45) | D46/C46 |
| 47 | | | | | |
| 48 | | | | | |
| 49 | Таблица 2.3 | | |||
| 50 | Показатели внутригрупповой дисперсии | | |||
| 51 | Номер группы | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий | Внутригрупповая дисперсия | |
| 52 | 1 | | | ДИСПР() | |
| 53 | 2 | | | ДИСПР() | |
| 54 | 3 | | | ДИСПР() | |
| 55 | 4 | | | ДИСПР() | |
| 56 | 5 | | | ДИСПР() | |
| 57 | Итого | | СУММ(C52:C56) | | |
| | A | B | C | D |
| 60 | Таблица 2.4 | |||
| 61 | Показатели дисперсий и эмпирического корреляционного отношения | |||
| 62 | Общая дисперсия | Средняя из внутригрупповых дисперсий | Межгрупповая дисперсия | Эмпирическое корреляционное отношение η |
| 63 | ДИСПР(C4:C33) | СУММПРОИЗВ (D52:D56,C52:C56)/C46 | A63-B63 | КОРЕНЬ (C63/A63) |
| | A | B | C | D | E | F |
| 73 | Выходные таблицы | | | | ||
| 74 | | | | | | |
| 75 | ВЫВОД ИТОГОВ | | | | | |
| 76 | Таблица 2.5 | | | | | |
| 77 | Регрессионная статистика | | | | | |
| 78 | Множественный R | | | | | |
| 79 | R-квадрат | | | | | |
| 80 | Нормированный R-квадрат | | | | | |
| 81 | Стандартная ошибка | | | | | |
| 82 | Наблюдения | | | | | |
| 83 | Таблица 2.6 | |||||
| 84 | Дисперсионный анализ | |||||
| 85 | | df | SS | MS | F | Значимость F |
| 86 | Регрессия | | | | | |
| 87 | Остаток | | | | | |
| 88 | Итого | | | | | |
| | A | B | C | D | E | F | G | H | I |
| 89 | Таблица 2.7 | ||||||||
| 90 | | Коэффициенты | Стандартная ошибка | t-статис - тика | P- зна - чение | Нижние 95% | Верхние 95% | Нижние 68,3% | Верхние 68,3% |
| 91 | Y-пересечение | | | | | | | | |
| 92 | Переменная X1 | | | | | | | | |
| 93 | | | | | | | | | |
| 94 | | | | | | | | | |
| 95 | | | | | | | | | |
| 96 | Таблица 2.8 | | | | | | | ||
| 97 | ВЫВОД ОСТАТКА | | | | | | | ||
| 98 | Наблюдение | Предсказанное Y | Остатки | | | | | | |
| 99 | | | | | | | | | |
| 100 | | | | | | | | | |
| … | | | | | | | | | |
2. Этап выполнения статистических расчетов
Задание 1
Построение аналитической группировки для выявления корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков
Выполнение Задания 1 заключается в решении следующих двух задач:
Задача 1. Построение аналитической группировки предприятий по факторному признаку Х – Среднегодовая стоимость основных производственных фондов.
Задача 2. Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения.
Алгоритмы выполнения Задания 1
Задача 1. Построение аналитической группировки предприятий по признаку Среднегодовая стоимость основных производственных фондов.
Задача решается в три этапа:
1. Ранжирование единиц совокупности по возрастанию факторного признака Х – Среднегодовая стоимость основных производственных фондов – алгоритм 1.1.
2. Распределение предприятий по группам в соответствии с интервальным рядом распределения факторного признака – алгоритм 1.2.
3. Расчет суммарных и средних групповых значений результативного признака Y – Выпуск продукции – алгоритмы 1.3 и 1.4.
Алгоритм 1.1. Ранжирование исходных данных по факторному признаку
1. Выделить исходные данные (вместе с заголовком) табл. 2.1 (А3:С33);
2. Данные => Сортировка;
3. Сортировать по <= заголовок столбца, по которому выполняется сортировка, т.е. Среднегодовая стоимость основных производственных фондов;
4. По возрастанию/по убыванию – устанавливается в положение по возрастанию;
5. Затем и В последнюю очередь по – НЕ активизировать;
6. Идентифицировать поля по подписям/обозначениям столбцов листа – устанавливается в положение подписям;
7. ОК.
В результате указанных действий в таблице 2.1 размещаются данные, ранжированные по возрастанию признакаСреднегодовая стоимость основных производственных фондов.
Алгоритм 1.2. Выделение групп предприятий с помощью заливки контрастным цветом
1. Из всего диапазона отсортированных данных A4:C33 выделить мышью диапазон ячеек первой группы, для чего необходимо отсчитать в ранжированном ряду количество строк, соответствующее числу предприятий первой группы (графа 3 табл.2.2);
2. Нажать на панели инструментов кнопку
3. Выбрать цвет заливки по собственному усмотрению;
►Внимание!
Цвет желательно брать контрастный, чтобы четко отделить одну группу от другой.
4. Выполнить действия 1–3 для всех групп, выбирая контрастные цвета для цветовой заливки очередной группы.
Результаты работы алгоритмов 1.1 и 1.2 для демонстрационного примера представлены в табл.2.1–ДП.
| | А | В | С |
| 1 | Таблица 2.1–ДП | ||
| 2 | Исходные данные | ||
| 3 | Номер предприятия | Среднегодовая стоимость основных производственных фондов, млн руб. | Выпуск продукции, млн руб. |
| 4 | 1 | 94,00 | 110,00 |
| 5 | 2 | 107,00 | 101,00 |
| 6 | 3 | 134,00 | 120,00 |
| 7 | 4 | 157,00 | 81,00 |
| 8 | 5 | 163,00 | 80,00 |
| 9 | 6 | 167,00 | 114,00 |
| 10 | 29 | 167,00 | 114,00 |
| 11 | 7 | 173,00 | 161,00 |
| 12 | 8 | 173,00 | 90,00 |
| 13 | 9 | 177,00 | 178,00 |
| 14 | 10 | 179,00 | 107,00 |
| 15 | 11 | 200,00 | 125,00 |
| 16 | 12 | 201,00 | 108,00 |
| 17 | 13 | 205,00 | 133,00 |
| 18 | 30 | 205,00 | 133,00 |
| 19 | 14 | 208,00 | 124,00 |
| 20 | 15 | 212,00 | 201,00 |
| 21 | 16 | 213,00 | 161,00 |
| 22 | 17 | 214,00 | 151,00 |
| 23 | 18 | 216,00 | 169,00 |
| 24 | 19 | 218,00 | 149,00 |
| 25 | 20 | 230,00 | 180,00 |
| 26 | 21 | 234,00 | 148,00 |
| 27 | 22 | 237,00 | 162,00 |
| 28 | 23 | 241,00 | 166,00 |
| 29 | 24 | 248,00 | 168,00 |
| 30 | 32 | 260,00 | 224,00 |
| 31 | 26 | 276,00 | 171,00 |
| 32 | 27 | 290,00 | 191,00 |
| 33 | 28 | 298,00 | 220,00 |
Алгоритм 1.3. Расчет суммарных групповых значений результативного признака
1. В ячейке (D
41), выделенной для суммарного значения результативного признакаВыпуск продукции первой группы, перед формулой поставить знак равенства «=»;
2. В качествеаргумента функции СУММ() указать диапазон ячеек из табл. 2.1 с результативными значениями уi первой группы (визуально легко определяется по цвету заливки диапазона);
►Внимание!
Здесь возможен ошибочный захват мышью столбца значений первого (факторного) признака Х. Необходимо проконтролировать правильность задания аргумента функции СУММ().
3. Enter;
4. Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
Алгоритм 1.4. Расчет средних групповых значений результативного признака
В таблице 2.2 приведены формулы для расчета средних групповых значений результативного признака Выпуск продукции.
1. В ячейке (Е41), выделенной для среднего значения результативного признакаВыпуск продукции первой группы, перед формулой поставить знак равенства «=»;
2. Enter
;
3. Выполнить действия 1–2 поочередно для всех групп;
4. В ячейках (C
46,
D
46 иE
46), выделенных для расчета итоговых сумм:
Перед формулой поставить знак равенства «=»;
Enter
.
Результаты работы алгоритмов 1.3 и 1.4 для демонстрационного примера приведены в табл. 2.2–ДП.
| | A | B | C | D | E |
| 37 | Таблица 2.2–ДП | ||||
| 38 | Зависимость выпуска продукции от среднегодовой стоимости основных производственных фондов | ||||
| 39 | Номер группы | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий | Выпуск продукции, млн руб. | |
| 40 | Всего | В среднем на одно предприятие | |||
| 41 | 1 | 94 – 134,8 | 3 | 331,00 | 110,33 |
| 42 | 2 | 134,8 – 175,6 | 6 | 640,00 | 106,67 |
| 43 | 3 | 175,6 – 216,4 | 11 | 1590,00 | 144,55 |
| 44 | 4 | 216,4 – 257,2 | 6 | 973,00 | 162,17 |
| 45 | 5 | 257,2 – 298 | 4 | 806,00 | 201,50 |
| 46 | Итого | | 30 | 4340,00 | 144,67 |
Задача 2. Оценка тесноты связи изучаемых признаков на основе эмпирического корреляционного отношения
Задача решается в два этапа:
1. Расчет внутригрупповых дисперсий результативного признака.
2. Расчет эмпирического корреляционного отношения.
Алгоритм 2.1. Расчет внутригрупповых дисперсий результативного признака
1. В ячейке, выделенной для внутригрупповых дисперсий первой группы (D
52), перед формулой поставить знак равенства «=»;
2. В качествеаргумента функции ДИСПР() указать диапазон ячеек из табл. 2.1 со значениями yi
первой группы – визуально легко определяется по цвету заливки диапазона;
►Внимание!
Здесь возможен ошибочный захват мышью столбца значений первого (факторного признака) Х. Необходимо проконтролировать правильность задания аргумента функции ДИСПР().
3. Enter;
4. Выполнить действия 1–3 поочередно для всех групп, используя цветовые заливки диапазонов.
5. Для расчета итоговой суммы в табл. 2.3 (в ячейке C
57) перед формулой необходимо поставить знак равенства «=»;
6. Enter
.
Результат работы алгоритма 2.1 для демонстрационного примера представлен в табл.2.3–ДП.
| | A | B | C | D |
| 49 | Таблица 2.3–ДП | |||
| 50 | Показатели внутригрупповой дисперсии | |||
| 51 | Номер группы | Группы предприятий по признаку Среднегодовая стоимость основных производственных фондов, млн руб. | Число предприятий | Внутригрупповая дисперсия |
| 52 | 1 | 94 – 134,8 | 3 | 60,22 |
| 53 | 2 | 134,8 – 175,6 | 6 | 784,56 |
| 54 | 3 | 175,6 – 216,4 | 11 | 821,16 |
| 55 | 4 | 216,4 – 257,2 | 6 | 123,47 |
| 56 | 5 | 257,2 – 298 | 4 | 472,25 |
| 57 | Итого | | 30 | |
Алгоритм 2.2. Расчет эмпирического корреляционного отношения
1. В ячейке, выделенной для общей дисперсии (А63), перед формулой поставить знак равенства «=»;
2. Enter
;
3. В ячейке, выделенной для средней из внутригрупповых дисперсий (В63), перед формулой поставить знак равенства «=»;
4. Enter
;
►
Примечание
. В случае если при выполнении вычисления в ячейке В63 выдается сообщение "Ошибка в формуле", то разделительный знак «,» между аргументами функции СУМПРОИЗВ(Д1,Д2) необходимо заменить на знак «;».
5. В ячейке, выделенной для значения межгрупповой (факторной) дисперсии (С63), перед формулой поставить знак равенства «=»;
6. Enter
;
7. В ячейке, выделенной для эмпирического корреляционного отношения (D
63), перед формулой поставить знак равенства «=»;
8. Enter
.
Результат работы алгоритма 2.2 для демонстрационного примера представлен в табл.2.4–ДП.
| | A | B | C | D |
| 60 | Таблица 2.4–ДП | |||
| 61 | Показатели дисперсий и эмпирического корреляционного отношения | |||
| 62 | Общая дисперсия | Средняя из внутригрупповых дисперсий | Межгрупповая дисперсия | Эмпирическое корреляционное отношение η |
| 63 | 1450,288889 | 551,6853535 | 898,6035354 | 0,787148735 |
Задание 2
Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента
Регрессия
надстройки
Пакет анализа
Алгоритм выполнения Задания 2
Алгоритм 1. Расчет параметров уравнения линейной регрессии и проверки адекватности модели исходным данным
1.
Сервис => Анализ данных => Регрессия => ОК;
2. Входной интервал Y <= диапазон ячеек таблицы со значениями признакаY
–Выпуск продукции (С4:С33);
3. Входной интервал X – диапазон ячеек таблицы со значениями признакаX
–Среднегодовая стоимость основных производственных фондов (В4:В33);
4. Метки в первой строке/Метки в первом столбце –НЕ активизировать;
5. Уровень надежности <= 68,3 (или 68.3);
6. Константа–ноль – НЕ активизировать;
7. Выходной интервал <= адрес ячейки заголовка первого столбца первой выходной результативной таблицы (А75);
8. Новый рабочий лист и Новая рабочая книга – НЕ активизировать;
9. Остатки – Активизировать;
10. Стандартизованные остатки – НЕ активизировать;
11. График остатков –НЕ активизировать;
12. График подбора – НЕ активизировать;
13. График нормальной вероятности –
НЕ активизировать;
14. ОК.
В результате указанных действий осуществляется вывод четырех выходных таблиц на Лист 2 Рабочего файла, начиная с ячейки, указанной в поле Выходной интервал диалогового окна инструмента Регрессия (для демонстрационного примера они имеют следующий вид).
| | А | В |
| 77 | Регрессионная статистика | |
| 78 | Множественный R | 0,753661673 |
| 79 | R–квадрат | 0,568005917 |
| 80 | Нормированный R-квадрат | 0,552577557 |
| 81 | Стандартная ошибка | 25,90882817 |
| 82 | Наблюдения | 30 |
| | А | B | C | D | E | F |
| 84 | Дисперсионный анализ | |||||
| 85 | | df | SS | MS | F | Значимость F |
| 86 | Регрессия | 1 | 24713,1801 | 24713,1801 | 36,81570256 | 1,52606E-06 |
| 87 | Остаток | 28 | 18795,48657 | 671,2673773 | | |
| 88 | Итого | 29 | 43508,66667 | | | |
| A | B | C | D | E | F | G | H | I | |
| 90 | | Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | Нижние 68,3% | Верхние 68,3% |
| 91 | Y-пересечение | 21,64454934 | 20,81975413 | 1,039615992 | 0,307412837 | -21,0028 | 64,29193 | 0,432468 | 42,85664 |
| 92 | Переменная X 1 | 0,605324507 | 0,099763508 | 6,067594462 | 1,52606E-06 | 0,400968 | 0,809681 | 0,503681 | 0,706968 |
| | A | B | C |
| 96 | ВЫВОД ОСТАТКА | ||
| 97 | | ||
| 98 | Наблюдение | Предсказанное Y | Остатки |
| 99 | 1 | 78,54505301 | 31,45494699 |
| 100 | 2 | 86,4142716 | 14,5857284 |
| 101 | 3 | 102,7580333 | 17,24196671 |
| 102 | 4 | 116,680497 | -35,68049696 |
| | … | … | … |
| 128 | 30 | 202,0312525 | 17,96874754 |
Задание 3
Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного нелинейного уравнения регрессии
Алгоритмы выполнения Задания 3
Алгоритм 1. Построение уравнений регрессионных моделей для различных видов нелинейной зависимости признаков с использованием средств инструмента
Мастер диаграмм
1. Выделить мышью диаграмму рассеяния признаков, расположенную начиная с ячейки Е4, и увеличить диаграмму на весь экран, используя прием "захват мышью";
2. Диаграмма => Добавить линию тренда;
3. В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид регрессионной модели – полином 2-го порядка;
4. Выбрать вкладку Параметры и выполнить действия:
1. Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда– полином 2-го порядка;
2. Поле Прогноз вперед на – НЕ активизировать;
3. Поле Прогноз назад на – НЕ активизировать;
4. Флажок Пересечение кривой с осью Y
в точке –
НЕ активизировать;
5. Флажок Показывать уравнение на диаграмме – Активизировать;
6. Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;
7. ОК;
8. Установить курсор на линию регрессии и щелкнуть правой клавишей мыши;
9. В появившемся диалоговом окне Формат линии тренда выбрать по своему усмотрению тип, цвет и толщину линии;
10. ОК;
11. Выделить уравнение регрессии и индекс детерминации R2 и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.
5. Действия 2 – 4 (в п.4 шаги 1–11) выполнить поочередно для следующих видов регрессионных моделей:
– полином 3-го порядка,
– степенная функция.
По окончании работы алгоритма 1 выполнить следующие действия:
1. Присвоить полученной диаграмме заголовок "Диаграмма 2.1" и удалить линии сетки по оси Y
(
алгоритм 2);
2. Снять заливку области построения (
алгоритм 3);
3. При необходимости изменить масштаб шкалы осей диаграммы (алгоритм 4).
Алгоритм 2. Присвоение полученной диаграмме заголовка
"
Диаграмма 2.1
" и удаление линий сетки по оси Y
1. Выделить мышью построенную диаграмму;
2. Диаграмма => Параметры диаграммы;
3. В появившемся диалоговом окне Параметры диаграммы выбрать вкладку Заголовки и в поле Название диаграммы ввести заголовок диаграммы "
Диаграмма 2.1
";
4. Выбрать вкладку Линии сетки, в полях Ось Х и Ось Y все флажки – Не активизировать;
5. ОК.
Алгоритм 3. Снятие заливки области построения
1. Выделить мышью Область построения диаграммы;
2. Формат => Выделенная область построения;
3. В появившемся диалоговом окне Формат области переключатель Заливка установить в положение Обычная;
4. ОК.
Алгоритм 4. Изменение масштаба шкалы осей диаграммы
1. Выделить на полученной диаграмме ось, для которой необходимо изменить масштаб (подвести курсор к требуемой оси и щелкнуть левой клавишей мыши);
2. Формат => Выделенная ось;
3. В появившемся диалоговом окне Формат оси выбрать вкладку Шкала;
4. В поле Минимальное значение – ввести минимальное (или несколько ниже) значение признака, соответствующего форматируемой оси;
5. В поле Максимальное значение – ввести максимальное (или несколько выше) значение признака, соответствующего форматируемой оси;
6. ОК.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
В результате указанных действий для выбранных видов моделей регрессии осуществляется вывод на диаграмму рассеяния 3-х уравнений регрессии, их графиков и значений соответствующих индексов детерминации R2 (для демонстрационного примера Диаграмма 2.1–ДП приведена на рис. 2.1).
Лабораторная работа № 3
Автоматизированный анализ динамики социально-экономических явлений в среде
MS
Excel
.
В процессе статистического изучения деятельности одного из предприятий получены данные о годовом выпуске продукции (в стоимостном выражении) за шестилетний период, а также данные о выпуске продукции по месяцам за 6-ой год.
Полученные два ряда динамики представлены в формате электронных таблиц процессора Excel, годовые данные - в диапазоне ячеек A
6:
B
12, а данные за 6-ой год по месяцам - в диапазоне D6:E19. Для демонстрационного примера (ДП) данные о выпуске продукции приведены в табл. 3.1-ДП.
Таблица 3.1-ДП
Исходные данные демонстрационного примера
| A | B | C | D | Е | |
| 6 | Годы | Выпуск продукции, млн. руб. | По месяцам за 6-ой год | Выпуск продукции, млн. руб. | |
| 7 | 1 | 3020,00 | январь | 175,00 | |
| 8 | 2 | 3260,00 | февраль | 241,00 | |
| 9 | 3 | 3650,00 | март | 300,00 | |
| 10 | 4 | 3530,00 | апрель | 270,00 | |
| 11 | 5 | 3765,00 | май | 330,00 | |
| 12 | 6 | 4077,00 | июнь | 310,00 | |
| 13 | июль | 366,00 | |||
| 14 | | | август | 341,00 | |
| 15 | | | сентябрь | 420,00 | |
| 16 | | | октябрь | 441,00 | |
| 17 | | | ноябрь | 453,00 | |
| 18 | | | | декабрь | 430,00 |
| 19 | | | | Итого | 4077,00 |
В процессе автоматизированного анализа динамики выпуска продукции за шестилетний период необходимо решить следующие статистические задачи.
Задание 1. Расчёт и анализ показателей ряда динамики выпуска продукции за шестилетний период.
Задание 2. Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции.
Задание 3. Выявление тенденции развития изучаемого явления (тренда) по данным о выпуске продукции по месяцам за 6-ой год методами скользящей средней и аналитического выравнивания.
II
. Порядок выполнения лабораторной работы
1. Подготовительный этап
Для выполнения Лабораторной работы №3 (ЛР3) выделяется Лист 3 Рабочего файла, сформированного в персональной папке студента при выполнении ЛР1.
На данном этапе студент должен записать в Отчетный файл ЛР3 индивидуальный вариант исходных данных.
На Листе 3 Рабочего файла персональной папки студента заготовлены макеты таблиц, используемые при выполнении ЛР3.
Расположение исходных данных и макетов результативных таблиц в Рабочем файле на Листе 3
персональной папки студента
| | A | B | C | D | E | |
| 4 | Таблица 3.1 | |||||
| 5 | Исходные данные | |||||
| 6 | Годы | Выпуск продукции, млн. руб. | По месяцам за 6-ой год | Выпуск продукции, млн. руб. | ||
| 7 | 1 | январь | ||||
| 8 | 2 | февраль | ||||
| 9 | 3 | март | ||||
| 10 | 4 | апрель | ||||
| 11 | 5 | май | ||||
| 12 | 6 | июнь | ||||
| 13 | июль | |||||
| 14 | | | август | |||
| 15 | | | сентябрь | |||
| 16 | | | октябрь | |||
| 17 | | | ноябрь | |||
| 18 | | | декабрь | |||
| 19 | | | Итого | |||
| | A | B | C | D | E | F | G | H | I |
| 21 | Таблица 3.2 | ||||||||
| 22 | Определения показателей динамики выпуска продукции | ||||||||
| 23 | Годы | Выпуск продукции, млн. руб. | Абсолютный прирост, млн. руб. | Темп роста, % | Темп прироста, % | Абсолют-ное значение 1 % прироста | |||
| 24 | цепной | базисный | цепной | базисный | цепной | базисный | |||
| 25 | 1-й | | | | | | | | |
| 26 | 2-й | | B26-B25 | B26-$B$25 | B26/B25*100 | B26/$B$25*100 | E26-100 | F26-100 | 0,01*B25 |
| 27 | 3-й | | | | | | | | |
| 28 | 4-й | | | | | | | | |
| 29 | 5-й | | | | | | | | |
| 30 | 6-й | | | | | | | | |
| | A | B | C | D | E |
| 32 | Таблица 3.3 | ||||
| 33 | Расчёт средних показателей ряда динамики | ||||
| 34 | Средний уровень ряда динамики, млн. руб., | =СУММ(B7:B12)/6 | |||
| 35 | Средний абсолютный прирост, млн. руб., | =(B12-B7)/5 | |||
| 36 | Средний темп роста, %, | =ОКРУГЛ(СТЕПЕНЬ(B12/B7;1/5)*100;1) | |||
| 37 | Средний темп прироста %, | =Е36-100 | |||
| 38 | | | |||
| 39 | Таблица 3.4 | ||||
| 40 | Прогноз выпуска продукции на год вперед | ||||
| 41 | По среднему абсолютному приросту, млн. руб., | В12+Е35 | |||
| 42 | По среднему темпу роста, %, | В12*(Е36/100) | |||
| 44 | Таблица 3.5 | ||
| 4 5 | Выпуск продукции за 6-ой год | ||
| 4 6 | Месяцы | Выпуск продукции, млн. руб. | Скользящее среднее |
| 4 7 | январь | | |
| 4 8 | февраль | | |
| 4 9 | март | | |
| 50 | апрель | | |
| 51 | май | | |
| 52 | июнь | | |
| 53 | июль | | |
| 54 | август | | |
| 5 5 | сентябрь | | |
| 5 6 | октябрь | | |
| 5 7 | ноябрь | | |
| 5 8 | декабрь | | |
2. Этап выполнения статистических расчетов
Задание 1.
Расчёт и анализ показателей ряда динамики выпуска продукции за шестилетний период.
Выполнение Задания 1 заключается в решении двух задач:
Задача 1. Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп прироста (сокращения) и абсолютное значение 1 % прироста.
Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста.
Алгоритмы выполнения Задания 1
Задача 1. . Расчет цепных и базисных показателей динамики: абсолютный прирост (сокращение); темп роста (снижения); темп прироста (сокращения) и абсолютное значение 1 % прироста
Алгоритм 1.1
.
Расчёт цепных и базисных показателей динамики, характеризующих изменение отдельных уровней ряда динамики
1. В ячейке, выделенной для значения абсолютного прироста цепного за первый год (С26), перед формулой поставить знак равенства «=»;
2. Enter;
3. Установить курсор в правом нижнем углу ячейки (С26) с формулой – образцом (курсор примет форму черного крестика) и, удерживая левую клавишу мыши в нажатом состоянии, переместить курсор до нижней клетки графы. Отпустить клавишу мыши (формула – образец размножилась на всю графу).
4. Выполнить действия 1–2 поочередно для всех аналитических показателей ряда динамики табл.3.2:
абсолютного прироста базисного;
темпа роста цепного и базисного;
темпа прироста цепного и базисного;
абсолютного значения 1% прироста.
Результат работы алгоритма 1.1 для демонстрационного примера приведены в табл. 3.2–ДП.
| | A | B | C | D | E | F | G | H | I |
| 21 | Таблица 3.2–ДП | ||||||||
| 22 | Показатели динамики выпуска продукции | ||||||||
| 23 | Годы | Выпуск продукции, млн. руб. | Абсолютный прирост, тыс. тонн | Темп роста, % | Темп прироста, % | Абсолютное значение 1 % прироста | |||
| 24 | цепной | базисный | цепной | базисный | цепной | базисный | | ||
| 25 | 1-й | 3020,00 | | | | | | | |
| 26 | 2-й | 3260,00 | 240,00 | 240,00 | 107,9 | 107,9 | 7,9 | 7,9 | 30,2 |
| 27 | 3-й | 3650,00 | 390,00 | 630,00 | 112,0 | 120,9 | 12,0 | 20,9 | 32,6 |
| 28 | 4-й | 3530,00 | -120,00 | 510,00 | 96,7 | 116,9 | -3,3 | 16,9 | 36,5 |
| 29 | 5-й | 3765,00 | 235,00 | 745,00 | 106,7 | 124,7 | 6,7 | 24,7 | 35,3 |
| 30 | 6-й | 4077,00 | 312,00 | 1 057,00 | 108,3 | 135,0 | 8,3 | 35,0 | 37,65 |
Задача 2. Расчет средних показателей ряда динамики: средний уровень ряда динамики; средний абсолютный прирост; средний темп роста и средний темп прироста
Алгоритм 1.2. Расчёт средних показателей ряда динамики
1. В ячейке, выделенной для значениясредний уровень ряда динамики (E
34),
перед формулой поставить знак равенства «=»;
2. Enter;
3. Выполнить действия 1–2 поочередно для всех средних показателей ряда динамики табл.3.3:
среднего абсолютного прироста;
среднего темпа роста;
среднего темпа прироста.
Результат работы алгоритма 1.2 для демонстрационного примера приведены в табл. 3.3–ДП.
| | A | B | C | D |
| 3 2 | Таблица 3.3-ДП | |||
| 3 3 | Средние показатели ряда динамики | |||
| 3 4 | Средний уровень ряда динамики, млн. руб., | 3550,33 | ||
| 3 5 | Средний абсолютный прирост, млн. руб., | 211,40 | ||
| 3 6 | Средний темп роста, %, | 106,2 | ||
| 3 7 | Средний темп прироста %, | 6,2 | ||
Задание 2.
Прогноз показателя выпуска продукции на 7-ой год методом экстраполяции.
Выполнение Задания 2 заключается в решении двух задач:
Задача 1. Прогнозирование выпуска продукции предприятием на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста.
Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.
Алгоритмы выполнения Задания 2
Задача 1. Прогнозирование выпуска продукции предприятием на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста.
Алгоритм 2.1
.
Расчёт выпуска продукции на год вперёд с использованием среднего абсолютного прироста и среднего темпа роста
1. В ячейке, выделенной для значений прогнозируемого выпуска продукции по среднему абсолютному приросту (Е41), перед формулой поставить знак равенства «=».
2. Enter;
3. Выполнить действия 1–2 для прогнозируемого выпуска продукции по значению среднего темпа роста (табл.3.4).
Результат работы алгоритма 2.1 для демонстрационного примера приведен в табл. 3.4 – ДП.
| | A | B | C | D | E |
| 39 | Таблица 3.4–ДП | ||||
| 40 | Прогноз выпуска продукции на год вперед | ||||
| 41 | По среднему абсолютному приросту, млн. руб., | 4288,40 | |||
| 42 | По среднему темпу роста, %, | 4329,77 | |||
Задача 2. Прогнозирование выпуска продукции предприятием на год вперёд с использованием аналитического выравнивания ряда динамики по прямой, параболе и полиному 3-го порядка.
Алгоритм 2.2.
Построение графика
динамики выпуска продукции за 6 лет
с использованием средств инструмента
МАСТЕР ДИАГРАММ
1. Выделить мышью ячейки, содержащие выпуск продукции за 6 лет (диапазон ячеек B
7:
B
12);
2. Вставка=>Диаграмма=>Точечная;
3. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;
4. Далее;
5. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и задать имя ряда 1 – «Исходные данные»;
6. Далее;
7. В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Прогнозирование выпуска продукции на 7-ой год») и осей Х («Годы») и У («Выпуск продукции. млн. руб.»;;
8. Готово;
9. Выделить на полученной диаграмме ось Y (подвести курсор к требуемой оси и щелкнуть левой клавишей мыши);
10. Формат => Выделенная ось;
11. В появившемся диалоговом окне Формат оси выбрать вкладку Шкала;
12. В поле Минимальное значение – ввести минимальное (или несколько ниже) значение признака «Выпуск продукции»;
13. ОК.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
Алгоритм 2.3
.
Нахождение тренда ряда динамики выпуска продукции методом аналитического выравнивания
и прогнозирование его на год вперед с помощью инструмента
Мастер диаграмм
1. Выделить мышью диаграмму «Прогнозирование выпуска продукции на 7-ой год», расположенную в конце Рабочего файла;
2. Диаграмма => Добавить линию тренда;
3. В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид линии тренда – линейная;
4. Выбрать вкладку Параметры и выполнить действия:
1. Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда– Прямая;
2. В поле Прогноз вперед на…едициц ввести значение «1»;
3. Поле Прогноз назад на…единиц – НЕ активизировать;
4. Флажок Пересечение кривой с осью Y
в точке – НЕ активизировать;
5. Флажок Показывать уравнение на диаграмме – Активизировать;
6. Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;
7. ОК;
8. Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;
9. В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;
10. ОК;
11. Выделить уравнение линии тренда и индекс детерминации R2 и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.
5. Действия 1 – 4 (в п.4 шаги 1–11) выполнить для линии тренда:
Парабола (полином 2-го порядка);
Степенная.
Результат работы алгоритмов 2.2 – 2.3 для демонстрационного примера представлен на рис.3.1.
Рис.3.1. График динамики выпуска продукции за 6 лет и прогноз выпуска на год вперед
Задание 3.
Выявление тенденции развития изучаемого явления (тренда)
по данным о выпуске продукции по месяцам за 6-ой год методами скользящей средней и аналитического выравнивания.
Выполнение Задания 3 заключается в решении двух задач:
Задача 1. Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы.
Задача 2. Аналитическое выравнивание по прямой и параболе.
Алгоритмы выполнения Задания 3
Задача 1. Расчет скользящей средней, полученной на основе трёхчленной скользящей суммы
Алгоритм 3.1.
Нахождение значений
скользящей средней
с помощью инструмента
СКОЛЬЗЯЩЕЕ СРЕДНЕЕ
надстройки
ПАКЕТ АНАЛИЗА
1.
Сервис => Анализ данных => Скользящее среднее => ОК;
2. Входной интервал <= диапазон ячеек табл. 3.5 со значениями признака–Выпуск продукции (В47:В58);
3. Метки в первой строке–НЕ активизировать;
4. Интервал – НЕ активизировать;
5. Выходной интервал <= адрес первой ячейки третьего столбца выходной результативной таблицы (С47);
6. Новый рабочий лист и Новая рабочая книга – НЕ активизировать;
7. Вывод графика –Активизировать;
8. Стандартные погрешности –
НЕ активизировать;
9. ОК.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
В результате указанных действий осуществляется вывод значений скользящей средней, начиная с ячейки, указанной в поле Выходной интервал диалогового окна инструмента Скользящее среднее, и графика. Для демонстрационного примера они представлены в табл.3.5 и на рис.3.2.
| | A | B | C | |
| 44 | Таблица 3.5 -ДП | |||
| 45 | Выпуск продукции за 6-ой год | |||
| 46 | Месяцы | Выпуск продукции, млн. руб. | Скользящее среднее | |
| 47 | январь | 175,00 | #Н/Д | |
| 48 | февраль | 241,00 | #Н/Д | |
| 49 | март | 300,00 | 238,67 | |
| 50 | апрель | 270,00 | 270,33 | |
| 51 | май | 330,00 | 300,00 | |
| 52 | июнь | 310,00 | 303,33 | |
| 53 | июль | 366,00 | 335,33 | |
| 54 | август | 341,00 | 339,00 | |
| 55 | сентябрь | 420,00 | 375,67 | |
| 56 | октябрь | 441,00 | 400,67 | |
| 57 | ноябрь | 453,00 | 438,00 | |
| 58 | декабрь | 430,00 | 441,33 | |
Рис.3.2. График сглаживания ряда динамики выпуска продукции за 6-ой год, сгенерированный в режиме «скользящее среднее» Пакета анализа
Алгоритм 3.2
. Приведение выходной таблицы к виду, принятому в статистике
1. Ячейку С47, содержащую термин " #Н/Д", выделить мышью и очистить, нажав клавишу [Delete];
2. Ячейки результативной таблицы (С49:С58), содержащие значения «Скользящее среднее», вырезать с помощью инструмента
3. Вставить в табл. 3.5, начиная с ячейки С48, с помощью инструмента
4. Отформатировать таблицу по образцу с помощью инструмента
Для демонстрационного примера полученная результативная таблица выглядит следующим образом.
| | A | B | C | |
| 44 | Таблица 3.5-ДП | |||
| 45 | Выпуск продукции за 6-ой год | |||
| 46 | Месяцы | Выпуск продукции, млн. руб. | Скользящее среднее | |
| 47 | январь | 175,00 | ||
| 48 | февраль | 241,00 | 238,67 | |
| 49 | март | 300,00 | 270,33 | |
| 50 | апрель | 270,00 | 300,00 | |
| 51 | май | 330,00 | 303,33 | |
| 52 | июнь | 310,00 | 335,33 | |
| 53 | июль | 366,00 | 339,00 | |
| 54 | август | 341,00 | 375,67 | |
| 55 | сентябрь | 420,00 | 400,67 | |
| 56 | октябрь | 441,00 | 438,00 | |
| 57 | ноябрь | 453,00 | 441,33 | |
| 58 | декабрь | 430,00 | ||
График сглаживания ряда динамики выпуска продукции методом скользящей средней представлен на рис. 3.3.
Рис.3.3. График сглаживания скользящей средней ряда динамики выпуска продукции за 6-ой год
Задача 2. Аналитическое выравнивание по прямой и параболе
Алгоритм 3.3
. Построение графика динамики выпуска продукции по месяцам за 6-ой год с использованием средств инструмента
МАСТЕР ДИАГРАММ
1. Выделить мышью столбец данных в диапазоне B
47:В58 (табл. 3.5);
2. Вставка=>Диаграмма=>Точечная;
3. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4) выбрать вид точечной диаграммы, на которой значения соединены отрезками;
4. Далее;
5. В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4) выбрать вкладку Ряд и ввести имя ряда «Исходные данные»;
6. Далее;
7. В появившемся диалоговом окне Мастер диаграмм (шаг 3 из 4) выбрать вкладку Заголовки и задать названия диаграммы («Выпуск продукции за 6-ой год по месяцам») и осей Х («Месяцы») и У («Выпуск продукции. млн. руб.»;
8. Готово.
Выделить диаграмму и с помощью приема "захват мышью" переместить ее в конец Рабочего файла.
Алгоритм 3.4
. Сглаживание ряда динамики выпуска продукции методом аналитического выравнивания
с помощью инструмента
Мастер диаграмм
1. Выделить мышью диаграмму «Выпуск продукции за 6-ой год по месяцам», расположенную в конце Рабочего файла;
2. Диаграмма => Добавить линию тренда;
3. В появившемся диалоговом окне Линия тренда выбрать вкладку Тип и задать вид линии тренда – линейная;
4. Выбрать вкладку Параметры и выполнить действия:
1. Переключатель Название аппроксимирующей кривой: автоматическое/другое – установить в положение другое и ввести имя тренда - Прямая;
2. Поле Прогноз вперед на – НЕ активизировать;
3. Поле Прогноз назад на – НЕ активизировать;
4. Флажок Пересечение кривой с осью Y
в точке – НЕ активизировать;
5. Флажок Показывать уравнение на диаграмме – Активизировать;
6. Флажок Поместить на диаграмму величину достоверности аппроксимации R2 – Активизировать;
7. ОК;
8. Установить курсор на линию тренда, щелкнуть правой клавишей мыши и выбрать меню Формат линии тренда;
9. В появившемся диалоговом окне Формат линии тренда выбрать вкладку Вид и задать по своему усмотрению тип, цвет и толщину линии;
10. ОК;
11. Выделить уравнение линии тренда и индекс детерминации R2 и с помощью приема "захват мышью" вынести их за корреляционное поле. При необходимости уменьшить размер шрифта.
5. Действия 1 – 4 (в п.4 шаги 1–11) выполнить для линии тренда парабола.
По окончании работы алгоритма 2.4 выполнить следующие действия:
1. Снять заливку области построения;
2. При необходимости изменить масштаб шкалы осей диаграммы.
Результат работы алгоритмов 3.3 – 3.4 для демонстрационного примера представлен на рис.3.4.
Рис. 3.4. График сглаживания по прямой и параболе ряда динамики выпуска продукции за 6-ой год
