Дубликаты внутри ячейки

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

Исходные данные

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

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

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

Делим текст по пробелам

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

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

Выбираем место для вывода результатов

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

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

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

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

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

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

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

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

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

Sub Color_Duplicates()
    Dim col As New Collection
    Dim curpos As Integer, i As Integer
    On Error Resume Next

    For Each cell In Selection
        Set col = Nothing
        curpos = 1
        
        'убираем лишние пробелы и разбиваем текст из ячейки по пробелам
        arWords = Split(WorksheetFunction.Trim(cell.Value), " ")
        
        For i = LBound(arWords) To UBound(arWords)      'перебираем слова в получившемся массиве
            Err.Clear                                   'сбрасываем ошибки
            curpos = InStr(curpos, cell, arWords(i))    'позиция начала текущего слова
            col.Add arWords(i), arWords(i)              'пытаемся добавить текущее слово в коллекцию
            
            If Err.Number <> 0 Then         'если возникает ошибка - значит это повтор, выделяем красным
                cell.Characters(Start:=curpos, Length:=Len(arWords(i))).Font.ColorIndex = 3
                cell.Characters(Start:=InStr(1, cell, arWords(i)), Length:=Len(arWords(i))).Font.ColorIndex = 3
            End If
            curpos = curpos + Len(arWords(i))       'переходим к следующему слову
        Next i
    Next cell
End Sub

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

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

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

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

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

Function GetDuplicates(cell As Range) As String
    Dim col As New Collection
    Dim i As Integer, sDupes As String
    
    On Error Resume Next
    Set col = Nothing
        
    'делим текст в ячейке по пробелам
    arWords = Split(WorksheetFunction.Trim(cell.Value), " ")
        
    'проходим в цикле по всем получившимся словам
    For i = LBound(arWords) To UBound(arWords)
        Err.Clear                           'сбрасываем ошибки
        col.Add arWords(i), arWords(i)      'пробуем добавить слово в коллекцию
        'если ошибки не возникает, то это не повтор - добавляем слово к результату
        If Err.Number <> 0 Then sDupes = sDupes & " " & arWords(i)
    Next i
    GetDuplicates = Trim(sDupes)      'выводим результаты
End Function

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

Макрофункция для выявления повторов внутри ячейки

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

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

Sub Delete_Duplicates()
    Dim col As New Collection
    Dim i As Integer
    On Error Resume Next

    For Each cell In Selection
        Set col = Nothing
        sResult = ""
        
        'делим текст в ячейке по пробелам
        arWords = Split(WorksheetFunction.Trim(cell.Value), " ")
        
        'проходим в цикле по всем получившимся словам
        For i = LBound(arWords) To UBound(arWords)
            Err.Clear                           'сбрасываем ошибки
            col.Add arWords(i), arWords(i)      'пробуем добавить слово в коллекцию
            'если ошибки не возникает, то это не повтор - добавляем слово к результату
            If Err.Number = 0 Then sResult = sResult & " " & arWords(i)
        Next i
        cell.Value = Trim(sResult)      'выводим результаты без повторов
    Next cell
End Sub

Способ 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 или новее):

Загружаем таблицу в Power Query

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

Окно 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) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):

Извлекаем имена и склеиваем через пробел

=Text.Combine([Ячейки][Имена]," ")

Здесь выражение [Ячейки][Имена] извлекает содержимое столбца Имена из каждой таблицы в колонке Ячейки, а функция Text.Combine склеивает затем их все через заданный разделитель (пробел). После нажатия на ОК мы, наконец, увидим желаемое:

Развернутые списки

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

Результаты

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

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




19.06.2019 04:29:40
Великолепно! Больше статей о работе в Power Query
24.06.2019 11:45:23
Отличная техника в подходе с Power Query
Спасибо
24.06.2019 12:11:37
Кстати, Николай, может сделаете статью на тему "самоссылающихся" таблиц Power Query. На форуме к таким вещам интерес всплывает время от времени, а как спросят что да как, на ссылку к товарищу Мэтту реагируют что чой-та не очень понятно. Не хочет наш народ языки учить. :D
Хотя инструмент, на мой взгляд, просто огонь, очень до фига всего можно таким приемом реализовать интересного.
26.06.2019 18:22:47
Спасибо за наводку, Алексей - сейчас изучим вопрос :)
26.06.2019 12:53:10
Всем  здорово. я новичок в  макросе и хотел спросить:
пример:
KZ466010002003797659
KZ166010002010329694
KZ47563048PN00408546
KZ715630899GG0056207
Это счета банков клиентов.  мне нужно  разделить их по цветам и потом  через  сортировка  по цветам ячейки  сделаю   сортировку. мне нужно написать код в макросе

если  в ячейке есть  значение 1000200  или 1000201 то эту ячейка  должна быть  зеленой
если  в ячейке есть  значение PN   то эту ячейка  должна быть  синим.
можете  помочь?
26.06.2019 14:55:25
С вопросами не относящимися к статье рекомендую обращаться на форум, там много добрых людей, и вам наверняка помогут.
26.06.2019 18:24:30
Для этого макросы не нужны :) Выделяете ваши данные, затем Главная - Условное форматирование - Правила выделения ячеек - Текст содержит
И вводите ваши значения и выбираете цвет для ячеек.
И все :)
27.06.2019 06:31:24
я знаю это просто  хотелось вообще  упростить задачу. я отправляю деньги к 8000 клиентам. и там много  разных банков.
можете  подсказать как это пишется в макросе->если  в ячейке есть  значение 1000200  или 1000201 то эту ячейка  должна быть  зеленой
остальным  ЕСЛИ уже сам сделаю.

но спасибо  за ваши уроки  мне очень помогли ваши уроки про  впр,  суммесли, счетесли. зная эти формулы  моя работа стала намного проще.
27.06.2019 11:48:29
Если макросом, то как-то так:
for each cell in Range("A1:A100")
if instr(1,cell,"1000200") then cell.Interior.ColorIndex = 4
next cell
17.07.2019 14:01:28
Можете еще воспользовать паттернами из RegExp, а далее через "Условное форматирование" - "Выделение цветом".
01.08.2019 10:48:54
Добрый день! Скажите пожалуйста есть ли возможность выявления и выделения дубликатов на нескольких листах? Кроме того, таблицы сделаны так, что иногда и размер ячеек не совпадает (объединены несколько строк). Что можно тут сделать? Я сам работаю в системе лесного хозяйства и нужно исключить повторение вот по такой таблице.

2017 год - 1лист

АльшеевскоеМиякибашевское5820
АльшеевскоеМиякинское3929
АльшеевскоеМиякинское4526
АльшеевскоеМиякинское905
АльшеевскоеМиякинское9012
2018 год -2 лист
УчалинскоеИльтибановское14123
УчалинскоеИльтибановское14231
УчалинскоеИльтибановское14215
УчалинскоеИльтибановское14125
2019 год - 3 лист
УчалинскоеИльтибановское14123
УчалинскоеИльтибановское14231
УчалинскоеИльтибановское14215
Вот в 2018 и 2019 годах есть повторение, можно ли это выявить?

Спасибо за любой ответ))
21.08.2019 15:16:48
Ильдар, это статья про дубликаты внутри ячеек, а у вас задача про поиск дубликатов в разных ячейках (таблицах) и на разных листах - это совсем другое дело. Гляньте вот эту статью, должно помочь.
20.08.2019 15:05:24

с помощью формулы СЧЁТЕСЛИ(B2:G2;B2:G2) вычисляем по очереди количество вхождений каждого имени в диапазон B2:G2 и получаем на выходе массив {1, 2, 1, 2, 1}
Если применить формулу "{=СЧЁТЕСЛИ(B2:G2;B2:G2)}" то в ячейке отображается "1". Почему отображается "1", а не {1, 2, 1, 2, 1}?
21.08.2019 15:14:16
Потому что так работают формулы массива :)
Если вы вводите формулу в одну ячейку, а она на выходе выдает несколько значений, то вы увидите только первое.
Если хотите увидеть все значения, то нужно перед вводом вашей формулы выделить сразу несколько пустых ячеек.
29.10.2019 14:23:59
Подскажите, а как макросом реализовать, чтобы остались только значения у которых не было дубликатов?
Наверх