Функция впр в excel для сравнения двух таблиц на разных листах: Сравнение двух таблиц

Содержание

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

6926
04.12.2017
Скачать пример


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



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


Для любой задачи в 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. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку «Столбец такой-то не найден!» при попытке обновить запрос.

Ссылки по теме

  • Как собрать данные из всех файлов Excel в заданной папке с помощью Power Query

  • Как найти совпадения между двумя списками в Excel

  • Слияние двух списков без дубликатов

Сравнение двух таблиц в Excel на совпадение значений в столбцах

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

Сравнение двух столбцов на совпадения в Excel

Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:

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

  1. Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
  2. В появившемся окне в поле «Имя:» введите значение – Таблица_1.
  3. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.

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



Позиции, которые есть в Таблице_1, но нет в Таблцие_2 будут отображаться зеленым цветом. В тоже время позиции, находящиеся в Таблице_2, но отсутствующие в Таблице_1, будут подсвечены синим цветом.

  1. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
  2. В поле ввода введите формулу:
  3. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
  4. Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
  5. В поле ввода введите формулу:
  6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

Принцип сравнения данных двух столбцов в Excel

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Скачать пример сравнения 2 таблицы в Excel

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

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

Смотреть видео – Как сравнить два листа Excel для выявления различий

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

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

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

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

Начнем!

В этом руководстве рассматриваются:

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

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

Это опция View Side by Side .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    5. .

  5. Выберите «Вертикальный», чтобы сравнить данные в столбцах (или выберите «Горизонтальный», если хотите сравнить данные в строках).
  6. Нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В ячейку A1 введите следующую формулу:

 =ЕСЛИ(Янв!A1<>Фев!A1,"Значение января:"&Янв!A1&CHAR(10)&"Значение февраля:"&Фев!A1,"")

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

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

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

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

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

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

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

 Sub CompareSheets()
Dim rngCell как диапазон
 
Для каждой ячейки rngCell в рабочих листах ("Янв").  UsedRange
    Если Не rngCell = Рабочие листы("Фев").Ячейки(rngCell.Row, rngCell.Column) Тогда
        rngCell.Interior.Color = vbYellow
    Конец, если
Следующий rngCell
Конец суб

Приведенный выше код использует цикл For Next для просмотра каждой ячейки на листе Jan (весь используемый диапазон) и сравнивает ее с соответствующей ячейкой на листе Feb. В случае обнаружения различий (что проверяется с помощью оператора If-Then), эти ячейки выделяются желтым цветом.

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

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

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

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

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

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

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

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

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

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

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

    5. .

  5. Выберите один из четырех вариантов, хотите ли вы получить совпадающие данные или разные данные (на основе файла 1 или файла 2)
  6. Нажмите Далее
  7. Загрузите файл сравнения, в котором будут данные (в зависимости от того, какой вариант вы выбрали на шаге 5)

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

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

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

Обратите внимание, что на веб-сайте XL Comparator упоминается, что они удаляют все файлы через 1 час после проведения сравнения.

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

Вам также могут понравиться следующие учебные пособия по Excel:

  • Как сравнить два столбца в Excel (для совпадений и различий)
  • Как удалить дубликаты в Excel
  • Как сравнить текст в Excel (простые формулы)
  • Разделить каждый лист Excel на отдельные файлы (шаг за шагом)
  • Объединение данных из нескольких книг в Excel
  • Объединение данных из нескольких рабочих листов в один рабочий лист в Excel
  • Как сравнивать даты в Excel (больше/меньше, несовпадения)

Сравнение двух рабочих листов Excel, обновление и сопоставление данных

Сравнить два листа — это часть Ablebits Ultimate Suite для Excel, которая может помочь вам быстро найти и выделить разные строки или ячейки в двух рабочих листах Excel и объединить их вручную, ячейка за ячейкой. Найдите ответы на наиболее часто задаваемые вопросы об этом инструменте на странице «Сравнить две таблицы: часто задаваемые вопросы».

  • Видео: Как сравнить листы Excel
  • Еще Сравнить видео с двумя листами
  • Прежде чем начать
  • Как использовать Сравнить два листа
    • Начать сравнение двух листов
    • Шаг 1. Выберите рабочие листы и диапазоны
    • Шаг 2: укажите режим сравнения
    • Шаг 3. Выберите ключевые столбцы (если они есть)
    • Шаг 4. Выберите параметры сравнения
  • Понимание процесса сравнения
  • Как работать в режиме просмотра различий
    • Что такое режим обзора различий
    • Как пользоваться панелью инструментов
    • Как выйти из режима просмотра различий
    • Если что-то пойдет не так
  • Как закрыть листы
  • Часто задаваемые вопросы
  • Ссылки по теме

Видео: Как сравнивать листы Excel

Перед началом работы

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

