Возможно, каждый, кто работает с данными в Excel, сталкивается с вопросом, как сравнить два столбца в Excel по совпадениям и различиям. Есть несколько способов сделать это. Рассмотрим подробнее каждую из них.
Как сравнить два столбца в Excel по строкам
При сравнении двух столбцов с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Мы можем провести этот анализ с помощью функции ЕСЛИ. Посмотрим, как это работает, на следующих примерах.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Чтобы сравнить данные в каждой строке из двух столбцов в Excel, давайте напишем простую формулу ЕСЛИ. Формулу необходимо вводить в каждой строке соседнего столбца, рядом с таблицей, в которой расположены основные данные. После создания формулы для первой строки таблицы мы можем растянуть / скопировать ее в остальные строки.
Чтобы проверить, содержат ли два столбца одной строки одинаковые данные, нам понадобится формула:
= ЕСЛИ (LA2 = SI2, «Соответствие”; “”)
Формула, определяющая различия между данными двух столбцов в строке, будет выглядеть так:
= ЕСЛИ (LA2 SI2, «Не совпадает”; “”)
Мы можем адаптировать проверку совпадений и различий между двумя столбцами в строке в одной формуле:
= ЕСЛИ (LA2 = SI2, «Совпадение», «Несоответствие”)
или
= ЕСЛИ (LA2 B2, «Не совпадает», «Совпадает”)
Пример результата расчета может выглядеть так:
Чтобы сравнить данные в двух столбцах одной строки с учетом регистра, используйте формулу:
= ЕСЛИ (ПОИСКПОЗ (A2; B2); «Совпадение»; «Уникально”)
Как сравнить несколько столбцов на совпадения в одной строке Excel
В Excel есть возможность сравнивать данные в нескольких столбцах одной строки в соответствии со следующими критериями:
- Найдите строки с одинаковыми значениями во всех столбцах таблицы;
- Найдите строки с одинаковыми значениями в любых двух столбцах таблицы;
Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы
Представим, что наша таблица состоит из нескольких столбцов данных. Наша задача — найти строки, в которых значения одинаковы во всех столбцах. В этом нам помогут функции ЕСЛИ и И в Excel. Формула определения совпадений будет следующей:
= ЕСЛИ (И (LA2 = B2; LA2 = C2), «Игра”; ” “)
Если в нашей таблице много столбцов, будет проще использовать функцию СЧЁТЕСЛИ в сочетании с ЕСЛИ:
= ЕСЛИ (СЧЁТЕСЛИ ($ A2: $ C2; $ A2) = 3, «Соответствие”;” “)
В формуле «5» — это количество столбцов в таблице, для которых мы создали формулу. Если в вашей таблице больше или меньше столбцов, это значение должно равняться количеству столбцов.
Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы
Представим, что наша задача — определить из таблицы с данными в нескольких столбцах те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции ЕСЛИ и ИЛИ. Напишем формулу для таблицы, состоящей из трех столбцов данных:
= ЕСЛИ (ИЛИ (LA2 = B2; B2 = C2; A2 = C2), «Соответствие”;” “)
В случаях, когда в нашей таблице слишком много столбцов, наша формула с функцией ИЛИ будет очень большой, поскольку в ее параметрах мы должны указать критерии соответствия между каждым столбцом таблицы. В этом случае проще использовать функцию СЧЁТЕСЛИ.
= ЕСЛИ (СЧЁТЕСЛИ (B2: D2; A2) + СЧЁТЕСЛИ (C2: D2; B2) + (C2 = D2) = 0; «уникальная строка»; «не уникальная строка”)
Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке A2, вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением в ячейке B2. Если результатом вычисления является «0», это означает, что в каждой ячейке, в каждом столбце, эта строка содержит уникальные значения. В этом случае формула вернет результат «Уникальная строка», в противном случае — «Неуникальная строка”.
Как сравнить два столбца в Excel на совпадения
Представим, что наша таблица состоит из двух столбцов данных. Нам нужно определить повторяющиеся значения в первом и втором столбцах. Функции ЕСЛИ и СЧЁТЕСЛИ помогут нам решить проблему.
= IF (COUNTIF ($ B: $ B; $ A5) = 0; «Нет совпадений в столбце B»; «В столбце B есть совпадения”)
Эта формула проверяет значения в столбце B, чтобы узнать, соответствуют ли они данным в ячейках столбца A.
Если ваша таблица имеет фиксированное количество строк, вы можете указать в формуле четкий диапазон (например, $ B2: $ B10). Это ускорит формулу.
Как сравнить два столбца в Excel на совпадения и выделить цветом
При поиске совпадений между двумя столбцами в Excel вам может потребоваться увидеть совпадения или различия, обнаруженные в данных, например, с помощью цветного выделения. Самый простой способ выделить совпадения и различия цветом — использовать «Условное форматирование» в Excel. Давайте посмотрим, как это сделать, на следующих примерах.
Поиск и выделение совпадений цветом в нескольких столбцах в Эксель
В случаях, когда нам нужно найти совпадения в нескольких столбцах, для этого нам понадобятся:
- Выберите столбцы с данными, в которых вы хотите посчитать совпадения;
- На вкладке «Главная» панели инструментов щелкните пункт меню «Условное форматирование» -> «Правила выбора ячеек» -> «Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите пункт «Дублировать» в раскрывающемся списке слева, в раскрывающемся списке справа выберите, каким цветом будут выделяться повторяющиеся значения. Нажмите кнопку «ОК”:
- Впоследствии в выбранном столбце будет выделен соответствующий цвет:
Поиск и выделение цветом совпадающих строк в Excel
Поиск совпадающих ячеек с данными в двух или более столбцах и поиск совпадений для целых строк с данными — это разные концепции. Обратите внимание на следующие две таблицы:
Таблицы выше содержат те же данные. Их отличие в том, что в примере слева мы искали совпадающие ячейки, а справа мы нашли целые строки повторяющихся данных.
Давайте посмотрим, как найти совпадающие строки в таблице:
- Справа от таблицы с данными создаем вспомогательный столбец, в котором перед каждой строкой с данными помещаем формулу, объединяющую все значения строки таблицы в одну ячейку:
= A2, B2, C2 и D2
Во вспомогательном столбце вы увидите данные объединенной таблицы:
Теперь, чтобы определить соответствующие строки в таблице, выполните следующие действия:
- Выделите область с данными во вспомогательном столбце (в нашем примере это диапазон ячеек E2: E15);
- На вкладке «Главная» панели инструментов щелкните пункт меню «Условное форматирование» -> «Правила выбора ячеек» -> «Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите «Дублировать» в раскрывающемся списке слева, в раскрывающемся списке справа выберите цвет, которым будут выделяться повторяющиеся значения. Нажмите кнопку «ОК”:
- После этого в выбранном столбце будут выделены повторяющиеся строки:
В приведенном выше примере мы выбрали строки в созданном вспомогательном столбце.
Но что, если нам нужно выделить строки не во вспомогательном столбце, а сами строки в таблице данных?
Для этого поступаем следующим образом:
- Как и в примере выше, создайте вспомогательный столбец, в каждой строке которого мы вводим следующую формулу:
= A2, B2, C2 и D2
Таким образом, мы получим собранные данные всей строки таблицы в ячейке:
- Теперь выделим все данные в таблице (кроме вспомогательного столбца). В нашем случае это ячейки диапазона A2: D15;
- Затем на вкладке «Главная» панели инструментов нажмите «Условное форматирование» -> «Создать правило”:
- В диалоговом окне «Новое правило форматирования» щелкните элемент «Использовать формулу для определения форматированных ячеек» и в поле «Значения формата, для которых истинна следующая формула» вставьте формулу:
= СЧЁТЕСЛИ ($ E $ 2: $ E $ 15, $ E2)> 1
- Не забудьте установить формат найденных повторяющихся строк.
Эта формула проверяет диапазон данных в вспомогательном столбце и выделяет повторяющиеся строки в таблице, если есть повторяющиеся строки:
