Многоуровневый список по столбцам в Excel
Вставка
- Опубліковано 28 вер 2024
- Три способа (формулы, Power Query и макрос) чтобы разложить многоуровневый список с вложенной нумерацией по отдельным столбцам.
Скачать пример www.planetaexc...
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Подпишитесь на наш канал в Telegram, чтобы оперативно узнавать о всех новых возможностях и лайфхаках при работе в Excel и Power BI - t.me/planetaexcel
Самый полезный канал по работе с Excel. Техника подачи материала супер и не меняется уже годами.
Очень полезное видео. Всегда жду новых выпусков, снимите пожалуйста больше роликов про PQ, потому что PQ для многих в частности для меня самого это тёмная лошадка в которой знаю только несколько элементарных функций но не более.
Особенно порадовали муж с котом на диване 😂 Спасибо за видео, всегда очень интересно и полезно
Вижу ролик Николая - ставлю лайк. Спасибо вам!
сколько раз выручали Ваши видео, использовала для работы, спасибо огромное!
Николай огромное спасибо как всегда Вы на высоте!
Спасибо! По BI бы ещё побольше уроков.
Николай, спасибо за всегда полезный контент. Запишите пожалуйста видео по созданию, редактированию xml прайсов и схем в экселе.
О! Спасибище! Эту функцию неделю искал и вот оно!
Николай! Преклоняю голову... Спасибо Вам за ваш труд!)
Очень полезный и нужный контент! Действительно, всё просто - когда знаешь...)
🤝
Спасибо большое!!!
Спасибо за выпуск
Николай, где можно поподробнее ознакомиться со списком команд и их описанием в PowetQuery?
Из 1с когда выгружается список , вот такие уровни только без нумераций . Есть решение преобразовать такой список для сводной таблицы ? Ну чтоб все строки были заполнены ?
Не показали случай где подуровни в разных столбцах. Расскажите про GPT помощника... Какой выбрать...
Интересно спасибо! 🙏
А как можно решить такое же задание если начинать с такой структурой:
| id | name | parent-id |
|-------|--------------|-----------|
| 100000| house | root |
| 101000| kitchen | 100000 |
| 101010| table | 101000 |
| 102000| bathroom | 100000 |
| 102010| toilettpaper| 102000 |
благодарен за каждый подсказ! 🙏
222 подписчиков! Красивая цифра! Пусть будет min 2 222 222!!!🤲🤲🤲
Спасибо за урок.
Часто работаю с строительными сметами и иногда приходится так делать.
Был бы рад если подскажите как поставить быстро функцию агрегат на первом вхождении и по нему сделать группировку.
Очередной восторг от магии Николая
12:35 Николай, как сделать Сводную таблицу, чтобы в верхнем уровне 1 показывал список позиций всех нижних уровней 3 и суммирование или выбор поздней даты? Сделайте видео, если не сложно. Спасибо.
День добрый! Подскажите как файл фида, полученый в xml конвертировать в xlsx?
Подписался. Вопрос. В ячейке листа две цифры через слеш (наклонная черта). Можно через формулу изменять эти цифры в другой лист? Во втором листе так-же чтобы цифры были через слеш
имхо макросы самый лучший. я их обажаю!
🔥🔥🔥
спасибо, сэр
❤❤❤
А как сделать что бы на первом листе скажем вводим данные как фио, дата рождения, номер, и это все копируется в текст ниже на 5 листов скажем? Что бы не копиравать каждый раз. Курсовая скажем на 30 листов...
спасибо
🎉
Здравствуйте, подскажите как быть, вчера купили новый МФУ, всё установили, а когда начали печатать документ в Эксель, то обнаружила, что на самом листе распечатались верхние и боковые Ячейки, помогите пожалуйста как правильно настроить область печати, чтоб распечатывался только сам документ без всего лишнего
Выделить нужный диапазон - вкладка Разметка страницы - Область печати - Задать
А как сделать, чтоб список сворачивался по пунктам?
Имхо, если один уз уровней нумерации достигнет двузначной цифры - всё поедет. Нет?
Мне кажется, нужно всё-таки считать разделители.
Как бы прогресс ни шагал по планете Excel - кот и муж находятся на диване ! Осталось еше незыблемое в этом мире )
👍👍👍👍👍
Да уже не протягивайте, а пользуйтесь динамикой. :)
Я бы с радостью, но 95% сейчас (по моим ощущениям) всё ещё на версиях без ДМ :(
@@planetaexcel А... Ясно.
Я тоже до упора сидел на семёрке на своём старичке, а не так давно пересел на 10ку, радости нет предела. :)
@@Rice0987 а я бы наоборот, на 7 вернулся, если бы её поддерживали, так как 10 портит и размывает шрифты во многих окнах и никака защита от размытия это не лечит, потому что это врождённый дефект 10 и её создателей.
@@timpro8110 мне, наверное, повезло, у меня шрифты не размытые.
Творец чудес!🎉🎉🎉 Thank you👏👏👏👏👏👏👏👏👏👏👏👏👏🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥👍👍👍
Спасибо, Николай, за очень полезные советы. Всегда жду и смотрю с интересом Ваши уроки. Всех благ Вам!
Во истину, "Когда знаешь - всё просто"!!!
Благодарю, что делитесь своими знаниями, Николай!
"Воистину" - теперь Вы и это знаете.
Можно использовать формулы динамический массивов, тогда всё решение поместится в одну формулу, без протягивания и обновления формул:
=LET(
Level, LEN(Table4[Номер])-LEN(SUBSTITUTE(Table4[Номер],".",""))+1,
cLevel, TRANSPOSE(SORT(UNIQUE(Level))),
VSTACK(cLevel, IF(Level=cLevel, Table4[Данные], "" )))
Ждем видео с уроками по Power BI
Поддерживаю! Тоже жду с нетерпением.
Спасибо, интересно. Но Ваш метод будет работать только при маленьких списках, количество значений в каждом уровне которых не более 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 подпунктов в каком-то пункте.
Ставлю лайк и смотрю!
А надо - наоборот!
Лайки до просмотра ЮТ воспринимает, как накрутку, т.ч., это - медвежья услуга...
Для Power Query, если вы не уверены, какие именно символы будут использованы в нумерации строк, чтобы их потом вручную не добавлять, можно использовать эту формулу - Text.Length( Text.Select( [Номер] , {"0".."9"} ) )
Отличная мысль, спасибо! :)
Полезное видео, но как правило из 1С таблицы выгружаются без столбца с нумерацией. Хотелось бы узнать как в таком случае таблицу перевести в плоский вид
Здравствуйте. Спасибо за Ваш труд и видео. А как на счёт того, чтобы сравнить таблицы 2-х групп, в процентном соотношении, т.е. какой процент соответствие или процент отличие в двух группах? В интернете нигде не нашел. Имеется только в одном видео от XLTOOLS, но там требуется установка приложение, а приложение не установить, видимо данные устарели?! Не могли бы показать как это делается.
Есть минус для заполнения в PQ первого столбца "вниз". Могут быть пропуски уровней в нумерации, тогда структура данных будет нарушена.
Николай, спасибо за пример.
А как добавить в в макрос еще такую функцию, чтобы убрать пустые строки в каждом уровне, чтобы на основе этой таблицы можно было делать сводные таблицы.
Это как раз таки очень утобно для строительных смет.
Я имею в виду, чтобы ввесть первый уровень Заполнился словом "Прихожая" до слова Кухня, Кухня до Зала и т.д.
Добрый день,👋 спасибо за ваши обучающие материалы.
Хотел бы задать вопрос, по автоматическому заполнению шаблона документа в Excel.
Я работаю в гостинице и каждое утро делаю выгрузку Excel таблицы клиентов, которые должны приехать в гостиницу для зачисления. Все клиенты должны заплатить однотипный документ "Карточку регистрации" в котором указано ФИО и другие данные из Excel таблицы выгрузки.
Вопрос: как автоматически перенести данные из Excel выгрузки в однотипный шаблон?
-Функция "Слияние " в Word-e мне не подходит тк на одном листе А4 нужно разместить две "Карточки регистрации" двух разных клиентов. Даже в разделе "этикетки" в Word-e нет возможности расположить две этикетки на А4. У нас две "карточки регистрации" печатаются на одином листе А4, затем мы их рвем пополам и каждому клиенту даем на подпись свой экземпляр.
-Получается лучше всего сделать шаблон в Excel-е, но как перенести данные из таблицы выгрузки в шаблон?
Заранее спасибо 😀👍
Всё классно за исключением того, что в реальных сметах несколько тысяч позиций, набранных слепыми умалишёнными неандертальцами, вследствие чего количество точек в шифре статьи не показывает примерно ничего. Даже если бы каким-то чудом и показывало, то смысла в этом нет, т.к. где-то однотипные работы будут оказываться на 3-м уровне, а где-то на 7-м. Например:
Парковка>Полы>Стяжка и
КорпусА>Западное крыло>Офисы>Внутренняя отделка>Полы>Тип1>Стяжка
Потому что строителям плевать на программистские загоны
Боюсь, что это уже не ко мне :)
Я бы формулой определила уровень так: (длина строки+1)/2
3:54 Николай, когда Вы нажимаете F4, над номером ячейки выводится значение этой ячейки. Это Вы монтируете для видео или это при обычной работе у Вас тоже выводится? Если второе, то расскажите, пожалуйста, как это сделать. Про F9 знаю, но это не так удобно как подсказки.
Это фишка последних обновлений Office 365 :)
@@planetaexcel Проверил у себя, не выводит. Может где-то нужно включить это? У меня подписка 365 домашняя. Версия Microsoft Excel для Microsoft 365 MSO (16.0.12527.22286), 64-разрядная версия. При попытке обновить, отвечает, что у меня последняя версия. Лицензия честно оплачивается через Черногорию.
Оказывается офис не обновляется на windows 7. Либо переустанавливать офис, либо устанавливать 10+
Два кота - один с мужем, второй в цветке)
И где же наша полюбившиеся рубрика - с помощью нейронки)
А унутре у неё - неонка (зачеркнуто) нейронка! :)
Как всегда круто!👍
Upstairs Level 🔝
Не понял - почему в PQ не заполнили вниз второй уровень.
Потому что тогда заполнятся лишние ячейки - посмотрите внимательнее.
С заполнением второго уровня нужно шаманить - добавлять столбец с проверкой на окончание комнаты и начало новой и только потом заполнять по нему.