Как построить кумулятивную кривую в excel

Как создать кумулятивную диаграмму суммы в Excel (с примером)

В этом руководстве представлен пошаговый пример того, как создать следующую сводную диаграмму в Excel:

Шаг 1: введите данные

Во-первых, давайте создадим следующий набор данных, который показывает общий объем продаж некоторого товара в течение каждого месяца в году:

Шаг 2: Рассчитайте совокупную сумму

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

Мы можем ввести эту формулу в ячейку C2 , а затем скопировать и вставить ее в каждую оставшуюся ячейку в столбце C:

Шаг 3: Создайте гистограмму со средней линией

Затем выделите диапазон ячеек A1:C13 , затем нажмите вкладку « Вставка » на верхней ленте, затем нажмите « Сгруппированный столбец » в группе « Диаграммы ».

Будет создана следующая диаграмма:

Затем щелкните правой кнопкой мыши в любом месте диаграммы и выберите « Изменить тип диаграммы» :

В появившемся новом окне нажмите Combo , а затем нажмите OK :

Диаграмма будет преобразована в гистограмму с линией:

Синие столбцы представляют продажи за каждый месяц, а оранжевая линия — совокупные продажи.

Шаг 4. Настройте диаграмму (необязательно)

Не стесняйтесь добавлять заголовок, настраивать цвета, настраивать стиль линий и настраивать ширину полос, чтобы сделать график более эстетичным:

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как в excel построить кумуляту

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

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

Полигон чаще всего используют для изображения дискретных рядов. Для построения полигона в прямоугольной системе координат на оси абсцисс в произвольно выбранном масштабе откладывают значения аргумента, т. е. варианты, а на оси ординат также в произвольно выбранном масштабе — значения частот или относительных частот. Масштаб выбирают такой, чтобы была обеспечена необходимая наглядность, и чтобы рисунок имел желательный размер. Далее в этой системе координат строят точки, координатами которых являются пары соответствующих чисел из вариационного ряда. Полученные точки последовательно соединяют отрезками прямой. Крайнюю "левую" точку соединяют с точкой оси абсцисс, абсцисса которой находится слева от рассматриваемой точки на таком же расстоянии, как абсцисса ближайшей справа точки. Аналогично крайнюю "правую" точку также соединяют с точкой оси абсцисс.

Кумулята служит для графического изображения кумулятивного вариационного ряда. Для ее построения на оси абсцисс откладывают значения аргумента, а на оси ординат — накопленные частоты или накопленные относительные частоты. Масштаб на каждой оси выбирают произвольно. Далее строят точки, абсциссы которых равны вариантам (в случае дискретных рядов) или верхним границам интервалов (в случае интервальных рядов), а ординаты — соответствующим частотам (накопленным частотам). Эти точки соединяют отрезками прямой. Полученная ломаная и является кумулятой.

МИНОБРНАУКИ РОССИИ

Федеральное государственное бюджетное образовательное

Учреждение высшего профессионального образования

«Юго-Западный государственный университет»

Кафедра финансов и кредита

Лабораторная работа №1

Методы группировки статистических данных

студент 1 курса

группы ЭБ-21 Гревцева Наталья

к.э.н., ст. преподаватель Обухова Анна Сергеевна

Курск 2013

Выборочный метод.

Статистическое распределение выборки

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

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

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

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

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

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

При бесповоротном отборе элемент в выборку не возвращается.

Число единиц (элементов) статистической совокупности называется ее объемом. Объем генеральной совокупности обозначается N, а объем выборочной совокупности n.

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

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

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

Вариационный ряд

Элементами выборки < , …, > являются числовые значения, называемые вариантами, которые могут быть дискретными, т.е. изолированными (например, целыми числами), или могут принимать значения из некоторого интервала (a,b). Другими словами, выборка может быть частью генеральной совокупности, которая соответствует дискретной или непрерывной случайной величине.

Вариационный ряд получается из выборки упорядочением по возрастанию (или убыванию) и подсчетом частоты каждого значения. Если выборка соответствует дискретной случайной величине, то вариационный ряд представляет собой таблицу, которая ставит в соответствие каждому значению его частоту . Такой ряд носит название дискретный вариационный ряд.

Например, на основе наблюдения за ростом растения получены n=50 значений числа почек на единицу длины ветки (пример 3.1, табл.3.2). Понятно, что здесь мы имеем пример дискретной случайной величины, так как число почек может быть только целым.

Если нам известно, что исследуемый показатель может принимать любые значения из некоторого интервала (a,b), то строим интервальный вариационный ряд с помощью группировки вариант.

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

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

1. Сначала определяют число интервалов m. Для этого обычно применяют формулу Стреджесса:

m = 1 + 3,22 × lg n. (3.1)

Число m округляют до целого значения.

Приведем еще несколько формул расчета числа интервалов:

m = — 0,013n , (3.1a)

m = 1,72 (3.1b)

m = + 1. (3.1c)

В программе Excel есть процедура «Гистограмма», которая умеет строить вариационный ряд и вычисляет число интервалов по формуле (3.1с). Пример применения процедуры «Гистограмма» приведен ниже.

В табл. 3.1 вычислены рекомендуемые формулами (3.1), (3.1а), (3.1b) и (3.1с) числа интервалов. Значения приведены с округлением до целого.

Таблица 3.1

Объем выборки n	Рекомендуемое число интервалов
формула 3.1	формула 3.1а	формула 3.1b	формула 3.1 с
3,723	2,555	3,29	3,646
4,965	3,902	4,423	5,123
5,612	4,845152	5,16	6,196
6,053	5,602	5,731	7,083
6,388	6,245	6,207	7,856
6,658	6,809	6,619	8,55
6,884	7,314	6,986	9,185

2. Далее вычисляют границы интервалов.

Приведём два способа определения границ.

В первом способе длину интервала вычисляют по формуле.

h=

x_min=min _i>, x_max=_i>, (3.2a) и определяют границы интервалов по формулам:

При таком выборе х_min попадает в середину первого интервала, а x_max – в середину последнего, и число интервалов m.

Во втором способе длина интервала и границы вычисляются по формулам:

h= (3.2б)

При этом х_min относится к первому, а x_max – к последнему интервалам.

h= 10

3. После определения границ интервалов вычисляют для каждого j-того интервала

X_ср.j (3.4)

и частоту n_j т.е. число таких элементов x_i выборки, которые удовлетворяют условиям

_j-1 накопл = w_j накопл = = , j= 1,…,m. (3.7)

Вариационный ряд записывают в виде таблицы (табл.3.2)

Приведем два способа определения границ.

В первом способе длину интервала определяют по формуле.

h= , x_min= min_i>, x_maxi>, (3.2a)

определяют границы интервалов по формулам:

При таком выборе x_min попадет в середину первого интервала, а x_max — в середину последнего, и число интервалов равно m.

Во втором способе длина интервала и границы вычисляются по формулам:

h= (3.3а)

При этом х_min относят к первому, а х_max — к послед­нему интервалам

Таблица 3.2

Номер интервала j	Интервал ( />j-1, />j]	Середина интервала Xср.j	Частота nj	Накопленная частота nj накопл	Частость wk	Накопленная частость wj накопл
(2,12]	0,14	0,14
(12,22]	0,24	0,38
(22,32]	0,33	0,71
(32,42]	0,43	1,14
(42,52]	0,53	1,67
(52,62]	0,63	2,3

Замечание. Вариационный ряд можно задать двумя столбцами: интервалами (или их серединами) и частотами. Остальные столбцы легко вычисляются.

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

При бесповторном отборе элемент в выборку не возвращается.

Число единиц (Элементов) статистической совокупности называется ее объемом. Объем генеральн6ой совокупности обозначается N, а объем выборочной совокупности n.

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

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

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

Гистограмма, полигон, кумулята и огива

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

Для дискретного вариационного ряда полигон частот представляет собой многоугольник (рис. 3.1), ограниченный осью ОХ и ломанной, соединяющей точки ( _,0), ( , ), ( ),…,( , ), ( ,0)

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

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

Обычно гистограмму состоят по относительным частотам, так чтобы сумма площадей прямоугольников была равна единице. Тогда ломаная, соединяющая середины верхних сторон прямоугольников (полигон), является аналогом графика плотности вероятностей распределения.

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

Полигон накопленных частот строится так же, как и полигон частот, при этом вместо частот используются накопленные частоты.

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

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

В российских учебниках по статистике огива опреде­ляется по-разному.

В одном случае огива — это ломаная, соединяющая точки, полученные при откладывании значений вариант на оси ординат, а накопленные частот — на оси абсцисс (Шмойлова Р. А., Минашкин В. Г., Садовникова Н. А., Шувалова Е. Б. Теория статистики: учебник,М.: Финансы и статистика, 2006).

В другом случае огива строится так же, как и кумулята, только вместо накопленных частот используются частоты, подсчитанные с условием «больше чем» (Теория статистики: учебник / под ред.: «проф. Г. Л. Громыко. — М.: ИНФРА-М, 2000).

Таблица 3.2

Номер интервала j	Интервал (хо-1,хj]	Середина интервала	Частота n	Накопленная частота Nj накопл.
(2,12]
(12,22]
(22,32]
(32,42]
(42,52]
(52,62]

Введем в программе Excel исходные данные из таблицы 3.2 и построим полигон (рис.3.3) и гистограмму (рис. 3.4).

Построим кумулятивную кривую. Введем варианты и накопленные частоты в Exel, выделим диапАзон A1:B2, выберем тип диаграммы «Точечная диаграмма со значениями, соединенными сглаживающими линиями». После преобразований получим диаграмму, изображенную на рис. 3.5.

Если мы просто поменяем местами столбцы A1 :A6 и B1: B6, то диаграмма преобразуется в огиву. После замены заголовка и форматирования осей получим диаграмму на рис. 3.6. Эта кривая соответствует определению огивы из первого из указанных выше учебников.

В одном случае огива – это ломаная, соединяющая точки, полученные при откладывании значений вариант на оси ординат, а накопленных частот – на оси абсцисс (Шмойлова Р.А., Минашкин В.Г., Садовникова Н.А., Шувалова Е.Б. Теория статистики: учебник. – М.: Финансы и статистика, 2006).

В другом случае огива строится так же, как и кумулята, только вместо накопленных частот используются частоты, подсчитанные с условием «больше чем» (Теория статистики: учебник / под ред. проф. Г.Л. Громыко. – М.: ИНФРА-М,2000).

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

Ряд распределния является одним из видов группировок.

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

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

• Атрибутивными — называют ряды распределения, построенные по качественными признакам.
• Ряды распределения, построенные в порядке возрастания или убывания значений количественного признака называются вариационными.

Вариационный ряд распределения состоит из двух столбцов:

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

Частоты — это абсолютные числа, показывающие столько раз в совокупности встречается данное значение признака, которые обозначают . Сумма всех частот равна должна быть равна численности единиц всей совокупности.

Частости ( ) — это частоты выраженные в процентах к итогу. Сумма всех частостей выраженных в процентах должна быть равна 100% в долях единице.

Графическое изображение рядов распределения

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

Ряды распределения изображаются в виде:

Полигон

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

Полигон на рис. 1 построен по данным микропереписи населения России в 1994 г.

Домохозяйства, состоящие из:	одного человека	двух человек	трех человек	5 или более	всего
Число домохозяйств в %	19,2	26,2	22,6	20,5	100,0

Рис. 1. Распределение домохозяйств по размеру

Условие: Приводятся данные о распределении 25 работников одного из предприятий по тарифным разрядам:
4; 2; 4; 6; 5; 6; 4; 1; 3; 1; 2; 5; 2; 6; 3; 1; 2; 3; 4; 5; 4; 6; 2; 3; 4
Задача: Построить дискретный вариационный ряд и изобразить его графически в виде полигона распределения.
Решение:
В данном примере вариантами является тарифный разряд работника. Для определения частот необходимо рассчитать число работников, имеющих соответствующий тарифный разряд.

Тарифный разряд Xi	Число работников fi
1	3
2	5
3	4
4	6
5	3
6	4
Итого:	25

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

Для построения полигона распределения (рис 1) по оси абсцисс (X) откладываем количественные значения варьирующего признака — варианты, а по оси ординат — частоты или частости.

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

Статистическая таблица

Условие: Приведены данные о размерах вкладов 20 физических лиц в одном банке (тыс.руб) 60; 25; 12; 10; 68; 35; 2; 17; 51; 9; 3; 130; 24; 85; 100; 152; 6; 18; 7; 42.
Задача: Построить интервальный вариационный ряд с равными интервалами.
Решение:

1. Исходная совокупность состоит из 20 единиц (N = 20).
2. По формуле Стерджесса определим необходимое количество используемых групп: n=1+3,322*lg20=5
3. Вычислим величину равного интервала: i=(152 — 2) /5 = 30 тыс.руб
4. Расчленим исходную совокупность на 5 групп с величиной интервала в 30 тыс.руб.
5. Результаты группировки представим в таблице:

При такой записи непрерывного признака, когда одна и та же величина встречается дважды (как верхняя граница одного интервала и нижняя граница другого интервала), то эта величина относится к той группе, где эта величина выступает в роли верхней границы.

Гистограмма

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

На рис. 2. изображена гистограмма распределения населения России в 1997 г. по возрастным группам.

Все население	В том числе в возрасте
до 10	10-20	20-30	30-40	40-50	50-60	60-70	70 и старше	Всего
Численность населения	12,1	15,7	13,6	16,1	15,3	10,1	9,8	7,3	100,0

Рис. 2. Распределение населения России по возрастным группам

Условие: Приводится распределение 30 работников фирмы по размеру месячной заработной платы

Размер заработной платы руб. в месяц	Численность работников чел.
до 5000	4
5000 — 7000	12
7000 — 10000	8
10000 — 15000	6
Итого:	30

Задача: Изобразить интервальный вариационный ряд графически в виде гистограммы и кумуляты.
Решение:

1. Неизвестная граница открытого (первого) интервала определяется по величине второго интервала: 7000 — 5000 = 2000 руб. С той же величиной находим нижнюю границу первого интервала: 5000 — 2000 = 3000 руб.
2. Для построения гистограммы в прямоугольной системе координат по оси абсцисс откладываем отрезки, величины которых соответствуют интервалам варицонного ряда.
   Эти отрезки служат нижним основанием, а соответствующая частота (частость) — высотой образуемых прямоугольников.
3. Построим гистограмму:

Для построения кумуляты необходимо рассчитать накопленные частоты (частости). Они определяются путем последовательного суммирования частот (частостей) предшествующих интервалов и обозначаются S. Накопленные частоты показывают, сколько единиц совокупности имеют значение признака не больше, чем рассматриваемое.

Кумулята

Распределение признака в вариационном ряду по накопленным частотам (частостям) изображается с помощью кумуляты.

Кумулята или кумулятивная кривая в отличие от полигона строится по накопленным частотам или частостям. При этом на оси абсцисс помещают значения признака, а на оси ординат — накопленные частоты или частости (рис. 3).

Рис. 3. Кумулята распределения домохозяйств по размеру

4. Рассчитаем накопленные частоты:
Наколенная частота первого интервала рассчитывается следующим образом: 0 + 4 = 4, для второго: 4 + 12 = 16; для третьего: 4 + 12 + 8 = 24 и т.д.

Размер заработной платы руб в месяц Xi	Численность работников чел. fi	Накопленные частоты S
до 5000	4	4
5000 — 7000	12	16
7000 — 10000	8	24
10000 — 15000	6	30
Итого:	30	—

При построении кумуляты накопленная частота (частость) соответствующего интервала присваивается его верхней границе:

Огива

Огива строится аналогично кумуляте с той лишь разницей, что накопленные частоты помещают на оси абсцисс, а значения признака — на оси ординат.

Разновидностью кумуляты является кривая концентрации или график Лоренца. Для построения кривой концентрации на обе оси прямоугольной системы координат наносится масштабная шкала в процентах от 0 до 100. При этом на оси абсцисс указывают накопленные частости, а на оси ординат — накопленные значения доли (в процентах) по объему признака.

Равномерному распределению признака соответствует на графике диагональ квадрата (рис. 4). При неравномерном распределении график представляет собой вогнутую кривую в зависимости от уровня концентрации признака.

Кумулята

Теперь построим кумуляту — график накопленных относительных частот. Расположим его под гистограммой.

Кумулята — это экспериментальная оценка формы графика функции распределения. Теоретическая кривая будет красивой и гладкой — мы познакомились с ней в начале работы, обсуждая свой вариант задания. Экспериментальная оценка — ломаная линия, да ещё и с погрешностями. Эти случайные ошибки вызваны ограниченным, не бесконечным объёмом выборки. В любом случае, эти графики начинаются в нуле и постепенно растут до 100%.

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

Построим график в виде ломаной линии:

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart

Вставка — Диаграммы — Вставить точечную (X, Y) или пузырьковую диаграмму

Вставка графика Y (X)

Выбираем тип графика

Scatter — Scatter with Straight Lines

Точечная — Точечная с прямыми отрезками

Это просто ломаная линия без маркеров точек.

Выбираем данные для графика:

Select Data — Select Data Source — Legend Entries (Series) — Add

Выбрать данные — Выбор источника данных — Элементы легенды (ряды) — Добавить

Edit Series

Изменение ряда

выбираем следующие данные.

Столбец «иксов» — верхние границы:

Series X Values

Значения Х

Столбец «игреков» — накопленные частоты:

Series Y Values

Значения Y

Убираем заголовок диаграммы:

Chart Elements — Chart Title

Элементы диаграммы — Название диаграммы

Настраиваем цвет линии на графике.

Format Data Series — Series options — Fill & Line — Line

Формат ряда данных —Параметры ряда — Заливка и границы — Линия

Line — Solid line

Линия — Сплошная линия

Color — Black

Цвет — Чёрный

Width = 0.5 pt

Ширина — 0,5 пт

Если отрезков много, то ломаная линия выглядит как гладкая кривая.

Настроим числовые метки на вертикальной оси, чтобы выводились целые числа:

Format Axis — Axis Options — Number — Decimal places — 0

Формат оси — Параметры оси — Число — Число десятичных знаков — 0

Установим диапазоны значений по осям.

Вертикальная ось — метки в процентах, а границы диапазона — числа. Поэтому пределы изменения будут от 0 до 1:

Category — Percentage

Категория — Процентный

Axis Options — Bounds

Параметры оси — Границы

Minimum — 0

Минимум — 0

Maximum — 1

Максимум — 1

Горизонтальная ось — в соответствии с интервалами группировки — от 190 до 310.

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

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

Format Chart Area — Chart Options — Size & Properties — Size

Формат области диаграммы — Параметры диаграммы — Размер и свойства — Размер

Height — 1.8 in

Высота — 7,62 см

Width — 5.3 in

Ширина — 12,7 см

В английской версии пакета размеры измеряются в дюймах. В русской версии — в сантиметрах. Можем установить точные значения размеров вручную.

Окончательно совмещаем маштаб гистограммы и кумуляты: начало первого интервала 190, конец последнего интервала 310. Положения этих двух меток на обоих графиках должны совпадать.

Проблемы с масштабом решаем так. Значение 190 находится в начале интервала, обозначенного 193. Значение 310 находится в конце интервала, следующего за 303.

Как сделать кумуляту в excel по данным таблицы

Гистограмма — интервальный ряд, изображаемый столбиковой диаграммой, в которой основания столбиков, расположенные по оси абсцисс (x), являются интервалами значений варьирующего признака; а высоты столбиков — это частоты, соответствующие масштабу по оси ординат (y).

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

k = 1 + 3,322 lgN

где k — число групп, округляемое до ближайшего целого числа, а N — численность совокупности.

k = 1 + 3,322 lg150 = 1 + 3,322 2,176 = 8,229 ? 8

Можно было воспользоваться готовой таблицей оптимальных соотношений числа единиц статистической совокупности и числа групп. Значения вычислены по той же формуле Стёрджесса:

Как построить кумуляту в excel по данным таблицы

Построение вариационных рядов, графическое изображение вариационного ряда с помощью MS Office Excel

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

Число единиц (элементов) статистической совокупности называется ее объемом. Объем генеральной совокупности обозначается N, а объем выборочной совокупности п.

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

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

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

Пример 2.1.

Известны следующие данные о результатах сдачи студентами экзамена (в баллах):

18	16	20	17	19	20	17
17	12	15	20	18	19	18
18	16	18	14	14	17	19
16	14	19	12	15	16	20

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

Введем данные в диапазоне A1: A29, в ячейку A1 введем текст «Балл» (рис.2.6).

Рисунок 2.6. Баллы успеваемости студентов

Определим наименьший и наибольший балл по выборке. Для этого введем в ячейках С1 и С2 соответственно введем формулы =МИН(A2:A29) и =МАКС(A2:A29). Получим значения 12 и 20 соответственно (рис.2.7).

Рисунок 2.7. Минимальный и максимальный балл

Построим вариационный ряд. Для каждого значения необходимо подсчитать частоту. Так как значения признака (балл) отличаются на единицу, то можно воспользоваться следующим способом. В ячейку С4 введем формулу =С1, в С5 соответственно С4+1. Ячейку С5 протянем маркером заполнения (правый нижний угол ячейки) вниз до С12. Результаты представлены на рисунке 2.8.

Рисунок 2.8. Значения признака

Вычислим частоту для каждого значения признака. В ячейку D4 введем формулу =СЧЕТЕСЛИ(A$2:A$29;C4) и протянем D4 маркером вниз до заполнения D12. В ячейке D13 просуммируем частоты с помощью формулы =СУММ(D4:D12).

Получим вариационный ряд (значения признака и соответствующие им частоты) на рисунке 2.9.

Рис.2.9. Частоты вариационного ряда

Вычислим частость (относительную частоту) для каждого значения признака. В ячейку Е4 введем формулу = D4/D$13. Протянем Е4 маркером заполнения вниз до Е12 (рис.2.10).

Рисунок 2.10. Частости ряда распределения

Вычислим накопленные частоты. В ячейку F4 введем формулу =D4, а в ячейку F5 – формулу = D5+F4. Протянем F5 маркером заполнения вниз до F12 (рис.2.11).

Рисунок 2.11. Накопленные частоты ряда

Построим эмпирическую функцию распределения, т.е. найдем наколенные частости. Выделим F4:F12 и маркером заполнения протянем вправо на соседний столбец (рис.2.12). В G4 получим формулу = Е4, в ячейке G5 формулу =Е5+ G4 и т.д.

Рисунок 2.12. Накопленные частости ряда

Построим полигон распределения частот и частостей. Выделим диапазон ячеек С4:D12. Выполним команду меню «Диаграмма» и выберем тип «Точечная», вариант «Точечная с прямыми отрезками и маркерами». Полигон распределения частот представлен на рисунке 2.13.

Рисунок 2.13. Полигон распределения частот

Выделим диапазон ячеек С4:С12 и, удерживая клавишу CTRL, диапазон Е4:Е12. Выполним команду меню «Диаграмма» и выберем тип «Точечная», вариант «Точечная с прямыми отрезками и маркерами». Полигон распределения частостей представлен на рисунке 2.14.

Рисунок 2.14. Полигон распределения частостей

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

Построим кумуляту частостей, для чего выделим диапазон ячеек С4:С12 и, удерживая клавишу CTRL, диапазон G4:G12. Выполним команду меню «Диаграмма» и выберем тип «Точечная», вариант «Точечная с прямыми отрезками». Кумулята представлена на рис.2.16.

Рисунок 2.16. Кумулята

Пример 2.2.

В таблице 2.7 представлены значения процентных ставок по кредитам по 30 коммерческим банкам.

Банковские процентные ставки

№ Банка	Процентная ставка, %
1	20,3
2	17,1
3	14,2
4	11,0
5	17,3
6	19,6
7	20,5
8	23,6
9	14,6
10	17,5
11	20,8
12	13,6
13	24,0
14	17,5
15	15,0
16	21,1
17	17,6
18	15,8
19	18,8
20	22,4
21	16,1
22	17,9
23	21,7
24	18,0
25	16,4
26	26,0
27	18,4
28	16,7
29	12,2
30	13,9

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

Введем данные в диапазоне A1:A31 (рис.2.17). Определим максимальное и минимальное значения (ячейки С2 и С3 соответственно) так же как и в примере 2.1. Определим число интервалов по формуле Стэрджесса, для чего в ячейку С6 введем формулу =ЦЕЛОЕ(1+3,322*LOG10(30)) (рис.2.18).

Рисунок 2.17. Процентные ставки банков

Рисунок 2.18. Число интервалов

Вычислим длину интервалов, для чего в ячейке С8 введем формулу =ОКРУГЛ((C3-C2)/C6;2) (рис.2.19).

Рисунок 2.19. Длина интервала

Определим нижние и верхние границы интервалов (карманы), для чего в ячейке Е2 запишем формулу =С2, в ячейке Е3 запишем ==E2+$C$8. Протянем Е3 маркером заполнения вниз до Е7 (рис.2.20).

Рисунок 2.20. Границы интервалов

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

Воспользуемся функцией ЧАСТОТА. Для этого в ячейке F2 введем формулу =ЧАСТОТА(A2:A31;E2:E7). Протянем F2 маркером заполнения вниз до F8.

Формулу в этом примере необходимо ввести как формулу массива. Выделим диапазон F2:F8, нажмем клавишу F2, а затем нажмем клавиши CTRL+SHIFT+ВВОД (рис.2.21).

Если формула не будет введена как формула массива, отобразится только одно ее значение в ячейке F2.

Рисунок 2.21. Частоты значений признака

Рисунок 2.22. Построение гистограммы

Полученная гистограмма представлена на рис.2.23.

Рис.2.23. Гистограмма частот

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