Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.
В данной статье я покажу, как можно преобразовать такой «горе-отчет» в массив для анализа с помощью Сводной таблицы.
Инструкция
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
 Пример | 52 Кб | 3085 |
Итак, у нас имеется отчет, примерно такого вида:
Нам соответственно необходимо растащить по разным столбцам Название Клиента, Товарную категорию и SKU, так же нам необходимо свести в один столбец данные из Января, Февраля и Марта, и создать еще один столбец, в который напротив соответствующей суммы поместить название месяца. Да, и еще нам необходимо убрать все итоговые столбцы и строки. В общем нам необходимо получить что-то вроде этого:
Для этого нам понадобиться надстройка ЁXCEL (PRO).
А дальше начинаются чудеса, для начала удалим итоговый столбец, в данном случае это столбец "E". Выделяем и удаляем:
Далее мы встаем на первую ячейку с данными в столбце с Клиентами, Категориями и SKU, в данном случае это будет ячейка "А2", заходим в главном меню во вкладку ЁXCEL и нажимаем кнопку "Таблицы", в выпавшем меню выбираем команду "Трансформировать цветную таблицу в массив":
В открывшемся диалоговом окне нажимаем "ОК":
В следующем диалоговом окне также нажимаем "ОК":
Ждем. Получаем такую таблицу:
Даем соответствующие названия столбцам "А", "В" и "С", чтобы получилось так:
Спускаемся вниз таблицы и удаляем строку с общим итогом:
Выделяем вновь получившуюся таблицу в главном меню переходим во вкладку ЁXCEL, нажимаем кнопку "Таблицы", в выпавшем меню выбираем команду "Трансформировать таблицу в массив":
В открывшемся окне мастера нажимаем кнопку "Далее":
Во второй вкладке мастера вводим "Количество столбцов содержащих названия строк:", в нашем случае "3" (Клиент, Категория, SKU) и нажимаем кнопку "Трансформировать":
Ждем. Получаем такой массив:
Подписываем заголовки столбцов "D" и "E":
Встаем в ячейку "А1" и строим сводную таблицу (Как построить сводную таблицу?):
 |
 |
 |
Комментарии
1. Выделили лишние ячейки;
2. Есть скрытые ячейки;
3. Есть объединенные ячейки;
4. ....
У меня Эксель 2016 года . Во вкладке Главная нет таблицы и соответственно трансформироват ь в массив нет.