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

Обновлено: 28.06.2024

Гистограмма – это распространенный инструмент анализа данных в деловом мире. Это столбчатая диаграмма, показывающая частоту появления переменной в указанном диапазоне.

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

Простым примером гистограммы является распределение оценок по предмету. Вы можете легко создать гистограмму и посмотреть, сколько учащихся набрали меньше 35 баллов, сколько из них набрали от 35 до 50, сколько от 50 до 60 и т. д.

Существуют разные способы создания гистограммы в Excel:

• Если вы используете Excel 2016, вы можете использовать встроенную гистограмму.
• Если вы используете Excel 2013, 2010 или более ранние версии (и даже Excel 2016), вы можете создать гистограмму с помощью пакета инструментов анализа данных или с помощью функции ЧАСТОТА (описанной далее в этом руководстве)

Давайте посмотрим, как сделать гистограмму в Excel.

Это руководство охватывает:

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

Excel 2016 получил новое дополнение в разделе диаграмм, где диаграмма гистограммы была добавлена ​​как встроенная диаграмма.

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

Предположим, у вас есть набор данных, как показано ниже. Он имеет оценки (из 100) 40 студентов по предмету.

Вот шаги для создания гистограммы в Excel 2016:

Вышеуказанные шаги позволят вставить гистограмму на основе вашего набора данных (как показано ниже).

Теперь вы можете настроить эту диаграмму, щелкнув правой кнопкой мыши по вертикальной оси и выбрав Формат оси.

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

Вот некоторые вещи, которые вы можете сделать, чтобы настроить эту гистограмму:

1. По категориям: этот параметр используется при наличии текстовых категорий. Это может быть полезно, когда у вас есть повторения в категориях, и вы хотите узнать сумму или количество категорий. Например, если у вас есть данные о продажах таких товаров, как принтер, ноутбук, мышь и сканер, и вы хотите узнать общий объем продаж каждого из этих товаров, вы можете использовать параметр «По категориям». В нашем примере это бесполезно, так как все наши категории разные (Учащийся 1, Учащийся 2, Ученик 3 и т. д.)
2. Автоматически: этот параметр автоматически определяет, какие интервалы создавать на гистограмме. Например, на нашем графике было решено, что должно быть четыре интервала. Вы можете изменить это с помощью параметров «Ширина ячейки/количество ячеек» (см. ниже).
3. Ширина корзины: здесь вы можете указать, насколько большой должна быть корзина. Если я введу здесь 20, будут созданы ячейки, такие как 36–56, 56–76, 76–96, 96–116.
4. Количество ячеек. Здесь вы можете указать, сколько ячеек вы хотите. Он автоматически создаст диаграмму с таким количеством ячеек. Например, если я укажу здесь 7, будет создана диаграмма, как показано ниже. В определенный момент вы можете указать либо ширину ячейки, либо количество ячеек (но не оба).
5. Ячейка переполнения: используйте эту ячейку, если вы хотите, чтобы все значения выше определенного значения были объединены вместе на диаграмме гистограммы. Например, если я хочу узнать количество учащихся, набравших более 75 баллов, я могу ввести 75 в качестве значения переполненной корзины. Это покажет мне что-то, как показано ниже.
6. Корзина с переполнением: аналогично корзине с переполнением, если я хочу узнать количество учащихся, набравших менее 40 баллов, я могу ввести 4o в качестве значения и отобразить диаграмму, как показано ниже.

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

Создание гистограммы с помощью пакета инструментов анализа данных

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

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

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

Установка пакета инструментов анализа данных

Чтобы установить надстройку Data Analysis Toolpak:




• В диалоговом окне "Параметры Excel" выберите "Надстройки" на панели навигации.




• В раскрывающемся списке "Управление" выберите "Надстройки Excel" и нажмите "Перейти".




• В диалоговом окне "Надстройки" выберите "Пакет анализа" и нажмите "ОК".




При этом будет установлен пакет инструментов анализа, доступ к которому можно получить на вкладке "Данные" в группе "Анализ".




Создание гистограммы с помощью пакета инструментов анализа данных

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

Предположим, у вас есть набор данных, как показано ниже. Он имеет оценки (из 100) 40 студентов по предмету.




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

В приведенном выше наборе данных бинами будут интервалы меток.

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




Теперь, когда у нас есть все данные, давайте посмотрим, как создать гистограмму, используя эти данные:

• Перейдите на вкладку "Данные".
• В группе "Анализ" нажмите "Анализ данных".




• В диалоговом окне "Анализ данных" выберите из списка пункт "Гистограмма".




• Нажмите "ОК".
• В диалоговом окне "Гистограмма":
  • Выберите диапазон ввода (все метки в нашем примере)
  • Выберите диапазон ячеек (ячейки D2:D7)
  • Не устанавливайте флажок "Ярлыки" (необходимо установить его, если вы включили ярлыки в выборку данных).
  • Укажите выходной диапазон, если вы хотите получить гистограмму на том же рабочем листе.В противном случае выберите параметр «Новый лист/книга», чтобы получить его на отдельном листе/книге.
  • Выберите вывод диаграммы.

  
  

  Это позволит вставить таблицу частотного распределения и диаграмму в указанное место.

  
  

  Теперь есть кое-что, что вам нужно знать о гистограмме, созданной с помощью пакета инструментов анализа:

  • Первая ячейка включает все значения ниже нее. В этом случае 35 показывает 3 значения, указывающие на то, что трое учащихся набрали меньше 35 баллов.
  • Последняя указанная ячейка — 90, однако Excel автоматически добавляет еще одну ячейку — «Дополнительно». Этот бин будет включать любую точку данных, которая находится после последнего указанного бина. В данном примере это означает, что есть 2 студента, набравших более 90 баллов.
    • Обратите внимание, что даже если я добавлю к последней ячейке значение 100, эта дополнительная ячейка все равно будет создана.
    • Диаграмма по умолчанию не всегда имеет наилучший формат. Вы можете изменить форматирование, как и любую другую обычную диаграмму.

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

    Создание гистограммы с использованием функции FREQUENCY

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

    В этом разделе вы узнаете, как использовать функцию ЧАСТОТА для создания динамической гистограммы в Excel.

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

    
    

    Вот функция, которая будет вычислять частоту для каждого интервала:

    Поскольку это формула массива, вам нужно использовать Control + Shift + Enter, а не просто Enter.

    Вот шаги, которые помогут вам получить правильный результат:

    • Выберите все ячейки, прилегающие к корзинам. В данном случае это E2:E8.
    • Нажмите F2, чтобы войти в режим редактирования ячейки E2.
    • Введите формулу частоты: =ЧАСТОТА(B2:B41;D2:D8)
    • Нажмите Control + Shift + Ввод.

    
    

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

    
    

    Вот некоторые важные вещи, которые необходимо знать при использовании функции ЧАСТОТА:

    • Результатом является массив, и вы не можете удалить часть массива. При необходимости удалите все ячейки с частотной функцией.
    • Если бин равен 35, функция частоты вернет результат, который включает 35. Таким образом, 35 ​​означает оценку до 35, а 50 – оценку от 35 до 50.

    Кроме того, допустим, вы хотите, чтобы указанные интервалы данных были до 80, и вы хотите сгруппировать все результаты выше 80 вместе, вы можете сделать это с помощью функции ЧАСТОТА. В этом случае выберите на одну ячейку больше, чем количество интервалов. Например, если у вас есть 5 ячеек, выберите 6 ячеек, как показано ниже:

    
    

    Функция FREQUENCY автоматически вычисляет все значения выше 80 и возвращает количество.

    В Excel вы можете использовать инструмент анализа данных гистограммы, чтобы создать частотное распределение и, при необходимости, диаграмму гистограммы.Частотное распределение показывает, как значения в наборе данных распределяются по категориям. Гистограмма показывает ту же информацию в симпатичной маленькой столбчатой ​​диаграмме. Вот пример того, как все это работает — все станет понятнее, если вы сейчас запутались.

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

    На этом листе показана информация о ячейках в диапазоне E1:E12. Информация о ячейках показывает Excel, какие именно ячейки (категории) вы хотите использовать для классификации данных о продажах. Например, информация о ячейках, отображаемая на рабочем листе в диапазоне E1:E12, создает ячейки по сотням единиц: 0–100, 101–200, 201–300 и т. д.

    
    

    Чтобы создать частотное распределение и гистограмму, выполните следующие действия:

    Нажмите кнопку команды "Анализ данных" на вкладке "Данные", чтобы указать Excel, что вы хотите создать частотное распределение и гистограмму.

    Когда Excel отобразит диалоговое окно "Анализ данных", выберите "Гистограмма" в списке "Инструменты анализа" и нажмите "ОК".

    В появившемся диалоговом окне "Гистограмма" укажите данные, которые вы хотите проанализировать.

    Используйте текстовое поле «Входной диапазон», чтобы указать данные, которые вы хотите использовать для создания частотного распределения и гистограммы. Например, если вы хотите создать частотное распределение и гистограмму данных о единичных продажах, введите диапазон рабочего листа $B$1:$B$38 в текстовое поле Диапазон ввода.

    
    

    Чтобы определить бины, которые вы используете для частотного распределения и гистограммы, введите диапазон рабочего листа, который содержит бины, в текстовое поле Bin Range. В случае листа примера диапазон ячеек равен $E$1:$E$12.

    Если ваши диапазоны данных содержат метки, установите флажок Ярлыки.

    Укажите Excel, где разместить частотное распределение и гистограмму.

    Используйте кнопки «Параметры вывода», чтобы указать Excel, где следует размещать частотное распределение и гистограмму. Например, чтобы поместить гистограмму на текущий рабочий лист, выберите переключатель «Диапазон вывода» и введите адрес диапазона в соответствующее текстовое поле «Диапазон вывода».

    Чтобы поместить частотное распределение и гистограмму на новый рабочий лист, выберите переключатель New Worksheet Ply. Затем, при желании, введите имя рабочего листа в текстовое поле New Worksheet Ply. Чтобы поместить информацию о частотном распределении и гистограмме в новую книгу, выберите переключатель «Новая книга».

    (Необязательно) Настройте гистограмму.

    Выберите варианты с помощью флажков «Параметры вывода», чтобы указать, какую гистограмму создает Excel. Например, установите флажок Парето (отсортированная гистограмма), и Excel отсортирует интервалы в порядке убывания. И наоборот, если вы не хотите, чтобы интервалы сортировались в порядке убывания, оставьте флажок Парето (отсортированная гистограмма) снятым.

    Установив флажок "Накопленные проценты", Excel начертит линию, показывающую совокупные проценты на гистограмме.

    При необходимости установите флажок Вывод диаграммы, чтобы Excel включил гистограмму с частотным распределением. Если вы не установите этот флажок, вы не получите гистограмму — только частотное распределение.

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

    Целью этого руководства является создание гистограммы и проверка того, соответствует ли образец отрицательному биномиальному распределению, с помощью инструмента подбора распределения XLSTAT в Excel. Это распределение часто используется для представления феномена агрегации/рассеяния бактерий в водной среде.

    Данные для создания гистограммы и подбора распределения

    Лист Excel с данными и результатами можно загрузить выше.

    Данные соответствуют эксперименту, в ходе которого 200 проб речной воды были культивированы на среде с питательными веществами для определения наличия или отсутствия бактериального загрязнения Escherichia coli. Количество колоний подсчитывали через 72 часа инкубации. В столбце Bact-Data вы найдете количество для 200 образцов.

    Настройка диалогового окна для создания гистограммы

    Открыв XLSTAT, выберите команду XLSTAT / Визуализация данных / Гистограммы (см. ниже).

    
    

    Откроется диалоговое окно. Выберите данные на листе Excel с именем Данные. На вкладке «Общие» выберите столбец B в поле «Данные».Мы активируем дискретную опцию, потому что счетчики являются дискретными значениями. Параметр «Ярлыки проб» остается активированным, поскольку первая строка выборки данных содержит название пробы.

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

    Интерпретация гистограммы

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

    
    

    На гистограмме видно, что наиболее частым значением является 0, что составляет более 20% данных. То есть более чем в одном образце из пяти бактерии не обнаружены. Мы также замечаем, что частота быстро уменьшается. В одном образце было подсчитано более 36 колоний. В следующем видео показано, как это сделать.

    Создание гистограммы с указанием границ интервалов

    Поскольку мы хотим проверить соответствие между функцией отрицательного биномиального распределения и образцом (критерий хи-квадрат требует, чтобы в классе было не менее 5 данных), а также из-за неопределенной точности подсчета бактерий , представляется необходимым сгруппировать счетчики в более крупные классы. По этой причине мы создаем список границ, который кажется соответствующим нашей задаче: 0,1,2,3,4,5,10,15,20,40. Чтобы убедиться, что частоты новых классов больше 5 и регулярно уменьшаются, мы создаем новую гистограмму, указав на этот раз границы интервалов на вкладке «Параметры».

    
    

    Вычисления начинаются после того, как вы нажмете кнопку OK и появится новая гистограмма (см. лист "Гистограмма1").

    
    

    В следующем видео показано, как воспроизвести эти результаты.

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

    Настройка диалогового окна в соответствии с дистрибутивом

    Выберите команду XLSTAT / Данные моделирования / Распределительный фитинг (см. ниже).

    
    

    Появится диалоговое окно «Распределительный фитинг». Выберите данные на листе Excel с именем Данные.

    На вкладке "Общие" выберите столбец B в поле "Данные". Мы позволим XLSTAT оценить параметры функции отрицательного биномиального распределения. XLSTAT предлагает две разные формулировки отрицательного биномиального распределения. Адаптированный к нашему случаю — второй.

    
    

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

    
    

    Выберите следующие параметры на вкладке "Диаграммы".

    
    

    Интерпретация результатов анализа подбора распределения

    Первый интересующий нас результат — это значение параметров k и p отрицательного биномиального распределения (подобранное с использованием метода максимального правдоподобия), а также оценки выборочного и теоретического среднего, дисперсии, асимметрии и эксцесса. Чем ближе эти статистические данные, полученные из данных и из параметров, тем лучше соответствие. Здесь посадка отличная. Примечание. Теоретическое среднее дается kp, а дисперсия - kp(p+1).

    Тест согласия хи-квадрат позволяет проверить, превышает ли расстояние хи-квадрат между эмпирической и теоретической функциями распределения критическое значение или нет. Визуальное сравнение наблюдаемой и теоретической частот доступно на следующем рисунке. Для классов 2, 6 и 7, кажется, есть небольшая разница. Несмотря на эту небольшую разницу, значение p, рассчитанное для теста (0,770), значительно выше выбранного нами уровня значимости (0,05). Таким образом, критерий хи-квадрат подтверждает нашу гипотезу о том, что данные следуют отрицательному биномиальному распределению. Таким образом, наличие представляющих интерес бактерий в реке, из которой был собран образец, соответствует отрицательному биномиальному распределению (k = 0,823, p = 5,921) со средним значением 4,8 и дисперсией 33,4.

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

    До Excel 2016 создание гистограммы было немного утомительным.Но теперь вы можете сделать это за считанные секунды.

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

    
    

    Каспер Лангманн, соучредитель Spreadsheeto

    *Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.

    Оглавление

    Бесплатное видео на гистограммах

    Посмотрите мое видео и узнайте, как использовать гистограммы всего за 2,5 минуты.