Как сравнить две колонки в эксель и найти разницу
Перейти к содержимому

Как сравнить две колонки в эксель и найти разницу

  • автор:

Сравнение двух или нескольких листов одновременно

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

Одновременный просмотр двух листов в одной книге

Вкладка

  1. На вкладке Вид в группе Окно щелкните элемент Новое окно.

в группе Окно на вкладке Вид .

Примечание: Этот параметр доступен, только если включен режим Рядом.

    Если изменить размер окон книги для оптимального просмотра, можно нажать кнопку Сбросить положение окна

Одновременный просмотр двух листов разных книг

  1. Откройте обе книги, которые содержат листы для сравнения.
  2. На вкладке Вид в группе Окно щелкните Вид рядом

Вкладка

.

в группе Окно на вкладке Вид .

Примечание: Этот параметр доступен, только если включен режим Рядом.

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

Одновременный просмотр нескольких листов

Вкладка

  1. Откройте одну или несколько книг, листы из которых требуется просмотреть одновременно.
  2. Выполните одно из указанных ниже действий.
  3. Если листы, которые нужно просмотреть, находятся в одной книге, выполните указанные ниже действия.
    1. Щелкните лист, который нужно просмотреть.
    2. На вкладке Вид в группе Окно щелкните Новое окно.

    Совет: Чтобы восстановить окно книги в полный размер, нажмите кнопку Развернуть

    в правом верхнем углу окна книги.

    Одновременный просмотр двух листов в одной книге

    Значок новой кнопки на вкладке представления на ленте

    1. На вкладке Вид в группе Окно щелкните элемент Новое окно.

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

    Способ 1. Использование формулы рабочего листа

    1. Запустите Excel.
    2. В качестве примера введите в новом листе введите следующие данные (оставьте столбец B пустым):
    A B C
    1 1 3
    2 2 5
    3 3 8
    4 4 2
    5 5 0

    Повторяющиеся числа отображаются в столбце B, как в следующем примере:

    A B C
    1 1 3
    2 2 2 5
    3 3 3 8
    4 4 2
    5 5 5 0

    Способ 2. Использование макроса Visual Basic

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

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

    1. Запустите Excel.
    2. Нажмите ALT+F11, чтобы запустить редактор Visual Basic.
    3. В меню Вставка выберите Модуль.
    4. Введите следующий код на листе модуля:

    Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub
      Введите в качестве примера следующие данные (оставьте столбец B пустым):
    A B C
    1 1 3
    2 2 5
    3 3 8
    4 4 2
    5 5 0

    Примечание: Если вы не видите вкладку Разработчик, возможно, вам понадобится включить ее. Для этого выберите Файл > Параметры > Настроить ленту, а затем выберите вкладку Разработчик в поле настройки справа.

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

    A B C
    1 1 3
    2 2 2 5
    3 3 3 8
    4 4 2
    5 5 5 0

    9 способов сравнить две таблицы в Excel и найти разницу

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

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

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

    Просмотр рядом, чтобы сравнить таблицы.

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

    Сравните 2 книги.

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

    Чтобы просмотреть два файла Эксель рядом, сделайте следующее:

    1. Откройте оба файла.
    2. Перейдите на вкладку «Вид» и нажмите кнопку «Рядом». (1) Это оно!

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

    Чтобы разделить окна по вертикали, нажмите кнопку «Упорядочить все» (3) и выберите «Рядом» (4):

    Просмотр рядом, чтобы сравнить таблицы

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

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

    Расположите рядом несколько таблиц Excel.

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

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

    сравнить рядом несколько таблиц Excel

    Чтобы просмотреть все открытые файлы одновременно, нажмите кнопку «Упорядочить все» и выберите предпочтительное расположение: мозаичное, горизонтальное, вертикальное или каскадное.

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

    Сравните два листа в одной книге.

    Иногда 2 листа, которые вы хотите сравнить, находятся в одной книге. Чтобы просмотреть их рядом, выполните следующие действия.

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

    Быстрое выделение значений, которые различаются.

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

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

    К сожалению, это нормально работает только для сравнения 2 столбцов (или строк), а не всей таблицы целиком. Кроме того, строки должны быть одинаковым образом отсортированы, поскольку ячейки сравниваются построчно. Если у вас товары отсортированы по-разному, либо вообще различный ассортимент, то никакой пользы от этого метода не будет.

    Формула сравнения.

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

    Простейший вариант – сопоставление двух таблиц, находящихся на одном листе. Можно соотносить как числовые, так и текстовые значения, всего-навсего прописав в одной из соседних ячеек формулу их равенства. В результате при тождестве ячеек мы получим сообщение ИСТИНА, в противном случае — ЛОЖЬ.

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

    Результатом будет являться либо ИСТИНА (в случае совпадения), либо ЛОЖЬ (при отрицательном результате).

    сравнить таблицы при помощи формул

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

    Если ваши таблицы достаточно велики, то довольно утомительно будет просматривать колонку I на предмет поиска слова ЛОЖЬ. Поэтому может быть полезным сразу определить — а есть ли вообще несовпадения?

    Можно подсчитать общее количество расхождений при помощи формулы СУММПРОИЗВ и сразу вывести это число где-нибудь отдельно.

    или можно сделать это формулой массива

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

    Как произвести сравнение на отдельном листе.

    Чтобы сравнить два листа Эксель на предмет различий, просто откройте новый пустой лист, введите формулу ЕСЛИ в ячейку A1, а затем скопируйте ее вниз и вправо, перетащив маркер заполнения:

    Поскольку мы используем относительные ссылки на ячейки, формула будет меняться в зависимости от расположения столбца и строки. В результате формула в A1 будет сравнивать ячейки A1 в Лист1 и Лист2, формула в B1 будет сравнивать ячейку B1 на обоих листах и ​​так далее. Результат будет выглядеть примерно так:

    сравнение двух таблиц при помощи формул на отдельном листе

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

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

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

    Как сравнить две таблицы при помощи формулы ВПР.

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

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

    Для наглядности расположим обе таблицы на одном листе.

    сравнение таблиц при помощи формулы ВПР

    берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

    Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «».

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

    Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —

    а в K3 – его новая цена:

    Ну а далее в L3 просто найдем разность K3-J3.

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

    И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.

    В ячейке B2 запишем формулу

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

    Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными —

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

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

    Выделение различий между таблицами цветом.

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

    • На листе, где вы хотите выделить различия, выберите все используемые ячейки. Для этого щелкните верхнюю левую ячейку используемого диапазона, обычно A1, и нажмите Ctrl + Shift + End , чтобы расширить выделение до последней использованной ячейки.
    • На вкладке Главная кликните Условное форматирование >Новое правило и создайте его со следующей формулой:

    Где Лист2 — это имя другого листа, который вы сравниваете с текущим.

    В результате ячейки с разными значениями будут выделены выбранным вами цветом:

    как сравнить таблицы и выделить различия цветом

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

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

    Хороший вариант сравнения — объединить таблицы в единую сводную, и там уже сопоставлять данные между собой.

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

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

    Поместим поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений.

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

    сравнение таблиц при помощи новой сводной таблицы

    Сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайсов и сортирует их по алфавиту. Причём, без повторов. У новых товаров нет старой цены, у удаленных товаров — новой цены. Легко увидеть изменения цен, если таковые были.

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

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

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

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

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

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

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

    Загрузите первый прайс в Power Query с помощью кнопки Из таблицы/диапазона на вкладке Данные.

    После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в.

    В появившемся затем окне выбираем «Только создать подключение».

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

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

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

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

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

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

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

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

    Теперь осталось вернуться на вкладку Главная и нажать Закрыть и загрузить.

    Получаем новый лист в нашей рабочей книге:

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

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

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

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

    • Они находят различия только в значениях, но не могут сравнивать формулы или форматирование ячеек.
    • Многие из них не могут идентифицировать добавленные или удаленные строки и столбцы. Как только вы добавите или удалите строку / столбец на одном листе, все последующие строки / столбцы будут отмечены как отличия.
    • Они хорошо работают на уровне листа, но не могут обнаруживать структурные различия на уровне книги Excel, к примеру добавление и удаление листов.

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

    Как сравнить таблицы при помощи Ultimat Suite для Excel

    Последняя версия Ultimate Suite включает более 60 новых функций и улучшений, самым интересным из которых является «Сравнение таблиц» — инструмент для сравнения листов или диапазонов данных в Excel.

    Чтобы сделать сравнение более интуитивным и удобным, надстройка разработана следующим образом:

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

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

    1. Нажмите кнопку «Сравнить листы (Compare Two Sheets)» на вкладке «Данные Ablebits » в группе « Объединить »:
    1. Появится окно мастера с предложением выбрать два листа, которые вы хотите сравнить на предмет различий.

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

    сравнить таблицы при помощи Ultimat Suite для Excel

    1. На следующем шаге вы выбираете алгоритм сравнения:
      • Без ключевых столбцов (по умолчанию) — лучше всего подходит для сложных документов, таких как счета-фактуры или контракты.
      • По ключевым столбцам — подходит для таблиц, организованных по столбцам, которые имеют один или несколько уникальных идентификаторов, таких как номера заказов или артикулы товаров.
      • По ячейке — лучше всего использовать для сравнения таблиц с одинаковым макетом и размером, таких как балансы или статистические отчеты.

    Надстройка Compare Sheets для сравнения таблиц

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

    На этом же шаге вы можете выбрать предпочтительный тип соответствия:

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

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

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

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

    1. Нажмите кнопку «Сравнить (Compare)» и подождите немного, пока программа обработает ваши данные и создаст их резервные копии. Резервные копии всегда создаются автоматически, поэтому вы можете не беспокоиться о сохранности своих данных.

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

    На скриншоте выше различия выделены цветами по умолчанию:

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

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

    После этого мы видим немного другой результат сравнения:

    Как видите, основным здесь действительно является факт совпадения значений в столбцах B. Строки, в которых нет такого совпадения, сразу выделяются красным или фиолетовым. А вот если совпадение есть, тогда идем в столбец С и сравниваем записанную там цену. Зелёные ячейки как раз и показывают нам товары, которые имеются в обоих прайс-листах, но цена на них изменилась.

    Не знаю как вам, но мне второй вариант представляется более информативным.

    А что же дальше делать с этим сравнением?

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

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

    • Сохраните внесенные вами изменения и сохраните оставшиеся различия (Save workbooks and keep difference marks),
    • Сохраните внесенные вами изменения и удалите оставшиеся различия (Save workbooks and remove difference marks),
    • Восстановите исходные книги из резервных копий (Restore workbooks from backup copies).

    Вот как вы можете сравнить два листа в Excel при помощи инструмента сравнения Compare Two Sheets (надеюсь, он вам понравился 🙂

    Как сравнить два столбца в Excel на совпадения и различия

    На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные совпадения, различия либо уникальные значения. Итак, обратный отсчет начался!

    Мы все время от времени делаем сравнение данных в Excel. Microsoft Excel предлагает ряд опций для сопоставления данных, но большинство из них ориентированы на поиск в одной колонке. Встроенный инструмент удаления дубликатов, доступный в Excel 2019-2010, не может справиться с этой задачей, поскольку он не умеет сравнивать данные между двумя столбиками. Кроме того, он может только удалять дубликаты. Других возможностей — таких как выделение или раскраска, увы, нет :-(.

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

    • Сравнение в двух столбцах построчно
      • 1. Проверяем совпадения или различия в одной строке.
      • 2. Сравниваем построчно с учетом регистра.
      • Пример 1. Ищем полное совпадение в каждой строке
      • Пример 2. Найдите хотя бы 2 совпадения в нескольких столбцах
      • 1. Выделяем построчно
      • 2. Выделите уникальные записи в каждом столбце
      • 3. Выделите дубликаты в 2 столбцах.
      • 1. Как выделить дубликаты
      • 2. Как выделить различия.

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

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

      1. Проверяем совпадения или различия в одной строке.

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

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

      Чтобы найти в соответствующей строке позиции с одинаковым содержимым, A2 и B2 в этом примере, запишите:

      Чтобы найти позиции в одной строке с разным содержимым, просто замените » wp-block-quote is-layout-flow wp-block-quote-is-layout-flow»>

      =ЕСЛИ(A2<>B2; «НЕ совпадает»;””)

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

      Результат может выглядеть примерно так:

      построчно сравнить столбцы при помощи функции ЕСЛИ

      Как видите, одинаково хорошо обрабатываются числа, даты, время и текст.

      2. Сравниваем построчно с учетом регистра.

      Как вы, наверное, заметили, формулы из предыдущего примера игнорируют регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если вы хотите сопоставить значения с учетом регистра, используйте функцию СОВПАД (EXACT в английской версии):

      построчное сравнение столбцов с учетом регистра символов

      Чтобы найти различия с учетом регистра в одной строке, введите соответствующий текст («Уникальный» например) в третий аргумент функции ЕСЛИ:

      Сравните несколько столбцов построчно

      Мы можем ставить перед собой следующие цели:

      • Найти строки с одинаковыми значениями во всех из них.
      • Найти строки с одинаковыми значениями в любых двух.

      Пример 1. Ищем полное совпадение в каждой строке

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

      как сравнить несколько столбцов построчно

      Если в вашей таблице очень много колонок, более элегантным решением будет использование функции СЧЁТЕСЛИ :

      где 3 — количество сравниваемых колонок.

      Или можно использовать —

      Пример 2. Найдите хотя бы 2 совпадения в нескольких столбцах

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

      сравнить столбцы и найти совпадения хотя бы в двух из них

      Если имеется много данных, ваша конструкция с оператором ИЛИ может стать слишком громоздкой. В этом случае лучшим решением было бы добавить несколько функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько раз текущее значение из первой колонки встречается во всех данных, находящихся правее него, второй СЧЁТЕСЛИ определяет то же самое для значения из второй колонки, и так далее. Если счетчик равен 0, возвращается надпись «Все уникальные», в противном случае — «Найдены одинаковые». Например:

      =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0;»Все уникальные»;»Найдены одинаковые»)

      Могу предложить также более компактный вариант выявления совпадений — формула массива:

      Попробуйте — получите тот же результат. Также не забудьте нажать Ctrl + Shift + Enter, чтобы ввести всё правильно.

      Сравниваем значения столбцов целиком

      Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, но их нет в B. То есть, исходные данные из А мы сравниваем с В.

      Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B;$A2)=0 в логический тест ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).

      Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ выполняет поиск значения из A2 по всему столбцу B. Если совпадений не найдено, возвращается «Нет совпадений в B», в противном случае — пустую строку:

      сравнить два столбца в Excel на совпадения и различия

      Примечание. Если ваша таблица имеет фиксированное количество строк, вы можете указать определенный диапазон (например, $B2:$B20), а не целиком $B:$B, чтобы программа работала быстрее с большими наборами данных.

      Тот же результат может быть достигнут при использовании функции ЕСЛИ всесте с ЕОШИБКА и ПОИСКПОЗ:

      =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ($A2;$B$2:$B$10;0));»Уникальное»; » Найдено в B»)

      Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):

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

      =ЕСЛИ(СЧЁТЕСЛИ($B:$B; $A2)=0; «Уникальное»; «Дубликат»)

      Думаю, вы понимаете, что точно таким же образом можно наоборот сравнивать В с А.

      Сравниваем списки в Excel и получаем совпадающие данные

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

      Кроме того, в отдельной статье мы подробно рассмотрели 4 способа сравнить таблицы при помощи формулы ВПР.

      В качестве альтернативы вы можете использовать более мощную и универсальную комбинацию ИНДЕКС и ПОИСКПОЗ.

      Например, следующее выражение сравнивает названия продуктов в колонках D и A, и если совпадение найдено, соответствующая цифра продаж извлекается из B. Если совпадения не найдено, возвращается ошибка #Н/Д.

      сравнить два столбца в Excel и извлечь совпадающие данные

      Сообщение об ошибке в таблице выглядит не слишком красиво. Поэтому обработаем это выражение при помощи ЕОШИБКА:

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

      Как выделить совпадения и несовпадения в 2 столбцах.

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

      1. Выделяем построчно

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

      Как выделить совпадения и различия в 2 столбцах при помощи условного форматирования

      • Выберите область, в которой вы хотите выделить.
      • Щелкните Условное форматирование> Новое правило…> Используйте формулу.
      • Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок таблицы). Пожалуйста, дважды проверьте, что вы используете относительную ссылку на строку (без знака $), как записано выше.

      Чтобы выделить различия между колонками A и B, создайте правило с формулой =$B2<>$A2

      Если вы новичок в условном форматировании Excel, смотрите пошаговые инструкции в статье Как закрасить строку или столбец по условию.

      2. Выделите уникальные записи в каждом столбце

      Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:

      • Предметы только в первом списке (уникальные)
      • Предметы только во втором списке (уникальные)
      • Элементы, которые есть в обоих списках (дубликаты).

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

      Предположим, что ваш список 1 находится в колонке A (A2:A8), а список 2 — в колонке C (C2:C8). Вы создаете правила условного форматирования с помощью следующих формул:

      Выделите уникальные значения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)=0

      Выделите уникальные значения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)=0

      И получите следующий результат:

      сравнить 2 столбца и выделить уникальные записи в каждом столбце

      3. Выделите дубликаты в 2 столбцах.

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

      сравнить 2 столбца и выделить цветом уникальные значения

      Вновь используем условное форматирование при помощи формулы.

      Выделите совпадения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)>0

      Выделите совпадения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)>0

      Выделите цветом одинаковые и различные значения в нескольких столбцах

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

      1. Как выделить дубликаты

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

      Где A2, B2 и C2 — самые верхние в вашем диапазоне, а 3 — количество колонок для сопоставления между собой.

      Выделить цветом различия и совпадения в нескольких столбцах

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

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

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

      2. Как выделить различия.

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

      1. Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.

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

      Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если нужно перемещаться снизу вверх, то нажмите и удерживайте SHIFT, и вновь используйте ТАВ — будете двигаться не вниз, а вверх. Вы увидите, как ваше белое пятно перемещается, и соответственно изменяется активный столбец.

      Примечание. Чтобы выбрать несмежные столбцы, выберите первый диапазон, нажмите и удерживайте CTRL, а затем выбирайте дальше. Активная ячейка будет находиться в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу TAB или Enter, как описано выше.

      1. На вкладке «Главная» нажмите «Найти и выделить» > « Выделить группу ячеек». Затем выберите «Отличия по строкам» и нажмите «ОК» .
      1. Позиции, значения которых отличаются от ячеек сравнения в каждой строке, выделяются. Если вы хотите закрасить выделенные ячейки каким-либо цветом, просто щелкните значок «Цвет заливки» на ленте и выберите нужный цвет.

      Как сопоставить два значения в отдельных столбцах.

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

      Например, чтобы сравнить ячейки A1 и C1, можно использовать:

      Для совпадений: =ЕСЛИ(A1=C1; «Совпадает»; «»)

      Для различий: =ЕСЛИ(A1<>C1; «Уникальные»; «»)

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

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

      • Использование функции ЕСЛИ в Excel
      • Функция ЕСЛИ: проверяем условия с текстом

      Быстрый способ сравнения двух столбцов или списков без формул.

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

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

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

      Для начала рассмотрим самый простой случай – сравним два отдельных столбца.

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

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

      Инструмент Compare Tables для сравнения столбцов

      На втором шаге выбираем второй столбец для сравнения.

      На третьем шаге нужно указать, что именно мы ищем – дубликаты либо уникальные значения.

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

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

      Ячейки списка 1, дубликаты которых имеются в списке 2, будут закрашены цветом.

      А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:

      Не закрашенные цветом ячейки содержат уникальные значения. Красиво и наглядно.

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

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

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

      И вот результат. Несовпадающие строки закрашены цветом.

      Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *