Как сделать гистограмму в Excel (пошаговое руководство)

Посмотреть видео - 3 способа создания гистограммы в Excel

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

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

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

Есть разные способы создания гистограммы в Excel:

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

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

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

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

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

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

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

1. Выберите весь набор данных.
2. Щелкните вкладку Вставка.
3. В группе «Диаграммы» нажмите «Вставить статическую диаграмму».
4. В группе «Гистограмма» щелкните значок диаграммы «Гистограмма».

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

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

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

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

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» и нажмите «Перейти».

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

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

Создание гистограммы с помощью Data Analysis Toolpak

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

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

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

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

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

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

• Щелкните вкладку Данные.
• В группе Анализ щелкните Анализ данных.

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

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

• Щелкните ОК.

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

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

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

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

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

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

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

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

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

= ЧАСТОТА (B2: B41; D2: D8)

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

Вот шаги, чтобы убедиться, что вы получите правильный результат:

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

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

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

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

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

Функция ЧАСТОТА автоматически вычислит все значения выше 80 и вернет счетчик.