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

Добавил пользователь Алексей Ф.
Обновлено: 19.09.2024

Описание работы программы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Эти инструменты сравнения могут помочь вам обнаружить несогласованные данные, устаревшие значения, неверные формулы, неправильные вычисления и другие проблемы на вашем листе Excel.

1. Сравните два листа Excel: бок о бок

В новом окне выберите (второй) рабочий лист, который вы хотите сравнить, или переключитесь на него.

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

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

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

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

Когда вы закончите сравнение, выберите Просмотр бок о бок, чтобы вернуть листы к их исходным размерам.

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

2. Сравните два файла Excel с помощью онлайн-инструментов

Существуют веб-инструменты, предлагающие услуги сравнения Excel. Вы найдете эти инструменты полезными, если на вашем компьютере не установлен Excel. Этот Инструмент сравнения Excel от Aspose — хороший веб-инструмент для сравнения двух файлов Excel.

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

Spreadsheet Compare — надежное программное обеспечение для сравнения двух файлов или листов Excel. К сожалению, на данный момент он доступен только для устройств с Windows. Он поставляется как отдельная программа, а также встроен в Microsoft Excel, включенный в версии / пакеты Office: Office Professional Plus (2013 и 2016) или Microsoft 365.

Использование сравнения электронных таблиц в Excel

Это запустит сравнение электронных таблиц в новом окне, подчеркнув любое несоответствие в вашем наборе данных. Обычные ячейки с разными значениями будут выделены зеленым цветом. Ячейки с формулами имеют фиолетовый формат, а ячейки с макросом имеют бирюзовую заливку.

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

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

Используйте сравнение электронных таблиц как отдельную программу

Сравнение электронных таблиц обработает файлы и выделит ячейки с разными значениями зеленым цветом.

Найди отличия

Часто мы сталкиваемся с задачами, где необходимо сверить данные двух файлов или таблиц до изменений и после. Это не проблема, если в каждом источнике небольшое количество строк и максимум одна страница. А если предстоит найти отличия в файлах, состоящих из сотен страниц и тысяч строк? Искать глазами трудно и долго — велика вероятность что-то не заметить и потратить на столь утомительное занятие часы, дни, а то и недели. В статье я поделюсь 4 способами сравнить объемные таблицы по различным значениям, которые легко освоит любой пользователь.

Способ № 1: сравнить в базе 1С

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

На верхней панели инструментов доступны следующие команды :

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

меняет порядок сравнения: если ее нажать, то правый лист окажется слева, а левый — справа;

— настройка ячеек по цветам.

В нижней части окна отображается подсказка, что означает каждый цвет.

Плюсы:

не требует дополнительного программного обеспечения;

моментально показывает все изменения без предварительных настроек;

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

Минусы:

ограниченное количество форматов — работает только с .txt и .mxl. Нельзя вставить Excel-файлы;

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

нет дополнительных формул, например, чтобы вывести сумму по колонке.

Способ № 2: сравнить в Excel

Самый распространенный метод: выгружаете первоначальный вариант отчета и тот же отчет с поправками в формате .xls и сверяете их в Microsoft Excel, пользуясь доступными формулами.

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

2. В верхней панели кликаем на ярлык формулы .

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

Способ занимает много времени, особенно если сводка состоит из тысячи строк. Его удобно использовать, работая с небольшим объемом данных.

В маленьких файлах вы можете суммировать значения ячеек в колонках и тем самым выходить на строки с различиями.

Вот один из примеров настройки:

Плюсы:

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

Минусы:

иногда потребуется не один час, чтобы подстроить таблицу для шаблонной формулы;

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

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

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

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

Плюсы:

позволяет быстро и просто сверять готовые таблицы;

можно сохранить результат для дальнейшего использования;

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

Минусы:

нельзя редактировать табличные части;

работает только с форматом Excel;

доступна в Microsoft Office 2013 Pro+ и Microsoft Office 365 Pro+.

Способ № 4: использовать Excel Power Query

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

внешнего файла форматом Excel, CSV, XML;

баз данных SQL, Access, IBM DB2 и других;

веб-служб Dynamics 365,

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

Повторите все шаги со вторым документом.

В примере задано:

Итог: таблицы до сравнения и объединенная сводка с готовыми результатами для анализа.

Плюсы:

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

надстройка работает со множеством форматов и различными источниками данных.

Минусы:

пока не обнаружено =)

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

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

Сравните два листа на предмет различий в одной книге с формулой

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

1. Создайте новый лист для размещения результатов сравнения, выберите пустую ячейку (здесь я выбираю ячейку B5) на новом листе, скопируйте и вставьте в нее формулу ниже и нажмите клавишу Enter. Продолжая выбирать ячейку C2, перетащите маркер заполнения в правую ячейку, а затем перетащите вниз, чтобы просмотреть все сравниваемые результаты.

Формула:

= ЕСЛИ ( Лист1! B5 <> Лист2! B5 " Sheet1 : "& Лист1! B5 & "VS. Sheet2 : "& Лист2! B5 " Совпадение ")

Ноты:

1). В формуле Sheet1 и Sheet2 таблицы, с которыми вы будете сравнивать.

2). B5 - первая сравниваемая ячейка двух таблиц. Пожалуйста, измените их по своему усмотрению.

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

Легко сравнить два листа на различия в файле Excel или двух файлах Excel:

Выберите одинаковые и разные ячейки полезности Kutools for Excel может помочь вам легко сравнить два листа на предмет различий в книге или разных книгах. Все отличия будут выбраны автоматически после сравнения. Необязательно выделять различные ячейки, добавляя цвет фона или цвет шрифта, как показано в демонстрации ниже. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)

Выделите различия между двумя листами с помощью условного форматирования

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

1. Выделите всю таблицу на листе, в которой вы выделите различные ячейки значений (в данном случае я выбираю таблицу 1 на листе Sheet1), а затем включите Условное форматирование функция, нажав Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

2.1) Выбрать Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант в Выберите тип правила диалог;
2.2) Введите приведенную ниже формулу в Формат значений, где эта формула истинна коробка;
2.3) Нажмите Формат кнопка для указания цвета выделения различий;
2.4) Нажмите OK кнопка. Смотрите скриншот:

Формула:

= B4 <> Лист2! B4

Примечание:

В формуле B4 - первая ячейка сравниваемых диапазонов. Sheet2 рабочий лист, с которым вы будете сравнивать.

Теперь все различия в таблице 1 по сравнению с таблицей 2 в Sheet2 сразу выделяются, как показано на скриншоте ниже.

Легко сравнивайте два листа на предмет различий в файле Excel или двух файлах Excel

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

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выделите всю таблицу на листе, в которой вы выделите различные ячейки значений (в данном случае я выбираю таблицу 1 на листе Sheet1), а затем щелкните Кутулс > Выберите > Выберите одинаковые и разные ячейки.

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

2.1) Выберите диапазон, с которым вы будете сравнивать на другом листе текущей книги или в другой книге в Согласно информации коробка;
2.2) Выбрать Каждый ряд в на основании раздел;
2.3) Выберите Разные ценности вариант;
2.4) Нажмите OK кнопка. Смотрите скриншот:

Ноты:

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

2. Проверить Заполните цвет фона или Цвет заливки шрифта вариант в Обработка результатов раздел, чтобы выявить различия с определенным цветом фона или цветом шрифта по мере необходимости.

Теперь все различия в Таблице 1 на Листе 1 выделяются или выделяются сразу после сравнения с Таблицей 2 на Листе 2. Смотрите скриншот:

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

Kutools для Excel - Помогает всегда закончить работу раньше времени, чтобы у вас было больше времени, чтобы наслаждаться жизнью

Вы часто играете в догонялки с работой, не хватает времени на себя и семью? Kutools for Excel может помочь вам решить 80% головоломок Excel и повысить эффективность работы на 80%, даст вам больше времени, чтобы заботиться о семье и наслаждаться жизнью.

300 расширенных инструментов для 1500 рабочих сценариев сделают вашу работу намного проще, чем когда-либо.
Больше не нужно запоминать формулы и коды VBA, теперь дайте вашему мозгу отдохнуть.
Сложные и повторяющиеся операции могут быть выполнены разовой обработкой за секунды.
Сократите количество операций с клавиатурой и мышью каждый день, попрощайтесь с профессиональными заболеваниями прямо сейчас.
Станьте экспертом по Excel за 3 минуты, это поможет вам быстро получить признание и продвижение по службе.
110,000 300 высокоэффективных людей и выбор XNUMX+ всемирно известных компаний.
Сделайте так, чтобы ваши 39.0 доллара были больше, чем обучение других людей.
Полнофункциональная бесплатная 30-дневная пробная версия. 60-дневная гарантия возврата денег без причины.

Читайте также: