СРАВНЕНИЕ ДВУХ ТАБЛИЦ МЕЖДУ СОБОЙ ИНСТРУМЕНТАМИ EXCEL

На просторах интернета, да и в реальной жизни я часто слышу просьбу помочь сравнить две таблицы между собой. К примеру, предприятие отгружает свою продукцию покупателю.  И покупатель, и поставщик ведут реестр отгруженной продукции. И часто – не по халатности, а по техническим причинам – например, просто одна из сторон не успела внести обновленную информацию, или товар уже отправлен, но еще не поступил к покупателю и им не оприходован – часто возникает несоответствие между таблицами. Как это исправить? Как найти, в чем именно не совпадают таблицы? Об этом и пойдет речь в нашей статье.

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

Нет, им говорят правду. Но не всю. Если обратить внимание, то практически на всех примерах фигурирует сравнение ТОЛЬКО ОДНОГО СТОЛБЦА. Много вы видели таблиц только с одним столбцом?

 На самом деле, применение таких формул может привести к неверному результату. Насколько это опасно, объяснять, думаю, не надо. Посмотрите на следующий пример. Перед вами начисления за март и январь. Надо определить, одинаковы они или нет. Я специально привел на скриншотах формулы

Казалось бы, все в порядке. Действительно, если бы ВПР не обнаружила во второй таблице сотрудника, появилась бы ошибка #НД. То же самое и суммами начислений. Если сравнить общую сумму начислений, она тоже совпадет. Так что же, таблицы одинаковы? НЕТ!

Сравните хотя бы начисления за январь и март у Бурко или Мусаевой. Разве они одинаковы? Явно нет.

Дело в том, что данные отдельных столбцов в таблице могут и совпасть, а вот строчки нет. Более того, если, например, в таблице несколько раз в столбце встретилось одно и то же название, то ВПР найдет только первое вхождение. Как быть? На самом деле всё не так сложно. Надо в отдельном столбце вначале соединить построчно позиции ключевых столбцов. Для этого либо используем знак амперсанда «&» или стандартную функцию сцепить.

И уже значение этого столбца можно сравнивать между собой. К примеру, можно применить функцию счет если. Она подсчитает вхождения значения одного ключевого столбца в другом. Напомним ее синтаксис.

=СЦЕПИТЬ (где ищем ячейки с нужным значением; само значение для поиска)

Результат видно на скриншоте. Обратите внимание, что те строки где информация в целом различается наша функция вывела ноль.

Если же нельзя по каким-либо причинам добавлять ключевой столбец, то можно применить немного, скажем так, сложноватую формулу следующего вида

=ВПР(СЦЕПИТЬ(A3;B3);СЦЕПИТЬ('ЗП Март'!$A$3:$A$22;'ЗП Март'!$B$3:$B$22);1;0).

Поясним, что здесь написано

СЦЕПИТЬ(A3;B3); тут мы соединяем ячейки проверяемой стоки текущей таблицы

СЦЕПИТЬ('ЗП Март'!$A$3:$A$22;'ЗП Март'!$B$3:$B$22); это мы соединяем проверяемые столбцы второй таблицы ЦЕЛИКОМ. Обратите так же внимание на то, что в этой части формулы все адреса закреплены!

1;0). – в результате сцепления все столбцы второй таблицы будут помещены в один виртуальный столбец, вот почему появилась единица, ну а знак «0» говорит о том, что нас интересует абсолютное совпадение. Например, в реальной таблице могут совпасть дата и предмет договора, сумма договора, а вот количество единиц различаться.

 Или вроде бы все совпадает, но не тот контрагент. Естественно, что все это надо учитывать

Данная составная – или, по-другому, вложенная -  функция является формулой массива. Поэтому ВВОДИТЬ ЕЕ НАДО СОЧЕТАНИЕМ КЛАВИШ CTRL SHIFT ENTER.  Если все сделано все правильно, то с обеих сторон введенной формулы появятся фигурные скобки. Нажатие же только клавиши ENTER приведет к ошибке. Набирать формулу надо напрямую с клавиатуры, начиная с первых ячеек проверяемых таблиц

Ниже показан пример сравнения более сложный таблиц с листов реализация и реализация 1. Видно, что и тут несовпадающие строки отмечены кодом #НД

Если же вы хотите отметить несовпадающие строки цветом, то мы рекомендуем применить ключевой столбец, в котором необходимо сцепить построчно ячейки знА^'чимых столбцов. Результаты можно поместить на отдельном листе. Предварительно надо избавиться от объединённых ячеек. Это позволит избежать дополнительных не столь очевидных ошибок.

Вот теперь можно применить условное форматирование.  К примеру, чтобы увидеть строки из таблицы на листе «лакомка_1», которые отсутствуют на листе «Лакоимка2», надо выделить ячейки со значениями таблицы «Лакомка 1», не затрагивая заголовки, и на вкладке «Главная» открыть «Условное форматирование», затем выбрать «Управление правилами». Нажать «создать правило», выбрать тип «использовать формулу для определения форматируемых ячеек и ввести следующую формулу

=СЧЁТЕСЛИ('КЛЮЧИ СРАВНЕНИЯ'!$B$2:$B$219;'КЛЮЧИ СРАВНЕНИЯ'!$A2)=0

Обратите еще раз снимание на расположение знаком доллара в формуле. Если диапазон столбца «В» в листе «КЛЮЧИ СРАВНЕНИЯ» закреплен полностью и не будет меняться во время проверки, то ячейка столбца «А» в этом листе, которая проверяется по столбцу «В,  будет во время проверки перемещаться построчно вниз, оставаясь в столбце «А».

Теперь нажмите кнопу «формат», выберите желаемый цвет (я выбрал красный) и сохраните созданное условие нажатием кнопок ОК.  Вы увидите наглядный результат. Заметьте так же, что я не только изменил заливку у несовпадающих ячеек , но и изменил шрифт.

Как видно, строки из таблицы «Лакомка_1», отсутствующие в таблице «Лакомка_2», выделены красным цветом. Аналогично поступаем и в таблице «Лакомка_2», но уже с помощью функции СЦЕПИТЬ проверяем наличие количества ячеек из столбца «В» в контрольном столбце «А». Те же, кто любят показать свою крутость, могут попробовать применить комбинацию функций «НЕ», «ПОИСКПОЗ» и «ЕЧИСЛО». Я намеренно не буду приводить нужную формулу здесь, полагая что те кто знают EXCEL по настоящему, справятся сами, ну а для середнячков лишние сложности ни к чему.

На этом я прощаюсь с Вами, желаю удачи и успехов в работе. Если будут еще вопросы или имеются свои проблемы, которые надо решить, то пишите смело, а мы постараемся максимально быстро на них ответить.

До встречи на занятиях в Учебном Центре «Зерде»!

С уважением, Сергей