Только Xlsx и xlsm

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

Сравнение рабочих листов из OneDrive

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

Сохраните все изменения

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

Резервные копии

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

Форматирование ячеек

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

Защищенные рабочие книги

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

Объединенные ячейки

Рабочие листы с объединенными ячейками не могут быть обработаны.

Изменчивые функции

Не пытайтесь сравнивать листы, содержащие изменяемые функции, поскольку каждый раз, когда Excel открывает или пересчитывает книгу, эти функции возвращают новые значения. Нестабильные функции: СЕЙЧАС, СЕГОДНЯ, СЛУЧАЙ, СЛУЧМЕЖДУ, СМЕЩЕНИЕ, ДВССЫЛ. Кроме того, INFO и CELL могут быть изменчивыми в зависимости от их аргументов.

Что игнорируется

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

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

Как использовать Сравнить два листа

Начать сравнение двух листов

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

Чтобы запустить надстройку, на вкладке Ablebits Data в группе Merge нажмите Compare &gt Compare Two Sheets :

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

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

Нажмите кнопку Согласен , чтобы продолжить.

Шаг 1: Выберите рабочие листы и диапазоны

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

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

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

Примечание. Если ваша рабочая книга выделена серым цветом, это означает, что ее нельзя выбрать для сравнения (скорее всего, из-за требований и ограничений, описанных выше):

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

Шаг 2. Укажите режим сравнения

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

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

    Посмотрите, как это работает в Сравните таблицы по всем столбцам видео.

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

    Чтобы узнать, как это работает, посмотрите видео Сравнение листов по ключевым столбцам .

  • По ячейкам
    Третий тип предназначен для листов одинаковой компоновки и размера, таких как балансы или годовые отчеты.

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

Укажите вариант сопоставления

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

  • Первое совпадение
    Если вы выберете Первое совпадение для строки AAA на листе 1, «Сравнить два листа» сопоставит его с первой строкой, содержащей хотя бы один A ценить. Другие строки со значениями A будут помечены как уникальные:
  • Наилучшее совпадение
    Если выбрать Наилучшее соответствие для строки AAA на листе 1, функция «Сравнить два листа» сопоставит его со строкой, содержащей максимальное количество значений A. Другие строки со значениями A будут помечены как уникальные:
  • Только полное совпадение
    Если вы выберете Только полное совпадение для строки AAA на листе 1, функция «Сравнить два листа» пометит только строку AAA как совпадающую, если она будет найдена. Другие строки со значениями A будут помечены как уникальные:

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

Выберите подходящий режим сравнения и нажмите Далее .

Шаг 3: Выберите ключевые столбцы (если они есть)

Если на предыдущем шаге вы выбрали По ключевым столбцам Режим сравнения Сравнить два листа попросит вас выбрать ключевые столбцы. Просто выберите их для Листа 1, и надстройка автоматически найдет соответствующие столбцы на Листе 2:

Щелкните Далее .

Шаг 4. Выберите параметры сравнения

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

  • Показать различия в формулах
    Разные формулы могут возвращать одинаковые значения, поэтому различия в формулах игнорируются. Но если они важны для вас, отметьте эту опцию.
  • Показать различия в форматировании
    Выберите этот параметр для поиска различий в форматировании ячеек.
  • Игнорировать скрытые строки/столбцы
    По умолчанию мы игнорируем скрытые строки и столбцы. Если вам нужно найти различия в скрытых строках или столбцах, снимите этот флажок. Но, конечно же, вы не увидите эти различия отмеченными, если сначала не сделаете их видимыми 🙂
  • Игнорировать пустые строки/столбцы
    Иногда пустые строки и столбцы могут быть важной частью ваших данных. Если это ваш случай, не устанавливайте этот флажок, и они будут включены в сравнение. Отметьте эту опцию, если вы не хотите сравнивать пустые строки.
  • Игнорировать начальные/конечные пробелы
    Иногда в начале или в конце текстовых значений в Excel могут быть лишние пробелы. Установите этот флажок, если вы предпочитаете, чтобы надстройка игнорировала такие пробелы.
  • Добавить столбец состояния
    Если вы выберете эту опцию, Сравнить два листа добавит новый столбец на ваши листы и отметит найденные различия там, а также в выбранном диапазоне. Это не только сделает результат сравнения более понятным, но и позволит вам комфортно управлять различиями.
  • Отметить только столбцом состояния
    Если вы решите ничего не делать с ячейками, отметьте эту опцию, и различия будут отмечены цветом только в столбце состояния. Ваши столы останутся нетронутыми.
  • Добавить текстовые метки в столбец состояния
    Выберите этот параметр, если хотите, чтобы уникальные, разные и совпавшие записи идентифицировались с помощью текстовых меток в столбце состояния.
  • Отметьте различия с помощью
    Выберите наиболее подходящий вариант маркировки. По умолчанию мы предлагаем использовать фоновый цвет. Но если вам нужно увидеть исходный цвет ваших ячеек, вы можете отметить различия цветом шрифта или нижней границей.

Выберите все необходимые параметры и нажмите Сравнить .

Понимание процесса сравнения

При нажатии кнопки Сравнить функция Сравнить два листа следует определенному алгоритму. Вот пошаговое описание:

  1. Сравниваемые рабочие книги сохраняются, включая все изменения, сделанные до сравнения, и закрываются (или одна рабочая книга, если она содержит оба указанных листа).
  2. Резервные копии сохраняются. Путь и время хранения можно найти и изменить в настройках инструментов.
  3. Compare Two Sheets загружает книги в память (не в Excel, это отдельное приложение/процесс) и сканирует их на наличие различий.
  4. Ваши листы обрабатываются построчно.
  5. Инструмент берет первые строки с Листа 1 и Листа 2 и сравнивает их ячейка за ячейкой.
  6. Если все ячейки разные, строки помечаются как уникальные.
  7. Если хотя бы одна пара ячеек в найденной паре строк совпадает, то строки считаются частично совпадающими в обоих листах, а неидентичные ячейки считаются разными.
  8. Инструмент сравнивает следующую пару строк и повторяет шаги 5–7.
  9. Когда обработка завершена, ваши рабочие книги открываются в режиме просмотра различий.

Примечание. Это описание максимально упрощено только с одной целью — улучшить понимание. Compare Two Sheets использует модифицированный, но тем не менее вполне стандартный алгоритм сравнения двух больших наборов данных.

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

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

Как работать в режиме просмотра различий

Что такое режим просмотра различий

Надстройка обрабатывает ваши рабочие листы и открывает их в режиме просмотра различий:

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

  • Синие строки — строки, существующие только на Листе 1.
  • Красные строки — строки, существующие только на Листе 2.
  • Зеленые ячейки — ячейки, отличающиеся на Листе 1 и Листе 2 частично совпадающими строками.

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

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

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

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

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

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

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

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

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

  4. Кнопка Удалить выбранные различия удаляет все красные или синие строки , включенные в ваш выбор (в зависимости от текущего активного листа). Кроме того, эта кнопка очищает все зеленых ячеек на обоих листах, если они включены в ваш выбор.
  5. Игнорировать выбранные различия и удалить отметки различий из выбранных ячеек 9Кнопка 0030 восстанавливает исходное форматирование для синих или красных строк , включенных в ваш выбор (в зависимости от текущего активного листа). Для зеленых ячеек кнопка восстанавливает исходное форматирование на обоих листах. После игнорирования инструмент переходит к следующему различию, если включен параметр Автопрокрутка .
  6. Undo и Redo отменить и восстановить действия описанных выше кнопок.

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

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

В нижней части панели инструментов вы можете нажать кнопку Настройки и откройте всплывающее меню с несколькими параметрами:

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

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

  • Параметры вставки здесь аналогичны встроенным параметрам вставки Excel. Вы можете использовать их для вставки и копирования синие и красные ряды и зеленые ячейки .

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

Как выйти из режима просмотра различий

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

Выйти после просмотра всех различий

По умолчанию мы предполагаем, что вы пройдете весь путь просмотра различий в своих рабочих листах, от первого до последнего. Как только вы закончите, Compare Two Sheets автоматически спросит, хотите ли вы сохранить свои книги и переключиться на обычный вид Excel.

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

Если вы абсолютно уверены, что все в порядке, нажмите OK .

Выйти из режима без просмотра всех различий

Если вы решили прекратить просмотр различий, используйте кнопку Выйти из режима просмотра различий :

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

Если что-то пойдет не так

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

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

Для этого перейдите на вкладку Review , найдите группу Protect и нажмите Unprotect Sheet :

Excel попросит вас ввести пароль. Вот оно: 90 436 ablebitscomparesheets 90 437

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

Как закрыть листы

Вы можете выйти из режима просмотра различий и закрыть листы, используя стандартную программу Excel 9.0027 Закрыть кнопка:

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

  • Сохраните и закройте книгу, сохранив отметки различий.
    Выберите этот вариант, если хотите сохранить все цвета, сделанные инструментом. Листы будут закрыты, а различия сохранены.
  • Закрыть книгу без сохранения. Отличия будут сохранены, все ваши изменения будут утеряны.
    Этот вариант имеет смысл, если вы обработали метки отличий с помощью панели инструментов (вставили строки с одного листа на другой, скопировали ячейки, удалили метки отличий или проигнорировали их), но не хотите сохранять результат своей работы. Таким образом, расцветка сохранится, но все изменения, сделанные вами с помощью панели инструментов, будут утеряны.

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