Дубликаты внутри ячейки
Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):
Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать.
Способ 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 и получаем на выходе массив {1,2,1,2,1}, т.к. Иван встречается в первой строке 1 раз, Елена - 2 раза, Сергей - 1 и т.д.
- Проверяем с помощью СЧЁТЕСЛИ(B2:G2;B2:G2)>1 какие из полученных чисел больше единицы, т.е. где у нас повторы. На выходе эта формула выдаст нам массив результатов проверки в виде {ЛОЖЬ, ИСТИНА, ЛОЖЬ, ИСТИНА, ЛОЖЬ}.
- Переводим логические значения ЛОЖЬ и ИСТИНА в более удобные для подсчета 0 и 1, соответственно, с помощью функции Ч. На выходе получаем массив {0,1,0,1,0}.
- Суммируем все элементы получившегося массива функцией СУММПРОИЗВ. Можно было бы использовать и обычную функцию СУММ, но тогда пришлось бы жать вместо привычного 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 или новее):
Поверх окна 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) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):
=Text.Combine([Ячейки][Имена]," ")
Здесь выражение [Ячейки][Имена] извлекает содержимое столбца Имена из каждой таблицы в колонке Ячейки, а функция Text.Combine склеивает затем их все через заданный разделитель (пробел). После нажатия на ОК мы, наконец, увидим желаемое:
Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to..):
Задача решена! Если в будущем данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl+Alt+F5.
Ссылки по теме
- Как найти и вывести отличия в двух списках в Excel
- Как подсветить совпадающие пары дубликатов одинаковым цветом
- Сравнение двух таблиц с помощью ВПР, сводной или Power Query
Спасибо
Хотя инструмент, на мой взгляд, просто огонь, очень до фига всего можно таким приемом реализовать интересного.
пример:
KZ466010002003797659
KZ166010002010329694
KZ47563048PN00408546
KZ715630899GG0056207
Это счета банков клиентов. мне нужно разделить их по цветам и потом через сортировка по цветам ячейки сделаю сортировку. мне нужно написать код в макросе
если в ячейке есть значение 1000200 или 1000201 то эту ячейка должна быть зеленой
если в ячейке есть значение PN то эту ячейка должна быть синим.
можете помочь?
И вводите ваши значения и выбираете цвет для ячеек.
И все
можете подсказать как это пишется в макросе->если в ячейке есть значение 1000200 или 1000201 то эту ячейка должна быть зеленой
остальным ЕСЛИ уже сам сделаю.
но спасибо за ваши уроки мне очень помогли ваши уроки про впр, суммесли, счетесли. зная эти формулы моя работа стала намного проще.
for each cell in Range("A1:A100") if instr(1,cell,"1000200") then cell.Interior.ColorIndex = 4 next cell2017 год - 1лист
Спасибо за любой ответ))
Если вы вводите формулу в одну ячейку, а она на выходе выдает несколько значений, то вы увидите только первое.
Если хотите увидеть все значения, то нужно перед вводом вашей формулы выделить сразу несколько пустых ячеек.
Очередной раз с вашей помощью решил поставленную задачу.
Изначально: в ячейке был результат сцепления по другим ячейкам девятизначных чисел с разделителем ", ", числа повторялись, нужна книга без поддержки макросов, поэтому воспользовался вариантом с Power Query, правда, чтобы заработало, пришлось использовать перед числами апостроф, потом из итоговой ячейки его удалять.
Может быть, есть вариант поизящнее, пожалуйста, поделитесь
Николай очен полезная трюк.
Помагите пожалуста как разный наименованный книг привести в одном порядке в Power Query.
Например
VМосква январ
VN Москва феврал
р Новосибир январ.xlsx
Должно быть
Москва
Москва
Новосибир
И каждый раз когда обновляю Power Query, вложенный новый Эксел книг в папку с разным именем региона оставлял толка называние региона
Спосиба вам огромный зарание
Как можно организовать при помощи данного макроса, поиск дублей внутри выделенных ячеек (что бы при выделениии 2 и более ячеек дубли искались внутри ячеек, например в ячейке1 стоит 1, 2, 3, , а в ячейке2 стоит 3, 4, 5, и именно повторяющееся значение 3 подсвечивалось в обеих ячейках)?
подскажите, пожалуйста, в способе 5 разделение столбца по строкам в 2016м экселе присутствует? В справке такой опции нет, в самой функции тоже не могу найти... Или, к сожалению, его можно выкидывать из списка работающих?
Заранее спасибо.
Спасибо!