Современные информационные технологии

/2. Вычислительная техника и программирование

К.т.н. Тощигин И.К.

Майкопский государственный технологический университет, Россия

Систематизация и статистическая обработка одномерной выборки средствами электронной таблицы

Электронная таблица с возможностями создания базы данных, организации математических операций с применением многих встроенных функций, процедур выполнения анализа и вывода стандартной графики является мощным и удобным средством статистической обработки данных.

1. Подготовка данных и их первичная обработка

Приведенные в табл. 1 данные отражают ежедневные стоимости произведенных предприятием металлоконструкций. Смысл обработки такой выборки заключается в оценке уровня выполнения нормы и статистических оснований для постановки вопроса о ее пересмотре. Данные относятся к близким условиям работ по категории техники, составу персонала и т.д. Выборка колеблется в широки пределах, т.е. ведет себя как случайная величина. Поскольку она насчитывает 168 вариант, она является репрезентативной.

Таблица 1

56	290	470	151	118	76	50	196	200	176	222	235	143	185
216	203	233	91	140	89	104	145	120	136	178	230	176	188
402	139	51	182	350	193	344	331	188	187	301	309	223	141
118	99	117	135	92	119	320	243	140	173	250	156	244	179
53	196	54	101	176	55	102	58	88	93	118	144	116	99
121	134	97	59	60	197	60	61	165	63	184	65	66	66
161	161	199	160	89	141	100	122	133	141	123	104	159	154
70	71	72	186	194	72	72	169	75	77	78	80	149	81
137	117	117	105	133	116	106	124	81	142	83	148	137	85
147	86	147	101	119	138	87	103	138	150	90	124	107	126
112	106	132	115	105	125	131	115	127	107	114	130	105	114
126	127	108	106	125	129	126	107	128	112	127	115	111	110

Поскольку выборка является одномерной, ввод данных в таблицу следует выполнять в один столбец, в противном случае она будет воспринята как двумерная.

2. Систематизация и визуализация данных

Систематизация выборки в Excel, т.е. подсчет числа вариант в интервалах (карманах), предполагает задание их границ:

- явно (перечислением в отдельном столбце);

- автоматически (с помощью встроенного алгоритма). При явном задании интервалов с помощью функций МИН и МАКС определяется размах выборки [1]. Курсор устанавливается в свободной клетке, и в списке функций Статистические выбирается МАКС. В ее диалоговом окне в поле Число1 указываются исходные данные – их диапазон в столбце. Аналогично функцией МИН возвращается минимальное значение выборки.

Размах находится как разность максимального и минимального значений и разбивается на 10 – 12 карманов, например, на возрастающую последовательность граничных значений: 50, 90, …, 490.

Задать границы интервалов можно автоматически с помощью процедуры Гистограмма в пакете Анализ данных меню Сервис. В диалоговом окне следует ввести в поле Входной интервал диапазон данных. Поле Интервал карманов заполняется в случае явного задания интервала или остается пустым. Флажок метки включается, если первая строка или первый столбец входного интервала содержит заголовки. В поле Выходной интервал вводится ссылка на левую верхнюю ячейку выходного диапазона, размер которого определяется автоматически. Переключатель Новый рабочий лист устанавливается для вставки выходного диапазона на Новом рабочем листе с ячейки А1. При установки переключателя Новая рабочая книга выходной диапазон выводится в новой книге на Листе 1, начиная с ячейки А1. Флажок Парето предназначен для вывода диаграммы с убыванием частот. Флажок Интегральный процент устанавливается для вывода интегральных процентных отношений и включения их в накопительную кривую. Флажок Вывод графика служит для вывода на экран гистограммы и накопительной кривой.

Полученная диаграмма имеет один максимум с монотонным уменьшением частот (спусками). Это позволяет считать совокупность однородной и получить описательную статистику, которая имеет смысл для однородной статистической совокупности.

3. Статистическая обработка стоимостных объемов производства в Excel

1. Ввести в таблицу все варианты, представленные в табл. 1.1, располагая их в столбец начиная с ячейки А1.

2. В меню Сервис вызвать пакет Анализ данных, в котором вызвать процедуру Гистограмма.

3. В диалоговом окне установить следующие параметры:

- в текстовое окно Входной интервал ввести интервал со значениями вариант;

- текстовое окно Интервал карманов оставить пустым;

- флажок Метки не устанавливать;

- в текстовое окно Выходной интервал ввести ссылку на верхнюю левую ячейку диапазона с результатами обработки данных, перед этим установив переключатель в положение Выходной интервал;

- включить флажки Интегральный процент и Вывод графика.

4. Нажать кнопку ОК.

5. Проанализировать полученные результаты.

4. Вывод описательной статистики

Процедура вызывается из меню Сервис, где выбирается пакет Анализ данных и команда Описательная статистика.

В диалоговом окне в поле Входной интервал вводится адрес диапазона данных. Переключатель Группирование ставится по столбцам или по строкам в зависимости от расположения Входного диапазона. Флажок Метки в первой строке (или столбце) устанавливается, если в первом столбце (строке) содержатся названия. Установка флажка Уровень надежности отбрасывает данные с вероятностью меньше 5%. Флажки К-й наибольший (наименьший) позволяют включить в выходную таблицу строку k-го наибольшего (наименьшего значения). При k=1 в таблицу включается наибольшее (наименьшее) значение. В поле Выходной интервал набирается адрес верхней левой ячейки выходного диапазона. Из двух столбцов: левого – с метками статистик и правого – с их значениями. Флажок Итоговая статистика используется для вывода числовых значений статистических параметров.

Таблица 1.2

Статистика	Значение	Комментарий
Среднее	139,738	Оценка среднего арифметического
Стандартная ошибка	5,21927	Стандарт погрешности оценки среднего арифметического
Медиана	125,5	«Срединное» значение вариант упорядоченной выборки
Мода	118	Наиболее часто встречающееся значение
Стандартное отклонение	67,6495	Оценка среднеквадратичного отклонения вариант
Дисперсия выборки	4576,46	Квадрат стандартного отклонения
Эксцесс	4,82795	Островершинность распределения
Асимметричность	1,82874	Скошенность распределения
Интервал	420	Размах
Минимум	50	Наименьшая варианта
Максимум	470	Наибольшая варианта
Сумма	23476	Сумма вариант
Счет	168	Количество вариант

Оценка, полученная по гипотезе о нормальном распрелделении – 139,328 ± 4,931.

Следует обратить внимание, что значения эксцесса и асимметричности, что говорит не несоответствии распределения случайной величины нормальному. В этом случае рекомендуется проводить анализ на основе логарифмически-нормального распределения с последующим потенцированием результатов.

6. Расчет и анализ логнормального распределения

После анализа значений эксцесса и асимметрии по результатам Описательной статистики перейти к расчету и анализу логарифмически нормального распределения.

1. В поле В1 ввести формулу =LN(A1).

2. Скопировать полученную формулу в ячейки В2:В168.

3. Установить курсор в ячейку D32.

4. Провести Анализ данных с помощью меню Гистограмма.

5. Установить курсор в ячейку D47. Провести Анализ данных с помощью меню Описательная статистика.

6. Установить курсор в ячейку F49, вызвать функцию ЕХР() и получить уточненное значение математического ожидания на основе значения ячейки Е49.

В результате мы получим следующие значения статистического среднего, эксцесса и асимметричности:

Сравнение эксцесса
Нормальное	Логнормальное
4,923502108	0,284173965
Сравнение ассиметричности
1,855698086	0,262093986
Статистического среднего
139,1428571	126, 2581782

Литература

1. Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel. СПб: «Питер», 2004.

56	290	470	151	118	76	50	196	200	176	222	235	143	185
216	203	233	91	140	89	104	145	120	136	178	230	176	188
402	139	51	182	350	193	344	331	188	187	301	309	223	141
118	99	117	135	92	119	320	243	140	173	250	156	244	179
53	196	54	101	176	55	102	58	88	93	118	144	116	99
121	134	97	59	60	197	60	61	165	63	184	65	66	66
161	161	199	160	89	141	100	122	133	141	123	104	159	154
70	71	72	186	194	72	72	169	75	77	78	80	149	81
137	117	117	105	133	116	106	124	81	142	83	148	137	85
147	86	147	101	119	138	87	103	138	150	90	124	107	126
112	106	132	115	105	125	131	115	127	107	114	130	105	114
126	127	108	106	125	129	126	107	128	112	127	115	111	110

56	290	470	151	118	76	50	196	200	176	222	235	143	185
216	203	233	91	140	89	104	145	120	136	178	230	176	188
402	139	51	182	350	193	344	331	188	187	301	309	223	141
118	99	117	135	92	119	320	243	140	173	250	156	244	179
53	196	54	101	176	55	102	58	88	93	118	144	116	99
121	134	97	59	60	197	60	61	165	63	184	65	66	66
161	161	199	160	89	141	100	122	133	141	123	104	159	154
70	71	72	186	194	72	72	169	75	77	78	80	149	81
137	117	117	105	133	116	106	124	81	142	83	148	137	85
147	86	147	101	119	138	87	103	138	150	90	124	107	126
112	106	132	115	105	125	131	115	127	107	114	130	105	114
126	127	108	106	125	129	126	107	128	112	127	115	111	110

56	290	470	151	118	76	50	196	200	176	222	235	143	185
216	203	233	91	140	89	104	145	120	136	178	230	176	188
402	139	51	182	350	193	344	331	188	187	301	309	223	141
118	99	117	135	92	119	320	243	140	173	250	156	244	179
53	196	54	101	176	55	102	58	88	93	118	144	116	99
121	134	97	59	60	197	60	61	165	63	184	65	66	66
161	161	199	160	89	141	100	122	133	141	123	104	159	154
70	71	72	186	194	72	72	169	75	77	78	80	149	81
137	117	117	105	133	116	106	124	81	142	83	148	137	85
147	86	147	101	119	138	87	103	138	150	90	124	107	126
112	106	132	115	105	125	131	115	127	107	114	130	105	114
126	127	108	106	125	129	126	107	128	112	127	115	111	110