Как получить данные из закрытого файла Excel
Добрый день, уважаемые подписчики и читатели блога!
Сегодня мы поговорим о получении данных из закрытых файлов Excel. В одном из предыдущих уроков рассматривалась та же задача, но с применением макроса. Теперь посмотрим как обойтись без него.
Способ №1
Воспользуемся строкой формул, это самый простой и понятный способ действий. Представим картину: есть папка с несколькими книгами Excel; в каждой книге несколько листов, они заполнены данными; эти данные нужно собрать в один общий файл.
Итак, книга "Итог" должна собрать из файла "Магазины" наименование магазина за определённый месяц и из файла "Продажи" сумму продаж за каждый месяц по магазинам.
Взглянем в строчку формул книги "Итого" на листе "январь".
='C:\папка\[Продажи.xlsx]январь'!$B$3
Весь фокус заключается в чём - необходимо путь до папки с файлом заключить в кавычки, имя файла заключить в квадратные скобки, далее через знак восклицания указать адрес ячейки откуда хотим забрать данные.
='C:\папка\[Магазины.xlsx]январь'!$A$3
Точно так же и с именем магазина, который подгружается из файла "Магазины".
Итог: необходимо правильно прописать имена файлов и их пути.
Способ №2
Воспользуемся Power Query. Как им пользоваться мы рассматривали ранее.
Напомню - если у вас Excel версии 2013 придётся установить этот модуль дополнительно. Если Excel 2016 он уже будет в комплекте.
Переходим на вкладку "Power Query", нажимаем кнопку "Из файла" и выбираем вариант "Из файла Excel".
В появившемся окне выбираем файл из которого нужно выгрузить информацию.
Далее выберем информацию с каких листов нужно загрузить. Щёлкаем галку "Несколько элементов" и отмечаем листы.
Нажимаем кнопку "Загрузить в" и выбираем "Таблица", будет выгружена информация на новые листы. Если хотим всё на один лист - придётся по очереди выгружать каждый лист.
Каждый лист загрузится в таблицу "Итог" и можно будет оперировать данными как угодно.
Наведя курсор в часть запроса, можно будет увидеть подробную статистику по элементу Power Query.
Вот такие два способа получения данных из закрытых файлов Excel. Всем удачи!