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

Рубрика: 7. Полезняшки Excel

Если требуется сравнить два списка элементов и определить, какие различия есть между ними, воспользуйтесь условным форматированием (рис. 1). [1] В примере используется текст, но этот метод работает и с числовыми данными.

%d1%80%d0%b8%d1%81-1-%d0%bc%d0%be%d0%b6%d0%bd%d0%be-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d1%82%d1%8c-%d1%83%d1%81%d0%bb%d0%be%d0%b2%d0%bd%d0%be%d0%b5-%d1%84%d0%be%d1%80%d0%bc

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

Скачать заметку в формате Word или pdf, примеры в формате Excel

Диапазоны были поименованы с помощью команды Формулы –> Определенные имена –> Присвоить имя (как это сделать, подробнее см. Excel. Имена диапазонов). Именовать диапазоны не обязательно, но это помогает при работе с ними. Сначала добавим условное форматирование к старому списку.

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

%d1%80%d0%b8%d1%81-2-%d0%bf%d1%80%d0%b8%d0%bc%d0%b5%d0%bd%d0%b5%d0%bd%d0%b8%d0%b5-%d1%83%d1%81%d0%bb%d0%be%d0%b2%d0%bd%d0%be%d0%b3%d0%be-%d1%84%d0%be%d1%80%d0%bc%d0%b0%d1%82%d0%b8%d1%80%d0%be%d0%b2

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

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

%d1%80%d0%b8%d1%81-3-%d1%83%d1%81%d0%bb%d0%be%d0%b2%d0%bd%d0%be%d0%b5-%d1%84%d0%be%d1%80%d0%bc%d0%b0%d1%82%d0%b8%d1%80%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d0%bf%d0%be%d0%b7%d0%b2%d0%be%d0%bb%d1%8f

Рис. 3. Условное форматирование позволяет наглядно показать разницу между двумя списками

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 177–179.


Прокомментировать