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

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

Сравнение списков в Excel

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

  1. Сперва выделите диапазон A1:A18 и дайте ему имя "firstList".
  2. Затем выделите диапазон B1:B20 и назовите его "secondList".
  3. Теперь выделите диапазон A1:A18.
  4. На вкладке Главная (Home) выберите команду Условное форматирование > Создать правило (Conditional Formatting > New rule).

    Сравнение списков в Excel

  5. Выберите Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format).
  6. Введите следующую формулу:

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

  7. Задайте стиль форматирования и нажмите ОК.

    Сравнение списков в Excel

    Результат: Команд "Miami Dolphins" и "Tennessee Titans" нет во втором списке.

    Сравнение списков в Excel

    Пояснение:

    • Формула =СЧЕТЕСЛИ (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" нет в первом списке.

    Сравнение списков в Excel

03.10.2015 20:55
2545

Комментарии

Нет комментариев. Ваш будет первым!