Автонумерация внутри составной записи в Excel

Автозаполнение в в Excel относится к базовому функционалу ввода различных типов данных, который следует изучить в первую очередь. Благо делается это довольно просто, в большинстве случаев, все сводится к выделению части данных и продолжению ряда с помощью маркера автозаполнения.

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

Естественно, числовая часть, которая должна изменяться в таком коде, не обязательно должна быть одна, равно, как и инкремент может быть не 1, а 2, 3 и т.д.

Решение

Основу решения составит функция ЧСТРОК, в английской версии ROWS, которая подсчитывает количество строк в диапазоне. Если первую часть аргумента функции зафиксировать абсолютной ссылкой, а вторую не фиксировать, то получим динамический диапазон, который будет изменяться с растягиванием формулы.

В простейшем случае, как изображено на рисунке выше, мы получаем стандартный ряд из автозаполнения. Теперь нужно преобразовать данный ряд в номер, когда в начале идут нули. Стандартное изменение типа данных в ячейке здесь мало поможет, зато функция ТЕКС (TEXT в английской версии) придется как нельзя кстати.

Функция ТЕКС – преобразует число в текст с заданным форматированием, в нашем случае мы зададим формат так, чтобы число всегда занимало 4 символа, т.е 1 будет 0001, а 121 – 0121 и т.п.

В итоге средняя часть (или части) изменяемого текста будет рассчитываться следующим образом (если число состоит из 4х цифр):

=ТЕКСТ(ЧСТРОК($C$4:C4);"0000")

На последнем этапе остается только склеить текстовые части воедино, делается это с помощью функции (СЦЕП или CONCAT в английской версии), однако, в данном случае возможно обойтись и простым знаком амперсанта (&).

Итак, для примера1 формула:

="АА-"&ТЕКСТ(ЧСТРОК($A$4:A4);"0000")&"-B13"

Для примера 2 в первой части не забываем сделать формат для трех цифр, а вторую часть умножим на 2, чтобы инкремент была не единица, а двойка:

="ABC"&ТЕКСТ(ЧСТРОК($B$4:B4);"000")&"KU"&ТЕКСТ(ЧСТРОК($B$4:B4)*2;"0000")&"12L"

Файл с примером, с возможностью скачивания

Функции, которые использовались для решения задачи

ЧСТРОК (ROWS)

ТЕКСТ (TEXT)