Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск ближайшей похожей строки без макроса
 
#1
28.09.2017 20:43:35
Доброго вечера, уважаемые знатоки экселя!

Прошу помочь в следующем вопросе.
Файл с примером приложил.
К примеру, у меня в ячейке "А2" есть строка: "Футбольный клуб Барселона".
Мне необходимо, чтобы при вставке в соседнюю ячейку похожего текста, похожий автоматически менялся на шаблонный. То есть, если я впишу с ошибкой "Футбольный клаб Барсилона", чтобы это предложение заменялось на "Футбольный клуб Барселона".

В процессе поиска решения данной задачи нашел несколько пользовательских функций, которые помогают сделать примерно то, что нужно. Но обнаружил один минус - очень сильно нагружается ОЗУ, хотя строк массива, в котором необходимо искать похожую искомой строку, не более 300.
Насколько я понял в ходе поиска, данную задачу можно решить при помощи ВПР, то есть, без макроса, что, как я понимаю, должно работать быстрее и с меньше загрузкой ОЗУ.

Подскажите, пожалуйста, можно ли решить данную задачу без макроса? Буду благодарен и за помощь с кодом, и за помощь со ссылкой на ресурс, где можно увидеть похожее решение.
Заранее спасибо!
 
 
 
#2
28.09.2017 20:52:27
footballplayer, добрый и Вам. Может через Проверку данных не давать возможность вводить названия с ошибками? Попробуйте в столбце В (выделил цветом ).
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#3
28.09.2017 21:01:17
Bema,спасибо за ответ!

Это немного не то, что нужно :-)
Скорее, всего, я не очень понятно расписал вопрос)
Суть вопроса:

в ячейку "В2" будет вставляться строка, примерно похожая на одну из строк в диапазоне "А2:А6". Мне необходимо, чтобы строка из  ячейки "В2" проверялась на совпадение с одной из строк в диапазоне "А2:А6" и, если есть примерно совпадение, то в ячейке "С2" вставлялось значение из диапазона "А2:А6", которое максимально похожу на строку из ячейки "В2".
Такой функционал реализован в функциях FuzzySearch, который доступен в интернете, но он очень сильно грузит ПК.
 
 
 
#4
28.09.2017 21:05:04
footballplayer, я понял что Вам нужно, но предложил альтернативное решение, т.к. Ваша задача, на мой взгляд, формулами не решаема. Может выпадающие списки?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#5
28.09.2017 21:15:23
Bema, сказать честно, в формулах совсем не силен. В макросах более или менее начинаю понимать.
Вычитав различные ресурсы в процессе поиска решения и увидев, что предлагается заменить макрос на ПОИСКПОЗ, подумал, что все же можно обойтись формулами :) Но сейчас просматривал ПОИСКПОЗ и примерно понял, что это функция не отработает так, как надо.
Как Вы говорите, наверное, придется пользоваться макросом)
 
 
 
#6
28.09.2017 21:36:14
Цитата
footballplayer написал: Как Вы говорите, наверное, придется пользоваться макросом)
Я так не говорил ;) . Макросы не пишу, как же я могу их рекомендовать. Выпадающие списке не рассматриваете как альтернативное решение Вашей задачи?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#7
28.09.2017 21:47:28
Bema, сказать честно, практически ничего не знаю про выпадающие списки. Могли бы Вы на примере показать, как это будет выглядеть?
С удовольствием воспользовался бы этим способом )
 
 
 
#8
28.09.2017 21:54:30
Выпадающий список в ячейке листа
Видео от автора сайта.
В Приемах есть раздел посвященный Выпадающим спискам
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#9
28.09.2017 21:59:18
Можно, если есть четкий критерий определения "похожести".
К сожалению, у человека богатый опыт, практически не реализуемый алгоритмически. Одна интуиция чего стоит! А вот поди объясни: почему они похожи?

И, что обидно для человека, ложные срабатывания интуиции тоже есть. И "похожесть" порою оказывается кажущейся. Омонимы, например. Кто знает, что конкретно она имела в виду?

Так что, думаю, и FuzzySearch безгрешно работать не будет.

Что как выход. Предлагаю напрячься и постараться набрать максимум ключей для поиска подстроки. На отдельном листе напротив списка таких ключей расположить список того, что должно отображаться. Получится словарик.

Дальше можно реализовать формулами точный поиск (ПОИСКПОЗ, ВПР и т.п.), а можно по подстроке формулами массива. Если появляется очепятка, которой не было, можно добавлять ее в словарик (хоть программно, хоть ручками).

В файле реализовано по поиску подстроки.

Выбирайте: кого грузить: память или быстродействие...
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#10
28.09.2017 22:15:53
Bema, спасибо за примеры. Но, опять же, вводимое искомое значение часто будет не идентично и с ошибками в буквах. Позволит ли такой способ обработать строку и подобрать подходящую строку из заранее заготовленных значений?
 
 
 
#11
28.09.2017 22:20:53
PerfectVam, спасибо за ответ!
Вы правы, необходимы четкие критерии "похожести".
Как я себе представляю, критерий схожести - это максимальное количество символов подряд во всех словах строки.
К примеру, если взять строку БК Барслноа и сравнить ее с массивом из двух значение - ФК Барселона и ФК Ювентус, то должно выдать ФК Барселона, т.к. тут больше символов идущих подряд в двух строках.
 
 
 
#12
28.09.2017 22:21:23
footballplayer, я Вам советую не исправлять ошибки, а предотвращать их появление разными способами.
1. Не дать возможность ввести ошибочное значение.
2. Выбирать значения из предопределённого списка.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#13
28.09.2017 22:39:05
Поддерживаю Bema.

Для Вашего последнего примера Вы дали выбор из двух(!!!) вариантов. А этих вариантов будет 300!
Это Вам глазами видно сразу, а программе (формуле) надо перелопатить и посравнивать разные сочетания букв. Комбинаторику помните? С увеличением на один символ количество вариантов возрастает экспоненциально! Потому FuzzySearch память и жрет. Там оптимизировано, конечно, но оптимизация небесконечна, если результата все же достигнуть надо.

Есть реализации выпадающих списков (без макросов), еще краше формы или комбибоксы с макросами. В последних можно реализовать поиск и отсечение вариантов в процессе набора.

Согласен с Bema - это лучше будет...

Это мое мнение. Вам решать, что для Вас действительно лучше.
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#14
28.09.2017 22:46:50
Bema,  PerfectVam, спасибо за ответ.
Да, согласен с Вами, каждое новое значение приводит к дополнительной необходимости в памяти.
Реализовать выпадающие списки,  к сожалению, не представляется возможным, т.к. данных с большой долей вероятности будут с небольшими, но ошибками. В связи с этим и появилась необходимость в поиске максимально похожей строки к искомой. Буду думать дальше)
 
 
 
#15
28.09.2017 22:55:55
Цитата
footballplayer написал:
Реализовать выпадающие списки,  к сожалению, не представляется возможным, т.к. данных с большой долей вероятности будут с небольшими, но ошибками
Выпадающий список позволяет выбирать данные из списка, а не вводить вручную. Это исключает возможность ошибки. Т.е. у Вас будет один столбец, в ячейках которого пользователь выбирает названия клубов.
И как уже написал PerfectVam,
Цитата
PerfectVam написал:
Вам решать, что для Вас действительно лучше.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#16
28.09.2017 23:05:15
Цитата
Bema написал:
Выпадающий список позволяет выбирать данные из списка, а не вводить вручную. Это исключает возможность ошибки
Дополню: можно и вручную вводить, но настройки не дадут ввести ошибочное значение.
 
 
 
#17
29.09.2017 09:02:41
footballplayer, здравствуйте! И почему Вы так упорно отказываетесь от выпадающих списков? Они ведь полностью исключают ввод ошибочных данных. Может, сначала попробуете их в работе? (см. пример). Если клубов очень много - для облегчения выбора можно сделать взаимозависимые списки (например, разбить по странам). Другое дело - если данные вставляются откуда-то программно (с сайта, из другого файла и т.п., этот момент Вы не уточнили, просто пишете, что будет вставляться строка - а как и откуда она будет вставляться?). Для ручного ввода лучше списки, для автоматического - макрос.
 
 
 
#18
01.10.2017 13:13:42
_Igor_61,добрый день! Спасибо за ответ!

Да, Вы правы, мой недочет, не указал при создании темы, что данные будут поступать программно, из другого файла. Эти данные частично могут быть и, скорее всего, будут с какими-либо ошибками. В связи с этим, метод с выпадающими списками не подойдет. В случае ручного ввода данных этот метод был бы идеальным.
Видимо, обойтись в данном случае можно только макросом)
 
 
 
#19
01.10.2017 13:23:13
Подскажите, пожалуйста, а есть ли аналоги макрос FuzzySearch? Нашел макрос CompareTxt, но он практически не исправляет некорректно написанное значение по словарю.
 
 
 
Страницы: 1
Читают тему
Наверх