Как сравнить данные двух таблиц в excel на совпадения построчно

Корпорация "Центр"

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

history 23 сентября 2014 г.
    Группы статей

  • Имена
  • Сравнение списков
  • Условное форматирование

Сравним две таблицы имеющих одинаковую структуру (одинаковое количество строк и столбцов). Таблицы будем сравнивать построчно: выделим те значения из строки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, т.е. выражение истинно и форматирование будет произведено, что и мы и наблюдаем на картинке выше.

Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

Вопрос от пользователя

Здравствуйте!

У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

Корпорация "Центр"

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

Пример работы с функцией ВПР

В качестве примера я взял две небольших таблички, представлены они на скриншоте ниже. В первой таблице (столбцы A, B — товар и цена) нет данных по столбцу B; во второй — заполнены оба столбца (товар и цена). Теперь нужно проверить первые столбцы в обоих таблицах и автоматически, при найденном совпадении, скопировать цену в первую табличку. Вроде, задачка простая.

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать.

Ставим указатель мышки в ячейку B2 — то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:

=ВПР( A2 ; $E$1:$F$7 ; 2 ; ЛОЖЬ )

где:

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок "$" — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали — будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

Значения из одной таблицы подставлены в другую

Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).

Ну а у меня на этом пока всё, удачи!

Корпорация "Центр"

Ссылка на основную публикацию