Как вставить таблицу из Интернета в Excel

Обновлено: 05.07.2024

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

В наших примерах мы будем использовать инструменты «Получить и преобразовать данные», которые находятся на ленте «Данные» в Excel.

Раньше эти инструменты назывались надстройками Power Query. Начиная с Excel 2016 эти инструменты стали стандартной функцией, доступной пользователям этой версии и более поздних версий. Мы по-прежнему называем эти инструменты «Power Query», поскольку к ним привыкло большинство пользователей… к тому же это звучит круто.

Power Query – чрезвычайно полезная функция, которая позволяет нам:

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

Самое приятное то, что если исходные данные изменяются, вы можете обновить конечные результаты одним щелчком мыши; то, что идеально подходит для данных, которые часто меняются.

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

Если вы умеете нажимать кнопки, вы можете создавать автоматизированные решения Power Query.

Первый шаг — подключение к источнику данных. В этом примере мы подключимся к Управлению энергетической информации США.

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

В «старые времена» мы переносили информацию с веб-сайта в Excel, выделяя таблицу веб-страницы и копируя/вставляя данные в Excel.

Если вы когда-либо делали это, вы знаете, что это может быть случайным предложением. Это правило 50/50/90: если у вас 50/50 шансов на победу, вы проиграете в 90% случаев.

Даже если вы успешно перенесете информацию в Excel, она не будет связана с веб-страницей. При изменении веб-страницы вам потребуется повторно скопировать/вставить (и, возможно, исправить) обновленную информацию.

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

В диалоговом окне "Из Интернета" вставьте URL-адрес в поле URL и нажмите "ОК".

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

Если вы хотите убедиться, что находитесь на правильной веб-странице, нажмите на вкладку с надписью Web View, чтобы получить предварительный просмотр страницы в традиционном формате HTML.

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

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

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

В диалоговом окне "Импорт данных" выберите "Существующий рабочий лист" и укажите ячейку на желаемом целевом рабочем листе (например, ячейку A1 на "Листе 1").

Результатом является таблица, связанная с запросом. На панели «Запросы и подключения» (справа) перечислены все существующие запросы в этом файле.

Если навести указатель мыши на запрос, появится информационное окно со следующей информацией:

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

< /p>

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

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

Нам нужно внести некоторые коррективы в данные.

Дважды щелкните (или щелкните правой кнопкой мыши и выберите "Изменить") запрос в списке, чтобы активировать редактор Power Query.

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

В редакторе Power Query выберите столбец «Продукт» и нажмите «Преобразовать» (вкладка) -> «Любой столбец (группа)» -> «Заполнить» -> «Вниз».

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

Мы заменим все «ложно пустые» ячейки нулевыми значениями. Это должно позволить операции Fill Down работать должным образом.

Выберите шаг «Измененный тип» на панели «Параметры запроса» (справа).

< /p>

Выберите столбец «Продукт» и нажмите «Преобразовать» (вкладка) -> «Любой столбец» (группа) -> «Заменить значения».

Сообщите Power Query, что вы хотите вставить этот новый шаг в существующий запрос, нажав "Вставить".

В диалоговом окне «Заменить значения» оставьте поле «Значение для поиска» пустым и введите «null» (без кавычек) в поле «Заменить на». Нажмите OK, когда закончите.

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

< /p>

Нажмите верхнюю часть кнопки "Закрыть и загрузить" в крайнем левом углу главной страницы.

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

Параметры запроса

Есть некоторые управляемые параметры, доступные при выборе «Данные» (вкладка) -> «Запросы и подключения» (группа) -> «Обновить все» -> «Свойства подключения»…

Некоторые из наиболее популярных вариантов включают:

Обновлять данные каждые N минут
Обновлять данные при открытии файла
Выбор участия во время операции «Обновить все».

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

Это то, что нужно печатать каждый день, где каждый последующий день смотрит на следующие 10 дней.

Начнем с поиска веб-сайта, который может предоставить 10-дневный прогноз для Сиэтла.

Теперь, когда у нас есть веб-ссылка на 10-дневный прогноз для Нью-Йорка, мы скопируем и вставим ее в веб-запрос в Excel (Данные (вкладка) -> Получить и преобразовать (группа) — > Из Интернета).
Выберите таблицу в левой части окна навигатора.

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

В окне "Навигатор" выберите "Преобразовать данные", чтобы загрузить прогноз в Power Query.
Чтобы начать редактирование данных, щелкните правой кнопкой мыши заголовок столбца "День" и выберите "Удалить".

Выберите последние 3 столбца ("Ветер", "Влажность" и "Столбец 7") и удалите их.

