Многоуровневый список по столбцам в Excel

Три способа (формулы, Power Query и макрос) чтобы разложить многоуровневый список с вложенной нумерацией по отдельным столбцам.
Скачать пример www.planetaexc...
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Подпишитесь на наш канал в Telegram, чтобы оперативно узнавать о всех новых возможностях и лайфхаках при работе в Excel и Power BI - t.me/planetaexcel

[@1114den]

Самый полезный канал по работе с Excel. Техника подачи материала супер и не меняется уже годами.

[@АлексейЛ-ж4с]

Николай! Преклоняю голову... Спасибо Вам за ваш труд!) Очень полезный и нужный контент! Действительно, всё просто - когда знаешь...) 🤝

[@user-11_01]

Творец чудес!🎉🎉🎉 Thank you👏👏👏👏👏👏👏👏👏👏👏👏👏🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥👍👍👍

[@ASGol]

Во истину, "Когда знаешь - всё просто"!!! Благодарю, что делитесь своими знаниями, Николай!

[@SV-13]

"Воистину" - теперь Вы и это знаете.

[@ВикторОсадчий-ы5п]

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

[@Albertus99]

Николай огромное спасибо как всегда Вы на высоте!

[@ЮлияВойченко-м5е]

Особенно порадовали муж с котом на диване 😂 Спасибо за видео, всегда очень интересно и полезно

[@mirabe11a]

Вижу ролик Николая - ставлю лайк. Спасибо вам!

[@alexshat4715]

Николай, спасибо за всегда полезный контент. Запишите пожалуйста видео по созданию, редактированию xml прайсов и схем в экселе.

[@АзимНишанов-ф5э]

Очень полезное видео. Всегда жду новых выпусков, снимите пожалуйста больше роликов про PQ, потому что PQ для многих в частности для меня самого это тёмная лошадка в которой знаю только несколько элементарных функций но не более.

[@aiguljantliyeva54]

сколько раз выручали Ваши видео, использовала для работы, спасибо огромное!

[@zaurmissikov]

Ставлю лайк и смотрю!

[@SV-13]

А надо - наоборот! Лайки до просмотра ЮТ воспринимает, как накрутку, т.ч., это - медвежья услуга...

[@Ozadachnick]

О! Спасибище! Эту функцию неделю искал и вот оно!

[@nat29011985]

Спасибо! По BI бы ещё побольше уроков.

[@Denius177]

Ждем видео с уроками по Power BI

[@nat29011985]

Поддерживаю! Тоже жду с нетерпением.

[@anton_a388]

Спасибо за выпуск

[@Alu-c4d]

Как всегда круто!👍

[@ВасилийКротов-м6и]

Спасибо большое!!!

[@artynd]

Можно использовать формулы динамический массивов, тогда всё решение поместится в одну формулу, без протягивания и обновления формул: =LET( Level, LEN(Table4[Номер])-LEN(SUBSTITUTE(Table4[Номер],".",""))+1, cLevel, TRANSPOSE(SORT(UNIQUE(Level))), VSTACK(cLevel, IF(Level=cLevel, Table4[Данные], "" )))

[@grankin69]

Николай, спасибо за пример. А как добавить в в макрос еще такую функцию, чтобы убрать пустые строки в каждом уровне, чтобы на основе этой таблицы можно было делать сводные таблицы. Это как раз таки очень утобно для строительных смет. Я имею в виду, чтобы ввесть первый уровень Заполнился словом "Прихожая" до слова Кухня, Кухня до Зала и т.д.

[@1iuh]

12:35 Николай, как сделать Сводную таблицу, чтобы в верхнем уровне 1 показывал список позиций всех нижних уровней 3 и суммирование или выбор поздней даты? Сделайте видео, если не сложно. Спасибо.

[@Gennadii_Aleksandrovich]

Спасибо за урок. Часто работаю с строительными сметами и иногда приходится так делать. Был бы рад если подскажите как поставить быстро функцию агрегат на первом вхождении и по нему сделать группировку.

[@artynd]

Для Power Query, если вы не уверены, какие именно символы будут использованы в нумерации строк, чтобы их потом вручную не добавлять, можно использовать эту формулу - Text.Length( Text.Select( [Номер] , {"0".."9"} ) )

[@planetaexcel]

Отличная мысль, спасибо! :)

[@tomcruz2801]

Из 1с когда выгружается список , вот такие уровни только без нумераций . Есть решение преобразовать такой список для сводной таблицы ? Ну чтоб все строки были заполнены ?

[@edkorkz]

Аналогичная проблема. Подскажите, пожалуйста, способ как решить эту проблему? Сейчас количество отступов приходится рассчитывать через макрос подсчета отступов.

[@user-11_01]

222 подписчиков! Красивая цифра! Пусть будет min 2 222 222!!!🤲🤲🤲

[@евгенийтаранов-ф3о]

Николай, где можно поподробнее ознакомиться со списком команд и их описанием в PowetQuery?

[@armen13]

3:54 Николай, когда Вы нажимаете F4, над номером ячейки выводится значение этой ячейки. Это Вы монтируете для видео или это при обычной работе у Вас тоже выводится? Если второе, то расскажите, пожалуйста, как это сделать. Про F9 знаю, но это не так удобно как подсказки.

[@planetaexcel]

Это фишка последних обновлений Office 365 :)

[@armen13]

@@planetaexcel Проверил у себя, не выводит. Может где-то нужно включить это? У меня подписка 365 домашняя. Версия Microsoft Excel для Microsoft 365 MSO (16.0.12527.22286), 64-разрядная версия. При попытке обновить, отвечает, что у меня последняя версия. Лицензия честно оплачивается через Черногорию.

[@armen13]

Оказывается офис не обновляется на windows 7. Либо переустанавливать офис, либо устанавливать 10+

[@AlexanderGorshkov]

Два кота - один с мужем, второй в цветке) И где же наша полюбившиеся рубрика - с помощью нейронки)

[@planetaexcel]

А унутре у неё - неонка (зачеркнуто) нейронка! :)

[@antonsll]

Интересно спасибо! 🙏 А как можно решить такое же задание если начинать с такой структурой: | id | name | parent-id | |-------|--------------|-----------| | 100000| house | root | | 101000| kitchen | 100000 | | 101010| table | 101000 | | 102000| bathroom | 100000 | | 102010| toilettpaper| 102000 | благодарен за каждый подсказ! 🙏

[@ДмитрийПахомов-б6у]

имхо макросы самый лучший. я их обажаю!

[@alexandershavleyko1313]

Upstairs Level 🔝

[@mansurdesert]

Здравствуйте. Спасибо за Ваш труд и видео. А как на счёт того, чтобы сравнить таблицы 2-х групп, в процентном соотношении, т.е. какой процент соответствие или процент отличие в двух группах? В интернете нигде не нашел. Имеется только в одном видео от XLTOOLS, но там требуется установка приложение, а приложение не установить, видимо данные устарели?! Не могли бы показать как это делается.

[@4atckuu]

Не показали случай где подуровни в разных столбцах. Расскажите про GPT помощника... Какой выбрать...

[@moiskon]

спасибо

[@АндрейАбвгдеёж-о3ь]

спасибо, сэр

[@lechmaria-d5z]

Добрый день,👋 спасибо за ваши обучающие материалы. Хотел бы задать вопрос, по автоматическому заполнению шаблона документа в Excel. Я работаю в гостинице и каждое утро делаю выгрузку Excel таблицы клиентов, которые должны приехать в гостиницу для зачисления. Все клиенты должны заплатить однотипный документ "Карточку регистрации" в котором указано ФИО и другие данные из Excel таблицы выгрузки. Вопрос: как автоматически перенести данные из Excel выгрузки в однотипный шаблон? -Функция "Слияние " в Word-e мне не подходит тк на одном листе А4 нужно разместить две "Карточки регистрации" двух разных клиентов. Даже в разделе "этикетки" в Word-e нет возможности расположить две этикетки на А4. У нас две "карточки регистрации" печатаются на одином листе А4, затем мы их рвем пополам и каждому клиенту даем на подпись свой экземпляр. -Получается лучше всего сделать шаблон в Excel-е, но как перенести данные из таблицы выгрузки в шаблон? Заранее спасибо 😀👍

[@glockeduard]

Спасибо, интересно. Но Ваш метод будет работать только при маленьких списках, количество значений в каждом уровне которых не более 9. Как только уровень станет 10 и более, то подсчет сломается. Пример: с 1.1 по 1.9 - будут во втором столбце, а вот 1.10 до 1.99 уже в третьем, т.к. Вы считаете количество цифр, а правильней будет считать количество разделителей плюс 1.

[@planetaexcel]

Согласен, но больше 9 уровней вложенности - это редкость. Или диагноз :)

[@glockeduard]

@@planetaexcel если именно глубина вложенности, то конечно это редкость. или диагноз :), но речь не о глубине. Просто расширьте Ваш список например "1. Прихожая" до 10 пунктов: "1.1 Гардероб, 1.2 Зеркало, 1.3 Ящик, 1.4 Цветок, 1.5 ...... 1.10 стул" и на 1.10 метод уже будет не рабочий. 1.10 и последующие улетят в третью колонку :)

[@artynd]

@@glockeduard тогда в этом случае можно использовать формулу - Text.Length( Text.Remove([Номер], {"0".."9"}))+1

[@arsushkins]

Подождите-ка, мы же считаем не длину строки, а разницу между длинами строк с точками и без. Значит условные 10 как были двумя символами в исходной ячейке, так они и останутся двумя символами после замены точек, а значит разница не исказится, будь там хоть 5 символов.

[@arsushkins]

@@glockeduard, берём, например, любую рабочую инструкцию на предприятии по СМК, и я уверен, что там будет больше 9 подпунктов в каком-то пункте.

[@ТатьянаКаримова-ы9м]

❤❤❤

[@Анна-т5п1д]

🔥🔥🔥

[@bornic983]

Подписался. Вопрос. В ячейке листа две цифры через слеш (наклонная черта). Можно через формулу изменять эти цифры в другой лист? Во втором листе так-же чтобы цифры были через слеш

[@mikhaill.2924]

Полезное видео, но как правило из 1С таблицы выгружаются без столбца с нумерацией. Хотелось бы узнать как в таком случае таблицу перевести в плоский вид

[@planetaexcel]

Такое, боюсь, только макросом - делал похожий у себя в надстройке PLEX www.planetaexcel.ru/plex/features/17/5675/

[@Bundle.Moscow]

День добрый! Подскажите как файл фида, полученый в xml конвертировать в xlsx?

[@tolkishto]

🎉

[@aleksiyvtoriy1004]

Очередной восторг от магии Николая

[@ivansidorov840]

👍👍👍👍👍

[@ЛойсоБрузница]

Есть минус для заполнения в PQ первого столбца "вниз". Могут быть пропуски уровней в нумерации, тогда структура данных будет нарушена.

[@ramanloseu6740]

Как бы прогресс ни шагал по планете Excel - кот и муж находятся на диване ! Осталось еше незыблемое в этом мире )

[@yuliyab167]

Здравствуйте, подскажите как быть, вчера купили новый МФУ, всё установили, а когда начали печатать документ в Эксель, то обнаружила, что на самом листе распечатались верхние и боковые Ячейки, помогите пожалуйста как правильно настроить область печати, чтоб распечатывался только сам документ без всего лишнего

[@planetaexcel]

Выделить нужный диапазон - вкладка Разметка страницы - Область печати - Задать

[@storkmany]

А как сделать, чтоб список сворачивался по пунктам?

[@mrbit4893]

А как сделать что бы на первом листе скажем вводим данные как фио, дата рождения, номер, и это все копируется в текст ниже на 5 листов скажем? Что бы не копиравать каждый раз. Курсовая скажем на 30 листов...

[@ВикторияВикторовна-н3ы]

Я бы формулой определила уровень так: (длина строки+1)/2

[@Dmb2007]

Имхо, если один уз уровней нумерации достигнет двузначной цифры - всё поедет. Нет? Мне кажется, нужно всё-таки считать разделители.

[@Rice0987]

Да уже не протягивайте, а пользуйтесь динамикой. :)

[@planetaexcel]

Я бы с радостью, но 95% сейчас (по моим ощущениям) всё ещё на версиях без ДМ :(

[@Rice0987]

@@planetaexcel А... Ясно. Я тоже до упора сидел на семёрке на своём старичке, а не так давно пересел на 10ку, радости нет предела. :)

[@timpro8110]

@@Rice0987 а я бы наоборот, на 7 вернулся, если бы её поддерживали, так как 10 портит и размывает шрифты во многих окнах и никака защита от размытия это не лечит, потому что это врождённый дефект 10 и её создателей.

[@Rice0987]

@@timpro8110 мне, наверное, повезло, у меня шрифты не размытые.

[@TxHJ5sez3mp]

Не понял - почему в PQ не заполнили вниз второй уровень.

[@planetaexcel]

Потому что тогда заполнятся лишние ячейки - посмотрите внимательнее. С заполнением второго уровня нужно шаманить - добавлять столбец с проверкой на окончание комнаты и начало новой и только потом заполнять по нему.

[@jwlry6913]

Всё классно за исключением того, что в реальных сметах несколько тысяч позиций, набранных слепыми умалишёнными неандертальцами, вследствие чего количество точек в шифре статьи не показывает примерно ничего. Даже если бы каким-то чудом и показывало, то смысла в этом нет, т.к. где-то однотипные работы будут оказываться на 3-м уровне, а где-то на 7-м. Например: Парковка>Полы>Стяжка и КорпусА>Западное крыло>Офисы>Внутренняя отделка>Полы>Тип1>Стяжка Потому что строителям плевать на программистские загоны

[@planetaexcel]

Боюсь, что это уже не ко мне :)

[@ЕгорФ-р7ц]

Научите меня писать макросы !!!