Excel не обновляет данные из интернета
В 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
Ошибки обновления могут возникнуть при работе в книге с типами данных вручную или при включенной поставщиком данных функции автоматического обновления. Автоматическое обновление является новым и включено по умолчанию, поэтому эти ошибки могут появляться даже при том, что вы ничего не пытались обновить. Ниже приведены ситуации, которые приводят к таким ошибкам обновления, и узнайте, как их избежать.
Чтобы обойти эти ошибки, отключите автоматическое обновление. После разрешения условий, вызывающих ошибки обновления, можно обновить типы данных вручную.
Если автоматическое обновление отключено, данные могут стать неавтоматической.
Обновление вручную происходит по тем же причинам, что и автоматическое обновление, но вы можете работать с существующими данными, не сталкиваясь с ошибками обновления автоматически. Имейте в виду, что данные могут быть не обновлены.
Где могут возникнуть эти ошибки
Эти ошибки могут возникнуть в трех местах:
Пример подсказки инструмента:
Ошибки и рекомендации
Не удалось найти этот тип данных. Возможно, у вас нет доступа к ней или исходные данные могли измениться.
Убедитесь, что у вас есть необходимые разрешения на доступ к таблице.
Не удалось связать все данные. Снова Excel и попробуйте еще раз.
Выйдите Excel и снова откройте файл.
Вы не можете получить доступ к типам данных организации. Вы можете не соответствовать требованиям или иметь правильную лицензию.
Убедитесь, что у вас есть учетная запись с Office подпиской и планом Power BI Pro обслуживания.
Что-то пошло не так на нашей стороне, и нам не удалось связать все данные. Мы работаем над ее устранением.
Это может быть вызвано вопросами поставщика данных. Повторите попытку позднее.
К сожалению, на нашем сервере временно возникают проблемы. Мы работаем над ее устранением.
Повторите попытку позднее.
К сожалению, служба сейчас недоступна. Попробуйте еще раз через несколько минут.
Подождите несколько минут, а затем попробуйте еще раз.
Этот тип данных пока не поддерживается.
Мы не можем сказать, когда запросимый тип данных станет доступен. Думайте о том, чтобы в книге использовались только поддерживаемые типы данных.
Эта функция отключена администратором Power BI администратором. Обратитесь за помощью к Power BI администратору.
Администратор Power BI может включить эту функцию для вас.
Не удалось обновить типы данных.
Повторите попытку позднее.
Не удалось обновить все данные, но мы обновили все, что могли. Повторите попытку позднее.
Повторите попытку позднее.
Не удалось обновить данные, так как исходные данные или ваши разрешения могли измениться.
Убедитесь, что у вас есть необходимые разрешения на доступ к Power BI данных. Обратитесь к владельцу наборов данных (см. нижнюю часть карточки типов данных).
Мы обновили некоторые данные, но нам не удалось обновить их все, потому что мы изменили или удалили их на нашей стороне. Для данных, которые мы больше не можем обновлять, мы сохранили последние результаты.
Обновление затронутых данных не будет возобновлено, но учитывайте влияние изменений на процессы, цели и назначение книги.
При этом обновление связанных типов данных было прервано. Попробуйте обновить его еще раз.
Повторите попытку позднее.
В этой книге нельзя получить доступ к типам данных. Вы можете не соответствовать требованиям или иметь правильную лицензию.
Убедитесь, что у вас есть учетная запись с Office подпиской.
У вас нет разрешения на изменение определенных типов данных. Некоторые данные не обновляются.
Убедитесь, что у вас есть необходимые разрешения. Если у вас нет таких разрешений, рассмотрите возможность получения этих разрешений и повторная попытка.
Превышено ограничение на ежедневное количество запросов. Попробуйте еще раз через 24 часа.
Повторите попытку позднее. Это может занять не более 24 часов, прежде чем вы сможете повторить попытку.
Для обновления связанных типов данных необходимо быть в сети. Проверьте подключение и попробуйте еще раз.
Убедитесь, что вы подключены, и обратитесь к ИТ-администратору, если вы не подключены. Попробуйте еще раз, когда подключение будет готово.
Чтобы использовать типы данных, необходимо установить последнюю версию Excel.
Попросите ИТ-администратора установить последнюю версию Excel, чтобы вы могли повторить попытку.
Для использования этих типов данных необходимо войти с помощью учетной записи подписки.
Убедитесь, что вы вошли в свою учетную запись Excel и попробуйте еще раз.
Для использования этих типов данных необходимо войти с помощью учетной записи подписки. Выберите Excel в верхнем меню, а затем выберите Войти.
Убедитесь, что вы вошли в свою учетную запись Excel и попробуйте еще раз.
Когда дело доходит до работы с электронными таблицами, нельзя отрицать, что самым популярным инструментом является Microsoft Excel, которым пользуются миллионы пользователей по всему миру. В рамках его широкого спектра функций у нас будет возможность импортировать данные с веб-сайта в нашу электронную таблицу и обновлять ее позже.
Excel предлагает возможность агрегировать данные для углубленного просмотра, расчета и анализа. Возможно, ручной способ — добавить его вручную, но это очень непрактично. Поэтому мы можем импортировать данные непосредственно с веб-сайта с помощью инструмента Power Query.
Что такое Power Query в Excel
Мы говорим об инструменте, включенном в Microsoft Excel и помогающем нам упростить процесс импорт данных из разных источников в электронную таблицу в наиболее удобном формате, а затем очищать, преобразовывать и переделывать эти данные по мере необходимости. . Поскольку он принадлежит офисному пакету Office, это бесплатное дополнение, которое периодически обновляется.
Его главное преимущество и то, что делает его чрезвычайно полезным инструментом, заключается в том, что он выполняет функцию ETL, то есть извлечение, преобразование и загрузку данных. Его пользовательский интерфейс интуитивно понятен и хорошо разработан, что упрощает его изучение. Для этого нам не нужно знать или использовать какой-либо код, поэтому его использование намного проще по сравнению с другими инструментами, такими как формулы или VBA.
- Возможность подключения к различным источникам данных, таким как текстовые файлы, файлы Excel, веб-сайты, базы данных и другие.
- Преобразует полученные данные на основе предпосылок отчета.
- Это позволяет вам сохранять данные в таблице Excel или просто подключаться к данным, чтобы загрузить их позже.
Кроме того, если исходные данные со временем меняются, можно обновить полученные результаты и найти их в нашей электронной таблице простым щелчком мыши, поэтому это очень полезно, если мы работаем с данными, которые могут часто меняться.
Аспекты, которые следует учитывать
- Версия, которую мы используем Microsoft Excel: чтобы выполнить важность данных для нашей электронной таблицы, мы должны использовать функцию Power Query. Таким образом, мы можем установить соединение, преобразовать, объединить и загрузить данные. Для этого необходимо использовать совместимую версию Excel, такую как Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 и Excel 2010.
- Формат данных: поскольку мы собираемся импортировать данные с веб-сайта в Excel, для того, чтобы это было сделано удовлетворительно, они должны быть встроены в таблицу HTML. В противном случае Power Query не сможет их идентифицировать, поэтому мы не сможем их импортировать.
Как только мы убедимся, что сможем выполнить оба требования, мы можем начать процесс импорта.
Шаги для импорта данных в Excel
Первое, что мы должны сделать, это открыть Microsoft Excel и выбрать существующий или новый лист, куда мы собираемся импортировать данные. Позже на ленте щелкните вкладку «Данные». Далее переходим в раздел данных «Получить и преобразовать», который находится слева. Отсюда мы нажимаем на "Получить данные" раздел, в котором будет отображаться меню, где мы выбираем «Из других источников» и «Из Интернета».
Это откроет новое всплывающее окно под названием «Из Интернета», где мы должны вставьте URL-адрес веб-страницы который мы хотим импортировать в текстовое поле «URL-адрес». Один раз нажимаем «ОК». В некоторых случаях Excel попросит нас подтвердить данные, которые мы хотим загрузить на веб-сайт, поэтому мы должны нажать «Подключиться» для подтверждения.
Как только Excel закончит загрузку данных, появится новое окно с именем «Навигатор» появится, что покажет нам компоненты веб-страницы в левой панели. Теперь мы должны выбрать любую из пронумерованных таблиц в «Навигаторе», чтобы проверить правильность импорта данных.
Если мы хотим убедиться, что находимся на правильной странице, мы можем щелкнуть значок «Веб-просмотр» tab, чтобы получить традиционный предварительный просмотр страницы в формате HTML.
Как правило, перечисленные компоненты не представлены с очевидными именами, которые позволяют нам определить, к какому элементу или компоненту веб-страницы он относится, поэтому нам придется щелкнуть по каждому из них, чтобы получить предварительный просмотр каждого элемента в правую панель, таким образом имея возможность узнать, какая из них принадлежит нужной таблице. Если мы согласны с тем, как данные будут импортированы из Интернета, мы можем нажать «Загрузить в» для отправки данных непосредственно в Excel.
После этого появится окно с именем «Импорт данных» появится, который предложит нам выбрать, как мы хотим видеть данные в книге, либо в формате таблицы, отчета сводной таблицы, сводной диаграммы или Создать только соединение. Мы также можем указать, где мы хотим разместить данные, будь то в существующей электронной таблице или в новой. После выбора нажмите «ОК».
В результате мы увидим таблицу в нашей электронной таблице. Это связано с «Запросы и связи» панель справа. В нем перечислены все существующие запросы в этом файле. При наведении на запрос открывается информационное окно. В нем мы увидим предварительный просмотр данных, количество импортированных столбцов, дату и время обновления, способ загрузки данных и место их происхождения.
Обновить веб-данные
Как мы уже знаем, когда мы импортируем данные с веб-страницы в Excel, они автоматически обновляются. Таким же образом можно отредактировать наши предпочтения, указав, как мы хотим, чтобы они выполнялись.
Для этого на рабочем листе, подключенном к Интернету, мы нажимаем на таблицу, а на ленте мы нажимаем вкладку «Дизайн таблицы». Здесь, в разделе «Внешние данные таблицы», мы нажимаем раскрывающееся меню «Обновить». Здесь мы найдем три варианта: Обновить, Обновить все и «Свойства соединения» . Мы нажмем на последний, чтобы установить наши предпочтения.
Это откроет новое окно под названием «Свойства запроса». В котором мы нажмем на вкладку «Использование», чтобы установить наши настройки обновления.
Здесь мы обнаружим установленный флажок «Включить фоновое обновление», что гарантирует постоянное обновление содержимого. Мы также можем установить флажок «Обновлять каждые», чтобы Excel обновлял данные только в течение определенного периода. Наконец, опция «Обновлять при открытии файла» позволяет обновлять данные только при запуске приложения.
После завершения нажмите «Принять», чтобы сохранить наши настройки обновления.
Когда дело доходит до работы с электронными таблицами, Microsoft Excel - это мировой справочник. Когда мы работаем с несколькими книгами в одной организации, нас может заинтересовать импорт данных из нескольких внешних источников данных, таких как другая книга Excel, база данных, файлы CSV и т. Д. Хотя связывание с другими книгами является очень распространенной задачей, мы можем иногда возникают проблемы, из-за которых Excel не может обновить некоторые ссылки.
Какие ссылки и причины вызывают проблему
В Excel есть инструмент под названием Ссылки или внешние ссылки это позволит нам скопировать все данные с одного листа и вставить их в другой, как в одной книге, так и в разных книгах. Эта ссылка означает, что при изменении данных исходного листа это изменение также влияет и выполняется на целевом листе. Важно уточнить, что эта функция отличается от функции копирования и вставки или гиперссылок, поскольку таким образом не выполняется обновленный процесс связывания. Таким образом, каждый раз, когда исходное содержимое ссылки обновляется, оно также будет выполняться в книге, в которую мы добавили ссылку.
Далее мы увидим различные действия, которые мы можем предпринять, чтобы попытаться решить проблемы при обновлении ссылок в Microsoft Excel.
Найдите внешние ссылки, чтобы решить проблему
В нашей книге все ячейки, содержащие ссылки или внешние ссылки, имеют имя книги, на которую они ссылаются, поэтому ячейка будет содержать расширение книги. Это то, что мы можем использовать для поиска ячеек и их восстановления, чтобы решить проблему с обновлением.
Контроль обновления ссылок
Чтобы обновить ссылки, на вкладке «Данные» мы должны нажать кнопку «Обновить все».
Разблокировать защищенную таблицу
Иногда мы можем обнаружить, что проблема с обновлением ссылок в Excel может возникнуть, когда мы защищаем электронную таблицу. Если мы установили пароль, который защищает наш лист, мы можем попытаться удалить его, чтобы увидеть, решит ли он проблему.
Для этого достаточно будет щелкнуть правой кнопкой на вкладке «Лист» в левой нижней части. Это приведет к появлению контекстного меню, в котором мы должны выбрать опцию «Снять защиту листа «. Если наша электронная таблица защищена паролем, мы должны ввести правильный пароль и нажать «Применить». Если он не заблокирован каким-либо паролем, нажатие на «Снять защиту с листа» автоматически разблокирует защищенный лист.
В качестве альтернативы мы также можем разблокировать защищенную электронную таблицу на вкладке «Обзор», которую мы находим в верхней правой части Excel. Отсюда в разделе «Защитить» нажмите кнопку «Снять защиту с листа».
Восстановить битые ссылки
Перед тем, как сделать этот вариант, удобно, чтобы сохраняем резервную копию нашей книги Excel. Позже мы открываем книгу Excel, которая показывает нам ошибку со ссылками, и щелкаем вкладку «Данные». Здесь мы увидим, что команда «Редактировать ссылки» недоступна, если наша книга не содержит ссылок.
Отключить DDE (динамический обмен данными)
Протокол динамического обмена данными DDE был разработан Office, чтобы иметь возможность обмениваться данными между различными приложениями, которые его составляют, например, между PowerPoint и Excel, Word и Excel и т. д. Когда мы пытаемся открыть документ с помощью этого протокола, появится окно с предупреждением, указывающее, мы хотим обновить данные связанных файлов.
Если мы хотим отключить его, мы можем щелкнуть по вкладке «Файл» и «Параметры». В новом окне в разделе «Дополнительно» мы смотрим в раздел «Общие» и устанавливаем флажок «Пропустить другие приложения, использующие динамический обмен данными (DDE)» в Excel.
Хотите использовать регулярно обновляющиеся данные из интернета? Мы покажем Вам, как легко и быстро настроить импорт данных из интернета в Excel 2010, чтобы Ваша таблица была постоянно в актуальном состоянии.
Как создать таблицу, связанную с интернетом?
Чтобы импортировать данные в таблицу Excel, выберите команду From Web (Из интернета) в разделе Get External Data (Получение внешних данных) на вкладке Data (Данные).
Перед каждой из web-таблиц имеется маленькая стрелочка, которая указывает, что эта таблица может быть импортирована в Excel. Кликните по ней, чтобы выбрать данные для загрузки, а затем нажмите Import (Импорт).
Выберите ячейку, в которой будут размещены данные из интернета, и нажмите ОК.
Через некоторое время информация с выбранной web-страницы появится в таблице Excel. Возможно, в таблицу попадут некоторые лишние данные – их можно спокойно удалить.
Импортированные данные Вы можете использовать точно так же, как и любую другую информацию в Excel. Их можно использовать для построения графиков, спарклайнов, формул. Спарклайны – это новый инструмент для работы с данными, появившийся в Excel 2010. Более подробно о спарклайнах Вы можете узнать из урока Как использовать спарклайны в Excel 2010. Использование динамических данных в Excel даёт одно замечательное преимущество – они будут автоматически обновляться при изменении информации на web-странице.
Обновление данных
Если Вы хотите быть уверенными, что информация в таблице обновлена и максимально актуальна, нажмите команду Refresh All (Обновить все) на вкладке Data (Данные). Это действие отправит запрос web-странице и, если есть более свежая версия данных, запустит процесс обновления в таблице.
Если же нужно, чтобы информация в таблице автоматически обновлялась с какой-то заданной периодичностью, выберите ячейку таблицы, содержащую динамические данные, и нажмите команду Properties (Свойства) в разделе Connections (Подключения) на вкладке Data (Данные).
В открывшемся диалоговом окне поставьте галочку Refresh every (Обновлять каждые) и укажите частоту обновления в минутах. По умолчанию Excel автоматически обновляет данные каждые 60 минут, но Вы можете установить любой необходимый период. Или, например, указать Excel обновлять информацию каждый раз при открытии файла.
Если Вы используете статические данные из интернета в Excel, например, удельные веса минералов или площади территорий государств, тогда обновление в фоновом режиме можно отключить, чтобы Excel не соединялся с интернетом без необходимости.
Заключение
Интернет предоставляет бездонную сокровищницу информации, которую можно применять с пользой для Вашего дела. С помощью инструментов, позволяющих импортировать информацию в Excel, Вы легко можете использовать онлайн-данные в своей работе. Спортивные таблицы результатов, температуры плавления металлов или обменные курсы валют со всех точках земного шара – теперь нет необходимости вводить их вручную и постоянно проверять актуальность информации на различных интернет-ресурсах.
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение:
А как автоматизировать процесс передачи данных из интернета в excel?
Все до безобразия просто, мелкософт, иногда радует своим дружелюбием к пользователям не программистам.
Собственно, приступим к делу:
1. Этот пункт могут не читать те люди, кто уже знает, как создаются файлы excel, как, собственно, и другие продукты Майкрософт офис. Жмем правую кнопку мыши (ПКМ) ? Создать ? Лист Microsoft excel
2. Открываем полученный файл, выбираем вкладку «Данные» ? из Интернета в excel
4. После добавления данных получится приблизительно следующая картина.
Крайне неприглядно, а посему этот лист будем использовать, как источник данных интернета в excel. Теперь же создадим лист уже для выведением чистовых данных
5. Теперь смотрите, что получилось
6. Так как данные полученные из интернета в excel передаются не в числовом формате, для того, чтобы их обработать примените следующее программное средство excel (ПСТР()), т.е. для нашего случая, с Яндекс, получится следующая конструкция в ячейке =ПСТР(B1;1;4), В1 данные из ячейки выделенных данных, 1 число с которого начинается исключение всего ненужного сначала строкового набора, а 4 — число чисел от начала исключения(т.е. число знаков которое вошло в промежуток от 1 до 4), т.е. если вы имели скажем текстовую строку 36,4536,4461, то после применения ПСТР(B1;1;4) останется 36,4, при ПСТР(B1;2;6) получите 6,4536 и так далее. После этих манипуляций числа становятся пригодными к вычислению
Вот и все. Майкрософт предоставил гибкую систему импорта данных из интернета в excel. Так что пользуйтесь, надеюсь будет полезным.
В 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
Excel. Получение информации с веб-страницы
Если требуется регулярно обращаться к обновляемым данным с веб-страницы, создайте веб-запрос. На рис. 1 представлен сайт показывающий курс доллара.
Рис. 1. Использование диалогового окна Создание веб-запроса для указания данных, которые следует импортировать; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Термин «веб-запрос» немного неточен, так как с его помощью можно получать данные не только из Сети. Можно также выполнять веб-запрос к HTML-файлу, расположенному на локальном компьютере, или к файлу, хранящемуся на сетевом сервере либо на веб-сервере в Интернете. Чтобы получить информацию с веб-сервера, необходимо подключиться к Интернету. Когда данные получены, соединение с Интернетом для работы с ними больше не нужно (конечно, если вы не собираетесь обновлять эти данные).
Создать веб-запрос, обеспечивающий получение информации, которую позже можно будет обновить одним щелчком мыши, можно следующим образом.
Результат импорта (рис. 2) не совсем то, чего мы хотели. Нас интересовала только таблица валют, а веб-запрос вернул страницу целиком. По умолчанию импортированные данные являются веб-запросом. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке в импортированном диапазоне и в контекстном меню выполните команду Обновить.
Рис. 2. Информация, полученная с помощью веб-запроса
Если вы не собираетесь создавать обновляемый запрос, укажите это в шаге 5 приведенного выше порядка действий. В диалоговом окне Импорт данных нажмите кнопку Свойства и снимите флажок Сохранить определение запроса.
Встроенная в Excel возможность работы с веб-запросами позволяет находить в HTML-документе таблицы (обозначаемые HTML-тегом
Читайте также:
- 1с группировка данных не разрешена без указания основной таблицы
- Как вставить подпись в пдф из автокада
- Пропасть вид сверху для фотошопа
- Не удалось запустить драйвер прозрачного проксирования отключите мониторинг портов 80 и 443
- В каких случаях с интернет браузер можно работать и без подключения к интернет