Топ-100

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

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

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

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

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

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

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

=ЕСЛИ(A2=B2; “Совпадают”; “”)

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

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

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

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

или

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

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

Поиск различий в двух столбцах Excel

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

=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)

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

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

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

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

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

=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)

Поиск различий в двух столбцах Excel

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

=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

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

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

=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)

Поиск различий в двух столбцах Excel

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2, вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2. Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

Поиск различий в двух столбцах Excel

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

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Поиск различий в двух столбцах Excel

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

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

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

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

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

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

  • Выделить столбцы с данными, в которых нужно вычислить совпадения;
  • На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
  • После этого в выделенной колонке будут подсвечены цветом совпадения:

поиск и выделение цветом повторяющихся значений в Excel

Поиск и выделение цветом совпадающих строк в Excel

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

поиск и подсветка дублирующихся строк в Эксель - 1         поиск и подсветка дублирующихся строк в Эксель

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

Рассмотрим как найти совпадающие строки в таблице:

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

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

вспомогательная колонка для поиска дублирующихся строк в Excel

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

  • Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15);
  • На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
  • После этого в выделенной колонке будут подсвечены дублирующиеся строки:

поиск дубликатов строк в Эксель

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

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

Для этого сделаем следующее:

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

=A2&B2&C2&D2

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

вспомогательная колонка для поиска дублирующихся строк в Excel

  • Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
  • Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

создать правило условия форматирования в excel

  • В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

новая формула в условном форматировании

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

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

дубликаты строк в excel

 

Комментариев: 23
  1. Бек

    Спасибо очень помогли

  2. Саня

    :idea:

  3. Сергей

    Спасибо большое! Все объяснено понятно, влет получилось решить свою проблему. :idea:

  4. Сергей

    Добрый день! В первом примере вместо ЕСЛИ можно использовать формулу “=A2=B2”. Значение ИСТИНА покажет совпадение, ЛОЖЬ – несовпадение.

    Функцию СЧЁТЕСЛИ стоит использовать только на небольших таблицах, на больших она жутко тормозит.Лучше взять связку ИНДЕКС и ПОИСКПОЗ.

    1. Владислав Каманин
      Владислав Каманин

      Сергей, добрый день!

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

  5. Роман

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

    1. Владислав Каманин
      Владислав Каманин

      Добрый день, Роман, создайте вспомогательный столбец и подстройте формулу =ЕСЛИ(И(G9=H9;I9=H9;I9=G9);1;0) под свои ячейки. “1” будет означать что данные в трех столбцах совпадают, “0” – если разные.

  6. Александр

    Добрый день…
    У меня есть три книги в одном документе. В каждой книге есть столбец с данным (числа).
    Мне нужно выяснить есть ли совпадения чисел столбца первой книги, с аналогичными данными во второй и третей книге….. Если данные совпали с первой книгой, то их нужно выделить цветом….. Как лучше это сделать…..?

    1. Владислав Каманин
      Владислав Каманин

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

      Я могу помочь вам с выполнением задачи – напишите, пожалуйста мне в форму чата на сайте в правом нижнем углу =>

  7. Борис

    Добрый день.
    У меня два документа Excel. В обоих документах есть столбцы “номера помещений” и “площадь” (помещения). Как сравнить площади по номерам помещений и указать в одном из документов измененную площадь, рядом с начальной?

    1. Владислав Каманин
      Владислав Каманин

      Здравствуйте, Борис.
      Я бы сделал так:
      1. В том файле, с которым я хочу сравнить создал вспомогательный столбец, в который с помощью функции ВПР “подтянул” к номеру помещений площадь из второго файла.
      2. Создал еще один вспомогательный столбец, в котором с помощью формулы отразил разницу между значениями двух площадей по номеру помещения.

  8. Владимир

    здравствуйте, необходимо сверить 2 колонки с кбк в котором 20 символов.
    где- до 14 символа сверяет а если 20 символов и отличается только 1 последний эксель ставит как совпадение:
    | 09220225519050000151|09220225519050000150|
    хотя они различаются, подскажите как сравнить?

    1. Владислав Каманин
      Владислав Каманин

      Здравствуйте, Владимир. Подскажите, кбк у вас состоит только из чисел или из букв, в том числе?

  9. Марина

    Добрый день.
    Подскажите, пожалуйста.
    Мне необходимо сравнить соседние столбцы на частичное совпадение.
    Например,
    в столбце C – Уплотнение 12/28/6 D25413K
    в столбце E – D25413K TYPE 1
    Можно ли сделать так, чтобы эти ячейки подсветились, т.к. в обеих содержится одинаковая информация (D25413K) ?

    1. Владислав Каманин
      Владислав Каманин

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

  10. Ольга

    Здравствуйте!
    Можно ли сравнить данные в одном столбце с данными в другом столбце, но не построчно, а в целом?
    Например, в первом столбце: Иванов, Петров, Сидоров сравнить с данными во втором: Федоров, Жуков, Иванов.

    1. Владислав Каманин
      Владислав Каманин

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

  11. Юлия

    Добрый день! Мне необходимо сравнить значения в двух столбцах, где построчно данные не совпадают. Количество строк разное (например в столбце А строк на 2 меньше, чем в столбце В) и вот именно эти 2 ячейки, которых нет в столбце А мне и нужно найти и желательно выделить как-то либо содержимое совпадающих ячеек, либо содержимое тех ячеек, которые есть в столбце В (и которых, соответственно нет в столбце А). Содержимое ячеек – числовое (после запятой 2 знака).
    Спасибо за ответ!

    1. Владислав Каманин
      Владислав Каманин

      Здравствуйте, Юлия, пробовали ли вы использовать подходы указанные в статье?
      Если не получается выполнить работу собственными силами, напишите, пожалуйста на адрес info@excelhack.ru

  12. Владимир

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

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

    1. Владислав Каманин
      Владислав Каманин

      Владимир, добрый день!

      Будет проще разобраться с тем, как вам помочь, если вы пришлете файл с примером и описанием задачи на почту info@excelhack.ru

  13. Иван

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

    1. Владислав Каманин
      Владислав Каманин

      Иван, добрый день!

      Будет проще разобраться с тем, как вам помочь, если вы пришлете файл с примером и описанием задачи на почту info@excelhack.ru

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

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Курс "Сводные таблицы в Excel"

всего за 990 рублей !