Условное форматирование. По формуле.
Условное форматирование строк по формуле





Настройка правил условного форматирования с использованием формулы: выделение столбцов (проверка текста в шапке таблицы), обработка пустот, выделение повторов.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

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

Настройка правил условного форматирования с использованием формулы: выделение столбцов (проверка текста в шапке таблицы), обработка пустот, выделение повторов. Используя любую формулу проверки значений можно выделить ячейки или целые строки, отвечающие Вашему условию (или набору условий). Если в ячейке с помощью знака равно удаётся получить ответ Excel ИСТИНА, то такую формулу точно можно использовать для выделения соответствующих ячеек цветом.

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

Для занятия Вам потребуются книги Excel с заготовками таблиц, по которым составлен конспект - эти файлы прикреплены в самом низу страницы (без проблем откроются в Excel версии 2007 и новее)

 

Как выделить выходные дни красным цветом

При помощи условного форматирования можно решать самые разные задачи, связанные с выделением элементов таблиц – если только есть какая-либо закономерность в данных. Форматирование данных вручную возможно, конечно, но для этого требуется слишком много времени. Например, для того, чтобы выделить строки/столбцы с выходными днями красным цветом; или же сравнить два списка и найти отличия (выделить цветом новые записи или наоборот те, которые встречаются в другом списке); оформление промежуточных итогов вручную – это вообще большая головная боль – на этом уроке научимся автоматизировать и эту задачу – автоматически выделять заливкой все строки с итогами

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

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

Формула проверки условия получается достаточно большая и писать ее в окошке создания правила условного форматирования будет неудобно, поэтому проще написать ее в ячейке Excel, а потом скопировать и вставить. Для какой ячейки писать эту формулу? – Очевидно для той, которая будет активна на момент создания правила, поэтому лучше следовать вот такому порядку: когда придет время выделить диапазон ячеек для форматирования, начнем делать это с верхнего левого угла, т.е. с ячейки B3; стало быть именно для нее напишем формулу проверки условия и именно ее будем копировать. В какой ячейке писать эту формулу – неважно, а получится она в итоге вот такая: =ИЛИ(ДЕНЬНЕД(B$2;2)=6;ДЕНЬНЕД(B$2;2)=7)

Результатом в ячейке может быть одно из двух значений: ИСТИНА [TRUE] (если хотя бы одно из двух условий выполняется – то есть дата соответствует субботе или воскресенью) либо ЛОЖЬ [FALSE] (если ни одно из условий не выполняется – то есть дата не соответствует субботе или воскресенью). А правило условного форматирования применяет к ячейкам форматирование как раз в том случае, когда проверка выполняется, то есть результат ее – ИСТИНА [TRUE]. Копируем эту формулу (не ячейку, а именно текст формулы из ячейки либо через строку формул):

Выделяем диапазон данных на листе B2:L7, в которых нужно выборочно применить заливку выходных дней, и выполняем команду Главная – Стили – Условное форматирование – Создать правило [HomeStylesConditional_FormattingNew_Rule], выбираем шестой тип Использовать формулу для определения форматируемых ячеек [Use_a_formula…] и в нижней части окна вставляем скопированную формулу. Не забываем выбрать оформление для ячеек, которые по итогам проверки будут соответствовать субботам или воскресеньям, и можно нажимать ОК. Готово!

Отредактировать (изменить) или удалить правило всегда можно в диалоговом окне, которое открывается при выполнении команды Главная – Стили – Условное форматирование – Управление правилам [HomeStylesConditional_FormattingManager_Rules]

 

Как найти в списке новые значения

Для работы потребуется файл, прикрепленный в самом низу страницы: Ex-РВ-Урок-15-Условное форматирование-Поиск значений, начнем с листа Новые названия. Хочется разобрать очень часто задаваемый вопрос по сравнению двух списков – как найти и выделить в одном списке значения, которых нет в другом? Основных причин таких поисков на практике встречала две:

  1. Кто-то добавил в список новые названия (Вы, разумеется, просили этого не делать, но так или иначе файл пострадал и нужно найти виновные в этом строчки)
  2. Выгрузка из базы данных всегда имела определенное число строк, но в какой-то момент их стало больше (или меньше). Как найти новые значения?

Для данного вопроса задача минимум – просто найти новые значения, задача максимум – выделить эти новые значения в списке любым цветом. У меня в примере будет зоопарк и два списка: старый и новый. В новом списке позиций очевидно больше. И сейчас постараемся с минимальными трудозатратами ответить на вопрос: какие новые звери появились в этом зоопарке :-)

На помощь можно привлечь уже знакомую функцию ВПР [VLOOKUP], но можно заменить ее и более простой функцией, которая также подойдет для решения этой задачки –ПОИСКОПЗ [MATCH]. Любая из поисковых функций либо найдет каждое значение из нового списка в старом, либо нет – последние, собственно, и будут теми значениями, которые мы ищем. Как и в прошлый раз, предлагаю сначала написать формулу в ячейке листа, а потом скопировать ее для создания правила. У меня получилось вот так: =ПОИСКПОЗ(A4;D:D;0) – никаких знаков долларов, кстати

Результат работы формулы очевиден – ошибка вида #Н/Д (нет данных) содержится напротив тех названий, которых и в самом деле нет в старом списке. Теперь нужно подумать, как сформулировать правило проверки, ведь оно должно выполняться именно в том случае, когда функция будет выдавать ошибку. В этом поможет функция из категории Проверка свойств и значенийЕОШИБКА [ISERROR], которая проверяет, есть ли в расчете ошибка и возвращает ИСТИНА [TRUE], если ошибка есть, либо ЛОЖЬ [FALSE], если ошибки нет, то есть расчет может быть выполнен. Вот так выглядит моя формула после всех преобразований: =ЕОШИБКА(ПОИСКПОЗ(A4;D:D;0))

А вот теперь воспроизводим уже знакомый порядок действий:

Дословно получилось следующее правило: если есть ошибка в расчете, тогда формат

Отредактировать (изменить) или удалить правило всегда можно в диалоговом окне, которое открывается при выполнении команды Главная – Стили – Условное форматирование – Управление правилам [HomeStylesConditional_FormattingManager_Rules]

 

Как в большом списке найти несколько нужных значений

По такому же алгоритму и в той же логике решается похожая задача: как найти и выделить в списке только те значения, которые есть в другом списке? Только на этот раз нас интересует не тот случай, когда функция ПОИСКОПЗ [MATCH] не найдет данных, а напротив – выделять цветом будет только те значения из Полного списка, которые есть в списке ВАЖНЫЕ. Поэтому нужно будет скорректировать формулу проверки условия – добавить функцию НЕ [NOT]. Дословно условие проверки получается следующее: если нет ошибки в расчете, тогда формат…: =НЕ(ЕОШИБКА(ПОИСКПОЗ(A4;D:D;0)))

Отредактировать (изменить) или удалить правило всегда можно в диалоговом окне, которое открывается при выполнении команды Главная – Стили – Условное форматирование – Управление правилам [HomeStylesConditional_FormattingManager_Rules]

 

Как быстро выделить строки с итогами и пустыми значениями (ячейками)

Положим, у нас есть таблица с большим количеством итоговых строк. Нужно быстро настроить для таких строк заливку и границы. И можно это сделать, если найти закономерность. В нашем случае она есть – в каждой ячейке столбца A, соответствующей итоговой строке, есть слова Итого по – именно их можно использовать для настройки правила условного форматирования по шестому типу. На помощь можно привлечь функцию ЛЕВСИМВ [LEFT] – с ее помощью проверим первые 8 знаков ячейки столбца A и если они равны Итого по – применим условное форматирование. В адресе проверяемой ячейки не забываем знак доллара – перед буквой столбца, так как именно на столбец A нужно ориентироваться при форматировании ячеек всех других столбцов. Такая вот получилась формула: =ЛЕВСИМВ($A4;8)="Итого по"

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

И еще одно правило я бы для этой таблицы предусмотрела. Если наш коллега забыл вписать какое-либо наименование, то пусть соответствующая ему строка выделяется желтой заливкой. Наверняка, пользователя файла будет беспокоить такое поведение таблицы и причина быстро обнаружится. Алгоритм действий аналогичен только что описанному, но на этот раз проверяемая ячейка не должна содержать никакого текста. И сформулировать такое условие будет удобно при помощи функции из категории Проверки свойств и значений ЕПУСТО [ISEMPTY]: =ЕПУСТО($A4) – все по тем же причинам нужен один знак доллара перед буквой столбца проверяемой ячейки:

Если для конкретной таблицы необходимо напротив выделить цветом все строчки для непустых ячеек, то измените формулу проверки одним из двух способов:

  1. =НЕ(ЕПУСТО($A4))
  2. =ЕПУСТО($A4)=ЛОЖЬ

Отредактировать (изменить) или удалить правило всегда можно в диалоговом окне, которое открывается при выполнении команды Главная – Стили – Условное форматирование – Управление правилам [HomeStylesConditional_FormattingManager_Rules]

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

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

Строки, столбцы, листы - перемещение по документу и управление количеством и положением его элементов
Операции со строками, столбцами и листами
В этом материале подробно описаны операции вставки, удаления, скрытия, отображения, копирования и перемещения строк, столбцов и листов в Excel.

Подсчёт количества ячеек и вычисление средних показателей
Математические и статистические функции
Как посчитать количество ячеек, отвечающих поставленному условию. Как посчитать все заполненные или все пустые ячейки. Расчёт среднеарифметического значения.

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

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.