После установки надстройки у вас появится новая вкладка с командой вызова функции. При нажатии на команду Сравнение диапазонов появляется диалоговое окно для ввода параметров.

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

Диалоговое окно поделено на две части: левая для первой таблицы и правая для второй.

Чтобы сравнить таблицы необходимо выполнить следующие действия:

• Указать диапазоны таблиц.
• Установить чекбокс (галку/птичку) под выбранным диапазоном таблиц в том случае если таблица включает в себя шапку (строку заголовка).
• Выбрать столбцы левой и правой таблицы, по которым будет вестись сравнение (в случае, если диапазоны таблиц не включают заголовки столбцы будут пронумерованы).
• Указать тип сравнения.
• Выбрать вариант выдачи результатов.

Тип сравнения таблиц

Программа позволяет выбрать несколько типов сравнения таблиц:

Найти строки одной таблицы, которые отсутствуют в другой таблице

При выборе данного типа сравнения программа ищет строки одной таблицы, которые отсутствуют в другой. Если вы сопоставляете таблицы по нескольким столбцам, то результатом работы будут строки, в которых имеется различие хотя бы по одному из столбцов.

Найти совпадающие строки

При выборе данного типа сравнения, программа находит строки, которые совпадают в первой и второй таблицах. Совпадающими считаются строки, в которых значения в выбранных столбцах сравнения (1, 2, 3) одной таблицы полностью совпадают со значениями столбцов второй таблицы.

Пример работы программы в данном режиме представлен справа на картинке.

Сопоставить таблицы на основе выбранной

В данном режиме сравнения, напротив каждой строки первой таблицы (выбранной в качестве основной), копируются данные совпадающей строки второй таблицы. В случае если совпадающие строки отсутствуют, строка напротив основной таблицы остается пустой.

Сравнение таблиц по четырем и более столбцам

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

• Создайте в таблицах по пустому столбцу.
• В новых столбцах используя формулу =СЦЕПИТЬ объедините столбцы, ко которым хотите провести сравнение.

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

Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.

Как сравнить два столбца в Excel по строкам

Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции . Рассмотрим как это работает на примерах ниже.

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:

=ЕСЛИ(A2=B2; “Совпадают”; “”)

Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:

=ЕСЛИ(A2<>B2; “Не совпадают”; “”)

Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)

Пример результата вычислений может выглядеть так:

Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

Как сравнить несколько столбцов на совпадения в одной строке Excel

В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:

• Найти строки с одинаковыми значениями во всех столбцах таблицы;
• Найти строки с одинаковыми значениями в любых двух столбцах таблицы;

Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы

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

=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)

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

=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

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

=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Как сравнить два столбца в Excel на совпадения и выделить цветом

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

• Выделить столбцы с данными, в которых нужно вычислить совпадения;
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.

Рассмотрим как найти совпадающие строки в таблице:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

• Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
• На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
• Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
• После этого в выделенной колонке будут подсвечены дублирующиеся строки:

Иногда возникает необходимость сравнить два файла MS Excel. Это может быть нахождение расхождения цен на определенные позиции или изменение каких-либо показаний, не суть важно, главное, что необходимо найти определенные расхождения.

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

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

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

Первый способ решения поставленной задачи. Решение только силами формул MS Excel.

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

Для сравнения показателей бега на 100 метров формула выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА)<>D2;D2-ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА);"Разницы нет")
В случае, если разницы нет, выводится сообщение, что разницы нет, если она присутствует, тогда от значения в конце сезона отнимается показатель начала сезона.

Формула для бега на 3000 метров выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;4;ИСТИНА)<>E2;"Разница есть";"Разницы нет")
Если конечное и начальное значения не равны выводится соответствующее сообщение. Формула для подтягиваний может быть аналогична любой из предыдущих, дополнительно приводить ее смысла нет. Конечный файл с найденными расхождениями приведен ниже.

Небольшое пояснение. Для удобства читаемости формул данные из двух файлов были перемещены в один (на разные листы), но этого можно было не делать.

Видео сравнения двух файлов MS Excel с помощью функций и .

Второй способ решения задачи. Решение с помощью MS Access.

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

Последний представляет собой связь файлов Excel и Access, поэтому при изменении данных в файлах Excel, расхождения будут находится автоматически при выполнении запроса в MS Access.

Следующим шагом после произведения импорта будет создание связей между таблицами. В качестве связующего поля выбираем уникальное поле «№ п/п».
Третьим шагом будет создание простого запроса на выборку с помощью конструктора запросов.

В первом столбце указываем, какие записи необходимо вывести, а во втором – при каких условиях записи будут выведены. Естественно, для второго и третьего поля действия будут аналогичными.

Видео сравнения файлов MS в Excel, с помощью MS Access.

В результате проделанных манипуляций выведены все записи, с разными данными в поле: «Бег на 100 метров». Файл MS Access представлен ниже (к сожалению, внедрить, как файл Excel, SkyDrive не позволяет)

Вот такие два способа существуют для нахождение расхождений в таблицах MS Excel. У каждого есть как преимущества, так и недостатки. Понятно, что это не исчерпывающий список сравнения двух файлов Excel. Ваши предложения ждем в комментариях.

Статья даёт ответы на следующие вопросы:

• Как сравнить две таблицы в Excel?
• Как сравнивать сложные таблицы в Excel?
• Как производить сравнение таблиц в Excel с использованием функции ВПР()?
• Как формировать уникальные идентификаторы строк, если их уникальность изначально определяется набором значений в нескольких столбцах?
• Как фиксировать значения ячеек в формулах при копировании формул?

При работе с большими объемами информации пользователь может столкнуться с такой задачей, как сравнение двух табличных источников данных. При хранении данных в единой системе учета (например, системы на базе 1С Предприятие, системы, использующие SQL базы данных), для сравнения данных могут использоваться встроенные в систему или СУБД возможности. Как правило, для этого достаточно привлечь программиста, который напишет запрос к базе данных, или программный механизм отчета. С запросом может справиться и опытный пользователь, владеющий навыком написания запросов 1C, либо SQL.

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

Для решения обозначенных проблем идеальным приемом является использование для сравнения данных табличного редактора Microsoft Excel. Большинство распространенных систем управленческого и регламентированного учета поддерживают выгрузку в формат Excel. Эта задача потребует всего лишь определенной квалификации пользователя по работе с этим офисным пакетом и не потребует навыков программирования.

Рассмотрим решение задачи сравнения таблиц в Excel на примере. Мы имеем две таблицы, содержащие списки квартир. Источники выгрузки - 1С Предприятие (учёт строительства) и таблица в Excel (учёт продаж). Таблицы размещены в рабочей книге Excel на первом и втором листах соответственно.

Перед нами стоит задача сравнить эти списки по адресу. В первой таблице - все квартиры дома. Во второй таблице - только проданные квартиры и имя покупателя. Конечная цель - отобразить в первой таблице по каждой квартире имя покупателя (для тех квартир, которые были проданы). Задача осложняется тем, что адрес квартиры в каждой таблице является строительным и состоит из нескольких полей: 1) адрес корпуса (дома), 2) секция (подъезд), 3) этаж, 4) номер на этаже (например, от 1 до 4).

Для сравнения двух таблиц Excel нам нужно добиться того, чтобы в обеих таблицах каждая строка идентифицировалась бы одним полем, а не четырьмя. Получить такое поле можно объединив значения четырех полей адреса функцией Сцепить(). Назначение функции Сцепить() - объединение нескольких текстовых значений в одну строку. Значения в функции перечисляются через символ ";". В качестве значений могут выступать как адреса ячеек, так и произвольный текст, заданный в кавычках.

Шаг 1. Вставим в начале первой таблицы пустую колонку "A" и пропишем в ячейке этой колонки напротив первой строки с данными формулу:
=СЦЕПИТЬ(B3;"-";C3;"-";D3;"-";E3)
Для удобства визуального восприятия между значениями объединяемых ячеек мы установили символы "-".

Шаг 2. Скопируем формулу в нижеследующие ячейки колонки А.

Шаг 4. Для сравнения таблиц Excel по значениям следует воспользоваться функцией ВПР(). Назначение функции ВПР() - поиск значения в крайнем левом столбце таблицы и возвращение значения ячейки, находящейся в указанном столбце той же строки. Первый параметр - искомое значение. Второй параметр - таблица, в которой будет осуществляться поиск значения. Третий параметр - номер столбца, из ячейки которого в найденной строке будет возвращено значение. Четвертый параметр - тип поиска: ложь - точное совпадение, истина - приближенное совпадение. Поскольку выходная информация должна быть размещена в первой таблице (именно в нее требовалось добавить имена покупателей), то формулу будем прописывать в ней. Сформируем в свободной колонке справа от таблицы напротив первой строки данных формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;ЛОЖЬ)
При копировании формул "умный" Excel автоматически изменяет адресацию ячеек. В нашем случае искомое значение для каждой строки будет меняться: A3,A4 и т.д., а адрес таблицы, в которой ведется поиск, должен оставаться неизменным. Для этого зафиксируем ячейки в параметре адреса таблицы символами "$". Вместо "Лист2!A3:F10" делаем "Лист2!$A$3:$F$10".