Условное форматирование наборы значков в excel
Обновлено: 06.07.2024
В этой заметке я расскажу об использовании формул в условном форматировании и о том, как основываясь на значении ячейки форматировать другие ячейки или даже целые диапазоны. Например, как по значению в ячейке выделить всю строку, в которой ячейка находится.
В первую очередь разберемся с формулами при создании правил условного форматирования.
В предыдущих заметках ( 1 часть , 2 часть , 3 часть ) я рассказал о стандартных шаблонах, которые доступны в Excel, однако использование формул предоставляют намного более широкие возможности.
Условное форматирование с помощью формулы
Рассмотрим следующий пример.
Есть зарплатная ведомость и нужно выделить все оклады свыше 35000 рублей. В одной из предыдущих заметок мы уже решали подобную задачу и сумма в 35000 задавалась непосредственно в правиле, что очень неудобно, ведь может потребоваться постоянно изменять значения для анализа таблицы. В такой ситуации придется каждый раз корректировать правило.
Использование стандартных шаблонов условного форматирования Использование стандартных шаблонов условного форматированияИменно поэтому интересующее нас значение вынесем в отдельное поле.
Затем выделим диапазон с окладами (1) и создадим собственное правило условного форматирования (3). При задании условия (4) мышью укажем нужные ячейки в таблице (5). Затем зададим само форматирование (6), например, изменим фон на красный.
Создание собственного правила условного форматирования Создание собственного правила условного форматированияТакая формула работать не будет и, скорее всего, вы уже догадываетесь почему. По умолчанию в формулу подставляются абсолютные ссылки на ячейки и поэтому в итоге мы не увидим никакого форматирования, так как самая первая ячейка диапазона (С6) не удовлетворяет заданному условию, а из-за абсолютных ссылок в нем условие не будет изменяться для последующих ячеек.
Абсолютным и относительным ссылкам я посвятил отдельное очень подробное видео , поэтому если вы еще с ними незнакомы или не очень понимаете их суть, то обязательно сначала изучите его. Без понимания абсолютной и относительной адресации в Excel невозможно использовать условное форматирование.
Ну а чтобы было более понятно дополним таблицу вспомогательным столбцом. Условное форматирование производится, так сказать, в фоновом режиме, поэтому, вынеся на свет его подноготную, мы сможем понять, что же произошло.
Итак, скопируем формулу из правила условного форматирования и вставим ее в столбец рядом. Раскопируем формулу по диапазону.
Мы видим, что все значения одинаковы и являются ложью. Также пробежав по столбцу со значениями в строке формул увидим неизменную формулу с абсолютными ссылками. То есть весь столбец зависит от значения его первой ячейки (С6), в чем легко убедиться, изменив ее значение на удовлетворяющее условию.
Значение ячейки С6 меняет форматирование всего столбца Значение ячейки С6 меняет форматирование всего столбцаТеперь весь столбец стал красным, хотя далеко не все значения удовлетворяют условию.
Давайте приведем формулу во вспомогательном столбце к должному виду. Абсолютной должна остаться только ссылка на ячейку из поля с условием, чтобы она не изменялась при копировании формулы по диапазону. Ссылка на ячейку столбца с данными должна остаться относительной. Размножим формулу и увидим верный результат.
Интересующие нас значения будут ИСТИНОЙ, а значит в случае с условным форматированием такая ячейка ему подвергнется.
Осталось лишь скопировать формулу и изменить правило условного форматирования (1). Сразу можно изменить и само условное форматирование, поменяв фон на менее едкий, а также сделав шрифт полужирным (2).
Библиотека JavaScript Excel предоставляет API для применения условного форматирования к диапазонам данных в книгах. Эта функция упрощает визуальный анализ больших наборов данных. Форматирование также динамически обновляется с учетом изменений в диапазоне.
В этой статье рассматривается условное форматирование в контексте надстроек JavaScript для Excel. В указанных ниже статьях представлены подробные сведения о всех возможностях условного форматирования в Excel.
Программное управление условным форматированием
Свойство Range.conditionalFormats — это коллекция объектов ConditionalFormat, применяемых к диапазону. Объект ConditionalFormat содержит несколько свойств, определяющих применяемый формат на основе ConditionalFormatType.
- cellValue
- colorScale
- custom
- dataBar
- iconSet
- preset
- textComparison
- topBottom
У каждого из этих свойств форматирования есть соответствующий вариант *OrNullObject . Дополнительные статьи об этом шаблоне в разделе * Методы OrNullObject.
Для объекта ConditionalFormat можно установить только один тип формата. Это определено свойством type , которое является значением перечисления объекта ConditionalFormatType. Параметр type устанавливается при добавлении условного форматирования к диапазону.
Создание правил условного форматирования
Условное форматирование добавляется к диапазону с помощью conditionalFormats.add . После добавления можно задать свойства, относящиеся к условному форматированию. В примерах ниже показано создание различных типов форматирования.
Значение ячейки
При условном форматировании значения ячейки применяется пользовательский формат на основе результатов одной или двух формул в ConditionalCellValueRule. Свойство operator является оператором ConditionalCellValueOperator, который определяет, как итоговое выражение связано с форматированием.
В приведенном ниже примере показано применение красного шрифта ко всем значениям диапазона, которые меньше нуля.
Цветовая шкала
При условном форматировании с использованием цветовой шкалы применяется цветовой градиент в диапазоне данных. Свойство criteria в ColorScaleConditionalFormat определяет три точки ConditionalColorScaleCriterion: minimum , maximum и (при желании) midpoint . У каждой точки условия есть три свойства:
- color — HTML-код цвета для конечной точки.
- formula — число или формула, представляющая значение конечной точки. Оно будет равным null , если type имеет значение lowestValue или highestValue .
- type — способ оценки формулы. highestValue и lowestValue относятся к значениям в форматируемом диапазоне.
В приведенном ниже примере показан диапазон, окрашенный с переходом от синего к желтому и красному цвету. Обратите внимание, что minimum и maximum являются минимальным и максимальным значением соответственно, и для них используются формулы null . Для значения midpoint используется тип percentage с формулой "=50" , чтобы самая желтая ячейка соответствовала среднему значению.
Пользовательское
При пользовательском условном форматировании применяется пользовательский формат к ячейкам на основе формулы произвольной сложности. Объект ConditionalFormatRule позволяет определять формулу в разных нотациях:
- formula — стандартная нотация.
- formulaLocal - Локализовано на основе языка пользователя.
- formulaR1C1 — нотация R1C1.
В приведенном ниже примере зеленым цветом окрашен шрифт ячеек с более высокими значениями, чем в ячейках слева.
Гистограмма
При условном форматировании с использованием гистограмм они добавляются к ячейкам. По умолчанию минимальное и максимальное значения в диапазоне создают границы и пропорциональные размеры гистограмм. Объект DataBarConditionalFormat обладает несколькими свойствами для управления внешним видом панели.
В приведенном ниже примере используется форматирование с помощью гистограмм с заполнением слева направо.
Набор значков
При условном форматировании с набором значков используются значки Excel для выделения ячеек. Свойство criteria — это массив объекта ConditionalIconCriterion, определяющий добавляемый символ и условия для добавления. Этот массив автоматически заполняется элементами условия со свойствами по умолчанию. Отдельные свойства не могут быть перезаписаны. Вместо этого необходимо заменить весь объект условия.
В приведенном ниже примере показано применение в диапазоне набора из трех значков с треугольниками.
Готовые условия
При условном форматировании с готовыми условиями применяется пользовательский формат к диапазону на основе выбранного стандартного правила. Эти правила определяются с помощью ConditionalFormatPresetCriterion в ConditionalPresetCriteriaRule.
В следующем примере цвет шрифта белый, где значение ячейки по крайней мере на одно стандартное отклонение выше среднего диапазона.
Сравнение текста
При условном форматировании со сравнением текста используется сравнение строк в качестве условия. Свойство rule является объектом ConditionalTextComparisonRule, определяющим строку для сравнения с ячейкой и оператор для указания типа сравнения.
В следующем примере форматы цвета шрифта красный, когда текст ячейки содержит "Задержки".
Верхнее или нижнее значение
При условном форматировании верхнего или нижнего значения применяется форматирование к наибольшему или наименьшему значению в диапазоне. Свойство rule , являющееся типом ConditionalTopBottomRule, указывает основание для условия (максимальное или минимальное значение), а также применение ранжированной или процентной оценки.
В приведенном ниже примере применяется зеленое выделение к ячейке с максимальным значением в диапазоне.
Разные форматирования и приоритет
К диапазону можно применять несколько типов условного форматирования. Если форматы содержат конфликтующие элементы, например разный цвет шрифта, только один формат применяет этот конкретный элемент. Приоритет определяется свойством ConditionalFormat.priority . Приоритет — это число (равное индексу в ConditionalFormatCollection ), которое можно установить при создании формата. Чем ниже значение priority , тем выше приоритет формата.
В приведенном ниже примере показан выбор цвета шрифта при конфликте между двумя форматами. Для отрицательных чисел применяется полужирный шрифт, но НЕ красный, так как приоритет получает формат, устанавливающий для них синий цвет шрифта.
Взаимоисключающие условные форматирования
Свойство stopIfTrue объекта ConditionalFormat не позволяет применять к диапазону условное форматирование с более низким приоритетом. Если при сопоставлении с диапазоном применяется условное форматирование со свойством stopIfTrue === true , последующие условные форматирования не применяются, даже если их элементы не вступают в противоречие.
В приведенном ниже примере показано добавление в диапазон двух условных форматов. Для отрицательных чисел будет использоваться синий шрифт со светло-зеленым фоном, независимо от того, выполняются ли условия другого формата.
Правило Условного форматирования под названием Набор значков упрощает сравнение значений в диапазоне ячеек.
Поясним на примере (см. файл примера ).
Пусть имеется несколько значений в столбце А.
С помощью Условного форматирования каждому значению сопоставим один из 4-х значков, соответствующих его относительной величине.
Для это выделите ячейки A7:A15 и выберите в меню Условного форматирования набор значков "4 оценки" (см. 1-й рисунок к статье).
Меньшим значениям будут сопоставлены значки с одной закрашенной полоской (0 и 24), а наибольшим - с 4-мя (100, 80, 77). Теперь разберем подробнее, почему значки были присвоены значениям именно так, а не иначе.
Откроем правило Условного форматирования (выделите любую ячейку со значением из нашего диапазона и в меню Главная/ Условное форматирование/ Управление правилами дважды кликните на правило).
Т.е. если значение больше или равно 75%, то ему сопоставляется значок с 4-мя закрашенными полосками, если меньше 25% - то с одной полоской. Что это за 75% и 25%?
Для простоты наши значения в столбце А введены от 0 до 100. Т.е. если значение больше или равно 75, то ему сопоставляется значок с 4-мя закрашенными полосками, если меньше 25 - то с одной полоской. Как видно на 2-м рисунке сверху, в этом случае относительная величина значения, выраженная в % совпадает с самим значением (столбец В). Относительная величина рассчитывается по формуле =(A7-МИН($A$7:$A$15))/$P$5 , где в Р5 находится "длина" диапазона - разница максимального и минимального значения (=100-0=100).
Если в нашем диапазоне вместо 0 мы введем значение 31, то значки изменятся.
Теперь минимальным значением станет 24 (его относительная величина =0%), значение 31 будет соответствовать 9,2% от длины диапазона (=100-24=76), т.е. (31-24)/76=9,2%.
Как и раньше, тем значениям, у которых их относительная величина (см. столбец В) больше 75%, будет соспоставлен значок с 4-мя закрашенными полосками (теперь это только значение 100). Значениям 31, 24, 25, 30 будет сопоставлен значок с одной полоской, т.к. их относительная величина <25%.
Для наглядности можно построить диаграмму , которая выделяет одним цветом значения имеющие одинаковые значки, а также границы интервалов.
Для удобства настройки отсекающих значений можно в правиле Условного форматирования использовать формулу или ссылку на ячейку.
Предельные значения теперь указаны в соответствующих ячейках в столбце Н.
Изменяя значения в этих ячейках можно двигать границы интервалов.
Совет : о базовых настройках Условного орматирования рассказано в статье Условное форматирование в MS EXCEL .
Продолжаем разбирать базовые шаблоны условного форматирования, доступны в Excel. Правилам выделения и отбора ячеек были посвящены две предыдущие заметки. Остался последний раздел, который позволяет не просто подсветить значения ячеек, а визуализировать их.
Так гистограммы отобразят величины значений ячеек.
Например, таблицу с финансовыми показателями компании можно сделать более приятной для восприятия, визуализировав значения выручки с помощью гистограмм.
Предложенные в меню варианты отличаются только цветом и типом заливки - есть вариант с градиентной заливкой, когда оттенок цвета меняется от более насыщенного к менее, и второй вариант с обычной заливкой цветом (сплошная заливка).
Гистограмма может отражать и отрицательные значения, поэтому фактическую прибыль компании можно отразить с ее помощью.
Отрицательные значения, указывающие на убытки, будут отображаться другим цветом и гистограмма будет иметь противоположное направление.
Ну а расходы компании можно визуализировать с помощью цветовой шкалы. Здесь цвет ячейки будет зависеть от величины значения, которое в ней находится. Выберем красную схему и теперь по оттенкам красного цвета мы сможем визуально определить величину расходов по каждому месяцу.
Вид цветовой шкалы подбирается под конкретную задачу. Например, с помощью цветовых шкал часто визуализируются метеоданные.
Смоделируем задачу в Excel. Выделяем диапазон значений со средними температурами и подбираем подходящую шкалу.
Ну и последний инструмент этого подраздела позволяет иллюстрировать данные различными наборами значков.
Например, есть данные по колебаниям курса доллара за месяц.
Мы можем проиллюстрировать как изменялся курс каждый день. Для этого дополним данные новым столбцом, в котором рассчитаем изменение курса.
Затем воспользуемся набором значков со стрелками.
Все рассмотренные правила можно и даже нужно изменять. Например, в данном случае для нас неважна величина изменения курса доллара, а важен сам факт увеличения или уменьшения его цены. Поэтому изменим только что созданное правилоправило.
Отключим отображение значений в ячейках, а также откорректируем условия для присвоения того или иного значка. Нужно чтобы зеленая стрелка выводилась для всех положительных значений, красная для отрицательных, а желтая для нулевых.
Отключаем показ значений и корректируем правила отображения стрелок Отключаем показ значений и корректируем правила отображения стрелокТеперь в ячейке отражается только значок и таблица не перегружена лишними цифрами.
Итак, мы рассмотрели стандартные шаблоны, позволяющие быстро решать самые обычные задачи. Однако, как я уже продемонстрировал, эти правила можно (и даже нужно) корректировать. Также можно создать свои собственные правила форматирования с нуля. В таких правилах для задания условий можно использовать формулы, что очень сильно расширяет возмжности условного форматирования.
И о решении некоторых задач с помощью правил условного форматирования я расскажу в следующей заметке.
Ну а пока видео, объединяющее все три заметки по условному форматриовнаию:
Читайте также: