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

Гистограмма – это мощный инструмент визуализации данных, позволяющий представить распределение частот значений в наборе данных. Она особенно полезна для анализа больших объемов информации, помогая выявить закономерности, выбросы и общую форму распределения. Excel предлагает встроенные функции и инструменты для создания гистограмм, что делает его доступным и удобным для пользователей любого уровня подготовки.

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

* **Что такое гистограмма и зачем она нужна**
* **Подготовка данных для гистограммы**
* **Создание простой гистограммы с помощью инструмента “Анализ данных”**
* **Настройка гистограммы: интервалы, заголовки, оси**
* **Использование функции ЧАСТОТА для построения гистограммы**
* **Создание гистограммы с использованием сводных таблиц (PivotTables)**
* **Продвинутые техники: создание гистограмм с несколькими рядами данных, накопленные гистограммы**
* **Альтернативные инструменты для построения гистограмм в Excel**
* **Примеры использования гистограмм в различных областях**
* **Советы и рекомендации по созданию эффективных гистограмм**

## Что такое гистограмма и зачем она нужна

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

**Зачем нужна гистограмма?**

* **Визуализация распределения данных:** Гистограмма позволяет быстро увидеть, как распределены данные – равномерно, нормально, скошено вправо или влево, бимодально и т.д.
* **Выявление закономерностей:** Можно обнаружить кластеры значений, пики и другие интересные особенности в данных.
* **Обнаружение выбросов:** Значения, которые значительно отличаются от остальной части данных, легко обнаруживаются на гистограмме.
* **Сравнение распределений:** Можно сравнить распределения двух или более наборов данных.
* **Принятие решений:** На основе анализа гистограммы можно принимать обоснованные решения в различных областях, таких как контроль качества, анализ рынка, научные исследования и т.д.

## Подготовка данных для гистограммы

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

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

**Как выбрать интервалы?**

Существует несколько правил и рекомендаций по выбору интервалов:

* **Правило Стерджеса:** Количество интервалов (k) можно оценить по формуле: k = 1 + 3.322 * log10(n), где n – количество значений в наборе данных. Это правило является отправной точкой и может быть скорректировано в зависимости от конкретных данных.
* **Квадратный корень:** Количество интервалов можно оценить как квадратный корень из количества значений.
* **Попробуйте разные варианты:** Лучший способ выбрать интервалы – это попробовать разные варианты и посмотреть, какая гистограмма наиболее информативна.

**Пример:**

Допустим, у вас есть набор данных о росте людей (в сантиметрах):

165, 170, 175, 180, 185, 160, 172, 178, 182, 188, 168, 174, 179, 184, 190

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

* 160-165
* 165-170
* 170-175
* 175-180
* 180-185
* 185-190

Либо можно использовать только верхнюю границу каждого интервала: 165, 170, 175, 180, 185, 190. Excel будет использовать эти значения как границы интервалов.

## Создание простой гистограммы с помощью инструмента “Анализ данных”

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

1. **Файл > Параметры > Надстройки.**
2. В выпадающем списке “Управление” выберите “Надстройки Excel” и нажмите кнопку “Перейти…”.
3. В открывшемся окне поставьте галочку напротив “Пакет анализа” и нажмите кнопку “ОК”.

После установки пакета анализа, он появится на вкладке “Данные” в группе “Анализ”.

**Шаги для создания гистограммы с помощью инструмента “Анализ данных”:**

1. **Подготовьте данные:** Убедитесь, что данные находятся в одном столбце, как описано выше.
2. **Создайте столбец с интервалами (ячейками):** Создайте столбец с верхними границами интервалов, как описано выше. Этот шаг необязателен. Если его пропустить, Excel автоматически определит интервалы.
3. **Перейдите на вкладку “Данные” и нажмите кнопку “Анализ данных”.**
4. **В открывшемся окне выберите “Гистограмма” и нажмите кнопку “ОК”.**
5. **В появившемся диалоговом окне укажите:**
* **Входной интервал:** Выберите диапазон ячеек, содержащих данные.
* **Интервал ячеек:** Выберите диапазон ячеек, содержащих верхние границы интервалов (если вы создали такой столбец). Если оставить это поле пустым, Excel автоматически определит интервалы.
* **Метка:** Поставьте галочку, если первая строка во входном интервале и интервале ячеек содержит заголовки столбцов.
* **Выходные параметры:** Укажите, куда вывести результаты: на новый лист, в новую книгу или в указанный диапазон на текущем листе.
* **Создать гистограмму:** Поставьте галочку, чтобы Excel автоматически создал гистограмму на основе полученных данных.
6. **Нажмите кнопку “ОК”.**

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

## Настройка гистограммы: интервалы, заголовки, оси

После создания гистограммы с помощью инструмента “Анализ данных”, ее можно настроить для улучшения внешнего вида и информативности.

**Настройка интервалов:**

* **Изменение интервалов вручную:** Если гистограмма была создана с использованием столбца с интервалами, можно изменить значения в этом столбце и гистограмма автоматически обновится. Обратите внимание, что при изменении интервалов, необходимо обновить диапазон в инструменте “Анализ данных” и перезапустить его.
* **Автоматическая настройка интервалов:** Excel автоматически определяет интервалы, если столбец с интервалами не был указан. Однако, после создания гистограммы, невозможно изменить автоматические интервалы напрямую. В этом случае, необходимо вернуться к шагу создания гистограммы и либо создать столбец с интервалами, либо попробовать другой метод построения гистограммы (например, с использованием функции ЧАСТОТА). Инструмент “Анализ данных” не предлагает гибкой настройки автоматических интервалов после создания гистограммы.

**Настройка заголовков и осей:**

* **Изменение заголовка диаграммы:** Щелкните по заголовку диаграммы и введите новый заголовок.
* **Изменение заголовков осей:** Щелкните по оси и введите новый заголовок. Можно также отформатировать ось, изменив шкалу, формат чисел и другие параметры.
* **Добавление подписей данных:** Щелкните правой кнопкой мыши по столбцу гистограммы и выберите “Добавить подписи данных”. Excel отобразит количество значений в каждом интервале над столбцами.

**Другие настройки:**

* **Изменение цвета столбцов:** Щелкните по столбцу гистограммы и выберите нужный цвет на вкладке “Формат”.
* **Изменение ширины столбцов:** Щелкните правой кнопкой мыши по столбцу гистограммы и выберите “Формат ряда данных”. В открывшемся окне можно изменить ширину зазора между столбцами.
* **Добавление линий сетки:** Добавление линий сетки может улучшить читаемость гистограммы. Выберите диаграмму, перейдите на вкладку “Макет” и добавьте линии сетки по горизонтали и вертикали.

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

Функция ЧАСТОТА – это встроенная функция Excel, которая позволяет подсчитать, сколько значений попадает в каждый интервал. Эта функция является более гибкой, чем инструмент “Анализ данных”, и позволяет более точно контролировать процесс построения гистограммы.

**Синтаксис функции ЧАСТОТА:**

`ЧАСТОТА(массив_данных;массив_интервалов)`

* `массив_данных` – это диапазон ячеек, содержащих данные.
* `массив_интервалов` – это диапазон ячеек, содержащих верхние границы интервалов.

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

1. **Подготовьте данные:** Убедитесь, что данные находятся в одном столбце.
2. **Создайте столбец с интервалами (ячейками):** Создайте столбец с верхними границами интервалов.
3. **Выделите диапазон ячеек, где будут отображаться частоты:** Этот диапазон должен быть на одну ячейку больше, чем диапазон интервалов, так как функция ЧАСТОТА возвращает также количество значений, превышающих верхнюю границу последнего интервала.
4. **Введите функцию ЧАСТОТА как формулу массива:** Введите `=ЧАСТОТА(диапазон_данных;диапазон_интервалов)` и нажмите **Ctrl+Shift+Enter**. Excel автоматически заключит формулу в фигурные скобки `{}`. **Важно: не вводите фигурные скобки вручную!**
5. **Создайте гистограмму на основе полученных частот:** Выделите диапазон ячеек с частотами и диапазон ячеек с интервалами. Перейдите на вкладку “Вставка” и выберите подходящий тип диаграммы (например, “Гистограмма” или “Столбчатая диаграмма”).

**Пример:**

Допустим, у вас есть данные о зарплатах сотрудников (в тысячах рублей) в диапазоне A1:A20, а интервалы (например, 50, 75, 100, 125) находятся в диапазоне B1:B4. Выделите диапазон C1:C5 (на одну ячейку больше, чем диапазон B1:B4), введите `=ЧАСТОТА(A1:A20;B1:B4)` и нажмите **Ctrl+Shift+Enter**. В диапазоне C1:C5 будут отображены частоты для каждого интервала.

После этого выделите диапазоны B1:B4 и C1:C4 (без последней ячейки в диапазоне C1:C5) и создайте гистограмму, как описано выше.

## Создание гистограммы с использованием сводных таблиц (PivotTables)

Сводные таблицы (PivotTables) – это мощный инструмент для анализа и обобщения данных в Excel. Их также можно использовать для создания гистограмм, особенно если требуется группировать данные по нескольким критериям.

**Шаги для создания гистограммы с использованием сводной таблицы:**

1. **Подготовьте данные:** Убедитесь, что данные находятся в одном столбце.
2. **Создайте сводную таблицу:** Выделите диапазон ячеек с данными. Перейдите на вкладку “Вставка” и нажмите кнопку “Сводная таблица”. Выберите место, где будет создана сводная таблица.
3. **Настройте сводную таблицу:** В области полей сводной таблицы перетащите столбец с данными в область “Строки” и в область “Значения”. По умолчанию, в области “Значения” будет отображаться сумма значений. Измените функцию агрегирования на “Количество”, щелкнув по полю в области “Значения”, выбрав “Параметры поля значений” и выбрав “Количество”.
4. **Сгруппируйте данные:** Щелкните правой кнопкой мыши по любому значению в столбце “Строки” сводной таблицы и выберите “Группа”. В открывшемся окне укажите начальное значение, конечное значение и размер интервала. Excel автоматически сгруппирует данные по указанным интервалам.
5. **Создайте гистограмму:** Выберите любую ячейку внутри сводной таблицы. Перейдите на вкладку “Анализ” (или “Параметры” в старых версиях Excel) и нажмите кнопку “Сводная диаграмма”. Выберите подходящий тип диаграммы (например, “Гистограмма” или “Столбчатая диаграмма”).

## Продвинутые техники: создание гистограмм с несколькими рядами данных, накопленные гистограммы

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

**Создание гистограммы с несколькими рядами данных:**

1. **Подготовьте данные:** Убедитесь, что каждый набор данных находится в отдельном столбце. Создайте столбец с интервалами (ячейками), который будет общим для всех наборов данных.
2. **Используйте функцию ЧАСТОТА для каждого набора данных:** Используйте функцию ЧАСТОТА для каждого столбца данных, как описано выше. Убедитесь, что для всех столбцов используется один и тот же диапазон интервалов.
3. **Создайте гистограмму:** Выделите диапазон ячеек, содержащих интервалы и частоты для всех наборов данных. Перейдите на вкладку “Вставка” и выберите подходящий тип диаграммы (например, “Гистограмма” или “Столбчатая диаграмма”).

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

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

1. **Создайте гистограмму с несколькими рядами данных, как описано выше.**
2. **Измените тип диаграммы на “Гистограмма с накоплением” или “Столбчатая диаграмма с накоплением”.** Щелкните правой кнопкой мыши по диаграмме и выберите “Изменить тип диаграммы”. Выберите подходящий тип диаграммы.

## Альтернативные инструменты для построения гистограмм в Excel

Помимо инструмента “Анализ данных” и функции ЧАСТОТА, существуют и другие способы построения гистограмм в Excel.

* **Надстройки Excel:** Существуют сторонние надстройки Excel, которые предлагают расширенные возможности для построения гистограмм и анализа данных. Некоторые из них могут предлагать более гибкую настройку интервалов, интерактивные графики и другие полезные функции.
* **Power BI:** Power BI – это мощный инструмент бизнес-аналитики от Microsoft, который позволяет создавать интерактивные дашборды и отчеты. Power BI имеет более продвинутые возможности для построения гистограмм, чем Excel, и позволяет легко подключаться к различным источникам данных.

## Примеры использования гистограмм в различных областях

Гистограммы широко используются в различных областях:

* **Контроль качества:** Для анализа распределения размеров деталей, веса продукции и других параметров для выявления дефектов и отклонений от стандартов.
* **Анализ рынка:** Для анализа распределения доходов потребителей, цен на товары и других параметров для выявления рыночных тенденций и сегментов.
* **Научные исследования:** Для анализа распределения результатов экспериментов, данных наблюдений и других параметров для проверки гипотез и выявления закономерностей.
* **Образование:** Для анализа распределения оценок студентов, результатов тестов и других параметров для оценки эффективности обучения и выявления проблемных областей.
* **Финансы:** Для анализа распределения доходности акций, процентных ставок и других параметров для оценки рисков и принятия инвестиционных решений.

## Советы и рекомендации по созданию эффективных гистограмм

* **Правильно выбирайте интервалы:** Выбор интервалов существенно влияет на внешний вид и информативность гистограммы. Попробуйте разные варианты и выберите тот, который наилучшим образом отображает распределение данных.
* **Используйте понятные заголовки и подписи осей:** Убедитесь, что заголовки и подписи осей четко и ясно описывают, что изображено на гистограмме.
* **Избегайте перегруженности:** Не добавляйте слишком много деталей на гистограмму, чтобы не затруднить ее чтение.
* **Используйте цвета умеренно:** Используйте цвета для выделения важных элементов, но не перегружайте гистограмму слишком яркими цветами.
* **Интегрируйте гистограмму в контекст:** Включайте гистограмму в отчет или презентацию с пояснениями и интерпретациями, чтобы зрители могли понять ее значение.
* **Рассмотрите альтернативные типы диаграмм:** Гистограмма – это не всегда лучший выбор для визуализации данных. В зависимости от задачи и типа данных, могут быть более подходящие типы диаграмм, такие как столбчатая диаграмма, линейная диаграмма, точечная диаграмма и т.д.

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