Коэффициент похожести

[Konstantin_Z]

Наверняка Вам (тем, кто много работает с данными в Эксель, а особенно в торговле) приходилось сталкиваться с проблемой, когда один и тот же товар называют несколькими разными способами. Каждый по разному выходить из ситуации. Кто присваивает коды, кто составляет т.н. таблицы соответствий. Я вот, в силу определенных причин избрал второй вариант.
То есть у меня существует эталонное название, а также "синонимы" этого названия.

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

вот формула:

=МИН(СУММПРОИЗВ(КОДСИМВ(ПСТР(ПРОПИС Н(C68);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(C68 )));1)))*КОДСИМВ(ПРОПИСН(ЛЕВСИМВ(C6 8)));СУММПРОИЗВ(КОДСИМВ(ПСТР(ПРОПИС Н(D68);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(D68 )));1)))*КОДСИМВ(ПРОПИСН(ЛЕВСИМВ(D6 8))))/МАКС(СУММПРОИЗВ(КОДСИМВ(ПСТР(ПРОПИС Н(C68);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(C68 )));1)))*КОДСИМВ(ПРОПИСН(ЛЕВСИМВ(C6 8)));СУММПРОИЗВ(КОДСИМВ(ПСТР(ПРОПИС Н(D68);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(D68 )));1)))*КОДСИМВ(ПРОПИСН(ЛЕВСИМВ(D6 8))))

понятно, что сравниваемые тексты, в данном случае находятся в C68 и D68.

Формула делает следующее:
1. суммирует все коды символов первой ячейки
2. то что получилось умножается на код первого символа

то же для второй ячейки

Далее находиться отношения минимума к максимуму.


Хотелось бы услышать конструктивную критику экспертов по:
1. усовершенствованию формулы
2. упрощению формулы

[Konstantin_Z]

для простоты все символы делаются прописными, т.е. формула, как говориться "не чувствительна к регистру"

[Serge_Bliznykov]

извините, Константин, но разобрать такую формулу нет времени и сил.
Поэтому просто выскажу пару мыслей вслух.. :-)
1) ну, насчёт регистра - это беспорно обязательно приводить к единому "знаменателю"
2) очень неплохо при сравнении выбрасывать все пробелы (или, если задача такое не позволяет и, например, "из ада" и "и зада" разные названия, то хотя бы несколько подряд идущих пробелов заменять на один)
3) для английского языка действует функция SOUNDEX
(почитать можно - тут - Soundex — Википедия - там же есть ссылки на попытки это сделать для русского языка)

но, имхо, это всё это не решение проблемы. Только строжайший контроль + выбор значений из справочников на этапе ввода данных могут помочь избавиться от подобной проблемы...

и позвольте небольшое лирическое отступление - как-то занимался автоматизацией - понадобилось реструктуризировать БД потребителей - из адреса, написанного "ручками" выделить название улицы (переулка/бульвара и т.д.), номер дома, строение, индекс, квартиру...
Это потребовало около двух (подчёркиваю ДВУХ) недель на обработку 100 тыс. записей. 90% работы было ручное.
Например, название улицы "Дзержинского" было написано 14 (четырнадцатью) различными вариантами...

Поэтому, как гласит один из законов Мерфи:
"Защиту от дурака можно сделать, но только от дурака неизобретательного!"

[Konstantin_Z]

формула то простая
тем более, что я написал, что она делает

Спасибо, Serge_Bliznykov, Ваше мнение, как несомненного профи, для меня очень важно.

[Konstantin_Z]

Приглашаю высказаться и других экспертов

[IgorGO]

тема древняя. всплывает тут раз в неделю.
Я понимаю, что делает формула, но не понимаю вложенной в нее логики.
написал в ячейки
"WL520gc" и "Роутер WL520gc"
мой мозг понимает, что это одно и то же, причем это 100% совпадение, без малейших сомнений.
а формула выдает коэффициент схожести 0,109662235.

Мое мнение - только ВБА, причем довольно неоднозначно.
Хорошая мысль вычилять коэффициент от 0 до 1.

[Konstantin_Z]

IgorGO
Как же это так "100% совпадение" ??
Ведь во втором случае в ячейке на 1 слово и пробел больше...
соответственно сумма кодов всех символов у второго варианта больше, соответственно, имеем 0,10966...

[IgorGO]

100% совпадение, это значит, что я на 100% уверен что в разных прайсах речь идет об одном и том же товаре. Вот такие 100%.

[Serge_Bliznykov]

я полностью согласен с IgorGO.
Поймите, что в 99% случаев без человеческого участия просто не обойтись ;-( Ваш подход позволяет лишь помочь человеку найти похожие... А дальше ручками-глазками-мозгом оператора надо пользоваться... Без человека - никуда... ;-(

вот, посмотрите:

0.46107963	Тройной	zW01 Роутер
0.441648423	Роутер zW01	zW01 Роутер

одно и то же (переставлены слова) совпадает на 44%
а "тройной" совпал на 46% :-)

а как Вам такой казус?:

0.815784939	ТРОН	zW01 Роутер

а такой?:

0.15199035	ТРОН	TPOH

Так что - как ВСПОМОГАТЕЛЬНОЕ (для человека) средство Вашу формулу использовать можно. Но вот строить пары (или объединять значения) на основании результатов её работы - я бы лично поостерёгся.. ;-(

удачи!

[Konstantin_Z]

я ж об этом же! как раз речь о том, что добавить/удалить/изменить в формуле "коэффициента похожести", чтобы он считался более корректно?

я ж ее и выложил для того, чтобы Вы высказывались на тему как ее улучшить.

Бесспорно, что после обработки формулами (в любом случае!) придется немного покопаться руками.