Создание веб запроса в excel

Обновлено: 07.01.2025

Рис. 1. Использование диалогового окна Создание веб-запроса для указания данных, которые следует импортировать; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Термин «веб-запрос» немного неточен, так как с его помощью можно получать данные не только из Сети. Можно также выполнять веб-запрос к HTML-файлу, расположенному на локальном компьютере, или к файлу, хранящемуся на сетевом сервере либо на веб-сервере в Интернете. Чтобы получить информацию с веб-сервера, необходимо подключиться к Интернету. Когда данные получены, соединение с Интернетом для работы с ними больше не нужно (конечно, если вы не собираетесь обновлять эти данные).

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

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

Рис. 2. Информация, полученная с помощью веб-запроса

Если вы не собираетесь создавать обновляемый запрос, укажите это в шаге 5 приведенного выше порядка действий. В диалоговом окне Импорт данных нажмите кнопку Свойства и снимите флажок Сохранить определение запроса.

Встроенная в Excel возможность работы с веб-запросами позволяет находить в HTML-документе таблицы (обозначаемые HTML-тегом <TABLE>). Однако веб-дизайнеры для отображения табличной информации все чаще пользуются каскадными таблицами стилей (CSS). Как понятно из данного примера, Excel не распознает такие таблицы и не показывает желтого квадрата со стрелкой, поэтому получить таблицу вы не сможете — вам может потребоваться получить целый документ, а потом удалить (или скрыть) все данные, кроме интересующей вас таблицы.

Еще один способ отобразить данные с веб-страницы на рабочем листе напрямую открыть URL в Excel, воспользовавшись командой Файл –> Открыть. Просто введите URL полностью в поле Имя файла и нажмите Открыть (рис. 2). Результаты могут различаться в зависимости от макета веб-страницы. Как правило, итог удовлетворительный. Иногда вы получаете немного лишней информации. Обратите внимание: такие данные не обновляются. Если информация на веб-странице изменится, нужно будет закрыть книгу и снова воспользоваться командой Файл –> Открыть. Мне не удалось этого сделать. Excel сначала писал, что открывает страницу в режиме защищенного просмотра, долго работал, а потом выдал, что книга повреждена, и открыть ее нельзя((

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 194–196.

Цель книги – предоставить инструменты Excel для автоматизации повторяющихся задач извлечения данных из Интернета. Автор предлагает несколько десятков готового кода VBA и описывает приемы работы в Power Query.

Eduardo Sanchez. Excel and The World Wide Web. Straight to the Point. – Holy Macro! Books, 2021. – 58 p.

Глава 1. Приступая к работе

Что такое HTML?

HTML (Hyper Text Markup Language) – язык гипертекстовой разметки. Он используется для создания веб-сайтов. Гипертекст – это контент, который ведет себя нелинейным образом. Представьте себе веб-сайт, на каждой странице которого есть несколько ссылок на другие страницы, как того же самого сайта, так и других сайтов. Пользователь перемещается, переходя с одной страницы на другую; это гипертекстовое поведение. Обычная печатная книга – это контрпример, ее предполагается читать последовательно.

Информация в HTML помечена тегами; ниже мы поговорим об этом подробнее. Существуют и другие языки, используемые для создания веб-сайтов, такие как CSS и JavaScript, но мы не будем подробно рассматривать их здесь.

CSS (Cascading Style Sheet) – каскадная таблица стилей, язык описания внешнего вида документа. Он работает вместе с HTML, который отвечать за содержимое страницы. Каскадирование означает, что можно использовать несколько CSS-файлов для создания окончательного визуального стиля. Этот язык управляет такими элементами, как размер шрифта, фоновые изображения и цветовая палитра.

JavaScript – язык программирования для реализации динамического поведения на веб-сайтах. С его помощью разработчики могут манипулировать содержимым страницы, создавать диаграммы и взаимодействовать с API (Application Programming Interface, интерфейс прикладного программирования). Обратите внимание, что JavaScript и Java – это два разных языка. Говорят, что в будущем JavaScript может заменить VBA в качестве языка программирования Office.

Одна из замечательных особенностей современных браузеров заключается в том, что они предоставляют исходный код страниц. Если вы используете Google Chrome, просто щелкните правой кнопкой мыши любой элемент страницы и выберите пункт Просмотреть код; в правой части окна появится панель, аналогичная показанной ниже:

Рис. 1. Фрагмент кода HTML веб-страницы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Ключевые слова header, div, aside, nav, h4, li и другие являются тегами.
Когда маленькие черные треугольники указывают вправо, это означает, что их можно щелкнуть, чтобы развернуть и отобразить дополнительную информацию.
Ключевые слова id, class и href являются атрибутами.
<li – это открывающий тег, а </li> – закрывающий.
Элемент может принадлежать к нескольким различным классам.

Если вы чувствуете себя подавленным всем этим, не паникуйте; не обязательно быть программистом HTML, чтобы работать с такого рода автоматизацией. Позже мы увидим, как читать код HTML и извлекать информацию с помощью VBA.

Хотя веб-дизайнеры используют профессиональные редакторы, можно создавать HTML-файлы в стандартных приложениях Windows, например, в Блокноте. Это особенно быть полезно, когда вы хотите протестировать код VBA, который будет взаимодействовать с веб-страницей, но по какой-то причине реальный сайт недоступен.

Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберем ячейку с данными и, если появится вкладка ленты Контекст запроса, данные будут загружены из Power Query.

Знать, в какой среде вы сейчас? Power Query хорошо интегрирован с пользовательским интерфейсом Excel, особенно при импорте данных, работе с подключениями и редактировании таблиц, Excel таблиц и именовых диапазонов. Чтобы избежать путаницы, важно знать, в какой среде вы находитесь, в какой Excel или Power Query в любой момент времени.

Знакомые Excel, лента и сетка

Лента редактора Power Query и предварительный просмотр данных

Например, работа с данными на Excel значительно отличается от Power Query. Кроме того, подключенные данные, которые вы видите на Excel, могут не работать с Power Query для их обработки. Это происходит только при загрузке данных на таблицу или модель данных из Power Query.

Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel , которая называется Лист1,и запросом, созданным путем импорта таблицы Excel Таблица1,ее можно легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.

Вы можете создать запрос из импортируемых данных или пустой запрос.

Создание запроса из импортируемых данных

Это самый распространенный способ создания запроса.

Выберем ячейку в данных и выберите запрос> Изменить.

Создание пустого запроса

Вы можете начать с нуля. Это можно сделать двумя способами.

Выберите Данные> Получить данные >из других источников > пустой запрос.

Выберите Данные> получить данные >запустить редактор Power Query.

На этом этапе вы можете вручную добавлять шаги и формулы, если язык формул Power Query M хорошо разный.

Кроме того, можно выбрать команду На главная в группе Новый запрос. Выполните одно из указанных ниже действий.

Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> "Получить данные" на Excel ленте.

Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.

Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.

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

Загрузка запроса из редактора Power Query

В редакторе Power Query сделайте следующее:

Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & загрузить.

Чтобы загрузить данные в модель данных, выберите Главная > закрыть & Загрузить > Закрыть & Загрузить в.

В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.

Совет Иногда команда "Загрузить в" неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите Запрос > Загрузить в.

Загрузка запроса из области "Запросы и подключения"

В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.

Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).

Существует несколько способов редактирования запроса, загруженного на таблицу.

Изменение запроса на Excel данных

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

Изменение запроса в области "Запросы & подключения"

Возможно, вы & области "Запросы и подключения" удобнее всего использовать, если в одной книге много запросов и вы хотите быстро найти их.

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.

В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.

Изменение запроса в диалоговом окне "Свойства запроса"

В Excel выберите Data > Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.

Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.

Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.

Чтобы открыть модель данных, выберите Power Pivot > Управление.

В нижней части окна Power Pivot выберите вкладку нужной таблицы.

Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.

Обратите внимание на имя таблицы.

Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.

Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.

Завершив внесение изменений в редакторе Power Query, выберите файл> Закрыть & загрузить.

Запрос на этом и в таблице в модели данных обновляются.

Загрузка запроса в модель данных занимает необычно много времени

Если вы заметили, что загрузка запроса в модель данных занимает намного больше времени, чем загрузка на лист, проверьте действия Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит Excel повторно прогонять весь набор данных для каждой строки. Более того, Excel не могут эффективно использовать многопрочитанные выполнения. В качестве обходного решения попробуйте использовать другой оператор, например Равно или Начинает с.

Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.

Вы можете загрузить Power Query:

На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load (&).

В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.

По умолчанию Power Query загружает запросы на новый таблицу при загрузке одного запроса и одновременно загружает несколько запросов в модель данных. Вы можете изменить поведение по умолчанию для всех книг или только текущей книги. При настройке этих параметров Power Query не меняет результаты запроса на месте или в данных модели данных и примечаниях.

Вы также можете динамически переопределять стандартные параметры запроса с помощью диалогового окна Импорт, которое отображается после выбора параметра Закрыть & Загрузкав.

Глобальные параметры, которые применяются во всех книгах

В редакторе Power Query выберите Параметры > параметры и параметры >запроса.

В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.

В разделе Загрузка запроса Параметры по умолчанию сделайте следующее:

Выберите Использовать стандартные параметры загрузки.

Выберите Указать настраиваемые параметры загрузкипо умолчанию , а затем выберите или сберем загрузить на таблицу или Загрузить в модель данных.

Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.

Параметры книги, которые применяются только к текущей книге

В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.

Выполните одно или несколько из указанных ниже действий:

В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.

По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.

В области Связивыберите или сберем создать связи между таблицами при первом добавлении в модель данных.

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

В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.

По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи, в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к новым связям. Тем не менее, если вы хотите сделать это, выберите этот параметр.

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

По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.

См. также

Excel для Интернета включает в себя технологию Power Query (также называемую функцией & преобразования), которая обеспечивает больше возможностей при работе с импортируемыми источниками данных. Вы можете просматривать запросы и связанные с ними сведения в области задач Запросы.

Примечание: Следите за дальнейшими объявлениями об улучшениях Excel интеграции с Веб-сайтом и Power Query.

Просмотр области задач "Запросы"

Убедитесь, что вы в режиме правки (выберите Просмотр> редактирование).

Выберите Запросы > данных.

Excel отображает области задач Запросы и все запросы в текущей книге, а также сведения о запросе, такие как количество строк, дата последнего обновления, расположение и состояние загрузки. Если запрос загружается в модель данных, а не в сетку, Excel отображается "Только подключение".

Некоторые возможности могут присутствовать в одном продукте, но не в других из-за расписаний развертывания и возможностей конкретного узла.

Предварительные требования

Поддерживаемые возможности

Загрузка веб-данных с помощью Power Query Desktop

Для загрузки данных с веб-сайта с помощью Power Query Desktop:

выберите получить данные > веб-сайт в Power BI или из интернета на ленте данных в Excel.

Если введенный URL-адрес является недопустимым, рядом с текстовым полем URL-адреса появится значок предупреждения.

Если вам нужно создать более расширенный URL-адрес перед подключением к веб-сайту, перейдите к разделу Загрузка веб-данных с помощью расширенного URL-адреса.

Для этого соединителя доступны следующие методы проверки подлинности:

Anonymous: Выберите этот метод проверки подлинности, если веб-страница не требует никаких учетных данных.

Windows. выберите этот метод проверки подлинности, если для веб-страницы требуются учетные данные Windows.

Базовый. Выберите этот метод проверки подлинности, если веб-странице требуется базовое имя пользователя и пароль.

Веб-API. Выберите этот метод, если веб-ресурс, к которому выполняется подключение, использует ключ API для проверки подлинности.

Учетная запись организации. Выберите этот метод проверки подлинности, если веб-странице требуются учетные данные Организации.

при отправке отчета в службу Power BI доступны только анонимные, Windows и простые методы проверки подлинности.

Уровень, выбранный для метода проверки подлинности, определяет, к какой части URL-адреса будет применен метод проверки подлинности. Если выбрать веб-адрес верхнего уровня, то выбранный здесь метод проверки подлинности будет использоваться для этого URL-адреса или любого подадреса в этом адресе. Однако вы можете не захотеть задать в качестве верхнего URL-адреса конкретный метод проверки подлинности, так как разные подадресы могут потребовать различных методов проверки подлинности. например, если вы обращаетесь к двум отдельным папкам одного SharePointного сайта и хотели использовать разные учетные записи майкрософт для доступа к каждому из них.

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

В диалоговом окне Навигатор можно выбрать таблицу, а затем либо преобразовать данные в Power Query редактор, выбрав команду преобразовать данные, либо загрузить данные, нажав кнопку загрузить.

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

В нижней левой части диалогового окна Навигатор можно также нажать кнопку Добавить таблицу с помощью примеров . Этот выбор представляет интерактивное окно, в котором можно просмотреть содержимое веб-страницы и ввести образцы значений данных, которые требуется извлечь. Дополнительные сведения об использовании этой функции см. в статье Получение данных веб-страницы с помощью примеров.

Загрузка веб-данных с помощью Power Query Online

Для загрузки данных с веб-сайта с помощью Power Query Online:

В диалоговом окне Получение данных выберите веб-страница или веб-API.

В большинстве случаев необходимо выбрать соединитель веб-страниц. По соображениям безопасности необходимо использовать локальный шлюз данных с этим соединителем. Для соединителя веб-страниц требуется шлюз, так как страницы HTML извлекаются с помощью элемента управления браузера, который включает потенциальные проблемы безопасности. Эта проблема не связана с соединителем веб-API, так как не использует элемент управления браузера.

В некоторых случаях может потребоваться использовать URL-адрес, указывающий на либо API, либо файл, хранящийся в Интернете. В этих сценариях соединитель веб-API (или соединители для конкретных файлов) позволит вам перейти прямо без использования локального шлюза данных.

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

Выберите имя локального шлюза данных.

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

Для этого соединителя доступны следующие методы проверки подлинности:

Anonymous: Выберите этот метод проверки подлинности, если веб-страница не требует никаких учетных данных.

Windows. выберите этот метод проверки подлинности, если для веб-страницы требуются учетные данные Windows.

После выбора метода проверки подлинности нажмите кнопку Далее.

В диалоговом окне Навигатор можно выбрать таблицу, а затем преобразовать данные в редакторе Power Query, выбрав преобразовать данные.

Загрузка веб-данных с помощью расширенного URL-адреса

При выборе получить данные > из Интернета в Power Query рабочем столе в большинстве случаев в базовом параметре будут вводиться URL-адреса. Однако в некоторых случаях может потребоваться собрать URL-адрес из отдельных частей, установить время ожидания для соединения или предоставить отдельные данные заголовка URL-адреса. В этом случае выберите пункт Дополнительно в диалоговом окне из Интернета .

В зависимости от того, как долго запрос POST обрабатывает данные, может потребоваться продлить время, в течение которого запрос будет оставаться подключенным к веб-сайту. Время ожидания по умолчанию для POST и GET составляет 100 секунд. Если время ожидания слишком мало, можно использовать необязательное время ожидания команды в минутах , чтобы продлить количество минут, в течение которых вы постоянно подключены.

Импорт файлов из Интернета

Обычно при импорте локального локального файла в Power Query Desktop вы будете использовать конкретный соединитель типа файлов для импорта этого файла, например соединитель JSON для импорта JSON-файла или соединителя CSV для импорта CSV-файла. Однако если вы используете Power Query Desktop и импортируемый файл находится в Интернете, для импорта этого файла необходимо использовать веб-соединитель. Как и в локальном случае, вы увидите таблицу, которую соединитель загружает по умолчанию, которую можно затем загрузить или преобразовать.

Веб-соединитель поддерживает следующие типы файлов:

В диалоговом окне Получение данных выберите веб- соединитель.

Щелкните ОК.

если вы посещаете этот URL-адрес в первый раз, выберите Anonymous в качестве типа проверки подлинности, а затем выберите Подключение.

Редактор Power Query теперь откроется с данными, импортированными из JSON. Перейдите на вкладку вид в редакторе Power Query, а затем выберите строку формул , чтобы включить строку формул в редакторе.

Как видите, веб-соединитель возвращает веб-содержимое из предоставленного URL-адреса, а затем автоматически заключает содержимое веб-содержимого в соответствующий тип документа, указанный URL-адресом ( Json.Document в этом примере).

Обработка динамических веб-страниц

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

Вы обновите сайт.
Отображается ошибка (например, "столбец" foo "таблицы не найден).
Вы снова обновите сайт.
Ошибок не возникает.

Решением является передача WaitFor параметра в Web.BrowserContents , который указывает либо селектор, либо период времени, который должен ожидать перед загрузкой HTML.

Как можно определить, является ли страница динамической? Обычно это довольно просто. Откройте страницу в браузере и просмотрите ее нагрузку. Если содержимое отображается сразу же, то это обычная HTML-страница. Если он отображается динамически или с течением времени, это динамическая страница.

Читайте также: