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

Недавно, одним из читателей сайта Exceltip.ru, Сергеем, был задан вопрос, который заставил задуматься. Вопрос звучал следующим образом:

Ответа на тот момент я не знал, но вопрос настолько заинтересовал меня, что мне самому захотелось разобраться. При попытке импорта данных стандартными инструментами, Excel не воспринимает URL адрес, указывающий путь к файлу в интернет. Решение было найдено, но у данного решения есть серьезное ограничение, необходимо, чтобы у пользователя, запрашиваемого данные, были права доступа к файловой системе сайта. Данный принцип заключается в следующем, зная логин и пароль доступа к директории хостинга, на котором находится сайт, подключаем сетевой диск, содержащий данные сайта (и excel книгу, данные из которой требуется импортировать, в том числе). Затем получаем данные этой книги, как будто Excel файл находится на нашем компьютере.

В сегодняшнем уроке мы будем импортировать данные с Excel файла, которые я предварительно разместил на своем хостинге, и который можно найти по адресу http://exceltip.ru/test/82-test-import.xlsx. Файл содержит небольшую выдуманную таблицу с двумя колонками.

Итак, обо все по порядку…

Открываем Проводник, переходим в директорию Компьютер. Windows отобразит все подключенные диски на данный момент: как физические, так и сетевые. В панели управления Проводника находим кнопку Подключить сетевой диск и жмем на нее.

Перед вами появится диалоговое окно Подключить сетевой диск, находим вкладку Подключение к сайту, на котором можно хранить документы и изображения и переходим по ней. Windows запустит мастер добавления сетевого размещения. На первом шаге будет дано описание процесса, щелкаем Далее. В следующем окне на вопрос Укажите, где вы хотите создать сетевое размещение,выделите Выберите другое сетевое размещение и щелкните Далее. На третьем шаге нам будет предложено указать адрес FTP-сайта. Так как я пользуюсь услугами хостинга Timeweb (не реклама!)), мой FTP выглядит следующим образом: ftp://fabula.timeweb.ru. Щелкаем Далее.

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

После щелчка Далее, попадаем на завершающее окно мастера Подключения сетевого размещения, где можно увидеть итоговую информацию. Щелкаем Готово, Windows попросит нас ввести пароль для входа на FTP-сервер, вводим его и жмем Вход.

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

Следующий шаг — импорт данных. Для этого я воспользовался функционалом PowerPivot для Excel, о котором вы можете узнать в моих предыдущих статьях. Открываем книгу, куда мы планируем импортировать данные, переходим на вкладку PowerPivot и щелкаем по кнопке Окно PowerPivot.

В появившемся диалоговом окне PowerPivot для Excel переходим по вкладке В начало в группуПолучение внешних данных. Выбираем Из других источников –> Файл Excel -> ОК.

Появится Мастер импорта таблиц, где необходимо будет указать местоположение импортируемого файла. Для этого щелкаем кнопку Обзор и выбираем необходимый файл из сетевой директории, которую мы ранее добавили. Жмем Далее.

Здесь PowerPivot отобразит все таблицы (листы), которые он нашел в нашем файле. Выбираем необходимый лист, щелкаем Далее. Мастер импорта укажет статус выгрузки и количество строк, которое удалось передать. Жмем Закрыть.

В окне PowerPivot для Excel появится таблица с импортированными данными, которые уже можно использовать в своих целях.

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