Сравнение листов в excel на совпадения. Сравнение данных в Excel на разных листах. Как сравнить файлы Excel

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

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

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

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

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

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

Вы можете экспортировать результаты в файл Excel, более удобный для чтения. Выберите Home > Export Results (Главная > Экспорт результатов).

Чтобы скопировать результаты и вставить их в другую программу, выберите Home > Copy Results to Clipboard (Главная > Копировать результаты в буфер обмена).

Чтобы отобразить форматирование ячеек из книги, выберите Home > Show Workbook Colors (Главная > Показать цвета книги).

Другие причины для сравнения книг

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

Средство сравнения электронных таблиц можно использовать не только для сравнения содержимого листов, но и для поиска различий в коде Visual Basic для приложений (VBA). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.

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

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

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

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

Первый способ решения поставленной задачи. Решение только силами формул MS Excel.

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

Для сравнения показателей бега на 100 метров формула выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА)<>D2;D2-ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА);"Разницы нет")
В случае, если разницы нет, выводится сообщение, что разницы нет, если она присутствует, тогда от значения в конце сезона отнимается показатель начала сезона.

Формула для бега на 3000 метров выглядит следующим образом:
=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;4;ИСТИНА)<>E2;"Разница есть";"Разницы нет")
Если конечное и начальное значения не равны выводится соответствующее сообщение. Формула для подтягиваний может быть аналогична любой из предыдущих, дополнительно приводить ее смысла нет. Конечный файл с найденными расхождениями приведен ниже.

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

Видео сравнения двух файлов MS Excel с помощью функций и .

Второй способ решения задачи. Решение с помощью MS Access.

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

Последний представляет собой связь файлов Excel и Access, поэтому при изменении данных в файлах Excel, расхождения будут находится автоматически при выполнении запроса в MS Access.

Следующим шагом после произведения импорта будет создание связей между таблицами. В качестве связующего поля выбираем уникальное поле «№ п/п».
Третьим шагом будет создание простого запроса на выборку с помощью конструктора запросов.

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

Видео сравнения файлов MS в Excel, с помощью MS Access.

В результате проделанных манипуляций выведены все записи, с разными данными в поле: «Бег на 100 метров». Файл MS Access представлен ниже (к сожалению, внедрить, как файл Excel, SkyDrive не позволяет)

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

Казалось бы простая задача - сравнение таблиц. Точнее сравнение двух столбцов таблицы на совпадения или различия. Логично предложить что Excel идеальное средство решения проблемы, но увы простого бесплатного сравнение таблиц в Excel я не нашёл, разве что кроме примитивного «строка1=строка2». В реальности необходима некоторая обработка строк до сравнения, так как они могут содержать лишние пробелы, знаки препинания и так далее. Как итог было решено написать утилиту сравнивающую два текстовых файла построчно и с обработкой строк по выбору пользователя…

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

Скачайте и распакуйте программу. В исходном варианте она содержит три файла «Compare.exe» - сама программа. «Список 1.txt» и «Список 2.txt» - пустые текстовые файлы. Как раз в них нужно вставить ваши строки для сравнения. Запускаем:

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

После того, как наиграетесь с примером сравнения - скопируйте ваши данные для сравнения в файлы «Список 1.txt» и «Список 2.txt» и с выбранными ранее настройками нажмите кнопку «Обработать файлы». Во время обработки файлов на кнопке появляется надпись «Идёт обработка» красным цветом, дождитесь окончания этого процесса. По окончанию загляните в место откуда запустили программу, в папке с программой в зависимости от настроек появляются файлы указанные в примере сравнения. При каждом новом сравнении или открытие/закрытие программы все файлы кроме «Список 1.txt» и «Список 2.txt» удаляются.

И немного о скорости сравнения. Большинство реальных задач решаются практически мгновенно. Ну а мои тесты такие (процессор типа Intel Core под сокет LGA 775 c частотой 2ГГц):

Сравнение 2-х списков размером 1Мб каждый (25 символов на строку и 39 тысяч строк в каждом списке), для сравнения очевидно нужно каждую строку 1-ого списка сравнить со всеми строками другого. Итого получаем 1,521 миллиарда сравнений строк. Время выполнения около 20 секунд. Расход памяти менее 10Мб.

Сравнение 2-х списков размером 10Мб каждый (25 символов на строку и 390 тысяч строк в каждом списке). Итого получаем 152,1 миллиарда сравнений строк. Время выполнения около часа. При этом программа отхватывает себе около 200Мб оперативной памяти. Хотя такие размеры - это уже территория баз данных. В этой программе все разумные способы повышения скорости я уже использовал.

Алгоритм работы и параметры сравнения

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

Искать совпадающие строки и Искать отличающиеся строки - тут всё понятно, будут искаться совпадающие строки или отличающиеся. Совпадающие будут записываться в файл «Совпадающие.txt». При поиске отличающихся строк они будут записываться в два файла «Несовпадения 1.txt» и «Несовпадения 2.txt» для Списков 1 и 2 соответственно. Так же в области примера сравнения вместо одного окна появляется два.

ВНИМАНИЕ! При сравнении списков на совпадение есть особенность, так как одинаковые строки имеются в обоих списках, в результат попадают строки из списка 1. Более аккуратно оформленный текст помещайте в список 1 при сравнении на совпадения.

Исправлять ошибки раскладки клавиатуры - до Punto Switcher тут конечно далеко. Имеются ввиду ошибки набора похожих букв не в той раскладке (C,H,P и т.д.). Например русской «с» и латинской. Они находятся на одной клавише и если слово начинается с «с», то можно набрать первую букву в английской раскладке, а потом переключиться на русскую или же наоборот. Алгоритм замены такой, что если в слове русских букв больше чем английских, то английские меняются на русские и наоборот.

Исправлять Ё(ё) на Е(е) - просто все «ё» заменяются на «е».

Сравнивать без учёта регистра - все буквы стают большими.

Сравнивать по уникальным строкам - Если параметр включён, то сначала списки проверяются на совпадающие строки. Если строка повторяется например 5 раз, то один экземпляр этой строки остаётся в списке для сравнения, а 4-е отправляются в список «повторов». Повторы для каждого списка слов свои.

Без этого параметра строки сравниваются как бы попарно. Например при сравнении на совпадающие строки если в 1-ом списке будет 2-е одинаковые строки, а во 2-ом списке ещё 3-и такие строки, то результатом будут только две строки, т.к. 3-ей строке не нашлось пары с которой бы она совпадала. Если для этого примера переключить сравнения на не совпадающие строки, то в результат несовпадающих попадёт одна строка из списка 2-а, т.к. она ни с чем не совпадает.

Используя сравнение по уникальным строкам можно найти повторяющиеся строки в списке. Для этого можно например заполнить строками только файл «Список 1.txt» и сравнить с пустым файлом «Список 2.txt» и тогда в файле «ПовторСп1.txt» появятся потворяющиеся строки из списка 1.

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

Удалять точки и запятые и Удалять все пробелы - просто удаляются и всё тут.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Наличием (отсутствием) строк (наименований счетов). Например, в таблице на листе Январь отсутствует счет 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

В случае отсутствия соответствующей строки функция ВПР() возвращает ошибку #Н/Д, которая обрабатывается связкой функций ЕНД() и ЕСЛИ() , заменяя ошибку на 0 (в случае отсутствия строки) или на значение из соответствующего столбца.

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

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

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

Для этого необходимо:

С помощью =ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));"") сформировать в столбце А перечень счетов из обоих таблиц (без повторов);
С помощью =ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , где Список -