Как сделать кумуляту в экселе

Использование Excel для расчета статистических характеристик случайной величины

Разделы: Математика

– что называется случайной величиной? (Случайной величиной называют переменную величину, которая в зависимости от исхода испытания принимает одно значение из множества возможных значений.)

– Какие виды случайных величин мы знаем? (Дискретные, непрерывные.)

– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).

– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).

– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).

Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):

1. Занести данные в EXCEL, каждое число в отдельную ячейку.

23252425302430262826
32333131253325293028
23302924333030282625
26292729262827262928
29302730283228263026
31273027332826303129
27303029272628312928
33273033263134283222
29302729342932292930
29293629293423282428

Как сделать кумуляту в экселе

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Как сделать кумуляту в экселе

Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.

Используя тот же путь вычисляем медиану.

Вставка – Функция – Статистические – Медиана.

Как сделать кумуляту в экселе

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Как сделать кумуляту в экселе

Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.

Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.

Вставка – Функция – Статистические – МАКС.

Как сделать кумуляту в экселе

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Как сделать кумуляту в экселе

Нажимаем клавишу ОК. Получили наибольшее значение = 36.

Вставка – Функция – Статистические – МИН.

Как сделать кумуляту в экселе

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Как сделать кумуляту в экселе

Нажимаем клавишу ОК. Получили наименьшее значение = 22.

36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.

Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.

xi222324252627282930313233343536
ni

Чтобы сосчитать частоту каждого значения воспользуемся

Вставка – Функция – Статистические – СЧЕТЕСЛИ.

В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22

Как сделать кумуляту в экселе

Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.

xi222324252627282930313233343536
ni1345119131816646301

Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)

Как сделать кумуляту в экселе

Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.

Как сделать кумуляту в экселе

Как сделать кумуляту в экселе

Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).

Диаграмма – Стандартные – Круговая.

Как сделать кумуляту в экселе

Диаграмма – Стандартные – Гистограмма.

Как сделать кумуляту в экселе

4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.

Источник

Выполнение задания в ППП MS Excel

Значения каждого из трех признаков должны быть упорядочены, что реализуется с помощью сортировки (отдельно каждого из признаков): выбираем вкладку ДАННЫЕСОРТИРОВКА (традиционно по возрастанию).

Для выполнения структурной равноинтервальной группировки сначала определяем величину интервала по формуле:

Как сделать кумуляту в экселе

где R – размах вариации, Как сделать кумуляту в экселе;

Как сделать кумуляту в экселе— максимальное значение группировочного признака;

Как сделать кумуляту в экселе— минимальное значение группировочного признака;

Число групп определяется следующими факторами: задачами исследования, основанием группировки, численностью совокупности, степенью вариации (изменчивости) признака.

Как ориентир для определения количества групп может быть использована формула Стерджесса:

Как сделать кумуляту в экселе

где N – число единиц совокупности.

Если признак варьирует незначительно, может быть взято и меньшее число групп. И наоборот, если вариация велика, для более подробного ее описания лучше выбрать большее число групп.

Далее определяются границы каждого интервала по следующей схеме:

№ интервалаГраницы интервала
нижняяверхняя
Как сделать кумуляту в экселе Как сделать кумуляту в экселе
Как сделать кумуляту в экселе Как сделать кумуляту в экселе
k Как сделать кумуляту в экселе Как сделать кумуляту в экселе

Например, пусть статистическая совокупность состоит из 40 туристических компаний, показатели выручки которых варьируют от 50 млн. у.е./год до 650 млн. у.е./год, что является, соответственно, минимальным и максимальным значениями признака. Тогда по формуле Стерджесса получаем: Как сделать кумуляту в экселе. Величина интервала для построения равноинтервальной группировки определяется следующим образом: Как сделать кумуляту в экселе(млн.у.е.). Таким образом, совокупность компаний будет разделена по показателю выручки на шесть равных групп: 141, 166, 302, 435, 481, 561 (млн. у.е./год).

После определения границ интервалов рассчитываются частоты, для чего используется функция ЧАСТОТА(вводится как формула массива, т.е. комбинацией клавиш CTRL + SHIFT + ВВОД). Выделяем столбец, в который должны поместиться частоты, соответствующие каждому интервалу. Вызываем функцию (из категории «Статистические») :

= ЧАСТОТА(массив_данных;массив_интервалов)

где массив_данных – это столбец исходных значений признака, для которых вычисляются частоты;

массив_интервалов – это столбец верхних границ интервалов с 1-го по k-1 –й (т.е. без последнего). Функция ЧАСТОТА предполагает формирование верхних границ по принципу «включительно», а нижних – «исключительно».

Сумма частот должна быть равна объему совокупности:

