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

Например, вы перечислили все оценки студентов и результаты последнего семестра на двух листах, как показано на скриншоте ниже. Теперь вы хотите сравнить оценки на двух листах и ​​выделить строки на листе Оценка , если оценки выше, чем в прошлом семестре. В этой ситуации вы можете применить условное форматирование на основе функции ВПР в Excel.

1 . В таблице оценок выберите оценки учащихся, кроме заголовков (в моем случае я выбрал B3: C26), и нажмите Главная > Условное форматирование > Новое правило . См. Снимок экрана:

2 . В диалоговом окне «Новое правило форматирования» сделайте следующее:
(1) Щелкните, чтобы выбрать Используйте формулу, чтобы определить, какие ячейки нужно форматировать в Выберите Поле со списком Тип правила ;
(2) В поле Формат значений, в котором эта формула верна , введите эту формулу = VLOOKUP ($ B3, «Оценка за последний семестр»! $ B $ 2: $ C $ 26,2, FALSE) ;
(3) Нажмите на Формат кнопка.

Примечание: в формуле выше

• $ B3 – первая ячейка с именем студента в таблице Score ;
• ‘Score of Last Semester’! $ B $ 2: $ C $ 26 – это таблица результатов за последний семестр в таблице Score of Last Semester ;
• 2 означает поиск значений во втором столбце таблицы результатов за последний семестр.
• Score! $ C3 – первая ячейка в таблице Score лист.

3 . В диалоговом окне «Формат ячеек» перейдите на вкладку Заливка , выберите цвет заливки и нажмите OK > OK , чтобы закройте два диалоговых окна.

Теперь вы увидите, соответствует ли балл ученика таблица Оценка выше, чем таблица Оценка последнего семестра , строка этого студента будет выделена автоматически. См. Снимок экрана:

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

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

Например, у меня список победителей на Sheet1 и список студентов на Sheet2 , как показано на скриншоте ниже. Теперь я сопоставлю список победителей и список студентов с помощью функции ВПР. , а затем выделите совпавшие строки в списке победителей.

1 . Выберите список победителей, кроме заголовков, и нажмите Главная > Условное форматирование > Новое правило .

2 . В диалоговом окне «Новое правило форматирования» сделайте следующее:
(1) В списке Выберите тип правила щелкните, чтобы выбрать Использовать формулу чтобы определить, какие ячейки следует форматировать ;
(2) В поле Форматировать значения, в которых эта формула верна , введите эту формулу = НЕ (ISNA (VLOOKUP ($ C3, Sheet2! $ B $ 2: $ C $ 24,1, FALSE))) ;
(3) Нажмите кнопку Форматировать .

Примечание . В формуле выше

• $ C3 – первое имя в списке победителей;
• Sheet2! $ B $ 2: $ C $ 24 – список студентов на Sheet2;
• 1 означает поиск совпадающего значения в первом столбце списка студентов.

Если вам нужно выделить несовпадающие значения, вы можете использовать эту формулу = ISNA (VLOOKUP ($ C3, Sheet2! $ B $ 2: $ C $ 24,1, ЛОЖЬ))

3 . В диалоговом окне «Формат ячеек» перейдите на вкладку Заливка , выберите цвет заливки и нажмите OK > OK . чтобы закрыть оба диалога.

Теперь вы увидите, есть ли имя в победителе list совпадает (или не совпадает) со списком студентов, строка с таким именем будет выделена автоматически.

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

Kutools for Excel – включает более 300 удобных инструментов для Excel.. Полнофункциональная бесплатная пробная версия на 60 дней, кредитная карта не требуется! Получить сейчас

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

2 . В диалоговом окне «Выбор одинаковых и разных ячеек» выполните следующие действия:
(1) Выберите столбец «Имя» в списке победителей в поле Найти значения в , выберите столбец «Имя» для список учащихся в поле Согласно . Необязательно устанавливать флажок У моих данных есть заголовки , как вам нужно.
(2) Установите флажок параметр Каждая строка в разделе На основе ;
(3) Проверьте одинаковые значения или Разные значения , если вам нужно в разделе Найти ;
(4) Установите флажок Цвет фона заливки параметр и выберите цвет заливки из раскрывающегося списка ниже;
(5) Установите флажок Выбрать целые строки .

. Нажмите кнопку Ok , чтобы применить эту функцию.

Теперь вы увидите, что все строки с совпадающими (или несогласованными) значениями выделены и выбраны одновременно, и одновременно появляется диалоговое окно, в котором указывается, сколько строк было выбрано.