Построчное сравнение таблиц в EXCEL

Сравним две таблицы имеющих одинаковую структуру (одинаковое количество строк и столбцов). Таблицы будем сравнивать построчно: выделим те значения из строки1 таблицы1, которые содержатся в строке1 таблицы2, а также значения из строки2 таблицы1, которые содержатся в строке2 таблицы2 и т.д.

Пусть на листах Полугодие1 и Полугодие2 имеется две таблицы со значениями за каждый месяц.

Выделим те значения из строк таблицы Полугодие1 , которые содержатся в соответствующих строках таблицы Полугодие2 .

Как видно из рисунков выше, в 1-х строках обоих таблиц совпадают значения 11 и 7, во 2-х строках - только значение 3 встречается в обоих таблицах.

Чтобы это реализовать - используем Условное форматирование . Предварительно создадим Именованный диапазон Массив с относительной адресацией . Именованный диапазон нам необходим, т.к. в правилах Условного форматирования нельзя указывать ссылку на другой лист (в EXCEL 2007 и ранее, в EXCEL 2010 можно).

• выделите на листе Полугодие1 ячейку А7 ;
• создайте именованный диапазон через меню Формулы/Определенные имена/ Присвоить имя ;
• в качестве ссылки на диапазон введите =полугодие2!$A7:$F7
• нажмите ОК.

Теперь выделите на листе Полугодие1 диапазон А7:А16 и создайте правило Условного форматирования (меню Главная/ Стили/ Условное форматирование/ Создать правило ) с формулой =МАКС(--(A7=Массив))

Обратите внимание, что формула работает как формула массива , хотя введена в правило как обычная формула (по другому и не возможно).

Покажем как она работает на примере 2-й строки таблицы (8-я строка листа, см. файл примера ).

Так как в правиле Условного форматирования и в Именованном диапазоне Массив использованы относительные ссылки, то для 2-й строки (ячейка А8 ) формула примет вид =МАКС(--(А8=полугодие2!$A8:$F8)) , т.е. значение ячейки А8 (число 7) из таблицы Полугодие1 будет сравниваться с каждым значением второй строки из таблицы Полугодие2. В результате сравнения получим массив {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}, т.е. числа 7 во второй строке таблицы Полугодие2 нет. Далее двойное отрицание (--) преобразует полученный массив в массив чисел {0;0;0;0;0;0}, т.к. ЛОЖЬ соответствует 0, а ИСТИНА соответствует 1. Функция МАКС() вернет 0, т.е. выражение не истинно и форматирование не будет произведено.

А, например, для ячейки С8 (число 3) формула примет вид =МАКС(--(С8=полугодие2!$A8:$F8)) и мы получим массив {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}, т.е. число 3 содержится в пятом столбце во второй строке таблицы Полугодие2 . Далее двойное отрицание (--) преобразует полученный массив в массив чисел {0;0;0;0;1;0}. Функция МАКС() вернет 1, т.е. выражение истинно и форматирование будет произведено, что и мы и наблюдаем на картинке выше.