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