Получение внешних данных с веб-страницы

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

Если у вас Excel 2010 или более поздней версии, вы можете подключиться к веб-странице с помощью интерфейса Excel для получения и преобразования данных (Power Query).

В этой статье

Дополнительные сведения о получении внешних данных с веб-страницы

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

Создание веб-запроса с параметрами

Копирование данных с веб-страницы

Выполнение сохраненного файла веб-запроса

Перенаправление веб-запроса с веб-страницы в другой источник данных

Решение проблем с сортировкой, фильтрацией и поиском внешних данных из веб-запроса

Дополнительные сведения о получении внешних данных с веб-страницы

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

Веб-запросы особенно удобно использовать для извлечения данных, содержащихся в таблицах или предварительно отформатированных областях. (Таблицы определяются с помощью тега HTML <TABLE>. Предварительно отформатированные области обычно определяются с помощью тега HTML <PRE>.) Извлекаемые данные не включают рисунки (например, изображения GIF) и содержимое сценариев.

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

Образцы веб-запросов

Вместе с Microsoft Office Excel 2007 устанавливается несколько образцов веб-запросов. По умолчанию эти запросы находятся в папке \Program Files\Microsoft Office\Office12\QUERIES на жестком диске:

  • MSN MoneyCentral Investor Major Indicies.iqy

  • MSN MoneyCentral Investor Currency Rates.iqy

  • MSN MoneyCentral Investor Stock Quotes.iqy (веб-запрос с параметрами)

Способы получения внешних данных

Если используется Excel, размещенные на веб-странице данные можно импортировать с помощью диалогового окна Создание веб-запроса. (На вкладке Данные в группе Внешние данные нажмите кнопку Из Интернета.)

Если используется Internet Explorer, получить внешние данные в Office Excel можно двумя способами.

  • Используйте стандартные команды копирования и вставки для переноса данных с веб-страницы на лист Excel. При вставке данных веб-страницы в Excel их можно оставить статическими или сделать обновляемыми, нажав кнопку Параметры вставки Изображение кнопки и выбрав команду Создать обновляемый веб-запрос.

  • Щелкните веб-страницу правой кнопкой мыши и выберите в контекстном меню команду Экспорт в Microsoft Excel. Откроется диалоговое окно Создание веб-запроса.

Примечание : Можно также открыть в Excel любой HTML-, MHTML-файл или правильно структурированный XML-файл. При открытии HTML- или MHTML-файла отображается вся веб-страница, но могут быть потеряны некоторые элементы форматирования, сценарии, файлы изображений (в случае HTML) или списки данных в одной ячейке. При открытии XML-файла можно применить одну или несколько доступных таблиц стилей XML. Во всех этих случаях данные не будут обновляемыми.

Создание веб-запроса с параметрами

Запрос с параметрами — это запрос, при каждом запуске которого предлагается ввести параметры. Можно создать веб-запрос с параметрами путем изменения сохраненного файла веб-запроса (IQY-файла). Например, веб-запрос, который получает котировки акций с веб-страницы, может при выполнении запрашивать символ акции.

Веб-запрос с параметрами основан на строке запроса URL-адреса. Эта одна или несколько пар, состоящих из имени и значения, которые добавляются в конец URL-адреса. Некоторые веб-сайты изменяют содержимое веб-страницы с учетом строки запроса URL-адреса. Строка запроса URL-адреса имеет следующий формат: 1 2 3 4 5 -----------------------------URL------------------------------?<Name>=<Val> http://moneycentral.msn.com/investor/external/excel/quotes.asp?quote=MSFT

1

URL-адрес

URL-адрес веб-страницы

2

?

Строка запроса URL-адреса начинается с вопросительного знака (?).

3

<Имя>

Имя, в этом примере — Котировка.

4

=

Знак равенства (=) отделяет имя от значения.

5

<Значение>

Значение, в этом примере — MSFT.

Если необходимо передать несколько пар, состоящих из имени и значения, отделите их амперсандом (&). Например:1 2 3 ...?<Name>=<Val>&<Name>=<Val> ...?Quote=MSFT&Quote=IBM

1

<Имя>=<Значение>

Первой парой из имени и значения является Котировка=MSFT.

2

&

Амперсанд (&) разделяет пары, состоящие из имя и значения.

3

<Имя>=<Значение>

Второй парой из имени и значения является Котировка=IBM.

Веб-запрос с параметрами создается на основе строки запроса URL-адреса в указанном ниже формате. Например:1 2 3 4 5 6 ...?<Name>=["Parameter","Prompt"] ...?Symbol=["StockSymbol","Enter stock symbol:"]

1

<Имя>

Имя, в этом примере — Символ.

2

=

Знак равенства (=) отделяет имя от строки параметра и приглашения.

3

"Параметр"

Имя параметра, заключенное в кавычки, которое используется для определения параметра в диалоговом окне Параметры. В этом примере — СимволАкции.

4

,

Запятая (,) отделяет параметр от приглашения.

5

"Приглашение"

Приглашение, заключенное в кавычки, которое отображаются в диалоговом окне Excel Введите символ акции. В данном примере это Введите символ акции.

6

[...]

Левая и правая квадратные скобки ([]) используются для отделения строки приглашения.

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

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

Например, если на HTML-странице приведена таблица котировок акций, эти табличные данные можно использовать в веб-запросе, даже если они берутся из другого источника. Или же, если на сайте Windows SharePoint Services есть таблица со сводкой данных о ходе проекта, можно создать веб-запрос на извлечение данных из главного списка сведений о состоянии проекта.

Такое перенаправление запросов дает два важных преимущества. Во-первых, импортируемую веб-страницу HTML можно специально отформатировать для просмотра, даже если эти данные оптимизированы для анализа (например, в формате XML). Во-вторых, HTML-представление данных может быть разбито на страницы данных (например, по 20 результатов на странице), но веб-запрос можно перенаправить на полный набор данных.

Вместе с тегом TABLE, PRE, XMP, LISTING или PLAINTEXT можно использовать атрибут HTML-кода, обозначающий место хранения исходных данных. Разработчики веб-страниц могут использовать этот атрибут перенаправления, o:WebQuerySourceHRef, для указания URL-адреса источника данных, чтобы веб-запросы могли устанавливать связь с базовыми исходными данными. Этот атрибут определяется в пространстве имен Microsoft Office во избежание конфликтов с другими атрибутами перенаправления. Источник данных может относиться к любому типу данных, поддерживаемому веб-запросами, такому как HTML, XML или текст. Используя этот атрибут, можно не беспокоиться о правильности содержимого, получаемого при обновлении. Веб-запросы содержат сведения о пути к источнику данных и могут извлекать обновленные данные непосредственно из источника.

К началу страницы

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

  1. Для создания нового или изменения существующего веб-запроса выполните одну из указанных ниже процедур.

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

    1. Выполните одно из следующих действий:

      • В Office Excel на вкладке Данные в группе Внешние данные нажмите кнопку Из Интернета.

      • В браузере перейдите на веб-страницу, с которой требуется запрашивать данные, щелкните ее правой кнопкой мыши и выберите в контекстном меню команду Экспорт в Microsoft Excel.

        Откроется диалоговое окно Создание веб-запроса.

    2. В диалоговом окне Создание веб-запроса введите URL-адрес веб-страницы, с которой требуется получить данные. Можно ввести этот адрес с клавиатуры, вставить из буфера обмена или щелкнуть стрелку справа от поля Адрес и выбрать из списка один из недавно посещенных адресов.

      Примечание : Максимальная длина URL-адреса составляет 255 символов.

    3. Нажмите кнопку Перейти.

      Изменение существующего веб-запроса    

    4. На вкладке Данные в группе Подключения нажмите кнопку Подключения.

      Изображение ленты Excel

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

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

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

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

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

    1. В группе Форматирование выберите один из следующих вариантов.

      • Нет.     Возврат с запрашиваемой веб-страницы текста без форматирования. К нему будет применено форматирование, действующее на листе.

      • Только формат RTF.     Возврат текста с сохранением форматирования, которое наиболее адекватно воспроизводится в Office Excel, например начертаний шрифтов. Элементы форматирования HTML, такие как гиперссылка, не сохраняются.

      • Полностью формат HTML.     Возврат всех элементов форматирования HTML, поддерживаемых в Excel, например форматирования гиперссылок. Выбор этого варианта приводит к снятию флажка Автоформат данных в диалоговом окне Свойства внешнего диапазона.

    2. В группе Импорт настроек для блоков <PRE> установите один или несколько следующих флажков.

      • Импорт блоков <PRE> в столбцы.     Блоки данных, заключенные в теги <PRE>, будут импортироваться в отдельные столбцы на листе. Чтобы все данные возвращались в одном столбце, снимите этот флажок.

      • Считать последовательные разделители одним.     Если в блоках <PRE> используются символы, отделяющие (ограничивающие) текст каждого столбца (например, запятые), то в результате установки этого флажка несколько стоящих подряд таких разделителей будут рассматриваться в Excel как один разделитель. В этих случаях между соседними разделителями не вставляется пустой столбец. Данный флажок доступен, только если установлен флажок Импорт блоков <PRE> в столбцы.

      • Одинаковые настройки для всего раздела.     Установите этот флажок, чтобы значение параметра Считать последовательные разделители одним использовалось для всех предварительно отформатированных разделов веб-страницы. Снимите флажок, если требуется, чтобы оно применялось только для первого предварительно отформатированного раздела или чтобы в Excel автоматически определялись оптимальные настройки. Данный флажок доступен, только если установлен флажок Импорт блоков <PRE> в столбцы.

    3. В группе Другие параметры импорта установите один или несколько следующих флажков.

      • Отключить распознавание дат.     Если этот флажок установлен, то числа на веб-странице, которые могут трактоваться как даты, будут отображены на листе в виде чисел. Например, если этот флажок не установлен, результат спортивного матча 03-07 будет распознан в Excel как дата 7 марта.

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

  4. Нажмите кнопку ОК. Веб-запрос сохраняется вместе с книгой.

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

    Дополнительные сведения см. в разделе Создание веб-запроса с параметрами.

    Сохранение веб-запроса в файле веб-запроса

    1. В диалоговом окне Изменить веб-запрос нажмите кнопку Сохранить запрос Изображение кнопки .

    2. Перейдите к папке, в которой следует сохранить запрос.

      По умолчанию запросы сохраняются в папке \Documents and Settings\<имя_пользователя>\Application Data\Microsoft\Queries на жестком диске.

    3. В поле Имя файла введите имя файла.

    4. Нажмите кнопку Сохранить.

      Запрос сохраняется в текстовом файле с расширением имени IQY.

  6. Нажмите кнопку Импорт.

    В диалоговом окне Импорт данных выполните одно из следующих действий:

    1. Чтобы поместить данные с веб-страницы на текущий лист, выберите вариант Имеющийся лист. На листе выделите ячейку, в которой будет располагаться левый верхний угол диапазона внешних данных, и нажмите кнопку ОК.

    2. Чтобы поместить данные на новый лист, выберите вариант Новый лист и нажмите кнопку OK. В данную книгу будет добавлен новый лист, в левый верхний угол которого будет помещен диапазон внешних данных.

      После нажатия кнопки ОК в строка состояния появится значок Фоновое обновление Индикатор обновления , показывающий, что запрос выполняется. Для проверки состояния запроса дважды щелкните значок обновления.

К началу страницы

Создание веб-запроса с параметрами

  1. В проводнике найдите файл запроса на компьютере.

    По умолчанию запросы сохраняются в папке \Documents and Settings\<имя_пользователя>\Application Data\Microsoft\Queries на жестком диске.

  2. Щелкните файл правой кнопкой мыши и выберите команду Изменить в блокноте. Запрос будет открыт в Блокноте, где можно внести в него изменения.

  3. В тексте файла найдите URL-адрес веб-страницы, с которой были получены импортированные данные. Например:

http://moneycentral.msn.com/investor/external/excel/quotes.asp

В конце URL-адреса введите следующее (без пробела после URL-адреса):

?Symbol=["Quote", "Enter one or more financial symbols separated by commas."]

  1. В меню Файл выберите команду Сохранить, а затем закройте Блокнот.

К началу страницы

Копирование данных с веб-страницы

  1. В веб-браузере выделите данные, которые требуется скопировать.

  2. Скопируйте данные.

    Примечание : Сведения о том, как выполнить копирование, см. в справке браузера.

  3. Переключитесь в окно Excel.

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

  5. На вкладке Главная в группе Буфер обмена нажмите кнопку Вставить.

    Изображение ленты Excel

  6. Если данные выглядят не так, как ожидалось, нажмите кнопку Параметры вставки Изображение кнопки и выберите один из следующих вариантов:

    • Сохранить исходное форматирование — вставка без изменений.

    • Использовать форматы конечных ячеек — вставка с учетом форматирования ячейки.

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

К началу страницы

Выполнение сохраненного файла веб-запроса

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

  1. Нажмите кнопку Microsoft Office Изображение кнопки Office и выберите Открыть.

  2. В поле Папка найдите папку, в которой хранится файл запроса.

  3. Выберите веб-запрос, который требуется выполнить.

  4. Нажмите кнопку Открыть.

  5. В диалоговом окне Импорт данных выполните одно из следующих действий:

    • Чтобы поместить данные с веб-страницы на текущий лист, выберите вариант Имеющийся лист. На листе выделите ячейку, в которой будет располагаться левый верхний угол диапазона внешних данных, и нажмите кнопку ОК.

    • Чтобы поместить данные на новый лист, выберите вариант Новый лист и нажмите кнопку OK. В данную книгу будет добавлен новый лист, в левый верхний угол которого будет помещен диапазон внешних данных.

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

    Чтобы изменить значения параметров, нажмите кнопку Параметры в диалоговом окне Импорт данных на шаге 5.

    Дополнительные сведения см. в статье Настройка запроса с параметрами.

    Начнется выполнение запроса. При этом в строке состояния появится значок Фоновое обновление Индикатор обновления , показывающий, что запрос выполняется. Для проверки состояния запроса дважды щелкните значок обновления.

К началу страницы

Перенаправление веб-запроса с веб-страницы в другой источник данных

  1. Включите в открывающий тег HTML веб-страницы объявление пространства имен Microsoft Office:

    <HTML xmlns:o="urn:schemas-microsoft-com:office:office”>

  2. В тексте кода веб-страницы добавьте к открывающему тегу TABLE, PRE, XMP, LISTING или PLAINTEXT указанный ниже атрибут. Например:

    <TABLE ... o:WebQuerySourceHRef="URL">,

    где следует указать URL-адрес (относительный или абсолютный), на который будет перенаправлен веб-запрос.

Примечание :  Пользователь может отменить перенаправление на веб-странице, установив флажок Отключить перенаправление веб-запросов в диалоговом окне Настройка веб-запроса. Это окно доступно из диалогового окна Создание веб-запроса.

К началу страницы

Решение проблем с сортировкой, фильтрацией и поиском внешних данных из веб-запроса

Во внешних данных веб-запроса могут содержаться начальные, конечные или множественные внедренные символы пробела в кодировке Юникод (значение 160). Такие символы могут вызывать непредвиденные результаты при сортировке и фильтрации данных, а также при выполнении поиска.

Дополнительные сведения о том, что делать с этими и подобными им символами, см. в статье Удаление из текста пробелов и непечатаемых знаков.

К началу страницы

См. также

Импорт данных из внешних источников (Power Query)

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×