Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Отличия в один символ - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Отличия в один символ
okorinalaz Дата: Четверг, 01.02.2024, 01:24 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

2023
Добрый день!
Подскажите пожалуйста, есть ли формула, которая поможет найти в столбце все дубликаты или неявные дубликаты, т.е. те значения, которые отличаются только на один символ.
Например:
яблоко / мальтийское
яблоко мальтийское
яблоко, мальтийское

Уже весь интернет перерыла и формулы...


Екатерина
 
Ответить
СообщениеДобрый день!
Подскажите пожалуйста, есть ли формула, которая поможет найти в столбце все дубликаты или неявные дубликаты, т.е. те значения, которые отличаются только на один символ.
Например:
яблоко / мальтийское
яблоко мальтийское
яблоко, мальтийское

Уже весь интернет перерыла и формулы...

Автор - okorinalaz
Дата добавления - 01.02.2024 в 01:24
Gustav Дата: Четверг, 01.02.2024, 02:55 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В порядке лёгкого эксперимента, например, такое можно замутить. Берете наиболее короткую строку, можно очистить ее от знаков препинания и пробелов. И вставляете в нее звёздочки * вокруг всех букв. Следующие формулы находят совпадение с результатом 1:
Код
=ПОИСК("*я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*"; "яблоко, мальтийское")

Код
=ПОИСК("*я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*"; "яблоко / мальтийское")

А вообще с подобными вопросами - это в тему про нечеткий поиск: Простая метрика сходства текстовых строк. Правда, в ней можно раствориться... %)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВ порядке лёгкого эксперимента, например, такое можно замутить. Берете наиболее короткую строку, можно очистить ее от знаков препинания и пробелов. И вставляете в нее звёздочки * вокруг всех букв. Следующие формулы находят совпадение с результатом 1:
Код
=ПОИСК("*я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*"; "яблоко, мальтийское")

Код
=ПОИСК("*я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*"; "яблоко / мальтийское")

А вообще с подобными вопросами - это в тему про нечеткий поиск: Простая метрика сходства текстовых строк. Правда, в ней можно раствориться... %)

Автор - Gustav
Дата добавления - 01.02.2024 в 02:55
Gustav Дата: Четверг, 01.02.2024, 17:47 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.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). Три условия в "Пользовательский автофильтр" не влезут, но легко влезут в формулу, например, такую:
Код
=ИЛИ(ЕЧИСЛО(ПОИСК("*я*о*м*ь*й*о*"; A1)); ЕЧИСЛО(ПОИСК("*б*к*а*т*с*е*"; A1)); ЕЧИСЛО(ПОИСК("*л*о*л*и*к*"; A1)))

Формулу можно поместить в допстолбец рядом с проверяемым и фильтровать его по значению ИСТИНА.

Вместо указанной выше можно применить формулу покороче. Плюс в ней условия расположены к друг другу поближе и понагляднее:
Код
=СУММ(--ЕЧИСЛО(ПОИСК( {"*я*о*м*ь*й*о*"; "*б*к*а*т*с*е*"; "*л*о*л*и*к*"}; A1))) > 0

И, наконец, если убрать сравнение суммы с 0, то появляется как бы количественная мера совпадения сравниваемых строк - от 1 до 3 в зависимости от количества успешных ПОИСКов из трёх имеющихся внутри формулы. При желании этим тоже как-то можно воспользоваться.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВ продолжение разговора. Для поиска неявных дубликатов полученную строку со звездочками можно применить в автофильтре, задав "Текстовые фильтры... содержит…" и наша строка: *я*б*л*о*к*о*м*а*л*ь*т*и*й*с*к*о*е*

А поскольку в опции "Текстовые фильтры -> Настраиваемый фильтр…" предусмотрено два поля ввода условий, то грех этим не воспользоваться. Но что можно ввести в эти два поля? Наверное, разное. Но мы попробуем такой финт: в первое поле введем последовательность из 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). Три условия в "Пользовательский автофильтр" не влезут, но легко влезут в формулу, например, такую:
Код
=ИЛИ(ЕЧИСЛО(ПОИСК("*я*о*м*ь*й*о*"; A1)); ЕЧИСЛО(ПОИСК("*б*к*а*т*с*е*"; A1)); ЕЧИСЛО(ПОИСК("*л*о*л*и*к*"; A1)))

Формулу можно поместить в допстолбец рядом с проверяемым и фильтровать его по значению ИСТИНА.

Вместо указанной выше можно применить формулу покороче. Плюс в ней условия расположены к друг другу поближе и понагляднее:
Код
=СУММ(--ЕЧИСЛО(ПОИСК( {"*я*о*м*ь*й*о*"; "*б*к*а*т*с*е*"; "*л*о*л*и*к*"}; A1))) > 0

И, наконец, если убрать сравнение суммы с 0, то появляется как бы количественная мера совпадения сравниваемых строк - от 1 до 3 в зависимости от количества успешных ПОИСКов из трёх имеющихся внутри формулы. При желании этим тоже как-то можно воспользоваться.

Автор - Gustav
Дата добавления - 01.02.2024 в 17:47
okorinalaz Дата: Четверг, 01.02.2024, 22:09 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

2023
спасибо огромное...серьезные методы. С этими формулами работала, что странно: на массиве 5000 правильно отрабатывает, а на большем - врет нагло. Правда это формулы простые, типа таких:
=ЕСЛИ(СЧЁТЕСЛИ(A:A; A25564)>1; "дубликат"; "уникальное")

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


Екатерина

Сообщение отредактировал okorinalaz - Четверг, 01.02.2024, 22:48
 
Ответить
Сообщениеспасибо огромное...серьезные методы. С этими формулами работала, что странно: на массиве 5000 правильно отрабатывает, а на большем - врет нагло. Правда это формулы простые, типа таких:
=ЕСЛИ(СЧЁТЕСЛИ(A:A; A25564)>1; "дубликат"; "уникальное")

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

Автор - okorinalaz
Дата добавления - 01.02.2024 в 22:09
Gustav Дата: Четверг, 01.02.2024, 22:26 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
okorinalaz, а как Вы вообще себе представляете процедуру в целом? Допустим, у Вас есть некое идеальное средство (формула или макрос) сравнения двух текстовых строк. Дальше что?

С автофильтром мне понятно: вставляете строку-шаблон в него и получаете список совпадений. Но такую процедуру надо проделать для всех строк списка - т.е. каждая строка списка должна поочередно выступить в роли шаблона и собрать "на себя" несколько строк совпадений из остального списка. В результате у Вас исходный, допустим ВЕРТИКАЛЬНЫЙ, список и справа от его столбца ГОРИЗОНТАЛЬНЫЕ списки синонимов для каждого значения, допустим их от одного до 10 может быть.

Что дальше с этим, "лохматым справа", хозяйством можно делать? Как получить желаемый список?


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщениеokorinalaz, а как Вы вообще себе представляете процедуру в целом? Допустим, у Вас есть некое идеальное средство (формула или макрос) сравнения двух текстовых строк. Дальше что?

С автофильтром мне понятно: вставляете строку-шаблон в него и получаете список совпадений. Но такую процедуру надо проделать для всех строк списка - т.е. каждая строка списка должна поочередно выступить в роли шаблона и собрать "на себя" несколько строк совпадений из остального списка. В результате у Вас исходный, допустим ВЕРТИКАЛЬНЫЙ, список и справа от его столбца ГОРИЗОНТАЛЬНЫЕ списки синонимов для каждого значения, допустим их от одного до 10 может быть.

Что дальше с этим, "лохматым справа", хозяйством можно делать? Как получить желаемый список?

Автор - Gustav
Дата добавления - 01.02.2024 в 22:26
Gustav Дата: Четверг, 01.02.2024, 22:37 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2763
Репутация: 1140 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
серьезные методы

Да у меня-то что! На ходу импровизация. Вот если в тему заглянете, ссылку на которую я дал выше в сообщении № 2, то там гораздо всё серьезнее...

вместо яблок то разные слова,

У меня ж функция параметрическая, с любыми словами работает. "Яблоки" там в комментариях - как пример того, что в результате будет получаться. Т.е. функция не только яблоки "ест" :)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
серьезные методы

Да у меня-то что! На ходу импровизация. Вот если в тему заглянете, ссылку на которую я дал выше в сообщении № 2, то там гораздо всё серьезнее...

вместо яблок то разные слова,

У меня ж функция параметрическая, с любыми словами работает. "Яблоки" там в комментариях - как пример того, что в результате будет получаться. Т.е. функция не только яблоки "ест" :)

Автор - Gustav
Дата добавления - 01.02.2024 в 22:37
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!