Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Динамический отсев вводимых значений с учетом возможного частичного совпадения с раннее внесенными данными, Оптимизация работы с БД
 
#1
31.08.2017 16:38:32
Всем тем, кто рискнул заглянуть в эту тему, после столь громкого названия - доброго Вам времени суток! А тем, кто завидев кол-во текста, не закрыл тему после 5 секунд - добро пожаловать в мой маленький дурдом.

Причина поста: сейчас занимаюсь созданием клиентской базы данных по контактам из каталогов одного предприятия за период 5-7 лет. Каждый год выпускались 3 каталога со списками предприятий по сферам деятельности, которые сопряжены с тематикой предприятия. Столкнулся с проблемой повторов компаний из года в год. Хочу оптимизировать затраты времени и сил.

Поставленная цель готовой базы данных в Excel: список компаний с их реквизитами и контактными данными (разнесенными по соответствующим столбцам) БЕЗ повторов по названию, номерам телефона, мейлу, сайту и другим типовым реквизитам.

Цель поиска помощи здесь: оптимизация процесса внесения данных из каталогов в среду Excel с возможностью отсева повторов в процессе внесения данных про каждую компанию в одну таблицу, а не после соединения нескольких таблиц по каждому каталогу.

Поясняю суть проблемы: каждый выпуск в каталогах (над которыми я сейчас работаю) менялся формат подачи реквизитов предприятий и достаточно большое количество предприятий в разных каталогах по факту повторяются (по предварительной оценке от 30% до 60% в разных годах). Если пойти самым простым (и долгим) путем то сначала нужно сформировать таблицу по каждому каталогу отдельно, а потом вертеться с копированием всех каталогов в одну таблицу с последующим "убиванием" дублей.
И если быть ленивым как я, то: нужно настроить работу фильтров, макросов, функций Excel таким образом, чтоб в одну таблицу сразу можно было вводить все данные и Excel посредством сообщений или выделения ячеек цветом "сообщал" о том, что введен "дубль" (хорошо, если даже скажет где он находится), который встречался раннее. НО(!) тут сразу возникает главная проблема поиска решения: так как форма подачи однотипных данных (а иногда и повторяющихся компаний) каждый год  отличалась (допустим за 2014 год была компания "ГП ХЗ "Хлебница", а в 2016м году она же указывалась как "Государственное предприятие "Хлебный завод "Хлебница") усложняется возможность вовремя "заметить" дубль (если первое упоминание про эту компанию было в первой тысяче компаний, а второе - в 12й тысяче компаний общего списка) без должной настройки фильтрации, макросов, группировки данных (чакр, парада планет и всего другого). Та же ситуация касается номеров телефонов: в 2014м году они указывались в виде "26533 226 41", а в 2016м - в виде "+37 (26533) 22-641". Внимание! Даже при настройке сокращения нецифровых символов проблема остается: в первом случае получим 2653322641, а во втором - 372653322641, что не "подсветиться" как дубль и не приведет к "возможности" его избежать, так как повторяющийся номер как бы намекает на то, что компания одна и та же.

Итог задачи: нужно чтоб при введении данных в ячейку Excel проверял даже частичное совпадение введенных значений в данную ячейку с данными из того же столбца(-цов? если поиск ведется по столбцам "рабочий номер №1, №2, №3" или "факсовый номер №1, №2, №3"). Самым оптимальным отсев/фильтрация мне кажется по критерию 5-7 повторяющимся подряд символам из данных введенных в конкретную ячейку. Поясняю: В таблице где более 12 тыс компаний в столбец "Название" вносится "ГП ХЗ "Хлебница", а Excel, сразу проверив совпадения по всем вариантам комбинаций (допустим) 6 чередующихся символов из этой ячейки и "просмотрев" весь столбец "Названия", сообщил, что в таблице найдены 3 совпадения трех комбинаций чередующихся символов ("хлебни", "лебниц", "ебница") с ячейкой в которой написано "Государственное предприятие "Хлебный завод "Хлебница". Почему необходимо делать поиск и сравнение "по комбинациям из чередующихся введенных символов из заполненной ячейки"? Идея: максимизация точности поиска дубля на основании "количества совпавших комбинаций чередующихся символов" с одной и той же ячейкой при различии формы подачи данных в однотипных столбцах.

Пояснение. Пример №1: после введения данных в ячейку и автоматической активации фильтрации/поиска дублей ячейки "ГП ХЗ "Хлебница" Excel (например) выдает диалоговое окно с текстом:
Найдены дубли по комбинациям с ячейками
С ячейкой (ООО "Хлебницкий и Компания") совпадает 2 комбинации из 6 очередных символов ("хлебни", "лебниц");
С ячейкой (Государственное предприятие "Хлебный завод "Хлебница") совпадает 3 комбинации из 6 очередных символов ("хлебни", "лебниц", "ебница");
С ячейкой (ФЛП "Плебницов В.О.") совпадает 1 комбинации из 6 очередных символов ("лебниц")
/что в результате дает понимание, что "дубль" = ячейка (Государственное предприятие "Хлебный завод "Хлебница"), так как там наибольшее кол-во повторяющихся комбинаций (3).

Пояснение. Пример №2: после введения данных в ячейку и автоматической активации фильтрации/поиска дублей ячейки "2653322641" (рабочий номер телефона) Excel (например) "подсветит дублем" ячейку из столбика "Рабочий номер №1" ("372653322641"), так как там больше всего повторяющихся комбинаций чередующихся символов:
С ячейкой  ("372653322641") совпадает 5 комбинации из 6 символов ("265332", "653322", "533226", "332264", "322641");
С ячейкой ("498653322641") совпадает 4 комбинации из 6 символов ("653322", "533226", "332264", "322641");
С ячейкой ("37653322651") совпадает 2 комбинации из 6 символов ("653322", "533226");
С ячейкой ("37653322214") совпадает 1 комбинации из 6 символов ("653322").
/что в результате подтверждает, что ячейка ("372653322641") является дублем, а соответственно вся строчка в базе - повтор введенной компании.

Возможность задать количество очередных символов для фильтрации/поиска есть важной, что позволит обеспечить вариативность требований к фильтрации/поиску в зависимости от типа вводимых/искомых данных.

В прикрепленных - шаблон таблицы с вводимыми данными. На ее основе уже вношу данные про компании из предоставленных каталогов.
Сразу скажу - пожалуйста, не предлагайте перейти в другие программы для внесения данных, ведь мы на форуме Excel. К тому же, Excel - универсальная программа для импорта данных в любые другие программы ведения БД, что в моем случае - крайне важно.

Спасибо всем, кто окажет посильную помощь в решении этой непростой задачи, не стесняйтесь - спрашивайте, уточняйте, я расскажу максимум для достижения результата.

P.S. Названия и номера телефонов придуманы во время написания поста для примера и не несут рекламного характера.

На связи.
Изменено: msdoser - 31.08.2017 16:44:26 (Доработка текста, так как писал из головы)
 
 
 
#2
31.08.2017 22:26:08
msdoser, не смогу предложить конкретной помощи, но кажется плагин Fuzzy Lookup создан именно для решения проблем как у Вас.
Краткая демонстрация на Youtube
In GoTo we trust
 
 
 
#3
31.08.2017 23:00:46
А  как же быть с очепятками:
Уже введена Хленица. Вы видите Хлебницу. По какому критерию будете определять дубль?

Жизнь богата. Бывают предприятия-омонимы. Допустим, с разной организационно-правовой формой, с приставкой "Группа компаний", да просто зарегистрированные в разных регионах.

У одного предприятия могут поменяться телефоны, реквизиты, ИНН, КПП и т.д. и т.п.

Уже осилить Ваш текст, претендующий на ТЗ - время. Решить проблему с широким набором неопределенностей - задача не просто трудная...

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

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

Увы, пока только направлением попробовать помочь могу...
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#4
31.08.2017 23:59:49
В данной задаче играться в комбинаторику - самый худший вариант для разработчика. Работа с подобными БД по-хорошему должна вестись по ключу, например по коду ОКПО (думаю, в таких каталогах он обязательно должен был указываться).
 
 
 
#5
01.09.2017 12:40:32
tolstak,  спасибо за расширение, это сильно упростит процесс слияния нескольких таблиц по разным каталогам. Принцип работы отличный, но для внесения данных в одну таблицу для наибольшей эффективности понадобится чтоб оно сразу давало подсказку о том, что "Введенное название в ячейку В11322 на 0,87 совпадает с ранее введенным названием из ячейки(ячеек) В4397".

Для определения дублей на данный момент у меня подобрано: форматирования цвета в ячейке при введении или вставке (100%) дубля + сообщение про (100%) дубль при попытке его ввода. Возможно ли  настроить условное форматирование ячеек с учетом не 100% совпадения? Например как в расширении  Fuzzy Lookup (совпадение <1) и в активном режиме?


PerfectVam,  насчет имени - tolstak, дал прекрасный вариант обхода этого вопроса (оно узнает дубль даже с очепяткой). Но, насчет форм собственности в названиях, ты - прав. В связи с этим столбец "названия" разделяю на 2 - "Форма собственности или деятельности" + "Непосредственно само название", что позволит выделить и упростить индивидуальные части названия. Пример ГП ХЗ будет в первом столбике, а Хлебница - во втором, что позволит быстрее "узнать про дубль". Это упрощает дальнейшую систематизацию и фильтрацию по алфавиту.
Насчет разбиения на части: по факту необходимо форматирование ячейки при не 100% совпадении после введения или вставки данных в ячейку, как я написал выше. Принцип работы естественно может отличаться. Я подумаю над разбивкой на составляющие, есть несколько идей.

Влад, к моему глубочайшему сожалению в каталогах "универсальных ключей" по которым можно было "убить" дубли нет (не было бы причины создавать тему здесь). Единственное что там есть в этом плане - индивидуальный номер в каталоге, но он из года в год даже у одной и той же компании разный, что не дает возможности как-либо использовать его для систематизации данных в БД.

В прикрепленных файлик с примером "подсветки (100%) дублей" в нынешней таблице БД (там есть макрос).
 
 
 
#6
01.09.2017 14:12:06
Влад, PerfectVam, tolstak,

Нашел такую тему:
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=53924
Человеку нужно было из столбика с Фамилиями подсветить и указать ячейки с повторами с частичным совпадением фамилий (Иванов и Иванова, Сидоров и Сидорова и др.)
Пользователь под ником Nic70y дал свое предложение с использованием функции "=ГИПЕРССЫЛКА" и "=ПОДСТАВИТЬ". Файл Excel с его трудами в прикрепленных.

Само выполнение отличное: рядом со столбиком в котором делается проверка сделан отдельный столбик, в котором сразу же после ввода подсвечивается был ли частичный повтор и (самое главное) в какой ячейке. Итоговая формула для проверки:

=ГИПЕРССЫЛКА(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЯЧЕЙКА("filename";A1);"";"");"]";"#")&"!A"&ПОИСКПОЗ(ЛЕВБ(A2;ДЛСТР(A2)-1)&"*";A3:A$2000;0)+СТРОКА();"A"&ПОИСКПОЗ(ЛЕВБ(A2;ДЛСТР(A2)-1)&"*";A3:A$2000;0)+СТРОКА())

Минусы: Оно ищет совпадения по принципу "значение ячейки с фамилией" - 1 знак с конца, что исключает возможность поиска по схожести как в Fuzzy Lookup.

Вопрос: можно ли в эту функцию добавить поиск по схожести в независимости от размещения текста (так чтоб оно увидело схожесть между "Коровцова" и "Боровцов-Коровцова") и показывало размещение ячейки(-ек) со схожестю?
Если получиться, то данный принцип можно будет применить на столбик с названиями, номерами телефонов, мейлами и др. и моя проблема будет решена.

Буду благодарен за Ваши ответы.  
Изменено: msdoser - 01.09.2017 14:32:54
 
 
 
#7
01.09.2017 14:46:16
msdoser, на мой взгляд, у Вас чуть более сложная проблема - там начало неизменной фамилии, а у Вас - произвольный текст в произвольном месте.. Т.е как минимум Вам нужно выделять Хлебницу как значимую часть, и тогда, может и сработает...
Вот еще статья интересная про алгоритмы нечеткого поиска на хабрахабре.
In GoTo we trust
 
 
 
#8
01.09.2017 15:47:16
Сначала неплохо бы данные нормализовать.
Первую идею уже дали - разделение имени. Я бы уточнил, что для видов организаций надо создавать отдельный справочник с полным и сокращённым названием: ИП = Индивидуальный предприниматель. Тогда и писать лишние слова не потребуется и возможность внесения ошибки типа "пердприниматель" исключается. Про телефоны та же история, введите унификацию: НИКАКИХ символов кроме цифр в телефоне быть не должно, а сам номер строго разделить на код страны, код региона и, собственно, номер. тогда будет не "глупый" набор цифр 372653322641, а умная комбинация 37, 26533 и 22641. указал оператор код страны 37 и сразу отпали все организации из других стран (отвалились чужие "хлебницы" и "ебницы" даже с полным совпадением имени, так как это явно не они).
Так что нормализация, нормализация и ещё раз нормализация!. Один раз поработать со структурой и забыть навсегда про кучу проблем и "СЛОЖНЫЕ" виды поисков.
 
 
 
#9
04.09.2017 09:46:45
tolstak, На данный момент по Вашему совету разделил столбец названия на 2 столбца: Стобец "Форма собственности/деятельности" и "Именная часть", теоретически найти способ выделения дублей должно быть проще.
Мартын, Вы правильно говорите, что для "правильной базы" нужно выделять поля "код страны", "код города", "непосредственно сам номер телефона". Но: не всегда можно понять где заканчивается код страны/города и начинается сам номер и если пытаться разделять его по такому принципу, то дело затянется на крайне длительный срок (чего желаю избежать). У меня уже есть алгоритм сохранения только циферных комбинаций с "умным" удалением не цифровых символов (В результате в независимости от номера телефона я получаю набор цифр подряд). Единственное что осталось: сообразить как сделать "частичный поиск".
 
 
 
#10
04.09.2017 11:07:57
Я не знаю как тут отнесутся к ссылкам на другие ресурсы, соответственно предоставлю сам текст проблемы от пользователя и решение, которое ему предложили.
Теоретически, это схоже с тем что я искал.

Пользователь:
"Не могу решить задачу удаление всей строки при частичном совпадении одной определенной ячейки с любой ячейкой из массива данных.
Цитата
например, если в случае искомого текста будет "мясная колбаса рецепт", а в качестве просматриваемого массива слово "рецепт".
Я вынес уроки из прошлой темы и ознакомился с понятием поиска по словарю, но как его создать пока ума не приложу. Спасибо Boroda за помощь в прошлой теме. Формулы здесь тоже могут помочь? Если да, то как?"

Ответ пользователю с решением:
"Удалить строки формулы не могут. Это либо макрос, либо пользователь.А вот пометить строки, которые подлежат удалению, формулы вполне способны. Как критерий Вам поможет функция ПОИСК, которая возвращает число, если подстрока найдена и ошибку, если нет.
Конструкция получится примерно такая:
=СУММПРОИЗВ(--НЕ(ЕОШИБКА(ПОИСК('для удаления'!$A$1:$A$8;A4))))
В первом аргументе функции ПОИСК задан не один элемент для поиска, а массив. Соответственно и возвращен будет массив результатов поиска. От формулы массива спасает обработка функцией СУММПРОИЗВ. -- используется для преобразования логических ЛОЖЬ/ИСТИНА в 0/1.
Результат - получится количество частичных совпадений. Останется только отфильтровать ячейки со значениями 1 и больше (или не 0) и удалить только оставленные фильтром видимыми.
P.S.: Если я неверно понял: что где должно быть подстрокой, достаточно поменять аргументы функции ПОИСК местами. Остальное останется без изменений."

В прикрепленных 2 файла: "от пользователя" с исходной таблицей от пользователя и "решение" то что прислали в ответ как решение задачи.
Прошу, объясните по какому принципу работает эта формула и можно ли ее использовать для моей задачи?
 
 
 
#11
04.09.2017 15:49:06
Есть файл с макросом. Он показывает коэфициент "схожести" двух ячеек в % (в прикрепленных).
Можно ли используя данный макрос в моем случае, сделать отдельный столбик, где указывались бы адреса ячейки(-ек) с которыми у этого названия наибольший процент совпадения без учета ячейки из которой было взято само значение для поиска?
Код
Option Explicit
Option Base 1

Public Function TextSimilarity(text1 As Variant, text2 As Variant) As Double
' Метрика сходства текстовых строк (0-1)
Dim i As Integer, k As Integer, m As Integer
Dim profile() As Integer
Dim dupVect() As String * 2
Dim txt As String
Dim txtNum As Integer

    ReDim profile(2, Len(text1) + Len(text2) - 2 + 4)
    ReDim dupVect(Len(text1) + Len(text2) - 2 + 4)
    m = 0
    
    txt = " " & text1 & " "
    For txtNum = 1 To 2
        For i = Len(txt) - 1 To 1 Step -1
            For k = 1 To m
                If StrComp(dupVect(k), Mid(txt, i, 2), 1) = 0 Then Exit For '>>>
            Next k
            If k > m Then 'Not found
                m = k 'm + 1
                dupVect(k) = Mid(txt, i, 2)
            End If
            If profile(txtNum, k) = 0 Then
                profile(txtNum, k) = 1
                'profile(txtNum, k) = 2
                'Различия в количестве одинаковых последовательностей
                'в 2 раза менее значимы, чем в их наличии/отсутствии.
            Else
                profile(txtNum, k) = profile(txtNum, k) + 1
            End If
        Next i
    txt = " " & text2 & " "
    Next txtNum
    
Dim sumMax As Integer, sumDif As Integer
    For k = 1 To m
        sumDif = sumDif + Abs(profile(1, k) - profile(2, k))
        If profile(1, k) < profile(2, k) Then
            sumMax = sumMax + profile(2, k)
        Else
            sumMax = sumMax + profile(1, k)
        End If
    Next k
    TextSimilarity = 1 - sumDif / sumMax
    
End Function
Пожалуйста, помогите!
 
 
 
#12
04.09.2017 17:17:35
Ответ на пост 10.
Стояла задача сравнивать по подтексту не с одной ячейкой, а с несколькими. Решение это и делало. При перемене мест параметров, о котором было написано, сравнивается либо "является ли подстрокой", либо "содержит ли как подстроку" указанные для сравнения ячейки. Поиск при этом жесткий, а не вариативный, который Вы пытаетесь "прикрутить" для своей задачи.

Поможет ли это и чем - Вам решать.

Мне по-прежнему кажется самым правильным реализовать поиск по подстроке формой или комбобоксом. А дальше, к сожалению, ручками. Но в указанном варианте ручками - это поэкспериментировать с набором части наименования, а потом (если дубликат найдется), то просто выбрать из списка. Любая автоматизация принятия решения без участия пользователя чревата ошибками разных родов: как признать дубль недублем, так и наоборот.
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#13
05.09.2017 16:54:46
Решение, которое я так долго искал:
http://www.excelworld.ru/forum/2-10223-1
Человек на форуме предоставил эксель в котором есть 2 макроса, которые выполняют именно то что нужно, да еще и вычитают сам источник поиска из анализируемого диапазона.
Единственное "но": макрос сравнения схожести при большом кол-ве строк начинает чуть "проседать" в быстродействии.
Кому интересна тема "неточного поиска" и методы его реализации: поищите посты и видео Игоря Бокалова и, в том числе, обратите внимание на программу keycollector (это конечно не эксель, но программа крайне продуктивно работает с фильтрацией и отсевом "неточных дублей" в пределах одного и того же списка). Прошу не воспринимать как рекламу, эти материалы действительно были полезны в планировании дальнейшей работы над моей БД.
Спасибо форуму, администрации и всем, кто отвечал в этой теме.
Тему сохраняю себе на комп. Надеюсь кому-то также будет полезна.
 
 
 
Страницы: 1
Читают тему
Наверх