Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

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

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

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

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

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

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

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

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

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

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

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

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

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).

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

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

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

В таблице 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).

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

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

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

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

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

Как Сделать Гистограмму в Excel Интервалы • Актуальность и использование

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

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
При выборочном наблюдении обследованию подвергается определенная, заранее обусловленная часть совокупности, а результаты обследования распространяются на всю совокупность. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Гистограмма — интервальный ряд, изображаемый столбиковой диаграммой, в которой основания столбиков, расположенные по оси абсцисс (x), являются интервалами значений варьирующего признака; а высоты столбиков — это частоты, соответствующие масштабу по оси ординат (y).
hello_html_m6ef150d0.jpg

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

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

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
Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector