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

Представьте, что после выгрузки из какой-либо корпоративной 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 - Standard - Mod):

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

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

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

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

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

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

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

Готовую таблицу выгружаем на новый или любой из имеющихся листов с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить как (Home - Close&Load - Close&Load 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
Здравствуйте!
Сделал по этому алгоритму, но ничего не получилось.
Николай, объясните, пожалуйста, решение этой задачи (когда блоки разного размера) чуть подробней.
Три дня уже мучаюсь :).
29.01.2021 20:39:55
Объясните, пожалуйста, по подробнее
Никак не введу пользовательскую Формулу, что должно получиться?
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"),
 
И получится табличка, где останется только удалить лишние столбцы и назвать правильно существующие.
13.01.2019 12:53:50
Василий, шикарный способ - спасибо за идею! :)
nik
06.08.2019 22:27:14
А в чем разница метода от описанного, для чего использовать divide (Integer) на 7.? Что делать дальше
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
20.07.2019 08:34:07
1. Добавить столбец - Столбец индекса
2. Добавить столбец - Пользовательский столбец - вводим формулу: =if Text.Start([имя столбца с данными],3)="ООО" then [столбец индекса] else null
3. Выделяем созданный столбец с формулой и Преобразование - Заполнить вниз
4. Преобразовать - Столбец сведения
04.09.2018 11:48:33
а как трансформировать наоборот, из таблицы в столбец? PLEX-ом могу, а через формулы, что-бы отображались сразу изменения?
11.12.2018 06:26:07
Как трансформировать в таблицу при разном количестве значений в блоках? Понимаю, что нужно добавить строки сснулями, чтоб выровнять блоки, но как это сделать макросом и в нужных местах, а не где попало?
20.07.2019 08:32:46
Чуть выше был ответ на такой же вопрос :)
04.12.2020 15:36:08
Не нашли выход? Очень актуальная проблема
28.12.2018 09:15:18
Power query подвел(
Expression.Error: Элементов в перечислении было слишком много для выполнения операции.
Подробные сведения:
   List
(300 строк на 6 колонок)
количество считает, при не агрегировании не работает
12.06.2020 15:58:23
Подтверждаю, на дворе 2020 год PQ не смог раскидать столбец из 600 строк на 7 столбцов.
столкнулась с такой же ошибкой, но у меня и строк больше полумиллиона....
нашла на  ютубе, заграничная барышня рассказывает, как избавиться от этого спецэффекта, ссылку не даю, можно найти по названию видео "How to fix the "Too many elements in the enumeration to complete..." pivot error in Power Query"

правда, мне не помогло... самое интересное, один раз сработал алгоритм, а при последующем обновлении выдало такую ошибку
20.07.2019 08:31:45
В случае блоков одинакового размера в Power Query можно всё сделать еще проще, оказывается. Нашел недавно решение буквально в одну строку М-кода. Мощь!
nik
06.08.2019 22:34:29
Мне выдает ошибку Expression.Error: A cyclic reference was encountered during evaluation.
Не пойму что делать.
22.08.2019 11:36:56
вот такая засада после пивота
There were too many elements in the enumeration to complete the operation.
буду рад помощи.
18.09.2019 18:26:53
Добрый! А если в одной ячейке текст, который нужно разделить на столбцы? Либо одинаковые слова и их надо разделить на столбцы?
Николай, добрый день! Занимаюсь самостоятельным изучением Power Query, конечно, с помощью Ваших уроков, и при транспонировании столбца в таблицу с разным количеством строк в блоках получаю, что каждый блок располагается в отдельном столбце - Ура!, НО не со второй строки, а на  несколько строк ниже. Не знаю как с этим бороться. Пользуюсь Вашей инструкцией из данной темы:

1. Добавить столбец - Столбец индекса
2. Добавить столбец - Пользовательский столбец - вводим формулу: =if Text.Start([имя столбца с данными],3)="ООО" then [столбец индекса] else null
3. Выделяем созданный столбец с формулой и Преобразование - Заполнить вниз
4. Преобразовать - Столбец сведения

но чувствую, что нужен следующий шаг, чтобы данные в столбцах новой таблице заполнялись с первой строки. Помогите, пожалуйста!
Наверх