Трансформация столбца в таблицу

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

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

Очевидно, что вручную этим заниматься очень долго и муторно, поэтому давайте разберем несколько способов сделать это красиво.

Способ 1. Формулы

Если внимательно присмотреться к исходным данным, то можно заметить четкую систему - столбец делится на блоки по 7 ячеек, каждый из которых нам нужно трансформировать в соответствующую строку. Чтобы это сделать, нужно привязаться к номерам строк в исходной таблице. Каждая седьмая строка (1,8,15...) - это дата сделки. На одну строку ниже - имя менеджера. Еще на одну ниже - город и т.д. Для простоты и наглядности добавим к заготовке будущей таблицы нумерацию слева и сверху:

Добавляем нумерацию

Числа слева (1,8,15...) - это номера строк, начинающих каждый блок. Числа сверху (0,1,2,3...) - это сдвиг внутри блока. Теперь, чтобы получить адреса нужных нам ячеек можно ввести простую формулу, склеивающую букву столбца с номером строки:

Формируем адрес нужной ячейки

Обратите внимание на корректное закрепление знаками доллара строк и столбцов - это важно при копировании ссылки из первой ячейки на весь зеленый диапазон.

И осталось превратить нашу текстовую ссылку в полноценную. В этом нам поможет функция ДВССЫЛ (INDIRECT), о которой я подробно уже писал:

Используем ДВССЫЛ

Вуаля.

Способ 2. Power Query

Power Query - это бесплатная надстройка для Excel, созданная компанией Microsoft. Для Excel 2010-2013 ее можно совершенно свободно скачать и установить (появится вкладка Power Query), а в Excel 2016 она уже встроена по умолчанию и все ее инструменты находятся на вкладке Данные (Data). Эта надстройка умеет импортировать в Excel данные практически из любых источников и трансформировать их потом любым желаемым образом.

Для начала, превратим наш диапазон в "умную таблицу" - для этого данные нужно выделить и нажать сочетание клавиш Ctrl+T или выбрать на вкладке Главная команду Форматировать как таблицу (Home - Format as Table). В появившемся затем окне важно снять галочку Таблица с заголовками (My table has headers), т.к. "шапки" у нас нет:

Преобразуем данные в умную таблицу

После загрузки наши данные появятся в новом окне Power Query:

Окно Power Query с данными

Теперь добавляем к данным столбец индекса (т.е., фактически, имитируем нумерацию строк) через вкладку Добавить столбец - Столбец индекса (Add Column - Index Column):

Добавляем индекс

А затем добавим столбец, где выведем остаток от деления индекса на 7 с помощью команды Добавить столбец - Стандартные - Остаток от деления (Add Column - Standart - Mod):

Остаток от деления на 7

Теперь свернем нашу таблицу, конвертируя числа в получившемся столбце в заголовки новых колонок. Для этого используем команду Столбец сведения на вкладке Преобразовать (Transform - Pivot Column):

Сворачиваем в столбцы

Осталось заполнить получившиеся пустоты (null) во всех столбцах, кроме двух последних командой Заполнить вниз на вкладке Преобразовать (Transform - Fill Down):

Заполнение вниз

Удалить ненужные столбцы (первый и последний) и отфильтровать лишние строки (убрать галочку Null в фильтре по столбцу 5). Заголовки столбцов можно переименовать двойным щелчком для пущей приятности, а также настроить подходящие форматы данных с помощью иконок в шапке:

Убираем лишнее и переименовываем столбцы

Готовую таблицу выгружаем на новый или любой из имеющихся листов с помощью команды Главная - Сохранить и загрузить - Сохранить и загрузить как (Home - Save&Close - Save&Close to):

Выгружаем результаты на лист

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

Способ 3. Макрос из надстройки PLEX

Если у вас установлена моя надстройка PLEX для Excel 2007-2016, то можно все сделать еще быстрее и проще. Выделяем исходные данные и жмем на вкладке PLEX - Трансформация - Изменить размеры (Transform - Resize):

Команда Изменить размеры на PLEX

В открывшемся окне настраиваем параметры преобразования:

Настройки ресайза

Жмем ОК и получаем готовый результат:

Результат

Быстрее уже некуда :)

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




16.11.2017 16:07:06
Класс! Как всегда, очень полезная информация!
Спасибо!
03.12.2017 22:54:22
Обычно писал макрос под такую  задачу
А тут как просто Спасибо.
eak
07.12.2017 20:26:17
А что делать, если внимательно присмотревшись к исходным данным, замечаешь, что нет четкой системы. Блоки произвольно чередуются по 8,7 и 4 строки, но что есть четко, так это пустая строка между блоками. Есть ли способ развернуть данные, опираясь на этот признак? Кстати в вашем примере, блоки также отделены друг от друга пустой строкой.
08.12.2017 10:40:36
1. Добавить столбец - Столбец индекса
2. Добавить столбец - Пользовательский столбец - вводим формулу: =if [имя столбца с данными]=null then [столбец индекса] else null
3. Выделяем созданный столбец с формулой и Преобразование - Заполнить вниз
4. Преобразовать - Столбец сведения

Как-то так бы делал :)
08.02.2018 08:41:20
Здравствуйте!
Сделал по этому алгоритму, но ничего не получилось.
Николай, объясните, пожалуйста, решение этой задачи (когда блоки разного размера) чуть подробней.
Три дня уже мучаюсь :).
25.04.2018 11:12:35
В способе 2 можно сделать чуть-чуть по-другому избавившись от протаскивания совсем.

После того как мы добавили столбец "Индекс", следующий шаг - для колонки "Индекс" сделать Add Column - Standard - Divide (Integer) на 7.
Затем из вкладки Transform сделать "Modulo" также на 7 на столбец "Индекс".
Потом, выделив столбец "Индекс" делать Pivot (Преобразовать) на столбец с исходными данными, в данном пример "Столбец 1", без агрегации.

    #"Добавлен индекс" = Table.AddIndexColumn(#"Измененный тип", "Индекс", 0, 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Добавлен индекс", "Integer-Division", each Number.IntegerDivide([Индекс], 7), Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Inserted Integer-Division", {{"Индекс", each Number.Mod(_, 7), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Индекс", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Индекс", type text}}, "ru-RU")[Индекс]), "Индекс", "Столбец1"),
 
И получится табличка, где останется только удалить лишние столбцы и назвать правильно существующие.
29.06.2018 08:58:58
подскажите, пожалуйста, как трансформировать столбец в таблицу, если у блоков разное количество значений
пример
ООО"МИР"
165
130
8
ООО "Спэк"
678
65
789
78
34
ООО "Рэкс"
89
123
90
34
23
ООО "Норм"
98
765
78
4
56
78
04.09.2018 11:48:33
а как трансформировать наоборот, из таблицы в столбец? PLEX-ом могу, а через формулы, что-бы отображались сразу изменения?
Наверх