Преобразование отчета из какой-то левой БД в плоскую таблицу в Excel⁠⁠

Кому лениво читать статью, видео со всеми действиями в конце статьи

Приложить файл примера не знаю как. Если кто подскажет, добавлю файл.

***

Ой, как я «люблю» отчеты из 1С анализировать! (Нет, нет и еще раз нет!) Там такие неудобные шапки, сводки, да еще объединенные ячейки везде. Выполнить анализ в Excel примерно такого отчета без предварительных «танцев с бубном» раньше было сложно:

Сейчас же у нас есть волшебный Power Query, позволяющий в несколько шагов очистить данные от лишних заголовков и объединений, и «развернуть» сводку данных в плоскую таблицу.

Итак, шаги по порядку.

1 шаг. Сформировать запрос к таблице

Лучше делать это из чистой книги.

1. На вкладке Данные выбираем Получить данные — Из файла — Из книги, находим файл, нажимаем Импорт:

2. Выбираем лист с таблицей, нажимаем Преобразовать (данные):

3. Пугаемся того, что открылось. Знакомимся с окном Power Query и открытой импортированной таблицей:

Для тех, что ранее не работал с окном Power Query:

В правой части окна видно имя запроса (его можно изменить, если необходимо) и примененные изменения (шаги). В списке шагов будет видна последовательность всех примененных действий. Если нужно, шаги можно удалять кнопкой «Х» рядом с соответствующим шагом.

Сверху в окне Power Query находятся командные вкладки для преобразования данных.

2 шаг. Преобразование данных

1. Удаление лишних строк

Так как данные листа импортированы полностью, а таблица начинается лишь с 4-й строки на листе, нужно удалить первые 3 строки: вкладка Главная — Удалить строки — Удаление верхних строк. В появившемся окне ввести «3», нажать ОК:

2. Повышение заголовков

Еще нужно удалить лишние столбцы, но пока столбцы без названий, определять их неудобно. Чтобы первую строку таблицы установить заголовками, на Главной вкладке нажимаем Использовать первую строку в качестве заголовков:

3. Переименуем столбец Column3 в «Наименование товара», т.к. здесь не было понятного заголовка: двойной клик по наименованию Column3:

4. Удаляем лишние столбцы

Все столбцы, что идут с названием ColumnХХ - пустые. Они получились в результате разъединения объединенных ячеек. Чтобы их удалить, на Главной вкладке нажимаем Выбор столбцов — Выбор столбцов, а затем снимаем флажки со всех столбцов с заголовками ColumnХХ, а также со столбца Итого:

Результат, который получается на данный момент:

5. Заполняем пустые строки в столбце Категория

Выделив этот столбец, на вкладке Преобразование выбираем Заполнить — Заполнить вниз. По всему столбцу будут заполнены категории товаров:

6. Удалим пустые строки

Оставшиеся пустые строки (null) содержат либо ненужные заголовки, либо суммы по категориям товаров. Эти сведения для плоской таблицы не нужны. Убрать их можно фильтрацией по столбцу Наименование товара: нажав кнопку фильтра столбца, убрать флаг NULL:

Результат после 6-го действия:

7. «Развернем» данные

Нужно выделить первые 2 столбца (Категория и Наименование товара), затем на вкладке Преобразование в команде Отменить свертывание столбцов выбрать Отменить свертывание других столбцов:

8. Данные из столбцов будет расположены в стоки, появятся столбцы Атрибут с датами (месяцами продаж) и Значение с суммами. Имеет смысл их сразу переименовать в Период и Сумма соответственно:

9. Для корректного анализа данных Периоду нужно присвоить формат данных Дата, а Сумме — формат Валюта: кнопка «АВС123» в заголовках столбцов:

Результат после 9 действия (4 столбца и 126 строк):

10. Последнее действие — выгрузить данные на лист Excel: команда Закрыть и Загрузить на вкладке Главная:

На листе Excel появится таблица и сведения о запросе:

Область с именем запроса не нужна для отображения, ее можно закрыть.

А дальше уже работаем с получившейся таблицей, как необходимо.

Например, можно сделать такой отчет:

***

Видео со всеми шагами: