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

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

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

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

Смотрите также видеоверсию статьи «Автонумерация внутри составной записи в 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"

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

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

- Advertisment -

Встречаем новый Excel, Microsoft представила динамические массивы (Dynamic Array Functions)

24 сентября 2018 года Microsoft представила одно из самых существенных изменений в Excel за последнее десятилетие - Dynamic Array...

Горизонтальная сортировка в Excel

В большинстве случаев работы с данными они организованны вертикально, поэтому когда подразумевается обработка имеется ввиду именно вертикальная обработка данных,...

Создание динамической диаграммы с отображением максимального и минимального значений

В Excel построение простых, не динамических диаграмм, процедура не из сложных, достаточно определиться с правильным типом графика, выделить данные...

Еще записей в тему?
Если честно, некоторые могут быть не свежие:)