Условное форматирование в excel

Обновлено: 02.07.2024

Библиотека 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 , последующие условные форматирования не применяются, даже если их элементы не вступают в противоречие.

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

Условное форматирование – это инструмент в Excel, который служит для присвоения особенного формата ячейкам или целым диапазонам ячеек на основе критериев условий определенных пользователем. Вы познакомитесь с примерами использования условий основанных на сложных формулах. А так же научитесь управлять такими функциями как:

• поля данных;
• цветовая палитра;
• настройка шрифтов.

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

Как сделать условное форматирование в Excel

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

Допустим столбец, содержит диапазон ячеек с числовыми значениями. Если определить их соответствующим условием форматирования, то все значения с числом более чем 100 будут отображаться красным цветом. Чтобы реализовать эту задачу данный инструмент Excel проанализирует в соответствии с условиями критериев значение каждой ячейки заданного диапазона. Результаты анализа дают положительный результат, например (A2>100=ИСТИНА), тогда будет присвоен предварительно заданный новый формат (красный цвет). В противоположном результате (A2>100=ЛОЖЬ), формат ячеек не изменяется.

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

Как создать правило условного форматирования в Excel

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

Допустим в ячейке A1 находиться числовое значение 50:

Определим следующие условия для формата отображения значений в A1:

1. Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
2. Если число больше чем 30, то шрифт будет отображаться желтым цветом.
3. Если число больше чем 40, то шрифт будет отображаться красным.

Определенно вы заметили, что значение 50 в ячейке A1 соответствует всем условиям (A1>15, A1>30 и A1>40 = ИСТИНА). Каким же цветом шрифта Excel отобразит числовое значение 50?

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

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

Создать второе правило

Второй пример. Допустим нам нужно отформатировать расходы в колонке C следующим образом:

Все суммы в пределах 300$-600$ должны закрасить свои ячейки желтым фоном, а суммы меньше чем 500$ должны еще иметь и красный цвет шрифта.

Попробуем сконструировать данные условия:

1. Выделите диапазон ячеек C4:C7 и выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
2. В появившемся окне нажмите на кнопку «Создать правило».
3. В окне «Создание правила форматирования», выберите тип правила «Форматировать только ячейки, которые содержат».
4. В первом выпадающем списке укажите «Значение ячейки», во втором «между». А в остальных двух введите границы придела 300 и 600.
5. Нажмите на кнопку «Формат» и на вкладке «Заливка» укажите желтый цвет.
6. Нажмите «ОК» для возврата к окну «Диспетчер правил условного форматирования».
7. Снова нажмите на кнопку «Создать правило» и снова же выберите тип правила «Форматировать только ячейки, которые содержат».
8. Первый выпадающий список оставляем без изменений «Значение ячейки», а во втором выбираем опцию «меньше». В поле ввода указываем значение 500.
9. Нажмите на кнопку «Формат» и на вкладке «Шрифт» укажите красный цвет.
10. На всех окнах нажмите «ОК».

Обратите внимание, в какой способ Excel применил форматирование. Суммы в ячейках C10, С13 и С15 соответствуют обоим условиям. Поэтому к ним применены оба стиля форматирования. А там где значение соответствует только одному из условий, они отображаются соответственным форматам.

Условное форматирование позволяет экспонировать данные, которые соответствуют определенным условиям.

В Excel существует два вида условного форматирования:

1. Присвоение формата ячейкам с помощью нестандартного форматирования.
2. Задание условного формата с помощью специальных инструментов на вкладке «Файл»-«Стили»-«Условное форматирование».

Рассмотрим оба эти метода в деле и проанализируем, насколько или чем они отличаются.

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

Для решения данной задачи выделим ячейки со значениями в колонке C и присвоим им условный формат так, чтобы выделить значения которые меньше чем 2. Где в Excel условное форматирование:

1. Выделяем диапазон C2:C5 и выбираем инструмент: «Файл»-«Стили»-«Условное форматирование».
2. В появившемся выпадающем списке выбираем опцию: «Правила выделения ячеек»-«Меньше».
3. В диалоговом окне «меньше» указываем в поле значение 2, а напротив него выбираем из выпадающего списка желаемый формат. Жмем ОК.

«Светло-красная заливка и темно-красный текст» - это формат выделения значений, который предложен по умолчанию. Но из списка можно выбрать другие или решится на последний пункт: «Пользовательский формат». Сразу же появится знакомое нам окно из предыдущих уроков: «Формат ячеек». В нем вы можете задать свой стиль оформления ячейки, который будет отображаться только лишь при соблюдении условия.

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

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

Теперь будем форматировать с условиями нестандартным способом. Сделаем так, чтобы при определенном условии значение получало не только оформление, но и подпись. Для этого снова выделяем диапазон C2:C5 и вызываем окно «Формат ячеек».

Переходим на вкладку «Число» выбираем опцию «(все форматы)» и в поле «Тип:» указываем следующее значение: 0;[Красный]"убыток"-0 .

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

Пользовательские форматы позволяют использовать от 1-ой до 4-х таких секций:

1. В одной секции форматируются все числа.
2. Две секции оформляют числа больше и меньше чем 0.
3. Три секции разделяют форматы на: I)>0; II) [Черный] [Белый] [Желтый] [Красный] [Фиолетовый] [Синий] [Голубой] [Зеленый]

Условное форматирование цветом в Excel

Для продвинутых пользователей доступен код [ЦВЕТn] где n – это число 1-56. Например [ЦВЕТ50] – это бирюзовый .

Таблица цветов Excel с кодами:

Теперь в нашем отчете о доходах скроем нулевые значения. Для этого зададим тот же формат, только в конце точка с запятой: 0;[Красный]"убыток"-0; - в конце (;) для открытия третей пустой секции.

Если секция пуста, значит, не отображает значений. То есть таким образом можно оставить и вторую или первую секцию, чтобы скрыть числа больше или меньше чем 0.

Используем больше цветов в нестандартном форматировании. Условия следующие:

• числа >100 в синем цвете;
• числа [Зеленый] [ 100]0;[Красный]0

Как видно на рисунке нестандартное условное форматирование так же обладает широкой функциональностью.

Как упоминалось выше, секция должна начинаться с кода цвета (если нужно задать цвет), а после указываем условие:

• в квадратных скобках, а после способ отображения числа;
• число 0 значит отображение числа стандартным способом.

Заполните новый лист как показано на рисунке:

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

Мы видим, что символ 0 отображает значение в ячейках как число, а если целых чисел недостаточно отображается просто 0.

Символ пробела служит как разделитель на тысячи.

Задача следующая. Нужно отобразить значения нестандартным способом:

• числа должны отображаться в формате «Общий»;
• нули будут скрыты;
• текст должен отображаться красным цветом.

Решение: 0; 0;;[Красный]@

Примечание: символ @ - значит отображение любого текста, то есть сам текст указывать не обязательно.

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

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

Используя условное форматирование, мы можем:

• закрашивать значения цветом
• менять шрифт
• задавать формат границ

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

Где находится условное форматирование в Эксель?

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо задать две настройки:

• Каким ячейкам вы хотите задать формат;
• По каким условиям будет присвоен формат.

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

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

Также, доступны следующие условия:

• Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

• Во всплывающем окне формата укажите:
  • цвет заливки
  • цвет шрифта
  • шрифт
  • границы ячеек

  
  

  Ниже пример таблицы с применением условного форматирования по заданным нами параметрам. Данные с отрицательными значениями выделены красным цветом:

  
  

  Как создать правило

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

  
  

  
  

  
  

  Условное форматирование по значению другой ячейки

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

  Для создания условия по значению другой ячейки выполним следующие шаги:

  
  

  
  

  
  

  
  

  На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

  
  

  Как применить несколько правил условного форматирования к одной ячейке

  Возможно применять несколько правил к одной ячейке.

  Для применения нескольких условий к одной ячейке выполним следующие действия:

  
  

  
  

  
  

  Как редактировать правило условного форматирования

  Для редактирования присвоенного правила выполните следующие шаги:

  
  

  Как копировать правило условного форматирования

  Для копирования формата на другие ячейки выполним следующие действия:

  
  

  Как удалить условное форматирование

  Для удаления формата проделайте следующие действия:

  
  

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

    
  • 1с как настроить дисконтные карты в 1с
    
  • Как создать плоскость в автокаде
    
  • Ошибка init driver 0xc0000603 xone
    
  • Исполнительная схема образец word
    
  • Программы для изучения китайского языка для андроид