В этот раз хотелось бы рассказать об использовании Excel в качестве поисковой системы в сети Интернет.

Идея возникла не просто так: необходимо было определить по примерно тысяче организаций вид их деятельности. Ничего, кроме как официальных наименований этих самых организаций (наподобие ООО «Ромашка»), не было.

 

 

 

В качестве самого простого способа я решил использовать сервис Яндекс.XML, позволяющий возвращать в XML -формате результаты поиска.

Решим задачу в несколько этапов:

  1. Оптимизируем наши поисковые запросы так, чтобы в них не фигурировала организационно-правовая форма (как показала практика, в противном случае будут найдены файлы договоров, но не описание организации);
  2. Зарегистрируемся на Яндексе и подключим Яндекс. XML;
  3. Используем VBA для получения результатов поиска и извлечения нужных нам данных из XML.

Оптимизация

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

В основном это:

  • Различающиеся кавычки: парные («») и прямые ("), двойные апострофы ('')
  • Наличие организационно-правовой формы (ООО, ЗАО и т.п.)
  • Указание в скобках дополнительной информации (бренд, регионы/города присутствия)

В качестве наиболее верного способа решил привести все кавычки к единому стилю с помощью макроса и в отдельную ячейку перенести наименование организации, заключенное в кавычки. Если кавычек нет – перенести весь текст.

Макрос для унификации кавычек

{code brush:vb}Sub Optimize()

  Application.ScreenUpdating = False
 
  ' Замена апострофов (') на прямые кавычки (")
  Selection.Replace What:="'", Replacement:="""", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
 
  ' Замена апострофов (`) на прямые кавычки (")
  Selection.Replace What:="`", Replacement:="""", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
 
  ' Замена двойных кавычек («) на прямые кавычки (")
  Selection.Replace What:="«", Replacement:="""", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
 
  ' Замена двойных кавычек (») на прямые кавычки (")
  Selection.Replace What:="»", Replacement:="""", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
 
  Application.ScreenUpdating = True
End Sub{/code}

Формула для вывода наименования организации внутри кавычек

=ЕСЛИ(ЕОШИБКА(НАЙТИ("""";A1));A1;ПСТР(A1;НАЙТИ("""";A1)+1;НАЙТИ("""";A1;НАЙТИ("""";A1)+1)-НАЙТИ("""";A1)-1)), где A1 – ячейка с исходным текстом.

Если хотя бы одна кавычка найдена, ищется текст внутри, в противном случае результатом будет вся ячейка.

Работа с Яндекс.XML

Перед тем как выбрать Яндекс в качестве поставщика, я посмотрел, есть ли еще поисковые системы, предлагающие вывод результатов поиска в XML. Оказалось, что таких нет, либо есть, но это платные сервисы.

Для работы с Яндекс.XML необходимо прежде всего зарегистрироваться в системе, перейти на страницу сервиса и зарегистрировать IP-адрес, с которого будут отправляться запросы.

Подробнее о том, как задавать запросы, можно посмотреть здесь (метод GET) и здесь (метод POST).

Для нашего случая мы используем метод GET, позволяющий задавать параметры поиска в URL.

Пример такого запроса:

http://xmlsearch.yandex.ru/xmlsearch?user={имя пользователя}&key={API-ключ, выданный при регистрации}&query=yandex&groupby=attr%3Dd.mode%3Ddeep.groups-on-page%3D10.docs-in-group%3D1

Мы изменим его только в части самого запроса (query=yandex) и количества выдаваемых результатов – сведем их к одному для меньшего объема передаваемых данных (groups-on-page=1.docs-in-group=1).

Формула запроса

="http://xmlsearch.yandex.ru/xmlsearch?user={имя пользователя}&key={API-ключ, выданный при регистрации}&query="&B1&"&groupby=attr%3Dd.mode%3Ddeep.groups-on-page%3D1.docs-in-group%3D1", где B1 – оптимизированный текст.

Для всех ячеек из столбца со сформированными URL присвоим имя urls: выделим все ячейки и в поле слева от строки формулы введем urls.

Код для получения и обработки запросов

Для работы с XML нам понадобится подключить связь с Microsoft XML. Сделать это можно в меню Tools – References…

Разберем следующий код, получающий XML с результатом поиска каждого запроса и выводящего определенные его части в ячейки таблицы.

{code brush:vb}Sub GetSearchResults()
 
  Dim oXMLDoc As DOMDocument
  Dim oXMLNode As IXMLDOMNode
 
  For Each c In Range("urls").Cells
    Set oXMLDoc = New DOMDocument
    oXMLDoc.Load (c.Value)
    Do Until oXMLDoc.XML <> ""
      Application.Wait (Now() + TimeValue("00:00:01"))
    Loop
 
    Set oXMLNode = oXMLDoc.selectSingleNode("//yandexsearch/response/results/grouping/group/doc/title")
    Cells(c.Row, c.Column + 1) = oXMLNode.Text
 
    Set oXMLNode = oXMLDoc.selectSingleNode("//yandexsearch/response/results/grouping/group/doc/passages/passage")
    If Not (oXMLNode Is Nothing) Then Cells(c.Row, c.Column + 2) = oXMLNode.Text
  Next
 
End Sub{/code}

№ строки Код Описание
6-18 For Each c In Range("urls").Cells

Next
Код внутри этой обертки будет повторяться столько раз, сколько строк с запросом будет выбрано.
3, 7-8

Dim oXMLDoc As DOMDocument

Set oXMLDoc = New DOMDocument

oXMLDoc.Load (c.Value)

Загружаем в переменную oXMLDoc результаты запроса по URL из ячейки.
9-11

Do Until oXMLDoc.XML <> ""

Application.Wait (Now() + TimeValue("00:00:01"))

Loop

Без данного кода я столкнулся с тем, что результаты запроса начинают обрабатываться кодом из строк 13-17 раньше, чем они оказываются загружены. Следственно, возникала ошибка.
Этим кодом мы проверяем, загружен ли XML: если нет, ставим задержку в одну секунду и повторяем проверку.
13-14

Set oXMLNode = oXMLDoc.selectSingleNode("//yandexsearch/response/results/grouping/group/doc/title")

Cells(c.Row, c.Column + 1) = oXMLNode.Text

Из загруженного XML извлекаем нужные нам данные: заголовок первой найденной страницы и помещаем его в столбец, следующий за столбцом с URL.
16-17

Set oXMLNode = oXMLDoc.selectSingleNode("//yandexsearch/response/results/grouping/group/doc/passages/passage")

If Not (oXMLNode Is Nothing) Then Cells(c.Row, c.Column + 2) = oXMLNode.Text

Из загруженного XML извлекаем нужные нам данные: описание первой найденной страницы и помещаем его в столбец, следующий за столбцом с заголовком страницы. При этом, описания к результату поиска может и не быть, поэтому предварительно проверяем oXMLNode на значение.

Итого мы имеем код, получающий XML по заданной нами URL и извлекающий из полученного XML верхний заголовок и описание найденной страницы.

Дальше остается только проанализировать массив информации по ключевым словам и присвоить каждому значению какую-либо категорию.

Добавить комментарий


Защитный код
Обновить

Похожие статьи

Вверх