Как сравнить два листа Excel (на предмет различий)

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

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

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

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

Давайте начнем!

Сравните два листа Excel в отдельных файлах Excel (бок о бок)

Если вы хотите сравнить два отдельных файла Excel рядом (или два листа в одной книге), в Excel есть встроенная функция для этого.

Это Просмотр бок о бок вариант.

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

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

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

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

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

Ниже приведены шаги по выравниванию двух файлов бок о бок и их сравнению:

1. Откройте файлы, которые хотите сравнить.
2. В каждом файле выберите лист, который вы хотите сравнить.
3. Перейдите на вкладку «Просмотр».
4. В группе Windows нажмите на опцию «Просмотр бок о бок». Это становится доступным только в том случае, если у вас открыто два или более файла Excel.

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

Если вы хотите расположить файлы вертикально, нажмите на опцию «Упорядочить все» (на вкладке «Просмотр»).

Откроется диалоговое окно «Упорядочить окна», в котором вы можете выбрать «Вертикально».

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

Но для этого нужно включить Синхронная прокрутка.

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

Сравнение нескольких листов в отдельных файлах Excel (бок о бок)

С опцией «Просмотр бок о бок» вы можете сравнить только два файла Excel за один раз.

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

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

В диалоговом окне «Упорядочить окна» выберите «Вертикально / горизонтально» и нажмите «ОК».

Это расположит все открытые файлы Excel в выбранном порядке (по вертикали или горизонтали).

Сравните два листа (бок о бок) в одной книге Excel

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

Но вы все равно можете провести такое же параллельное сравнение.

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

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

Ниже приведены шаги для сравнения двух листов в Excel:

1. Откройте книгу, в которой есть листы, которые вы хотите сравнить.
2. Перейдите на вкладку «Просмотр».
3. В группе «Окно» нажмите «Новое окно». Это открывает второй экземпляр той же книги.
4. На вкладке «Просмотр» нажмите «Упорядочить все». Откроется диалоговое окно «Упорядочить окна».
5. Выберите «По вертикали», чтобы сравнить данные в столбцах (или выберите «По горизонтали», если вы хотите сравнить данные в строках).
6. Щелкните ОК.

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

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

Как это работает?

Когда вы нажимаете «Новое окно», он снова открывает ту же книгу с немного другим именем. Например, если ваша книга называется «Тест», и вы нажимаете «Новое окно», она назовет уже открытую книгу «Тест — 1», а второй экземпляр — «Тест — 2».

Обратите внимание, что это все та же книга. Если вы внесете какие-либо изменения в любую из этих книг, это отразится на обеих.

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

Вы также можете включить синхронную прокрутку, если хотите (нажав на опцию «Синхронная прокрутка» на вкладке «Просмотр»).

[lyte id=’OYxyOwOQea8′ /]

Сравните два листа и выделите различия (с использованием условного форматирования)

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

Кроме того, выполнение этого уровня сравнения вручную может привести к множеству ошибок.

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

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

Обратите внимание, что вы НЕ МОЖЕШЬ сравните два листа в разных книгах.

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

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

Ниже приведены шаги для этого:

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

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

Как это работает?

Условное форматирование выделяет ячейку, когда заданная формула для этой ячейки возвращает ИСТИНА. В этом примере мы сравниваем каждую ячейку на одном листе с соответствующей ячейкой на другом листе (выполняется с помощью оператора not equal to в формуле).

Когда условное форматирование обнаруживает какие-либо различия в данных, оно выделяет это на листе Ян (тот, в котором мы применили условное форматирование.

Обратите внимание, что в этом примере я использовал относительную ссылку (A1, а не $ A $ 1, $ A1 или A $ 1).

При использовании этого метода для сравнения двух листов в Excel помните следующее;

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

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

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

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

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

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

Предположим, у вас есть набор данных, показанный ниже, на листе с названием «Янв» (и аналогичные данные на листе с именем «Фев»), и вы хотите знать, какие значения отличаются.

Чтобы сравнить два листа, сначала вставьте новый лист (назовем этот лист «Разница»).

В ячейке A1 введите следующую формулу:
= ЕСЛИ (Янв! A1Фев! A1, "Значение января:" & Янв! A1 & CHAR (10) & "Значение февраля:" & Фев! A1, "")

Скопируйте и вставьте эту формулу для диапазона, чтобы охватить весь набор данных на обоих листах. Поскольку у меня небольшой набор данных, я скопирую и вставлю эту формулу только в диапазон A1: B10.

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

Преимущество этого метода в том, что он только дает вам различия и показывает, в чем именно разница. В этом примере я легко вижу, что цены в ячейках B4 и B8 различаются (а также точные значения в этих ячейках).

Сравните два файла / листа Excel и узнайте разницу с помощью VBA

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

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

Предположим, у вас есть два листа Jan и Feb, и вы хотите сравнить и выделить различия в листе Jan, вы можете использовать приведенный ниже код VBA:
Sub CompareSheets () Dim rngCell As Range для каждой rngCell в листах («Янв»). UsedRange, если не rngCell = Worksheets («Feb»). Cells (rngCell.Row, rngCell.Column) Then rngCell.Interior.Color = vbYellow End Если следующий rngCell End Sub
В приведенном выше коде цикл For Next используется для просмотра каждой ячейки на листе Jan (весь используемый диапазон) и сравнения его с соответствующей ячейкой на листе Feb. Если он обнаруживает разницу (которая проверяется с помощью оператора If-Then), он выделяет эти ячейки желтым цветом.

Вы можете использовать этот код в обычном модуле редактора VB.

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

Вот шаги, чтобы получить личную книгу макросов в Excel (она недоступна по умолчанию, поэтому вам необходимо включить ее).

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

Здесь вы найдете инструкции по добавлению этого макроса в QAT.

Использование стороннего инструмента — XL Comparator

Еще один быстрый способ сравнить два файла Excel и проверить совпадения и различия — использовать бесплатный сторонний инструмент, такой как XL Comparator.

Это веб-инструмент, в который вы можете загрузить два файла Excel, и он создаст файл сравнения, который будет содержать общие данные (или разные данные в зависимости от того, какой вариант вы выбрали.

Предположим, у вас есть два файла с наборами данных о клиентах (например, имя и адрес электронной почты), и вы хотите быстро проверить, какие клиенты находятся в файле 1, а не в файле 2.

Ниже показано, как вы сравниваете два файла Excel и создаете сравнительный отчет:

1. Откройте https://www.xlcomparator.net/
2. Используйте параметр «Выбрать файл», чтобы загрузить два файла (максимальный размер каждого файла может составлять 5 МБ).
3. Щелкните по кнопке Далее.
4. Выберите общий столбец в обоих этих файлах. Инструмент будет использовать этот общий столбец для поиска совпадений и различий.
5. Выберите один из четырех вариантов, хотите ли вы получить совпадающие данные или разные данные (на основе файла 1 или файла 2).
6. Нажмите Далее
7. Загрузите файл сравнения, в котором будут данные (в зависимости от того, какой вариант вы выбрали на шаге 5)

Ниже приведено видео, в котором показано, как работает инструмент XL Comparator.

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

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