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

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

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

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

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

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

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

Откроется окно Новое правило форматирования, в котором вы сможете задать критерии выделения.

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

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

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

• В разделе Правило отображается выбранное правило и выбранное условие сравнения. Щелкните стрелку вниз, чтобы открыть список доступных правил и условий. Используйте поле Выбор данных, чтобы задать значение ячейки для сравнения. Используйте ссылку на одну ячейку или ссылку с функцией листа, например =СУММ(A1:B5).
• В разделе Формат находится ряд параметров форматирования ячеек. Вы можете выбрать одну из доступных Предустановок формата

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

В приведенном ниже примере показаны предустановленные критерии форматирования, такие как Больше и Между. Горы высотой более 6960 выделены зеленым фоном, а высотой от 5000 до 6500 — розовым.

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

Правило Наибольшее/Наименьшее используется для поиска и отображения наибольших и наименьших значений в электронной таблице:

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

• В разделе Правило отображается выбранное правило и выбранное условие. Щелкните стрелку вниз, чтобы открыть список доступных правил и условий, указать количество отображаемых элементов (процентов) и выбрать, что вы хотите выделить: элементы или проценты.
• В разделе Формат находится ряд параметров форматирования ячеек. Вы можете выбрать одну из доступных Предустановок формата

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

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

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

Правило Среднее используется для поиска и отображения значений выше или ниже среднего или стандартного отклонения:

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

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

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

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

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

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

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

• В разделе Правило отображается выбранное правило и выбранное условие форматирования. Щелкните стрелку вниз, чтобы открыть список доступных правил и условий. Используйте поле Выбор данных, чтобы задать значение ячейки для сравнения. Используйте ссылку на одну ячейку или ссылку с функцией листа, например =СУММ(A1:B5).
• В разделе Формат находится ряд параметров форматирования ячеек. Вы можете выбрать одну из доступных Предустановок формата

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

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

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

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

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

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

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

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

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

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

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

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

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

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

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

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

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

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

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

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

Форматирование — Гистограммы

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

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

Форматирование — Цветовые шкалы

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

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

Форматирование — Наборы значков

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

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

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

• Вместо чисел и процентных значений вы видите отформатированные ячейки с соответствующими стрелками, которые показывают достижение цели по выручке в столбце «Status» и динамику будущих тенденций в столбце «Trend».

  

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

  

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

  

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

  

Форматирование при помощи формул

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

• В разделе Правило отображается выбранное правило и выбранное условие форматирования. Щелкните стрелку вниз, чтобы открыть список доступных правил и условий. Используйте поле Выбор данных, чтобы задать значение ячейки для сравнения. Используйте ссылку на одну ячейку или ссылку с функцией листа, например =СУММ(A1:B5).
• В разделе Формат находится ряд параметров форматирования ячеек. Вы можете выбрать одну из доступных Предустановок формата

или

настройте форматирование с помощью параметров шрифта (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвета текста, Цвета заливки и Границ. Используйте выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент). Поле Просмотр показывает, как ячейка будет выглядеть после форматирования. Чтобы удалить все форматирование, нажмите кнопку Очистить.

Нажмите OK для подтверждения.

Примеры ниже иллюстрируют возможности форматирования при помощи формул.

Заливка чередующихся строк,

Сравнение с эталонным значением (здесь это $55) и выделение значения зеленым, если оно выше, или красным, если оно ниже,

Выделение строк, соответствующих нужным критериям (посмотрите, каких целей вы должны достичь в этом месяце, в данном случае это октябрь),

Или выделение только уникальных строк

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

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

• Перейдите на вкладку Главная, нажмите кнопку Условное форматирование и выберите Новое правило в выпадающем меню.
• Перейдите на вкладку Параметры ячейки правой боковой панели, щелкните стрелку рядом с кнопкой Условное форматирование и выберите Новое правило в выпадающем меню.
• Щелкните правой кнопкой мыши по любой ячейке и выберите пункт Условное форматирование в контекстном меню.

Сразу же откроется окно Новое правило форматирования. Установите необходимые параметры для настройки правила, как описано выше, и нажмите OK для подтверждения.

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

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

Опция Показать правила форматирования для позволяет выбрать правила, которые вы хотите отобразить:

• Текущий выделенный фрагмент
• Этот лист
• Эта таблица
• Эта сводная таблица

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

• Нажмите кнопку Новое, чтобы добавить новое правило.
• Нажмите кнопку Изменить, если вы хотите отредактировать существующее правило, — откроется окно Изменение правила форматирования. Измените правило так, как считаете нужным, и нажмите OK.
• Используйте стрелки вверх и вниз, чтобы изменить приоритет правила.
• Нажмите кнопку Удалить, чтобы удалить правило.
• Нажмите OK для подтверждения.

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

Редактирование правил Значение равно, Наибольшее/Наименьшее, Среднее, Текст, Дата, Пустая ячейка/Ошибка, Повторяющееся/Уникальное, Формула

Окно Изменение правила форматирования для правил Значение равно, Наибольшее/Наименьшее, Среднее, Текст, Дата, Пустая ячейка/Ошибка, Повторяющееся/Уникальное, Формула содержит ряд стандартных настроек:

• Щелкните Правило, чтобы изменить правило и условие форматирования, примененные ранее;
• В поле Выбор данных можно изменить диапазон ячеек, на который нужно ссылаться (для правил Значение равно, Текст и Формула)
• Используйте параметры форматирования шрифта и ячейки (Полужирный, Курсив, Подчеркнутый, Зачеркнутый), Цвет текста, Цвет заливки и Границы.
• Щелкните выпадающий список Общий, чтобы выбрать нужный числовой формат (Общий, Числовой, Научный, Финансовый, Денежный, Дата, Время, Процент).
• Поле Просмотр показывает, как ячейка будет выглядеть после форматирования.
• Нажмите OK для подтверждения.

Редактирование гистограмм

Окно Изменение правила форматирования для Гистограммы содержит следующие параметры:

• Правило — позволяет изменить правило и условие форматирования, примененные ранее;

• Минимум/Максимум — позволяют задать тип минимального и максимального значений для гистограмм, если вы хотите сосредоточиться на различиях. Типы максимального/минимального значения:

  • Минимум/Максимум
  • Числовой
  • Процентный
  • Формула
  • Процентиль
  • Автоматически

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

  Щелкните поле Выбор данных, чтобы изменить диапазон ячеек для минимального/максимального значений.

• Внешний вид столбца

  Настройте внешний вид Гистограммы, выбрав тип и цвет заливки и границы, а также направление столбцов.

  • Доступно два типа Заливки: Сплошной и Градиент. С помощью стрелки вниз под ними выберите цвет заливки столбцов для положительных и отрицательных значений.
  • Доступно два типа Границы: Сплошной и Нет. С помощью стрелки вниз под ними выберите цвет границ столбцов для положительных и отрицательных значений.
  • Установите флажок Как положительное, чтобы отображать положительные и отрицательные значения одним цветом. Если этот флажок установлен, настройки цвета для Отрицательных значений будут отключены.
  • Используйте опцию Направление столбца, чтобы изменить направление гистограмм. По умолчанию выбрана опция Контекст, но вы можете выбрать Слева направо или Справа налево в зависимости от предпочтений представления данных.
  • Установите флажок Показывать только столбец, чтобы отображать в ячейках только столбцы гистограммы и скрыть значения.

• Оси

  Выберите Положение оси гистограммы относительно середины ячейки, чтобы разделить положительные и отрицательные значения. Доступно три варианта положения оси: Автоматически, Середина ячейки и Нет. Щелкните стрелку вниз у поля цвета, чтобы задать цвет оси.

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

• Нажмите OK для подтверждения.

Редактирование цветовых шкал

Редактирование двухцветной шкалы

Окно Изменение правила форматирования для Двухцветной шкалы содержит следующие параметры форматирования:

• Щелкните Правило, чтобы изменить правило и условие форматирования, примененные ранее;

• Щелкните Мин. точка/Макс. точка, чтобы задать тип значений для минимальной и максимальной точек цветовой шкалы, если вы хотите сосредоточиться на различиях. Типы значений минимальной/максимальной точки:

  • Минимум/Максимум
  • Числовой
  • Процентный
  • Формула
  • Процентиль

  По умолчанию выбрана опция Минимум/Максимум.

• Используйте поле Выбор данных, чтобы изменить диапазон ячеек для минимальной и максимальной точек.

• С помощью стрелки вниз под ним выберите цвет для каждой шкалы.

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

• Нажмите OK для подтверждения.

Редактирование трехцветной шкалы

Окно Изменение правила форматирования для Трехцветной шкалы содержит следующие параметры форматирования:

• Щелкните Правило, чтобы изменить правило и условие форматирования, примененные ранее;

• Щелкните Мин. точка/Средняя точка/Макс. точка, чтобы задать тип значений для минимальной, средней и максимальной точек цветовой шкалы, если вы хотите сосредоточиться на различиях.

  Типы значений минимальной/максимальной точки:

  • Минимум/Максимум
  • Числовой
  • Процентный
  • Формула
  • Процентиль

  Типы значений средней точки:

  • Числовой
  • Процентный
  • Формула
  • Процентиль

  По умолчанию для форматирования Трехцветной шкалы выбрана опция Минимум/Процентиль/Максимум.

• Используйте поле Выбор данных, чтобы изменить диапазон ячеек для минимальной, средней и максимальной точек.

• С помощью стрелки вниз под ним выберите цвет для каждой шкалы.

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

• Нажмите OK для подтверждения.

Редактирование наборов значков

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

• Щелкните Правило, чтобы изменить правило и условие форматирования, примененные ранее;
• Щелкните Стиль значка, чтобы настроить стиль значков для созданного правила.
• Установите флажок Показать только значок, чтобы отображать в ячейках только значки и скрыть значения.
• Установите флажок Значки в обратном порядке, чтобы изменить порядок значков и расположить их от наименьшего значения к наибольшему. По умолчанию значки расположены от наибольшего значения к наименьшему.
• Задайте правило для каждого значка и настройте операторы сравнения (больше или равно, больше), пороговые значения и тип значения (Числовой, Процентный, Формула, Процентиль), чтобы расположить значения в последовательности сверху вниз. По умолчанию значения разделены поровну.
• Нажмите OK для подтверждения.

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

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

• Из текущего выделенного фрагмента
• Из этого листа
• Из этой таблицы
• Из этой сводной таблицы

примечание

Обратите внимание, что в этом руководстве используются графические материалы из книги Microsoft Office Conditional Formatting Samples and guidelines. Чтобы посмотреть, как отображаются описанные выше правила, скачайте эту книгу и откройте ее в Редакторе таблиц.