Как сделать кумуляту в экселе

где Как сделать кумуляту в экселе— число наблюдений (частота) в i – ой группе.

Далее оценивается относительная структура совокупности через расчет частостей:

Как сделать кумуляту в экселе

Как сделать кумуляту в экселе

где Как сделать кумуляту в экселе— частость в i – ой группе, выраженная в долях единицы или в процентах к итогу.

В результате получаем структурную равноинтервальную группировку.

Группировки с равными интервалами предпочтительнее, но характер изменения большинства социально-экономических явлений не отвечает требованиям, предъявляемым к равноинтервальной группировке.

Если в результате построения равноинтервальной группировки большая часть совокупности попала в один-два смежных интервала, а остальные содержат незначительное число наблюдений, это свидетельствует о том, что исследуемый признак варьирует неравномерно. В данном случае может быть использован «прогрессивный» подход к определению границ интервалов.

В этом случае величина интервалов определяется формулами:

Как сделать кумуляту в экселе

Как сделать кумуляту в экселе

где Как сделать кумуляту в экселе— величина i+1 – го интервала;

Как сделать кумуляту в экселе– константа арифметической прогрессии, для возрастающих интервалов Как сделать кумуляту в экселе, для убывающих интервалов Как сделать кумуляту в экселе;

q – константа геометрической прогрессии, для возрастающих интервалов Как сделать кумуляту в экселе, для убывающих интервалов Как сделать кумуляту в экселе

Описанные выше технические способы определения величины интервалов не гарантируют, что не появятся группы малочисленные или вообще «пустые», в которые не попало ни одно наблюдение. Если это произошло, необходимо изменить число групп и/или величины интервалов, так как подобная группировка является некорректной.

Для обеспечения статистической устойчивости показателей, исчисляемых для отдельных групп, может использоваться равнонаполненная группировка, в которой число наблюдений в каждой группе примерно одинаковое и определяется по формуле:

Как сделать кумуляту в экселе.

Если полученное n не целое и/или в совокупности есть повторяющиеся значения признака, то число наблюдений в каждой группе может различаться. При этом надо стремиться к тому, чтобы эти различия были незначительны.

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

Границы групп могут определяться и произвольно, когда ни один из вышеописанных методов не дал хороших результатов.

В результате на основе итоговой группировки формируется вариационный ряд распределения (см. табл. 1).

Таблица 1. Ряд распределения выручки туристических компаний, млн. у.е./год

Границы интервалаЧастотаЧастостьПлотность абсол.Плотность отснос.
нижняяверхняя
0,3000,120,00300
0,2500,100,00250
0,2000,080,00200
0,1250,050,00125
0,1000,040,00100
0,0250,010,00025
Итого

Для неравноинтервального вариационного ряда распределения сравнение частот по группам неправомерно. В данном случае необходимо избавиться от влияния величины интервала путем перехода от частот/частостей к абсолютной/относительной плотности распределения:

Как сделать кумуляту в экселеКак сделать кумуляту в экселе

где Как сделать кумуляту в экселе— абсолютная плотность распределения в i – ой группе;

Как сделать кумуляту в экселе— относительная плотность распределения в i – ой группе;

Как сделать кумуляту в экселе— величина i – го интервала.

Далее строится кумулятивный ряд распределения, для чего рассчитываются накопленные частоты/частости к концу каждого интервала:

Как сделать кумуляту в экселеКак сделать кумуляту в экселе

где Как сделать кумуляту в экселе/ Как сделать кумуляту в экселе— накопленная частота/частость к концу i – ой группы.

Построение гистограммы и кумуляты выполняется с указанием названия графика и каждой оси. Для кумуляты в таблице рассчитываем накопленные частоты/частости (см. табл.2):

Как сделать кумуляту в экселеКак сделать кумуляту в экселе

где Как сделать кумуляту в экселе/ Как сделать кумуляту в экселе— накопленная частота/частость к концу i – ой группы.

Таблица 2. Кумулятивный ряд распределения выручки туристических компаний, млн. у.е./год

Границы интервалаЧастотаЧастостьНакопленная частотаНакопленная частость
нижняяверхняя
0,300,300
0,250,550
0,200,750
0,130,875
0,100,975
0,03
Итого

Гистограмма– графическое изображение интервального вариационного ряда распределения, дающее представление о характере изменения его частот (рис. 1).

Как сделать кумуляту в экселе

Рис. 1. Гистограмма распределения выручки туристических компаний

Как сделать кумуляту в экселе

Рис. 2. Кумулята распределения выручки туристических компаний

Кумулята – графическое изображение кумулятивной кривой, дающее представление о характере изменения накопленных частот/частостей (рис. 2).

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

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *