Vba excel поиск повторяющихся значений в столбце

Обновлено: 03.07.2024

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

Expression . Find ( What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

В скобках перечислены параметры метода, среди них только What является обязательным.

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.

Параметры метода Range.Find

xlValues (-4163) – значения;
xlComments (-4144) – примечания*;
xlNotes (-4144) – примечания*;
[xlFormulas (-4123) – формулы]**.

xlWhole (1) – полное совпадение;
xlPart (2) – частичное совпадение.

xlByRows (1) – поиск по строкам;
xlByColumns (2) – поиск по столбцам.

xlNext (1) – поиск вперед;
xlPrevious (2) – поиск назад.

False (0) – поиск без учета регистра (по умолчанию);
True (1) – поиск с учетом регистра.

False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

Метод Range.RemoveDuplicates предназначен в VBA Excel для удаления повторяющихся значений по столбцам в заданном диапазоне ячеек рабочего листа. Строки с обнаруженными дубликатами удаляются целиком.

Синтаксис метода Range.RemoveDuplicates

expression. RemoveDuplicates (Columns , Header) ,

где expression — переменная или выражение, возвращающее объект Range.

Параметры метода Range.RemoveDuplicates

xlNo — первая строка списка не содержит заголовок (значение по умолчанию);
xlYes — первая строка диапазона содержит заголовок;
xlGuess — VBA Excel решает сам, есть ли у списка заголовок.

Необязательный параметр. Тип данных – XlYesNoGuess.

Метод работает как с круглыми скобками, в которые заключены параметры, так и без них. Если требуется указать несколько столбцов в параметре Columns, следует использовать функцию Array, например, Array(2, 3).

Примеры удаления дубликатов

Исходная таблица для всех примеров

По третьей колонке легко определить, какие строки были удалены.

Пример 1
Удаление повторяющихся значений по первому столбцу:

Range("A1:C10").RemoveDuplicates 1

Range(Cells(1, 1), Cells(10, 3)).RemoveDuplicates (1)

Второй вариант позволяет использовать вместо индексов строк и столбцов переменные. Наличие или отсутствие скобок, в которые заключен параметр Columns, на работу метода не влияет.

Пример 2
Удаление дубликатов по первому столбцу с указанием, что первая строка содержит заголовок:

Range("A1:C10").RemoveDuplicates 1, xlYes

Здесь мы видим, что первая строка не учитывалась при поиске повторяющихся значений.

Пример 3
Удаление дубликатов по первому и второму столбцам:

Range("A1:C10").RemoveDuplicates Array(1, 2)

Обратите внимание, что при удалении повторяющихся значений по нескольким столбцам, будут удалены дубли только тех строк, в которых во всех указанных столбцах содержатся одинаковые значения. В третьем примере удалены «лишние» строки с дублями значений по двум первым столбцам: Корова+Лягушка, Свинья+Бурундук и Овца+Собака.

Смотрите, как отобрать уникальные значения из списка в VBA Excel с помощью объекта Collection и объекта Dictionary.

Итак есть столбец "улица", рядом столбец "дом". Есть еще другие Столбцы в которых есть информация.
Если есть 2 записи в которых улица и номер дома идентичны, то подсветить красным цветом чтобы менеджер мог удалить лишнюю запись.
Так как дубликатов быть не должно.
Таблица состоит из 20 000! записей(строк)

Как можно было бы организовать алгоритм обхода так, чтобы это работало максимально производительно(быстро)

во вложении к посту
тестовая база на 6000 записей + мой макрос который жутко вешает EXCEL

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

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

SQL запрос: поиск дубликатов в таблице
Всем привет! Пишу макрос. Помогите пжл с следующей проблемой. Она упирается в правильное написание.

Ошибка поиск значения в столбце макросом в excel
Подскажите делитанту, как узнать есть ли такое значение в колонке, если есть то присвоить какому.

Поиск в таблице Excel
Задача в том чтобы введенные данные в поле сравнивались с таблице в exel т.е. я ввожу данные в.

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

Примечание:
Для сохранения целостности табличных данных, перед сортироукой таблицу лучше выделить. Далее воспользоваться командой Данные -> Сортировка.

я в макросах не сильна, но эту задачу можно решить еще вот так:

по такому алгоритму у меня расчет занял 22 минуты 12 секунд. не знаю быстрее это или медленее чем у Вас, но мало ли. м.б. пригодится.

если доступна сотировка, то есть более быстрый и простой механизм, только он делается не макросом, а формулой. сортируем сначала по а, затем по б и в пустой столбец во вторую строку забиваем формулу "=если((A2=A3)*И(B2=B3);1;"")". растягиваем формулу до конца.
в результате получаем единички напротив повторов.
чисто теоретически все это можно загнать в макрос

Loter. Ты прав на все 100%. Однако вся прелесть макросов - это автоматизация твоих действий.

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

У-Ф-Ф-Ф. Даже рука устала писать. Гораздо проще все это проделать одним кликом по кнопке, который присвоен специальный макрос.

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

на базу в 6000 заняло меньше 5 секунд
всё это дело можно завязать на сочетание клавиш типа ctrl+w или конпку

Не знаю как bloogrox, а я результатом в общем и целом удовлетворен.

Единственное, на что нужно обратить внимание - это то, что сейчас программа использует дополнительный столбец книги Excell для того чтобы ввести формулу

Это не всегда хорошо. Поиск можно осуществить простым перебором ячеек, не выводя никакой дополнительной информации на листы книги Excell. maximus09, а о каких встроенных механизмах сортировки ты говорил?

(макрос для этого можно не писать в ручную, а воспользоваться командой "Запись макроса" и выбрать сортировку по возрастанию или убыванию). В отсортированной таблице поиск повторяющихся элементов не должен занять много времени, особенно если воспользоваться "бинарным поиском".

Более подробно об описанном здесь механизме программирования можно прочитать в книге
А.Ю. Гарнаев Самоучитель VBA. - СПб.: БХВ-Петербург, 2002.

Кстати, там данный пример ручной с сортировкой описан в подробностях (что, какие опции в окне параметров сортировки нужно выбирать, зачем нужно выделять всю таблицу прежде чем производить сортировку и т.п.). Очень рекомендую книгу. Сам учился по ней. Но, стоит сказать, что она целиком посвящена VBA в Excell.

Сравнение повторяющихся значений
Добрый день, помогите решить задачу. Есть таблица с двумя столбцами, в перовом id, во втором.

Удаление повторяющихся значений из комбобокса
Добрый день. Как в VBA удалить из комбобокса данные которые повторяются, оставив только по одному.

Подсчет повторяющихся значений в ячейке
Всем здрасьте. Помогите, пожалуйста, реализовать макрос последовательного (с конца) подсчета нолей.

Изменение значений у повторяющихся элементов
Доброй ночи. Начну с того, что в VBA и программировании в целом я неофит, так что, пожалуйста, не.

Я помогу Вам, но прежде хотел узнатьзачем это Вам т.е. цель просто интересно. Завтра я Вам отправлю файлик на форум Ser Artur,
Да я и сам написать могу, хотя и файлик посмотреть будет интересно
Я просто спрашивал про концепцию. Правильный ли я подход выбрал и нет ли у этого подхода подводных камней.
А нужно это для целей аудита, так как я аудитор. Ну вот, представте, что есть база основных средств строк так на 55,000 и у каждого ОС есть ID (хотя обычно ID нет). Так вот, на практике в 95% случаев:
1. Или среди ID есть повторяющиеся значения
2. Или этот самый ID необходимо создавать самому (поскольку его нет), объединяя несколько параметров, примером "Название&Сумма&Дата_Введения_в_эксплуатацию" и т. д.
Так вот, для дальнейших <STRONG>корректных</STRONG> манипуляций необходимо отловить одинаковые значения.
Надеюсь, мой ответ удовлетворил Ваше любопытство

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

а в Вашем случае с каждым ходом массив все больше и больше увиличивается а соответственно и скорость выполнения операции постепенно падает - если учесть что количество строк может доходить до 55 тысяч - получается очень мрачная ситуация даже на сильной машине уже после первых 5 тысяч строк - и дальше - хуже

если бы такая задача стояла передо мной я бы:

1. создал столбец идентификационных номеров ->

2. перевел бы их в текстовый формат ->

3. используя автофильтр отсортировал бы весь масив по этому полю ->

4. далее, например, в колонке справа от автофильтра вписал бы формулу "=b2=b1" и скопировал бы до конца массива (для упращения дальнейшей обработки можно использовать более сложную конструкцию - например "=ЕСЛИ(I2=I1;"ахтунг";"")"->

5. полученый столбец скопирывал бы и вставил как значения - чтобы не напрягать систему ненужным пересчетом при фильтрации..

ну в принципе все - имеем то что хотели. без VBA (хотя оч уважаю и активно применяю его ), быстро, без проблем. если хочу ячейку в красный окрасить - условное форматирование.

Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):

Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать.

Способ 1. Ищем повторения: текст по столбцам и формула массива

Это не самый удобный и быстрый, но зато самый простой вариант решения задачи "на коленке". Выделим исходный список и разобъем его на столбцы по пробелам с помощью команды Данные - Текст по столбцам (Data - Text to columns) . В открывшемся окне трёхшагового Мастера выберем формат По разделителю (By delimiter) на первом шаге и поставим флажок Пробел (Space) на втором:

Если в исходных данных могут быть лишние пробелы, то лучше включить и опцию Считать последовательные разделители одним (Treat consecutive delimiters as one) - это избавит нас от лишних столбцов.

На третьем шаге в поле Поместить в зададим пустую ячейку рядом с таблицей, чтобы результаты не затёрли нам исходные данные и нажмём на Готово (Finish) :

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

В английской версии это будет =SUMPRODUCT(N(COUNTIF(B2:G2,B2:G2)>1))

Давайте разберём логику её работы на примере первой строки.

Сначала мы с помощью формулы СЧЁТЕСЛИ( B2:G2 ; B2:G2 ) вычисляем по очереди количество вхождений каждого имени в диапазон B2:G2 и получаем на выходе массив 2,1,2,1>, т.к. Иван встречается в первой строке 1 раз, Елена - 2 раза, Сергей - 1 и т.д.
Проверяем с помощью СЧЁТЕСЛИ(B2:G2;B2:G2) >1 какие из полученных чисел больше единицы, т.е. где у нас повторы. На выходе эта формула выдаст нам массив результатов проверки в виде .
Переводим логические значения ЛОЖЬ и ИСТИНА в более удобные для подсчета 0 и 1, соответственно, с помощью функции Ч. На выходе получаем массив >.
Суммируем все элементы получившегося массива функцией СУММПРОИЗВ. Можно было бы использовать и обычную функцию СУММ, но тогда пришлось бы жать вместо привычного Enter сочетание клавиш Ctrl + Shift + Enter , чтобы ввести формулу как формулу массива.

По получившемуся столбцу можно легко отфильтровать строки с повторами и работать потом с ними дальше уже вручную.

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

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

Если дубликаты нужно именно наглядно показать, то удобнее будет использовать для этого специальный макрос. Откроем редактор Visual Basic одноимённой кнопкой на вкладке Разработчик (Developer - Visual Basic) или сочетанием клавиш Alt + F11 . Вставим в книгу новый пустой модуль через меню Insert - Module и скопируем туда вот такой код:

Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:

Если нужно, чтобы цветом выделялись только клоны, но не первые вхождения (т.е. только вторая и третья, но не первая Алиса, например), то достаточно будет просто убрать из кода строку 20.

Способ 3. Выводим повторы в соседний столбец

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

Эта функция, как легко догадаться, принимает в качестве единственного аргумента ячейку с текстом и выводит в качестве результата все повторы, которые там найдет:

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

Если нужно просто удалить дубликаты внутри ячейки, чтобы все оставшиеся там слова не повторялись, то макрос будет похож на предыдущий, но попроще:

Способ 5. Удаление повторов внутри ячейки через Power Query

Этот способ использует бесплатную надстройку Excel для обработки данных под названием Power Query. Для Excel 2010-2013 скачать её можно с сайта Microsoft, а в Excel 2016-2019 она уже встроена по умолчанию. Огромным плюсом этого варианта является возможность автоматического обновления - если в будущем исходные данные изменятся, то нам не придется заново проделывать всю обработку (как в Способе 1) или запускать макрос (как в Способе 4) - достаточно будет просто обновить созданный запрос.

Сначала наши данные нужно загрузить в Power Query. Проще всего для этого превратить нашу таблицу в "умную" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) , а затем нажать кнопку Из таблицы/диапазона (From table/range) на вкладке Power Query (если у вас Excel 2010-2013) или на вкладке Данные (если у вас Excel 2016 или новее):

Поверх окна Excel откроется окно редактора запросов Power Query с загруженными туда нашими данными:

Дальше делаем следующую цепочку действий:

Удаляем ненужный пока шаг Измененный тип (Changed Type) справа в панели применённых шагов с помощью крестика слева от шага.

Чтобы можно было потом идентифицировать принадлежность каждого имени к исходной строке - добавляем столбец с нумерацией строк на вкладке Добавление столбца - Столбец индекса - От 1 (Add Column - Index Column - From 1) :

Выделяем столбец с именами и жмём на вкладке Преобразование - Разделить столбец - По разделителю (Transform - Split Column - By delimiter) , а в открывшемся окне выбираем деление по каждому пробелу и - главное - деление на строки, а не на столбцы в расширенных параметрах:

После нажатия на ОК увидим следующее:

Теперь выделяем оба столбца (удерживая клавишу Ctrl или Shift ) и удаляем дубликаты через Главная - Удалить строки - Удалить дубликаты (Home - Remove Rows - Remove Duplicates) .

Осталось собрать всё обратно в ячейки :) Для этого выделим столбец Индекс и используем команду Группировать по на вкладке Преобразование (Transform - Group By) со следующими параметрами:

После нажатия на ОК наши имена сгруппируются во вложенные таблицы, имитирующие начальные ячейки - только уже без повторов. Увидеть содержимое свёрнутых таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table (но не в слово Table!):

Осталось вытащить все имена из первой колонки каждой таблицы и склеить их через пробел. Это можно сделать с помощью небольшой формулы на встроенном в Power Query языке М. Выберем на вкладке Добавление столбца команду Настраиваемый столбец (Add Column - Custom Column) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):

Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to..) :

Задача решена! Если в будущем данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .

Читайте также: