Разделение многоуровневного списка по столбцам

В моей практике весьма часто приходится сталкиваться с ситуацией, когда мне в руки попадает большой многоуровневый список с вложенной нумерацией:

Это могут быть сметы с их бесконечными пунктами-подпунктами работ и материалов, списки задач-подзадач по проектам, бухгалтерские выгрузки статей-подстатей из 1С и т.д. - вариантов миллион. И не знаю, как вам, а мне подобные списки сразу хочется разложить на столбцы по уровням нумерации. Готовых встроенных инструментов в Excel для такого нет, но когда это нас с вами останавливало? :)

Давайте рассмотрим несколько способов реализовать подобную трансформацию в Microsoft Excel.

Способ 1. Формулы

Для начала давайте выясним, на каком уровне (1,2,3...) находится каждый элемент. С ходу видно, что это можно вычислить по количеству точек в нумерации. В подобных случаях, когда вам нужно определить сколько именно раз какой-то символ входит в исходный текст, есть простой рецепт: заменить все интересующие символы (точки в нашем случае) на пустоту (т.е. удалить) и сравнить длину получившегося урезанного текста с длиной исходного. Разница в длине и будет количеством вхождений.

Реализовать эту логику можно следующей формулой:

Здесь функция ПОДСТАВИТЬ (SUBSTITUTE) заменяет точки на пустоту, а функция ДЛСТР (LEN) вычисляет длину текста.

Теперь введём над следующими столбцами цифры уровней 1, 2, 3 и т.д. и добавляем к нашей формуле проверку с извлечением элементов на каждый уровень:

Только не забудьте правильно закрепить ссылки на ячейки в этой формуле символом доллара, чтобы можно было скопировать её на весь диапазон.

Ну и, само собой, можно уложить всё это в одну формулу, используя классический принцип построения мегаформул в Excel, когда сначала вся логика расчёта реализуется в отдельных ячейках несколькими простыми формулами, а потом они по очереди копируются и вставляются друг в друга. Т.е. в нашем случае мы прямо в строке формул выделяем и копируем в буфер всё после знака = из ячейки С2 и затем аккуратно вставляем это вместо адреса этой же ячейки $C2 в формулу в ячейку D2 (не забыв добавить доллары где это нужно). Таким образом всё компактно собирается в одну мегаформулу, разобраться в которой, правда, может быть уже сложновато.

Способ 2. Power Query

Иногда подобную задачу бывает проще решить с помощью Power Query - мощной надстройки для импорта и преобразования данных, которая с 2016 версии уже стала неотъемлемой частью Excel.

Для начала давайте конвертируем нашу таблицу в динамическую "умную", используя сочетание клавиш Ctrl+T или команду Главная - Форматировать как таблицу (Home - Format as Table) и загрузим в Power Query - кнопкой Из таблицы/диапазона на вкладке Данные (Data - From table/range).

Важно сразу задать для столбца с номерами текстовый тип данных, т.к. далее мы будем работать с нимм именно как с строками:

Затем добавим вычисляемый столбец с уровнем для каждого элемента, используя команду Настраиваемый столбец на вкладке Добавление столбца (Add column - Custom column). 

Дальше, в принципе, можно было бы пойти по той же логике, что и в предыдущем способе - удалить все точки из нумерации и посчитать разницу в длине получившегося и исходного текста. Причем можно будет даже не заменять точку на пустоту, как мы это делали ранее функцией ПОДСТАВИТЬ, а удалить напрямую - в языке М, встроенному в Power Query для этого есть прямая функция Text.Remove. Ну, а для вычисления длины текстовой строки подойдет функция Text.Length.

В итоге наша формула могла бы выглядеть так:

Если в исходной нумерации где-то случайно вместо точки использовалась, например, запятая или подчеркивание, то можно задать сразу все эти символы как разделители - для этого во втором аргументе функции Text.Remove указывается список в фигурных скобках:

=Text.Length([Номер])-Text.Length(Text.Remove([Номер], {".", ",", "_"}))+1

Есть и другой способ, достойный упоминания - разделить нумерацию по точкам и посчитать затем количество получившихся фрагментов (плюс 1). Это можно сделать с помощью формулы:

=List.Count(Text.Split([Номер], "."))+1

Если символов-разделителей несколько, то можно использовать функцию Text.SplitAny, вторым аргументом которой и задать все разделители текстовой строкой (уже не используя список):

=List.Count(Text.SplitAny([Номер], ".,_"))+1

После этого останется выделить получившийся столбец с вычисленными уровнями и разложить элементы по значениям из этого столбца на разные колонки. Это легко сделать с помощью команды Преобразование - Столбец сведения (Transform - Pivot column):

В открывшемся окне выбираем столбец данных в качестве результата, а в расширенных параметрах не забываем отключить агрегацию (Don't aggregate) - и задача решена:

Большой плюс такого подхода в том, что при изменении исходных данных (добавлении новых строк, корректировки уровней и т.д.) достаточно будет лишь обновить наш запрос - кнопкой Обновить всё на вкладке Данные (Data - Refresh all).

Минус же в том, что если в столбце с нумерацией вдруг почему-то окажутся дубликаты, то на этих строчках наша процедура сведения даст ошибку. Подстраховаться от такого можно, добавив в наш запрос перед шагом Сведенный столбец (Pivoted column) пару дополнительных шагов, а именно:

1. Добавить столбец индекса на вкладке Добавление столбца - Столбец индекса (Add column - Index column).
2. Перенести его за шапку в начало таблицы

После выполнения свёртывания этот вспомогательный индексный столбец можно удалить.

Способ 3. Макрос на VBA

Ну и, наконец, можно решить нашу задачу с помощью коротенького макроса на Visual Basic. Чтобы добавить его в текущую книгу, нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Затем вставьте новый пустой модуль через меню Insert - Module и введите туда следующий код:

Sub Multilevel()

    dim n as Integer, cell as Range

    For Each cell In Selection
        n = UBound(Split(cell, "."))
        If n > 0 Then cell.Offset(0, 1).Resize(1, n).Insert xlShiftToRight
    Next cell

End Sub

Логика тут простая:

• Проходим в цикле For Each ... Next по выделенным ячейкам (это будут ячейки с нумерацией) и ссылку на очередную ячейку сохраняем в переменную cell.
• Разделяем полученный номер по точкам на фрагменты с помощью VBA-функции Split и помещаем их в массив.
• Подсчитываем размер получившегося массива функцией Ubound - это и будет уровень очередного элемента, который кладём в переменную n.
• Сдвигаемся на ячейку вправо относительно текущей с помощью Offiset(0,1) и выделяем с помощью метода Resize n ячеек.
• Вставляем n пустых ячеек со сдвигом вправо, отодвигая, таким образом, очередной элемент на его уровень.

Если теперь выделить ячейки с нумерацией и запустить наш макрос, используя сочетание клавиш Alt+F8 или кнопку Макросы на вкладке Разработчик (Developer - Macros), то наш список моментально разложится по столбцам-уровням.

Минус у такого подхода в том, что после запуска макросов в Excel традиционно, к сожалению, перестаёт работать отмена последнего действия (Undo). Ну, и сохранять наш файл теперь придётся в формате с поддержкой макросов, само собой.

Зато можно добавить этот код в Личную книгу макросов (вашу персональную библиотеку макросов, о которой я уже писал) - и пользоваться им потом многократно, когда это вам потребуется.

Ссылки по теме

• Использование функции ПОДСТАВИТЬ (SUBSTITUTE) в Excel
• Делим слипшийся текст на части
• Как использовать Личную книгу макросов в Excel