4 способа быстро и легко сравнить большие таблицы

Как сравнить два файла в Excel Online при помощи простой формулы

Одной из простейших форм информации является формула равенства. Если данные совпадают, система выдаст символ «Истина», если не совпадают — «Ложь». Вы можете сравнивать как текстовые данные, так и числовые данные. Недостатком является то, что его можно использовать только в том случае, если информация упорядочена одинаково и имеет одинаковое количество строк на листе.

Чтобы сравнить два файла по формуле:

1. Создайте дополнительный столбец и введите в нем знак «=».
2. Нажмите на имя, чтобы сравнить.
3. Снова поставьте «=» и нажмите на ячейку во второй сравниваемой таблице. Вы получите определенное выражение.
4. Нажмите «Enter», чтобы получить результаты.

Сравнение двух файлов в Экселе выделением групп ячеек

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

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

1. Выберите массивы, которые вы сравниваете. Нажмите «Главная» и нажмите «Найти и отметить». В открывшемся списке найдите «Выделить группу ячеек».
2. Нажмите «Выбрать» в появившемся окне».
3. Окно выбора групп ячеек будет запущено вне зависимости от того, какой из двух вариантов вы выберете. Нажмите «Выбрать по строкам» и нажмите «ОК».
4. Несовпадающие значения будут выделены другим цветом, а одна из ячеек в совпадающих строках станет активной.

Ищем повторы в двух файлах при помощи условного форматирования

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

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

1. Выберите основную область таблицы и область, где вы хотите искать отличия.
2. Перейдите в раздел «Главная», выберите «Условное форматирование» и в появившемся списке нажмите «Управление правилами».
3. В открывшемся окне нажмите «Создать правило» и выберите «Использовать формулу»;
4. Введите формулу в строку «Формат ячеек», которая должна состоять из адресов первых ячеек, разделенных знаком «<>».
5. Перейдите в раздел «Формат», в открывшемся окне перейдите в «Заливка» и выберите любой цвет, которым будут окрашиваться не совпадающие данные. Нажмите ОК».
6. Нажмите OK в открывшемся Диспетчере правил».
7. Данные во второй таблице, которые не совпадают, будут окрашены в цвет, выбранный вами при настройке.

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

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

На любой платформе 1С есть типовая обработка «Сравнить файлы». Находится в верхнем левом углу: «Главное меню — Файл — Сравнить файлы».

Он позволяет сравнивать содержимое файлов .txt и .mxl. Другие форматы не принимает и выдает сообщения: «Ошибка выполнения операции с файлом» и «Двоичные файлы разные».

Для начала добавьте файлы для сопоставления в поля «Сравнить» и «С». Укажите «Как» сравнивать: табличный документ, текстовый документ или бинарное сравнение. Нажмите клавишу ОК».

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

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

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

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

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

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

Плюсы:

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

Минусы:

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

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

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

Для примера приведу стандартную формулу «Если».

Приспособление:

1. Выберите пустую ячейку. Он будет отображать результат функции.
2. На верхней панели щелкните метку формулы
3. В предложенном списке выберите функцию «ЕСЛИ», нажмите «ОК».
4. Задайте условие в поле «Выражение журнала». На примере экрана условие следующее: если значение ячейки в столбце «Конечный остаток» в левой таблице равно значению ячейки в столбце «Конечный остаток» в правой.
5. В поле «Значение_если_истина» укажите, какой вывод отображать, если значения равны.
6. В поле «Значение_если_ложь» указываем, какой вывод должен отображаться, если значения разные.
7. Нажмите «ОК».

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

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

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

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

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

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

И ее результат:

Плюсы:

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

Минусы:

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

Способ №3: использовать «Средство сравнения электронных таблиц»

«Инструмент сравнения электронных таблиц» — это отдельный программный пакет, который устанавливается вместе с Microsoft Office 2013 Pro+ и Microsoft Office 365 Pro+. В других версиях этой программы нет.

Для его запуска в меню «Пуск» выберите: «Все программы — Microsoft Office — Spreadsheets Compare 2013)».

На вкладке «Главная» нажмите «Сравнить файлы).

В поля «Сравнить» и «От» введите нужные файлы Excel. Нажмите ОК».

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

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

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

Результаты сравнения можно сохранить в Excel с помощью команды: «Экспорт результатов» (Export results) на вкладке «Главная». Можно скопировать результаты и вставить их в другую текстовую программу: «Копировать результаты в буфер обмена).

Плюсы:

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

Минусы:

• разделы таблицы нельзя редактировать;
• работает только с форматом Excel;
• доступно в Microsoft Office 2013 Pro+ и Microsoft Office 365 Pro+.

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

Power Query — это отдельная надстройка Excel, включенная по умолчанию в Microsoft Office 2016 и более поздних версий (вкладка «Данные»). В младших версиях можно установить самостоятельно, скачав по ссылке: https://www.microsoft.com/ru-RU/download/details.aspx?id=39379 .

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

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

• из Интернета;
• формат внешнего файла Excel, CSV, XML;
• базы данных SQL, Access, IBM DB2 и другие;
• Лазурь;
• динамика 365 веб-сервисов,
• Фейсбук.

Самые продвинутые пользователи могут «Написать заявку с нуля».

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

Вот две таблицы в Excel. Вы должны преобразовать каждую в смарт-таблицу. Для этого выделите нужную область, на вкладке «Главная» нажмите «Форматировать как таблицу» или нажмите Ctrl+T на клавиатуре.

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

Нажмите «Закрыть и загрузить в…», выберите «Создать только подключение» и нажмите кнопку «Загрузить».

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

В правой части окна появились два запроса с указанными именами таблиц. На панели инструментов выберите команду «Объединить» и выберите созданные вами запросы один за другим. В каждой части окна щелкните, чтобы выбрать столбцы, с которыми вы хотите объединить файлы. Тип объединения «Полное внешнее (все строки из обеих таблиц)». Нажмите «ОК».

Вы вернулись в редактор Power Query. Здесь обе таблицы объединяются в одну путем слияния указанных столбцов. В «Свойствах» дайте имя новому документу — разверните крайний правый столбец, нажав на иконку
.

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

В примере указано:

1. Если в графе «Регистратор» в первой таблице указано нулевое значение, а во второй таблице это значение заполнено, документ «Добавлен».
2. Если во второй таблице столбец «Регистратор1» имеет нулевое значение, документ «Удален».
3. Если значения в столбцах «Конечный баланс» и «Конечный баланс1» не совпадают, данные «Изменены».

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

Обязательно заполните значение «Иначе» в левом нижнем углу — оно будет выполнено, если ни одно из вышеперечисленных условий не будет выполнено. Нажмите ОК».

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

Сохраните документ с помощью команды на вкладке «Главная»: «Закрыть и загрузить… — Таблица — Существующий лист —
— указать ячейку, с которой должна начинаться новая таблица — ОК — Загрузить».

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

Плюсы:

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

Минусы:

• пока не нашел =)

Как интерпретировать результаты сравнения

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