Построчное сравнение таблиц в MS 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, т.е. выражение истинно и форматирование будет произведено, что и мы и наблюдаем на картинке выше.

 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)

Комментарии

futurama3000

добрый день!
почему вы пишете, что в условном форматировании в правилах нельзя указывать ссылки на другие листы??? Это возможно. Все можно сделать и без создания именованных диапазонов. Exce2010

Wasp

Здравствуйте. Проверил на Excel 2010. Действительно, при указании диапазона, к которому применяется условное форматирование, возможно указать другой лист. Однако, после применения мы увидим что ничего не изменилось, т. е. Excel "скинул" эти данные. Т. о. ссылка на другой лист не работает. Если Вы смогли получить нужный результат, то прошу отправить измененный файл на creator@excel2.ru и мы все получим новый опыт и знания :)

Creator

Да, Вы правы, ссылку на другой лист теперь, начиная с EXCEL 2010 можно делать. Статья была написана несколько лет назад и не актуализировалась. Спасибо за комментарий. В текст внесли соответствующие пояснения.

Тем не менее, я продолжаю использовать имена, т.к. при просмотре файла в EXCEL2007, правила с прямыми ссылками на другие листы не работают.

futurama3000

выслал письмо на почту

futurama3000

с файлом