Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формирование кода ФИАС по строке адреса, Формирование кода ФИАС по строке адреса
 
#1
15.02.2023 13:44:56
Здравствуйте!
Как всегда неожиданно ввели новую форму, где помимо строки адреса необходимо заполнить поле с кодом ФИАС. Заполняемая таблица в MS Excel (2016) может иметь до 5000 записей. Можно ли как-то автоматизировать этот процесс, имея строковый адрес, автоматом получать код ФИАС и записывать его в ячейку?
Может, кто сталкивался с такой задачей... Файл с примером прилагаю. Необходимо заполнить столбец 6.
Изменено: Rodo - 15.02.2023 13:46:14
 
 
 
#2
15.02.2023 14:12:28
Rodo,  а как было найдено первое значение-пример (ячейка F5)?
Нужны коды улиц, например "Пожарная часть"?
Как быть с ячейками, в которых улиц несколько?

как вариант - простое решение по 10 копеек за строку )
https://dadata.ru/api/clean/address/#restrictions

там же есть и бесплатный вариант
https://dadata.ru/api/suggest/fias/#request
Изменено: nilske - 15.02.2023 14:27:27
 
 
 
#3
15.02.2023 14:24:54
Цитата
Rodo написал:
Заполняемая таблица в MS Excel (2016) может иметь до 5000 записей.
Вот еще адрес. Им можно отправить сразу все записи пакетом.
Альтернатива - закачать себе ФИАС. Архив - 36 ГБ.
Владимир
 
 
 
#4
15.02.2023 14:44:09
Цитата
написал:
Rodo,  а как было найдено первое значение-пример (ячейка F5)?
Нужны коды улиц, например "Пожарная часть"?
Как быть с ячейками, в которых улиц несколько?

как вариант - простое решение по 10 копеек за строку )
https://dadata.ru/api/clean/address/#restrictions

там же есть и бесплатный вариант
https://dadata.ru/api/suggest/fias/#request
Первое значение кода ФИАС было скопировано с сайта https://фиас.онлайн (^C+^V).
Нужны только коды ФИАС с уровнем "до улицы".
Ячейки с несколькими улицами обработаем до одной улицы (случайно проскочило).
 
 
 
#5
15.02.2023 14:50:06
Цитата
написал:
Цитата
Rodo написал:
Заполняемая таблица в MS Excel (2016) может иметь до 5000 записей.
Вот еще  адрес . Им можно отправить сразу все записи пакетом.
Альтернатива - закачать себе  ФИАС . Архив - 36 ГБ.
Хотелось бы, конечно, не платить за обработку таблиц, т.к. это в з/п не учтут...
Скачать базу ФИАС думали, но обрабатывать 36 ГБ... памяти и производительности ПК не хватит...
Видится решение, типа, в цикле, через API обратиться к сайту, подставить строковые данные адреса, получить код ФИАС и записать его в ячейку Excel.
 
 
 
#6
15.02.2023 14:53:09
Первое значение было получено с сайта https://фиас.онлайн, подставили строку адреса и получили код ФИАС.
Желательно получить коды ФИАС с уровнем глубины "до улицы".
Несколько улиц в ячейке не будет. (ошибка нормализации табл.)
Изменено: Rodo - 16.02.2023 14:09:40
 
 
 
#7
15.02.2023 15:34:05
пожарная часть
 
 
 
#8
15.02.2023 15:36:10
Тимофеев, а для фермы и Комсомола тоже так можно? )

Кажется понял, если нет соответствующего адреса, берём ID населённого пункта.
Изменено: nilske - 15.02.2023 15:41:59
 
 
 
#9
15.02.2023 15:40:34
Если нет улицы дает id населенного пункта - поэтому Ферма = Пожарка
 
 
 
#10
15.02.2023 15:52:03
Дома, это просто детализация. Есть всё и № дома, корпуса, помещения... Потом это можно будет добавить или убрать уже в Excel, главное, общее решение проблемы по автоматизированному заполнению кода ФИАС хотя бы до улицы.
Изменено: Rodo - 16.02.2023 14:07:50
 
 
 
#11
15.02.2023 16:13:46
Вариант штатными средствами - его надо будет разобрать после "guid":" до ","ifnsfl"  - 36 символов вроде в коде
Код
=ПСТР(
ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=
Чувашская Республика, Алатырский, с.Сойгино, ул.Пионерская
&oneString=1&limit=50&withParent=1");
ПОИСК("guid";
ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=
Чувашская Республика, Алатырский, с.Сойгино, ул.Пионерская
&oneString=1&limit=50&withParent=1"))+7;
36)
Изменено: Тимофеев - 15.02.2023 17:13:12
 
 
 
#12
15.02.2023 16:57:41
с помощью power query и dadata можно делать примерно так:

Код
// доработать и превратить в функцию
let
    city = "сойгино",
    street = "", // либо пишем улицу, либо оставляем значение пустым (для "пожарной части", "фермы" и т.п.)
    url = "https://suggestions.dadata.ru/suggestions/api/4_1/rs/suggest/fias",
    token = "указать_токен", // получить можно бесплатно в личном кабинете
    headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"Authorization" = "Token " & token
                ],
    data = Json.Document(Web.Contents(url,
            [
                Headers = headers,
                Content = Json.FromValue([query=city & " " & street])
            ]
                )),
    toTable = Record.ToTable(data),
    expandValue = Table.ExpandListColumn(toTable, "Value"),
    expandValue1 = Table.ExpandRecordColumn(expandValue, "Value", {"value", "unrestricted_value", "data"}, {"value.1", "unrestricted_value", "data"}),
    expandedData = Table.ExpandRecordColumn(expandValue1, "data", {"fias_id"}, {"fias_id"}),
    selectColumn = Table.SelectColumns(expandedData,{"fias_id"}),
    selectID = Table.FirstN(selectColumn, 1)
in
    selectID // 34c6b6ae-373e-478f-9b19-d15c31f1bb27
Изменено: nilske - 15.02.2023 16:58:03
 
 
 
#13
15.02.2023 17:13:02
Решение из 9 На Вашем примере изобразил:
если с улицей косяк, берет населенный пункт
Странно с Карла Маркса идет в ошибку по улице (берет населенный пункт), если поменять в данных на К.Маркса то выдаст результат
Код
=ЕСЛИОШИБКА(
ЕСЛИОШИБКА(
ПСТР(ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:E5)&
"&oneString=1&limit=50&withParent=1");
ПОИСК("guid";
ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:E5)&
"&oneString=1&limit=50&withParent=1"))+7;36);
ПСТР(ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:D5)&
"&oneString=1&limit=50&withParent=1");
ПОИСК("guid";ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query="&
ОБЪЕДИНИТЬ(", ";1;B5:D5)&
"&oneString=1&limit=50&withParent=1"))+7;36));
"не найден")
Изменено: Тимофеев - 15.02.2023 17:33:36
 
 
 
#14
15.02.2023 20:14:07
Благодарю! Всё заработало...
Изменено: Rodo - 16.02.2023 14:10:07
 
 
 
#15
16.02.2023 13:25:16
Здравствуйте.
Стал адаптировать предложенное решение на компьютере на непосредственной работе и выпадает ошибка.
На рабочем компьютере стоит MS Excel (2013), пришлось заменить формулу _xlfn.TEXTJOIN на СЦЕПИТЬ. Стал разбирать формулу по функциям, определил, что не работает встроенная функция MS Excel  =ВЕБСЛУЖБА().
При формировании API-запроса получается строка типа =ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=Чувашская Республика, Яльчикский, д.Тораево, ул.Карьерная&oneString=1&limit=50&withParent=1 ") , которая возвращает значение ошибки #ЗНАЧ! Пробовал использовать пример с сайта https://kladr-api.ru   =ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=Москва&oneString=1&limit=50&withParent=1") .
Тоже функцией ВЕБСЛУЖБА() возвращается ошибка #ЗНАЧ!
Разрешения в MS Excel все стоят. Проверил работу с API ЦБ РФ =ВЕБСЛУЖБА("http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=01.01.2022&date_req2=01.02.2022&VAL_NM_RQ=R01235") , всё работает, запрос возвращён без ошибки.
Есть ли решение данной проблемы или я где-то допустил ошибку?

ЗЫ
Написал в службу поддержки "ФИАС в облаке" <support@fias-api.ru>... ответили: "... к сожалению, мы не можем прокомментировать работу метода ВЕБСЛУЖБА в Excel.Скорее всего этот метод ожидает получение адрес веб-службы (которая работает по протоколу SOAP и передаем данные в XML формате). Наш сервис такой формат данных не поддерживает".
Изменено: Rodo - 16.02.2023 14:10:27
 
 
 
#16
16.02.2023 13:45:58
Решение - поставьте Excel новее
Заменил в файле на сцепку B5&", "&C5&", "&D5
Изменено: Тимофеев - 16.02.2023 13:51:15
 
 
 
#17
16.02.2023 13:51:09
В Excel 2016 указанная формула
Код
=ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=Чувашская Республика, Яльчикский, д.Тораево, ул.Карьерная&oneString=1&limit=50&withParent=1")

не выдает ошибок.
Возможно, в Excel 2013 проблема с использованием символов, не допустимых в стандарте URL (в Вашем случае - буквы русского алфавита).
Попробуйте записать так:
Код
=ВЕБСЛУЖБА("https://kladr-api.ru/api.php?query=" & КОДИР.URL("Чувашская Республика, Яльчикский, д.Тораево, ул.Карьерная") & "&oneString=1&limit=50&withParent=1")

И пожалуйста, уберите все цитаты из всех своих сообщений.
Владимир
 
 
 
#18
16.02.2023 13:52:56
Да лишним не будет записать с кодировкой
Код
=КОДИР.URL(B5&", "&C5&", "&D5)
Изменено: Тимофеев - 16.02.2023 13:54:12
 
 
 
#19
16.02.2023 14:23:31
Почему-то всё равно функция =ВЕБСЛУЖБА() выдаёт ошибку #ЗНАЧ!
Изменено: Rodo - 16.02.2023 15:10:36
 
 
 
#20
16.02.2023 14:32:02
На всякий случай, выгрузите книгу Excel с указанной формулой (которая выдает ошибочное значение).
Владимир
 
 
 
#21
16.02.2023 14:34:00
Rodo, скобку переставьте в вашей формуле после E18, а не D18 - а то передаете половину кодированного - половину русского
Попробуйте на другом компе - найдете комп на котором будет работать - они Вам быстро переведут данные и пришлют обратно )
Изменено: Тимофеев - 16.02.2023 15:10:15
 
 
 
#22
16.02.2023 14:41:55
У меня файл из #19 при загрузке выдает предупреждение, что служба WEBSERVICE отключена. После включения и переввода (достаточно удалить и добавить последний символ) формула пересчитывается и выдает правильное значение.

Может быть, дефект версии Excel 2013 (сейчас нет компьютера для проверки).
Изменено: sokol92 - 16.02.2023 14:47:50
Владимир
 
 
 
#23
16.02.2023 15:06:48
Скобку переставил, в примере и на прикреплённом скрине видно. Всё равно ошибка выпадает... (#ЗНАЧ!)
 
 
 
#24
16.02.2023 15:53:43
у меня в 2019 работает, в 2016 - нет.
 
 
 
#25
16.02.2023 19:34:10
Спасибо за помощь и участие.
 
 
 
#26
16.02.2023 20:41:53
Rodo, если на Power Query решение не подходит, а Вебслужба не работает, то можно попробовать через макрос (UDF).
   Напишите, если такое решение является интересным и подходящим, чтобы зря не стараться )
 
 
 
#27
16.02.2023 20:45:45
Нашёл сайт с сервисами по ФИАС. Функция =ВЕБСЛУЖБА() у Excel (2013) работает с примером с сайта корректно яч.F22 прилагаемого файла. Осталось только теперь понять как сформировать запрос по поиску кода ФИАС из строки с адресом. Пробовал формулу в яч.F23, но запрос возвращён сайтом пустым.
Изменено: Rodo - 16.02.2023 20:46:09
 
 
 
#28
16.02.2023 20:58:10
Цитата
nilske написал:
Rodo, если на Power Query решение не подходит, а Вебслужба не работает, то можно попробовать через макрос (UDF).
   Напишите, если такое решение является интересным и подходящим, чтобы зря не стараться )

Спасибо за предложение, но я боюсь, что не смогу объяснить безопасникам, чтобы они сделали исключение на запуск макросов.
 
 
 
#29
10.07.2024 19:43:00
Здравствуйте! Помогите пожалуйста, мне нужно чтобы не только до улицы код ФИАС определялся, а еще учитывая дом. Как отредактировать формулу? у меня к сожалению не получилось. Использовал последний прикрепленный файл Rodo
 
 
 
Страницы: 1
Читают тему
Наверх