Этот веб запрос не возвращает данные excel

Обновлено: 06.07.2024

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

Вставка статической информации

Один из способов получить данные из веб-страницы на лист — просто выделить текст в браузере, нажать Ctrl+C, чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал — в комплекте с настройками форматирования, гиперссылками и графикой.

Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная ► Буфер обмена ► Вставить, можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная ► Буфер обмена ► Вставить ► Специальная вставка, а затем пробовать различные варианты вставки.

Вставка обновляемой информации

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

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

Рис. 176.2. Данные, импортированные из веб-страницы

По умолчанию импортированные данные — это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить. Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.

Открытие веб-страницы напрямую

Еще один способ получить данные веб-страницы на лист — открыть URL-адрес напрямую, с помощью команды Файл ► Открыть. Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть. Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.

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

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

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

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

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

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

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

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

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

В Excel есть множество встроенных инструментов для импорта данных из самых разных внешних источников. Есть среди них, разумеется, и инструмент для выгрузки информации из Интернета.

Все инструменты для импорта данных расположены на ленте на вкладке "Данные".

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

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

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

Если у Вас Excel 2013 или более старый, то этот мастер откроется по умолчанию при выборе команды "Из интернета" на вкладке "Данные". Если у Вас Excel 2016, то после того, как Вы включили старый мастер в настройках, как показано выше, он будет доступен по следующему пути: "Данные"-"Получить данные"-"Устаревшие мастеры"-"Из интернета (прежних версий)".

После запуска откроется окно "Создание веб-запроса". Оно похоже на стандартное окно браузера. В адресную строку вверху нужно ввести URL страницы, с которой необходимо выгрузить данные. После того, как страница будет загружена, все данные, которые Excel может импортировать, будут помечены небольшой желтой стрелкой. Кликните по ней и нажмите кнопку "Импорт" в правом нижнем углу. Если на странице нет желтых стрелок или они расположены не там, где нужно, то данным способом выгрузить данные в Excel не получится.

До нажатия кнопки импорт, можете задать некоторые настройки, кликнув на кнопке "Параметры" в правом верхнем углу. Наиболее полезной здесь является настройка "Отключить распознавание дат". Она позволяет избежать частой ошибки, вызванной особенностями региональных настроек системы, когда число в формате "2.15" выгружается в Excel как "фев.15".

После нажатия кнопки "Импорт" программа спросит у Вас, куда именно нужно выгрузить данные. После указания листа и диапазона, таблица из интернета будет выгружена в файл Excel. При этом, будет создан именованный диапазон, содержащий выгруженные ячейки.

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

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

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

2. Импорт данных с помощью Power Query

После нажатия кнопки "Из интернета" в Excel 2016 Вы увидите не окно "Создание веб-запроса", а маленькое окошко, в котором нужно будет указать адрес страницы.

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

Если результат предпросмотра Вас устраивает, жмите кнопку "Загрузить". Если нет, то можно нажать кнопку "Правка". Тогда Вы попадете в окно редактора Power Query. Здесь можно настроить обработку данных после получения их из Интернета и перед выгрузкой на лист. Например, удалить лишние столбцы, изменить заголовки или поменять регистр текста.

После того, как данные примут нужный вид, нажмите кнопку "Закрыть и загрузить" на вкладке "Главная" в редакторе запросов.

Данные будут выгружены на новый лист. Обратите внимание, что импортированная таблица будет автоматически преобразована в "умную таблицу", а в списке запросов ("Данные" - "Запросы и подключения") появится новый запрос.

Обновить его можно кликнув правой кнопкой мыши и выбрав команду "Обновить", либо нажав на иконку в правом верхнем углу рядом с названием запроса. Там же (в контекстном меню по правой кнопке мыши) есть команда "Свойства", с помощью которой можно, например, настроить автообновление запроса.

В отличие от старого мастера импорта, Power Query имеет ряд преимуществ:

1) Может работать с гораздо большим числом сайтов и страниц;

2) Может осуществлять промежуточную обработку данных перед тем, как они будут выгружены на лист;

3) Выгружает данные из Интернета гораздо быстрее;

4) Автоматически создает "Умную таблицу".

Учитывая всё вышесказанное, настоятельно рекомендуем Вам пользоваться новыми версиями Excel в целом и Power Query в частности (в Excel 2016 он встроен по умолчанию, в Excel 2010 и 2013 может быть установлен как бесплатная надстройка).

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Случается, что люди, использующие Excel в своей повседневной жизни, сталкиваются с огромным количеством копипейста. Например, загрузка финансового отчета из Google Finance или котировки акций с сайта РТС или просто список лучших 250 фильмов. Что мы обычно при этом делаем? Открываем браузер, копируем/вставляем данные в Excel, анализируем и делаем отчет (на фиксированную дату). С помощью веб запросов можно производить аналитику в режиме реального времени.

5 шагов импорта данных в Excel листы с помощью веб-запросов:

Открываем Excel. Идем во вкладку Данные -> Получение внешних данных –> Из Интерента

В появившемся диалоговом окне вводим URL страницу, с которой мы хотим получить данные и жмем «Пуск»

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

Жмем «Импорт». В следующем диалоговом окне указываем область, куда следует поместить импортируемые данные. Вы также можете управлять поведением веб-запроса, изменяя значения в окне Параметры, но, как правило, этого не требуется. Во время импорта вы увидите крутящуюся иконку с земным шаром в строке состояния.

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

Вам также могут быть интересны следующие статьи

8 комментариев

Средствами программы Excel никак. То что вы хотите реализовать называется парсингом и это отдельная обширная тема для разговоров вне рамок Excel

Василий, спасибо за комментарий. Про парсинг в Excel никто не говорил

Это всё понятно. Но на новой версии excel 365 при просмотре сайта отсутствует возможность поставить галочку. Типа веб-представление в режиме совместимости с internet-explorer и поэтому выделения в таблицах отключены. Как быть в такой ситуации?

подключение к Microsoft Graph интерфейсам api Power Query не рекомендуется или не поддерживается. Дополнительные сведения см. в этой статье .

Использование шлюза с веб-соединителем

при каждом использовании веб-соединителя для получения данных из страницы HTML необходимо использовать локальный шлюз данных при публикации в облачной службе, независимо от того, Power BI ли он служба для наборов данных или потокового Power Apps обмена. (в настоящее время Dynamics 365 Customer Insights не поддерживает использование шлюза.) **Options and settings** > **Options** > **Preview features**. The option is enabled by default. -->

Сбор веб-запросов и отзыв сертификатов

Мы усиливается безопасность веб-подключений для защиты данных. Однако это означает, что определенные сценарии, такие как захват веб-запросов с помощью Fiddler, больше не будут работать по умолчанию. Чтобы включить эти сценарии, выполните следующие действия.

Откройте Power BI Desktop.

На вкладке файл выберите Параметры и > Параметры.

В области Параметры в разделе Глобальная > Безопасность снимите флажок включить проверку отзыва сертификатов.

Щелкните ОК.

Перезапустите Power BI Desktop.

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

Чтобы задать этот сценарий в групповая политика, используйте ключ "Дисаблецертификатеревокатиончекк" в пути реестра "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Power BI Desktop". Если задать для параметра "Дисаблецертификатеревокатиончекк" значение 0, всегда будет включена проверка (остановка Fiddler и похожего программного обеспечения), а установка "Дисаблецертификатеревокатиончекк" в значение 1 всегда отключит проверку (включение Fiddler и аналогичного программного обеспечения).

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

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

Ограничения на проверку подлинности веб-соединителя для содержимого HTML

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

Устаревший веб-соединитель Power Query автоматически создает Web.Page запрос, поддерживающий проверку подлинности. единственное ограничение возникает, если в диалоговом окне метод проверки подлинности выбрано Windows проверка подлинности. В этом случае выбор " использовать мои текущие учетные данные " работает правильно, но использование альтернативных учетных данных не будет проходить проверку подлинности.

новая версия веб-соединителя (в настоящее время доступна в Power BI Desktop) автоматически создает Web.BrowserContents запрос. В настоящее время такие запросы поддерживают только анонимную проверку подлинности. Другими словами, новый веб-соединитель нельзя использовать для подключения к источнику, для которого требуется неанонимная проверка подлинности. Это ограничение применяется к Web.BrowserContents функции независимо от среды размещения.

в настоящее время Power BI Desktop автоматически использует Web.BrowserContents функцию. Web.Page функция по-прежнему используется автоматически Excel и Power Query в сети. Power Query Online поддерживает Web.BrowserContents использование локального шлюза данных, но в данный момент придется вводить такую формулу вручную. Когда веб-пример становится доступным в Power Query Online в середине октября 2020, этот компонент будет использовать Web.BrowserContents .

для Web.Page работы функции требуется, чтобы на компьютере был установлен Internet Explorer 10. при обновлении Web.Page запроса с помощью локального шлюза данных на компьютере, содержащем шлюз, должен быть установлен Internet Explorer 10. если вы используете только Web.BrowserContents функцию, вам не нужно устанавливать Internet Explorer 10 на компьютере или компьютере, содержащем локальный шлюз данных.

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

в Power BI Desktop можно использовать более старую Web.Page функцию, сняв функцию предварительного просмотра вывода веб-таблицы .