Как в Excel сравнить два столбца на совпадение элементов
Содержание
При работе с таблицами у пользователей может возникнуть необходимость сравнения значений в двух колонках. Выполнить проверку на наличие совпадений в MS Excel можно несколькими способами. У каждого из них есть свои особенности, а потому стоит ознакомиться со всеми доступными методами. Из этой статьи вы получите подробную информацию о том, как сравнить два столбца в Excel, используя для этого только стандартные средства самой программы.
Выделение дубликатов через «Условное форматирование»
Инструмент для форматирования ячеек по заданным правилам — очень удобное средство для поиска дубликатов либо уникальных значений в диапазонах внутри одного документа или в разных таблицах. С помощью этой функции можно легко выполнить сравнение двух столбцов на наличие совпадений. При этом не придется задействовать вспомогательные ячейки, вставлять формулы или совершать какие-либо иные манипуляции, способные отразиться на структуре таблицы.
Рассмотрим, как пользоваться данным инструментом на примере двух таблиц. По условию задачи, необходимо сравнить данные в столбцах «C» и «G», в которых содержатся числовые значения и найти различия между ними. Чтобы выполнить поставленную задачу, следуем инструкции:
- Сначала выделяем 2 столбца, которые нужно подвергнуть сравнительной проверке. Для этого сначала выделите первый диапазон ячеек, затем зажмите и выделите вторую колонку с данными.
Выделяем столбцы для сравнения - Выделив нужные столбцы в Эксель, переходим во вкладку «Главная». Далее в Панели инструментов нажимаем «Условное форматирование». В выпадающем окне выбираем «Правила выделения ячеек» и в следующем окне нажимаем «Повторяющиеся значения».
Переходим к условному форматированию - Далее нужно выбрать способ форматирования. Можно выделить все одинаковые значения или, наоборот, подвергнуть форматированию только уникальные ячейки.
Настраиваем форматирование
Программа сравнивает выделенные пользователем диапазоны ячеек и выполняет форматирование одинаковых либо уникальных значений. Основным способом форматирования при использовании этого инструмента выступает изменение цвета заливки ячеек в столбцах. Содержащиеся в них данные при этом не подвергаются каким-либо изменениям. Поэтому они остаются доступными для последующей визуализации, подстановки в формулы или иные вычислительные конструкции.
Отметим, что пользователи Excel могут создавать собственные правила форматирования. Это нужно для того, чтобы оформлять ячейки, соответствующие одному или нескольким нестандартным критериям. В случае с проверкой на совпадения такой необходимости нет. В программе уже предусмотрено правило для нахождения и выделения клеток, данные в которых отличаются или совпадают, в зависимости от того, какой вариант форматирования выбрал пользователь.
Сравнение столбцов с помощью группирования ячеек
Помимо условного форматирования есть еще один способ, позволяющий сравнить два столбца в Excel на совпадения без применения формул. Речь идет об инструменте для выделения сгруппированных ячеек. Этот способ, в отличие от предыдущего, не предусматривает изменение внешнего вида фрагментов колонок, в которых в процессе сопоставления найдены отличия. Ячейки, для которых программа выявила наличие несовпадений, просто выделяются, и при необходимости их можно отредактировать, выделить цветом, удалить или изменить каким-либо иным образом.
Рассмотрим детально, как выделять группы ячеек во время сравнения двух столбцов:
- Выделяем колонки, которые необходимо сопоставить и найти в них различия. В нашем случае это столбцы C и G.
Выделяем столбцы - Во вкладке «Главная» в правой части Панели инструментов нажмите «Найти и выделить». В выпадающем окне выберите команду «Выделить группу ячеек».
Нажимаем выделить группу ячеек - Далее нужно задать параметр для выделения группы ячеек. В нашем случае нужно найти разницу в двух столбцах Excel с данными. Поэтому выбираем параметр «Отличия по строкам».
Нажимаем отличия по строкам - При выполнении данной операции, Excel будет построчно сопоставлять ячейки из выделенных колонок. Если значение во втором диапазоне отличается, оно будет выделено, что позволить в результате этого увидеть разницу двух таблиц.
Результат выполнения
На основании всего вышеперечисленного, можно сделать вывод о том, что описанный способ будет удобен как для новичков, так и для опытных пользователей Excel. Однако следует учитывать, что такой способ сравнения в Excel 2 таблиц не является универсальным. Его целесообразно использовать только в том случае, если колонки, которые сравниваются на совпадение, содержат одинаковый тип данных, например, только числа или только текст. Когда значения имеют неодинаковый формат, данная функция может работать некорректно.
Простая формула для сопоставления ячеек
Программа MS Excel поддерживает логический тип данных. Благодаря этому можно проверить любое значение и узнать, содержит ли определенная ячейка его дубликат или нет. Операция сравнения выполняется очень просто — посредством перечисления ссылок на две ячейки через знак равенства. Если они совпадают, программа выведет результат «ИСТИНА», а если отличаются — «ЛОЖЬ».
Рассмотрим, как в Excel можно сравнить два столбца на совпадения, применив конструкцию для логического сравнения:
- Выделяем ячейку для записи функции. Желательно, чтобы она находилась напротив первой строки двух сравниваемых столбцов. В нашем примере такое условие соблюдается, так как две таблицы с отличиями находятся на синхронных позициях. В выбранную ячейку вводим формулу для сравнения двух ячеек — все ссылки пишем при этом через знак «равно».
Вводим формулу для сравнения - Нажимаем и получаем логическое значение — «ИСТИНА». Это свидетельствует о том, что заданные ячейки имеют одинаковые значения.
Смотрим результат сравнения - Теперь нужно сделать так, чтобы формула проверила остальные ячейки в двух проверяемых столбцах. Для этого наводим курсор на нижний правый угол ячейки с формулой и при появлении знака «+» зажимаем левую кнопку мыши. Протягиваем границу клетки вниз до конца колонки. Таким образом мы через маркер автозаполнения распространяем формулу для поиска отличий на другие ячейки, и они заполняются соответствующими результатами.
Применяем формулу ко всем ячейкам
При использовании данного метода следует учитывать несколько нюансов. Программа будет находить различия форматов. Это значит, что если в одной колонке числам присвоен числовой формат данных, а во второй текстовый, они не будут идентифицированы как уникальные. То же самое можно сказать про разницу регистра букв в словах.
Это говорит о том, что такое сравнение двух столбцов в Excel не является строгим. В большинстве случаев это не является критическим недостатком. Однако если задача состоит в том, чтобы обнаружить любые несоответствия — данный метод использовать не рекомендуется.
Формула массива для сравнения двух столбцов
Мы выяснили выше, как сравнить два столбца в Excel на совпадения с помощью простой формулы и вывести логическое значение, которое позволяет определить уникальность ячейки. Логическая конструкция, которая применяется для сравнения двух колонок, может использоваться для построения формулы массива.
Отличие этого метода заключается в том, что мы сразу обработаем целый диапазон ячеек, а не будем находить значение для первой строчки. В частности, способ будет удобен в тех случаях, когда нужно сопоставить диапазоны, которые не находятся в параллельной области, но имеют равное количество строк. Рассмотрим, как сделать проверку двух столбцов на предмет совпадений с помощью формулы массива:
- Выделяем диапазон, в котором будут записаны результаты проверки — значения «ИСТИНА» или «ЛОЖЬ». Длина этого диапазона должна соответствовать количеству строк в сравниваемых столбцах.
Выделяем диапазон - Не снимая выделения, вводим формулу для проверки. В данном случае нам нужно сравнить два диапазона. Поэтому задаем ссылки на столбцы, проставив между ними знак равенства.
Вводим формулу массива - Далее нужно выполнить записанную формулу. При обычном сравнении ячеек для этого достаточно нажать кнопку «Enter». Однако в нашем случае функция применяется к массивам. Поэтому вместо нажимаем комбинацию клавиш ++. В результате этого выделенный диапазон заполняется значениями «ИСТИНА» и «ЛОЖЬ», в зависимости от того, одинаковые ли сравниваемые ячейки или нет.Результат выполнения
Как видно, способ довольно удобный и простой. С его помощью пользователи смогут быстро выполнить проверку данных в столбцах, найти совпадающие ячейки или уникальные значения. После редактирования таблицы колонку с формулой можно удалить, используя любой из доступных инструментов Excel для очистки.
Отметим также, что формула массива на основе логической операции сравнения может использоваться для сопоставления целых таблиц. Принцип применения ее в этом случае будет абсолютно идентичным. Единственное различие заключается только в том, что выделенный для записи результата диапазон будет состоит не из одного, а из нескольких столбцов.
Функция СОВПАД для поиска различий
Рассматривая вопрос, как проверить столбцы на наличие идентичных значений, стоит обратить внимание на еще один способ — формулу СОВПАД. Данная функция работает по тому же принципу, что и логическое сравнение, которое описывалось в двух предыдущих примерах. Формула принимает ссылки на 2 ячейки в качестве аргументов и возвращает результат сравнения. Все найденные дубликаты помечаются как «ИСТИНА», а уникальные значения как «ЛОЖЬ». Способ применения формул очень простой:
- Выделяем ячейку для записи результата. В нее вводим формулу СОВПАД. В качестве аргументов указываем адреса первых ячеек из сравниваемых колонок.
Применяем формулу СОВПАД - В результате этого вы узнаете, содержат ли указанные ячейки одинаковые значения или нет. Используя маркер автозаполнения, найдите уникальные ячейки и в других фрагментах столбцов.Применяем формулу СОВПАД к остальным ячейкам
На первый взгляд может показаться, что этот способ ничем не отличается от тех формул, которые использовались в примерах выше. На самом деле у формулы СОВПАД есть очень важное преимущество — она находит любые различия между значениями в ячейках. Это особенно удобно тогда, когда столбцах записан одинаковый текст, но с разным регистром символов. Таким образом, формула СОВПАД может использоваться для более строгой проверки диапазонов.
Как и в случае с предыдущей конструкцией, формула СОВПАД может использоваться для обработки массивов. За счет этого вы можете быстро проверить 2 столбца или целые таблицы на предмет совпадений. Это можно сделать следующим образом:
- Выделяем столбец, размер которого соответствует размеру проверяемых диапазонов. В нашем случае это всего 11 ячеек, но на практике их количество может быть любым.
Выделяем ячейки - Далее вводим в выбранную клетку формулу СОВПАД. Только в данном случае в качестве аргументов указываем не ячейки, а ссылки на проверяемые диапазоны.
Вводим аргументы - Чтобы выполнить обработку, нажимаем комбинацию ++. В итоге выделенный диапазон будет заполнен значениями, соответствующими результату сравнения.
Результат выполнения
В целом, формула СОВПАД — оптимальное решение в том случае, если необходимо выполнить проверку текстовых значений. Эта функция реагирует на любые различия в ячейках, тогда как обычная конструкция для сопоставления ячеек игнорирует возможные отличия в оформлении текста. Также стоит учитывать, что СОВПАД может обрабатывать не только текстовые, но и любые другие данные. Поэтому данный метод проверки можно уверенно назвать универсальным.
Сравнение с помощью формулы ЕСЛИ
Проверка двух столбцов в таблицах необязательно должна ограничиваться только поиском различий или соответствий. Применяя формулу «ЕСЛИ», пользователь может самостоятельно задать любое значение, которое будет отображаться при обнаружении одинаковых или разных значений. По сути, этот способ позволяет сразу же выполнить частичное редактирование таблицы с учетом заданных ее автором параметров. Рассмотрим на примере таблицы в Excel, как можно использовать формулу «ЕСЛИ» для сравнения двух столбцов:
- Сначала выберите ячейку для записи результате проверки. Можно использовать диапазон, параллельный исходным таблицам, но это не является жестким требованием. Также стоит учесть, что ячейки, которые сравниваются друг с другом, необязательно должны находиться на одной строке. В выбранную ячейку вписываем формулу «ЕСЛИ» и открываем скобку для записи аргументов.
Вводим формулу ЕСЛИ - Далее нужно ввести логическое выражение. Это уже знакомая нам конструкция для сравнения, в которой две ячейки сопоставляются через знак равенства. Указав первый аргумент, необходимо поставить разделитель — знак точки с запятой.
Вводим логическое выражение - Далее нужно указать, какой текст будет выводиться при обнаружении повторений, а какой при уникальных значениях. Вводим нужные значения в кавычках, разделяя их точкой с запятой.
Указываем текстовые значения - Указав все аргументы, активируем формулу нажатием . В результате этого отобразится результат сопоставления двух ячеек из сравниваемых столбцов. Используя маркер автозаполнения, применяем формулу к остальным строкам в диапазонах. В результате чего в новом столбце появятся соответствующие значения.Результат применения
При использовании данного метода нужно учитывать, что сравнение выполняется на основе простого логического выражения. Поэтому формула реагирует только на различия в смысловом содержании ячеек, тогда как регистр и способ форматирования она игнорирует.
Подводим итоги
Сравнение ячеек в MS Excel может выполняться разными способами. Встроенные инструменты данной программы позволяют сопоставить данные из разных диапазонов таблиц без помощи макросов или надстроек. Для проверки столбцов в Экселе может использоваться инструмент для группового выделения или форматирования ячеек по условию. Альтернативный вариант — применение формул, основанных на логическом выражении сравнения. При использовании таких методов пользователю придется задействовать вспомогательный диапазон для отображения результатов, что не во всех случаях удобно.
Загрузка...
