Блог

Главная - Вопросы по программированию - EXCEL: уникальный буквенно-цифровой код с исключенными определенными символами (без VBA / дубликатов)

EXCEL: уникальный буквенно-цифровой код с исключенными определенными символами (без VBA / дубликатов)

#excel

#excel

Вопрос:

Я пытаюсь создать список = 5 буквенно-цифровых символов.

Они не могут содержать 1 , i и не может быть дубликатов при перетаскивании / копировании кода вниз.

Разрешенные символы:

023456789ABCDEFGHJKLMNOPQRSTUWVXYZ (Заглавный)

Я перепробовал множество вариантов, но, похоже, не могу разобраться в этом.

Приветствия

Комментарии:

1. дубликаты? Означает ли это, что нельзя использовать один и тот же символ или комбинацию из 5 символов нельзя дублировать?

2. Какой наиболее перспективный из вариантов, которые вы пробовали? Вы ожидаете, что они будут выглядеть случайным образом?

3. Создайте именованную формулу в диспетчере имен -> Pallette: ="023456789ABCDEFGHJKLMNOPQRSTUWVXYZ" Затем используйте эту формулу для случайных кодов: =MID(Pallette,RANDBETWEEN(1,34),1) amp; MID(Pallette,RANDBETWEEN(1,34),1) amp; MID(Pallette,RANDBETWEEN(1,34),1) amp; MID(Pallette,RANDBETWEEN(1,34),1) amp; MID(Pallette,RANDBETWEEN(1,34),1) это даст вам рандомизированные коды, но нет гарантии, что они будут уникальными (но дубликаты будут редкими).

4. нет, я имею в виду, действителен ли A1111?

5. если у вас есть office 365: =CONCAT(MID("023456789ABCDEFGHJKLMNOPQRSTUWVXYZ",RANDARRAY(5,,1,34,TRUE),1)) но, как указано выше, это не гарантирует уникальность, но существует 45 435 424 уникальных комбинации, и вероятность дублирования невелика.

Ответ №1:

Если ваша допустимая символьная строка находится в ячейке A1 , то следующая формула приведет к случайным кодам, длина каждого из которых составляет пять символов:

 =MID(A1,RANDBETWEEN(1,34),1) amp; MID(A1,RANDBETWEEN(1,34),1) amp; MID(A1,RANDBETWEEN(1,34),1) amp; MID(A1,RANDBETWEEN(1,34),1) amp; MID(A1,RANDBETWEEN(1,34),1)

Но обратите внимание, что нет гарантии, что коды будут уникальными.

Как указал @ScottCraner … если у вас есть Office 365, вы можете использовать эту гораздо более короткую формулу, которая использует две новые функции, доступные только в Excel 365:

 =CONCAT(MID(A1,RANDARRAY(5,,1,34,TRUE),1))

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

Ответ №2:

Эта формула сгенерирует коды по порядку

 =SUBSTITUTE(SUBSTITUTE(BASE(K, 34,5),"1","Z"),"I","Y")

Здесь K может быть 0, 1, 2, ... . Один из способов сгенерировать первые ~ 1 048 576 K — это использовать ROW()-1 . Вы могли бы получить более высокие значения K, используя что-то вроде K = 1048576*(COLUMN()-1) ROW()-1 .

Формула работает следующим образом

(a) вызов BASE(K, 34, 5) для получения представления K длиной в 5 символов с базой 34

(b) замена Z на 1 since 1 не является допустимым символом

(c) замена Y на I since I не является допустимым символом

Комментарии:

1. А как насчет символа i ?

2. @excel hero Я не заметил, что i это незаконно. Я это исправлю.

3. Обратите внимание, это сгенерирует только первые 1 048 576 комбинаций, оставляя более 44 000 000.

4. @ScottCraner я не согласен. Вы можете использовать любое значение K до 34^5 - 1 . Использование ROW()-1 — это один из вариантов, и вы правы, что для этого варианта вы получаете только первые ~ 1 048 576 комбинаций. Кроме того, это решение может гарантированно генерировать уникальные коды.

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