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

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

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

При помощи Power Query мы получим табличные данные с каждой веб-страницы, а потом объединим их по вертикали в одну большую таблицу.

Решение

Для решения мы воспользуемся следующим:

• Web.Page
• Web.Contents
• Список из чисел
• Table.FromList
• Ссылки

Формула для получения данных для одного года будет следующей:

= Web.Page(Web.Contents("http://www.calc.ru/kotirovka-dollar-ssha.html?date=2000")){[ClassName="result_table"]}[Data]

Нам нужно эту формулу применить к каждому году с 2000 по 2018. Для этого вместо вместо подстроки «2000» мы будем передавать параметр из отдельного столбца. К каждой строке этого столбца мы применить эту формулу.

Примененные функции

• Table.FromList
• Splitter.SplitByNothing
• Table.AddColumn
• Web.Page
• Web.Contents
• Text.From
• Table.ExpandTableColumn
• Table.TransformColumnTypes
• Table.UnpivotOtherColumns
• Table.CombineColumns
• Combiner.CombineTextByDelimiter
• QuoteStyle.None
• Table.SelectRows
• Table.NestedJoin
• JoinKind.LeftOuter
• Table.RemoveColumns
• Table.Sort
• Table.ReplaceValue
• Replacer.ReplaceValue

Код

let
   list_years = Table.FromList(
      {2000 .. 2018}, 
      Splitter.SplitByNothing(), 
      {"Год"}
   ), 
   tab_add_col = Table.AddColumn(
      list_years, 
      "Пользовательская", 
      each Web.Page(
         Web.Contents(
            "http://www.calc.ru/kotirovka-dollar-ssha.html?date="
               & Text.From([Год])
               & ""
         )
      ){[ClassName = "result_table"]}[Data]
   ), 
   col_expand = Table.ExpandTableColumn(
      tab_add_col, 
      "Пользовательская", 
      {"Дата", "Курс"}, 
      {"Дата", "Курс"}
   ), 
   col_type = Table.TransformColumnTypes(col_expand, {{"Дата", type date}})
in
   col_type

Этот урок входит в курс Веб-запросы в Power Query

Многие люди используют Excel для записи данных или выполнения вычислений. Но одна малоизвестная функция Excel — это возможность извлекать данные из Интернета и переносить их в электронные таблицы.

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

Как работает веб-парсинг в Excel

Вы можете получить «внешние» данные из Excel. Просто имейте в виду следующие требования:

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

Когда вы настраиваете парсинг веб-страниц в Excel, вы создаете «веб-запрос». Этот запрос использует веб-URL для подключения к Интернету и получения веб-страницы с данными с веб-сервера. Excel обрабатывает входящий HTML-код и удаляет таблицы, которые присутствуют на этой странице. У вас будет возможность выбрать таблицу или таблицы, которые вы хотите сохранить в своей электронной таблице.

Как создать веб-запрос в Excel

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

1. Откройте Excel и выберите Данные меню.

2. На ленте под Получение и преобразование данных раздел, выберите Из Интернета.

3. Откроется окно веб-запроса. Вставьте URL-адрес страницы с данными в URL поле. Выбирать хорошо.

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

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

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

Поздравляем, вы успешно удалили данные веб-страницы с помощью Excel!

Расширенные параметры веб-запросов Excel

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

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

Варианты здесь включают:

• Стандартный стол
• Отчет сводной таблицы
• Сводная диаграмма
• Просто создайте соединение, но не импортируйте данные

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

Если вы выберете Передовой вместо Basic на странице URL-адреса можно предоставить более подробную информацию об URL-адресе.

Это полезно, если страница, с которой вы загружаете данные, включает параметры (обычно после символа «?») Или специальные запросы заголовка HTTP, например страницы, требующие параметров запроса заголовка JSON HTTP.

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

Как настроить веб-запрос в Excel

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

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

Откроется окно редактора веб-запросов. Кроме того, вы можете выбрать любую ячейку результатов, а затем щелкнуть небольшой значок листа справа от таблицы в поле «Запросы и подключения» справа. Откроется предварительный просмотр выбранных вами табличных данных. Выберите Редактировать кнопку внизу этого окна.

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

Варианты здесь включают:

• Изменение исходного URL-адреса (выберите Источник в разделе «Прикладные шаги»).
• Редактирование исходной таблицы для извлечения данных этой таблицы Excel (выберите Навигация в разделе «Прикладные шаги»).
• Изменение способа форматирования или преобразования данных в Excel перед их размещением на листе (выберите «Измененный тип» в разделе «Примененные шаги»).

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

Как обновлять таблицы вручную и автоматически

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

Вы можете обновить вручную, выбрав лист с таблицей, которую вы хотите обновить, выбрав Данные меню и выбрав Обновить все из ленты.

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

Более автоматизированный способ сделать это — запланировать обновления. Для этого выберите стрелку раскрывающегося списка внизу Обновить все кнопку и выберите Свойства подключения.

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

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

Веб-парсинг в Excel

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

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

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

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

Microsoft Excel

трюки • приёмы • решения

Как импортировать данные из веб-страниц в таблицу Excel

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

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

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

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

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

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

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

1. Выберите Данные ► Получение внешних данных ► Из Интернета для открытия диалогового окна Создание веб-запроса.
2. В поле Адрес введите URL сайта и нажмите кнопку Пуск. Для этого примера URL-адрес веб-страницы, показанной на рис. 176.1, будет таким: http://cbr.ru. Обратите внимание, что диалоговое окно Создание веб-запроса содержит мини-браузер (Internet Explorer). Вы можете переходить по ссылкам и посещать сайты, пока не найдете данные, которые вас заинтересуют. Когда веб-страница отображается в окне Создание веб-запроса, вы видите одну или несколько желтых стрелок, которые соответствуют различным элементам на веб-странице.
3. Щелкните на желтой стрелке, и она превратится в зеленый флажок, который указывает, что данные этого элемента будут импортированы. Вы можете импортировать столько элементов, сколько нам нужно. Для этого
   примера я щелкну на стрелке рядом с таблицей курсов.
4. Нажмите кнопку Импорт для открытия диалогового окна Импорт данных.
5. В окне Импорт данных укажите место для импортированных данных. Это может быть ячейка в существующем или новом листе.
6. Нажмите кнопку ОК, и Excel импортирует данные (рис. 176.2).

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

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

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

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

Как переносить табличные данные с сайта в «Excel». Порядок действий для импорта таблицы из интернет-сайта

Как переносить табличные данные с сайта в «Excel».

Если вы обладаете достаточным количеством времени и ресурсов перенести данные с сайта в таблицу «Excel» можно в «ручном режиме». Тот случай, когда таблица на сайте выделяется курсором мыши, копируется и вставляется в файл «Эксель». Естественно, этот способ долог и неудобен.

Я хочу рассказать Вам о другом, автоматизированном способе переноса данных с сайта в программу «Excel». Этот способ позволяет настроить процесс импорта обновленных актуальных данных с сайта прямо в таблицу одним нажатием кнопки мыши.

Для автоматизации импорта данных в «Эксель» из интернета потребуется «Excel» версии 2013 и выше, а так же надстройка Power Query.

Последовательность настройки скачивания данных:

Порядок действий для импорта таблицы из интернет-сайта в таблицу Excel.

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

• Шаг 5. Выбрать нужную таблицу из предложенного списка и нажать «Правка» или «Загрузить»

Если нажать «Загрузить», таблица будет импортирована целиком. В режиме правки можно редактировать вид загружаемой таблицы, выбирать нужные столбцы и т.д.

Результат скачивания таблицы в Excel

Учебник: импорт данных в Excel и создание модели данных

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

Аннотация. Это первый учебник из серии, который поможет ознакомиться с программой Excel и ее возможностями объединения и анализа данных, а также научиться легко использовать их. С помощью этой серии учебников вы научитесь создавать с нуля и совершенствовать рабочие книги Excel, строить модели данных и создавать удивительные интерактивные отчеты с использованием надстройки Power View. В этих учебниках приводится описание возможностей средств бизнес-аналитики Майкрософт в Excel, сводных таблиц, Power Pivot и Power View.

Примечание: В этой статье описаны модели данных в Excel 2013. Однако те же функции моделирования данных и Power PIVOT, представленные в Excel 2013, также применимы к Excel 2016.

Вы узнаете, как импортировать и просматривать данные в Excel, строить и совершенствовать модели данных с использованием Power Pivot, а также создавать с помощью надстройки Power View интерактивные отчеты с возможностью публикации, защиты и предоставления общего доступа.

Учебники этой серии

Импорт данных в Excel 2013 и создание модели данных

В этом учебнике вы начнете работу с пустой книги Excel.

Разделы учебника

В конце учебника есть тест, с помощью которого можно проверить свои знания.

В этой серии учебников используются данные, описывающие спортивных medals, страны размещения и различные спортивных спортивные мероприятия. Мы рекомендуем вам пройти каждый из этих учебников по порядку. Учебники также используют Excel 2013 с Power Pivot. Для получения дополнительных сведений о Excel 2013 щелкните здесь. Для получения инструкций по включению Power Pivot щелкните здесь.

Импорт данных из базы данных

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

Сначала загрузим данные из Интернета. Эти данные об олимпийских медалях являются базой данных Microsoft Access.

Щелкните следующие ссылки, чтобы скачать файлы, используемые во время этого ряда учебников. Скачайте каждый из четырех файлов в нужное место, например загружаемЫе файлы или Мои документы, или новую созданную папку.
_Гт_ олимпикмедалс. accdb Access

Откройте пустую книгу в Excel 2013.

Щелкните данные _Гт_ получить внешние данные _Гт_ из Access. Лента настраивается динамически в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от следующих экранов. На первом экране отображается лента, если книга является широкой, а на втором — книга, размер которой был изменен, чтобы занимать только часть экрана.

Выберите скачанный файл Олимпикмедалс. accdb и нажмите кнопку Открыть. Откроется следующее окно Выбор таблицы, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на листы и таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Нажмите кнопку ОК.

Появится окно «Импорт данных».

Примечание: Обратите внимание на флажок в нижней части окна, с помощью которого вы можете добавить эти данные в модель данных, как показано на следующем экране. Модель данных создается автоматически при одновременном импорте двух или нескольких таблиц. Модель данных интегрирует таблицы, обеспечивая расширенный анализ с помощью сводных таблиц, Power Pivot и Power View. При импорте таблиц из базы данных существующие связи между этими таблицами используются для создания модели данных в Excel. Модель данных является прозрачной в Excel, но ее можно просматривать и изменять непосредственно с помощью надстройки Power Pivot. Модель данных обсуждается более подробно далее в этом учебнике.

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

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

Теперь, когда данные импортированы в Excel и автоматически создана модель данных, можно приступить к их просмотру.

Просмотр данных в сводной таблице

Просматривать импортированные данные удобнее всего с помощью сводной таблицы. В сводной таблице можно перетаскивать поля (похожие на столбцы в Excel) из таблиц (например, таблиц, импортированных из базы данных Access) в разные области, настраивая представление данных. Сводная таблица содержит четыре области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

В полях сводной таблицы разверните таблицу medals , щелкнув стрелку рядом с ней. Найдите поле Нок_каунтрирегион в развернутой таблице medals и перетащите его в область столбцы. В NOC используется National спортивных комитетов, который является организационным подразделением для страны или региона.

Затем перетащите виды спорта из таблицы Disciplines в область СТРОКИ.

Давайте отфильтруем дисциплины, чтобы отображались только пять видов спорта: стрельба из лука (Archery), прыжки в воду (Diving), фехтование (Fencing), фигурное катание (Figure Skating) и конькобежный спорт (Speed Skating). Это можно сделать в области Поля сводной таблицы или в фильтре Метки строк в самой сводной таблице.

Щелкните в любом месте сводной таблицы, чтобы убедиться, что сводная таблица Excel выбрана. В списке Поля сводной таблицы, где развернута таблица Disciplines, наведите указатель на поле Discipline, и в его правой части появится стрелка раскрывающегося списка. Щелкните эту стрелку, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

Либо щелкните в разделе сводной таблицы Метки строк стрелку раскрывающегося списка рядом с полем Метки строк, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

В разделе Поля сводной таблицы перетащите поле Medal из таблицы Medals в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически изменит поле Medal на Count of Medal.

В таблице Medals снова выберите поле Medal и перетащите его в область ФИЛЬТРЫ.

Давайте отфильтруем сводную таблицу таким образом, чтобы отображались только страны или регионы, завоевавшие более 90 медалей. Вот как это сделать.

В сводной таблице щелкните стрелку раскрывающегося списка рядом с полем Метки столбцов.

Выберите Фильтры по значению, а затем — Больше.

Введите 90 в последнем поле (справа). Нажмите кнопку ОК.

Сводная таблица будет иметь следующий вид:

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

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

Импорт данных из таблицы

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

Начнем с создания пустого листа, а затем импортируем данные из книги Excel.

Вставьте новый лист Excel и назовите его Sports.

Перейдите к папке, в которой содержатся загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.

Выберите и скопируйте данные на листе Sheet1. При выборе ячейки с данными, например, ячейки А1, можно нажать клавиши Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

На листе Sports поместите курсор в ячейку А1 и вставьте данные.

Выделив данные, нажмите клавиши CTRL + T, чтобы отформатировать данные как таблицу. Вы также можете отформатировать данные в виде таблицы на ленте, выбрав в _Гт_ формат таблицы. Так как у данных есть заголовки, в появившемся окне Создание таблицы выберите пункт таблица с заголовками, как показано ниже.

Форматирование данных в виде таблицы имеет много преимуществ. Можно назначить имя таблице, что упрощает ее определение. Кроме того, вы можете устанавливать связи между таблицами, позволяя исследовать и анализировать в сводных таблицах, Power Pivot и в Power View.

ПриСвойте таблице имя. В разделе Работа с таблицаМи _Гт_ свойства _ГТ_ макета найдите поле имя таблицы и введите Спорт. Книга будет выглядеть так, как показано на следующем экране.

Импорт данных с помощью копирования и вставки

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

Вставьте новый лист Excel и назовите его Hosts.

Выделите и скопируйте приведенную ниже таблицу вместе с заголовками.