Сравнение двух списков в Excel с помощью условного форматирования

Этот урок описывает, как сравнить два списка в Excel с помощью условного форматирования. Для примера возьмём два списка команд НФЛ (Национальная футбольная лига).

Чтобы выделить команды в первом списке (не во втором!), выполните следующие действия:

1. Сперва выделите диапазон A1:A18 и дайте ему имя «firstList».
2. Затем выделите диапазон B1:B20 и назовите его «secondList».
3. Теперь выделите диапазон A1:A18.
4. На вкладке Главная (Home) выберите команду Условное форматирование > Создать правило (Conditional Formatting > New rule).
5. Выберите Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format).
6. Введите следующую формулу:

   =СЧЕТЕСЛИ(secondList;А1)=0
=COUNTIF(secondList,А1)=0

7. Задайте стиль форматирования и нажмите ОК.Результат: Команд «Miami Dolphins» и «Tennessee Titans» нет во втором списке.

   

   Пояснение:

   • Формула =СЧЕТЕСЛИ (secondList;А1) подсчитывает количество команд во втором списке, которые идентичны команде из ячейки A1. Если СЧЕТЕСЛИ(secondList;A1)=0, значит команды из ячейки A1 нет во втором списке. В результате Excel окрашивает ячейку голубым цветом.
   • Так как мы выбрали диапазон A1:A18 прежде, чем применили условное форматирование, Excel автоматически скопирует формулы в остальные ячейки.
   • Таким образом, ячейка A2 содержит следующую формулу:=СЧЕТЕСЛИ(secondList;А2)=0,ячейка А3:

     =СЧЕТЕСЛИ(secondList;А3)=0 и т.д.

8. Чтобы выделить команды во втором списке, которые не входят в первый, выделите диапазон B1:B20 и создайте новое правило, используя формулу ниже:

   =СЧЕТЕСЛИ(firstList;B1)=0
=COUNTIF(firstList,B1)=0

9. Затем задайте стиль форматирования (оранжевую заливку) и нажмите ОК.Результат: Команд «Denver Broncos», «Arizona Cardinals», «Minnesota Vikings» и «Pittsburgh Steelers» нет в первом списке.