В этом руководстве будет показано, как создавать диаграммы Excel с условным форматированием во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.
Условное форматирование - это практика присвоения настраиваемого форматирования ячейкам Excel (цвет, шрифт и т. Д.) На основе заданных критериев (условий). Эта функция помогает анализировать данные, находить статистически значимые значения и определять закономерности в заданном наборе данных.
Но, к сожалению, в Excel нет встроенных инструментов для применения условного форматирования к диаграммам Excel.
Однако с каплей магии электронных таблиц нет непреодолимых проблем. В этом руководстве вы узнаете, как создать динамическую диаграмму с условным форматированием, где нанесенные на график точки данных автоматически форматируются на основе их соответствующих фактических значений.
В качестве примера взгляните на эту столбчатую диаграмму, в которой показаны результаты работы менеджера по продажам за восемь месяцев. Обратите внимание, как цвет столбца автоматически регулируется в зависимости от его базового значения:
Начиная
Рассмотрим этот образец набора данных, содержащий данные об эффективности продаж:
Прежде чем мы начнем, переместите свой набор данных так, чтобы у вас были три пустые строки над таблицей с вашими необработанными данными. Строки будут использоваться для установки правил условного форматирования.
Шаг №1: Подготовьте данные диаграммы.
Чтобы все заработало, вам нужно добавить в уравнение дополнительные данные диаграммы. Для вашего удобства посмотрите, как должны выглядеть данные вашей диаграммы к концу этапа подготовки:
Нашим первым шагом является определение граничных значений (условий), которые должны разделить фактические значения на несколько категорий, что позволит отображать их в виде отдельных рядов данных.
Например, на скриншоте выше вы можете видеть, что показатели продаж за определенный месяц должны находиться в диапазоне от 90 000 до 150 000 долларов, чтобы считаться удовлетворительными.
Чтобы создать правила, используйте три пустые строки над набором данных:
- Ряд 1 (этикетка): Используйте эти ячейки для обозначения правил форматирования.
- Строка 2 (Мин. Значение): Значения в этой строке определяют нижнюю границу для каждого условия.
- Строка 3 (макс. Значение): Точно так же эти значения устанавливают верхние границы.
С помощью этого метода вы можете создать столько правил условного форматирования, сколько захотите, - просто убедитесь, что диапазоны значений не перекрываются.
По мере того, как вы продолжаете постепенно настраивать свою диаграмму, создавайте собственные элементы легенды диаграммы, которые будут обеспечивать контекст для диаграммы. Введите эту формулу в C4 и скопируйте его в D4 перетащив маркер заполнения:
| 1 | = C1 & ": от" & ТЕКСТ (C2, "$ #, ##") & "до" & ТЕКСТ (C3, "$ #, ##") |
Формула составляет аккуратно выглядящую динамическую метку на основе ранее установленных правил условного форматирования. Функция ТЕКСТ форматирует значения в виде валюты. Но если ваш тип данных отличается, вместо этого примените эту формулу:
| 1 | = C1 & ": от" & ТЕКСТ (C2, "#, ##") & "до" & ТЕКСТ (C3, "#, ##") |
Или вот этот, когда вы работаете с процентами:
| 1 | = C1 & ": от" & ТЕКСТ (C2, "#%") & "до" & ТЕКСТ (C3, "#%") |
Наконец, найдите значения диаграммы. Введите следующую формулу в C5скопируйте его в C12 и через соответствующие ячейки в столбец D:
| 1 | = ЕСЛИ (И (C $ 2 <$ B5, $ B5 <= C $ 3), $ B5, NA ()) |
Формула сравнивает данное фактическое значение в столбец B по заданным граничным значениям с помощью функций ЕСЛИ и И. Если значение попадает в диапазон, оно копируется в этот столбец. В противном случае формула возвращает ошибку # Н / Д, чтобы одно и то же значение не отображалось несколько раз.
Шаг № 2: Настройте столбчатую диаграмму.
Собрав все данные диаграммы, настройте простую столбчатую диаграмму или столбчатую диаграмму в качестве альтернативы:
- Выделите все данные диаграммы, кроме столбцов, содержащих фактические значения и правила, удерживая нажатой кнопку Ctrl ключ (A4: A12 и C4: D12).
- Перейти к Вставлять таб.
- Выбирать "Вставить столбец или гистограмму.”
- Выбирать "Кластеризованный столбец / Кластерный столбец.”
В результате вы получите диаграмму с точками данных, построенными на основе правил условного форматирования, как и было обещано:
Шаг № 3: Измените значения перекрытия и ширины зазора.
Прежде чем закрыть это руководство, исправьте проблему смещения столбцов, вызванную невидимыми пустыми значениями (ошибками # N / A), нанесенными на диаграмму.
Щелкните правой кнопкой мыши любой столбец и выберите «Форматировать ряд данных»Из всплывающего контекстного меню.
На панели задач измените положение и ширину столбцов:
- Переключитесь на Варианты серий таб.
- Изменять "Перекрытие серий" к "100%.”
- Установить Ширина зазора к "60%.”
Шаг №4: Настройте цветовую схему.
Наконец, добавим последние штрихи. Не закрывая Форматировать ряд данных области задач измените цветовую схему диаграммы:
- Перейти к Заливка и линия таб.
- Под "Наполнять," выбирать "Сплошная заливка.”
- Щелкните значок «Цвет заливки»И выберите зеленый из цветовой палитры.
Оказавшись там, перекрасите другие ряды данных, измените заголовок диаграммы, и ваша полностью динамическая диаграмма с условным форматированием готова к работе!
