Как сравнить два файла Excel на совпадения?

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

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

Важно: Spreadsheet Compare is only available with Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019, or Приложения Microsoft 365 для предприятий.

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

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

На вкладке Home (Главная) выберите элемент Compare Files (Сравнить файлы).

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

В диалоговом окне «Сравнение файлов» в строке «С» до нужной версии.

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

Нажмите кнопку ОК, чтобы выполнить сравнение.

Примечание: Появление сообщения «Не удается открыть книгу» может означать, что книга защищена паролем. Нажмите кнопку ОК и введите пароль. Узнайте подробнее о том, как действуют пароли при использовании средства сравнения электронных таблиц.

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

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

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

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

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

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

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

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

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

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

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

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

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

Как в офисе.

Казалось бы простая задача — сравнение таблиц. Точнее сравнение двух столбцов таблицы на совпадения или различия. Логично предложить что 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.

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

Сравнение 2 файлов в Excel на предмет различий

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

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

Таких действий множество. Давайте рассмотрим некоторые из них.

С какой целью проводится сравнение файлов Excel

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

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

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

Все способы сравнения 2 таблиц в Excel

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

Формула равенства и проверка на ложь-истину

Начнем, конечно, с наиболее простого метода. Таким методом сравнение документов возможно, причем в довольно широких пределах. Можно сопоставлять между собой не только текстовые значения, но и числовые. И давайте приведем небольшой пример. Допустим, у нас есть два диапазона с ячейками числового формата. Для этого достаточно просто написать формулу равенства =C2=E2. Если окажется, что они равны, в ячейке будет написано «ИСТИНА». Если же они будут отличаться, то «ЛОЖЬ». После этого нужно данную формулу перенести на весь диапазон с помощью маркера автозаполнения.

Теперь разница видна невооруженным глазом.

Выделение различающихся значений

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

В появившемся меню необходимо кликнуть по меню «Выделить группу ячеек…». Далее откроется окошко, в котором нам необходимо в качестве критерия выбрать отличия по строкам.

2 3

Сравнение 2 таблиц при помощи условного форматирования

Условное форматирование является очень удобным и, что важно, функциональным методом, позволяющим выбрать цвет, которым будет выделяться отличающееся или такое же значение. Найти этот параметр можно на вкладке «Главная». Там можно найти кнопку с соответствующим названием и в появившемся перечне выбираем «Управление правилами». Появится диспетчер правил, в котором нам надо выбрать меню «Создать правило».

Далее из перечня критериев нам нужно выбрать тот, где написано, что нужно использовать формулу для определения ячеек, которые будут отформатированы особенным образом. В описании правила нужно задать формулу. В нашем случае это =$C2<>$E2, после чего подтверждаем свои действия нажатием кнопки «Формат». После этого задаем внешний вид ячейки и смотрим, нравится ли он, через специальное мини-окошко с образцом.

Если все устраивает, нажимаем кнопку «ОК» и подтверждаем действия.

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

Функция СЧЁТЕСЛИ + правила для сравнения таблиц

Все методы, которые мы описали ранее, удобны для тех формат, формат которых един. Если же предварительно таблицы не были упорядоченными, то лучше всего метод, в котором необходимо сравнивать две таблицы с помощью функции СЧЕТЕСЛИ и правил.

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

В этом примере формула такая, как показана на этом скриншоте.

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

Функция ВПР для сравнения 2 таблиц

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

Эта функция перебирает каждое значение и смотрит, нет ли повторов из первой колонки во второй. Ну и после выполнения всех операций записывается в ячейке это значение. Если его там нет, то получаем ошибку #Н/Д, чего вполне достаточно для того, чтобы автоматически понимать, какое значение будет не соответствовать.

Функция ЕСЛИ

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

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

С помощью формулы, которая использует функции ЕСЛИ , ЕСЛИОШИБКА и ПОИСКПОЗ можно перебрать все нужные элементы колонки А, а потом в колонке B. Если оно было обнаружено в колонке B и A, то оно возвращается в соответствующую ячейку.

Макрос VBA

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

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

Как провести сравнение файлов в Эксель

Если пользователь поставил перед собой задачу (ну или ему поставили ее) сравнить два файла, то это можно сделать сразу двумя методами. Первый из них – использование специализированной функции. Чтобы реализовать этот метод, следуйте инструкции:

Откройте те файлы, сравнение которых требуется.
Откройте вкладку «Вид» – «Окно» – «Вид рядом».

После этого два файла будут открыты одном документе Excel.

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

Условное форматирование для сравнения 2 файлов Эксель

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

Сперва нам необходимо перенести сравниваемые листы в один документ.

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

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

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

Внимание: адреса ячеек нужно указывать те, которые на другом листе. Это можно сделать через меню ввода формулы.

Сравнение данных в Эксель на разных листах

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

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

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

Как сравнить 2 листа в таблице Эксель

Сравнение информации, располагающейся на двух листах, осуществляется с помощью функции ПОИСКПОЗ . В качестве первого ее параметра располагается пара значений, которую нужно искать на том листе, который отвечает за следующий месяц. Простыми словами, март. Мы можем обозначить просматриваемый диапазон, как совокупность ячеек, которые входят в состав именных диапазонов, объединенных попарно.

Так можно осуществить сравнение строк по двум критериям – фамилия и заработная плата. Ну или любым другим, определенным пользователем. Для всех совпадений, которые удалось найти, записывается в ячейке, в которую вводится формула, число. Для Excel такое значение всегда будет истинным. Поэтому для того, чтобы форматирование применялось по отношению к тем ячейкам, которые были различны, необходимо это значение заменить на ЛОЖЬ , воспользовавшись функцией =НЕ() .

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

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

Важно учесть, что это средство доступно лишь для тех пользователей, кто купил офисные пакеты «Профессиональный плюс».

Открыть его можно непосредственно с вкладки «Главная», выбрав пункт «Сравнить файлы».

После этого появится диалоговое окно, в в котором нужно выбрать вторую версию книги. Также можно ввести адрес в интернете, по которому эта книга расположена.

После того, как мы выберем две версии документа, нужно подтвердить свои действия с помощью клавиши ОК.

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

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

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

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

Как сравнить два файла Excel

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

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

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

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

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

Как сравнить файлы Excel

Excel позволяет пользователям сразу выводить на экран две версии документа, чтобы быстро установить различия между ними:

Сначала откройте рабочие книги, которые нужно сравнить.
Перейдите к Вид> Окно> Вид рядом.

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

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

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

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

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

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

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

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

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

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

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

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

Сравнение файлов Excel с использованием условного форматирования

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

Использование условного форматирования

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

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

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

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

Ctrl + Shift + End.

Перейдите к Главная> Стили> Условное форматирование> Новое правило.

Выбрать Используйте формулу, чтобы определить, какие ячейки форматировать и введите следующее:

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

Далее нажмите Формат и выберите, как вы хотите выделить любые расхождения. Я пошел на стандартную красную заливку. Далее нажмите Хорошо.

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

Пусть Excel сделает тяжелую работу

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

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

У вас есть совет по сравнению документов в Excel? Или вам нужна помощь с процессами, описанными в этом руководстве? В любом случае, почему бы не присоединиться к беседе в разделе комментариев ниже?

Сравните два файла Excel, используя SpreadSheet Compare

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

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

Запись: SpreadSheet Compare от Microsoft доступен только при наличии Office 2013 Pro Plus или Office 365 ProPlus. По какой-то причине он не включен в Office 2016. Также есть программа сравнения электронных таблиц с открытым исходным кодом на SourceForge, но он отличается от Microsoft.

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

Когда вы впервые откроете Spreadsheet Compare, вы увидите главный экран программы, который выглядит следующим образом:

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

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

Нажмите на Сравнить файлы в левом верхнем углу, а затем выберите два файла Excel, которые вы хотите сравнить.

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

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

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

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

График справа быстро покажет вам, сколько изменений каждого типа было записано в электронной таблице:

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

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

Сравнение двух таблиц

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:

С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок. ), что-то пропало (ежевика, малина. ), у каких-то товаров изменилась цена (инжир, дыня. ). Нужно быстро найти и вывести все эти изменения.

Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

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

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

Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.

Плюсы этого способа: просто и понятно, «классика жанра», что называется. Работает в любой версии Excel.

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

Способ 2. Сравнение таблиц с помощью сводной

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

Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table) . Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals) .

Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши — Обновить (Referesh).

Плюсы : такой подход на порядок быстрее работает с большими таблицами, чем ВПР.

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

Способ 3. Сравнение таблиц с помощью Power Query

Power Query — это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить — получите новую вкладку Power Query.

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl + T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table) . Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load To. ) :

. и в появившемся затем окне выбрем Только создать подключение (Connection Only) .

Повторите то же самое с новым прайс-листом.

Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить (Data — Get Data — Merge Queries — Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer) :

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

В итоге получим слияние данных из обеих таблиц:

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

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column) . А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home) :

Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl + Alt + F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data) .

Плюсы : Пожалуй, самый красивый и удобный способ из всех. Шустро работает с большими таблицами. Не требует ручных правок при изменении размеров таблиц.

Минусы : Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.