Как в 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= 
xmin=min i>, xmax=i>, (3.2a) и определяют границы интервалов по формулам:
При таком выборе хmin попадает в середину первого интервала, а xmax – в середину последнего, и число интервалов m.
Во втором способе длина интервала и границы вычисляются по формулам:
h= 
(3.2б)
При этом хmin относится к первому, а xmax – к последнему интервалам.
h= 
10
3. После определения границ интервалов вычисляют для каждого j-того интервала
Xср.j
(3.4)
и частоту nj т.е. число таких элементов xi выборки, которые удовлетворяют условиям
j-1 накопл = 
wj накопл = 
= 
, j= 1,…,m. (3.7)
Вариационный ряд записывают в виде таблицы (табл.3.2)
Приведем два способа определения границ.
В первом способе длину интервала определяют по формуле.
h= 
, xmin= mini>, xmaxi>, (3.2a)
определяют границы интервалов по формулам:
При таком выборе xmin попадет в середину первого интервала, а xmax — в середину последнего, и число интервалов равно 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.
Задача: Построить интервальный вариационный ряд с равными интервалами.
Решение:
- Исходная совокупность состоит из 20 единиц (N = 20).
- По формуле Стерджесса определим необходимое количество используемых групп: n=1+3,322*lg20=5
- Вычислим величину равного интервала: i=(152 — 2) /5 = 30 тыс.руб
- Расчленим исходную совокупность на 5 групп с величиной интервала в 30 тыс.руб.
- Результаты группировки представим в таблице:
При такой записи непрерывного признака, когда одна и та же величина встречается дважды (как верхняя граница одного интервала и нижняя граница другого интервала), то эта величина относится к той группе, где эта величина выступает в роли верхней границы.
Гистограмма
Для построения гистограммы по оси абсцисс указывают значения границ интервалов и на их основании строят прямоугольники, высота которых пропорциональна частотам (или частостям).
На рис. 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 |
Задача: Изобразить интервальный вариационный ряд графически в виде гистограммы и кумуляты.
Решение:
- Неизвестная граница открытого (первого) интервала определяется по величине второго интервала: 7000 — 5000 = 2000 руб. С той же величиной находим нижнюю границу первого интервала: 5000 — 2000 = 3000 руб.
- Для построения гистограммы в прямоугольной системе координат по оси абсцисс откладываем отрезки, величины которых соответствуют интервалам варицонного ряда.
Эти отрезки служат нижним основанием, а соответствующая частота (частость) — высотой образуемых прямоугольников. - Построим гистограмму:
Для построения кумуляты необходимо рассчитать накопленные частоты (частости). Они определяются путем последовательного суммирования частот (частостей) предшествующих интервалов и обозначаются 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). При неравномерном распределении график представляет собой вогнутую кривую в зависимости от уровня концентрации признака.
Как построить кумуляту в excel по данным таблицы
Теперь построим кумуляту — график накопленных относительных частот. Расположим его под гистограммой.
Кумулята — это экспериментальная оценка формы графика функции распределения. Теоретическая кривая будет красивой и гладкой — мы познакомились с ней в начале работы, обсуждая свой вариант задания. Экспериментальная оценка — ломаная линия, да ещё и с погрешностями. Эти случайные ошибки вызваны ограниченным, не бесконечным объёмом выборки. В любом случае, эти графики начинаются в нуле и постепенно растут до 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.
Построение вариационных рядов, графическое изображение вариационного ряда с помощью 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. Гистограмма частот
Замечание. Если диапазон карманов не был введен, то набор отрезков, равномерно распределенных между минимальным и максимальным значениями данных, будет создан автоматически.
Как в excel построить кумуляту
Графическое изображение зависимости между величинами дает возможность представить эту зависимость наглядно. Графики могут служить основой для открытия новых свойств, соотношений и закономерностей.
Наиболее употребительными графиками для изображения вариационных рядов, т. е. соотношений между значениями признака и соответствующими частотами или относительными частотами, являются полигон, гистограмма и кумулята.
Полигон чаще всего используют для изображения дискретных рядов. Для построения полигона в прямоугольной системе координат на оси абсцисс в произвольно выбранном масштабе откладывают значения аргумента, т. е. варианты, а на оси ординат также в произвольно выбранном масштабе — значения частот или относительных частот. Масштаб выбирают такой, чтобы была обеспечена необходимая наглядность, и чтобы рисунок имел желательный размер. Далее в этой системе координат строят точки, координатами которых являются пары соответствующих чисел из вариационного ряда. Полученные точки последовательно соединяют отрезками прямой. Крайнюю «левую» точку соединяют с точкой оси абсцисс, абсцисса которой находится слева от рассматриваемой точки на таком же расстоянии, как абсцисса ближайшей справа точки. Аналогично крайнюю «правую» точку также соединяют с точкой оси абсцисс.
Кумулята служит для графического изображения кумулятивного вариационного ряда. Для ее построения на оси абсцисс откладывают значения аргумента, а на оси ординат — накопленные частоты или накопленные относительные частоты. Масштаб на каждой оси выбирают произвольно. Далее строят точки, абсциссы которых равны вариантам (в случае дискретных рядов) или верхним границам интервалов (в случае интервальных рядов), а ординаты — соответствующим частотам (накопленным частотам). Эти точки соединяют отрезками прямой. Полученная ломаная и является кумулятой.
МИНОБРНАУКИ РОССИИ
Федеральное государственное бюджетное образовательное
Учреждение высшего профессионального образования
«Юго-Западный государственный университет»
Кафедра финансов и кредита
Лабораторная работа №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 = 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=
xmin=min i>, xmax=i>, (3.2a) и определяют границы интервалов по формулам:
При таком выборе хmin попадает в середину первого интервала, а xmax – в середину последнего, и число интервалов m.
Во втором способе длина интервала и границы вычисляются по формулам:
h= (3.2б)
При этом хmin относится к первому, а xmax – к последнему интервалам.
h= 10
3. После определения границ интервалов вычисляют для каждого j-того интервала
Xср.j(3.4)
и частоту nj т.е. число таких элементов xi выборки, которые удовлетворяют условиям
j-1 накопл = wj накопл = = , j= 1,…,m. (3.7)
Вариационный ряд записывают в виде таблицы (табл.3.2)
Приведем два способа определения границ.
В первом способе длину интервала определяют по формуле.
h= , xmin= mini>, xmaxi>, (3.2a)
определяют границы интервалов по формулам:
При таком выборе xmin попадет в середину первого интервала, а xmax — в середину последнего, и число интервалов равно m.
Во втором способе длина интервала и границы вычисляются по формулам:
h= (3.3а)
При этом хmin относят к первому, а хmax — к последнему интервалам
Таблица 3.2
| Номер интервала j | Интервал ( alt="image019" />j-1, alt="image019" />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.
Задача: Построить интервальный вариационный ряд с равными интервалами.
Решение:
При такой записи непрерывного признака, когда одна и та же величина встречается дважды (как верхняя граница одного интервала и нижняя граница другого интервала), то эта величина относится к той группе, где эта величина выступает в роли верхней границы.
Гистограмма
Для построения гистограммы по оси абсцисс указывают значения границ интервалов и на их основании строят прямоугольники, высота которых пропорциональна частотам (или частостям).
На рис. 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 |
Задача: Изобразить интервальный вариационный ряд графически в виде гистограммы и кумуляты.
Решение:
Для построения кумуляты необходимо рассчитать накопленные частоты (частости). Они определяются путем последовательного суммирования частот (частостей) предшествующих интервалов и обозначаются 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). При неравномерном распределении график представляет собой вогнутую кривую в зависимости от уровня концентрации признака.
Как построить график в Excel по данным таблицы
При работе в Excel табличных данных часто не достаточно для наглядности информации. Для повышения информативности ваших данных советуем использовать графики и диаграммы в Эксель. В этой статье рассмотрим пример того, как построить график в Excel по данным таблицы.
Кликните по кнопке ниже и мы пришлем Вам Excel файл с примером графика, построенного по данным таблицы на E-mail:
Видеоурок
Как построить простой график в Excel
Представим, что у нас есть таблица с ежемесячными данными среднего курса Доллара в течении года:
На основе этих данных нам нужно нарисовать график. Для этого нам потребуется:
- Выделить данные таблицы, включая даты и курсы валют левой клавишей мыши:
- На панели инструментов перейти во вкладку «Вставка» и в разделе «Диаграммы» выбрать «График»:
- Во всплывающем окне выбрать подходящий стиль графика. В нашем случае, мы выбираем график с маркерами:
- Система построила нам график:
Как построить график в Excel на основе данных таблицы с двумя осями
Представим, что у нас есть данные не только курса Доллара, но и Евро, которые мы хотим уместить на одном графике:
Для добавления данных курса Евро на наш график необходимо сделать следующее:
- Выделить созданный нами график в Excel левой клавишей мыши и перейти на вкладку «Конструктор» на панели инструментов и нажать «Выбрать данные»:
- Изменить диапазон данных для созданного графика. Вы можете поменять значения в ручную или выделить область ячеек зажав левую клавишу мыши:
- Готово. График для курсов валют Евро и Доллара построен:
Если вы хотите отразить данные графика в разных форматах по двум осям X и Y, то для этого нужно:
- Перейти в раздел «Конструктор» на панели инструментов и выбрать пункт «Изменить тип диаграммы»:
- Перейти в раздел «Комбинированная» и для каждой оси в разделе «Тип диаграммы» выбрать подходящий тип отображения данных:
- Нажать «ОК»
Ниже мы рассмотрим как улучшить информативность полученных графиков.
Как добавить название в график Эксель
На примерах выше мы строили графики курсов Доллара и Евро, без заголовка сложно понять про что он и к чему относится. Чтобы решить эту проблему нам нужно:
- Нажать на графике левой клавишей мыши;
- Нажать на «зеленый крестик» в правом верхнем углу графика;
- Во всплывающем окне поставить галочку напротив пункта «Название диаграммы»:
- Над графиком появится поле с названием графика. Кликните по нему левой клавишей мыши и внесите свое название:
Как подписать оси в графике Excel
Для лучше информативности нашего графика в Excel есть возможность подписать оси. Для этого:
- Щелкните левой клавишей мыши по графику. В правом верхнем углу графика появится «зеленый крестик», нажав на который раскроются настройки элементов диаграммы:
- Щелкните левой клавишей мыши на пункте «Названия осей». На графике под каждой осью появятся заголовки, в которые вы можете внести свой текст:
Как добавить подписи данных на графике Excel
Ваш график может стать еще более информативным с помощью подписи отображаемых данных.
На примере курсов валют мы хотим отобразить на графике стоимость курса Доллара и Евро помесячно. Для этого нам потребуется:
- Щелкнуть правой кнопкой мыши по линии графика, на которую хотим добавить данные. В раскрывающемся меню выбрать пункт «Добавить подписи данных»:
Система отобразила курс Доллара на линии графика, но наглядность данных это не улучшило, так как значения сливаются с графиком. Для настройки отображения подписи данных потребуется сделать следующие шаги:
- Кликните правой кнопкой мыши на любом значении линии графика. В всплывающем окне выберите пункт «Формат подписей данных»:
В этом меню вы сможете настроить положение подписи, а также из чего будет состоять подпись данных: из имени ряда, категории, значения и.т.д.
Помимо настроек расположения, в том же меню вы сможете настроить размер подписей, эффекты, заливку и.т.д:
Как построить график по данным таблицы в Excel
Визуальное представление информации является важной составляющей различных презентаций, отчетов и так далее. Они позволяют получить более глубокое представление о том, о чем рассказывает спикер или подчиненный, когда публикует данные о том, насколько успешными были продажи в определенном квартале.
Графики и диаграммы – один из самых популярных методов визуального представления информации. Без них невозможно представить любую более-менее внятную аналитику. Построение графика, исходя из данных таблицы, возможно несколькими способами. Все они имеют свои достоинства и недостатки. Давайте более детально их рассмотрим.
Элементарный график
Основная задача любого графика – продемонстрировать, как изменялись значения во времени. Давайте предположим, что у нас есть такая таблица, содержащая информацию о том, сколько чистой прибыли было получено организацией за последнюю пятилетку.
Мы приводим эти показатели исключительно для того, чтобы продемонстрировать работу графика. Они не соответствуют ни одной реальной компании и не привязаны ни к одной валюте.
Далее нам необходимо открыть вкладку «Вставка», где можно выбрать разные виды диаграмм. Давайте выберем «График», поскольку он больше всего соответствует нашей теме. При этом логика одинаковая у всех видов диаграмм, поэтому вы можете выбрать любой другой.
2
После того, как мы выберем тип диаграммы, появится окошко, где можно выбрать вид графика. Excel также приводит рекомендации, в каких ситуациях лучше тот или иной вариант. Чтобы получить подсказку, достаточно навести на нее курсором мыши и немного подождать.
3
После того, как был осуществлен выбор графика, необходимо задать исходную таблицу в области диаграммы. В результате, мы получим следующее.
4
Видим один дефект. Синяя линия абсолютно бесполезна в нашем примере, поэтому ее следует удалить. Чтобы сделать это, достаточно просто выделить ее и нажать клавишу Delete. Таким же способом убирается легенда, которая также не нужна в нашем случае (поскольку кривая всего одна). При этом маркеры желательно подписать, чтобы сразу было понятно, что они означают. Для этого открывается вкладка «Подписи данных».
5
Также желательно подписать оси. Чтобы сделать это, надо воспользоваться соответствующим меню на вкладке «Макет».
6
Точно так же убирается заголовок, если в нем нет необходимости. Это делается путем его перемещения за пределы графика. Также можно выполнить ряд других операций, воспользовавшись пунктом «Название диаграммы».
7
Используем год в качестве отчетного года, а не его порядковый номер, как изначально предлагается программой. Нам следует выделить значения той оси, где отображается время. После этого кликнуть правой кнопкой мыши и выбрать пункт «Выбрать данные» – «Изменить подписи горизонтальной оси». Далее появится вкладка, где выбираются данные, которые будут применяться. Если говорить о нашем примере, это первая колонка.
8
У пользователя есть возможность как оставить график в изначальном виде, так и отформатировать его. Также можно передвинуть диаграмму на иной лист, воспользовавшись вкладкой «Конструктор». Именно там находится одноименная опция.
Создание графика с двумя кривыми
Предположим, перед нами стоит задача указать не только лишь чистую прибыль предприятия, а и суммарную цену всех активов, находящихся у него на счету. Здесь информации для отображения в графике станет больше. Возможно ли создать диаграмму, которая будет учитывать все эти показатели без необходимости создавать еще одну?
9
Конечно, да. Более того, не нужно совершать принципиально других действий. Только в этом случае не нужно удалять легенду, поскольку она обозначает каждую из осей, позволяя тому, кто читает график, понимать, какой показатель каждая из них демонстрирует.
10
Вставка дополнительной оси
Какие действия нужно предпринять, если мы хотим еще одну ось добавить? Например, она пригодится, если используется несколько различных типов данных или единиц измерения.
Первый этап аналогичен предыдущему. Нужно таким образом строить график, как будто у нас все данные принадлежат к одному типу.
11
После этого ось, к которой будет добавляться вспомогательная, выделяется, делается клик правой кнопкой. Далее переходим по опциям «Формат ряда данных» – «Параметры ряда» – «По вспомогательной оси».
12
После того, как мы нажмем кнопку «Закрыть», появится еще одна ось на графике, которая описывает информацию второй кривой (в нашем примере, той, что синего цвета).
13
Также в функционал Excel заложен еще один метод – изменение вида диаграммы. В этом случае сперва делается правый клик по той линии, к которой будет добавляться дополнительная ось. После этого перед нами появляется меню, в каком нас интересует опция «Изменить тип диаграммы для ряда».
14
После этого нам нужно понять, как будет отображаться график второго ряда данных. В нашем случае мы сделали линейчатую диаграмму, потому что нам показалось, что такой способ представления наиболее удобен. Но у вас есть возможность какой-угодно другой вариант выбрать.
15
Как видим, достаточно сделать лишь несколько кликов, чтобы присоединить к графику еще одну ось, которая описывает измерения другого типа.
Построение графика функции
Все мы помним со школьных времен о том, что такое функция. На уроках алгебры очень популярным заданием было построение графика функции. Excel дает возможность всего в два шага автоматически это сделать. Сначала создается диапазон с данными, а потом осуществляется непосредственное построение диаграммы.
Предположим, наша функция такая: y=x(√x – 2). Шаг – 0,3.
Наш первый шаг – составление таблицы функции, состоящей из двух колонок. Первый – X. Чтобы его заполнить, необходимо написать 1 в первой ячейке и 1,3 во второй. Можно воспользоваться формулой =предыдущая ячейка + 0,3. После этого нужно взяться левой кнопкой мыши за правый нижний угол ячейки (он в форме квадратика, называемого маркером автозаполнения), после чего он тянется вниз на необходимое количество ячеек.
16
Столбец Y в нашем примере будет использоваться для расчета функции. Для этого необходимо в первую ячейку ввести формулу =A2*(КОРЕНЬ(A2)-2) и нажать на клавишу «Enter». Значение рассчитывается автоматически. Далее формула размножается по всему столбцу аналогичным предыдущему способом.
Все, теперь таблица с базовой информацией готова.
17
После этого нам следует понять, на каком листе вставлять диаграмму. Это делается как на новом, так и на существующем листе. В первом случае необходимо его создать. Если диаграмма вставляется на этот же лист, то достаточно выделить необходимую ячейку. Далее воспользуемся пунктами «Вставка» – «Диаграмма» – «Точечная». Появляется перечень разных видов диаграммы. У нас есть возможность осуществить выбор самой подходящей конкретно нам.
Далее выделяем первую колонку и кликаем «Добавить». После этого всплывет окно, в котором нужно с помощью функции задать имя ряда. В поле «Значения X» указывается информация из первого столбца. Соответственно, в поле «Значения Y» записывается диапазон второго столбца.
18
Как видим, ось X не содержит значений, в то время как они есть на вертикальной. Поэтому надо сделать правильные подписи осям. Просто делается правый клик по оси X, после чего кликаем на «Выбрать данные», после чего кликаем по пункту «Изменить подписи горизонтальной оси». После этого выделяем требуемый диапазон (простыми словами, таблицу, в которой содержатся нужные нам данные), и затем график обретает правильный вид.
19
Как объединять графики и накладывать их друг на друга
Видим, что два отдельных графика построить несложно, независимо от того, сколько осей там используется. А вот что делать, если нужно объединить две диаграммы в одну? Предположим, нам нужно отобразить две функции. Одна – та, которая была раньше, а другая – которая приводится на таблице ниже.
20
После этого необходимые ячейки выделяются и вставляются в диаграмму. Если оказывается, что что-то пошло не по плану, можно поправить ситуацию через вкладку «Выбрать данные».
21
Графики зависимости в Excel – особенности создания
Под графиком зависимости подразумевается такая диаграмма, где данные одной колонки или ряда изменяются тогда, когда редактируются значения другой.
Для создания графика зависимости нужно сделать такую табличку.
22
Условия: А = f (E); В = f (E); С = f (E); D = f (E).
Далее нужно выбрать нужный тип. Здесь пусть она будет по старой традиции точечной, а также с гладкими кривыми и маркерами.
После этого нажимаем по пункту «Выбор данных», который уже знаком нам, после чего нажимаем «Добавить». Значения вставляются по такой логике:
- Для имени ряда А в качестве значений по горизонтали используются значения А. Для значения по вертикали – Е.
- После этого опять нажимается кнопка «Добавить», после чего для ряда B в качестве значений по горизонтали вставляются значения B, а по вертикали – то же значение Е.
Таким образом обрабатывается вся таблица. В результате, появляется такой милый график зависимости.
23
Форматирование диаграммы
Здесь можно или остановиться, или начать редактировать вид диаграммы. Форматирование может быть как простым, так сложным. Здесь мы опишем базовые функции.
Как вы можете увидеть, после того, как будет выделена диаграмма, на ленте автоматически появляются вкладки «Конструктор» и «Формат». С их помощью можно настраивать внешний вид графиков.
Быстрое форматирование
В Excel предусмотрен набор шаблонов, позволяющих быстро оформить любой график. Они называются стилями. Microsoft предусмотрела 2 способа определения стиля графика:
- Перейти на вкладку «Конструктор» и там найти пункт «Стили диаграмм». Не забудьте предварительно выделить график. Вы можете предварительно посмотреть, как будет выглядеть та или иная диаграмма. Если появится необходимость применить нужный стиль, достаточно просто кликнуть по подходящему варианту.
- Использование кнопки «Стили диаграмм», которая появляется после выделения диаграммы. Этот метод работает таким же образом, как и предыдущий, просто стили отображаются не на ленте, а на правой панели.
Принципиальной разницы между этими двумя методами нет, поэтому вы можете выбрать тот, который подходит вам больше всего.
Добавление и форматирование элементов диаграммы
При создании графиков можно добавлять дополнительные элементы графика. Во вкладке «Конструктор» есть кнопка «Добавить элементы диаграммы». После ее нажатия появляется перечень всех элементов. Достаточно просто выбрать нужный и сделать клик.
Также можно это же сделать через кнопку «Элементы диаграммы», появляющуюся после выделения графика. После ее нажатия появляется панель, где с помощью галочки можно настроить необходимые элементы. После этого настраиваем их расположение, и все.
Больше всего возможностей форматирования находится на панели задач «Формат», впервые добавленную разработчиками в Excel 2013 версии. Ранее это было обычное диалоговое окно. Чтобы его открыть, следует сделать клик по графику и нажать Ctrl + 1. После этого делается двойной клик по необходимому элементу, потом вызывается контекстное меню и выбирается пункт «Формат».
Если же версия Excel более новая, то достаточно найти на вкладке «Формат» кнопку «Формат выделенного», которая находится на ленте и сделать клик по ней.
В любом случае, в окне «Формат» содержатся одни и те же возможности, такие как заливка и границы, эффекты, параметры и размеры разных объектов графика, цвет и так далее.
Выводы
Таким образом, построение графика в Excel – легкая задача, не требующая особой квалификации. Она позволяет представить огромный набор данных в простой для понимания форме. Это полезно в самых разных сферах деятельности, начиная учебным процессом и заканчивая презентацией бизнес-проекта перед потенциальными инвесторами.