Как применить условное форматирование на основе функции ВПР в Excel?

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

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

1. На листе Score выделите диапазон оценок студентов, которые нужно обработать (без заголовков; в данном примере — B3:C26). Перейдите на вкладку Главная, нажмите Использовать условное форматирование и выберите Создать правило.

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

1. Выберите Использовать формулу для определения форматируемых ячеек.
2. Введите следующую формулу в поле Форматировать значения, для которых данная формула принимает значение ИСТИНА:

   =VLOOKUP($B3,'Score of Last Semester'!$B$2:$C$26,2,FALSE) < Score!$C3

3. Нажмите кнопку Формат, чтобы выбрать нужное форматирование.

Примечание:В этой формуле

• $B3 ссылается на имя первого студента на листе Score. При применении условного форматирования к нескольким строкам Excel автоматически корректирует эту ссылку для каждой строки.
• „Score of Last Semester"!$B$2:$C$26 задаёт диапазон поиска оценок за прошлый семестр. При необходимости скорректируйте его, если ваш список длиннее или начинается и заканчивается в других строках.
• 2 означает, что значения, извлекаемые из диапазона поиска, расположены во втором столбце этого диапазона.
• Score!$C3 указывает на текущую оценку студента в форматируемом рабочем листе.

3. В диалоговом окне Установить формат ячейки перейдите на вкладку Заливка, выберите цвет выделения и нажмите ОК > ОК, чтобы применить изменения и закрыть диалоговые окна.

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

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

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

Примечание: переместите полосу прокрутки по оси Y, чтобы просмотреть изображение выше.

В данном случае список победителей может находиться на листе Sheet1, а полный реестр студентов — на листе Sheet2. Чтобы выделить имена диапазонов в списке победителей, которые также присутствуют в реестре, выполните следующие действия:

1. Выделите имена в списке победителей (без заголовков), затем перейдите в меню Главная > Использовать условное форматирование > Создать правило.

2. В диалоговом окне «Создание нового правила форматирования» выполните следующие действия:

1. В разделе Выбор типа правила выберите Использовать формулу для определения форматируемых ячеек.
2. Введите следующую формулу в поле Форматировать значения, для которых данная формула принимает значение ИСТИНА:

   =NOT(ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE)))

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

Примечание:

• $C3 ссылается на имя в списке победителей — убедитесь, что ваш выбор соответствует фактической структуре данных.
• Sheet2!$B$2:$C$24 — это таблица поиска со списком всех студентов. При необходимости скорректируйте диапазон под ваш лист.
• 1 указывает, что функция ВПР будет искать в первом столбце ограниченного диапазона.

Если вам нужно Выделить имена диапазонов из списка победителей, которые ненайдены в реестре студентов, используйте противоположную формулу:

=ISNA(VLOOKUP($C3,Sheet2!$B$2:$C$24,1,FALSE))

3. В диалоговом окне Установить формат ячейки перейдите на вкладку Заливка, выберите желаемый цвет фона, затем нажмите ОК > ОК, чтобы завершить настройку.

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

Совет: если результаты кажутся неожиданными, дважды проверьте диапазоны поиска на наличие лишних пробелов или несогласованных значений, которые могут нарушить логику точного совпадения функции ВПР. При необходимости используйте функцию СЖПРОБЕЛЫ во вспомогательных столбцах для стандартизации данных.

Если у вас установлен Kutools для Excel, вы получаете дополнительную гибкость и удобство при сравнении списков и применении условного форматирования. Функция Выбрать одинаковые/разные ячейки позволяет выделять совпадающие или несовпадающие значения всего за несколько кликов, минимизируя риск ошибок, характерных для ручного ввода формул.

Kutools для Excel включает более 300 практических инструментов для Excel, обеспечивая надёжную поддержку при сравнении и форматировании данных. Это особенно полезно для пользователей, регулярно работающих с объёмными списками или разнородными наборами данных. Вы можете бесплатно протестировать весь функционал в течение 60 дней без требования предоставления данных кредитной карты.

1. Нажмите Kutools > Выделить > Выбрать одинаковые/разные ячейки, чтобы запустить инструмент.

2. В диалоговом окне «Выбрать одинаковые/разные ячейки» настройте параметры следующим образом:

1. В поле Найти значения в выберите столбец «Имя» из списка победителей.
2. В поле Согласно выберите столбец «Имя» из реестра студентов.
3. Если ваши данные содержат заголовки, вы можете установить флажок Включить заголовки в зависимости от ваших потребностей.
4. В разделе На основе выберите Каждая строка, чтобы сравнивать построчно.
5. В разделе Найти выберите либо Одинаковые значения, либо Разные значения, в зависимости от того, хотите ли вы выделять совпадения или различия.
6. Установите флажок Заполнить цвет фона и выберите желаемый цвет для выделения.
7. Если вы хотите выделить всю строку, выберите Выбрать всю строку.

3. Нажмите OK, чтобы выполнить операцию. Инструмент немедленно выделит и выберет строки со совпадающими (или различающимися) значениями. Кроме того, в диалоговом окне отображается количество выбранных строк, что позволяет быстро оценить результаты сравнения.

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