УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL4

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

4. УСЛОВНОЕ ФОРМАТИРОВАНИЕ В MS EXCEL

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

"Умный" способ адресации

=СУММ(Результаты)

=СУММ(Результаты[#Данные])

=СУММ(Результаты[Продажи])

=Результаты[@Прибыль] Таблица 1. Использование формул умных таблиц.

Ссылки

Формула возвращает Стандартный диапазон

По умолчанию умная таблица, которая названа "Результаты" ссылается на область своих данных 87

Тот же результат вернёт данная формула, где область 87 данных указана в явном виде. Суммируем область данных столбца "Продажи". Если надо создать именованный диапазон, который будет ссылаться на столбец умной таблицы, то надо использовать синтаксис Результаты[Продажи]. 54

Данную формулу мы вводили в строке 3. @ - означает текущую строку, а Прибыль - 6 столбец, из которого возвращаются данные. B3:E7

B3:E7

D3:D7

E3

=СУММ(Результаты[Продажи]:Рез ультаты[Прибыль])

=СУММ(Результаты[@])

=СЧЁТЗ(Результаты[#Заголовки])

=Результаты[[#Итоги];[Продажи]] Ссылка на диапазон столбцов: от колонки "Продажи", до колонки "Прибыль" включительно. 87 Обратите внимание на оператор ":", который создаёт диапазон. Формулу вводили в троке 3. Она вернула всю строку таблицы. Подсчёт количества элементов в #Заголовки. 11

4

Формула возвращает итоговую строку для столбца Продажи. Это не одно и тоже, что Результаты[Продажи], так как итоговая функция может быть разной, например, средней величиной. 54 D3:E7

B3:E3

B2:E2

D8

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат. Рассмотрим несколько задач:

Сравнение с постоянным значением (константой)

Задача1. Сравним значения из диапазона A1:D1 с числом 4. • введем в диапазон A1:D1 значения 1, 3, 5, 7 • выделим этот диапазон; • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/

Правила выделения ячеек/ Меньше); • в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования. • Нажмем ОК.

Результат можно увидеть в файле примера на листе Задача1.

Сравнение со значением в ячейке (абсолютная ссылка)

Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4. Задача2. Сравним значения из диапазона A1:D1 с числом из ячейки А2. • введем в ячейку А2 число 4; • выделим диапазон A1:D1; • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше); • в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2).

Нажмите ОК. В результате, все значения из выделенного диапазона A1:D1 будут сравниваться с одной ячейкой $А$2. Те значения из A1:D1, которые меньше A2 будут выделены заливкой фона ячейки. Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/

Управление правилами; затем дважды кликните на правиле или нажмите кнопку Изменить правило. В результате получим следующее диалоговое окно:

Попарное сравнение строк/ столбцов (относительные ссылки)

Будем производить попарное сравнение значений в строках 1 и 2. Задача3. Сравнить значения ячеек диапазона A1:D1 со значениями из ячеек диапазона A2:D2. Для этого будем использовать относительную ссылку. • введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями); • выделим диапазон A1:D1; • применим к выделенному диапазону Условное форматирование на значение Меньше (Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше) • в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2). Убедитесь, что знак $ отсутствует перед названием столбца А. Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.

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

Примечание-отступление: О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками

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

СОВЕТ: Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя, там будет отображен адрес активной ячейки A1 или D1. Почему возможно 2 варианта и в чем разница для правил условного форматирования?

Посмотрим внимательно на второй шаг решения предыдущей задачи 3 выделение диапазона A1:D1. Указанный диапазон можно выделить двумя способами: выделить ячейку А1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1; либо, выделить ячейку D1, затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1. Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1, а во втором D1! Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой. Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2, мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2. Т.к. правило распространяется на диапазон A1:D1, то B1 будет сравниваться с В2 и т.д. Задача будет корректно решена. Если при создании правила Условного форматирования активной была ячейка D1, то именно ее значение будет сравниваться со значением ячейки А2. А значение из A1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A2, EXCEL выберет самую последнюю ячейку XFD для С1, затем предпоследнюю для B1 и, наконец XFB2 для А1). Убедиться в этом можно, посмотрев созданное правило: • выделите ячейку A1; • нажмите Главная/ Стили/ Условное форматирование/ Управление правилами; • теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки <XFB2 (или <XFB$2).

EXCEL отображает правило форматирования (Значение ячейки <XFB2) применительно к активной ячейке, т.е. к A1. Правильно примененное правило, в нашем случае, выглядит так:

Выделение ячеек с текстом

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

Применение нескольких правил

Часто требуется выделить значения или даже отдельные строки в зависимости от того диапазона, которому принадлежит значение. Используем Условное форматирование для выделения строк таблицы, в которых числа принадлежат к определенному диапазону. Например, если число в определенном столбце таблицы меньше 0, то вся строка будет выделена красным. Предположим, что пользователь заполняет таблицу о приросте продаж продукции. Требуется, чтобы EXCEL автоматически выделял строки следующим образом: • зеленым, если прирост продаж составил более 50%;

красным, если прирост продаж отрицательный, т.е. менее 0% (падение продаж); желтым - во всех остальных случаях (от 0% до 50%). Конечный результат должен выглядеть так.

Решение. Сначала создадим правило, по которому строки выделяются зеленым цветом если прирост продаж составил более 50%: • выделите диапазон, в который пользователь будет вводить данные (всю таблицу); • вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Управлениеправилами). Откроется окно Диспетчер правил условного форматирования, нажмите Создать правило. Или просто выберите Главная/ Стили/ Условное форматирование/Создать правило; • выберите Использовать формулу для определения форматируемых ячеек; • в поле ниже введите =$B4>=0,5; Обратите внимание на то, что в ссылке на ячейку использована смешанная адресация - именно она позволяет выделять не только ячейку содержащую значение, но и всю строку целиком. Например, для ячейки A5 это правило будет выглядеть =$B5>=0,5, т.е. также как и для ячейки В5. Обратите внимание, что в момент создания правила условного форматирования активной ячейкой у нас является ячейка А4 (если Вы выделяли таблицу начиная с левого верхнего угла, а не с нижнего правого). • нажав кнопку Формат выберите, зеленую заливку;

• нажмите ОК и вернитесь в Диспетчер правил условного

форматирования.

Аналогично создайте правило для выделения краcной заливкой строк со значениями менее 0. Формула в этом случае будет =$B4<0

И сделайте заливку всех ячеек таблицы желтым (кроме заголовков столбцов). СОВЕТ: Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо: • на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить; • выберите в списке пункт Условное форматирование; • будут выделены все ячейки, к которым применены правила Условного форматирования.

Приоритет правил

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

Когда к одной ячейке применяются два или более правил Условного форматирования, приоритет обработки определяется порядком их перечисления в Диспетчере правил условного форматирования. Правило, расположенное в списке выше, имеет более высокий приоритет, чем правило, расположенное в списке ниже. Новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, однако порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз. Например, в ячейке находится число 9 и к ней применено два правила Значение ячейки >6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон. На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная. При удалении правила условного форматирования форматирование вручную остается.

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

Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного

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

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

Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено. Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1<A2 и скопируем ее вправо на 4 ячейки.

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет.

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

До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования имён. Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то сначала определяется имя для этой ячейки, а затем необходимо сослаться на это имя в правиле Условного форматирования. Как это реализовано См. файл примера на листе Ссылка с другого листа.

Поиск ячеек с условным форматированием

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

Другие предопределенные правила

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

106

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее. • Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит…Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами...

Как видно из рисунка выше, Условное форматирование можно настроить выделять не только ячейки, содержащие определенный текст, но и не содержащие, начинающиеся с и заканчивающиеся на определенный

107

текст. Кроме того, в случае условий содержит и не содержит возможно применение подстановочных знаков ? и *. Пусть снова в ячейке имеется слово Дрель. Выделим ячейку и применим правило Текст содержит… Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ, т.к. знак ? означает любой символ. Если в качестве критерия запишем ?????? (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР(), ЛЕВСИМВ(), ДЛСТР(), но этот подход, согласитесь, быстрее. • Повторяющиеся значения… Это правило позволяет быстро настроить Условное форматирование для отображения уникальных и повторяющихся значений. Под уникальным значением Условное форматирование подразумевает неповторяющееся значение, т.е. значение которое встречается единственный раз в диапазоне, к которому применено правило. • Дата…На рисунке ниже приведены критерии отбора этого правила. Для того, чтобы добиться такого же результата с помощью формул потребуется гораздо больше времени.

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

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

Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений.

• Последние 10 элементов. Задача4. Пусть имеется 21 значение, для удобства отсортированных по возрастанию. Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера, лист Задача4. Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они. Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", ... "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10. К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000. Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

Аналогично можно создать правило для выделения нужно количества наибольших значений, применив правило Первые 10 элементов. • Последние 10%

Рассмотрим другое родственное правило Последние 10%. Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10%. В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений.

110

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10. 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы. Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

Правила с использованием формул

Рассмотрим пример: предположим, что необходимо выделять ячейки, содержащие ошибочные значения: • Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1). • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/ Создать правило) • Выберите Использовать формулу для определения форматируемых

ячеек

• В поле «Форматировать значения, для которых следующая формула является истинной» введите =ЕОШ(A1) – если хотим, чтобы выделялись ячейки, содержащие ошибочные значения, т.е. будут выделены #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! (кроме #Н/Д) • Выберите требуемый формат, например, красный цвет заливки. Того же результата можно добиться по другому: • Вызовите инструмент Условное форматирование (Главная/ Стили/ Условное форматирование/Создать правило) • Выделите пункт Форматировать только ячейки, которые содержат; • В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки. СОВЕТ: Отметить все ячейки, содержащие ошибочные значения можно также с помощью инструмента Выделение группы ячеек.