Приветствуем, дорогие читатели! Сегодня разберем проблему, с которой сталкивался почти каждый, кто работает с Excel: как превратить многоуровневый список с вложенной нумерацией в удобную таблицу по столбцам.
Представьте: вы получаете смету с пунктами 1, 1.1, 1.1.1, 1.2, бухгалтерскую выгрузку из 1С или список задач проекта. Всё это — один столбец, где глубина вложенности показывает иерархию. Сразу хочется разложить эти данные по уровням — и это правильное желание!
Готового инструмента в Excel нет, но мы с вами знаем: если чего-то нет — мы это создадим. Рассмотрим два подхода — через формулы и через Power Query.
📊 Способ 1: Формулы Excel
Шаг 1: Определяем уровень (Порядок)
Ключевая идея — посчитать количество точек в нумерации. Чем больше точек, тем глубже уровень.
Формула определения уровня:
=ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;".";""))+1
Как это работает:
- ПОДСТАВИТЬ удаляет все точки
- ДЛСТР считает длину строки
- Разница в длине +1 = количество уровней
Шаг 2: Раскладываем по столбцам
Добавляем заголовки столбцов уровней: 1, 2, 3...
Формула для извлечения названия на нужном уровне:
Уровень 1: =ЕСЛИ($C2=D$1;$B2;"")
Уровень 2: =ЕСЛИ($C2=E$1;$B2;"")
Уровень 3: =ЕСЛИ($C2=F$1;$B2;"")
Важно: не забывайте про знаки доллара для фиксации ссылок!
⚡ Способ 2: Power Query
Шаг 1: Загружаем данные
- Преобразуйте диапазон в «умную таблицу» (Ctrl+T)
- Загрузите в Power Query: Данные → Из таблицы/диапазона
- Убедитесь, что столбец с нумерацией имеет текстовый тип
Шаг 2: Вычисляем уровень
Добавляем настраиваемый столбец во вкладке "Добавление столбца".
Вводим имя нового столбца и формулу (чтобы оп аналогии убрать все точки и посчитать уровни):
=Text.Length([Нумерация]) - Text.Length(Text.Remove([Нумерация], ".")) + 1
Шаг 3: Раскладываем по столбцам
- Выделяем столбец с уровнями
- Преобразование → Столбец сведения
- Выбираем столбец с данными для значений
- Важно: в расширенных параметрах отключаем агрегацию
Получаем следующий вид:
Далее закрываем и загружаем в Excel:
📌 Что выбрать?
Формулы подходят, если:
- Нужно быстрое разовое решение
- Вы любите "покопаться" в формулах
- Требуется гибкость в отдельных случаях
Power Query лучше, если:
- Данные обновляются регулярно
- Нужна автоматизация (обновил — получил результат)
- Работаете с большими объемами
- Хотите повторяемый процесс
💡 Главный совет
Начинайте с Power Query — это современный подход, который экономит время в долгосрочной перспективе. Однажды настроив запрос, вы получаете инструмент на все будущие аналогичные задачи.
А какой способ используете вы? Сталкивались ли с подобными задачами? Делитесь в комментариях — обсудим нюансы и ваши кейсы!
Хотите больше лайфхаков по Excel? Ставьте лайк и подписывайтесь, впереди ещё больше полезных и интересных лайфхаков!