Добрый день! Подскажите пожалуйста, есть ли формула, которая поможет найти в столбце все дубликаты или неявные дубликаты, т.е. те значения, которые отличаются только на один символ. Например: яблоко / мальтийское яблоко мальтийское яблоко, мальтийское
Уже весь интернет перерыла и формулы...
Добрый день! Подскажите пожалуйста, есть ли формула, которая поможет найти в столбце все дубликаты или неявные дубликаты, т.е. те значения, которые отличаются только на один символ. Например: яблоко / мальтийское яблоко мальтийское яблоко, мальтийское
В порядке лёгкого эксперимента, например, такое можно замутить. Берете наиболее короткую строку, можно очистить ее от знаков препинания и пробелов. И вставляете в нее звёздочки * вокруг всех букв. Следующие формулы находят совпадение с результатом 1:
В порядке лёгкого эксперимента, например, такое можно замутить. Берете наиболее короткую строку, можно очистить ее от знаков препинания и пробелов. И вставляете в нее звёздочки * вокруг всех букв. Следующие формулы находят совпадение с результатом 1:
В продолжение разговора. Для поиска неявных дубликатов полученную строку со звездочками можно применить в автофильтре, задав "Текстовые фильтры... содержит…" и наша строка: *я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*
А поскольку в опции "Текстовые фильтры -> Настраиваемый фильтр…" предусмотрено два поля ввода условий, то грех этим не воспользоваться. Но что можно ввести в эти два поля? Наверное, разное. Но мы попробуем такой финт: в первое поле введем последовательность из 1-й, 3-й, 5-й букв и т.д. с шагом 2 до конца строки, во второе поле введем последовательность из 2-й, 4-й, 6-й букв и т.д. с шагом 2 до конца строки. Разумеется, буквы в обоих полях, как и раньше, окружим звездочками *. Для соединения условий выберем опцию ИЛИ.
В итоге этих манипуляций информация диалога "Пользовательский автофильтр" будет выглядеть так: [vba]
Код
Показать только те строки, значения которых: содержит *я*л*к*м*л*т*й*к*е* ИЛИ содержит *б*о*о*а*ь*и*с*о*
[/vba]
Преимущество фильтра с двумя условиями заключается, например, в том, что теперь будут отобраны и значения с ошибочно переставленными (при опечатке) соседними буквами: если вы торопились и вместо "яблоко" напечатали "ялбоок", то фильтр это поймает!
При указанном подходе возникает технологический вопрос: как комфортно подготовить эти шаблоны со звездочками? Никому не хочется делать это вручную. Мне тоже и поэтому я быстренько сварганил соответствующую пользовательскую функцию (UDF): [vba]
Код
Function keepLettersOnly(ByVal txt, ByVal typ) Dim str, i, fstart, fstep With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[^a-zёа-я]" str = .Replace(LCase(txt), "") End With Select Case typ 'яблоко мальтийское
'при поиске по двум условиям Case 1: fstart = 1: fstep = 2 '*я*л*к*м*л*т*й*к*е* Case 2: fstart = 2: fstep = 2 '*б*о*о*а*ь*и*с*о*
'при поиске по трем условиям Case 3: fstart = 1: fstep = 3 '*я*о*м*ь*й*о* Case 4: fstart = 2: fstep = 3 '*б*к*а*т*с*е* Case 5: fstart = 3: fstep = 3 '*л*о*л*и*к*
'при поиске по одному условию Case Else fstart = 1: fstep = 1 '*я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е* End Select keepLettersOnly = "*" For i = fstart To Len(str) Step fstep keepLettersOnly = keepLettersOnly & Mid(str, i, 1) & "*" Next i End Function
[/vba] Как видно из текста функции, в ней уже предусмотрена подготовка не только двух ИЛИ-условий (при параметре typ = 1, 2), но и трех (при параметре typ = 3, 4, 5). Три условия в "Пользовательский автофильтр" не влезут, но легко влезут в формулу, например, такую:
И, наконец, если убрать сравнение суммы с 0, то появляется как бы количественная мера совпадения сравниваемых строк - от 1 до 3 в зависимости от количества успешных ПОИСКов из трёх имеющихся внутри формулы. При желании этим тоже как-то можно воспользоваться.
В продолжение разговора. Для поиска неявных дубликатов полученную строку со звездочками можно применить в автофильтре, задав "Текстовые фильтры... содержит…" и наша строка: *я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*
А поскольку в опции "Текстовые фильтры -> Настраиваемый фильтр…" предусмотрено два поля ввода условий, то грех этим не воспользоваться. Но что можно ввести в эти два поля? Наверное, разное. Но мы попробуем такой финт: в первое поле введем последовательность из 1-й, 3-й, 5-й букв и т.д. с шагом 2 до конца строки, во второе поле введем последовательность из 2-й, 4-й, 6-й букв и т.д. с шагом 2 до конца строки. Разумеется, буквы в обоих полях, как и раньше, окружим звездочками *. Для соединения условий выберем опцию ИЛИ.
В итоге этих манипуляций информация диалога "Пользовательский автофильтр" будет выглядеть так: [vba]
Код
Показать только те строки, значения которых: содержит *я*л*к*м*л*т*й*к*е* ИЛИ содержит *б*о*о*а*ь*и*с*о*
[/vba]
Преимущество фильтра с двумя условиями заключается, например, в том, что теперь будут отобраны и значения с ошибочно переставленными (при опечатке) соседними буквами: если вы торопились и вместо "яблоко" напечатали "ялбоок", то фильтр это поймает!
При указанном подходе возникает технологический вопрос: как комфортно подготовить эти шаблоны со звездочками? Никому не хочется делать это вручную. Мне тоже и поэтому я быстренько сварганил соответствующую пользовательскую функцию (UDF): [vba]
Код
Function keepLettersOnly(ByVal txt, ByVal typ) Dim str, i, fstart, fstep With CreateObject("VBScript.RegExp") .Global = True .Pattern = "[^a-zёа-я]" str = .Replace(LCase(txt), "") End With Select Case typ 'яблоко мальтийское
'при поиске по двум условиям Case 1: fstart = 1: fstep = 2 '*я*л*к*м*л*т*й*к*е* Case 2: fstart = 2: fstep = 2 '*б*о*о*а*ь*и*с*о*
'при поиске по трем условиям Case 3: fstart = 1: fstep = 3 '*я*о*м*ь*й*о* Case 4: fstart = 2: fstep = 3 '*б*к*а*т*с*е* Case 5: fstart = 3: fstep = 3 '*л*о*л*и*к*
'при поиске по одному условию Case Else fstart = 1: fstep = 1 '*я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е* End Select keepLettersOnly = "*" For i = fstart To Len(str) Step fstep keepLettersOnly = keepLettersOnly & Mid(str, i, 1) & "*" Next i End Function
[/vba] Как видно из текста функции, в ней уже предусмотрена подготовка не только двух ИЛИ-условий (при параметре typ = 1, 2), но и трех (при параметре typ = 3, 4, 5). Три условия в "Пользовательский автофильтр" не влезут, но легко влезут в формулу, например, такую:
И, наконец, если убрать сравнение суммы с 0, то появляется как бы количественная мера совпадения сравниваемых строк - от 1 до 3 в зависимости от количества успешных ПОИСКов из трёх имеющихся внутри формулы. При желании этим тоже как-то можно воспользоваться.Gustav
спасибо огромное...серьезные методы. С этими формулами работала, что странно: на массиве 5000 правильно отрабатывает, а на большем - врет нагло. Правда это формулы простые, типа таких: =ЕСЛИ(СЧЁТЕСЛИ(A:A; A25564)>1; "дубликат"; "уникальное")
но теперь...пока запуталась еще больше...вместо яблок то разные слова, точнее наименования компьютерных комплектующих...где бывает и в два-три-четыре слова. Много дублей, но дубли с отличием в один символ... неужели все-таки макрос ваять? (( и на моем массиве виснет даже формулой сравнение....оперативки не хватает((
спасибо огромное...серьезные методы. С этими формулами работала, что странно: на массиве 5000 правильно отрабатывает, а на большем - врет нагло. Правда это формулы простые, типа таких: =ЕСЛИ(СЧЁТЕСЛИ(A:A; A25564)>1; "дубликат"; "уникальное")
но теперь...пока запуталась еще больше...вместо яблок то разные слова, точнее наименования компьютерных комплектующих...где бывает и в два-три-четыре слова. Много дублей, но дубли с отличием в один символ... неужели все-таки макрос ваять? (( и на моем массиве виснет даже формулой сравнение....оперативки не хватает((okorinalaz
Екатерина
Сообщение отредактировал okorinalaz - Четверг, 01.02.2024, 22:48
okorinalaz, а как Вы вообще себе представляете процедуру в целом? Допустим, у Вас есть некое идеальное средство (формула или макрос) сравнения двух текстовых строк. Дальше что?
С автофильтром мне понятно: вставляете строку-шаблон в него и получаете список совпадений. Но такую процедуру надо проделать для всех строк списка - т.е. каждая строка списка должна поочередно выступить в роли шаблона и собрать "на себя" несколько строк совпадений из остального списка. В результате у Вас исходный, допустим ВЕРТИКАЛЬНЫЙ, список и справа от его столбца ГОРИЗОНТАЛЬНЫЕ списки синонимов для каждого значения, допустим их от одного до 10 может быть.
Что дальше с этим, "лохматым справа", хозяйством можно делать? Как получить желаемый список?
okorinalaz, а как Вы вообще себе представляете процедуру в целом? Допустим, у Вас есть некое идеальное средство (формула или макрос) сравнения двух текстовых строк. Дальше что?
С автофильтром мне понятно: вставляете строку-шаблон в него и получаете список совпадений. Но такую процедуру надо проделать для всех строк списка - т.е. каждая строка списка должна поочередно выступить в роли шаблона и собрать "на себя" несколько строк совпадений из остального списка. В результате у Вас исходный, допустим ВЕРТИКАЛЬНЫЙ, список и справа от его столбца ГОРИЗОНТАЛЬНЫЕ списки синонимов для каждого значения, допустим их от одного до 10 может быть.
Что дальше с этим, "лохматым справа", хозяйством можно делать? Как получить желаемый список?Gustav
У меня ж функция параметрическая, с любыми словами работает. "Яблоки" там в комментариях - как пример того, что в результате будет получаться. Т.е. функция не только яблоки "ест"
У меня ж функция параметрическая, с любыми словами работает. "Яблоки" там в комментариях - как пример того, что в результате будет получаться. Т.е. функция не только яблоки "ест" Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
