Как сделать сравнение двух таблиц в excel

Добавил пользователь Владимир З.
Обновлено: 04.10.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нужно сравнить два файла 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 с помощью функции СЧЁТЕСЛИ и правил

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Зачем сравнивать таблицы Excel?

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

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

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

Что можно сравнить в двух книгах Excel?

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

Ошибки формул SysGen

Ошибка имен SysGen

Подключение для передачи данных

Защита листа / книги

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

Как сравнить две книги Excel?

Активация вкладки запроса в Microsoft Excel

Включение вкладки "Запрос" в Excel

Шаги по сравнению двух книг Excel

Шаг 1. У нас есть более ранняя рабочая тетрадь, подобная этой:

Предыдущая рабочая тетрадь

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

Текущая рабочая тетрадь

Выберите "Сравнить" на вкладке "Запрос".

Выберите файлы для сравнения

Результаты сравнения двух файлов Excel

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

Статьи по Теме:

Что нельзя сравнивать?

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

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

Заключение

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

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

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

Как видно из рисунков, таблицы различаются:

Наличием (отсутствием) строк (наименований счетов). Например, в таблице на листе Январь отсутствует счет 26 (см. файл примера ), а в таблице на листе Февраль отсутствуют счет 10 и его субсчета.
Разными значениями в строках. Например, по счету 57 обороты за январь и февраль не совпадают.

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

Простой вариант сравнения 2-х таблиц

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

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

Чтобы определить какая из двух таблиц является наиболее полной нужно ответить на 2 вопроса: Какие счета в февральской таблице отсутствуют в январской? и Какие счета в январской таблице отсутствуют в январской?

Это можно сделать с помощью формул (см. столбец Е): = ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));"Нет";"Есть") и = ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));"Нет";"Есть")

Сравнение оборотов по счетам произведем с помощью формул: = ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и = ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7

С помощью Условного форматирования можно выделить расхождения (например, красным цветом).

Более наглядный вариант сравнения 2-х таблиц (но более сложный)

По аналогии с задачей решенной в статье Сравнение 2-х списков в MS 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 минут.

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

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

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

Плюсы:

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

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

Минусы:

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

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

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