Закрыть ... [X]


  • Постановка задачи

    Давайте разберем красивое решение для одной из весьма стандартных ситуаций, с которой рано или поздно сталкивается большинство пользователей 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, то откройте вкладку Данные и выберите Создать запрос - Из файла - Из книги (Data - New Query- From file - From Excel)
    • если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла - Из книги (From file - From Excel)
    Затем в открывшемся окне переходим в нашу папку с отчетами и выбираем любой из файлов-городов (не играет роли какой именно, т.к. они все типовые). Через пару секунд должно появиться окно Навигатор, где нужно в левой части выбрать требуемый нам лист (Продажи), а в правой отобразится его содержимое:

    Навигатор Power Query

    Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load), то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла - это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit). После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:

    Редактор запросов Power Query

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

    • отфильтровывать ненужные данные, пустые строки, строки с ошибками
    • сортировать данные по одному или нескольким столбцам
    • избавляться от повторов
    • делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
    • приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
    • всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
    • транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
    • добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
    • ...

    Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column), а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование - Месяц - Название месяца:

    Добавление столбца с месяцем

    Должен образоваться новый столбец с текстовыми названиями месяца для каждой строки. Дважды щелкнув по заголовку столбца, его можно переименовать из Копия Дата в более удобное Месяц, например.

    Новый столбец
    Если в каких-то столбцах программа не совсем корректно распознала тип данных, то ей можно помочь, щелкнув по значку формата в левой части каждого столбца:

    Преобразование форматов данных в столбцах

    Исключить строки с ошибками или пустые строки, а также ненужных менеджеров или заказчиков можно с помощью простого фильтра:

    Фильтрация данных в Power Query

    Причем все выполненные преобразования фиксируются в правой панели, где их всегда можно откатить (крестик) или изменить их параметры (шестеренка):

    Шаги преобразований

    Легко и изящно, не правда ли?

    Шаг 2. Преобразуем наш запрос в функцию

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

    В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View - Advanced Editor). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:

    Исходный код

    Теперь аккуратно вносим пару правок:

    Измененный код

    Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath, а ниже мы меняем фиксированный путь на значение этой переменной. 

    Все. Жмем на Готово и должны увидеть вот это:

    Созданная функция

    Не пугайтесь, что пропали данные - на самом деле все ОК, все так и должно выглядеть :) Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData) на панели справа в поле Имя и можно жать Главная - Закрыть и загрузить (Home - Close and Load). Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:

    Подключение к функции

    Шаг 3. Собираем все файлы

    Все самое сложное - позади, осталась приятная и легкая часть. Идем на вкладку Данные - Создать запрос - Из файла - Из папки (Data - New Query - From file - From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:

    Импорт всех файлов Excel из заданной папки

    Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.

    Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая "вытянет" данные из каждого файла. Для этого идем на вкладку Добавить столбец - Пользовательский столбец (Add Column - Add Custom Column) и в появившемся окне вводим нашу функцию getData, указав для ее в качестве аргумента полный путь к каждому файлу:

    Дополнительный столбец

    После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.

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

    А теперь "вау-момент" - щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:

    Подгружаем данные в пользовательский столбец

    ... снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix)и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:

    Собранные данные

    Для полной красоты можно еще убрать расширения.xlsx из первого столбца с именами файлов - стандартной заменой на "ничего" (правой кнопкой мыши по заголовку столбца - Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.

    Все! Жмем на Главной - Закрыть и загрузить (Home - Close & Load). Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате "умной таблицы":

    Собранные данные

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

    В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) - Power Query "пересоберет" все данные заново за несколько секунд.

    P.S.

    Поправка. После январских обновлений 2017 года Power Query научился собирать Excel'евские книги сам, т.е. не нужно больше делать отдельную функцию - это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:

    1. Выбрать Создать запрос - Из файла - Из папки - Выбрать папку - ОК
    2. После появления списка файлов нажать Изменить
    3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла

    И все! Песня!

    Ссылки по теме



    Источник: http://www.planetaexcel.ru/techniques/12/2152/


    Поделись с друзьями



    Рекомендуем посмотреть ещё:



    Как собрать данные с нескольких листов или книг? Excel для всех И как следует пополнить своими руками

    Связать файлы эксель в один Связать файлы эксель в один Связать файлы эксель в один Связать файлы эксель в один Связать файлы эксель в один Связать файлы эксель в один

    ШОКИРУЮЩИЕ НОВОСТИ