Настройка условного форматирования

Об условном форматировании

Условное форматирование – это удобный инструмент, позволяющий пользователям легко и быстро интерпретировать большие объёмы данных с помощью визуальных подсказок. Данная функциональная возможность позволяет изменять внешний вид элементов таблицы в соответствии с заданными условиями.

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

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

• сравнить значения с константой (например, с нулём);

• сравнить диапазоны значений;

• настроить условие столбца которое зависит от значения текстовой меры.

Создание правил условного форматирования

Чтобы открыть окно настроек условного форматирования, нажмите напротив названия столбца, для которого вы хотите создать правила форматирования, и выберите Условное форматирование в выпадающем меню:

Open

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

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

Open

В таблице ниже приведен перечень доступных опций с их кратким описанием:

Опция	Описание

Опция	Описание
Open	Для плоской таблицы доступен только один вариант: Только к значениям. Это означает, что форматирование будет применяться ко всем ячейкам с данными указанного столбца. Open Для сводной таблицы доступны несколько вариантов: Только к значениям – форматирование применяется только к значениям в ячейках, исключая итоги. К значениям и итогам – форматирование распространяется как на значения в обычных ячейках, так и на ячейки с итоговыми данными. Только к итогам – форматирование применяется исключительно к ячейкам с итоговыми данными. Open
Open	Здесь выбирается столбец, который будет использоваться для определения условий форматирования. Open
Open	Позволяют указать для чего вы будете настраивать правила форматирования – для фона или для шрифта, а также настроить правила с использованием значков-индикаторов.
Open	Позволяет создать новое правило.
Open	Позволяет изменить порядок цветов в обратном направлении.
Open	В этом поле указывается тип данных, к которому будет применяться правило условного форматирования. Доступные опции: Число; Строка; Дата. Для каждого типа данных условия выглядят по-разному.
Open	Условие для типа данных Число. Включает в себя следующие элементы: Если – задаёт основное условие, которое должно быть выполнено для применения форматирования. Доступны следующие опции: Больше – форматирование применяется, если значение в ячейке больше числа, указанного в цифровом поле. Меньше – форматирование применяется, если значение меньше указанного числа. Больше или равно - форматирование применяется, если значение больше или равно указанному числу. Меньше или равно – форматирование применяется, если значение меньше или равно указанному числу. Равно – форматирование применяется, если значение точно соответствует указанному числу. Не равно – форматирование применяется, если значение не равно указанному числу. Является пустым – форматирование применяется, если ячейка пустая (не содержит данных). И – позволяет добавить дополнительное условие, которое должно быть выполнено одновременно с основным условием из пункта “Если”. Доступны следующие опции: Меньше – форматирование применяется, если значение также меньше числа, указанного в цифровом поле. Меньше или равно – форматирование применяется, если значение меньше или равно указанному числу. Нет условия – дополнительное условие не применяется, и форматирование зависит только от основного условия. То – в этом пункте задаётся визуальное оформление, которое будет применено, если условия из пунктов “Если” и “И” выполнены. Здесь можно выбрать цвет из стандартной палитры, чтобы изменить цвет фона или шрифта. При настройке значков здесь также можно указать значок и выбрать цвет для него: Open
Open	Условие для типа данных Дата. Настройки условия аналогичны тем, которые используются для типа данных Число (см. выше), но с учётом специфики временных данных.
Open	Условие для типа данных Строка. Включает в себя следующие элементы: Если – задаёт основное условие, которое должно быть выполнено для применения форматирования. Для типа данных "Строка" доступны следующие опции: Содержит – форматирование применяется, если текст в ячейке содержит указанную строку (например, слово или символ). Не содержит – форматирование применяется, если текст в ячейке не содержит указанной строки. Начинается с – форматирование применяется, если текст в ячейке начинается с указанной строки. Не начинается с – форматирование применяется, если текст в ячейке не начинается с указанной строки. Является – форматирование применяется, если текст в ячейке точно соответствует указанной строке. Не является – форматирование применяется, если текст в ячейке не соответствует указанной строке. Является пустым – форматирование применяется, если ячейка пустая (не содержит текста). Не является пустым – форматирование применяется, если ячейка не пустая (содержит текст). Для каждой из этих опций доступно текстовое поле, в которое можно ввести строку, используемую для проверки условия. То – в этом пункте задаётся визуальное оформление, которое будет применено, если условие из пункта "Если" выполнено. Здесь можно выбрать цвет из стандартной палитры, чтобы изменить цвет фона или шрифта. При настройке значков здесь также можно указать значок и выбрать цвет для него: Open
Open	(Только для значков) Позволяет задать расположение значка относительно значения по горизонтали.
Open	(Только для значков) Позволяет задать расположение значка по вертикали. Актуально при многострочных значениях в ячейке.

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

Сравнение значений с константой

Часто возникает необходимость сравнить числовые значения с фиксированной величиной, такой как ноль. Пользователь хочет видеть, какие значения выше нуля (положительные) и ниже нуля (отрицательные). В нашем примере мы покажем, как выделить положительные и отрицательные значения. Синий шрифт будет использоваться для обозначения положительных значений, а красный – для отрицательных. Чтобы выполнить такие настройки, нужно сделать следующее:

1. Откройте дашборд с таблицей, для которой вы хотите настроить условное форматирование. Для нашего примера мы откроем дашборд с плоской таблицей.

2. Щелкните таблицу, чтобы выбрать её.

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

   Open

   

    

4. В открывшемся окне перейдите на вкладку Шрифт:
   
   

   Open

   

5. В правой части окна нажмите кнопку Новое правило и задайте условие с помощью выпадающих меню и полей для значений. Так как мы хотим выделять отрицательные значения красным цветом, мы установим следующие параметры:

   • для условия Если установим значения Меньше 0;

   • для условия И укажем Нет условия;

   • нажмем на кнопку выбора цвета и выберем красный цвет с помощью стандартной палитры:
     
     

     Open

     

      

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

   Open

   

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

    

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

   Open

   

Сравнение диапазонов

В процессе анализа данных часто требуется классифицировать их по определенным диапазонам. Например, ячейки со значениями от 0 до 50 могут быть выделены одним цветом (красным), ячейки со значениями от 51 до 100 другим (желтым), а ячейки со значениями свыше 101 третьим (зеленым). Такой подход позволяет мгновенно оценить, насколько текущие показатели соответствуют установленным целям или нормам, и принять соответствующие меры.

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

1. Откройте дашборд с таблицей, для которой вы хотите настроить условное форматирование. Для нашего примера мы откроем дашборд со сводной таблицей.

2. Щелкните таблицу, чтобы выбрать её.

3. В панели настроек виджета нажмите в поле Показатели и выберите пункт Условное форматирование в выпадающем меню:
   
   

   Open

   

4. В открывшемся окне перейдите на вкладку Фон. В поле Применить мы также выберем пункт Только к значениям:

   Open

   

5. Создайте несколько правил, нажав на кнопку Новое правило в правой части окна и задав условия с помощью выпадающих меню и полей для значений. Так как мы хотим выделять диапазоны значений, мы выполнили следующие настройки:
   
   

   Open

   

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

6. Чтобы сохранить настройки, нажмите кнопку Применить.
   У нас получился следующий результат:
   
   

   Open

   

Настройка условия столбца, которое зависит от значения текстовой меры 

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

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

   Статус = IF( CALCULATE( SUM( 'Продажи'[Продажи-план] ) - SUM( 'Продажи'[Продажи-факт] ) ) > 0, "Не выполнен", IF( CALCULATE( SUM( 'Продажи'[Продажи-план] ) - SUM( 'Продажи'[Продажи-факт] ) ) = 0, "Выполнен", "Перевыполнен" ) )

2. Теперь мы настроим условное форматирование для столбца "Продажи-факт". В качестве основы для форматирования выберем столбец с созданной мерой (“Статус”) и укажем тип данных “Строка”:

   Open

   

3. Теперь мы можем задать условия, при выполнении которых в ячейки столбца "Продажи-факт" будут добавляться значки в зависимости от значений в столбце, используемом в качестве основы (в нашем примере это “Статус”):

   • если ячейка в столбце “Статус“ содержит слово "Выполнен", в соответствующей ячейке столбца "Продажи-факт" появится синяя галочка, символизирующая успешное выполнение.

   • если ячейка содержит "Не выполнен", в столбце "Продажи-факт" отобразится красная стрелка, направленная вниз, что указывает на невыполнение плана.

   • если ячейка содержит "Перевыполнен", в столбце "Продажи-факт" появится зеленая стрелка, направленная вверх, обозначающая превышение плановых показателей:
     
     

     Open

     

4. У нас получился такой результат:
   
   

   Open

   

Если наличие столбца с мерой, которую вы использовали для настройки форматирования, не требуется (в данном случае «Статус»), его можно скрыть. Просто кликните правой кнопкой мыши на заголовок столбца, выберите пункт Колонки в появившемся меню и снимите флажок рядом с нужной колонкой. Колонка исчезнет из таблицы, а заданное форматирование сохранится:

Open

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

При необходимости отредактировать правила условного форматирования выполните следующие шаги:

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

2. В панели настроек виджета нажмите в поле, для которого вы хотите обновить правила.

3. В открывшемся окне внесите необходимые изменения и нажмите Применить:
   
   

   Open

   

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

Вы можете удалить как отдельные правила, так и очистить все правила одним кликом.

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

Для удаления сразу всех правил форматирования, нажмите Очистить условное форматирование либо в окне редактирования, либо в меню:

Open

Смотрите также

Визуальное оформление виджетов
Создание дашбордов и листов

На этой странице

Время чтения: 3 мин.

Нужна дополнительная помощь?

Свяжитесь с технической поддержкой.