Лабораторная работа №1
Автоматизированный априорный анализ
статистической совокупности
в среде MS Excel
Задание 1. Выявление и удаление из выборки аномальных единиц наблюдения
Задание 2. Оценка описательных статистических параметров совокупности
Задание 3. Построение и графическое изображение интервального вариационного ряда распределения единиц совокупности по признаку Среднегодовая стоимость основных производственных фондов
Порядок выполнения лабораторной работы
1. Подготовительный этап
На данном этапе студент должен проделать следующие обязательные действия, связанные с организацией индивидуальной рабочей среды:
и подготовить персональную папку с рабочим и отчетным файлами;
сформировать индивидуальный вариант исходных данных и записать его в отчетный файл;
Подготовка персональной папки студента с рабочим и отчетным файлами
Для выполнения расчетов обобщающих показателей и подготовки отчета по лабораторной работе студент формирует персональную папку с именем ФИО, содержащую два файла: расчетный с именем Лаб. хls и отчетный с именем Отчет.doc.
Для этого необходимо выполнить следующие действия:
а) создание персональной папки и рабочего файла:
Загрузить файл с исходными данными и макетами таблиц по следующему алгоритму:
На рабочем столе активизировать Мой компьютер;
В диалоговом окне выбрать файл
Datadisk на “primary” (E:)\Преподаватели\Кафедра статистики\ Априорный анализ\stat_lab.xls
Сохранить файл с исходными данными в качестве рабочего файла по алгоритму:
Файл=>Сохранить как…;
В диалоговом окне Сохранение документа выбрать путь: Datadisk на “primary” (Е:)\Статистика\ Работы студентов\<Специальности>\ <Номер группы>;
3. Выбрать пиктограмму «Создать папку» и в появившимся диалоговом окне записать в поле «Имя» свою фамилию.
4. Сохранить файл в созданной папке под именем Лаб. хls.
б) создание отчетного файла:
Загрузить файл с форматом отчета из директории
Datadisk на “primary” (E:)\Преподаватели\Кафедра статистики\ Априорный анализ Формат отчета.doc
Сохранить файл по алгоритму:
Файл=>Сохранить как…;
В диалоговом окне Сохранение документа выбрать путь:
Datadisk на “primary” (Е:)\Статистика\ Работы студентов\ <Специальности>\ <Номер группы>\<Ф.И.О.>;
Сохранить файл в указанной папке под именем Отчет.doc
Формирование индивидуальных исходных данных
Номер варианта исходных данных соответствует номеру компьютера, на котором работает студент.
2.1. Для того, чтобы сформировать индивидуальные исходные данные, необходимо ввести номер варианта в ячейку E2 созданного рабочего файла Лаб. хls, в результате чего Excel сформирует исходные данные варианта.
Расположение макетов результативных таблиц в рабочем файле на Листе 1 персональной папки студента


2. Этап выполнения статистических расчетов
Задание 1
Выявление и удаление из выборки аномальных единиц наблюдения
Первичные данные выборочной совокупности могут содержать аномальные значения изучаемых признаков (см. п. 2 раздела II – «Теоретические основы лабораторной работы»). Задание 1 заключается в их выявлении и исключении из дальнейшего рассмотрения с целью обеспечения устойчивости данных статистического анализа.
Алгоритм выполнения Задания 1
Этап 1. Построение диаграммы рассеяния изучаемых признаков
Выделить мышью оба столбца исходных данных в диапазоне B4:C35;
Вставка=>Диаграмма=>Точечная=>Готово.
В результате выполнения этих действий на рабочем листе Excel появится диаграмма рассеяния.
EMBED PBrush
Рис. 1. Аномальные значения признаковна диаграмме рассеяния.
Этап 2. Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из первичных данных
Найти на графике точку, соответствующую аномальному наблюдению. Если таких точек нет, то перейти к действию 7, если есть - к действиям 2 - 6.
Подвести курсор к точке на диаграмме рассеяния, соответствующей аномальному наблюдению. После непродолжительного времени возле точки автоматически появится надпись, содержащая значения признаков (X,Y). этого наблюдения.
Для демонстрационного примера такая надпись выглядит следующим образом:
EMBED PBrush
В исходных данных визуально найти в табл.1 строку, соответствующую выявленной аномальной единице наблюдения (предприятию) и скопировать её в табл.2.;
Выделить мышью всю адресную строку (вместе с ее номером!) с данными, подлежащими удалению.
Для демонстрационного примера это адресная строка с номером 34, содержащая значения 31, 330 и 53:
Правка=>Удалить.
Выполнять действия 1-5 до полного удаления всех аномальных наблюдений.
Переместить диаграмму рассеяния в область ячеек, начиная с ячейки F4.
Для демонстрационного примера табл.2 имеет следующий вид.
Задание 2
Оценка описательных статистических параметров
совокупности
Обобщающие статистические показатели совокупности исчисляются на основе анализа вариационных рядов распределения (см. п.3 раздела II – «Теоретические основы лабораторной работы»). Однако пакет Excel позволяет рассчитать многие из этих показателей непосредственно по первичным данным наблюдения, используя инструмент Описательная статистика надстройки Пакет анализа, а также статистические функции инструмента Мастер функций.
Выполнение Задания 2 заключается в автоматизированном решении двух статистических задач:
Расчет описательных показателей выборочной и генеральной совокупностей по несгруппированным выборочным данным с использованием инструментов Описательная статистика и Мастер функций.
Оценка средней и предельной ошибок выборки для средней величины признака, а также границ, в которых эта средняя будет находиться в генеральной совокупности при заданных уровнях надежности.
Алгоритмы выполнения Задания 2
Выполнение задания включает три этапа:
1.Расчет описательных параметров выборочной и генеральной совокупностей с использованием инструмента Описательная статистика.
2.Оценка предельных ошибок выборки для различных уровней надежности в режиме Описательная статистика.
3.Расчет описательных параметров выборочной совокупности с использованием инструмента Мастер функций.
Этап 1. Расчет описательных параметров выборочной и генеральной совокупно-
стей с использованием инструмента ОПИСАТЕЛЬНАЯ СТАТИСТИКА
Алгоритм 1.1. Расчет описательных статистик
1.Сервис=>Анализ данных=>Описательная статистика=>OK;
2.Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции (B4:С33);
3.Группирование =>по столбцам;
4.Итоговая статистика - Активизировать;
5.Уровень надежности - Активизировать;
6.Уровень надежности <= 95,4 (или 95.4);
7.Выходной интервал <= адрес ячейки заголовка первого столбца табл.3 (А46);
8.OK;
9.При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
В результате указанных действий Excel осуществляет вывод таблицы описательных статистик в заданный диапазон рабочего файла (для демонстрационного примера это таб.3-ДП).
Этап 2. Оценка предельных ошибок выборки для различных уровней надежности в режиме Описательная статистика.
Алгоритм 2.1. Расчет предельной ошибки выборки при P=0,683
Сервис=>Анализ данных=>Описательная статистика=>OK;
Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции (В4:С33);
Итоговая статистика – Не активизировать;
Уровень надежности – Активизировать;
Уровень надежности<= 68,3 (или 68.3);
Выходной интервал <= адрес ячейки, выделенной для предельной ошибки выборки при P=0,683 (А67);
OK;
При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
Алгоритм 2.2. Расчет предельной ошибки выборки при P=0,997
Сервис=>Анализ данных=>Описательная статистика=>OK;
Входной интервал<= диапазон ячеек таблицы, выделенный для значений признаков Стоимость основных фондов и Выпуск продукции после удаления аномальных значений (В4:С33);
Итоговая статистика – НЕ активизировать;
Уровень надежности – Активизировать;
Уровень надежности <= 99,7 (или 99.7);
Выходной интервал <= адрес ячейки, выделенный для предельной ошибки выборки при P=0,997 (А75);
OK;
При появлении окна с сообщением "Выходной интервал накладывается на имеющиеся данные" =>ОК.
В результате работы алгоритмов 2.1 и 2.2 Excel выводит в соответствующие ячейки табл.4 рабочего файла значения предельных ошибок выборки при P=0,683 и P=0,997 (для демонстрационного примера табл.4а-ДП и табл.4б-ДП).
Этап 3. Расчет описательных параметров выборочной совокупности с использованием инструмента Мастер функций
Алгоритм 3.1. Расчет значений выборочных параметров ?n, ?2n, EMBED Equation.3 .
Вычисление показателей для обоих признаков осуществляется с использованием соответствующих статистических функций СТАНДОТКЛОНП, ДИСПР, СРОТКЛ инструмента Мастер функций.
В макете табл.5. приведены их имена вместе с аргументами. Для выполнения вычислений следует ввести знак равенства «=» перед именами функций.
Алгоритм 3.2. Расчет коэффициентов вариации EMBED Equation.3 и асимметрии Пирсона AsП для обоих признаков.
В макете табл.5. приведены расчетные формулы коэффициента вариации
EMBED Equation.3 и коэффициента асимметрии Пирсона EMBED Equation.3 .
Для выполнения вычислений следует ввести знак равенства «=» перед формулами.
В результате работы алгоритмов 3.1 - 3.2 Excel осуществляет вывод значений выборочных параметров ?n, ?2n, EMBED Equation.3 , EMBED Equation.3 и Аsn в соответствующие ячейки рабочего листа (для демонстрационного примера табл.5-ДП).
Задание 3
Построение и графическое изображение интервального вариационного ряда распределения единиц совокупности по признаку
Среднегодовая стоимость основных производственных фондов
Для того чтобы выявить структуру совокупности и тип закономерности распределения ее единиц по варьирующему признаку, строят и анализируют интервальный вариационный ряд распределения и его гистограмму (см. п. 3 раздела II – Теоретические основы лабораторной работы).
Выполнение Задания 3 заключается в решении двух статистических задач:
Построение интервального ряда распределения единиц выборочной совокупности по признаку Среднегодовая стоимость основных производственных фондов.
Построение гистограммы и кумуляты сформированного интервального ряда.
Алгоритмы выполнения Задания 3
Выполнение задания осуществляется в три этапа:
Построение промежуточной таблицы.
Генерация выходной таблицы и графиков.
Приведение выходной таблицы и диаграммы к виду, принятому в статистике.
Этап 1. Построение промежуточной таблицы.
Алгоритм 1.1. Расчет нижних границ интервалов
Сервис=>Анализ данных=>Гистограмма=>ОК;
Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);
!!! Внимание. Здесь возможен ошибочный захват мышью столбца второго признака. Необходимо проконтролировать правильность задания входных данных!
Интервал карманов оставить незаполненным;
Выходной интервал <= адрес заголовка первого столбца первичной промежуточной табл.6 (А90).
OK;
Алгоритм 1.2. Переход от нижних границ к верхним
Выделить курсором верхнюю левую ячейку табл.6 (A91) и нажать клавишу [Delete];
Ввести в последнюю ячейку табл.6 (A96) вместо "Еще" значение хmax первого признака из табл.3-Описательные статистики (Термин "Максимум").
Для демонстрационного примера построение промежуточной таблицы (алгоритмы 1.1 – 1.2) приведено на рис.11:
Рис.11. Схема перехода от нижних границ интервалов к верхнимЭтап 2. Генерация выходной таблицы и графиков
Алгоритм 2.1. Построение выходной таблицы, столбиковой диаграммы и кумуляты.
Сервис=>Анализ данных=>Гистограмма=>ОК;
Входной интервал<= диапазон ячеек, выделенный для столбца значений первого признака (В4:В33);
!!!Внимание! Здесь возможен ошибочный захват мышью столбца второго признака. Необходимо проконтролировать правильность задания входных данных!
Интервал карманов <= диапазон карманов итоговой промежуточной табл.6 с верхними границами (А92:А96);
Выходной интервал <= адрес заголовка первого столбца выходной табл.7 (А101);
Интегральный процент - Активизировать;
Вывод графика - Активизировать;
ОК;
При появлении сообщения о наложении данных - ОК.
Для демонстрационного примера выходная таблица имеет следующий вид:
Столбиковая диаграмма и кумулята приведены ниже:
EMBED Excel.Chart.8 \s
Этап 3. Приведение выходной таблицы и диаграммы к виду, принятому в статистике.
Алгоритм 3.1. Преобразование выходной таблицы в результативную.
Заменить названия столбцов выходной табл.7 следующим образом:
Строки первого столбца привести к виду «нижняя граница интервала - верхняя граница интервала», учитывая совпадение верхних границ предыдущего интервала с нижней границей последующего интервала;
Строку 107, содержащую термин «Еще», выделить мышью и очистить, нажав клавишу [Delete];
Добавить и заполнить итоговую строку 108 (ячейки А108:В108).
Для демонстрационного примера Excel-формат результативной таблицы выглядит следующим образом.
Алгоритм 3.2. Преобразование столбиковой диаграммы в гистограмму.
Осуществив «захват мышью», переместить график, расположив его вслед за табл.7.
Исключить зазоры, выполнив следующие действия:
2.1. Нажать правую кнопку мыши на одном из столбиков диаграммы.;
2.2. Формат рядов данных=>Параметры;
2.3. Ширина зазора<= 0;
2.4. ОК;
Используя "захват мышью" за угол поля графика, установить визуально соотношение ширины и высоты фигуры гистограммы в пропорции 1 : 0,6.
!!! Внимание! Здесь возможна ошибочная установка указанной пропорции для размеров поля графика, а не для самой геометрической фигуры гистограммы. Необходимо проконтролировать правильность установки пропорции ширины и высоты фигуры гистограммы.
Для демонстрационного примера гистограмма и кумулята выглядят следующим образом
EMBED Excel.Chart.8 \s