Как найти совпадения в excel в одном столбце

Как найти одинаковые значения в столбце Excel

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

Как найти повторяющиеся значения в Excel?

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

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Вберете «Использовать формулу для определения форматируемых ячеек».
3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

Как видно на рисунке с условным форматированием нам удалось легко и быстро реализовать поиск дубликатов в Excel и обнаружить повторяющиеся данные ячеек для таблицы журнала заказов.

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

Самые быстрые и простые способы: найти дубликаты в ячейках.

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Поиск совпадений в двух списках

Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов — подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:
Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен — Создать (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) :

В открывшемся окне делаем три вещи:

1. выбираем наши таблицы из выпадающих списков
2. выделяем столбцы, по которым идет сравнение
3. выбираем Тип соединения = Внутреннее (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 используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

Примеры использования функции ПОИСКПОЗ в Excel

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

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

Например, массив содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.

Формула для поиска неточного совпадения текста в Excel

Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

Вид исходной таблицы данных:

Для нахождения позиции текстовой строки в таблице используем следующую формулу:

• D2&»*» – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
• B:B – ссылка на столбец B:B, в котором выполняется поиск;
• 0 – поиск точного совпадения.

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

Сравнение двух таблиц в Excel на наличие несовпадений значений

Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:

Как видно, третьи элементы списков не совпадают.

Поиск ближайшего большего знания в диапазоне чисел Excel

Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

Вид исходной таблицы данных:

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

Особенности использования функции ПОИСКПОЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; [тип_сопоставления])

• искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
• просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
• [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:

1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
3. Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

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

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

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

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

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

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

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

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

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

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

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