Решение задачи первичной статистической обработки данных в среде Еxcel

РЕШЕНИЕ ЗАДАЧИ ПЕРВИЧНОЙ СТАТИСТИЧЕСКОЙ ОБРАБОТКИ ДАННЫХ В СРЕДЕ EXCEL

Будущие экономисты помимо серьезной математической подготовки должны обладать практическими навыками использования  математических понятий  и методов. Их  профессиональная деятельность  связана с необходимостью быстрой обработки большого объема различной информации. Для этого  нужно иметь практические навыки в решении  задач  математической статистики. Эти задачи  существенно  отличаются от всех остальных задач  высшей математики. Они характеризуются  громоздкими, часто приближенными  вычислениями. Решение этих  задач  «в ручную»,  даже с использованием калькуляторов,  очень трудоемкий процесс, требующий знания специальных алгоритмов вычислений. Именно поэтому раньше при изучении математической статистики тратилось значительное время на освоение специальных способов вычисления сумм,  на использование  расчетных таблиц и методов контроля каждого шага вычислений. Появление и быстрое развитие вычислительной техники, особенно персональных компьютеров, увеличили масштабы и ускорили темпы внедрения статистических методов анализа данных в практическую   и научно-исследовательскую деятельность. Появление электронных таблиц (табличных процессоров) привело к тому, что статистические методы, ранее доступные лишь узкому кругу математиков,  стали доступны широкому кругу специалистов разных областей. Дальнейшее развитие программного обеспечения привело к созданию большого количества прикладных пакетов по статистике. Но большинство специалистов, столкнувшись с трудностями при их освоении, предпочитают использовать доступный и достаточно простой   для проведения стандартных статистических методов табличный процессор Excel. Именно поэтому выпускники  вузов должны уметь пользоваться  Excel при решении статистических задач.

Со средой Excel студенты знакомятся в курсе информатики в школе и на первом курсе вуза и имеют основные навыки работы (ввод данных, копирование, ввод формул, использование встроенных функций, процедур, использование автовычислений). Во время изучения курса математической статистики они должны научиться использовать Excel при решении всех задач.

Рассмотрим одну из основных задач математической статистики о  первичной статистической обработке  данных и ее решение   с помощью  среды Excel. В статистике, как правило, статистические данные являются результатами наблюдений над некоторой случайной величиной X. Для краткости рассмотрим  случай дискретной  случайной величины X . Исследование непрерывной случайной величины  X аналогично и требует внесения незначительных изменений в алгоритм решения. Статистические данные заданы в виде выборки  . Требуется провести первичную статистическую обработку данных:

• построить вариационный ряд выборки;
• построить статистическое распределение выборки (статистический ряд) ;
• построить полигоны относительных и накопленных частот;
• определить основные выборочные характеристики;
• проверить гипотезу о виде распределения случайной величины с помощью критерия согласия Пирсона.

Данная задача решается с  помощью статистических процедур Анализа данных и статистических функций библиотеки встроенных функций Excel. При написании статьи использовалась версия Microsoft Office Excel 2007.  Если  Анализ данных  не доступен, нужно его предварительно подключить (используя кнопку Office, выбрать с помощью кнопки Параметры  Excel команду Настройки). В случае более ранней версии Excel Анализ данных доступен через меню Сервис. Жирным шрифтом в статье выделены команды меню, названия панелей инструментов и диалоговых окон и их элементов.

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

1.  Ввод данных. В диапазон ячеек А1:АN ввести выборочные значения  .

2.   Построение вариационного ряда.

• Скопировать содержимое ячеек А1:АN в ячейки В1:ВN.
• Упорядочить выборочные значения, используя кнопку сортировки по возрастанию.

 

3. Построение статистического ряда  выборки.

• В ячейки С1:СК ввести k различных выборочных значений.
• В меню Данные выделить строку Анализ данных.
• В открывшемся диалоговом меню выделить процедуру Гистограмма и нажать кнопку OK.
• В поле Входной интервал диалогового окна Гистограмма ввести ссылку на диапазон А1:АN, в котором находятся значения исследуемой выборки.
• В поле Интервал карманов ввести ссылку на диапазон С1:СК, в котором помещены различные выборочные значения.
• Активизировать поле Выходной интервал щелчком мышки. Ввести в это поле ссылку – левая верхняя ячейка, в которую будет введена таблица результатов решений.
• Установить флажок Вывод графика и нажать OK.
• Составить таблицу 1 статистического ряда  по следующему образцу:

различные выборочные значения

частота выборочного значения

относительная частота выборочного значения

накопленная относительная  частота

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

4. Построение полигонов относительных и накопленных частот.

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

 

5. Определение выборочных характеристик.

 

• В меню Данные выделить подменю  Анализ данных.
• В открывшемся окне Анализ данных выделить процедуру Описательная статистика и нажать кнопку OK.
• На экране появится диалоговое окно Описательная статистика. В поле ввода Входной интервал ввести ссылку на диапазон ячеек, содержащий статистические данные А1:АN.
• Установить флажок Итоговая статистика.
• Активизировать поле Выходной интервал щелчком мышки. Ввести в это поле ссылку – левая верхняя ячейка, в которую будет введена таблица результатов решений.

6. Проверка гипотезы о виде распределения случайной величины с помощью критерия согласия Пирсона.

 Заполнить таблицу 2:

различные выборочные  значения

частота выборочного значения

теоретическая вероятность  выборочного значения

теоретическая частота выборочного  значения

 

Первые столбцы заполнить  копированием, а оставшиеся -  заполнить  вычисленными по формулам значениями.

Если проверяется гипотеза о распределении Пуассона, то теоретические вероятности   вычислить с помощью функции ПУАССОН. Здесь выборочное среднее, оно определяется в пункте 5, 0 – параметр, показывающий, что вычисляется вероятность того, что случайная величина, распределенная по закону Пуассона, принимает значение  .

Если проверяется гипотеза о биномиальном распределении случайной величины, то теоретические вероятности   вычислить с помощью функции БИНОМРАСП,

 при этом вероятность  успеха   в одном испытании определить по формуле где - выборочное среднее.

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

Значение

 

является наблюдаемым  значением случайной величины . Число степеней свободы этой случайной величины равно при проверке гипотезы о распределении Пуассона и  , если проверяется гипотеза о биномиальном распределении. Критическое значение случайной величины определить с помощью функции ХИ2ОБР,

где - уровень значимости.

 Полученное наблюдаемое  значение  сравнить с  :

• если  , то гипотеза о виде распределения принимается при уровне значимости ,
• если  , то гипотеза отвергается с уровнем значимости

Пример. Исследуется случайная величина    - число правонарушений в течение одних суток в некотором городе N. Получены данные за первые 150 суток года

 

3    5    4    4    5    8    2    3    1    6    6    1    2    5    5

4    4    4    3    4    5    5    2    2    3    4    3    2    4    4

8   10   1    4    3    3    2    5    7    5    3    6    7    5    6

1    4    6    4    5    4    5    7    6    5    3     5    5    8    7

7    5    5    4    5    3    3    6    3    5    2    2     2    6    2

5    6    8    4    4    8    3    6    4    4    5     5    7    5    5

3    5    4    5    5    4    7    6    9    3    3     5    6    6    3

4    5    2    6    7    5    5    4    2    5    4     2    6    2    7

5    5    8    5    3    5    2    5    3    7    4     6    3    6    0

4    4    4    5    2    7    7    3    1    1    3     6    5    7    6

По предложенному алгоритму  провести первичную статистическую обработку данных.

Решение.

1. Вводим данные в ячейки А1:А150.
2. Копируем введенные данные в ячейки В1:В150 и с помощью кнопки сортировки по возрастанию упорядочиваем их. Получили вариационный ряд.
3. Используя вариационный ряд, находим  различных выборочных значений

1. 1     2     3      4      5      6      7      8      9       10.

Вводим эти числа в  ячейки С1:С11. С помощью пакета Анализ данных в соответствии с правилами пункта 3 алгоритма получаем статистический ряд выборки и его графическое представление (рис. 1).

Рис. 1

Решение этой элементарной задачи  статистики «в ручную»  требует значительного времени.  Построенная гистограмма (на самом деле это просто график таблично заданной функции) позволяет сделать предположение о виде распределения случайной величины X. На рис.2 представлена заполненная в соответствии с алгоритмом таблица 1. В третьем столбце представлены относительные, а в четвертом – накопленные относительные частоты выборочных значений.

Рис. 2. Статистический ряд.

 

4. Скопировав первый и третий столбцы таблицы статистического ряда, выделим их. Используя меню Вставка, применяем к выделенным числам средство диаграммы Точечная. Полученный  график (полигон 1)  на рис.3 есть полигон относительных частот.

 

Рис. 3

Скопировав первый и четвертый  столбцы таблицы статистического  ряда, выделим их. Используя меню Вставка, применяем к выделенным числам средство диаграммы Точечная. Полученный  график (полигон 2) на рис.4 есть полигон накопленных относительных частот.

 

Рис.4

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

 

6. Проверим гипотезу о распределении случайной величины по закону Пуассона. В качестве точечной оценки параметра распределения выбираем выборочное среднее  . Заполним таблицу 2 алгоритма (рис.6).

                    

Рис. 5.  Выборочные характеристики                                           Рис.6. Таблица 2.

Наблюдаемое значение случайной  величины . Оно получено суммированием чисел последнего столбца таблицы 2. Критическое значение

ХИ2ОБР,

где Так как   , то гипотеза о распределении по закону Пуассона при уровне значимости принимается. Задача решена.

 

Список литературы:

1. Вадзинский Р. Статистические вычисления в среде Excel. Библиотека пользователя.- СПб.: Питер, 2008.-608с.