Содержание:
Постановка задачи
Давайте рассмотрим красивое решение для одной из самых стандартных ситуаций, с которой рано или поздно сталкивается большинство пользователей Excel: вам нужно быстро и автоматически собрать данные из большого количества файлов в одну итоговую таблицу.
Предположим, у нас есть следующая папка, в которой находится несколько файлов с данными по городам-филиалам:
Количество файлов не имеет значения и может измениться в будущем. Каждый файл имеет лист с именем Продажагде находится таблица данных:
Количество строк (порядков) в таблицах, конечно, разное, но набор столбцов везде стандартный.
Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении или удалении городских файлов или строк в таблицах. По итоговой сводной таблице потом можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное уметь собирать.
Подбираем оружие
Для решения нам понадобится последняя версия Excel 2016 (необходимый функционал в нее уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой. Power Query от Microsoft (скачайте здесь). Power Query — это сверхгибкий и сверхмощный инструмент для загрузки данных в Excel из внешнего мира, их последующего разделения и обработки. Power Query поддерживает практически все существующие источники данных — от текстовых файлов до SQL и даже Facebook 🙂
Если у вас нет Excel 2013 или 2016, то дальше вы можете не читать (шутка). В старых версиях Excel такую задачу можно решить только программированием макроса на Visual Basic (что очень сложно для новичков) или монотонным ручным копированием (что занимает много времени и порождает ошибки).
Шаг 1. Импортируйте один файл в качестве образца.
Для начала давайте в качестве примера импортируем данные из одной книги, чтобы Excel «подхватил идею». Для этого создайте новую пустую книгу и…
- если у вас Excel 2016, то откройте вкладку Данные , а затем Создать запрос – Из файла – Из книги (Данные — Новый запрос — Из файла — Из Excel)
- если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на нем Из файла – Из книги (Из файла — Из Excel)
Затем в открывшемся окне переходим в нашу папку с отчетами и выбираем любой из городских файлов (неважно какой, ведь они все типовые). Через пару секунд должно появиться окно Навигатора, где нужно выбрать нужный нам лист (Продажи) в левой части, а в правой части отобразится его содержимое:
Если нажать на кнопку в правом нижнем углу этого окна Скачать (Нагрузка), то таблица будет сразу импортирована на лист в исходном виде. Для одного файла это хорошо, но нам нужно загрузить много таких файлов, поэтому пойдем немного иначе и нажмем кнопку Коррекция (Редактировать). После этого редактор запросов Power Query должен отобразиться в отдельном окне с нашими данными из книги:
Это очень мощный инструмент, позволяющий «довести» таблицу до нужного нам вида. Даже поверхностное описание всех его функций заняло бы около ста страниц, но, если очень кратко, с помощью этого окна можно:
- отфильтровать ненужные данные, пустые строки, строки с ошибками
- сортировать данные по одному или нескольким столбцам
- избавиться от повторений
- разделить липкий текст по столбцам (по разделителям, количеству символов и т. д.)
- привести текст в порядок (удалить лишние пробелы, исправить регистр и т. д.)
- конвертировать типы данных всеми возможными способами (превращать числа, подобные тексту, в обычные числа и наоборот)
- транспонировать (вращать) таблицы и расширять двумерные кросс-таблицы в плоские
- добавляйте в таблицу дополнительные столбцы и используйте в них формулы и функции с помощью языка M, встроенного в Power Query.
- ...
Например, добавим в нашу таблицу столбец с текстовым названием месяца, чтобы в дальнейшем было проще строить отчеты сводной таблицы. Для этого щелкните правой кнопкой мыши по заголовку столбца датыи выберите команду Повторяющийся столбец (Дубликат столбца), а затем щелкните правой кнопкой мыши заголовок появившегося дублирующего столбца и выберите «Команды». Преобразование – Месяц – Название месяца:
Должен быть сформирован новый столбец с текстовыми названиями месяцев для каждой строки. Дважды щелкнув заголовок столбца, вы можете переименовать его из Копировать дату к более удобному Месяц, например.
Если в некоторых столбцах программа не совсем правильно распознала тип данных, то вы можете ей помочь, нажав на значок формата в левой части каждого столбца:
Исключить строки с ошибками или пустые строки, а также ненужных менеджеров или клиентов можно с помощью простого фильтра:
При этом все выполненные преобразования фиксируются в правой панели, где их всегда можно откатить (перекрестить) или изменить их параметры (шестеренка):
Легкий и элегантный, не правда ли?
Шаг 2. Преобразуем наш запрос в функцию
Чтобы впоследствии повторить все преобразования данных, произведенные для каждой импортированной книги, нам необходимо преобразовать созданный нами запрос в функцию, которая затем будет применена, в свою очередь, ко всем нашим файлам. Сделать это на самом деле очень просто.
В редакторе запросов перейдите на вкладку «Вид» и нажмите кнопку Расширенный редактор (Вид — Расширенный редактор). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что путь к файлу, который мы импортировали для примера, жестко запрограммирован в коде:
Теперь внесем пару корректировок:
Их смысл прост: первая строка (путь к файлу)=> превращает нашу процедуру в функцию с аргументом Путь файла, а ниже мы меняем фиксированный путь к значению этой переменной.
Все. Нажмите на Завершить и должен увидеть это:
Не бойтесь, что данные пропали — на самом деле все ок, все должно выглядеть так 🙂 Мы успешно создали свою пользовательскую функцию, в которой запоминается весь алгоритм импорта и обработки данных без привязки к конкретному файлу. . Осталось дать ему более понятное имя (например получить данные) на панели справа в поле Имя и ты сможешь пожинать Главная — Закрыть и скачать (Главная страница — Закрыть и загрузить). Обратите внимание, что путь к файлу, который мы импортировали для примера, жестко запрограммирован в коде. Вы вернетесь в главное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:
Шаг 3. Сбор всех файлов
Все самое сложное позади, осталось приятное и легкое. Перейти на вкладку Данные – Создать запрос – Из файла – Из папки (Данные — Новый запрос — Из файла — Из папки) или, если у вас Excel 2010-2013, аналогично вкладке Power Query. В появившемся окне указываем папку, в которой находятся все файлы нашего исходного города и нажимаем OK. Следующим шагом должно открыться окно, в котором будут перечислены все файлы Excel, найденные в этой папке (и ее подпапках), и сведения о каждом из них:
Нажмите Изменить (Редактировать) и снова попадаем в знакомое окно редактора запросов.
Теперь нам нужно добавить в нашу таблицу еще один столбец с созданной нами функцией, которая будет «подтягивать» данные из каждого файла. Для этого перейдите на вкладку Добавить столбец – Пользовательский столбец (Добавить столбец — Добавить пользовательский столбец) и в появившемся окне вводим нашу функцию получить данные, указав для него в качестве аргумента полный путь к каждому файлу:
После нажатия на OK созданный столбец должен быть добавлен в нашу таблицу справа.
Теперь удалим все ненужные столбцы (как в Excel, правой кнопкой мыши – Удалить), оставив только добавленный столбец и столбец с именем файла, потому что это имя (точнее город) будет полезно иметь в итоговых данных по каждой строке.
А теперь «вау-момент» — кликаем по иконке с собственными стрелочками в правом верхнем углу добавленного столбца с нашей функцией:
… снимите флажок Использовать исходное имя столбца в качестве префикса (Используйте исходное имя столбца в качестве префикса)и нажмите OK. А наша функция будет загружать и обрабатывать данные из каждого файла, следуя записанному алгоритму и собирая все в общую таблицу:
Для полной красоты можно также убрать расширения .xlsx из первого столбца с именами файлов – стандартной заменой на «ничего» (правый клик по заголовку столбца – Замена) и переименуйте этот столбец в Город. А также поправьте формат данных в столбце с датой.
Все! Нажмите на Главная – Закрыть и загрузить (Главная страница — Закрыть и загрузить). Все данные, собранные запросом по всем городам, будут выгружены на текущий лист Excel в формате «умной таблицы»:
Созданное соединение и нашу функцию сборки никак не нужно сохранять отдельно — они сохраняются вместе с текущим файлом обычным способом.
В дальнейшем при любых изменениях в папке (добавление или удаление городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши непосредственно по таблице или по запросу в правой панели и выбрать пункт команда Обновить и сохранить (Обновить) – Power Query заново «перестроит» все данные за несколько секунд.
PS
Поправка. После обновлений за январь 2017 года Power Query научился собирать книги Excel самостоятельно, т.е. больше не нужно создавать отдельную функцию — это происходит автоматически. Таким образом, второй шаг из этой статьи больше не нужен и весь процесс становится заметно проще:
- Выберите Создать запрос – Из файла – Из папки – Выбрать папку – ОК.
- После появления списка файлов нажмите Изменить
- В окне «Редактор запросов» разверните столбец «Двоичный» с двойной стрелкой и выберите имя листа, которое будет взято из каждого файла.
И это все! Песня!
- Перепроектирование кросс-таблицы в плоскую, подходящую для построения сводных таблиц.
- Создание анимированной пузырьковой диаграммы в Power View
- Макрос для объединения листов из разных файлов Excel в один