Трансформация столбца в таблицу
Представьте, что после выгрузки из какой-либо корпоративной 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:
Теперь добавляем к данным столбец индекса (т.е., фактически, имитируем нумерацию строк) через вкладку Добавление столбца - Столбец индекса (Add Column - Index Column):
А затем добавим столбец, где выведем остаток от деления индекса на 7 с помощью команды Добавление столбца - Стандартные - Остаток от деления (Add Column - Standard - Mod):
Теперь свернем нашу таблицу, конвертируя числа в получившемся столбце в заголовки новых колонок. Для этого используем команду Столбец сведения на вкладке Преобразовать (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):
В открывшемся окне настраиваем параметры преобразования:
Жмем ОК и получаем готовый результат:
Быстрее уже некуда :)
Ссылки по теме
Спасибо!
А тут как просто Спасибо.
2. Добавить столбец - Пользовательский столбец - вводим формулу: =if [имя столбца с данными]=null then [столбец индекса] else null
3. Выделяем созданный столбец с формулой и Преобразование - Заполнить вниз
4. Преобразовать - Столбец сведения
Как-то так бы делал
Сделал по этому алгоритму, но ничего не получилось.
Николай, объясните, пожалуйста, решение этой задачи (когда блоки разного размера) чуть подробней.
Три дня уже мучаюсь
Никак не введу пользовательскую Формулу, что должно получиться?
После того как мы добавили столбец "Индекс", следующий шаг - для колонки "Индекс" сделать 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"),пример
ООО"МИР"
165
130
8
ООО "Спэк"
678
65
789
78
34
ООО "Рэкс"
89
123
90
34
23
ООО "Норм"
98
765
78
4
56
78
2. Добавить столбец - Пользовательский столбец - вводим формулу: =if Text.Start([имя столбца с данными],3)="ООО" then [столбец индекса] else null
3. Выделяем созданный столбец с формулой и Преобразование - Заполнить вниз
4. Преобразовать - Столбец сведения
Как в случае с непостоянным количеством строк в блоке обеспечить их (строк) однотипное именование? Остаток от деления не подходит, в предложенных алгоритмах, что с пустой строкой, что с Text.Start, такого шага по именованию столбцов нет.
Подскажите, пожалуйста
Expression.Error: Элементов в перечислении было слишком много для выполнения операции.
Подробные сведения:
List
(300 строк на 6 колонок)
количество считает, при не агрегировании не работает
нашла на ютубе, заграничная барышня рассказывает, как избавиться от этого спецэффекта, ссылку не даю, можно найти по названию видео "How to fix the "Too many elements in the enumeration to complete..." pivot error in Power Query"
правда, мне не помогло... самое интересное, один раз сработал алгоритм, а при последующем обновлении выдало такую ошибку
Не пойму что делать.
There were too many elements in the enumeration to complete the operation.
буду рад помощи.
1. Добавить столбец - Столбец индекса
2. Добавить столбец - Пользовательский столбец - вводим формулу: =if Text.Start([имя столбца с данными],3)="ООО" then [столбец индекса] else null
3. Выделяем созданный столбец с формулой и Преобразование - Заполнить вниз
4. Преобразовать - Столбец сведения
но чувствую, что нужен следующий шаг, чтобы данные в столбцах новой таблице заполнялись с первой строки. Помогите, пожалуйста!