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

Сравнение двух таблиц

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:
С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок. ), что-то пропало (ежевика, малина. ), у каких-то товаров изменилась цена (инжир, дыня. ). Нужно быстро найти и вывести все эти изменения. Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

• функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
• объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
• использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

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

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

Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.

Плюсы этого способа: просто и понятно, «классика жанра», что называется. Работает в любой версии Excel.

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

Способ 2. Сравнение таблиц с помощью сводной

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

Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table) . Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals) .

Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши — Обновить (Referesh).

Плюсы : такой подход на порядок быстрее работает с большими таблицами, чем ВПР.

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

Способ 3. Сравнение таблиц с помощью Power Query

Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить — получите новую вкладку Power Query.

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl + T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table) . Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load To. ) :

. и в появившемся затем окне выбрем Только создать подключение (Connection Only) .

Повторите то же самое с новым прайс-листом.

Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить (Data — Get Data — Merge Queries — Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer) :

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

В итоге получим слияние данных из обеих таблиц:

Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column) . А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home) :

Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl + Alt + F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data) .

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

Минусы : Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.

Ссылки по теме

• Как собрать данные из всех файлов Excel в заданной папке с помощью Power Query
• Как найти совпадения между двумя списками в Excel
• Слияние двух списков без дубликатов

9 способов сравнить две таблицы в Excel и найти разницу

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

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

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

Просмотр рядом, чтобы сравнить таблицы.

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

Сравните 2 книги.

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

Чтобы просмотреть два файла Excel рядом, сделайте следующее:

1. Откройте оба файла.
2. Перейдите на вкладку «Просмотр» и нажмите кнопку «Далее». (1) Вот и все!

По умолчанию два отдельных окна Excel отображаются по горизонтали.

Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):

В результате будут размещены два отдельных окна, как на скриншоте.

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

Расположите рядом несколько таблиц Excel.

Чтобы просмотреть более двух файлов одновременно, откройте все книги, которые вы хотите сравнить, и нажмите кнопку «Далее».

Рядом появляется диалоговое окно «Сравнить», в котором вы можете выбрать файлы для просмотра с активной книгой.

Чтобы просмотреть все открытые файлы сразу, нажмите кнопку «Упорядочить все» и выберите предпочтительный макет: бок о бок, горизонтальный, вертикальный или водопад.

Для небольших таблиц вы можете легко сравнить их данные визуально. Хотя, конечно, здесь есть риск ошибки из-за человеческого фактора.

Сравните два листа в одной книге.

Иногда 2 листа, которые вы хотите сравнить, находятся в одной книге. Чтобы просмотреть их рядом, выполните следующие действия.

1. Откройте файл, перейдите на вкладку «Просмотр» и нажмите кнопку «Новое окно».

1. Это действие откроет тот же файл в дополнительном окне.
2. Активируйте параллельный просмотр, нажав соответствующую кнопку на ленте.
3. Выберите лист 1 в первом окне и лист 2 во втором окне.

Быстрое выделение значений, которые различаются.

Это тоже не очень громоздкий способ. Если вам просто нужно найти и проверить наличие или отсутствие различий между записями, вам нужно нажать кнопку «Найти и выбрать» на вкладке «Главная» после выбора диапазона, в котором вы хотите сравнить данные в Excel.

В открывшемся меню выберите пункт «Выбрать группу ячеек…» и в появившемся диалоговом окне выберите «различия линий».

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

Формула сравнения.

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

Самый простой вариант — сравнить две таблицы на одном листе. Вы можете связать числовые и текстовые значения, просто написав формулу их равенства в одной из соседних ячеек. Следовательно, если ячейки идентичны, мы получим сообщение ИСТИНА, иначе — ЛОЖЬ.

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

Результатом будет ИСТИНА (в случае совпадения) или ЛОЖЬ (в случае отрицательного результата).

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

Если ваши таблицы достаточно велики, будет довольно утомительно сканировать столбец I на наличие слова FALSE. Поэтому может быть полезно сразу определить: есть ли неточности?

Вы можете посчитать общее количество расхождений и сразу посмотреть это число где-нибудь отдельно.

или вы можете сделать это с помощью формулы массива

Если формула возвращает ноль, данные точно такие же. Что ж, если результат положительный, нужно более детальное исследование. Об этом и поговорим дальше.

Как произвести сравнение на отдельном листе.

Чтобы сравнить два листа Excel на предмет различий, просто откройте новый чистый лист, введите следующую формулу в ячейку A1, затем скопируйте ее вниз и вправо, перетащив маркер заполнения:

Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от положения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 на листе Sheet1 и Sheet2, формула в B1 будет сравнивать ячейку B1 на обоих листах и ​​так далее. Результат будет таким:

В результате вы получите отчет о разнице на новом листе. Думаю, это достаточно информативно.

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

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

Как сравнить две таблицы при помощи формулы ВПР.

Допустим, у нас снова есть 2 прайс-листа. Однако, в отличие от предыдущего примера, они содержат другое количество продуктов, а сами продукты не расположены в каком-либо определенном порядке. Поэтому описанный выше метод, когда мы сравнивали две таблицы построчно, здесь работать не будет.

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

Для наглядности разместим обе таблицы на одном листе.

= ЕСЛИ ОШИБКА (ВПР (F3; $ B $ 3: $ C $ 18,2,0); 0)

берет название продукта из второго списка, ищет его в первом и, если да, извлекает соответствующую цену из первой таблицы. Она будет написана рядом с новой ценой в столбце H. Если поиск завершился неудачно, то есть такого элемента раньше не было, установите 0. Таким образом, старая и новая цена находятся рядом, и их можно легко сравнить с помощью простейшей операции вычитания. Именно это и было сделано в столбце I.

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

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

Разберем действия по шагам. Формула в ячейке J3 ищет название продукта из первой позиции второй таблицы внутри первой. Если он найден, старая цена, соответствующая этому продукту, извлекается и сразу сравнивается с новой. Если они совпадают, в ячейке пишется пробел «».

= ЕСЛИ (ЕСЛИ ОШИБКА (ВПР (F3; $ B $ 3: $ C $ 18,2,0); 0) = G3; «»; ЕСЛИ ОШИБКА (ВПР (F3; $ B $ 3: $ C $ 18; 2, 0); 0))

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

Кроме того, если ячейка J3 не пуста, I3 укажет название продукта —

а в К3 — его новая цена:

Что ж, позже в L3 мы просто найдем разницу K3-J3.

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

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

В ячейке B2 запишите формулу

= ЕСЛИ (UND (ВПР (A2; Price1! $ B $ 3: $ B $ 19; 1,0)), «Нет», VLOOKUP (A2, Price1! $ B $ 3: $ C $ 19; 2; 0))

Затем мы узнаем, какие цены из второй таблицы находятся в первой.

Для каждой цены первого прайс-листа мы проверяем, соответствует ли она новым данным —

= ЕСЛИ (UND (ВПР (A2; Цена2! $ B $ 3: $ B $ 22; 1; 0)); «Нет»; ВПР (A2; Цена2! $ B $ 3: $ C $ 22; 2; 0))

Список тестов находится в столбце A. В результате мы получили своего рода сводную таблицу цен — старых и новых.

Вы можете найти больше примеров использования функции ВПР для сравнения таблиц в этой статье.

Выделение различий между таблицами цветом.

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

• На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней использованной ячейки.
• На вкладке Главная щелкните Условное форматирование> Новое правило и создайте его с помощью следующей формулы:

Где Sheet2 — это имя другого листа, который вы сравниваете с текущим.

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

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

Сравнение при помощи сводной таблицы.

Хороший вариант сравнения — объединить таблицы в одну сводную таблицу, а затем сравнить данные друг с другом.

Вернемся к нашему примеру с двумя прайс-листами. Объединяем наши данные на одном листе. Чтобы отличать данные одной таблицы от другой, мы добавляем вспомогательный столбец D и указываем в нем, откуда именно были взяты данные:

Теперь приступим к созданию сводной таблицы. Не буду останавливаться на том, как мы это сделаем. Все шаги подробно описаны в статье Как создать сводную таблицу в Excel.

Поместите поле «Продукт» в область строки, поле «Цена» в область столбца и поле «Цена» в область значений.

Как вы можете видеть на скриншоте ниже, цена указана для каждого продукта, представленного хотя бы в одном из прайс-листов.

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

Общие итоги здесь не имеют смысла, и вы можете отключить их на вкладке Дизайн — Общие итоги — Выкл. Для строк и столбцов.

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

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

Минусы: вам нужно вручную скопировать данные в большую таблицу и добавить столбец с именем исходного файла.

Сравнение таблиц с помощью Power Query

Power Query — это бесплатная надстройка для Microsoft Excel, которая позволяет загружать данные практически из любого источника, а затем преобразовывать их так, как вы хотите. В Excel 2016 эта надстройка уже интегрирована во вкладку «Данные» по умолчанию, а для предыдущих версий ее необходимо загружать и устанавливать отдельно от Microsoft.

Перед загрузкой наших прайс-листов в Power Query их необходимо сначала преобразовать в интеллектуальные таблицы. Для этого выберите диапазон с данными и нажмите Ctrl + T на клавиатуре или выберите вкладку «Главная» — «Форматировать как таблицу» на ленте. Имена создаваемых таблиц можно изменить во вкладке «Дизайн» (я оставлю стандартные таблицы Table1 и Table2, которые создаются по умолчанию).

Загрузите первый прайс-лист в Power Query с помощью кнопки «Из таблицы / диапазона» на вкладке «Данные.

После загрузки возвращаемся в Excel из Power Query с помощью команды Close and Load — Close and Load In…

В появившемся окне выберите «Только создание подключения».

Повторите те же шаги с новым прайс-листом.

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

В окне слияния выбираем наши таблицы в выпадающих списках, выбираем столбцы с названиями товаров в них и внизу определяем режим слияния — Full External.

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

После нажатия вы увидите список столбцов из второго прайс-листа. Выбираем Товар и Цену. Получаем такую ​​картинку:

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

А теперь сравним цены. Перейдите на вкладку «Добавить столбец» и нажмите кнопку «Условный столбец». А затем в открывшемся окне введите разные условия тестирования с соответствующими значениями, которые должны отображаться:

Теперь осталось вернуться на вкладку «Главная» и нажать «Закрыть и загрузить.

Получаем новый лист в нашей рабочей тетради:

Примечание. Если в будущем в наших прайс-листах произойдут изменения (добавляются или удаляются строки, меняются цены и т.д.), То достаточно будет обновить наши запросы с помощью сочетания клавиш Ctrl + Alt + F5 или кнопки Обновить все на вкладке Данные.

В конце концов, все данные берутся из «умных» таблиц Excel, которые автоматически изменяют свой размер по мере добавления или удаления информации из них. Однако помните, что имена столбцов в исходных таблицах не должны изменяться, иначе вы получите ошибку «Такой-то столбец не найден!» при попытке обновить запрос.

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

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

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

Эти проблемы решаются с помощью надстроек для Excel, о которых мы поговорим позже.

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

Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, наиболее интересным из которых является «Сравнение таблиц», инструмент для сравнения листов или диапазонов данных в Excel.

Чтобы сравнение было нагляднее и удобнее, надстройка оформлена следующим образом:

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

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

1. Нажмите кнопку «Сравнить два листа» на вкладке «Биты данных» в группе «Объединить »:

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

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

1. На следующем шаге выберите алгоритм сравнения:
   • Без ключевых столбцов (по умолчанию) — идеально подходит для сложных документов, таких как счета-фактуры или контракты.
   • По ключевым столбцам — подходит для таблиц, организованных по столбцам, имеющим один или несколько уникальных идентификаторов, таких как номера заказов или артикулы.
   • По ячейкам — идеально подходит для сравнения таблиц с одинаковым макетом и измерениями, таких как балансы или статистические отчеты.

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

На этом же этапе вы можете выбрать предпочтительный тип соответствия:

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

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

На следующем шаге укажите, какие различия следует выделить, а какие игнорировать, а также как отмечать различия.

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

Нажмите кнопку «Сравнить» и подождите, пока программа обработает данные и создаст резервные копии. Резервные копии всегда создаются автоматически, поэтому вам не нужно беспокоиться о сохранности ваших данных.

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

На скриншоте выше различия выделены цветами по умолчанию:

• Красные линии — это линии, которые существуют только на листе 2 (справа).
• Зеленые ячейки — это разные ячейки в перекрывающихся рядах.

Но если мы выберем второй алгоритм сравнения — из ключевого столбца, нас попросят его указать. В нашем случае ключевой колонкой вполне можно обозначить «Товар».

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

Как видите, главным здесь является совпадение значений в столбцах B. Линии, у которых нет такого совпадения, сразу выделяются красным или фиолетовым цветом. Но если есть совпадение, перейдите в столбец C и сравните записанную там цену. Зеленые ячейки показывают нам только те товары, которые есть в обоих прайс-листах, но цена на них изменилась.

Не знаю, как вы, но мне второй вариант кажется более информативным.

Что тут делать с этим сравнением?

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

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

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

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

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

Вот как вы можете сравнить два листа в Excel с помощью инструмента Сравнить два листа (надеюсь, вам понравилось

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-го столбца);

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

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

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

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

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

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

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

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

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

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

Первая публикация: 25.11.2017

Сравнить файлы или таблицы Excel и найти разницу онлайн

Хотите сравнить файлы или таблицы Excel онлайн? На сайте xlCompare.com Вы можете сравнить два файла(таблицы) Excel, выделить отличия цветом, и сохранить их в третьем файле. xlCompare это лучшее средство для сравнения и объединения таблиц Microsoft Excel. xlCompare включает в себя онлайн сервис для сравнения таблиц Excel и настольное приложение для сравнения файлов Excel. Вам больше не нужно учить функционал Excel. Просто откройте две таблицы и получите различия между ними на Вашем экране.

Choose your file
Choose your file
Compare Tables
Please, help us to improve our application
Rate quality of our service

Сравнить две таблицы Excel онлайн и выделить отличия цветом

Сайт xlCompare.com это лучшее средство для поиска отличий в таблицах Excel. На нашем сайте Вы сможете открыть и сравнить 2 таблицы Excel, выделить отличия, перенести общие или добавленные строки в другой файл. Если Вам нужно быстро увидеть отличия в двух таблицах Excel, выбор сайта xlCompare — это правильное решение.

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

Что может этот онлайн сервис сравнения файлов Excel?
Онлайн сервис поиска отличий в файлах Excel это простое средство сравнения которое позволит Вам:

1. Визуально увидеть отличия между двумя таблицами Excel.
2. Фильтровать новые, измененные, и общие строки между двумя таблицами Excel.
3. Экспортировать видимые строки в приложение Excel используя кнопку Копировать.

Кнопка Копировать экспортирует видимые строки в Буфер Обмена в формате HTML, который можно вставить в файл Excel, документ Word или в почтовое сообщение.

Как сравнить два файла Excel онлайн шаг за шагом

Онлайн сервис повторяет часть функций приложения xlCompare на веб странице. Чтобы сравнить 2 файла Excel сделайте следующее:

1. Откройте Ваши файлы в панелях ФАЙЛ1 и ФАЙЛ2.
2. Выберите таблицы для сравнения, если файлы содержат несколько таблиц.
3. Укажите ключевые столбцы и заголовок, если Ваш файл имеет табличную структуру.
4. Кликните на кнопке Сравнить Таблицы и получите отчет о сравнении файлов Excel на этой странице.

Результаты сравнения появятся в отдельной панели ниже сравниваемых таблиц.
Что делает кнопка ВПР?

ВПР это функция сравнения выбранных столбцов в двух таблицах Excel онлайн, которая подобна использованию функции ВПР в Excel.

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

1. Без Ключей
2. A
3. A, B
4. A, B, C
5. A, B, C, D

Для выбора Ключевых Столбцов служит выпадающий список, расположенный над таблицей.

Если Вы хотите установить ключом только столбец C и оставить столбцы A и B как данные — Вам нужно установить и использовать наше приложение для сравнения файлов xlCompare.

Приложение xlCompare позволяет задавать любую комбинацию Ключевых Столбцов.
Как выбрать строки заголовка в таблице Excel?
xlCompare позволяет выбирать строки заголовка таблицы среди первых 5 строк.

Чтобы выбрать Строки Заголовка используйте выпадающий список расположенный выше таблицы после списка выбора Ключевых Столбцов.

Если Ваша таблица Excel содержит более широкий заголовок, для которого не достаточно 5 строк Вам следует установить приложение xlCompare.

Приложение xlCompare позволяет выбрать любое количество строк заголовка.
Как найти общие строки в двух таблицах Excel?

Онлайн сервис xlCompare позволяет находить общие строки между двумя таблицами Excel. Если у Вас есть 2 списка данных в которых Вы хотите найти общие записи — вы получите их всего за несколько секунд.

Пошаговая инструкция получения общих записей в двух таблицах Excel:

1. Откройте Ваши файлы Excel на этой странице в панелях ФАЙЛ1 и ФАЙЛ2
2. Выберите ключевые столбцы и строки заголовка, если таковые присутствуют в Ваших таблицах.
3. Кликните на кнопке Сравнить Таблицы для получения отчета о сравнении двух выбранных таблиц Excel
4. Примените фильтр Общие строки в списке выбора фильтров.
5. Чтобы скопировать видимые на экране строки в приложение Excel или Numbers используйте кнопку Копировать в окне отчета.

Как найти одинаковые данные в двух таблицах Excel?
Сайт xlCompare позволяет найди одинаковые строки на двух таблицах Excel.
Используйте данную инструкцию для поиска совпадающих строк в файлах Excel:

1. Выберите Ваши файлы Excel на этой странице
2. Если Ваша таблица имеет заголовок — укажите ключевые столбцы и строки заголовка
3. Сравните 2 выбранных таблицы Excel с помощью кнопки Сравнить Таблицы
4. Примените фильтр совпадающие строки в списке выбора фильтра
5. В панели результатов будут отображены только строки, ячейки которых совпадают в обеих таблицах
6. Если Вы хотите сохранить эти данные на диске — кликните на кнопке Копировать в окне отчета. Она скопирует ячейки в Буфер Обмена и Вы сможете вставить их в Excel

Как выделить цветом отличия в 2-х файлах Excel?

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

Сделайте следующее:

1. Выберите файлы которые Вы хотели бы сравнить на этой стринице
2. Если Ваша таблица имеет заголовок — укажите ключевые столбцы и строки заголовка
3. Сравните 2 выбранных таблицы Excel с помощью кнопки Сравнить Таблицы
4. Сайт xlCompare выделит все измененные ячейки оранжевым цветом
5. Для экспорта этих данных включая форматирование кликните на кнопке Копировать которая находится над отчетом
6. Теперь Вы можете вставить эту таблицу из Буфера Обмена в письмо, сохранив форматирование

Как сравнить две таблицы на разных листах в Excel?
Воспользуйтесь пошаговой инструкцией:

1. Выберите один и тот же файл в панели ФАЙЛ1 и панели ФАЙЛ2
2. Выберите разные тиблицы используя выпадающий список под таблицей
3. Сравните листы с помощью кнопки Сравнить Таблицы

ВАЖНО: Не забудьте выбрать ключи и строки заголовка.
Есть ли альтернативные методы сравнения файлов Excel?
Да конечно. Есть еще несколько методов сравнения таблиц Excel и все они хорошо описаны в Интернете:

1. Условное Форматирование
2. Функции EQUAL, ВПР, XLOOKUP
3. VBA макросы

Условное форматирование и функции Excel отлично работают если таблицы имеют одинаковую структуру. Если порядок строк хотя бы в одном месте не совпал — метод работать не будет.

Для написания VBA макросов нужно быть знакомым с программированием на VBA.

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

Вывод — отложите эти методы. Сайт xlCompare.com дает возможность легко и бесплатно получить тот же результат. Просто перетащите Ваши файлы на эту станицу и получите результат.

Если Вы хотели бы получить результат в другом виде — мы будем рады услышать и внедрить Ваши пожелания.

Общие Вопросы

Что такое Ключевой Столбец (Первичный Ключ)?
Первичный Ключ это такой столбец в таблице, который содержит уникальные значения в каждой строке.

В теории реляционных баз данных Ключ должен содержать уникальные значения, но на практике это встречается не всегда, поэтому xlCompare допускает дублирующиеся значения в ключевых столбцах.

Примеры ключевых столбцов — Номер Плательщика Налогов, номер телефона, идентификатор транспортного средства (VIN), .

Что такое Строка Заголовка?
Строка Заголовка в таблице как правило это верхняя строка, содержащая названия столбцов в таблице.
Все вместе строки заголовка определяют структуру таблицы.
В большинстве своем таблицы баз данных экспортируются вместе с названиями полей — заголовком.

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

Если Ваша таблица содержит заголовок, его обязательно нужно указать перед сравнением.
Этот сервис бесплатен?

Онлайн сервис по сравнению файлов Excel на сайте xlCompare бесплатен. Вы ничего не платите за пользование им.

Почему тогда на сайте бесплатного сервиса есть страница КУПИТЬ?

Приложение xlCompare которое Вы можете установить на свой компьютер не бесплатно. Приложение распространяется по бессрочной лицензии с единоразовой оплатой. Цена и прочая информация для покупателей находится на странице КУПИТЬ.

Загружаете ли Вы мои файлы на свой сайт?

Нет. Вам не о чем беспокоиться.
Мы уважаем Вашу приватность.

Все операции (сравнение, фильтрация, и т.д.) выполняются в Вашем броузере на Вашем компьютере. Spreadsheet Tools не имеет доступа к Вашим файлам. Этот онлайн сервис абсолютно безопасен.

Вы можете выключить Интернет соединение и продолжать пользоваться нашим онлайн сервисом по сравнению файлов.

Могут ли третьи лица получить доступ к моим данным?

Нет. Это не возможно.

Этот онлайн сервис не передает Ваши файлы и другие данные в сеть. Все манипуляции с данными проводятся на Вашем компьютере.

Ваши файлы не выходят за пределы Вашего компьютера.
Какие форматы файлов Вы поддерживаете?

Наш онлайн сервис работает с XLSX, XLSM и CSV файлами. Вы можете открыть любой из этих файлов и получить детальный отчет о сравнении.

Как сравнивать файлы в других форматах?

Наш сервис позволяет сравнивать таблицы в любом формате. Используйте пошаговую инструкцию написанную ниже:

1. Откройте файл в приложении для его редактирования (Excel, Numbers, и т.д.) и скопируйте таблицу в Буфер Обмена
2. Вставьте ячейки на эту страницу с помощью комады Вставить
3. Сравните вставленные ячейки и получите отчет о различиях.
4. Примените фильтр к ячейкам (общие, измененные, уникальные) и скопируйте его в Буфер Обмена
5. Вставьте ячейки обратно в приложение Excel (Numbers, . ) с этой страницы.

Могу ли я сравнить формулы в моих файлах Excel?

Онлайн сервис сравнивает только значения ячеек. Формулы и форматирование не сравниваются.

Чтобы сравнивать формулы в таблицах Excel — установите приложение xlCompare на Ваш компьютер со страницы: СКАЧАТЬ.

Могу ли я открыть и сравнить два больших файла Excel?

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

У меня есть вопросы. Как связаться с Вами?
Используйте форму на странице СВЯЗАТЬСЯ С НАМИ для отправки сообщений нам.
Также мы будем рады прочитать Ваш отзыв в комментариях внизу этой страницы.

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

ВПР: Как это работает?
Как сравнить столбцы в таблицах командой ВПР(VLOOKUP)?
Если кратко, то команда ВПР проверяет существование значения из ФАЙЛ2 в ФАЙЛ1.

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

Таблицы ФАЙЛ1 и ФАЙЛ2 могут иметь разную структуру.

1. Команда ВПР в xlCompare работает подобно функции ВПР в Excel.
2. ФАЙЛ1 интерпретируется как словарь.
3. ФАЙЛ2 считается файлом данных.
4. ВПР берет значение из выбранного столбца в ФАЙЛ2 и ищет его в выбранном столбце в ФАЙЛ1.
5. Строки со значениями которые не найдены выделяются красным цветом в панели результатов.
6. Все остальные ячейки не участвуют в сравнении и не выделяются цветом.
7. Команда ВПР сравнивает ТОЛЬКО два выбранных столбца!

Пример использования команды ВПР

У Вас есть список допустимых номеров SKU#, которые зарегистрированы в Вашей базе данных. Нужно проверить есть ли новые значения SKU# в полученной от клиентов таблице.

Команда ВПР идеальное решение для этого случая.
Как выбрать столбец для команды ВПР?
Кликните на заголовке столбца в таблице.
Область заголовка выбранного столбца будет отмечена синим цветом.
Можно ли выбрать больше одного столбца?
Нет. Команда ВПР допускает выбор только одного столбца.
После того как Вы выберете другой столбец — предыдущий выбор будет аннулирован.
Как отменить выбор столбца?
Повторный клик на заголовке отменяет выбор столбца.
Как в Excel найти все одинаковые значения в разных листах?
Как сравнивать столбцы двух таблиц Excel используя ВПР:

1. Выберите таблицы для сравнения на этой странице
2. Выберите столбцы для сравнения кликнув на их заголовок. Два столбца можно сравнить только если они выбраны!
3. Сравните две таблицы кликнув на кнопке ВПР
4. xlCompare отметит отсутствующие в словаре (первой таблице) записи красным цветом.
5. Выберите нужный Вам Фильтр чтобы показать только новые значения или только совпадающие.
6. Экспортируйте эту таблицу обратно в Excel через Буфер Обмена используя кнопку Копировать.

Как получить значения из ФАЙЛ1 которых нет в ФАЙЛ2?
ВПР дает Вам значения из ФАЙЛ2 которых которых нет в ФАЙЛ1.
Как получить список значений из словаря, которых нет в файле данных?
Поменяйте местами ФАЙЛ1 и ФАЙЛ2, выберите столбцы для сравнения и примените ВПР снова.

Все что нужно это поменять местами словарь и таблицу с данными. Это очень простой и быстрый метод получения результата.

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

Команда Копировать работает также как и при сравнении таблиц. Она копирует содержимое отчета в Буфер Обмена.

Как извлечь данные в другой Excel файл?

Скопировать отчет о сравнении в другой файл
Как экспортировать отчет о сравнении в XLSX файл и сохранить его на диске?

Часто увидеть отличия в двух таблицах Excel недостаточно, например как отправить отчет с несовпадающимим записями коллеге по e-mail?

Используйте кнопку Копировать на панели результатов.

Кнопка Копировать поместит таблицу из панели результатов в Буфер Обмена. После того как данные попадут в Буфер Обмена их можно вставить в:

1. Новый файл Excel
2. Документ Word
3. Письмо в формате HTML
4. Блокнот, и т.д.

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

Отчет о сравнении содержит все ячейки которые есть в обоих таблицах. Как отделить несовпадающие данные и перенести их в другой файл?

Используйте выпадающий список Фильтр для этого.
Фильтр включает в себя следующие режимы:

1. Без Фильтра (по умолчанию)
2. Добавленные Строки
3. Удаленные Строки
4. Совпадающие Строки
5. Измененные Строки
6. Общие Строки

В чем разница между Совпадающими, Измененными и Общими строками?
Совпадающие — полностью совпадающие строки в двух таблицах Excel. Все ячейки совпадают.

Измененные — строка содержит хотя бы одну измененную ячейку, которая имеет разные значения в ФАЙЛ1 и ФАЙЛ2.

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

1. Выберите Ваши файлы Excel в онлайн сервисе сравнения файлов
2. Укажите ключевые столбцы и строки заголовка если они есть в Вашей таблице
3. Нажмите кнопку Сравнить Таблицы и получите отчет о сравнении
4. Примените фильтр Общие Строки в выпадающем списке фильтра
5. Ваш броузер содержит строки, общие для двух таблиц.
6. Кликните на кнопке Копировать чтобы поместить таблицу с результатами в Буфер Обмена и вставьте ее в новую таблицу Excel, который можно сохранить на диске как XLSX\CSV файл.

Сравнить два списка и извлечь не совпадающие данные

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

Ниже приведена инструкция, которая укажет Вам как извлечь отличия между списками.

1. Выберите Ваши файлы Excel в онлайн сервисе сравнения файлов
2. Укажите ключевые столбцы и строки заголовка если они есть в Вашей таблице
3. Нажмите кнопку Сравнить Таблицы и получите отчет о сравнении
4. Примените фильтр Измененные Строки в выпадающем списке фильтра
5. В панели результатов появятся только строки которые были изменены
6. Кликните на кнопке Копировать чтобы поместить таблицу с результатами в Буфер Обмена и вставьте ее в новую таблицу Excel, который можно сохранить на диске как XLSX\CSV файл.

xlCompare — средство сравнения файлов Excel

Приложение xlCompare это лидер среди средств сравнения файлов Excel на рынке. xlCompare детально сравнит Ваши таблицы Excel и предоставит подробный отчет. xlCompare существенно улучшит Вашу продуктивность. С xlCompare сравнение файлов Excel становится очень простым!