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

Автор: | 06.05.2022

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

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

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

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

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

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

= ЕСЛИ (LA2 ​​= SI2, «Соответствие”; “”)

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

= ЕСЛИ (LA2 ​​ SI2, «Не совпадает”; “”)

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

= ЕСЛИ (LA2 ​​= SI2, «Совпадение», «Несоответствие”)

или

= ЕСЛИ (LA2 ​​ B2, «Не совпадает», «Совпадает”)

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

Найдите различия в двух столбцах Excel

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

= ЕСЛИ (ПОИСКПОЗ (A2; B2); «Совпадение»; «Уникально”)

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

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

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

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

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

= ЕСЛИ (И (LA2 ​​= B2; LA2 = C2), «Игра”; ” “)

Найдите различия в двух столбцах Excel

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

= ЕСЛИ (СЧЁТЕСЛИ ($ A2: $ C2; $ A2) = 3, «Соответствие”;” “)

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

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

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

= ЕСЛИ (ИЛИ (LA2 ​​= B2; B2 = C2; A2 = C2), «Соответствие”;” “)

Найдите различия в двух столбцах Excel

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

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

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

Найдите различия в двух столбцах Excel

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

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

= IF (COUNTIF ($ B: $ B; $ A5) = 0; «Нет совпадений в столбце B»; «В столбце B есть совпадения”)

Найдите различия в двух столбцах Excel

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

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

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

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

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

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

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

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

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

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

искать и выделять повторяющиеся строки в Excel - 1                    искать и выделять повторяющиеся строки в Excel

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

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

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

= A2, B2, C2 и D2

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

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

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

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

найти повторяющиеся строки в Excel

В приведенном выше примере мы выбрали строки в созданном вспомогательном столбце.

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

Для этого поступаем следующим образом:

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

= A2, B2, C2 и D2

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

 

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

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

  • В диалоговом окне «Новое правило форматирования» щелкните элемент «Использовать формулу для определения форматированных ячеек» и в поле «Значения формата, для которых истинна следующая формула» вставьте формулу:

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

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

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

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

повторяющиеся строки в Excel

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

Ваш адрес email не будет опубликован.