Vba excel сравнивает две таблицы на разных листах

Обновлено: 21.11.2024

Сравните два листа с помощью VBA и выведите различия на новый лист. С помощью следующего макроса vba вы можете сравнить содержимое двух рабочих листов и отобразить только различия. Следующее сравнивает столбец A на активном листе (Sheet1), например, с столбцом A на Sheet2. Он использует словарь сценариев, поэтому процесс выполняется очень быстро для больших объемов данных. Конечно, эту процедуру можно воспроизвести с помощью ВПР в родном Excel без вывода в новую область.

Sub NoMatches() 'Excel vba для удаления дубликатов.
Dim dic As Object
Dim ar As Variant
Dim ar1 As Variant
Dim var As Variant
Dim i As Long
Dim n As Long

Set dic=Createobject("Scripting.Dictionary")
dic.CompareMode=1
ar=Range("A2", Range("A" & Rows.Count).End(xlUp) ).Value
var=Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value
ReDim ar1(1 To UBound(var) , 1 к 1)

'Перебрать ar и добавить в словарь.

'Результаты вывода Удалить любое дублирование
Sheet3.Range("E2:E" & UBound(var)).Value=ar1
Range("E2:E" & UBound(var)). RemoveDuplicates 1
End Sub

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

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

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

ar=Sheet1.Cells(10, 1).CurrentRegion.Value
ar=Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value

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

Sub CompareIt() 'Excel VBA со словарем сценариев для сравнения листов
Dim ar As Variant
Dim arr As Variant
Dim Var As Variant
Dim v()
Размерьте i As Long
Размерьте n As Long
Размерьте j As Long
Размерьте str As String

ar = Sheet1.Cells(10, 1).CurrentRegion.Value

ar = Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value

Для i = 2 To UBound(ar, 1)

Если .exists(str), то

Для каждой записи в .keys

Приложенный файл Excel показывает работу с желтыми ячейками, показывающими различия.

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

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

  1. Выделите все ячейки во второй таблице (B10:F15), для которых требуется условное форматирование, и на ленте выберите Главная > Условное форматирование > Новое правило.

  1. В окне правила форматирования (1) выберите Использовать формулу, чтобы определить, какие ячейки форматировать для типа правила, (2) введите формулу:

Затем (3) нажмите Формат.

Эта формула воспроизводится ячейка за ячейкой в ​​выбранном диапазоне. Итак, в следующей ячейке он проверит, отличается ли B3 от B11, а когда он перейдет ко второму столбцу диапазона, он проверит, отличается ли C2 от C10, и так далее. Если условие истинно, ячейка форматируется в соответствии с правилом.

  1. В окне "Формат" перейдите на вкладку "Заливка", выберите цвет (красный) и нажмите "ОК".

  1. Вернувшись в окно «Новое правило форматирования», нажмите «ОК». Здесь вы также можете предварительно просмотреть форматирование, которое будет применено к соответствующим ячейкам.

В результате все различия между двумя таблицами (ячейки D10, D13, D14, F10, F13 и F14) выделяются красным во второй таблице.

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

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

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

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

Начнем!

Это руководство охватывает:

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

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

Это параметр Просмотреть рядом.

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

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

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

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

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

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

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

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

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

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

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

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

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

С параметром "Просмотреть рядом" вы можете одновременно сравнивать только два файла Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<Р>