Поиск совпадений в двух списках
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов — подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:
Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен — Создать (Formulas — Name Manager — Create) . Также можно превратить таблицы в «умные» с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table) .
Подсчет количества совпадений
Для подсчета количества совпадений в двух списках можно использовать следующую элегантную формулу: 
В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2)) Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек. Во-первых, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации: =СЧЁТЕСЛИ( Где_искать ; Что_искать ) Обычно первый аргумент — это диапазон, а второй — ячейка, значение или условие (одно!), совпадения с которым мы ищем в диапазоне. В нашей же формуле второй аргумент — тоже диапазон. На практике это означает, что мы заставляем Excel перебирать по очереди все ячейки из второго списка и подсчитывать количество вхождений каждого из них в первый список. По сути, это равносильно целому столбцу дополнительных вычислений, свернутому в одну формулу: 
Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции — суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl + Shift + Enter . Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.
Вывод списка совпадений формулой массива
Если нужно не просто подсчитать количество совпадений, но и вывести совпадающие элементы отдельным списком, то потребуется не самая простая формула массива:
В английской версии это будет, соответственно: =INDEX(Список1;MATCH(1;COUNTIF(Список2;Список1)*NOT(COUNTIF($E$1:E1;Список1));0)) Логика работы этой формулы следующая:
- фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
- фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
- и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент
Не забудьте в конце ввода этой формулы нажать сочетание клавиш Ctrl + Shift + Enter , т.к. она должна быть введена как формула массива.
Возникающие на избыточных ячейках ошибки #Н/Д можно дополнительно перехватить и заменить на пробелы или пустые строки «» с помощью функции ЕСЛИОШИБКА (IFERROR) .
Вывод списка совпадений с помощью слияния запросов Power Query
На больших таблицах формула массива из предыдущего способа может весьма ощутимо тормозить, поэтому гораздо удобнее будет использовать Power Query. Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать практически любые данные. Мощь и возможности Power Query так велики, что Microsoft включила все ее функции по умолчанию в Excel начиная с 2016 версии.
Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table) :
Excel превратит нашу таблицу в «умную» и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить — Закрыть и загрузить в. (Close & Load To. ) и выбрать в появившемся окне Только создать подключение (Create only connection) :
Затем повторяем то же самое со вторым диапазоном.
И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные — Объединить запросы — Объединить (Get Data — Merge Queries — Merge) :
В открывшемся окне делаем три вещи:
- выбираем наши таблицы из выпадающих списков
- выделяем столбцы, по которым идет сравнение
- выбираем Тип соединения = Внутреннее (Inner Join)
После нажатия на ОК на экране останутся только совпадающие строки:
Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :
Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 .
Макрос для вывода списка совпадений
Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer) . Если ее не видно, то отобразить ее можно через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .
В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert — Module и затем скопировать туда код нашего макроса:
Sub Find_Matches_In_Two_Lists() Dim coll As New Collection Dim rng1 As Range, rng2 As Range, rngOut As Range Dim i As Long, j As Long, k As Long Set rng1 = Selection.Areas(1) Set rng2 = Selection.Areas(2) Set rngOut = Application.InputBox(Prompt:="Выделите ячейку, начиная с которой нужно вывести совпадения", Type:=8) 'загружаем первый диапазон в коллекцию For i = 1 To rng1.Cells.Count coll.Add rng1.Cells(i), CStr(rng1.Cells(i)) Next i 'проверяем вхождение элементов второго диапазона в коллекцию k = 0 On Error Resume Next For j = 1 To rng2.Cells.Count Err.Clear elem = coll.Item(CStr(rng2.Cells(j))) If CLng(Err.Number) = 0 Then 'если найдено совпадение, то выводим со сдвигом вниз rngOut.Offset(k, 0) = rng2.Cells(j) k = k + 1 End If Next j End Sub
Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl , оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F8 . Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:
Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.
Ссылки по теме
- Поиск различий в двух списках Excel
- Слияние двух списков без дубликатов (3 способа)
- Что такое макросы, как их использовать, куда копировать код макросов на Visual Basic
Как проверить совпадения в excel в столбце
Проверка на совпадение
В EXCEL существует несколько вариантов сравнения содержимого ячеек, от проверки на равенство чисел до совпадения текста.
Именно о проверке текста мы и поговорим.
Если необходимо сравнить две ячейки с текстом, не обращая внимания на различие строчных или прописных букв, то можно воспользоваться выражением «=ячейка1=ячейка2» ,
результатом которого будет либо ИСТИНА либо ЛОЖЬ , если значения не совпадут.
Если необходимо сделать проверку на точное совпадение, то поможет функция СОВПАД .
Для того, чтобы сравнить две ячейки необходимо:
- Выбрать первую ячейку в которой будем получать результаты сравнения, ввести =СОВПАД и нажать fx.
- В открывшемся окне настроить аргументы, где в первой ячейке указать первое сравниваемое значение, во второй –второе соответственно.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Как в Эксель сравнить два столбца на совпадения и найти расхождения
Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 1-й строки ее можно протянуть / копировать на остальные строчки. Для проверки содержания одинаковых строк используйте формулу =ЕСЛИ(A2=B2; “Совпадают”; “”), для отличий — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Ниже подробно рассмотрим, как сравнить сведения для двух и более секциях, а также поговорим о выборе результата.
Как сравнить столбцы в Эксель
Одна из особенностей приложения — возможность в Эксель сравнить столбцы (два и более) на факт отличий и различий, а после вывести результаты в виде подсвечивания цветом. Ниже рассмотрим, как правильно сделать эту работу для разного количества столбцов.
Два
При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.
Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:
- =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
- =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).
При этом в таблице выводится информация о наличии совпадений или отличий.
Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
Альтернативный вариант
Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.
Больше двух
По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.
Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).
Поиск совпадений в двух и более столбцах
Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).
В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).
В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».
Как вывести результат в Эксель
Выше мы уже рассмотрел, как в Excel сравнить данные для двух или более столбцов. В указанных выше случаях информация выводится в последней строчке таблицы и показывает, совпадают данные или различаются в зависимости от задачи и используемой команды. Но существует еще один путь — выделение совпадений одним цветом в двух и более столбцах. В таком случае информация выглядит более наглядно.
Для этого в Эксель сделайте следующее:
- Выделите вертикальные секции с данными, которые нужно сравнить.
- Войдите во вкладку «Главная» на панели инструментов и жмите «Условное форматирование».
- Кликните на пункт «Правила выделения ячеек» и «Повторяющиеся значения».
- В появившемся диалоговом окне выберите слева пункт «Повторяющиеся», а в правом списке укажите, каким цветом будут выделяться данные. Жмите на кнопку «ОК».
- После этого в выделенной колонке подсвечиваются цветом совпадения.
При желании можно найти и выделить совпадающие в Эксель строки. Для этого сделайте следующее:
- С правой стороны от таблицы сделайте дополнительный столбик, где напротив каждой строчки с информацией установите формулу. Последняя должна объединять все параметры строки в одну ячейку. В дополнительной колонке будут видны объединенные сведения.
- Выделите область с информацией в дополнительной колонке.
- В разделе «Главная» жмите на «Условное форматирование», а после «Правила выделения ячеек».
- Кликните на «Повторяющиеся значения».
- Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.
Теперь вы знаете, как в Эксель сравнить два столбика и более, а после вывести результаты цветом или в последней колонке. В комментариях поделитесь, удалось ли вам сделать работу, и какие еще методы можно использовать.
Как проверить таблицы Excel на совпадение
Excel — одна из самых популярных программ для работы с таблицами. Ее широкий функционал позволяет создавать и анализировать большие объемы данных. Однако, при работе с большими таблицами иногда возникает необходимость проверить их на совпадение. Это может быть полезно, например, при сравнении версий документов или анализе данных из разных источников.
В этой статье мы рассмотрим несколько простых способов проверки таблиц Excel на совпадение. Мы подробно разберем каждый способ и предоставим пошаговую инструкцию о том, как использовать его. Независимо от вашего уровня знаний в Excel, эти инструкции помогут вам быстро и эффективно проверить таблицы на наличие совпадений.
Проверка таблиц Excel на совпадение может сэкономить вам время и силы, а также помочь избежать ошибок в работе с данными. Это особенно важно, если вы работаете с большими объемами информации или часто анализируете данные из разных источников.
Будьте внимательны при выполнении инструкций, чтобы избежать ошибок и достичь точных результатов. И помните, что проверка таблиц на совпадение — это всего лишь одна из многих возможностей, которые предлагает Excel для работы с данными.
Проверка таблиц Excel на совпадение: способы и инструкция
Первый способ — использование условного форматирования. Вы можете настроить условное форматирование, чтобы выделить дубликаты в таблице. Для этого выберите диапазон ячеек, которые вы хотите проверить, затем перейдите во вкладку «Форматирование» и выберите «Условное форматирование». Затем выберите «Дублированные значения» и настройте стиль форматирования для выделения дубликатов.
Второй способ — использование функции «Подсчет.Если». Эта функция позволяет вам подсчитать количество совпадений в диапазоне ячеек. Для этого вы можете использовать формулу «=Подсчет.Если(диапазон; значение)». Функция вернет количество совпадений с указанным значением.
Кроме того, вы также можете использовать специальные программы и онлайн-сервисы для проверки таблиц Excel на совпадение. Эти инструменты способны автоматически обнаруживать дубликаты и помогать вам редактировать их или удалять из таблицы.
Важно помнить, что проверка таблиц Excel на совпадение должна проводиться перед дальнейшей обработкой данных. Это позволит избежать ошибок и неправильного анализа информации. Благодаря использованию простых способов и инструментов, вы сможете быстро и эффективно проверить таблицы Excel на совпадение и обнаружить любые ошибки.
Как найти совпадения в таблицах Excel: поиск по одному столбцу
- Откройте первую таблицу Excel и выберите столбец, по которому вы хотите искать совпадения.
- Нажмите на вкладку «Данные» в верхней части экрана и выберите «Удалить дубликаты».
- Появится диалоговое окно с вариантами удаления дубликатов. Убедитесь, что выбран правильный столбец для поиска совпадений.
- Нажмите кнопку «ОК», чтобы удалить дубликаты и оставить только уникальные значения в выбранном столбце.
- Повторите те же самые шаги для второй таблицы Excel.
- Теперь у вас есть два столбца без дубликатов. Чтобы найти совпадения, выберите оба столбца и нажмите на вкладку «Данные». В разделе «Инструменты данных» выберите опцию «Условное форматирование» и затем «Найти дубликаты».
- Все совпадающие значения будут выделены в таблице, чтобы вы смогли легко их увидеть и анализировать.
Используя этот метод, вы сможете быстро найти совпадения в таблицах Excel и анализировать информацию для принятия более обоснованных решений.
Проверка таблиц на совпадение: сравнение по нескольким столбцам
При работе с таблицами Excel часто возникает необходимость проверить их на совпадение. Особенно актуально это при сравнении больших объемов данных или при анализе отчетов и баз данных.
Простейший способ сравнения таблиц заключается в проверке на совпадение значений в одном столбце. Но, когда необходимо выполнить сравнение по нескольким столбцам, на помощь приходят дополнительные инструменты и функции Excel.
Для сравнения таблиц по нескольким столбцам можно использовать условное форматирование или функции Excel, такие как СОВПАДЕНИЕ, И, ИЛИ. При этом необходимо указать столбцы, по которым нужно выполнить сравнение и определить условия, при которых значения будут считаться совпадающими.
Процесс сравнения таблиц по нескольким столбцам может быть следующим:
- Выделите диапазон ячеек в первой таблице, которые необходимо сравнить.
- Используя формулу СОВПАДЕНИЕ, сравните значения каждой ячейки первой таблицы с таблицей, с которой нужно выполнить сравнение.
- Повторите шаги 1-2 для каждого столбца, участвующего в сравнении.
- Если значения совпадают, можно добавить дополнительную обработку для таких случаев, например, выделение ячейки или запись значения в новую таблицу.
Такой подход позволит выполнить проверку таблиц на совпадение по нескольким столбцам и получить необходимую информацию. Этот метод особенно полезен при работе с большим объемом данных и позволяет автоматизировать процесс сравнения.