Условное форматирование диаграммы в Excel

Обновлено: 03.07.2024

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

Пример

Давайте иметь следующую таблицу данных. Мы хотим выделить столбец на диаграмме со значением меньше 20.

Простая диаграмма для этой таблицы выглядит так:

Как автоматически выделять столбцы?

Решение

Шаг 1

Создайте новую таблицу со значениями в 2 столбцах. В первом — значения больше или равные 20. Во втором столбце — остальные (значения меньше 20).

Формулы для элемента A:

Шаг 2

Создайте столбчатую диаграмму с накоплением из источника A9:C15. Результат работает как столбчатая диаграмма с условным форматированием.

И конечный результат:

Посмотрите это в обучающем видео:

Я пытаюсь изобразить 3 числа 3 цветами на трехмерной гистограмме с накоплением. У меня проблемы с самым верхним числом, когда оно равно нулю (или пусто). Если значение не равно нулю, цвет верхней части полосы отображается правильно. Однако, когда самое верхнее число ЯВЛЯЕТСЯ нулем, верхняя часть полосы отображается цветом нулевого значения (вместо ненулевого значения непосредственно под ним). Любая помощь приветствуется.

Я предполагаю, что вы используете ту же структуру с функцией ЕСЛИ, что и здесь. Похоже, что-то не так с конечными точками диапазона. Вы должны проверить свои >= и и Корми 10 декабря 2019 г. в 12:15

Как я могу связать его со сводной таблицей?

Знаете ли вы, что Microsoft добилась некоторого прогресса в графах с условным форматированием? Нужно ли покупать новую версию Excel? Это решение в порядке, но я хочу больше условий и случаев. Для больших таблиц это очень сложно.

ИМХО Нет лучшего решения, чем это. Я пытался найти эту функцию на ленте Excel, но ее нет.

Иногда самые красивые ответы оказываются самыми простыми. Спасибо!

Как создать цветную диаграмму в Excel 2016? Я хочу, чтобы разные кривые данных отображались разными цветами. Как установить эту опцию в настройки графика?

Я думаю, что этот пример также должен работать в Excel 2016.

Это решение отлично подходит для диаграмм Excel, где цвет зависит от значения.

Как динамически менять цвет на диаграмме? Я хочу создать диаграмму из значений и сделать цвета в зависимости от значений.

Excel не поддерживает условное форматирование в диаграммах. Но есть решение. Прочтите эту статью. У вас есть примеры. Если у вас есть конкретная проблема, мы можем вам помочь.

Можете ли вы показать нам пример условного форматирования на диаграмме с несколькими условиями? Могу ли я использовать для этого временную таблицу?

Аналогично 2 условиям. Вы должны отсортировать данные по столбцам и создать из них диаграмму.

Как изменить цвет на диаграмме с помощью условного форматирования в файле Excel?

Есть ли где-то еще подобные решения? Это действительно отличная идея для условного форматирования диаграммы. Я хочу создать собственную базу данных подсказок Excel.

Можно ли где-нибудь скачать этот пример в формате Excel?

Действительно простое и удобное решение условного форматирования в графике Excel.

Новый XRumer 16.0 — революция в онлайн-продвижении:
искусственный интеллект поможет вам привлекать клиентов так эффективно,
больше, чем когда-либо!

Чтобы различать оценки в разных диапазонах на диаграмме, вы можете создать диаграмму с условным форматированием в 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. После завершения операций нажмите кнопку «Закрыть», чтобы выйти из функции.

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

Возможно, вы уже знакомы с инструментом условного форматирования в Excel. Удивительный инструмент, который автоматически форматирует значения в соответствии с определенными критериями и улучшает визуализацию наших данных. К сожалению, в Excel нет условного форматирования для диаграмм.

Однако существует способ создать условное форматирование с эффектом диаграмм в Excel. И отличная новость. Это не так сложно. Все, что нам нужно, это простая функция ЕСЛИ или другая функция для выполнения теста и получения требуемого значения для диаграммы.

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

Выделить столбец на основе выбора пользователя

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

Приведенная ниже формула введена в ячейку C4. Он сравнивает название продукта с именем, выбранным пользователем в ячейке B3 листа Продажи по продуктам. Затем он отображает значение в ячейке B4, если оно совпадает, или показывает значение ошибки, если нет.

В этом примере пользователь выбрал «Музыка и звук».

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

Выберите диапазон A4:C10, нажмите «Вставка» > «Столбчатая диаграмма» и выберите столбец с двумерной кластеризацией. Это должно выглядеть так, как показано ниже.

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

Дважды щелкните по условно выделенному столбцу (оранжевый значок "Музыка и звук") и измените параметр "Перекрытие серий" на 100%.

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

Выделите столбцы, показывающие рост продаж

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

Как вы видели в предыдущем примере, все дело в функции ЕСЛИ. Мы используем логическую функцию, такую как IF, для выполнения условного теста и показываем значение только в случае необходимости.

После этого мы повторяем тот же метод диаграммы, что и раньше, чтобы перекрыть ряды данных.

Приведенная ниже формула введена в ячейку C4. Он сравнивает продажи этого года (B4) с продажами того же продукта в прошлом году. Поскольку списки расположены в порядке от наибольшего к наименьшему по объему продаж, функция ВПР используется для возврата правильного значения объема продаж.

Конечный результат выглядит так, как показано ниже: только два продукта увеличили продажи по сравнению с прошлым годом или на прошлой неделе.

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

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

Посмотрите видео – условное форматирование с диаграммами

Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

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

Приведенные ниже формулы разбивают значение в столбце B на один из трех рядов в D, E или F. Каждое значение в B относится ровно к одной ячейке в D:F.

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

При создании диаграммы создайте столбчатую диаграмму с накоплением. Вам нужно будет выбрать каждую серию и использовать Format, Shape Fill, чтобы выбрать правильный цвет.

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

Дополнительные ресурсы для Microsoft Excel

ExcelArticles.com предоставляет примеры формул, функций и процедур Visual Basic только для иллюстрации, без явных или подразумеваемых гарантий, включая, помимо прочего, подразумеваемые гарантии товарного состояния и/или пригодности для определенной цели. Формулы, функции и процедуры Visual Basic на этом веб-сайте предоставляются «как есть», и мы не гарантируем, что их можно использовать во всех ситуациях.

Excel® является зарегистрированным товарным знаком корпорации Microsoft.
MrExcel® является зарегистрированным товарным знаком Tickling Keys, Inc.

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