Excel无需VBA生成排除特定字符的不重复5位字母数字码
Excel 无重复5位指定字符编码实现方案
核心规则与实现逻辑
- 你指定的可用字符集为
023456789ABCDEFGHJKLMNOPQRSTUWVXYZ,全为大写格式,不含禁用字符1、i,逐位计数共34个有效字符 - 5位编码的总不重复容量为
34^5 = 45435424,可生成超4500万个不重复编码,完全满足常规使用需求 - 实现逻辑:以逐行固定递增的行号作为唯一计数基数,将其转换为对应进制的数,逐位映射到指定字符集上。因为行号在公式拖动时是逐行固定的,不会随表格刷新变动,从根本上避免重复值出现。
可直接套用的公式
Microsoft 365 / Excel 2021及以上版本(支持LET函数)
在编码列表的首个单元格输入以下公式,直接向下拖动即可自动生成连续不重复的5位编码:
=LET( chars,"023456789ABCDEFGHJKLMNOPQRSTUWVXYZ", m,LEN(chars), n,ROW()-1, p5,INT(n/(m^4)), p4,INT(MOD(n,m^4)/(m^3)), p3,INT(MOD(n,m^3)/(m^2)), p2,INT(MOD(n,m^2)/m), p1,MOD(n,m), MID(chars,p5+1,1)&MID(chars,p4+1,1)&MID(chars,p3+1,1)&MID(chars,p2+1,1)&MID(chars,p1+1,1) )
低版本Excel兼容方案(无LET函数支持)
对于2019及更早版本的Excel,直接使用以下硬编码公式即可实现相同效果:
= MID("023456789ABCDEFGHJKLMNOPQRSTUWVXYZ",INT((ROW()-1)/(34^4))+1,1) & MID("023456789ABCDEFGHJKLMNOPQRSTUWVXYZ",INT(MOD(ROW()-1,34^4)/(34^3))+1,1) & MID("023456789ABCDEFGHJKLMNOPQRSTUWVXYZ",INT(MOD(ROW()-1,34^3)/(34^2))+1,1) & MID("023456789ABCDEFGHJKLMNOPQRSTUWVXYZ",INT(MOD(ROW()-1,34^2)/34)+1,1) & MID("023456789ABCDEFGHJKLMNOPQRSTUWVXYZ",MOD(ROW()-1,34)+1,1)
方案特性说明
- 绝对无重复:公式以逐行递增的行号为映射基础,编码序列与自然数一一对应,只要不打乱行的排列顺序,拖动复制生成的所有编码不会出现重复值
- 无随机变动问题:全程仅使用
ROW()、INT()、MOD()、MID()等非易失性函数,不会因为编辑其他单元格、刷新表格导致编码值变动,复制粘贴后值固定 - 完全符合规则:所有生成的编码固定为5位长度,取值范围严格限定在你指定的字符集内,不会混入禁用字符
注意:如果编码不是从工作表第1行开始放置,只需把公式中的
ROW()-1替换为对应差值,保证第一个生成位置对应的计算值为0即可,例如从第8行开始放第一个编码,就把公式里的ROW()-1改成ROW()-7。
内容的提问来源于stack exchange,提问作者RJ34
相关产品推荐
相关产品推荐

