Условное форматирование гистограмм данных Excel с примерами
Учебник научит вас, как быстро добавлять цветные полосы в Excel и настраивать их по своему вкусу.
Чтобы сравнить различные категории данных на листе, вы можете построить диаграмму. Для визуального сравнения чисел в ячейках гораздо полезнее использовать цветные полоски внутри ячеек. Excel может отображать столбцы вместе со значениями ячеек или отображать только столбцы и скрывать числа.
Что такое гистограммы в Excel?
Гистограммы в Excel — это встроенный тип условного форматирования, который вставляет цветные полосы внутрь ячейки, чтобы показать, как данное значение ячейки сравнивается с другими. Более длинные столбцы представляют более высокие значения, а более короткие столбцы представляют меньшие значения. Гистограммы могут помочь вам с первого взгляда определить самые высокие и самые низкие значения в ваших электронных таблицах, например определить самые продаваемые и самые продаваемые продукты в отчете о продажах.
Гистограммы данных условного форматирования не следует путать с гистограммами — своего рода графиком Excel, который представляет различные категории данных в виде прямоугольных столбцов. Хотя гистограмма — это отдельный объект, который можно перемещать в любое место на листе, гистограммы всегда находятся внутри отдельных ячеек.
Как добавить гистограммы в Excel
Чтобы вставить гистограммы в Excel, выполните следующие действия:
- Выберите диапазон ячеек.
- На вкладке Главная в группе Стили щелкните Условное форматирование.
- Наведите курсор на панели данных и выберите нужный стиль — градиентная заливка или сплошная заливка.
Как только вы это сделаете, внутри выбранных ячеек сразу же появятся цветные полосы.
Например, вот как вы делаете градиентную заливку синих столбцов данных:
Чтобы добавить столбцы данных со сплошной заливкой в Excel, выберите нужный цвет в разделе «Сплошная заливка»:
Чтобы настроить внешний вид и параметры гистограмм, выберите любую из отформатированных ячеек, нажмите «Условное форматирование» > «Управление правилом» > «Изменить», а затем выберите нужный цвет и другие параметры.
Кончик. Чтобы сделать различия между столбцами более заметными, сделайте столбец шире, чем обычно, особенно если значения также отображаются в ячейках. В более широком столбце значения будут располагаться над более светлой частью полосы градиентной заливки.
Какой тип заполнения панели данных лучше выбрать?
В Excel есть два стиля полос — градиентная заливка и сплошная заливка.
Градиентная заливка — правильный выбор, когда в ячейках отображаются как столбцы данных, так и значения: более светлые цвета в конце столбцов облегчают чтение чисел.
Сплошную заливку лучше использовать, если видны только столбцы, а значения скрыты. Видеть как показать только гистограммы и скрыть числа.
Как создать пользовательские гистограммы в Excel
Если ни один из предустановленных форматов вам не подходит, вы можете создать собственное правило с собственным стилем панели данных. Шаги:
- Выберите ячейки, к которым вы хотите применить гистограммы.
- Нажмите «Условное форматирование» > «Гистограммы» > «Дополнительные правила».
- В диалоговом окне «Новое правило форматирования» настройте следующие параметры:
- Выберите тип данных для минимального и максимального значений. По умолчанию (Автоматически) в большинстве случаев работает нормально. Если вы хотите больше контролировать, как вычисляются самые низкие и самые высокие значения, выберите «Процент», «Число», «Формула» и т. д.
- Поэкспериментируйте с цветами заливки и границы, пока не будете довольны предварительным просмотром.
- Определите направление полосы: контекст (по умолчанию), слева направо или справа налево.
- При необходимости установите флажок «Показать только столбцы», чтобы скрыть значения ячеек и отображать только цветные столбцы.
- Когда закончите, нажмите OK.
Ниже приведен пример гистограмм с пользовательским цветом градиента. Все остальные параметры установлены по умолчанию.
Как определить минимальное и максимальное значение столбцов данных в Excel
При применении предустановленных гистограмм минимальное и максимальное значения устанавливаются программой Excel автоматически. Вместо этого вы можете решить, как рассчитать эти значения. Для этого сделайте следующее:
- Если вы создаете новое правило, нажмите «Условное форматирование» > «Гистограммы» > «Дополнительные правила».
Если вы редактируете существующее правило, нажмите «Условное форматирование» > «Управление правилом». В списке правил выберите правило панели данных и нажмите «Изменить».
- В диалоговом окне правила в разделе «Редактировать описание правила» выберите нужные параметры для минимальных и максимальных значений.
- Когда закончите, нажмите OK.
Например, вы можете установить процент гистограммы с минимальным значением, равным 0%, и максимальным значением, равным 100%. В результате полоса с наибольшим значением займет всю ячейку. Для наименьшего значения полоса не будет видна.
Создать гистограмму данных Excel на основе формулы
Вместо определения определенных значений вы можете рассчитать МИН. и МАКС. значения с помощью соответствующей функции. Для лучшей наглядности применим следующие формулы:
Для минимального значения формула устанавливает минимум на 5 % ниже самого низкого значения в указанном диапазоне. Это отобразит крошечную полосу для самой нижней ячейки. (Если вы используете формулу MIN в чистом виде, в этой ячейке не будет видно полосы).
=МИН($D$3:$D$12)*0,95
Для Максимального значения формула устанавливает максимум на 5 % выше самого высокого значения в диапазоне. Это добавит небольшое пространство в конце такта, чтобы оно не перекрывало все число.
=МАКС($D$3:$D$12)*1,05
Панели данных Excel на основе другого значения ячейки
В случае предустановленного условного форматирования нет очевидного способа отформатировать заданные ячейки на основе значений в других ячейках. При использовании гистограмм очень яркого или темного цвета такая опция была бы крайне полезна, чтобы не затенять значения в ячейках. К счастью, есть очень простой обходной путь.
Чтобы применить гистограммы на основе значения в другой ячейке, вам нужно сделать следующее:
- Скопируйте исходные значения в пустой столбец, где должны появиться полосы. Чтобы скопированные значения были связаны с исходными данными, используйте формулу вида =A1, предполагая, что A1 — самая верхняя ячейка, содержащая ваши числа.
- Добавьте гистограммы в столбец, в который вы скопировали значения.
- В диалоговом окне «Правило форматирования» поставьте галочку Показать только панель установите флажок, чтобы скрыть цифры. Сделанный!
В нашем случае числа находятся в столбце D, поэтому скопированная формула в E3 будет =D3. В результате у нас есть значения в столбце D и гистограммы в столбце E:
Гистограммы Excel для отрицательных значений
Если ваш набор данных содержит как положительные, так и отрицательные числа, вы будете рады узнать, что гистограммы Excel работают и для отрицательных чисел.
Чтобы применить разные цвета полос для положительных и отрицательных чисел, сделайте следующее:
- Выберите ячейки, которые вы хотите отформатировать.
- Нажмите «Условное форматирование» > «Гистограммы» > «Дополнительные правила».
- В окне «Новое правило форматирования» в разделе «Внешний вид столбцов» выберите цвет положительных столбцов данных.
- Нажмите кнопку «Отрицательное значение и ось».
- В диалоговом окне «Параметры отрицательного значения и оси» выберите цвета заливки и границы для отрицательных значений. Кроме того, определите положение оси и цвет. Если вам не нужна ось, выберите белый цвет, чтобы ось не была видна в ячейках.
- Нажмите OK столько раз, сколько необходимо, чтобы закрыть все открытые окна.
Теперь вы можете идентифицировать отрицательные числа, быстро взглянув на свой набор данных.
Как показать только бары без значений
Отображение и скрытие значений в отформатированных ячейках — это всего лишь одна галочка 🙂
Если вы хотите, чтобы отображались только цветные полосы, а не числа, в диалоговом окне «Правило форматирования» установите флажок «Показывать только полосы». Вот и все!
Вот как добавить гистограммы в Excel. Очень просто и очень полезно!
Практическая рабочая тетрадь для скачивания
Гистограммы в Excel — примеры (файл .xlsx)
