Разделение многоуровневного списка по столбцам
В моей практике весьма часто приходится сталкиваться с ситуацией, когда мне в руки попадает большой многоуровневый список с вложенной нумерацией:
Это могут быть сметы с их бесконечными пунктами-подпунктами работ и материалов, списки задач-подзадач по проектам, бухгалтерские выгрузки статей-подстатей из 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) пару дополнительных шагов, а именно:
- Добавить столбец индекса на вкладке Добавление столбца - Столбец индекса (Add column - Index column).
- Перенести его за шапку в начало таблицы
Способ 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
Подсчитать сколько разделителей через List.Count, добавить перед данными ";" и разделить полученный столбец на части
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "combine", each Text.Repeat(";",List.Count(Text.SplitAny([Номер],".,_"))-1)&[Данные]), #"Разделить столбец по разделителю" = Table.SplitColumn(#"Добавлен пользовательский объект", "combine", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"combine.1", "combine.2", "combine.3"}),Я, к сожалению, небольшой спец в Excel, но пытаюсь понять, где можно применить в работах предложенное разнесение по столбцам?
Прошу не винить меня строго ни автора статьи, ни комментаторов.
Весь смысл этой статьи и видео как раз в том, чтобы имея первый столбец с многоуровневой нумерацией разложить соседний справа столбец на несколько колонок по соответствующим уровням. Без первого столбца это выполнить в принципе невозможно.
Посмотрите еще раз внимательно видео от начала и до конца - должно помочь