Как сопоставить два списка в excel и выделить совпадающие
Перейти к содержимому

Как сопоставить два списка в excel и выделить совпадающие

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

Как скачать Standoff 2 на устройства Huawei

Табличный процессор Эксель – одна из самых популярных программ для работы с электронными таблицами. И нередко у пользователя возникает вопрос – можно ли сравнить в Excel несколько столбцов на наличие совпадений. Особенно это важно для тех, кто работает с огромными объемами информации и, соответственно, большими таблицами.

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

Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.

Подпишитесь на наши каналы, чтобы не пропустить интересные новости и полезные статьи

Подпишитесь на наши каналы, чтобы не пропустить интересные новости и полезные статьи

1 Сравнение с помощью простого поиска

При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.

  1. Перейти на главную вкладку табличного процессора.
  2. В группе «Редактирование» выбрать пункт поиска.
  3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
  4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.

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

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

Как работает округление чисел в Эксель: принципы и настройки

2 Операторы ЕСЛИ и СЧЕТЕСЛИ

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

  1. Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
  2. В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
  3. Протянуть формулу до конца столбца.

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

Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.

Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.

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

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

Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.

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

Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.

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

3 Формула подстановки ВПР

Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.

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

С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

  1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
  2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
  3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

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

Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».

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

Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.

Как в Экселе посчитать сумму определенных ячеек

5 Сравнение с выделением совпадений цветом

В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.

Порядок действий для применения методики следующий:

  1. Перейти на главную вкладку табличного процессора.
  2. Выделить диапазон, в котором будут сравниваться столбцы.
  3. Выбрать пункт условного форматирования.
  4. Перейти к пункту «Правила выделения ячеек».
  5. Выбрать «Повторяющиеся значения».
  6. В открывшемся окне указать, как именно будут выделяться совпадения в первой и второй колонке. Например, красным текстом, если цвет остальных сообщений стандартный черный. Затем указать, что выделяться будут именно повторяющиеся ячейки.

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

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

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

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

6 Надстройка Inquire

Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.

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

Процесс использования надстройки включает такие действия:

  1. Перейти к параметрам электронной таблицы.
  2. Выбрать сначала надстройки, а затем управление надстройками COM.
  3. Отметить пункт Inquire и нажать «ОК».
  4. Перейти к вкладке Inquire.
  5. Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
  6. В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.

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

Есть столбец в листе 2 содержащий значения которые надо сравнить со значениями в ячейках столбца 2 листа 1 и выделить цветом совпадающие ячейки.
Как это сделать?

PS
Excel почти не пользуюсь полный нуб в этом деле)

  • Вопрос задан более двух лет назад
  • 143 просмотра

2 комментария

Простой 2 комментария

как это сделать ?

Условное форматирование по пользовательской формуле. Например, =НЕ(ЕОШИБКА(ВПР()))

ProgrammerForever

Григорий Боев @ProgrammerForever Куратор тега Excel
Условное форматирование
=A1=B1
Решения вопроса 0
Ответы на вопрос 1

karabanov

Системный администратор
Ответ написан более двух лет назад
Комментировать
Нравится 1 Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

разработка-игр

  • Разработка игр
  • +4 ещё

Как рассчитать балл аномалии на базе средних значений в метриках?

  • 2 подписчика
  • 15 часов назад
  • 77 просмотров

Поиск отличий в двух списках

Типовая задача, возникающая периодически перед каждым пользователем Excel — сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) : difference1.pngЧисло несовпадений можно посчитать формулой:
=СУММПРОИЗВ(—(A2:A20<>B2:B20))
или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20)) Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter. Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special)Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)difference2.pngExcel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

  • залить цветом или как-то еще визуально отформатировать
  • очистить клавишей Delete
  • заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
  • удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
  • и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

difference5.png

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.

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

difference4.png

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

difference6.png

Выглядит страшновато, но свою работу выполняет отлично 😉

Ссылки по теме

  • Выделение дубликатов в списке цветом
  • Сравнение двух диапазонов с помощью надстройки PLEX
  • Запрет ввода повторяющихся значений

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

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

Иногда у пользователей Microsoft Excel возникает необходимость сравнить данные в столбцах и найти строки с одинаковыми или разными значениями. Это может быть нужно для ряда целей, включая получение данных об одинаковых событиях или поиск совпадений по продажам. В этом материале вы получите информацию о том, как быстро сравнить два столбца на совпадения по строкам без использования вспомогательных ячеек и дополнительных формул. Совпадения можно будет выделить, чтобы отличить их от уникальных значений. Это поможет быстрее проанализировать большую выборку данных.

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

Читайте также в Комьюнити: Как удалить дубликаты в Microsoft Excel

Вариант 1: Инструмент выделения группы ячеек

Эксель предоставляет своим пользователям разнообразный список небольших, но полезных функций. Сюда можно отнести выделение ячеек по группам. В отдельном окне появляются настройки этого выделения, среди которых можно выбрать пустые ячейки, разные области, формулы или отличия. Как раз последний тип нас и интересует, поэтому давайте более детально остановимся на использовании инструмента.

  1. Для начала выделите все содержимое двух столбцов для сравнения без заголовков. Значения в ячейках могут быть как текстовыми, так и числовыми или даже смешанными. Выделение значений для сравнения столбцов в Excel на совпадения в строках
  2. После выделения используйте сочетание клавиш Ctrl + G для вызова меню настройки выделения. Горячая клавиша для сравнения столбцов в Excel на совпадения в строках
  3. В следующем окне нажмите по кнопке «‎Выделить» для открытия необходимых параметров. Переход к настройке выделения для сравнения столбцов в Excel на совпадения в строках
  4. Далее понадобится отметить маркером пункт «‎Отличия по строкам», после чего нажать «‎ОК», чтобы сохранить это выделение. Настройка выделения для сравнения столбцов в Excel на совпадения в строках
  5. Вы заметите, что строки в двух столбцах с уникальными значениями теперь выделены, а выделение с других снято. Пока что этого недостаточно, чтобы комфортно изучать эти совпадения, поскольку выделение может быть случайно сброшенным. Результат выделения для сравнения столбцов в Excel на совпадения в строках
  6. Поэтому вызовите настройку заливки и выберите любой удобный для вас цвет, чтобы залить уникальные ячейки конкретным цветом. Заливка выделения для сравнения столбцов в Excel на совпадения в строках
  7. Теперь уникальные значения в столбце справа будут выделены отдельным цветом, а одинаковые – без заливки. Этого вполне достаточно для того, чтобы найти совпадения и решить, что с ними делать в дальнейшем. Результат заливки выделения для сравнения столбцов в Excel на совпадения в строках

Это довольно простой и понятный метод поиска совпадений, требующий исключительно применения настройки для выделения. Однако сработает он всего раз, и чтобы в дальнейшем снова повторить поиск, понадобится выполнить одни и те же действия повторно для всей области данных. Если такой вариант вам кажется долгим, то ознакомьтесь со следующим, который подразумевает другой подход к выполнению данной задачи.

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Вариант 2: Условное форматирование

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

  1. Если вы еще не отформатировали ваш диапазон данных как таблицу, рекомендую сделать это сейчас, поскольку так вы значительно упростите дальнейшую работу с условным форматированием. Форматирование диапазона как таблицы для сравнения столбцов в Excel на совпадения в строках
  2. После этого достаточно будет выделить всего одну ячейку из вашей таблицы, затем развернуть настройку «‎Условное форматирование» и выбрать действие «‎Создать правило». Переход к настройке условного форматирования для сравнения столбцов в Excel на совпадения в строках
  3. В списке с выбором типа правила вас интересует «‎Использовать формулу для определения форматирования ячеек». Сейчас нужно просто выделить это правило, кликнув по нему левой кнопкой мыши. Выбор правила форматирования для сравнения столбцов в Excel на совпадения в строках
  4. В качестве формулы для форматирования используйте =$A2=$B2, где A2 и B2 – первые строки в вашей таблице для сравнения. Ввод формулы форматирования для сравнения столбцов в Excel на совпадения в строках
  5. Теперь условное форматирование уже работает корректно, но пока не применяет никаких внешних изменений к ячейкам. Для исправления этой ситуации нажмите по кнопке «‎Формат». Переход к формату выделения для сравнения столбцов в Excel на совпадения в строках
  6. Вы можете задать любое изменение для совпадений, поменяв шрифт или его форматирование, добавив границы для ячеек или выполнив стандартную заливку. В этом случае я остановлюсь на последнем варианте, поскольку он будет самым наглядным. Выбор цвета заливки или изменения для сравнения столбцов в Excel на совпадения в строках
  7. Сейчас условное форматирование еще не работает, поскольку не был задан диапазон для проверки. Выделите всю таблицу, снова откройте «‎Условное форматирование» и выберите «‎Управление правилами». Переход к настройке правила для сравнения столбцов в Excel на совпадения в строках
  8. В строке «‎Применяется к» укажите весь диапазон ваших двух столбцов и примените изменения. Настройка правила для сравнения столбцов в Excel на совпадения в строках
  9. Если что, то вы можете как выделить его вручную, так и написать в виде диапазона по номерам ячеек. Выбор диапазона для правила для сравнения столбцов в Excel на совпадения в строках
  10. После применения условного форматирования вы увидите, что совпадения в обоих столбцах выделены по вашим правилам изменения внешнего вида. По необходимости вы всегда можете вернуться в настройку правила, указать там целую умную таблицу или повторно выбрать нужный диапазон для проверки. Действие условного форматирования для сравнения столбцов в Excel на совпадения в строках

Конечно, для сравнения значений иногда еще можно использовать вспомогательные формулы в отдельных ячейках, однако такой метод значительно сложнее и неактуален при наличии инструментов условного форматирования и выделения совпадений. В большинстве случаев описанных выше вариантов будет достаточно для того, чтобы найти разные или одинаковые значения, а после этого решить, что делать с такой информацией, как ее изменить, выделить или удалить.

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *