Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сопоставить 2 столбца адресов, и вывести результаты с доп столбцами.
 
#1
25.06.2019 13:28:13
Добрый день, Товарищи)
Почему не в ветке "работа".Тема создана специально для тех кто любит поковыряться в чём нибудь интересном т.д. просто интересно есть ли более лучшие варианты чем Fuzzy lookup. И не устраивать же кастинг в ветке "работа" в стиле кто лучше и быстрее(такое вроде низя)
Собственно всё что описано ниже решается Fuzzy lookup с настройкой 0,8 точности (уже испытано и верчено :) 100500 раз, но довольно таки не быстро, 1000 строк примерно за 20 минут... и не прям 100% сопоставляет но это не страшно)
Итак:
Нам надо сопоставить РР с Базой
В файле есть 2 листа (РР и БАЗА)(третий лист как раз то в каком ввиде я хочу видеть результат) не обязательно на отдельный лист(ниже пояснения)
Нужно сопоставить "Адрес (местоположение):" с лита РР, "Ключ АДРЕС" с листа База.(написание адресов разное)
При совпадении вывести все данные с листа база по строке, а с листа РР только которые отмечены зелёным и красным.(если на отдельный лист)
Или подставить на листе РР все данные с листа База (если на лист РР)
Кол-во строк на листах не совпадает. На листе РР по столбцу "Адрес (местоположение):" могут быть дубли НО "кадастровый номер" разный
В связи с этим нескольким строкам на листе РР может соответствовать 1 строка с листа База и она должна быть в каждой из этих строк.
Теперь немного оффтопик(и свои мысли)

Версия на гугл диске(больший объем чем в файле примере) но всё равно не такой какой будет. По факту будем иметь примерно 10 таких файлов по 100к строк.(сейчас на Воркуте, потом на остальных больших городах республики)
Гугл диск

8-0 Ух... стено-текст..... :oops:
Изменено: Wild.Godlike - 26.06.2019 10:16:25
 
 
 
#2
25.06.2019 14:08:16
Алгоритм, я бы применил следующий. Забрал с листа "База" адреса в словарь,  таблицу "РР" в массив, пробежал по массиву сверяя данные со словарем, если совпадает то прямо в текущий массив и перезаписывал, нужные столбцы, используя переменную счетчик. А затем кусок массива выгрузил на лист ограничив счетчиком который велся при совпадении по вертикали и количеством перезаписываемых столбцов по горизонтали.
Сам код писать нет времени, поэтому пишу алгоритм. В принципе задача не сложная, да и на больших объемах должна работать быстрее 20 мин.
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#3
25.06.2019 14:24:10
Из листа база, данные сами подставите, это уже не сложно, можно даже функцией ВПР воспользоваться, либо код написать.
Код
Sub test()
    Dim objDic As Object, sh As Worksheet
    Dim arr(), txt$, i&, j&
    j = 1
    Set objDic = CreateObject("scripting.dictionary")
    arr = Лист2.UsedRange.Value
    For i = 2 To UBound(arr)
        txt = arr(i, 1)
        objDic.Item(txt) = txt
    Next i
    Erase arr
    arr = Лист1.UsedRange.Value
    For i = 2 To UBound(arr)
        txt = arr(i, 11)
        If objDic.exists(txt) Then
            j = j + 1
            arr(j, 1) = arr(i, 1)
            arr(j, 2) = arr(i, 2)
            arr(j, 3) = arr(i, 3)
            arr(j, 4) = arr(i, 4)
            arr(j, 5) = arr(i, 5)
            arr(j, 6) = arr(i, 11)
            arr(j, 7) = arr(i, 12)
            arr(j, 8) = arr(i, 13)
            arr(j, 9) = arr(i, 14)
        End If
    Next i
    Set sh = Worksheets.Add
    With sh
        .Name = "отчет"
        .[a1].Resize(j, 9) = arr
        .[a1].Resize(, 9) = Array(arr(1, 1), arr(1, 2), arr(1, 3), arr(1, 4), arr(1, 5), _
        arr(1, 11), arr(1, 12), arr(1, 13), arr(1, 14))
    End With
End Sub
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#4
25.06.2019 15:33:30
Nordheim, Спасибо интересное решение, но не совсем. Как я писал ранее не везде 100% совпадение в адресе. присутствуют нюансы)(описание под спойлером более подробно в #1)
Провел тест на примере который на гугл диске. Из 30274 на листе РР. на лист "отчёт" попало только 16885 т.е. 100% совпадения, чуть больше 50%.
Было бы сразу 100% совпадение) этой бы темы не было)
Например:
Республика Коми, г Воркута, пгт Северный, ул Ватутина, д 9, кв 71
Республика Коми, г Воркута, пгт Северный, ул Ватутина, д 9, кв. 71
Республика Коми, г Воркута, пгт Северный, ул. Ватутина, д 9, кв 71
Республика Коми, г Воркута, ул Ватутина, д 9, кв 71
г Воркута, пгт Северный, ул. Ватутина, д 9, кв 71
насколько я понимаю с точки зрения вашего кода, это всё разные адреса. но по факту это один адрес)
В свою очередь хочу заметить что. ВСЕ адреса которые есть на листе РР присутствуют и на листе База а вот С листа БАЗА не все адреса есть на листе РР. т.к. выборка была как раз по списку базы, и некоторые данные по адресам из базы в РР отсутствовали.
 
 
 
#5
25.06.2019 15:40:52
Для такой БД нужен ключ, посмотрите в эту сторону, если будет ключ то и проблемы с неправильным заполнением адреса исчезнут, а так какой код не напиши быстро он не отработает, по крайней мере не на VBA Excel .
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#6
25.06.2019 15:49:32
Цитата
Wild.Godlike написал:
это всё разные адреса. но по факту это один адрес
"Какие Ваши доказательства?" (с)тырено
Вероятно, что это так, но ведь может улица с одинаковым названием присутствовать и в городе и в посёлке.
Вообще, за такую "базу" руки отрывают, медленно, с наслаждением.
Я бы для начала разобрал адреса по частям, на основе справочников. Тогда было бы ясно, что улицы Ватутина нет в Воркуте, значит адрес неверный и надо разгребать его руками.
 
 
 
#7
25.06.2019 15:57:57
Nordheim, Мартын, Товарищи ) Конкретно с моей базой, всё в порядке) и ключи и ИД и всё что нужно имеется. а вот с базой РР (Росреестр) всё не так радужно как хотелось бы) и если бы имелась возможность отрывать руки кадастровым инженерам я только за).
Так что работать с РР приходится с тем что, имеем :(
Так что в этом то и проблема) что надо притянуть данные с сайта РР к нам в базу)
Изменено: Wild.Godlike - 25.06.2019 16:36:38
 
 
 
#8
25.06.2019 20:45:32
Wild.Godlike, А в базе Росреестра нет ID ? если есть то может и в вашей базе сделать ID идентичный и по нему подтягивать данные?
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#9
26.06.2019 05:03:47
Nordheim, К сожелению если даже у них и имеется, то сотрудничать они не хотят невкакую, 2 года писем и запросов, в ответ пользуйтесь нашими сервисами на сайте и API, тот API который у них имеется не позволяет получать данную информацию с нужных сервисов, а в ответ жди когда нибудь реализуем. слишком большая гос. машина чтобы с ними договорится)
Инфа парсится из открытого источника ТЫЦ обход капчи и лалала)
вставляем например 11:16:0701001:1000 в поле кадастровый номер, вводим капчу, найти, тыкаем на адрес, видим табличку с данными). ну и парсим в эксель)
Причём как собираются данные (кадастровые номера) чтобы потом это подсунуть для этого сервиса в парсер(там только в ручную) ещё отдельная история). Я конечно могу всё рассписать. от и до) но это думаю врятли относится к теме)
Из за всего этого и остаётся что использовать Fuzzy lookup, других методов не нашел. и врятли найду походу)
Хотя думаю можно составить гиганское ТЗ от и до, но думаю разработка такого парсера выйдет в 20+к если не больше.
Изменено: Wild.Godlike - 26.06.2019 05:07:39
 
 
 
#10
26.06.2019 07:54:39
Цитата
Wild.Godlike написал:
разработка такого парсера выйдет в 20+к если не больше.
Остается только посочувствовать.  :(
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#11
26.06.2019 09:02:53
можно с помощь регулярных выражений
Получилось как-то так
https://drive.google.com/file/d/1E0hIz7iXHBrNDhkZ7qLNyEc3gTr-CG5W/view?usp=sharing
 
 
 
#12
26.06.2019 09:08:48
По какому алгоритму после парсинга сверять на идентичность это:
Цитата
Wild.Godlike написал:
Республика Коми, г Воркута, пгт Северный, ул Ватутина, д 9, кв 71
Республика Коми, г Воркута, пгт Северный, ул Ватутина, д 9, кв. 71
Республика Коми, г Воркута, пгт Северный, ул. Ватутина, д 9, кв 71
Республика Коми, г Воркута, ул Ватутина, д 9, кв 71
г Воркута, пгт Северный, ул. Ватутина, д 9, кв 71
?
Изменено: Nordheim - 26.06.2019 09:09:05
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#13
26.06.2019 10:06:28
msi2102, Посмотрел, годно, чучуть подредактировать только, а потом уже черезе ctrl+H почему то даже не подумал в сторону регулярных.(подумал сразу что при объемах в 100к строк, с таким кол-вом формул excel не выживет вообще)
Nordheim, В #1 под спойлером рассписано как я себе это представляю.)
Изменено: Wild.Godlike - 26.06.2019 10:07:04
 
 
 
#14
26.06.2019 10:33:35
Цитата
Wild.Godlike написал:
а потом уже черезе ctrl+H
Желательно и перед обработкой сделать ctrl+H на листе РР, заменить "район" - "р-н", "пгт" - "пгт." и т.д. Или в запрос добавлять все возможные варианты.
И ещё если "пгт.", "пос." и "п." можно удалить (навряд ли будут одинаковые названия к примеру: пгт. Воргашор и село Воргашор), то обозначения "ул", "б-р", "просп." и т.п., я бы посоветовал оставить, т.к. может присутствовать и ул. Блюхера, и пер. Блюхера.
 
 
 
#15
26.06.2019 10:39:56
msi2102, Согласен) играюсь вот сейчас) ещё нашел что лучше сразу на листре РР заменить ", корп. " на /, чтобы было не 15, корп. 3, кв **, а 15\3 как в базе. не подумал сразу об этом.
А какой UDF вы использовали для регулярок который идёт в PLEX работает с вашими Патернами, а который идёт из ПРИЁМОВ нет.
Изменено: Wild.Godlike - 26.06.2019 11:17:39
 
 
 
#16
26.06.2019 12:06:19
Установите надстройку

https://www.planetaexcel.ru/techniques/7/4844/

формула для населенного пункт: =RegExpSum(C2;D2) или =RegExpSum(C2;$D$2)
формула для улиц: =RegExpSum(C2;E2)  или  =RegExpSum(C2;$E$2)
Изменено: msi2102 - 26.06.2019 12:15:41
 
 
 
#17
26.06.2019 12:20:18
msi2102, По вашей ссылке там же вот это, только UDF не нашел там надстройки. я в #15 на неё ссылку и давал. что она у меня не работает (
Или вы про самый последний коммент внизу страницы?
разобрался)
Пошел играться дальше)
Изменено: Wild.Godlike - 26.06.2019 12:27:51
 
 
 
#18
26.06.2019 12:31:59
Wild.Godlike, в #16 есть прикрепленный файл RegExpExtract.xlam установите эту надстройку. Подключите библиотеку Microsoft VBScript Regular Expressions 5.5
 
 
 
#19
27.06.2019 10:44:39
И так) поюзал регулярки и Fuzzylookup. после регулярок и т.д. ещё надо обрабатывать в адекватный вид, много замен черезе CTRL+H и т.д.(думаю это от того что с регулярками до этого не работал, и приходилось юзать гугл ну и т.д. днище кароч я в этом), с fuzzy дольше но меньше действий руками). Думаю нужно усовершенствовать свои навыки)
 
 
 
Страницы: 1
Наверх