Огива распределения как построить в excel

Корпорация "Центр"

Гистограмма распределения в EXCEL

history 15 ноября 2016 г.
    Группы статей

  • Описательная статистика

Гистограмма распределения — это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции ЧАСТОТА() и диаграммы.

Гистограмма (frequency histogram) – это столбиковая диаграмма MS EXCEL , в каждый столбик представляет собой интервал значений (корзину, карман, class interval, bin, cell), а его высота пропорциональна количеству значений в ней (частоте наблюдений).

Гистограмма поможет визуально оценить распределение набора данных, если:

  • в наборе данных как минимум 50 значений;
  • ширина интервалов одинакова.

Построим гистограмму для набора данных, в котором содержатся значения непрерывной случайной величины . Набор данных (50 значений), а также рассмотренные примеры, можно взять на листе Гистограмма AT в файле примера. Данные содержатся в диапазоне А8:А57 .

Примечание : Для удобства написания формул для диапазона А8:А57 создан Именованный диапазон Исходные_данные.

Построение гистограммы с помощью надстройки Пакет анализа

Вызвав диалоговое окно надстройки Пакет анализа , выберите пункт Гистограмма и нажмите ОК.

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

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

Перед тем как анализировать полученный результат — отсортируйте исходный массив данных .

Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).

Второй интервал (отмечен на картинке серым) включает значения больше 113 и меньше или равные 216,428571428571. Можно проверить, что таких значений 11. Предпоследний интервал, от 630,142857142857 (не включая) до 733,571428571429 (включая) содержит 0 значений, т.к. в этом диапазоне значений нет. Последний интервал (со странным названием Еще ) содержит значения больше 733,571428571429 (не включая). Таких значений всего одно — максимальное значение в массиве (837).

Размеры карманов одинаковы и равны 103,428571428571. Это значение можно получить так: =(МАКС( Исходные_данные )-МИН( Исходные_данные ))/7 где Исходные_данные – именованный диапазон , содержащий наши данные.

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

Примечание : Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу =ЦЕЛОЕ(КОРЕНЬ(СЧЕТ( Исходные_данные )))+1

Попробуйте, например, сравнить количество интервалов для диапазонов длиной 35 и 36 значений – оно будет отличаться на 1, а у 36 и 48 – будет одинаковым, т.к. функция ЦЕЛОЕ() округляет до ближайшего меньшего целого (ЦЕЛОЕ(КОРЕНЬ(35))=5 , а ЦЕЛОЕ(КОРЕНЬ(36))=6) .

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

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

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

Для нашего набора данных установим размер кармана равным 100 и первый карман возьмем равным 150.

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

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

Построение гистограммы распределения без использования надстройки Пакет анализа

Порядок действий при построении гистограммы в этом случае следующий:

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

СОВЕТ : Часто рекомендуют, чтобы границы интервала были на один порядок точнее самих данных и оканчивались на 5. Например, если данные в массиве определены с точностью до десятых: 1,2; 2,3; 5,0; 6,1; 2,1, …, то границы интервалов должны быть округлены до сотых: 1,25-1,35; 1,35-1,45; … Для небольших наборов данных вид гистограммы сильно зависит количества интервалов и их ширины. Это приводит к тому, что сам метод гистограмм, как инструмент описательной статистики , может быть применен только для наборов данных состоящих, как минимум, из 50, а лучше из 100 значений.

В наших расчетах для определения количества интервалов мы будем пользоваться формулой =ЦЕЛОЕ(КОРЕНЬ(n))+1 .

Примечание : Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество — 13.

Расчет ширины интервала и таблица интервалов приведены в файле примера на листе Гистограмма . Для вычисления количества значений, попадающих в каждый интервал, использована формула массива на основе функции ЧАСТОТА() . О вводе этой функции см. статью Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в MS EXCEL .

В MS EXCEL имеется диаграмма типа Гистограмма с группировкой , которая обычно используется для построения Гистограмм распределения .

В итоге можно добиться вот такого результата.

Примечание : О построении и настройке макета диаграмм см. статью Основы построения диаграмм в MS EXCEL .

Одной из разновидностей гистограмм является график накопленной частоты (cumulative frequency plot).

На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.

СОВЕТ : О построении двумерной гистограммы см. статью Двумерная гистограмма в MS EXCEL .

Примечание : Альтернативой графику накопленной частоты может служить Кривая процентилей , которая рассмотрена в статье про Процентили .

Примечание : Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами ).

Статистические ряды распределений

Описание изменений варьирующего признака осуществляют с помощью рядов распределения.

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

Статистические ряды могут быть как вариационными, так и атрибутивными (рис. 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. Мастер функций, напротив, автоматизирует построение наиболее часто

Вид рабочего листа Excel после ввода данных

Рис. 3.12. Вид рабочего листа Excel после ввода данных

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

Вставка —•> Функция или кнопки Вставка функции ( ? ) панели

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

Вид рабочего листа Excel в режиме отображения формул

Рис. 3.13. Вид рабочего листа Excel в режиме отображения формул

Для построения графиков используется Мастер диаграмм. Он вызывается командой ВставкаДиаграмма или щелчком по

кнопке Мастер диаграмм ( т ) на панели инструментов Стандартная. Инструкцию по построению графиков в Excel можно найти в приложении 4. Окончательный вид гистограммы, полигона частот, огивы и кумуляты показан на рис. 3.14, 3.15, 3.16 и 3.17.

Гистограмма

Рис. 3.14. Гистограмма

Полигон частот

Рис. 3.15. Полигон частот

Огива

Рис. 3.16. Огива

Кумулята

Рис. 3.17. Кумулята

Корпорация "Центр"

Ссылка на основную публикацию