Гистограмма условного форматирования в Excel

Обновлено: 24.11.2024

Условное форматирование — это практика назначения пользовательского форматирования ячейкам Excel — цвета, шрифта и т. д. — на основе заданных критериев (условий). Эта функция помогает анализировать данные, находить статистически значимые значения и выявлять закономерности в заданном наборе данных.

К сожалению, в Excel нет встроенных инструментов для применения условного форматирования к диаграммам Excel.

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

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

Начало работы

Рассмотрите этот образец набора данных, содержащий данные об эффективности продаж:

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

Чтобы все заработало, вам нужно добавить в уравнение дополнительные данные диаграммы. Для вашего удобства взгляните, как должны выглядеть данные вашей диаграммы к концу этапа подготовки:

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

Например, на снимке экрана выше видно, что показатели продаж за данный месяц должны находиться в диапазоне от 90 000 до 150 000 долларов США, чтобы считаться удовлетворительными.

Чтобы создать правила, используйте три пустые строки над набором данных:

Строка 1 (метка). Используйте эти ячейки для маркировки правил форматирования.
Строка 2 (минимальное значение). Значения в этой строке определяют нижнюю границу для каждого условия.
Строка 3 (максимальное значение). Аналогично, эти значения устанавливают верхние границы.

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

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

Формула создает аккуратно выглядящую динамическую метку на основе ранее установленных правил условного форматирования. Функция ТЕКСТ форматирует значения как валюту. Но если ваш тип данных отличается, примените вместо этого эту формулу:

Или это, когда вы работаете с процентами:

Наконец, найдите значения диаграммы. Введите следующую формулу в ячейку C5, скопируйте ее в ячейку C12 и в соответствующие ячейки в столбце D:

Собрав все данные диаграммы, настройте простую столбчатую диаграмму или гистограмму в качестве альтернативы:

Выделите все данные диаграммы, кроме столбцов, содержащих фактические значения и правила, удерживая нажатой клавишу Ctrl (A4:A12 и C4:D12).
Перейдите на вкладку "Вставка".
Выберите «Вставить столбец или гистограмму».
Выберите «Сгруппированный столбец/Сгруппированная полоса».

В результате вы получите диаграмму с точками данных, построенными на основе правил условного форматирования, как и было обещано:

Щелкните правой кнопкой мыши любой из столбцов и выберите «Формат ряда данных» во всплывающем контекстном меню.

На панели задач измените положение и ширину столбцов:

Наконец добавьте последние штрихи. Не закрывая область задач «Формат ряда данных», измените цветовую схему диаграммы:

Перейдите на вкладку "Заливка и линия".
В разделе "Заливка" выберите "Сплошная заливка".
Нажмите значок "Цвет заливки" и выберите зеленый из цветовой палитры.

После этого перекрасьте другие ряды данных, измените название диаграммы, и ваша полностью динамическая диаграмма с условным форматированием готова к работе!

Я могу выполнить условное форматирование гистограммы в Excel 2010, но не могу понять, как переключить ось гистограммы, чтобы сделать ее вертикальной. Есть ли способ обойти это? Можно ли смоделировать вертикальную гистограмму с условным форматированием?

Два вопроса: хотите ли вы переключиться с гистограммы на гистограмму? Кроме того, что вы подразумеваете под условным форматированием в самой диаграмме?

Да, переключиться с гистограммы на гистограмму. Я имею в виду, что я пытаюсь, чтобы каждая ячейка имела одну полосу в зависимости от ее значения. Я использую параметр «Условное форматирование» > «Гистограммы» для создания гистограмм.

5 ответов 5

Гистограммы в условном форматировании отображаются только слева-направо или справа-налево. Варианты «вверх-вниз» или «вниз-вверх» недоступны.

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

Меня интересовало нечто подобное, и я смог этого добиться. Хотя я не использовал условное форматирование, я думаю, что этот пример покажет вам, как добиться того, чего вы хотите.

