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

На рис. 164.1 приведен пример двух многостолбцовых списков имен. Применение условного форматирования может сделать различия в списках очевидными. Эти примеры списков содержат текст, но рассматриваемый метод работает и с числовыми данными.

Рис. 164.1. Вы можете использовать условное форматирование, чтобы выделить различия в двух диапазонах

Первый список — А2:В31, этот диапазон называется OldList. Второй список — D2:E31, диапазон называется NewList. Диапазоны были названы с помощью команды Формулы ► Определенные имена ► Присвоить имя. Давать названия диапазонам необязательно, но это облегчает работу с ними.

Начнем с добавления условного форматирования к старому списку.

1. Выделите ячейки диапазона OldList.
2. Выберите Главная ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу для определения форматируемых ячеек.
4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0.
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
6. Нажмите кнопку ОК.

Рис. 164.2. Применение условного форматирования

Ячейки в диапазоне NewList используют подобную формулу условного форматирования.

1. Выделите ячейки диапазона NewList.
2. Выберите Главная ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
3. В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
4. Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0.
5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
6. Нажмите кнопку ОК.

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

Обе формулы условного форматирования используют функцию СЧЁТЕСЛИ. Она рассчитывает, какое количество раз определенное значение появляется в диапазоне. Если формула возвращает 0, это означает, что элемент не входит в диапазон. Таким образом, условное форматирование берется за дело, и цвет фона ячейки меняется.

Рис. 164.3. Условное форматирование приводит к тому, что различия в двух списках выделяются

Ссылка на ячейку в функции СЧЁТЕСЛИ всегда должна быть в верхней левой ячейке выбранного диапазона.