Как работает гистограмма в excel
Перейти к содержимому

Как работает гистограмма в 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 .

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

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

Создание гистограммы в Microsoft Excel

Гистограмма в Microsoft Excel

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

Построение гистограммы

Гистограмму в Экселе можно создать тремя способами:

    • С помощью инструмента, который входит в группу «Диаграммы»;
    • С использованием условного форматирования;
    • При помощи надстройки Пакет анализа.

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

    Способ 1: создание простой гистограммы в блоке диаграмм

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

      Строим таблицу, в которой содержатся данные, отображаемые в будущей диаграмме. Выделяем мышкой те столбцы таблицы, которые будут отображены на осях гистограммы.

    Выделение области в Microsoft Excel

    Все простые диаграммы расположены с левой части списка.

    После того, как выбор сделан, на листе Excel формируется гистограмма.

    Выбор гистограммы в Microsoft Excel

    С помощью инструментов, расположенных в группе вкладок «Работа с диаграммами» можно редактировать полученный объект:

    • Изменять стили столбцов;
    • Подписывать наименование диаграммы в целом, и отдельных её осей;
    • Изменять название и удалять легенду, и т.д.

    Редактирование гистограммы в Microsoft Excel

    Способ 2: построение гистограммы с накоплением

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

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

    Пустая ячейка в Microsoft Excel

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

    Создание гистограммы с накоплением в Microsoft Excel

  1. После этих действий гистограмма появится на листе. Её можно будет отредактировать с помощью тех же инструментов, о которых шёл разговор при описании первого способа построения.
  2. Редактирование гистограммы с накоплением в Microsoft Excel

    Способ 3: построение с использованием «Пакета анализа»

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

      Переходим во вкладку «Файл».

    Переход во вкладку Файл программы Microsoft Excel

    Кликаем по наименованию раздела «Параметры».

    Переход в раздел Параметры в Microsoft Excel

    Переходим в подраздел «Надстройки».

    Переход в надстройки в программе Microsoft Excel

    В блоке «Управление» переставляем переключатель в позицию «Надстройки Excel».

    Перемещение в надстройки в программе Microsoft Excel

    В открывшемся окне около пункта «Пакет анализа» устанавливаем галочку и кликаем по кнопке «OK».

    Активация пакета анализа в программе Microsoft Excel

    Перемещаемся во вкладку «Данные». Жмем на кнопку, расположенную на ленте «Анализ данных».

    Переход в анализ данных в Microsoft Excel

    В открывшемся небольшом окне выбираем пункт «Гистограммы». Жмем на кнопку «OK».

    Выбор гистограммы в анализе данных в Microsoft Excel

  3. Открывается окно настройки гистограммы. В поле «Входной интервал» вводим адрес диапазона ячеек, гистограмму которого хотим отобразить. Обязательно внизу ставим галочку около пункта «Вывод графика». В параметрах ввода можно указать, где будет выводиться гистограмма. По умолчанию — на новом листе. Можно указать, что вывод будет осуществляться на данном листе в определенных ячейках или в новой книге. После того, как все настройки введены, жмем кнопку «OK».
  4. Настройка гистограммы в Microsoft Excel

    Как видим, гистограмма сформирована в указанном вами месте.

    Гистограмма сформирована в Microsoft Excel

    Способ 4: Гистограммы при условном форматировании

    Гистограммы также можно выводить при условном форматировании ячеек.

    1. Выделяем ячейки с данными, которые хотим отформатировать в виде гистограммы.
    2. Во вкладке «Главная» на ленте жмем на кнопку «Условное форматирование». В выпавшем меню кликаем по пункту «Гистограмма». В появившемся перечне гистограмм со сплошной и градиентной заливкой выбираем ту, которую считаем более уместной в каждом конкретном случае.

    Создание условного форматирования в Microsoft Excel

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

    Условное форматирование в Microsoft Excel

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

    Мы рады, что смогли помочь Вам в решении проблемы.

    Помимо этой статьи, на сайте еще 11907 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

    Добавить комментарий

    Ваш адрес email не будет опубликован.