Как сделать кумуляту в excel
Обновлено: 03.07.2024
– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).
– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).
– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).
- Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.
Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):
- моду
- медиану
- размах ряда
- построить полигон частот
- построить столбчатую и круговую диаграммы
- раскрыть смысловую сторону каждой характеристики
1. Занести данные в EXCEL, каждое число в отдельную ячейку.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем - статистические, в списке: МОДА
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.
Используя тот же путь вычисляем медиану.
Вставка – Функция – Статистические – Медиана.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.
Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.
Вставка – Функция – Статистические – МАКС.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наибольшее значение = 36.
Вставка – Функция – Статистические – МИН.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наименьшее значение = 22.
36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.
Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni |
Чтобы сосчитать частоту каждого значения воспользуемся
Вставка – Функция – Статистические – СЧЕТЕСЛИ.
В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22
Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni | 1 | 3 | 4 | 5 | 11 | 9 | 13 | 18 | 16 | 6 | 4 | 6 | 3 | 0 | 1 |
Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические - СУММА). Должно получиться 100 (количество всех фирм).
Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)
Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.
Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).
Диаграмма – Стандартные – Круговая.
Диаграмма – Стандартные – Гистограмма.
4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.
Описание изменений варьирующего признака осуществляют с помощью рядов распределения.
Статистический ряд распределения — это упорядоченное распределение наблюдаемой статистической совокупности на отдельные группы по конкретному варьирующему признаку.
Статистические ряды могут быть как вариационными, так и атрибутивными (рис. 3.6).
Рис. 3.6. Статистические ряды распределения
Статистические ряды распределения состоят из двух элементов: вариант и частот.
Каждое отдельное значение признака, которое он принимает в статистическом ряду распределения, называется вариантой.
Частоты — это числовые значения, показывающие, как часто встречаются те или иные варианты в ряду распределения. Частоты могут быть выражены в абсолютных величинах, долях единицы или процентах к итоговому значению наблюдаемого признака.
Следовательно, некоторая переменная величина х может принимать различные значения х,, х2, х3, . хп. Каждое из этих значений имеет свою частоту повторений /р/2, . /„? Такой
двойной ряд ранжированных значений признака называется вариационным рядом, или рядом распределения.
Для атрибутивного ряда каждая варианта регистрируется в виде текстовой записи с указанием соответствующей частоты повторения. Примером вариационного ряда могут служить данные графика очередных отпусков 100 сотрудников фирмы по месяцам. В табл. 3.11 приводятся сведения о количестве сотрудников, находящихся в отпуске в январе, феврале, марте и т.д. по месяцам года.
Количество сотрудников фирмы, находящихся в очередном отпуске но месяцам года (данные условные)
Значение признака (варианты)
Повторяемость значений признака (частоты)
Ряды распределения показывают закономерность изменения изучаемого признака. В данном случае мы наблюдаем некоторую закономерность распределения отпусков сотрудников фирмы по месяцам. Нетрудно заметить, что наиболее привлекательным является летний период времени. Чтобы выразить ту или иную закономерность более наглядно, принято изображать вариационные ряды графически в виде гистограммы, полигона частот, кумуляты или огивы.
Гистограмма — это способ графического изображения интервальных распределений вариант при непрерывном варьировании признака. Гистограмма распределения применяется только для изображения интервального вариационного ряда.
Для построения гистограммы воспользуемся данными о распределении месячной заработной платы рабочих, приведенными в табл. 3.8. С этой целью построим оси координат и, выбрав соответствующий масштаб, отложим по оси абсцисс границы заработной платы каждой группы рабочих (величины интервалов). Одному значению ординаты будут соответствовать два значения абсциссы, поэтому на графике отмечается не точка, а линия, соединяющая два значения по оси абсцисс. По оси ординат откладывают частоты, т.е. количество рабочих, чья заработная плата не выходила за пределы установленных границ.
Таким образом, гистограмма представляет собой ряд прямоугольников, основанием которых являются границы интервалов групп, а высоты указывают частоты, соответствующие каждой группе (рис. 3.7). Графически гистограмма показывает распределение вариант при непрерывном варьировании признака. Прямоугольники соответствуют границам заработной платы х, а их высота — количеству рабочих/, получающих данную зарплату.
Рис. 3.7. Гистограмма
Если исходные данные представлены в виде дискретного вариационного ряда, то может быть построен график прерывистого варьирования, представляющий собой ломаную кривую, которая называется полигоном распределения.
Полигон распределения можно построить и для интервального вариационного ряда. Для этого по вертикальной оси откладывают те же частоты, что и при построении гистограммы, а по горизонтальной — середины интервалов.
На рис. 3.8 изображен полигон распределения заработной платы рабочих по соответствующим группам, который представляет собой многоугольную фигуру.
Рис. 3.8. Полигон распределения частот
Для того чтобы полигон распределения частот образовывал замкнутую многоугольную фигуру с горизонтальной осью графика, необходимо ввести дополнительные средние точки на нижнем и верхнем концах распределения для прилегающих несуществующих групп, частоты которых равны нулю (рис. 3.9). Если возникает необходимость, можно, имея полигон распределения частот, построить гистограмму, и наоборот. Кроме того, гистограмма и полигон частот могут быть совмещены на одном графике (см. рис. 3.9).
Рис. 3.9. Гистограмма и полигон распределения частот
Преобразованной формой вариационного ряда является ряд накопленных частот (кумулятивный ряд). Кумулятивный ряд позволяет графически представить данные вариационного ряда в виде кумуляты и огивы. Накопленные частоты получаются в результате последовательного суммирования (кумуляции) всех значений частот, либо от минимального значения варианты к максимальному, либо, наоборот, от максимального к минимальному. Расчет накопленных частот наглядно показан в табл. 3.12.
Приведенные в табл. 3.12 сведения о накопленных частотах позволяют построить огиву и кумуляту. Накопленные частоты получают последовательным суммированием (кумуляцией) всех частот вариационного ряда в направлении от минимальной варианты до конца ряда, и наоборот. Построенный график называется огивой (рис. 3.10). При построении огивы по оси абсцисс
Таблица 3.12
Месячная заработная плата рабочих (данные условные)
Месячная зарплата рабочих, руб.
Середина интервала месячной зарплаты
откладывают накопленные частоты, а по оси ординат — значение признака по мере его возрастания. В нашем примере это середина интервала месячной заработной платы.
Рис. 3.10. Огива
Если на оси абсцисс нанести значения середины интервалов, а на оси ординат — накопленные частоты, а затем соединить соответствующие точки в системе координат, то получим график, называемый кумулятой (рис. 3.11).
На этом графике изображена кумулята распределения заработной платы 20 рабочих цеха (по данным табл. 3.12).
Нетрудно заметить, что огива (см. рис. 3.10) есть не что иное, как кумулята (см. рис. 3.11), повернутая на 180 градусов.
Рис. 3.11. Кумулята
Для графического представления рядов распределения в Excel исходные значения вариант и частот представляют в виде таблицы. В частности, для примера, приведенного в табл. 3.12, таблица будет выглядеть так, как она представлена на рис. 3.12.
Поскольку для наглядной характеристики изучаемой закономерности следует построить гистограмму, полигон частот, кумуляту и огиву, необходимо выполнить дополнительные расчеты: определить интервалы месячной заработной платы рабочих и вычислить накопленные частоты, суммируя частоты в направлении от минимальной варианты до максимальной, а затем в обратном порядке.
Для выполнения вычислений можно воспользоваться Строкой формул или Мастером функций. Как правило, непосредственный ввод формул используется для ввода уникальных конструкций, не входящих в стандартный набор функций Excel. Мастер функций, напротив, автоматизирует построение наиболее часто
Рис. 3.12. Вид рабочего листа Excel после ввода данных
встречающихся формул. Признаком формулы является наличие знака равенства в начале строки. Для выполнения расчетов курсор помещают в интересующую ячейку, а затем в строке ввода вводят знак равенства и адреса участвующих в расчетах ячеек, между которыми ставятся знаки арифметических операций. В случае использования Мастера функций с помощью команды
Вставка —•> Функция или кнопки Вставка функции ( ? ) панели
инструментов Форматирование обеспечивается вызов диалоговых окон, в которых выбирается интересующая функция и задаются необходимые аргументы. Пошаговое выполнение расчетов приведено в приложении 4. В итоге таблица будет содержать формулы, приведенные на рис. 3.13.
Рис. 3.13. Вид рабочего листа Excel в режиме отображения формул
кнопке Мастер диаграмм ( т ) на панели инструментов Стандартная. Инструкцию по построению графиков в Excel можно найти в приложении 4. Окончательный вид гистограммы, полигона частот, огивы и кумуляты показан на рис. 3.14, 3.15, 3.16 и 3.17.
Для селекционера, например, важно знать, сколько зерен содержит колос выведенного (выводимого) им нового сорта пшеницы. В этой ситуации совершенно ясно, что подсчетом количества зерен только в одном колосе не обойтись. Для определения числа зерен надо воспользоваться достаточно большим количеством колосьев, скажем не менее сотни. Приведем пример математической обработки результатов селекции.
Все поле пшеницы, которое вырастил селекционер можно на математическом языке назвать генеральной совокупностью. Подсчитать количество зерен в колосьях всей генеральной совокупности, очевидно, не представляется возможным, но из всей генеральной совокупности можно выбрать, скажем, сто колосьев и подсчитать количество зерен в них. Эти сто колосьев будут называться выборкой из генеральной совокупности, и они с определенной точностью будут отражать число зерен во всем поле (генеральной совокупности). Чтобы по данным выборки иметь возможность судить обо всей генеральной совокупности, она должна быть отобрана случайно. Так в нашем случае селекционер ни в коем случае не должен отдавать предпочтение тем или иным колосьям (по размерам, внешнему виду, месту произрастания на поле и т.п.) в процессе их выборки. Наиболее целесообразно в данной ситуации совершать выбор колосьев из непрозрачного мешка наугад. У всех выбранных колосьев производится подсчет числа зерен, и результаты фиксируются в виде ряда чисел, с которыми в дальнейшем и предстоит совершать математические действия. В данном примере можно предложить следующую их последовательность.
2.1 Создание вариационного ряда.
Вариационным рядом называется ранжированный в порядке возрастания или убывания ряд вариантов с соответствующими им весами (частотами или частностями). Вариационный ряд будет дискретным, если любые его варианты отличаются на постоянную величину, и непрерывным, если варианты могут отличатся один от другого на сколь угодно малую величину.
Иными словами в вариационном ряду полученные значения располагаются в порядке их увеличения и, если значение повторяется, то рядом записывается число его повторений. Т.е. в данном примере по числу зерен в колосьях ряд может выглядеть так (таб. 2):
В реальных социально-экономических системах нельзя проводить активные эксперименты, поэтому данные обычно представляют собой наблюдения за происходящим процессом, например: курс валюты на бирже в течение месяца, урожайность пшеницы в хозяйстве за 30 лет, производительность труда рабочих за смену и т.д. Результаты наблюдений — это в общем случае ряд чисел, расположенных в беспорядке, который для изучения необходимо упорядочить (проранжи- ровать).
Операция, заключающаяся в расположении значений признака по возрастанию, называется ранжированием опытных данных.
После операции ранжирования опытные данные можно сгруппировать так, чтобы в каждой группе признак принимал одно и то же значение, которое называется вариантом (х,). Число элементов в каждой группе называется частотой варианта («,).
Размахом вариации называется число
где хтах — наибольший вариант;
x min — наименьший вариант.
Сумма всех частот равна определенному числу л, которое называется объемом совокупности:
Отношение частоты данного варианта к объему совокупности называется относительной частотой, или частостью, этого варианта:
Последовательность вариант, расположенных в возрастающем порядке, называется вариационным рядом (вариация — изменение).
Вариационные ряды бывают дискретными и непрерывными. Дискретным вариационным рядом называется ранжированная последовательность вариант с соответствующими частотами и (или) частостями.
Пример 1. В результате тестирования группа из 24 человек набрала баллы: 4, 0, 3, 4, 1, 0, 3, 1, 0, 4, 0, 0, 3, 1, 0, 1, 1, 3, 2, 3, 1, 2, 1, 2. Построить дискретный вариационный ряд.
Решение. Проранжируем исходный ряд, подсчитаем частоту и частость вариант: 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4.
В результате получим дискретный вариационный ряд (табл. 3.10).
Ранжированный ряд успеваемости
Балл, х.
Число студентов, л,
Относительная частота, А
В Excel проранжируем исходный ряд. Для этого введем все данные в диапазон А1 :А24 и воспользуемся кнопкой Щ (Сортировка по возрастанию).
Подсчитаем частоту и частость вариант. Построим таблицу в диапазоне D2:G7 (рис. 3.13).
Рис. 3.13. Контекстное меню строки состояния
Рассмотрим два варианта подсчета частот:
- 1) выделим диапазон, в котором находятся нули. Щелкнем в нижней правой части окна Excel правой кнопкой мыши и выберем в контекстном меню вид итога, который по умолчанию будет появляться в итоговой строке при выделении произвольного диапазона (см. рис. 3.13) — количество. Таким образом, последовательно выделяя диапазоны с одинаковыми значениями вариант, мы получим все частоты;
- 2) выполним команду Сервис — Анализ данных — Гистограмма. Заполним диалоговое окно в соответствии с рис. 3.14.
В результате получим таблицу с частотами вариантов и соответствующий график (рис. 3.15).
Рис. 3.15. Результаты применения инструмента «Гистограмма)
Найдем объем выборки, заполнив все частоты вариант в диапазоне ЕЗ:Е7, выделим его левой кнопкой мыши и щелкнем по кнопке ? (автосумма).
Excel значительно упрощает построение вариационных рядов.
В Excel открываем файл с данными ПРИМЕР 2.2. Вызываем диалоговое окно Анализ данных, в котором выбирается режим Гистограмма (см. рис. 2.3).
Установите один или несколько флажков:
Интегральный процент. Отображает суммарные проценты и добавляет в таблицу гистограммы строку суммарных процентов.
Вывод графика. Отображает встроенную таблицу гистограммы.
Рис. 2.19. Диалоговое окно гистограммы
В результате работы программы получим результат (рис. 2.20-2.22). Если вы захотите настроить гистограмму, можно изменить подписи и, щелкнув в любом месте гистограммы, использовать кнопки форматирования и макета справа от диаграммы.
Рис. 2.20. Результаты расчетов
Рис. 2.21. Гистограмма
Рис. 2.22. Диалоговое окно форматирования области построения
Используя кнопки форматирования, приводим диаграмму (гистограмму) к виду, показанному на рис. 2.23.
Щелкаем правой кнопкой мыши по полю графика гистограммы и выбираем вкладку Изменить тип диаграммы, на появившейся вкладке (рис. 2.24) выбираем и указываем, какой график, нажимаем ОК.
Читайте также: