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

Обновлено: 05.10.2024

Способ 1 . Можно воспользоваться знаком равенства, тогда при установке выражения =А1=В1, мы будем получать ответ либо "ИСТИНА", либо "ЛОЖЬ".

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

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

После этого нам открывается вспомогательное окно, в котором мы выбираем интересующий нас вариант проверки. В моём случае я выбрала поиск отличий по строкам.

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

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

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

простой способ, как сравнить две таблицы в Excel;
быстрое выделение значений, которые отличаются;
сравнить две таблицы в Excel с помощью условного форматирования;
сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил;
как сравнить две таблицы в Excel с помощью функции ВПР;
как сравнить две таблицы в Excel с помощью функции ЕСЛИ;
сравнить две таблицы с помощью макроса VBA;
сравнить с помощью надстройки Inquire.

Простой способ, как сравнить две таблицы в Excel

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

Сравнить две таблицы в Excel с помощью условного форматирования

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

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

Как сравнить две таблицы в Excel функции ЕСЛИ

Этот вариант предусматривает использования логической функции ЕСЛИ и отличие этого способа в том что для сравнения двух столбцов будет использован не весь массив целиком, а только та ее часть, которая нужна для сравнения.

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));"";C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

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

Excel 2013 Office для бизнеса Средство сравнения электронных таблиц 2013 Средство сравнения электронных таблиц 2016 Средство сравнения электронных таблиц 2019 Сравнение электронных таблиц 2021 Больше. Основные параметры

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

Важно: средство сравнения электронных таблиц доступна только в Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019 или Приложения Microsoft 365 для предприятий.

Открытие средства сравнения электронных таблиц

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

Помимо средства сравнения электронных таблиц имеется и соответствующая программа для Access — средство сравнения баз данных (Майкрософт). Кроме того, Office профессиональный плюс версии или Приложения Microsoft 365 для предприятий.

Сравнение двух книг Excel

Выберите Главная > Сравнить файлы.

Появляется диалоговое окно Compare Files (Сравнение файлов).

Щелкните значок синей папки рядом с полем Compare (Сравнить), чтобы перейти к более ранней версии книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.

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

Совет: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.

В левой области выберите элементы, которые нужно включить в результаты сравнения книг: установите или снимите флажки у таких элементов, как Formulas (Формулы), Macros (Макросы) или Cell Format (Формат ячеек). Или просто выберите вариант Select All (Выделить все).

Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле "Compare" (Сравнить) (обычно это более старый файл), а книга в правой части — файлу, указанному в поле "To" (С чем) (обычно это более новый файл). Подробные сведения отображаются в области под двумя частями таблицы. Изменения выделяются разными цветами в соответствии с их типом.

Интерпретация результатов

Примечание: Даже если лист скрыт, он все равно сравнивается и отображается в результатах.

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

В приведенном здесь примере результаты 4-го квартала (Q4) в более ранней версии не являются окончательными. Последняя версия книги содержит окончательные цифры для 4-го квартала в столбце E.

В результатах сравнения ячейки E2:E5 в обеих версиях выделены зеленым фоном, что означает изменение введенного значения. В результате изменения этих значений изменились и вычисляемые результаты в столбце YTD — ячейки F2:F4 и E6:F6 выделены сине-зеленым фоном, что означает изменение вычисленного значения.

Вычисляемый результат в ячейке F5 тоже изменился, но гораздо важнее то, что в более ранней версии его формула содержала ошибку (суммировались только ячейки B5:D5, а значение Q4 пропускалось). После обновления книги формула в ячейке F5 была исправлена и теперь имеет вид =СУММ(B5:E5).

Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).

Надстройка средства диагностики в Excel

Помимо средства сравнения электронных таблиц в Excel 2013 есть надстройка средства диагностики электронных таблиц, при включении которой появляется вкладка "Inquire" (Запрос). На этой вкладке можно проанализировать книгу, посмотреть на связи между ячейками, листами и другими книгами, а также убрать с листа лишнее форматирование. Если в Excel открыты две книги, которые нужно сравнить, можно запустить средство сравнения электронных таблиц с помощью команды "Сравнить файлы".

Если вкладка Inquire (Запрос) не Excel вкладки Inquire (Запрос) в Excel, см. включить надстройку Inquire(Запрос) . Подробнее об инструментах этой надстройки см. в статье Возможности средства диагностики электронных таблиц.

Дальнейшие действия

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

Нужно сравнить два файла Microsoft Excel? Вот два простых способа сделать это.

Существует множество причин, по которым вам может понадобиться взять один документ Excel и сравнить его с другим. Это может быть трудоемкой задачей

это требует большой концентрации, но есть способы облегчить себе жизнь.

Нужно ли вам внимательно посмотреть вручную или вы хотите, чтобы Excel выполнял некоторые тяжелые работы

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

Простой способ, как сравнить две таблицы в Excel

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

Использование макроса VBA

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

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

В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_Matches и щелкните кнопку выполнить.

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

Сравнить две таблицы в Excel с помощью условного форматирования

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

Поиск отличий в двух списках

Вариант 1. Синхронные списки

частями таблицы. Изменения
ее изменил? И
на именованные диапазоны.

базе данных Access вы можете использовать

втором одна, то по ним можно

Схема может пересекать
и текстовому файлу.
команды на вкладке

For Each x код покажет, что формулами подтянуть остальное: а, ещё, пардонте, втором. По второму значения и далее размеры тоже совпали весь столбец C работу выполняет отлично окне кнопку

Выделить (Special) и на вкладке
У нас имеется две их типом.
таблиц от МайкрософтВ данной схеме отображаются узнать в разделе(Запрос). Вкладка
перебор данных в единица первого списка.С помощью ВПР() в ячейках по в том же фильтр по цвету 10)).Select правый клик >>
Steel Rain

Вариант 2. Перемешанные списки

Анализ книги.Inquire словаре по ключамНу а по или индекса с которым нужен отбор столбце в первом заливки. На файле’ здесь 10 специальная вставка >>: Доброго всем времениОтличия по строкам (Row цвет. На всех в один рабочий частях таблицы сравнивается

на эти вопросы — ячейки для ячейки

К началу страницы(Запрос) ленты ExcelIf .Item(x) = итему думаю кей
поискпозицией :)
строковые

файле и 2-ую в 8Мб данный — это номер значения. суток. differences) окнах жмем ОК. лист. Необходимо выполнить

каждый лист из найдет изменения и A10 на листе
В книгах, связанных с
содержит кнопки для
1 Then ‘если
не получить безТо, что тамHugo во втором. Получил

способ занимает около последнего столбца вПохожая тема:

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

выделит их. 5 в книге другими книгами с

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

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

Интерпретация результатов

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

Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit

(Размер ячеек по размеру данных).

Как сравнить две таблицы в Excel функции ЕСЛИ

Сравнение файлов Excel на глаз

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

или совершенно разные файлы.

Если из одной и той же книги получено более одного листа, ее необходимо отделить заранее. Для этого перейдите к Вид> Окно> Новое окно.

Это не разделит отдельные листы навсегда, просто откроет новый экземпляр вашего документа.

Далее идите к Посмотреть вкладка и найти Посмотреть бок о бок в Окно раздел.

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

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

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

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