Переименуйте оставшиеся 3 столбца.
- Описание -> День
- Высокий/низкий -> Описание
- Осадки -> Высокий/Низкий

Переименуйте запрос в «SeattleWeather».
На вкладке «Главная» выберите нижнюю часть кнопки «Закрыть и загрузить», нажмите «Закрыть и загрузить в…» и выберите «Существующий рабочий лист» в диалоговом окне «Импорт данных». Нажмите OK, когда закончите.

Теперь у нас есть прогноз погоды на следующие 10 дней.

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

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

ПРИМЕЧАНИЕ. Этот творческий трюк с Excel принесли нам наши хорошие друзья Фредерик Ле Гуэн и Оз дю Солей. Ссылки на их блог и видео с описанием различных вариантов использования этого трюка можно найти в конце этого поста.

Первый шаг – добавить смайлик с зонтиком. Мы получим доступ к встроенной библиотеке эмодзи Windows.

На клавиатуре нажмите клавишу Windows и точку, чтобы отобразить библиотеку эмодзи.

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

Выделите эмодзи зонтика в строке формул и нажмите "Копировать" (CTRL-C), а затем – Enter.
Дважды щелкните запрос «SeattleWeather», чтобы запустить редактор Power Query.

Следующий шаг – создание нового столбца, в котором эмодзи-зонтик будет добавляться к любой строке, содержащей слова "дождь" или "ливень" в столбце "Описание".

Выберите Добавить столбец (вкладка) -> Общие (группа) -> Условный столбец.

Название нового столбца — «Быть экипированным», а логика — «если столбец с названием «Описание» содержит слово «дождь», отобразить смайлик с зонтиком». (Вставьтеэмодзи зонтика в выходные данные)
Нажмите кнопку "Добавить пункт", чтобы создать второе условие.
Логика второго описания такова: «иначе, если столбец с названием «Описание» содержит слово «душ», тогда отображается смайлик с зонтиком». (Вставьтеэмодзи зонтика в выходные данные)

Нажмите "ОК", чтобы добавить новый условный столбец.

Перетащите заголовок "Быть оборудованным", чтобы новый столбец оказался между столбцами "Описание" и "Высокий/Низкий".

Закройте и загрузите обновленный запрос обратно в Excel.

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

Дэвид Сарокин — известный интернет-специалист, чьи публикации посвящены широкому спектру бизнес-тем, от лучших способов использования информационных технологий до шагов по регистрации вашего бизнеса. Он является автором книги «Корпорация, ее история и будущее» (Cambridge Scholars, 2020 г.) о роли крупного бизнеса в современном мире и «Упущенная информация» (MIT Press, 2016 г.), в которой подробно описывается, как наши социальные системы, такие как здравоохранение, финансы и правительство может быть улучшено с помощью более качественной информации.

В этой статье я покажу вам, как импортировать HTML-таблицу в Excel. Это будет работать для любых данных из локального HTML-файла или онлайн-URL, которые хранятся в таблице. Я импортирую HTML-таблицу таким образом, чтобы данные можно было обновлять в случае их изменения, что особенно ценно для онлайн-данных. Это будет сделано с помощью запроса Get & Transform. Таким образом, этот метод совместим только с версией Microsoft Excel для Windows.

Шаг 1. Откройте файл или URL-адрес в редакторе запросов

Таблица для импорта в Excel

Чтобы импортировать данные из Интернета, на вкладке «Данные» нажмите «Получить данные» > «Из других источников», «Из Интернета». Если у вас есть локальный файл, вместо этого выберите «Получить данные» > «Из файла» > «Из XML».

Найдите нужную таблицу в Навигаторе

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

Шаг 2. Очистка в редакторе запросов

Готовая таблица в редакторе запросов

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

Когда таблица будет готова для Excel, нажмите "Закрыть и загрузить".

Таблица импортируется! Если вы использовали URL-адрес, вы можете включить автоматическое обновление данных. Щелкните ячейку в таблице, перейдите на вкладку Запрос, щелкните Свойства. В диалоговом окне «Свойства» вы найдете параметры для фонового обновления и автоматического обновления.

Спасибо, что прочитали. Если у вас есть какие-либо вопросы, оставьте комментарий ниже.

3 мысли о «Импорт таблицы HTML в Excel»

Уважаемые коллеги, я столкнулся со следующей проблемой:
когда я импортирую около 10 html-документов, хранящихся локально на моем компьютере, в таблице результатов меньше 100 строк.
Но для тех документов html, которые содержат таблицу только с одной строкой, ячейки пусты.
Как я могу решить эту проблему?

Кроме того, когда я импортирую только этот конкретный документ, все в порядке, данные видны.

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

Как импортировать HTML-таблицу в Google Таблицы

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