Консолидация любого количества файлов за 10 минут

Хитрости Excel

В прошлом выпуске мы упоминали о возможности импорта с помощью Power Query (далее — PQ). Этот редактор помогает не только импортировать данные, но и преобразовывать их еще до импорта в книгу Excel

ВЫПУСК №1 (ЯНВАРЬ, 2018)

 

Надстройку PQ рекомендуется  в обязательном порядке установить (кроме версии 2016 Pro+), и этот встроенный инструмент выглядит следующим образом (рисунок 1):

 

Рисунок 1

Консолидация любого количества файлов за 10 минут (рисунок 1)
 

Для установки вы должны знать, какая разрядность у вашего Excel: закладка «Файл» — «Учетная запись» — «О программе Excel».

Кстати, редактор PQ есть в свободном доступе и его можно бесплатно скачать из интернет-ресурса Microsoft. Достаточно ввести в поиске Power Query и перейти на официальный сайт. Первый шаг — выбрать язык надстройки. Обращаю ваше внимание, что языки надстроек не меняются при изменении языкового пакета Excel. Изменение языка возможно только с помощью переустановки надстройки.

 

Итак, выбираем язык, нажимаем далее. В следующем окне выбираем, какую версию надо скачать в соответствии с разрядностью вашего Excel. Когда файл скачан — делаем установку, перезагружаем Excel — надстройка готова для работы. PQ появится отдельной вкладкой на главной ленте.

При использовании PQ есть возможность подключаться к огромному количеству источников (рисунок 2).

Рисунок 2

Консолидация любого количества файлов за 10 минут (рисунок 2)

Функционал намного больше, чем стандартный импорт. Есть вариант импорта как одного источника, так и целой папки с файлами. Именно этот вариант нам понадобится для консолидации.

 

Исходные данные: необходимо консолидировать 8 файлов по разным клиентам в один. Как мы классически это делаем? «Чистим» каждый файл от лишней информации, лишних строк, убираем группировки, цвета, убираем объединенные ячейки, форматирование и прочее.

 

При подключении к файлам с помощью PQ все элементы обработки исчезают сами, но строки (пустые) остаются. Есть две группы товаров — фрукты и овощи (объединенные ячейки), в каждой группе определенный набор SKU (объединенные ячейки), для каждого SKU есть факт продаж за 2017 год и план продаж на 2018 год как в деньгах, так и в килограммах. Данные по каждому показателю разделены на 52 недели (по столбцам).

 

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

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

Рисунок 3

Консолидация любого количества файлов за 10 минут (рисунок 3)

 

Далее представлена таблица с итогами (рисунок 4).

Рисунок 4

Консолидация любого количества файлов за 10 минут (рисунок 4)

 

Для подключения к папке с файлами — Закладка «Данные» — «Создать запрос» — «Из файла» — «Из папки». Выбираем папку, где сохранены наши файлы для консолидации.

 

Читать дальше →