УДК 004.4

Excel: условное форматирование с помощью формул

Кузнецов Георгий Викторович – Уфимский государственный нефтяной технический университет.

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

Ключевые слова: Excel, условное форматирования, выделение дат, выделение цветом.

Microsoft Excel (также иногда называется Microsoft Office Excel) – программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS, а также Android, iOS и Windows Phone. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application). Microsoft Excel входит в состав Microsoft Office.

Условное форматирование – инструмент, который позволяет применить к ячейкам определенные параметры (фон, границы, шрифт) на основе их значений. Условное форматирование в MS Excel облегчает анализ больших объемов данных в таблицах, путем графического выделения (форматирования) ячеек таблицы в соответствии с определенным пользователем условием. В версии Excel 2010 возможности условного форматирования были значительно расширены и пользователю предоставлена возможность как использовать множество предопределенных шаблонов, так и настроить свой собственный.

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

Рисунок 1. Работа условного форматирования.

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

Но условное форматирование поддерживает не только выделение повторяющихся значений! Его возможности намного выше (рисунок 2):

Рисунок 2. Виды условного форматирования в Excel.

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

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

Для вставки формулы в условное форматирование нужно использовать пункт «Условное форматирование – Создать правило – Использовать формулу для определения форматируемых ячеек». Синтаксис формулы ничем не отличается от синтаксиса, используемого при вставке обычных формул в ячейки книги. Единственное отличие: условное форматирование будет выделять ячейки, для которых результат вычисления формулы является истинным, что накладывает определенные ограничения на использование формул. Также для формул можно использовать относительные и абсолютные ссылки на ячейки листа, а также брать ячейки не из диапазона условного форматирования!

Рассмотрим простой пример применения формулы для условного форматирования – работа с датами. Предположим, у нас есть график работ, где в строках записаны имена рабочих, а в колонках – даты (рисунок 3):

Рисунок 3. Журнал рабочего времени.

Было бы удобно выделять цветом колонку с датой сегодняшнего дня. С помощью условного форматирования это делается элементарно (рисунок 4):

Рисунок 4. Выделение сегодняшней даты.

Обратите внимание, что в формуле условного форматирования мы используем ячейки, которые даже не входят в диапазон условного форматирования! Также обратите внимание, что мы «закрепили» строку 1 с помощью знака «$». Если этого не сделать, то мы получим не совсем корректную работу (рисунок 5):

Рисунок 5. Выделение ячейки, а не всего столбца.

Аналогичным образом можно выделить прошедшие дни, которые нас не интересуют (рисунок 6):

Рисунок 6. Выделяем прошедшие дни.

Также с помощью условного форматирования можно с легкостью автоматизировать выделение выходных дней. Делается это опять-таки с помощью формулы (рисунок 7):

Рисунок 7. Выделение выходных дней с помощью формулы.

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

Также рассмотрим формулу для выделения выходных:

=ИЛИ(ДЕНЬНЕД(B$1;11)=6;ДЕНЬНЕД(B$1;11)=7)

Так как условного форматирование выделяет ячейки, для которых результат выполнения формулы является истинным, то для обработки двух и более «подформул» их необходимо заключить в И() или ИЛИ(). ИЛИ() возвращает ложь только в случае, если ВСЕ аргументы возвращают ложь.

Соответственно, т.к. у нас два выходных дня (суббота и воскресенье), то в нашем случае, у функции ИЛИ() два аргумента.

Функция ДЕНЬНЕД() возвращает день недели у определенной даты. Параметр «11» включает привычный нам порядок дней в неделе (1 – понедельник, …, 7 – воскресенье).

Сравнивая даты в названиях колонок с «субботой» или «воскресеньем» и используя функцию ИЛИ() мы возвращаем истину только для дат, дни недели которых приходятся на субботу или воскресенье. А условное форматирование форматирует эти ячейки установленным нами образом.

Выводы

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

Стоит отметить, что условное форматирование также отлично работает во многих аналогах Excel: LibreOffice, OpenOffice, OnlyOffice, Мой Офис, что делает его применение более предпочтительным, чем создание сложных условий выделения с помощью VBA.

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

Список литературы

Стратилатова Е.Н., Воробьева Е.В. Проведение лабораторных работ по математической статистике с использованием MS Excel // Актуальные проблемы преподавания математики в техническом вузе. – 2013. – № 1. – С. 105-108.
Царева Е.А. Использование программы Microsoft Excel для обработки результатов лабораторных работ по физике // Системы компьютерной математики и их приложения. – 2015. – № 16. – С. 267-269.
Сайт «Инфопедиа» [Электронный ресурс]. – Режим доступа: https://infopedia.su, свободный.
Сайт «MS Excel для новичков и профессионалов» [Электронный ресурс]. – Режим доступа: https://excel2.ru/, свободный.
Сайт «Планета Excel» [Электронный ресурс]. – Режим доступа: https://www.planetaexcel.ru/, свободный.

Интересная статья? Поделись ей с другими: