Топ-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

 

Комментариев: 45
  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

  14. Сергей

    Владислав, здравствуйте!!! У меня есть два файла с ФИО.
    В одном 5000 записей, в другом 600. Возможно ли сравнить и выбрать одинаковых людей и записать в другую ячейку.

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

      Сергей, добрый день, это возможно. Пришлите, пожалуйста, ваш пример на почту info@excelhack.ru

  15. Alex

    Здравствуйте!
    У кого не срабатывают формулы – примеры приведенные в данной статье: если копируете формулы через буфер, после вставки в Excel вручную меняйте двойные кавычки (чтобы получились обычные, без наклона) :smile:
    Автору спасибо!

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

      Александр, спасибо за ваш комментарий ;-)

  16. Дмитрий

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

    Не работает ИМЯ

  17. Чина

    Как сравнить два столбца на совпадения? Например: если в столбце А есть записи со столбца Б тогда удалить запись со столбца А.

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

      Это можно делать вручную

  18. Настя

    Добрый день!
    Можно ли сравнить колонки на совпадение если:
    колонки разные по размеру, количество строк разное.
    каждая колонка состоит из формулы СЦЕПКА (КОД И ДАТА)

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

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

  19. Abas

    я не нашел ответа на свой вопрос. у меня таблица:

    Сергей Иван Игорь
    Михаил Леонид Николай
    Антон Игорь Дмитрий
    Василий Иван Георгий
    Игорь Антон Клавдий

    необходимо определить, если ли такое значение, которое повторяется во всех столбцах, подстветить его или как-то ещё обозначить. (в помем примере Игорь) как это сделать?

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

      Добрый день. Пришлите, пожалуйста, пример вашего файла на почту info@excelhack.ru

  20. Валерий

    Есть две таблицы:
    первая ФИО (все раздельно по столбцам) + Д.Р. + столбцы с некоторыми разными данными
    Вторая ФИО (все раздельно по столбцам) + Д.Р. + столбцы в которые надо выбрать данные из таблицы 1.

    В первой таблице много строк, встречаются повторные записи на одного и того же , а также встречаются однофамильцы.
    Надо в таблицу два заполнить колонку сколько раз встречается Ф+И+О (не сравнивая Д.Р.)
    Надо вписать в таблицу 2 значение одного из параметров таблицы 1 по найденному полному совпадению Ф+И+О+Д.Р.

    Буду очень признателен если кто то отзовется!!!
    ;-)

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

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

  21. Ольга

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

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

      Здравствуйте, Ольга. Как это сделать описано в статье выше. Если есть трудности и нужна помощь – напишите на почту info@excelhack.ru поможем

  22. Евгений

    Подскажите пожалуйста, а как выявить соответствия в двух столбах, если количество строк в них не совпадает?

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

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

  23. Елена

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

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

      Здравствуйте, пришлите, пожалуйста, пример файла на почту info@excelhack.ru

  24. Дастан

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

  25. Александр

    Добрый день .Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99

    4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
    А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в каторых совпадают 4 цифры и более?
    За раннее спасибо!

  26. Александра Паук

    Добрый день!
    Помогите, пожалуйста. Есть 5 столбцов: Первый, 2 Сфера обращения (для вывода итога), Второй, TR, Сфера обращения.
    Во Втором 400 строк, в Первом около 2500. Например, 4 строки: 1,2,3,4.

    Если в столбце “Второй” строка “1” совпадает с одной из строк столбца “Первый”,
    то в столбец “2 Сфера обращения” вывести содержимое из столбца “Сфера обращения” напротив той строки столбца “Второй” , где совпадает.

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

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

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

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