Многоуровневый список по столбцам в Excel
ฝัง
- เผยแพร่เมื่อ 12 มี.ค. 2024
- Три способа (формулы, Power Query и макрос) чтобы разложить многоуровневый список с вложенной нумерацией по отдельным столбцам.
Скачать пример www.planetaexcel.ru/technique...
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Подпишитесь на наш канал в Telegram, чтобы оперативно узнавать о всех новых возможностях и лайфхаках при работе в Excel и Power BI - t.me/planetaexcel
![หมานแต่ผัวเขา - แสตมป์ โตเกียวมิวสิค [ OFFICIAL MUSIC VIDEO ]](http://i.ytimg.com/vi/m0g1TSBJPgo/mqdefault.jpg)
![เบิ้ล ปทุมราช - อ้ายกินดอง น้องลาบงัว ft. สแน็ก อัจฉรีย์ [OFFICIAL MV]](http://i.ytimg.com/vi/-OK--KaSPcI/mqdefault.jpg)
Очень полезное видео. Всегда жду новых выпусков, снимите пожалуйста больше роликов про PQ, потому что PQ для многих в частности для меня самого это тёмная лошадка в которой знаю только несколько элементарных функций но не более.
Николай! Преклоняю голову... Спасибо Вам за ваш труд!)
Очень полезный и нужный контент! Действительно, всё просто - когда знаешь...)
🤝
Особенно порадовали муж с котом на диване 😂 Спасибо за видео, всегда очень интересно и полезно
Самый полезный канал по работе с Excel. Техника подачи материала супер и не меняется уже годами.
Во истину, "Когда знаешь - всё просто"!!!
Благодарю, что делитесь своими знаниями, Николай!
"Воистину" - теперь Вы и это знаете.
Творец чудес!🎉🎉🎉 Thank you👏👏👏👏👏👏👏👏👏👏👏👏👏🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥👍👍👍
Спасибо, Николай, за очень полезные советы. Всегда жду и смотрю с интересом Ваши уроки. Всех благ Вам!
Вижу ролик Николая - ставлю лайк. Спасибо вам!
Ставлю лайк и смотрю!
А надо - наоборот!
Лайки до просмотра ЮТ воспринимает, как накрутку, т.ч., это - медвежья услуга...
сколько раз выручали Ваши видео, использовала для работы, спасибо огромное!
Спасибо! По BI бы ещё побольше уроков.
Николай огромное спасибо как всегда Вы на высоте!
Николай, спасибо за всегда полезный контент. Запишите пожалуйста видео по созданию, редактированию xml прайсов и схем в экселе.
Как всегда круто!👍
О! Спасибище! Эту функцию неделю искал и вот оно!
Спасибо за выпуск
Ждем видео с уроками по Power BI
Поддерживаю! Тоже жду с нетерпением.
Спасибо большое!!!
Можно использовать формулы динамический массивов, тогда всё решение поместится в одну формулу, без протягивания и обновления формул:
=LET(
Level, LEN(Table4[Номер])-LEN(SUBSTITUTE(Table4[Номер],".",""))+1,
cLevel, TRANSPOSE(SORT(UNIQUE(Level))),
VSTACK(cLevel, IF(Level=cLevel, Table4[Данные], "" )))
спасибо
Upstairs Level 🔝
спасибо, сэр
222 подписчиков! Красивая цифра! Пусть будет min 2 222 222!!!🤲🤲🤲
🔥🔥🔥
❤❤❤
Два кота - один с мужем, второй в цветке)
И где же наша полюбившиеся рубрика - с помощью нейронки)
А унутре у неё - неонка (зачеркнуто) нейронка! :)
Для Power Query, если вы не уверены, какие именно символы будут использованы в нумерации строк, чтобы их потом вручную не добавлять, можно использовать эту формулу - Text.Length( Text.Select( [Номер] , {"0".."9"} ) )
Отличная мысль, спасибо! :)
Очередной восторг от магии Николая
👍👍👍👍👍
Николай, спасибо за пример.
А как добавить в в макрос еще такую функцию, чтобы убрать пустые строки в каждом уровне, чтобы на основе этой таблицы можно было делать сводные таблицы.
Это как раз таки очень утобно для строительных смет.
Я имею в виду, чтобы ввесть первый уровень Заполнился словом "Прихожая" до слова Кухня, Кухня до Зала и т.д.
Интересно спасибо! 🙏
А как можно решить такое же задание если начинать с такой структурой:
| id | name | parent-id |
|-------|--------------|-----------|
| 100000| house | root |
| 101000| kitchen | 100000 |
| 101010| table | 101000 |
| 102000| bathroom | 100000 |
| 102010| toilettpaper| 102000 |
благодарен за каждый подсказ! 🙏
Спасибо за урок.
Часто работаю с строительными сметами и иногда приходится так делать.
Был бы рад если подскажите как поставить быстро функцию агрегат на первом вхождении и по нему сделать группировку.
Не показали случай где подуровни в разных столбцах. Расскажите про GPT помощника... Какой выбрать...
Николай, где можно поподробнее ознакомиться со списком команд и их описанием в PowetQuery?
имхо макросы самый лучший. я их обажаю!
Здравствуйте. Спасибо за Ваш труд и видео. А как на счёт того, чтобы сравнить таблицы 2-х групп, в процентном соотношении, т.е. какой процент соответствие или процент отличие в двух группах? В интернете нигде не нашел. Имеется только в одном видео от XLTOOLS, но там требуется установка приложение, а приложение не установить, видимо данные устарели?! Не могли бы показать как это делается.
12:35 Николай, как сделать Сводную таблицу, чтобы в верхнем уровне 1 показывал список позиций всех нижних уровней 3 и суммирование или выбор поздней даты? Сделайте видео, если не сложно. Спасибо.
Добрый день,👋 спасибо за ваши обучающие материалы.
Хотел бы задать вопрос, по автоматическому заполнению шаблона документа в Excel.
Я работаю в гостинице и каждое утро делаю выгрузку Excel таблицы клиентов, которые должны приехать в гостиницу для зачисления. Все клиенты должны заплатить однотипный документ "Карточку регистрации" в котором указано ФИО и другие данные из Excel таблицы выгрузки.
Вопрос: как автоматически перенести данные из Excel выгрузки в однотипный шаблон?
-Функция "Слияние " в Word-e мне не подходит тк на одном листе А4 нужно разместить две "Карточки регистрации" двух разных клиентов. Даже в разделе "этикетки" в Word-e нет возможности расположить две этикетки на А4. У нас две "карточки регистрации" печатаются на одином листе А4, затем мы их рвем пополам и каждому клиенту даем на подпись свой экземпляр.
-Получается лучше всего сделать шаблон в Excel-е, но как перенести данные из таблицы выгрузки в шаблон?
Заранее спасибо 😀👍
Подписался. Вопрос. В ячейке листа две цифры через слеш (наклонная черта). Можно через формулу изменять эти цифры в другой лист? Во втором листе так-же чтобы цифры были через слеш
Как бы прогресс ни шагал по планете Excel - кот и муж находятся на диване ! Осталось еше незыблемое в этом мире )
Есть минус для заполнения в PQ первого столбца "вниз". Могут быть пропуски уровней в нумерации, тогда структура данных будет нарушена.
День добрый! Подскажите как файл фида, полученый в xml конвертировать в xlsx?
Из 1с когда выгружается список , вот такие уровни только без нумераций . Есть решение преобразовать такой список для сводной таблицы ? Ну чтоб все строки были заполнены ?
Спасибо, интересно. Но Ваш метод будет работать только при маленьких списках, количество значений в каждом уровне которых не более 9. Как только уровень станет 10 и более, то подсчет сломается. Пример: с 1.1 по 1.9 - будут во втором столбце, а вот 1.10 до 1.99 уже в третьем, т.к. Вы считаете количество цифр, а правильней будет считать количество разделителей плюс 1.
Согласен, но больше 9 уровней вложенности - это редкость. Или диагноз :)
@@planetaexcel если именно глубина вложенности, то конечно это редкость. или диагноз :), но речь не о глубине. Просто расширьте Ваш список например "1. Прихожая" до 10 пунктов: "1.1 Гардероб, 1.2 Зеркало, 1.3 Ящик, 1.4 Цветок, 1.5 ...... 1.10 стул" и на 1.10 метод уже будет не рабочий. 1.10 и последующие улетят в третью колонку :)
@@glockeduard тогда в этом случае можно использовать формулу - Text.Length( Text.Remove([Номер], {"0".."9"}))+1
Подождите-ка, мы же считаем не длину строки, а разницу между длинами строк с точками и без. Значит условные 10 как были двумя символами в исходной ячейке, так они и останутся двумя символами после замены точек, а значит разница не исказится, будь там хоть 5 символов.
@@glockeduard, берём, например, любую рабочую инструкцию на предприятии по СМК, и я уверен, что там будет больше 9 подпунктов в каком-то пункте.
А как сделать что бы на первом листе скажем вводим данные как фио, дата рождения, номер, и это все копируется в текст ниже на 5 листов скажем? Что бы не копиравать каждый раз. Курсовая скажем на 30 листов...
А как сделать, чтоб список сворачивался по пунктам?
Я бы формулой определила уровень так: (длина строки+1)/2
Здравствуйте, подскажите как быть, вчера купили новый МФУ, всё установили, а когда начали печатать документ в Эксель, то обнаружила, что на самом листе распечатались верхние и боковые Ячейки, помогите пожалуйста как правильно настроить область печати, чтоб распечатывался только сам документ без всего лишнего
Выделить нужный диапазон - вкладка Разметка страницы - Область печати - Задать
3:54 Николай, когда Вы нажимаете F4, над номером ячейки выводится значение этой ячейки. Это Вы монтируете для видео или это при обычной работе у Вас тоже выводится? Если второе, то расскажите, пожалуйста, как это сделать. Про F9 знаю, но это не так удобно как подсказки.
Это фишка последних обновлений Office 365 :)
@@planetaexcel Проверил у себя, не выводит. Может где-то нужно включить это? У меня подписка 365 домашняя. Версия Microsoft Excel для Microsoft 365 MSO (16.0.12527.22286), 64-разрядная версия. При попытке обновить, отвечает, что у меня последняя версия. Лицензия честно оплачивается через Черногорию.
Оказывается офис не обновляется на windows 7. Либо переустанавливать офис, либо устанавливать 10+
Имхо, если один уз уровней нумерации достигнет двузначной цифры - всё поедет. Нет?
Мне кажется, нужно всё-таки считать разделители.
Да уже не протягивайте, а пользуйтесь динамикой. :)
Я бы с радостью, но 95% сейчас (по моим ощущениям) всё ещё на версиях без ДМ :(
@@planetaexcel А... Ясно.
Я тоже до упора сидел на семёрке на своём старичке, а не так давно пересел на 10ку, радости нет предела. :)
Не понял - почему в PQ не заполнили вниз второй уровень.
Потому что тогда заполнятся лишние ячейки - посмотрите внимательнее.
С заполнением второго уровня нужно шаманить - добавлять столбец с проверкой на окончание комнаты и начало новой и только потом заполнять по нему.
Всё классно за исключением того, что в реальных сметах несколько тысяч позиций, набранных слепыми умалишёнными неандертальцами, вследствие чего количество точек в шифре статьи не показывает примерно ничего. Даже если бы каким-то чудом и показывало, то смысла в этом нет, т.к. где-то однотипные работы будут оказываться на 3-м уровне, а где-то на 7-м. Например:
Парковка>Полы>Стяжка и
КорпусА>Западное крыло>Офисы>Внутренняя отделка>Полы>Тип1>Стяжка
Потому что строителям плевать на программистские загоны
Боюсь, что это уже не ко мне :)