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

Гистограмма – это мощный инструмент визуализации данных, который позволяет представить распределение частот значений в наборе данных. Она особенно полезна для выявления закономерностей, трендов и выбросов. В этой статье мы подробно рассмотрим, как построить гистограмму в двух популярных табличных редакторах: Microsoft Excel и Google Sheets.

## Что такое гистограмма?

Прежде чем приступить к построению, важно понимать, что именно представляет собой гистограмма. Гистограмма – это столбчатая диаграмма, где:

* **По оси X (горизонтальной оси)** расположены интервалы (классы или бины), представляющие диапазоны значений данных.
* **По оси Y (вертикальной оси)** отображается частота, то есть количество значений, попадающих в каждый интервал.

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

**Когда использовать гистограмму?**

Гистограммы особенно полезны в следующих случаях:

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

## Построение гистограммы в Microsoft Excel

Excel предлагает несколько способов построения гистограммы. Рассмотрим наиболее распространенные.

**Способ 1: Использование встроенной функции “Гистограмма” в пакете анализа данных**

Этот способ наиболее удобен, если у вас установлен пакет анализа данных (Data Analysis Toolpak). Если он не установлен, необходимо его активировать.

**Шаг 1: Активация пакета анализа данных (если он еще не активирован)**

1. Перейдите на вкладку **Файл** (File).
2. Выберите **Параметры** (Options).
3. В окне “Параметры Excel” перейдите в раздел **Надстройки** (Add-Ins).
4. В поле “Управление” (Manage) выберите **Надстройки Excel** (Excel Add-ins) и нажмите кнопку **Перейти…** (Go).
5. В появившемся окне установите флажок напротив **Пакет анализа** (Analysis Toolpak) и нажмите **OK**.

Теперь вкладка **Данные** (Data) должна содержать группу **Анализ** (Analysis).

**Шаг 2: Подготовка данных**

1. Введите данные, для которых вы хотите построить гистограмму, в столбец Excel. Например, в столбец A.
2. Определите интервалы (бины). Интервалы определяют диапазоны значений, которые будут сгруппированы в столбцах гистограммы. Интервалы можно задать вручную в отдельном столбце, либо позволить Excel автоматически определить их (в этом случае Excel использует статистические методы для определения оптимального количества и ширины интервалов).

* **Ручное задание интервалов:** Создайте новый столбец (например, столбец B) и введите верхние границы каждого интервала. Например, если вы хотите интервалы 0-10, 10-20, 20-30 и т.д., введите 10, 20, 30 и т.д. в столбец B. Важно, чтобы интервалы были непрерывными и не перекрывались.
* **Автоматическое определение интервалов:** Пропустите этот шаг, если вы хотите, чтобы Excel автоматически определил интервалы.

**Шаг 3: Запуск функции “Гистограмма”**

1. Перейдите на вкладку **Данные** (Data).
2. В группе **Анализ** (Analysis) нажмите кнопку **Анализ данных** (Data Analysis).
3. В появившемся окне выберите **Гистограмма** (Histogram) и нажмите **OK**.

**Шаг 4: Настройка параметров гистограммы**

В окне “Гистограмма” необходимо указать следующие параметры:

* **Входной диапазон (Input Range):** Укажите диапазон ячеек, содержащих ваши данные. Например, `$A$1:$A$100`.
* **Диапазон карманов (Bin Range):** Если вы задали интервалы вручную, укажите диапазон ячеек, содержащих верхние границы интервалов. Например, `$B$1:$B$10`. Если вы хотите, чтобы Excel автоматически определил интервалы, оставьте это поле пустым.
* **Метки (Labels):** Установите этот флажок, если в первой строке вашего входного диапазона содержатся заголовки столбцов.
* **Выходные параметры (Output options):** Укажите, куда вы хотите поместить результаты гистограммы:
* **Выходной диапазон (Output Range):** Укажите ячейку, которая будет верхним левым углом таблицы с результатами.
* **Новый рабочий лист (New Worksheet):** Результаты будут помещены на новый рабочий лист.
* **Новая книга (New Workbook):** Результаты будут помещены в новую книгу Excel.
* **Интегральный процент (Cumulative Percentage):** Установите этот флажок, чтобы отобразить кумулятивную частоту на гистограмме.
* **Вывод графика (Chart Output):** Установите этот флажок, чтобы создать график гистограммы.

**Шаг 5: Создание гистограммы**

После настройки всех параметров нажмите кнопку **OK**. Excel создаст таблицу частот и, если вы установили флажок “Вывод графика”, также создаст гистограмму.

**Способ 2: Использование функции ЧАСТОТА (FREQUENCY)**

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

**Шаг 1: Подготовка данных и интервалов (бинов)**

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

**Шаг 2: Использование функции ЧАСТОТА**

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

**Шаг 3: Создание гистограммы**

1. Выделите диапазон ячеек, содержащих частоты (включая дополнительную ячейку, которая содержит частоту значений, превышающих верхнюю границу последнего интервала).
2. Перейдите на вкладку **Вставка** (Insert).
3. В группе **Диаграммы** (Charts) выберите **Столбчатая диаграмма** (Column Chart) или **Гистограмма** (Histogram, если она доступна, в более новых версиях Excel). Обычно выбирают простую столбчатую диаграмму.
4. Excel создаст столбчатую диаграмму. Возможно, потребуется настроить внешний вид диаграммы, например, удалить промежутки между столбцами, чтобы она больше походила на гистограмму. Для этого щелкните правой кнопкой мыши на одном из столбцов диаграммы и выберите **Формат ряда данных** (Format Data Series). В разделе “Параметры ряда” (Series Options) установите значение “Боковой зазор” (Gap Width) на 0%.

## Построение гистограммы в Google Sheets

Google Sheets предлагает более простой способ построения гистограммы, чем Excel, благодаря встроенной функции `HISTOGRAM`.

**Шаг 1: Подготовка данных**

Введите данные, для которых вы хотите построить гистограмму, в столбец Google Sheets. Например, в столбец A.

**Шаг 2: Использование функции HISTOGRAM**

1. Выберите ячейку, в которую вы хотите поместить гистограмму (или куда-нибудь рядом с данными, чтобы было удобно видеть результат).
2. Введите формулу `=HISTOGRAM(данные, [количество_интервалов])`, где `данные` – это диапазон ячеек, содержащих ваши данные, а `количество_интервалов` – это необязательный аргумент, который указывает количество интервалов в гистограмме. Если этот аргумент не указан, Google Sheets автоматически определит оптимальное количество интервалов. Например, `=HISTOGRAM(A1:A100, 10)` создаст гистограмму с 10 интервалами.

**Шаг 3: Настройка гистограммы**

После ввода формулы Google Sheets автоматически создаст гистограмму. Чтобы настроить внешний вид гистограммы, щелкните на ней и используйте панель “Редактор диаграмм” (Chart editor), которая появится справа.

* **Тип диаграммы (Chart type):** Убедитесь, что выбран тип “Гистограмма” (Histogram).
* **Данные (Data):** Здесь можно изменить диапазон данных, используемых для гистограммы.
* **Настройка (Customize):** Этот раздел позволяет настроить различные аспекты гистограммы, такие как:
* **Стиль диаграммы (Chart style):** Изменить цвет фона, шрифты и другие элементы стиля.
* **Названия и оси (Chart & axis titles):** Добавить или изменить названия диаграммы и осей.
* **Серии (Series):** Изменить цвет столбцов, добавить линии тренда и т.д.
* **Легенда (Legend):** Настроить отображение легенды.
* **Горизонтальная ось (Horizontal axis):** Настроить отображение меток, минимальное и максимальное значения.
* **Вертикальная ось (Vertical axis):** Настроить отображение меток, минимальное и максимальное значения.
* **Карманы (Buckets):** Здесь можно более точно настроить интервалы гистограммы. В частности, можно указать `Размер кармана` (Bucket size), то есть ширину каждого интервала.

## Советы и рекомендации

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

* **Правило Стерджеса:** `k = 1 + 3.322 * log(n)`, где `k` – количество интервалов, а `n` – количество данных.
* **Правило Скотта:** `h = 3.5 * s / n^(1/3)`, где `h` – ширина интервала, `s` – стандартное отклонение данных, а `n` – количество данных. Количество интервалов можно получить как `(max(x) – min(x)) / h`, где `x` – ваши данные.
* **Ширина интервалов:** Чаще всего интервалы делают равной ширины, но в некоторых случаях может быть полезно использовать интервалы разной ширины, особенно если данные распределены неравномерно.
* **Анализ гистограммы:** Обратите внимание на форму гистограммы. Является ли она симметричной (нормальное распределение)? Есть ли скос (влево или вправо)? Есть ли несколько пиков (бимодальное или мультимодальное распределение)? Есть ли выбросы?
* **Интерактивные гистограммы:** В некоторых инструментах визуализации данных (например, в Power BI) можно создавать интерактивные гистограммы, которые позволяют фильтровать данные и изменять интервалы в режиме реального времени.

## Заключение

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