Сегодня хочу поделиться с Вами способом автоматизированного создания многоуровневой нумерации в excel.
Как сделать нумерацию как в столбце B на основании информации в столбце A?
Сейчас разберем подробно.
Данная задача решается путем создания четырех условий:
В ячейку B2 ставим 1, в ячейку B3 прописываем формулу на основании функции ЕСЛИ
=ЕСЛИ (A3>A2; B2&".1"; ЕСЛИ (A3=A2; (ЛЕВСИМВ (B2; ПОИСК ("@"; ПОДСТАВИТЬ (B2;".";"@"; ДЛСТР(B2)- ДЛСТР (ПОДСТАВИТЬ(B2;".";""))))) & ПРАВСИМВ (B2; ДЛСТР(B2)- ПОИСК ("@";ПОДСТАВИТЬ (B2;".";"@"; ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2;".";"")))))+1);
ЕСЛИ (И(A3<A2;A3<>1); ЛЕВСИМВ (ПРОСМОТРX (A3;$A$2:A2;$B$2:B2;0;0;-1); ПОИСК("@"; ПОДСТАВИТЬ( ПРОСМОТРX (A3;$A$2:A2;$B$2:B2;0;0;-1);".";"@"; A3-1);1)) & ПРАВСИМВ (ПРОСМОТРX (A3;$A$2:A2;$B$2:B2;0;0;-1); ДЛСТР (ПРОСМОТРX (A3;$A$2:A2;$B$2:B2;0;0;-1)) - ПОИСК ("@"; ПОДСТАВИТЬ (ПРОСМОТРX (A3;$A$2:A2;$B$2:B2;0;0;-1); ".";"@";A3-1);1))+1; ЕСЛИ(A3=1;СЧЁТЕСЛИ($A$2:A3;1)))))
Для тех, кто хочет разобрать данную формулу подробно дальнейший текст.
Первое [логическое выражение] - A3>A2, [значение если истина] - B2&".1"
тут все просто, если уровень увеличивается на 1, нам нужно приклеить к значению из предыдущей ячейки ".1", делаем это с помощью символа &
Второе [логическое условие] - A3=A2, [значение если истина] - (ЛЕВСИМВ(B2; ПОИСК ("@"; ПОДСТАВИТЬ(B2;".";"@";ДЛСТР(B2)- ДЛСТР(ПОДСТАВИТЬ (B2;".";""))))) & ПРАВСИМВ(B2; ДЛСТР(B2)- ПОИСК("@";ПОДСТАВИТЬ(B2;".";"@"; ДЛСТР(B2)- ДЛСТР(ПОДСТАВИТЬ(B2;".";"")))))+1)
если уровень в текущей строке такой же как в предыдущей, нам нужно увеличить на 1 значение предыдущей строчки, если быть точным, то значение, которое указано после последней точки в ячейке
для того, чтобы это реализовать, необходима формула, которая будет отрезать от значения ячейки левую часть включая последнюю точку, отрезать правую часть до последней точки, прибавлять 1, после чего склеивать левую и правую часть, основная сложность в данном случае связана с невозможностью посчитать количество символов в ячейке до определенного знака (нам необходима ".") начиная справа, поэтому используем замену последней точки на символ "@", после чего считаем его порядковый номер
Третье [логическое условие] - И(A3<A2;A3<>1), [значение если истина] - ЛЕВСИМВ(ПРОСМОТРX(A3;$A$2:A2;$B$2:B2;0;0;-1);ПОИСК("@";ПОДСТАВИТЬ(ПРОСМОТРX(A3;$A$2:A2;$B$2:B2;0;0;-1);".";"@";A3-1);1))& ПРАВСИМВ (ПРОСМОТРX (A3;$A$2:A2;$B$2:B2;0;0;-1);ДЛСТР(ПРОСМОТРX(A3;$A$2:A2;$B$2:B2;0;0;-1))-ПОИСК("@";ПОДСТАВИТЬ(ПРОСМОТРX(A3;$A$2:A2;$B$2:B2;0;0;-1);".";"@";A3-1);1))+1
если уровень в текущей строке меньше, чем в предыдущей, и это не уровень 1, то в данном случае используем логику как в условии номер 2, только на первом шаге, мы находим в диапазоне выше текущей ячейки последнюю ячейку такого же уровня, а уже затем производим манипуляции как в пункте 2.
Четвертое [логическое условие] - A3=1, [значение если истина] - СЧЁТЕСЛИ($A$2:A3;1))
если уровень равен 1, то просто считаем кол-во значений первого уровня в диапазоне до текущей строчки.
Для того, чтобы сделать у многоуровневой нумерации смещение вправо как показано на первом рисунке в столбце C достаточно добавить простую формулу в ячейку C2, которая и будет задавать сдвиг вправо в соответствии с номером уровня.
=ЕСЛИМН(A2=1;B2;A2=2;" "&B2;A2=3;" "&B2;A2=4;" "&B2;A2=5;" "&B2)
Вот пожалуй и все, надеюсь будет полезно.