В этой статье мы рассмотрим, как создать гистограмму в Excel и использовать ее для анализа данных. Вы узнаете, как построить гистограмму в Excel и автоматически отсортировать значения по убыванию или возрастанию, как сделать гистограмму с группировкой или с накоплением, с отрицательными значениями, как изменить ширину и цвет ее элементов, и многое другое.
Гистограмма – это графическое представление данных, которое помогает наглядно отобразить распределение значений и частоту их появления. В Excel вы можете создать гистограмму, используя встроенные инструменты, что позволяет легко анализировать большие объемы информации и делать выводы на основе полученных данных.
Наряду с круговыми диаграммами, гистограммы являются одним из наиболее часто используемых типов диаграмм. Они просты в построении и понятны для анализа данных. Для каких данных лучше всего подходят гистограммы? Это любые числовые данные, которые вы хотите сравнить, такие как числа, проценты, температуры, деньги или другие измерения. Как правило, гистограмму создают для сравнения отдельных значений в разных категориях данных либо в динамике изменений. В программах управления проектами часто используется особый тип гистограммы, называемый диаграммой Ганта.
В этой статье мы собираемся изучить следующие аспекты гистограмм в Excel:
Гистограммы в Excel — основные элементы.
Гистограмма или столбчатая диаграмма — это график, отображающий различные категории данных с помощью прямоугольных столбцов, где длина столбцов пропорциональна величине данных, которую они представляют. Столбцы гистограммы могут быть расположены вертикально или горизонтально. Вертикальная гистограмма в Excel — это отдельный тип диаграммы, известный как столбчатая гистограмма.
Чтобы облегчить понимание этого руководства, давайте сначала рассмотрим основные элементы вертикальной гистограммы Excel.
1. Столбцы. Главный визуальный компонент гистограммы - это столбцы, которые представляют каждое значение или диапазон значений в наборе данных. Высота столбцов отображает частоту или количество наблюдений для каждого значения.
2. Оси: гистограмма имеет две оси - горизонтальную и вертикальную. Горизонтальная ось представляет значения или категории данных, а вертикальная ось представляет частоту или количество наблюдений.
На рисунке ниже показана стандартная двухмерная вертикальная гистограмма с 4 рядами данных и 5 категориями данных (январь — май).
Более подробно об этих элементах читайте здесь: Основы работы с диаграммами Excel.
Как быстро сделать гистограмму в Excel
Рассмотрим построение гистограммы пошагово.
Шаг 1: Подготовка данных
Перед тем, как создавать гистограмму, вам необходимо иметь данные, которые вы хотите исследовать. Например, если вы хотите проанализировать продажи, вам нужно иметь таблицу с цифрами продаж.
Разместите свои данные, на основе которых вы хотите построить автоматически обновляемую гистограмму, в таблице Excel, где каждая строка представляет одну запись, а каждый столбец содержит разные переменные или атрибуты, которые вы хотите анализировать.
Шаг 2: Создание гистограммы
Теперь, когда у вас есть подготовленные данные, вы можете приступить к созданию гистограммы в Excel.
1. Выделите ячейки с данными, которые вы хотите использовать чтобы построить гистограмму.
2. Откройте вкладку "Вставка" на ленте инструментов Excel.
3. В разделе "Диаграммы" выберите тип диаграммы "Гистограмма".
4. Выберите подтип гистограммы, который наиболее подходит для ваших данных.
5. Нажмите кнопку "ОК".
В этом примере мы создаем стандартную двухмерную гистограмму:
Двумерная вертикальная гистограмма с параметрами по умолчанию, вставленная на лист Excel, будет выглядеть примерно так:
Если ваши исходные данные содержат два или более столбца числовых значений, гистограмма Excel будет содержать несколько рядов данных, каждый из которых окрашен в свой цвет.
Шаг 3: Анализ данных
Теперь, когда у вас есть гистограмма на основе ваших данных, вы можете использовать ее для анализа.
1. Изучите форму гистограммы. Она может помочь вам определить форму распределения данных - нормальное, скошенное влево или вправо, равномерное и т.д. Вы можете определить, какие значения являются наиболее частыми и как они соотносятся с другими значениями в наборе данных. Это может помочь вам выявить тренды и тенденции, которые могут быть полезны для прогнозирования и планирования.
2. Определите пики и просадки в гистограмме. Это может указывать на наличие выбросов или особенности в данных. Если вы видите столбец, который значительно выделяется из остальных, это может указывать на проблему или аномалию в вашем бизнесе. Это может быть признаком неэффективности или необычного поведения, которое требует дальнейшего изучения и принятия решений.
3. Сравните гистограммы разных групп или категорий данных. Это позволит вам увидеть различия и сходства между ними.
Подбираем параметры гистограммы.
А теперь рассмотрим, как вы можете сделать свою гистограмму лучше, чтобы было удобнее ее анализировать.
Просмотрите все доступные типы гистограмм.
Чтобы просмотреть все типы гистограмм, доступные в Excel, перейдите на закладку «Все диаграммы» и выберите один из подтипов гистограмм, отображаемых в меню «Вставка – Рекомендуемые диаграммы»:
Или же просто нажмите значок гистограммы рядом.
Выберите макет и стиль гистограммы
Если вас не полностью устраивает макет или стиль гистограммы по умолчанию, на основе которого вы построили вашу гистограмму, кликните на ней мышкой, чтобы активировать вкладку Конструктор диаграмм на ленте. После этого поэкспериментируйте с различными стилями гистограмм в группе «Стили диаграмм» и выберите оптимальное для вас форматирование.
Чтобы посмотреть больше шаблонов гистограмм, кликните по стрелке справа.
Типы гистограмм.
Когда вы создаете гистограмму, вы можете вместо обычной гистограммы выбрать один из следующих ее подвидов.
Гистограмма с накоплением
Гистограмма с накоплением в Excel дополнительно позволяет наглядно отобразить накопленную сумму значений в каждом столбце ваших исходных данных.
Построить такую гистограмму особенно полезно, когда ваши данные имеют временную или иерархическую структуру. Вы можете использовать гистограмму с накоплением, чтобы увидеть, как меняется распределение значений во времени или в различных категориях. Например, на скриншоте ниже вы наглядно видите, как меняется не только сумма, но и вклад отдельных продуктов в общую сумму продаж в каждом месяце.
Кроме того, гистограмма с накоплением может помочь вам сравнить распределение значений нескольких наборов данных на одной диаграмме. Вы можете легко определить, какие значения более значимы и какие менее значимы в каждом наборе данных. В данном случае, мы наглядно видим, какой из товаров внес наибольший вклад в продажи в каждом месяце.
Часто гистограмма с накоплением предоставляет более полную информацию о распределении значений и упрощает анализ данных в Excel.
Нормированная гистограмма с накоплением
Нормированная гистограмма с накоплением в Excel детально показывает долю отдельных элементов в общей сумме по каждому столбцу (в нашем случае – в каждом месяце).
Этот тип гистограмм аналогичен предыдущему типу, но он отображает долю каждого значения внутри каждой категории данных. На рисунке ниже вы можете узнать, как изменяется доля каждого продукта в общей сумме продаж по месяцам. При этом общей суммы продаж мы здесь не видим.
Линейчатая гистограмма.
Линейчатая гистограмма в Excel представляет собой графическое представление данных, где данные распределены по категориям и отображаются в виде горизонтальных прямоугольных столбцов. Она так же используется для отображения частоты или количественного распределения данных. На вертикальной оси обычно отображаются категории или группы данных, а на горизонтальной оси - значения, представляющие частоту или количество.
На скриншоте ниже мы использовали уже знакомые вам данные для создания линейчатой гистограммы. Как видите, это то же самое, что обычная гистограмма, просто повернута набок.
Естественно, в этом виде вам доступны также нормированная линейчатая и нормированная линейчатая гистограмма с накоплением, о которых мы говорили выше.
Трехмерная гистограмма.
Выбирая тип диаграммы, как мы это уже делали ранее, вы также можете выбрать объемный вид. Вы можете видеть это на скриншоте ниже.
Объемный вид доступен и для обычной, и для линейчатой диаграммы.
Цилиндрические, конусные и пирамидальные диаграммы
Как и стандартные прямоугольные гистограммы Excel, конусные, цилиндрические и пирамидальные диаграммы доступны в обычных, нормированных и нормированных гистограмм с накоплением.
Единственное отличие состоит в том, что эти типы диаграмм представляют ряды данных в форме цилиндра, конуса и пирамиды вместо прямоугольников.
В Excel 2010 и более ранних версиях вы можете создать цилиндрическую, конусную или пирамидальную диаграмму обычным способом, выбрав соответствующий тип диаграммы в группе Диаграммы на вкладке Вставка.
При создании гистограммы в Excel 2013 и более поздних версиях, вы не найдете тип цилиндра, конуса или пирамиды в группе «Диаграммы» на ленте. Согласно заявлений Microsoft, эти типы диаграмм были удалены, потому что в более ранних версиях Excel было слишком много вариантов диаграмм, из-за чего пользователю было трудно выбрать правильный вид. Тем не менее, в современных версиях Excel есть способ нарисовать график в виде цилиндра, конуса или пирамиды, но для этого потребуется несколько дополнительных действий.
Чтобы построить цилиндрическую, конусную или пирамидальную диаграмму, создайте трехмерную линейчатую диаграмму предпочитаемого типа (стандартную или с накоплением) обычным способом, а затем измените тип фигуры следующим образом:
- Выберите столбцы на диаграмме, щелкните их правой кнопкой мыши и выберите «Формат ряда данных...» в контекстном меню. Или просто дважды щелкните столбцы, чтобы открылась панель форматирования.
- На панели «Формат ряда данных» в разделе «Параметры ряда» выберите нужную форму столбца.
Примечание. Если на гистограмме Excel имеется несколько рядов данных, вам может потребоваться повторить описанные выше шаги для каждого ряда.
Стиль и внешний вид гистограммы в Excel
Как и другие типы диаграмм Excel, гистограммы допускают множество вариантов форматирования в отношении заголовка диаграммы, осей, меток данных и т. д. Ряд рекомендаций по настройке внешнего вида диаграмм вы можете посмотреть в этой статье: Заголовок диаграммы, легенда, оси, метки данных и другие настройки.
А теперь давайте подробнее рассмотрим отдельные настройки гистограмм в Excel.
Изменить ширину столбцов и расстояние между ними
Когда вы строите гистограмму в Excel, настройки по умолчанию таковы, что между столбцами остается довольно много места. Чтобы сделать полосы шире и поместить их ближе друг к другу, выполните действия, описанные ниже. Тот же метод можно использовать, чтобы сделать столбики тоньше и увеличить расстояние между ними. В двухмерных гистограммах столбцы могут даже перекрывать друг друга. Итак, пошагово:
- На диаграмме Excel щелкните правой кнопкой мыши любой ряд данных и выберите «Формат ряда данных...» в контекстном меню. Либо просто сделайте двойной щелчок по ряду данных.
- На панели «Формат ряда данных» в разделе «Параметры ряда» выполните одно из следующих действий.
- Чтобы изменить ширину столбцов и расстояние между категориями данных, перетащите ползунок «Боковой зазор» или введите процентное значение от 0 до 500. Чем меньше это значение, тем меньше зазор между категориями и толще полосы, и наоборот.
- В двухмерных гистограммах, чтобы изменить расстояние между рядами данных внутри каждой категории данных, перетащите ползунок «Перекрытие рядов» или введите процентное значение от -100 до 100. Чем выше значение, тем больше полосы перекрываются. Отрицательное число приведет к возникновению зазора между рядами данных, как показано на скриншоте выше.
- В трехмерных диаграммах, чтобы изменить интервал между рядами данных, перетащите ползунок Фронтальный зазор или введите процентное значение от 0 до 500. Чем выше значение, тем больше разрыв между рядами.
- Расстояние между категориями в объемных гистограммах регулируется ползунком Боковой зазор.
Как создать гистограмму Excel с отрицательными значениями
Для начала отметим, что исходные значения для гистограммы не обязательно должны быть больше нуля. Как правило, в Excel нет проблем с отображением отрицательных чисел на стандартной гистограмме, однако построенная диаграмма по умолчанию может оставлять желать лучшего с точки зрения макета и форматирования:
Как видите, подписи по горизонтальной оси местами перекрываются столбиками данных.
Чтобы приведенная выше гистограмма выглядела лучше, во-первых, вы можете переместить метки горизонтальной оси вниз, чтобы они не накладывались на отрицательные столбцы, а во-вторых, вы можете рассмотреть возможность использования разных цветов для отрицательных значений. Об этом – далее.
Изменение меток горизонтальной оси
Чтобы отформатировать горизонтальную ось, щелкните правой кнопкой мыши любую из ее меток и выберите «Формат оси...» в контекстном меню (или просто дважды щелкните метки оси). Это приведет к тому, что панель «Формат оси» появится в правой части рабочего листа.
На панели перейдите на вкладку «Параметры оси» (самая правая), разверните раздел «Подписи» и установите для параметра «Положение подписи» значение «Внизу»:
Вот как теперь выглядит наша гистограмма с отрицательными значениями. Как видите, подписи горизонтальной оси больше не перекрываются столбцами данных.
О других способах изменить внешний вид гистограммы в соответствии со своими предпочтениями вы можете прочитать в этом руководстве: Заголовок диаграммы, легенда, оси, метки данных и другие настройки.
Изменение цвета заливки для отрицательных значений
Если вы хотите привлечь внимание к отрицательным значениям на гистограмме Excel, изменение цвета заливки отрицательных столбцов сделает их более заметными.
Если на линейчатой диаграмме Excel имеется только один ряд данных, отрицательные значения можно закрасить стандартным красным цветом. Если ваша гистограмма содержит несколько рядов данных, вам придется заштриховывать отрицательные значения в каждом ряду разным цветом. Например, вы можете сохранить исходные цвета для положительных значений и использовать более светлые оттенки тех же цветов для отрицательных значений.
Чтобы изменить цвет отрицательных столбиков, выполните следующие действия:
- Щелкните правой кнопкой мыши любой столбец в ряду данных, цвет которого вы хотите изменить (синие столбцы в этом примере), и выберите «Формат ряда данных...» в контекстном меню. Либо просто сделайте двойной клик.
- На панели «Формат ряда данных» на вкладке «Заливка» установите флажок «Инвертировать, если <0 ».
- Как только вы поставите галочку в поле «Инверсия для чисел <0», вы должны увидеть два варианта цвета заливки: первый для положительных значений и второй для отрицательных значений.
Совет. Если второе поле заливки не отображается, щелкните маленькую черную стрелку в единственной опции цвета, которую вы видите, и выберите любой цвет, который вы хотите для положительных значений (вы можете выбрать тот же цвет, который был применен по умолчанию). Как только вы это сделаете, появится второй вариант цвета для отрицательных значений.
Сортировка данных на гистограммах в Excel
Когда вы создаете линейчатую гистограмму, по умолчанию категории данных отображаются на диаграмме в обратном порядке. То есть, если вы отсортируете данные от A до Z в электронной таблице, на гистограмме Excel они будут отображаться как от Z до A. Почему Excel всегда размещает категории данных в гистограммах задом наперед? Никто не знает. Но мы знаем, как это исправить :)
Самый простой способ изменить порядок категорий данных на линейчатой диаграмме — выполнить обратную сортировку на листе.
Давайте попробуем использовать простые данные, чтобы проиллюстрировать это. В примере ниже вы видите список из 10 крупнейших городов мира, отсортированных по населению в порядке убывания, от самого большого к меньшему. Однако на гистограмме данные отображаются в порядке возрастания, от наименьшего к наибольшему:
Чтобы гистограмма Excel была отсортирована сверху вниз, вы при помощи меню Данные – Сортировка и фильтр, просто упорядочиваете исходные данные в обратном порядке, то есть от меньшего к большему:
Если же сортировка данных на листе по каким-то причинам невозможна, вы можете изменить порядок данных в гистограмме Excel без изменения источника данных.
Сортировка гистограммы Excel по убыванию/возрастанию без сортировки исходных данных
Если порядок сортировки на вашем листе имеет значение и не может быть изменен, давайте сделаем так, чтобы столбцы на графике отображались точно в том же порядке. Это легко и требует только выбора нескольких опций.
- На гистограмме Excel щелкните правой кнопкой мыши любую из меток вертикальной оси и выберите «Формат оси...» в контекстном меню. Или просто дважды щелкните метки вертикальной оси, чтобы появилась панель «Формат оси» .
- На панели «Формат оси» в разделе «Параметры оси» выберите следующие параметры:
- В разделе «Пересечения по горизонтальной оси » отметьте «В максимальной категории».
- В разделе «Положение оси» установите обратный порядок категорий.
Готово! Ваша гистограмма Excel будет немедленно отсортирована так же, как источник данных, по убыванию или же по возрастанию. Вы можете это видеть на скриншоте выше.
Изменение порядка рядов данных в гистограмме
Если гистограмма Excel содержит несколько рядов данных, по умолчанию они также отображаются в обратном порядке. Например, обратите внимание на обратный порядок продуктов в таблице на рабочем листе и на гистограмме:
В таблице сначала идут яблоки и в конце – огурцы, а на диаграмме – все наоборот.
Чтобы упорядочить ряды данных на гистограмме в том же порядке, в котором они отображаются в таблице, вы можете установить флажки В максимальной категории и Обратный порядок категорий, как показано ниже.
Как изменить порядок данных с помощью диалогового окна «Выбрать источник данных».
Этот метод позволяет изменить порядок построения каждого отдельного ряда данных на гистограмме и сохранить исходное расположение данных на рабочем листе.
- Выберите диаграмму, чтобы активировать вкладки Работа с диаграммами на ленте. Перейдите на вкладку «Дизайн» > группу «Данные» и нажмите кнопку «Выбрать данные» .
Или нажмите кнопку «Фильтры диаграммы» справа от графика, а затем нажмите ссылку «Выбрать данные...» внизу.
- В диалоговом окне «Выбор источника данных» выберите ряд данных, расположение которого вы хотите изменить, и переместите его вверх или вниз с помощью соответствующей стрелки:
Эти шаги нужно повторить для каждого ряда, чтобы поменять их порядок с прямого на обратный. Однако, вы можете переместить только один ряд данных (к примеру, первым поставить наиболее важный для вас товар).
Эти манипуляции изменяют порядок рядов только на графике, исходные данные на листе остаются нетронутыми.
Как поменять местами строки и столбцы в гистограмме.
Также может потребоваться изменить способ группировки данных. Например, в уже знакомой нам таблице данные о продажах сгруппированы по товарам и имеют отдельный столбец для каждого месяца. Однако строки и столбцы можно поменять местами, чтобы гистограмма была сгруппирована по товарам и внутри каждого товара были столбцы по месяцам.
Для того чтобы сменить порядок строк и столбцов в ранее построенной гистограмме, проделайте следующие шаги:
- Выберите гистограмму, которую вы хотите отредактировать;
- На вкладке «Конструктор» выберите кнопку «Строка/Столбец»;
- Строки и столбцы на графике поменяются местами, как видно на скриншоте ниже.
Сравните этот результат со стандартным видом гистограммы, что вы уже видели в статье ранее:
В обоих случаях гистограмма содержит одни и те же данные, только сгруппированы они по-разному.
Как переместить гистограмму
Когда вы построили гистограмму, она по умолчанию будет помещена на одном листе с данными, на основе которых она была построена. Вы можете готовую гистограмму переместить на другой лист. Для этого проделайте следующие шаги:
- Выделите гистограмму, которую вы хотите переместить;
- Выберите меню «Конструктор диаграмм» на ленте, затем нажмите кнопку «Переместить диаграмму»;
В диалоговом окне выберите куда вы, хотите переместить гистограмму и нажмите ОК.
После этого гистограмма будет перенесена в новое место назначения. В нашем случае это отдельный новый лист.
Гистограммы - мощный инструмент анализа данных в Excel, который помогает визуализировать распределение значений и понять особенности их изменения. Следуя указанным выше шагам, вы сможете быстро сделать гистограмму по данным таблицы Excel и использовать ее для анализа ваших данных.
Чтобы узнать больше о диаграммах Excel, я рекомендую вам ознакомиться со списком других ресурсов, опубликованным в конце этого руководства.
