Редизайн кросс-таблицы в плоскую (Unpivoting)

Как трансформировать кросс-таблицу в плоскую, более удобную для анализа сводными таблицами.
Файл-пример можно скачать тут www.planetaexce...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...

[@myshlyapa]

Без проблем в PQ можно сделать unpivot таблицы в любым количеством заголовком (и 2 и 3 и N). 1. Транспонируем таблицу. Заголовки превратятся в строки 2. Объединяем столбы (которые содержат в строках заголовки) по любому разделителю (например "~") 3. Транспонируем таблицу обратно. В результате получаем таблицу с одним уровнем заголовков. 4. Делаем unpivot 5. Разделяем столбец с заголовками по разделителю (в нашем пример "~"). 6. Profit

[@tigranhayrapetyan8357]

У Николая есть другое, более свежее видео об обработке таблицы с многостраничной шапкой - "Сводная по таблице с многострочной шапкой", где как раз эта техника и используется. Сегодня она мне пригодилась))

[@ИванБорисович-н9х]

Добрый день Николай, очень не хватает Ваших видео по работе с данными в Power Pivot и DAX функциям. Благодарные зрители могут рассчитывать на обзоры этой темы?

[@planetaexcel]

Однозначно ДА!

[@Дмитрий-н2й2п]

Ниже ссылка на видео по редизайну таблиц с многоуровневой шапкой средствами Power Query kzbin.info/www/bejne/poebg6prnLidfKs

[@1iuh]

Удивительно, что ни одного лайка к комменту не поставили, а там показано решение.

[@АлексейГайворонский-ф3ш]

Однозначно, большое спасибо автору. Благодаря Вам, ежедневная рутинная работа превращается в творческий процесс, от которого получаешь удовольствие.

[@goshik73]

Когда ткнут носом, всё просто...;) Спасибо! Как раз предстоит подобная задача.

[@Voronza]

Шикарная "цитата", подняло настроение)

[@Midavok]

С помощью power query можно преобразовать таблицу и с многоуровневой шапкой. Для этого нужно с помощью формулы склеить шапку в одну строку, затем «удалить свертывание» таблицы, а после склеянную шапку развернуть с помощью опции «тест по столбцам». Уверен, что Николай об этом прекрасно знает.

[@Toni_Viper]

Вадим Окладников Да, да... Постоянно и с большим удовольствие смотрю видео Николая, но тут тоже подумал, что он "что-то не договаривает". Конечно Power Query может обработать таблицу с многоуровневой шапкой. И даже не нужно писать дополнительных формул. Просто на одном из шагов нужно "склеить" данные из нескольких столбцов, на другом - транспонировать таблицу, далее все тоже самое как показано в видео и в конце нужно разделить склеенные ранее столбцы. А цель видео - реклама надстройки. И хочу заметить, что надстройка действительно хороша по своей функциональности! Хотя сам лично чаще пользуюсь стандартными средствами Excel с использованием хитростей. ;)

[@planetaexcel]

Коллеги, спасибо за комментарий - все верно. Но хотелось показать что-то простое "на раз-два", а то, что вы описываете (склейка-расклейка, транспонирование и прочие танцы) и называется для простых людей "надстройка Power Query не умеет работать с многострочными шапками" :)

[@ИванБорисович-н9х]

Добрый день Николай, очень не хватает Ваших видео по работе с данными в Power Pivot и DAX функциям. Благодарные зрители могут рассчитывать на обзоры этой темы?

[@crypto_bross]

Как круто 👍. Всё просто, когда знаешь

[@Lenokk-lr5de]

Классный макрос! Спасибо! Очень понятная и приятная подача материала. (Только почему же нельзя сделать тоже самое через редактор запросов. Транспонируем. Всю шапку объединяем в один столбец. Транспонируем обратно. Делаем ее заголовком. А потом отменяем свертывание других столбцов зажав нужные. И шапка переходит в столбец, где мы ее разделяем обратно)

[@ИринаБарская-с2р]

Здравствуйте, мне нужно преобразовать таблицу "Динамика расходов за период" , выгруженную из 1С, в удобную, чтобы не было по три поля в одной строке. Могли бы Вы помочь?

[@СофьяПанасенко-м6ю]

Николай, спасибо, что делитесь полезностями! Много лет отслеживаю Ваш блог - реальная помощь в ежедневной работе с Excel!

[@T_i_m_T_i_m]

Кто подскажет, Почему файл с unpivot весит больше чем pivot, ведь кол-во ячеек не меняется по сути, увеличивается кол-во строк, но кол-во столбцов то уменьшается?

[@АнтонК-ы8й]

А если таблица имеет следующий вид: Менеджер, продажа 1, дата продажи, номер документа, продажа 2, дата продажи, номер документа, продажа 3 и т.д. как ее привести в нормальный вид?

[@МаксимНарушевич-с6к]

Замечательно! Еще одно видео в копилку "Антологии Николая Павлова"))

[@Albertus99]

Николай пример с Квери не содержит описания того как именно разобраться со строкой 1 (Еда). В фале примере строки заголовка 2, а в ролике рассматривается вариант как будто бы она 1 (((

[@biweb13]

Раньше решал эту задачу с помощью «Мастера сводных таблиц и диаграмм» встроенного в Excel, только вот уже не помню, может он переварить двухмерную шапку или нет? Но сейчас уже давно привык использовать PLEX. Спасибо Николай за Вашу надстройку!

[@konstantinzhizhilkin2930]

Кстати, да, "старый мастер" (сводных таблиц) действительно это тоже умеет, но там есть некая хитрость (или просто надо знать) как его настроить, чтобы получился нужный результат. На одном из экселевских сайтов (не на моем) есть неплохое описание как это можно сделать.

[@planetaexcel]

Построить сводную, закинуть все текстовые поля в строки, а числовые в значения и сделать двойной щелчок по общему итогу - провалитесь в новый лист с детализацией (drill down). Только с многоуровневыми шапками такой номер тоже не пройдет, к сожалению :)

[@СветланаНиколаевская-т3щ]

Спасибо, то, что нужно!

[@ВиталийЯрмыш-х2к]

Очень хорошо что затронули такой механизм как Power Query. Хотелось бы больше о нем узнать.

[@senge_v4871]

Виталий Варвар На канале Станислав Салостей есть видео про Power Query.

[@Shorek_chelvok]

недавно у Николая Павлова вышла книга по Power Query

[@АлексейГороднов-ц7л]

Николай здравствуйте,помогите решить проблему. Роботаю в компании,по роду груза через функцию,нужно разложить в крации,количество функции привышает 64.подскажите как можно увеличить это количество,спасибо.

[@ЦарьБогоф]

=ЕСЛИ(A2=Лист7!A2:A19;"фрукты";ЕСЛИ(A2=Лист7!B2:B19;"овощи";"специи")) подскажите что не так с этой формулой и как надо было сделать.

[@Pavlin072]

Спасибо за урок!!! Выручили с преобразованием таблиц - теперь все просто!

[@JackFamous]

Согласен с Иваном Борисовичем! Уважаемый Николай, с Вашими уроками (которые, кстати, и привели меня к углублённому изучению Excel) было бы намного проще овладеть таким масштабным инструментом, как Power Pivot с его "страшными" DAX-формулами))) жду-жду-жду)))) По уроку: у Станислава Салостея есть подобный урок - в нём проблема с несколькими уровнями решается через такой "костыль": все уровни (в данном примере - 2) сцепляются по столбцам через уникальный разделитель (например "%"), тем самым получаем одноуровневую шапку и, далее - как вы показали. После "свёртывания", можно расцепить обратно, например с помощью инструмента "Текст по столбцам" на вкладке "Данные". В итоге получим желаемое))) А PLEX - в любом случае mast have. Без вопросов и, тем более, такие копейки 😍

[@Rice0987]

Спасибо за урок! Следует также уточнить, что для работы надстройки Power Query необходим Internet Explorer 9 RTM или более поздняя. Это если у кого не самая последняя версия операционной системы.

[@Strelokcs16]

Как раз недавно на форуме спрашивал как такое можно сделать. Спасибо!

[@nasharussian4003]

Благодарю за сэкономленные часы жизни.

[@ЭдуардКалашников-д3ц]

Спасибо Николай! И как хорошо, что у меня уже есть надстройка.

[@АлексейСоков-ь8и]

2022

[@illiafilin4107]

В очередной раз спасибо за помощь!

[@zulu2echo]

Отлично, спасибо!

[@ЛизаДрагун-э8ю]

Николай, добрый день. Переписала в точности Ваш пример (и саму таблицу с данными и макрос). Когда запускаю макрос, система выдает ошибку: Выделяет желтым эту строчку: For r = (hr + 1) To inpdata.Rown.Count и пишет в автоматически открывшемся окне: Run-time errot "438" Object doesn't support this proprety method (данные перед тем как запустить макрос выделяю).Прошу помочь. Заранее спасибо!)

[@alexanderkapelnitsky1707]

Rown - такого свойства нет в VBA, надо писать Rows

[@ellabrodsky5622]

Николай, спасибо за Ваши уроки. Мне не понятно, как подключитьPLEX

[@Shorek_chelvok]

скачать с сайта там есть инструкции

[@GukovTimur]

Добрый день, Николай! На собеседовании спросили работал ли я с кросс-таблицами. Я ответил впервые слышу, но зато работал со сводными. Сказали это разные вещи и я не подхожу. Дома решил загуглить что это такое "кросс-таблицы". Таким образом наткнулся на данное видео. Полезный канал! Всё доступно объясняете и показываете. С меня подписка. Спасибо!

[@1iuh]

"Пёс Барбос и необыкновенный кросс".

[@SophieKruz-h2n]

Здравствуйте :) возможно сможете подсказать, как сделать это же только в гугл таблицах?

[@planetaexcel]

Вот в Гугл-таблицах я полный ноль, к сожалению. Нужен другой спец :)

[@SophieKruz-h2n]

@@planetaexcel благодарю за ответ :)

[@ИгорьСомов-м1щ]

Очень часто сталкиваюсь с тем что не могу сформулировать вопрос по работе в excel захожу на канал и вижу ответ. Вы просто Вольф Мессинг🥇

[@planetaexcel]

Просто мне их часто задают :) О очень много вопросов - похожие :)

[@ИгорьСомов-м1щ]

@@planetaexcel когда данных много запрос усекается. Просит сократить кол-во столбцов и фильтров.

[@ИгорьСомов-м1щ]

И если я даже с этим соглашаюсь то после редактирования выгрудается ещё меньше.

[@konstantinzhizhilkin2930]

А тем у кого "старый добрый Excel 2003" и для кого не приемлемы макросы приходится использовать формулы. Для вот таких малюсеньких таблиц как в примере, они могут еще нормально подойти, а для гигантских таблиц, формулы, конечно, очень сильно будут тормозить (хотя, впрочем, преобразование это разовое действие можно и подождать их пересчета). :-)