Анпивот (Unpivot) — отмена свертывания, преобразование двумерной таблицы в плоскую, пригодную для построения сводной таблицы.
Эту операцию можно проделать в Power Query или формулами. Но функции для решения задачи формулой есть только в новой версии Excel, так что начнем с доступной PQ.
Power Query
— Данные — «Из таблицы/диапазона» (выделяем предварительно исходную таблицу, любую ее ячейку):
Эту операцию можно проделать в Power Query или формулами. Но функции для решения задачи формулой есть только в новой версии Excel, так что начнем с доступной PQ.
Power Query
— Данные — «Из таблицы/диапазона» (выделяем предварительно исходную таблицу, любую ее ячейку):
— Выделяем в редакторе Power Query те столбцы, которые должны остаться в новой таблице — в нашем примере это «Компания».
— Преобразование — Отменить свертывание других столбцов (Transform — Unpivot Other Columns):
Получим такую картинку:
Столбцы «Атрибут» и «Значение» можно переименовать. Для этого достаточно кликнуть по ним дважды и ввести новое название.
Остается отправить полученное добро обратно в Excel — в виде таблицы или сводной. Закрыть и загрузить в… (Close & Load To…):
И выбрать нужный вариант, например, таблицу:
И вот она на листе Excel:
Новые формулы
Если у вас Microsoft 365, можно решить задачу формулами, используя новые функции.
Если у вас Microsoft 365, можно решить задачу формулами, используя новые функции.
На первом шаге используем функцию EXPAND / РАЗВЕРНУТЬ: она добавляет к массиву с названиями компаний (первому столбцу, который останется неизменным) два лишних пустых столбца, заполненных ошибкой. То есть мы просто увеличиваем размер массива. Данные остаются на месте, а «новые» значения отображаются как #Н/Д:
=РАЗВЕРНУТЬ (первый столбец;;количество других столбцов)
Количество столбцов вычисляем с помощью функции СЧЁТЗ / COUNTA:
=РАЗВЕРНУТЬ (Данные[Компания];;СЧЁТЗ (Данные[[#Заголовки];[Курс]:[Электронная библиотека]]))
=РАЗВЕРНУТЬ (первый столбец;;количество других столбцов)
Количество столбцов вычисляем с помощью функции СЧЁТЗ / COUNTA:
=РАЗВЕРНУТЬ (Данные[Компания];;СЧЁТЗ (Данные[[#Заголовки];[Курс]:[Электронная библиотека]]))
На втором шаге заменяем эти пустоты (ошибки) на исходные данные с помощью ЕСНД / IFNA (функции, которая заменяет ошибки Н/Д на второй свой аргумент). Иначе говоря, мы клонируем каждое название компании три раза (по числу столбцов с данными).
=ЕСНД(РАЗВЕРНУТЬ(первый столбец;;количество других столбцов); первый столбец)
На третьем шаге делаем этот массив плоским с функцией ПОСТОЛБЦ / TOCOL:
=ПОСТОЛБЦ (ЕСНД (РАЗВЕРНУТЬ (первый столбец;;количество других столбцов); первый столбец))
=ПОСТОЛБЦ (ЕСНД (РАЗВЕРНУТЬ (первый столбец;;количество других столбцов); первый столбец))
На финальном шаге к этому столбцу справа надо прибавить еще два: с названиями продуктов и с собственно данными. Продукты надо, как мы и делали с компаниями, РАЗВЕРНУТЬ с помощью одноименной функции, размножить и сделать плоским с помощью ПОСТОЛБЦ:
С данными (суммами) проще — там ничего не нужно клонировать, просто сделать плоским:
И вот у нас три столбца: с названиями компаний (первый столбец из исходной таблицы, но каждое значение из него мы повторили столько раз, сколько у нас столбцов в исходной таблице) + с названиями продуктов (каждое название тоже повторили) + суммы, которые мы превратили в одномерный массив. Собираем все это в одну таблицу функцией ГСТОЛБИК / HSTACK, которая объединяет массивы горизонтально:
