Правила форматирования

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

• Значение равно;
• Наибольшее/Наименьшее;
• Среднее;
• Текст;
• Дата;
• Пустая ячейка/Ошибка;
• Повторяющееся/Уникальное;
• Гистограммы;
• Цветовые шкалы;
• Наборы значков;
• Формула.

Значение равно — правило форматирования

Правило форматирования Значение равно используется для поиска и выделения ячеек, удовлетворяющих следующим условиям сравнения:

• Больше;
• Больше или равно;
• Меньше;
• Меньше или равно;
• Равно;
• Не равно;
• Между;
• Не между.

Окно создания правила форматирования Значение равно

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Поле Выбор данных позволяет указать данные вручную или выбрать ячейки для сравнения. Можно выбрать одну ячейку, ряд ячеек или функцию в формате: =СУММ(A1:B5).
3. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

В примере ниже показан результат заданных критериев форматирования Больше и Между. Горы высотой от 3000 м до 4000 м имеют зелёный фон, а более 4000 м — оранжевый фон.

Наибольшее/Наименьшее — правило форматирования

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

• Наибольшее 10 элементов;
• Наибольшее 10%;
• Наименьшее 10 элементов;
• Наименьшее 10%.

Окно создания правила форматирования Наибольшее/Наименьшее

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. В следующих двух полях раздела Правило укажите сколько элементов (процентов от общего числа элементов) надо найти и как должен происходить поиск: в элементах или процентах.
3. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

В примере ниже показан результат заданных критериев форматирования Наибольшее 10 и Наименьшее 20%. Города, в которых были наибольшие продажа, имеют зелёный фон, а те, в которых на складе осталось наименьшее количество (20% от общего числа элементов), — розовый.

Среднее — правило форматирования

Правило форматирования Среднее используется для поиска и выделения ячеек, значение которых выше/ниже среднего или стандартного отклонения. Доступные параметры форматирования:

• Выше;
• Ниже;
• Равно или больше;
• Равно или меньше;
• На 1 стандартное отклонение выше;
• На 1 стандартное отклонение ниже;
• На 2 стандартных отклонение выше;
• На 2 стандартных отклонение ниже;
• На 3 стандартных отклонение выше;
• На 3 стандартных отклонение ниже.

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

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

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

Текст — правило форматирования

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

• Содержит;
• Не содержит;
• Начинается с;
• Заканчивается на.

Окно создания правила форматирования Текст

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Поле Выбор данных позволяет указать данные вручную или выбрать ячейки для сравнения. Можно выбрать одну ячейку, ряд ячеек или функцию в формате: =СУММ(A1:B5).
3. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

В примере ниже показан результат заданного критерия форматирования Содержит. Чтобы выделить продажи для определенного региона или определенных товаров, ячейки, содержащие слово «Бурятия», имеют светло-зелёный фон, а содержащие слово «Зерно», — жёлтый.

Дата — правило форматирования

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

• Вчера;
• Сегодня;
• Завтра;
• За последние 7 дней;
• Прошлая неделя;
• Эта неделя;
• Следующая неделя;
• Прошлый месяц;
• Этот месяц;
• Следующий месяц.

Окно создания правила форматирования Дата

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

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

Пустая ячейка/Ошибка — правило форматирования

Правило форматирования Пустая ячейка/Ошибка используется для поиска и выделения ячеек, содержащих или не содержащих пустые ячейки и ошибки. Доступные параметры форматирования:

• Содержит пустые ячейки;
• Не содержит пустых ячеек;
• Содержит ошибки;
• Не содержит ошибок.

Окно создания правила форматирования Пустая ячейка/Ошибка

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

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

Повторяющееся/Уникальное — правило форматирования

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

• Повторяющееся;
• Уникальное.

Окно создания правила форматирования Повторяющееся/Уникальное

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

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

Гистограммы — правило форматирования

Правило форматирования Гистограммы используется для сравнения значений в виде гистограммы. Доступные шаблоны форматирования:

В приведённом примере указана высота гор в разных вариантах с использованием гистограмм: значение по умолчанию в метрах (зеленая гистограмма) и такое же значение в диапазоне от 0% до 100% (оранжевая гистограмма); процентиль, когда экстремальные значения сдвигают данные (синяя гистограмма); только гистограммы вместо цифр (жёлтая гистограмма); данные в два столбца для просмотра как чисел, так и столбцов (лиловая гистограмма с указанием минимума и максимума).

Цветовые шкалы — правило форматирования

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

В примере ниже показаны столбцы от «Молочные продукты» до «Напитки», в которых данные отображаются в виде двухцветной шкалы с вариациями от жёлтого до красного; в столбце «Всего продаж» данные отображаются в виде трехцветной шкалы от наименьшей суммы, которая окрашена в красный цвет, до наибольшей суммы, которая окрашена в синий цвет.

Наборы значков — правило форматирования

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

• Направления;
• Фигуры;
• Индикаторы;
• Оценки.

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

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

   

2. Вместо ячеек с номерами рейтинга от 1 до 5 условное форматирование отображает соответствующие значки из легенды вверху для каждого велосипеда в списке рейтинга.

   

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

   

4. Используйте систему светофоров (красные, желтые и зеленые круги), чтобы визуализировать динамику продаж.

   

Формула — правило форматирования

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

1. В разделе Правило отображается выбранное правило и условие. Для изменения условия нажмите на стрелку и выберите подходящее условие.
2. Поле Выбор данных позволяет указать данные вручную или выбрать ячейки для сравнения. Можно выбрать одну ячейку, ряд ячеек или функцию в формате: =СУММ(A1:B5).
3. Раздел Формат позволяет выбрать готовое оформление текста и ячейки из списка Предустановки.

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

Способ 1

• Измените настройки предлагаемого формата из списка Предустановки, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

Способ 2

• Нажмите кнопку Очистить для удаления всего форматирования.
• Настройте свой формат, используя стандартные функции форматирования.
• Результат применённого форматирования смотрите в поле Просмотр.
• Нажмите ОК.

В примерах ниже показан результат форматирования при помощи формул.

1. Закрашивание чередующихся строк с помощью формулы =ОСТАТ(СТРОКА();2)=0.
2. Сравните с эталонным значением (в примере эталон равен 55) и выделите значение зеленым, если оно выше или красным, если оно ниже. Использовались две формулы: =C65<$E$61 и формула =C65>$E$61.
3. Выделите строки, соответствующие необходимым критериям (посмотрите, каких целей необходимо достичь в этом месяце, в данном случае это июль). Использовалась формула =И($P28>$U$28;$P28<$U$29).
4. Выделение уникальных строк.

Использовалась формула =СЧЁТЕСЛИ($K$60:$K$77;$K60)=1.