Поместите числа 1,2,3,2,1 в ячейки с b2 по f2.
Поместите формулу =ПОВТОР("|",B2) в ячейку b4 и скопируйте через f4.
Измените направление текста вверх.

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

Это должно выглядеть так:

Если вам не нравятся пробелы между строками, переключите шрифт на афишу, и вы увидите обычную гистограмму без пробелов.

Это делает то, что вы хотите, без условного форматирования. Вы можете добавить условное форматирование, если хотите изменить цвет гистограмм или что-то еще.

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

Вместо этого вы можете создать столбчатую диаграмму, выбрав свои данные и нажав «Вставить» > «Столбец» (и выбрав свой подтип). Затем вы можете отформатировать диаграмму любым удобным для вас способом.

EDIT: на основе ваших заметок и заимствований у @Rhys Gibson, вот решение с использованием спарклайнов (которые доступны в Excel 2010 или надстройках до Excel 2010). Я включил как гистограммы условного форматирования (почти невозможно понять), так и их эквиваленты спарклайнов.

EDIT: Еще один момент, который может показаться относительно незначительным, гистограмма не является столбчатой (или гистограммой). Это явно (хотя и неявно) разные типы диаграмм, и терминология не должна быть взаимозаменяемой. Отличное описание разницы можно найти в книге Наоми Роббинс «Гистограмма — это не столбчатая диаграмма».

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

Введение в гистограммы

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

Как добавить гистограммы

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

Чтобы добавить условное форматирование с помощью гистограмм, выполните следующие действия.

На листе Excel выберите ячейки с числами, которые вы хотите отформатировать. НЕ включайте итоги по строкам или столбцам.
На ленте откройте вкладку "Главная", а затем в группе "Стили" нажмите "Условное форматирование".
В списке параметров условного форматирования нажмите «Гистограммы», а затем выберите один из параметров панели данных — «Градиентная заливка» или «Сплошная заливка». (см. советы ниже)

В выбранных ячейках теперь отображаются гистограммы данных вместе с исходными числами. В этом примере выбрана полоса данных "Зеленый градиент".

Советы по гистограммам

Тип заполнения

Существует два набора параметров панели данных: градиентная заливка и сплошная заливка.

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

ПРИМЕЧАНИЕ. Измените цифры на полужирный шрифт, чтобы их было лучше видно.

Ширина столбца

Если возможно, сделайте столбец шире, чем обычно, если вы добавляете гистограммы, особенно если числа также будут видны.

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

Показывать только гистограммы

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

Чтобы скрыть номера, выполните следующие действия:

Выберите ячейки, содержащие гистограммы.
На ленте откройте вкладку "Главная".
В группе "Стили" нажмите "Условное форматирование", а затем нажмите "Управление правилами".
В списке правил выберите правило панели данных.
Нажмите кнопку «Редактировать правило», чтобы открыть диалоговое окно «Редактировать правило форматирования».

Установить минимум панели данных

Когда вы добавляете гистограммы, их минимальное и максимальное значения устанавливаются автоматически, обычно минимальное значение равно нулю, а максимальное соответствует максимальному значению.

Чтобы «увеличить» разницу между суммами, выполните следующие действия, чтобы изменить параметр «Минимум»:

Выберите ячейки, содержащие гистограммы.
На ленте откройте вкладку "Главная".
В группе "Стили" нажмите "Условное форматирование", а затем нажмите "Управление правилами".
В списке правил выберите правило панели данных, затем нажмите кнопку "Изменить правило".
В разделе "Редактировать описание правила" показаны настройки по умолчанию для минимального и максимального значений.
Нажмите стрелку для параметра "Тип" в разделе "Минимум", чтобы просмотреть другие параметры.

=МИН($G$4:$G$9)*0,9

Формула находит наименьшее значение в группе ячеек и устанавливает минимум на 10 % ниже этого значения. (Если вы просто выберете «Наименьшее значение», полоса для этого значения не будет видна, что может сбивать с толку).

ПРИМЕЧАНИЕ. В примере файла для установки Максимума также используется формула: =MAX($G$4:$G$9)*1.1. Эта формула находит самое высокое значение и устанавливает максимум на 10% выше этого значения. Это создает небольшое пространство в конце полосы вместо перехода к границе ячейки.Используйте аналогичную формулу, если вы хотите, чтобы в дополнительном пространстве отображались числа, а полосы не перекрывали их.

Получить образец файла

Чтобы следовать этому руководству, загрузите файл примера гистограмм условного форматирования. Файл имеет формат xlsx и не содержит макросов.

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

<р>1. Добавьте три столбца прямо к исходным данным, как показано на снимке экрана ниже:
(1) Назовите первый столбец >90 , введите формулу =IF(B2>90,B2,0) в первую пустую ячейку этого столбца. , а затем перетащите маркер автозаполнения на весь столбец;
(2) Назовите второй столбец как , введите формулу =IF(B2 и перетащите маркер автозаполнения на весь столбец;
(3) Назовите третий столбец как 60~90 , введите формулу =IF(AND(B2>=60,B2 ) и перетащите маркер автозаполнения на весь столбец.

Теперь вы получите новые исходные данные, как показано ниже:

<р>2. Выберите столбец «Имя» и новые три столбца, удерживая клавишу Ctrl, а затем нажмите «Вставить» > «Вставить столбец» или «Гистограмма» (или «Столбец») > «Сгруппированный столбец». Смотрите скриншот:

Теперь создана диаграмма с условным форматированием. Вы увидите, что баллы больше 90 отображаются синим цветом, баллы меньше 60 — оранжевым, а баллы от 60 до 90 — серым. Смотрите скриншот:

Создайте диаграмму с условным форматированием с помощью замечательного инструмента

Если у вас установлен Kutools for Excel, вы можете использовать его функцию диаграммы группировки цветов, чтобы быстро создать диаграмму с условным форматированием в Excel.

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

<р>1. Выберите источник данных, на основе которого вы создадите диаграмму, и нажмите Kutools > Диаграммы > Диаграмма группировки цветов, чтобы включить эту функцию.

<р>2. В диаграмме группировки цветов выполните следующие действия:

(1) отметьте параметр «Столбчатая диаграмма»;
(2) Укажите диапазон меток оси;
(3) Укажите диапазон значений ряда;
(4) В разделе «Группа» нажмите кнопку «Добавить». Затем в диалоговом окне «Добавить группу» укажите имя группы, диапазон данных и определенные значения диапазона, которые вам нужны, и нажмите кнопку «Добавить».
Советы. Эта функция добавит правило условного форматирования одной группой. Если вам нужно добавить несколько правил условного форматирования для диаграммы, добавьте столько групп, сколько вам нужно.

<р>3. Нажмите кнопку "ОК".

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

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

Условное форматирование существующей диаграммы

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

<р>1. Выберите диаграмму, для которой вы хотите добавить условное форматирование, и нажмите Kutools > Диаграммы > Цветовая диаграмма по значению, чтобы включить эту функцию.

<р>2. В диалоговом окне Заполнить диаграмму цветом на основе выполните следующие действия:
(1) Выберите критерии диапазона из раскрывающегося списка Данные;
(2) Укажите значения диапазона в полях Минимальное значение или Максимальное значение;
(3) Выберите цвет заливки в раскрывающемся списке Цвет заливки;
(4) Нажмите кнопку «Заполнить».

Советы:
(1) Операции (1)–(4) изменят цвет заливки столбцов, значения точек данных которых попадают в указанный диапазон данных.
(2) Если вы хотите изменить цвет заливки других столбцов, вам нужно повторить операции (1)–(4) для создания других правил, например, изменить цвет заливки столбцов, значения точек данных которых находятся между 60 и 90 к серому.

<р>3. После завершения операций нажмите кнопку «Закрыть», чтобы выйти из функции.

Примечания. Этот метод существенно изменит цвет заливки столбцов на диаграмме. Если вы измените значения в исходных данных, цвета заливки соответствующих столбцов не изменятся.

Читайте также: