如何高效查找含字母数字的唯一ID序列中的下一个可用ID？

需求背景 #

需要在包含字母+数字结构的唯一ID列表中，快速定位下一个未被使用的ID，目标是替代现有分步方案，实现单公式高效计算。

现有分步方案回顾 #

现有方案依赖辅助列实现，步骤如下：

• A列：存储所有已存在的唯一ID（格式示例：A1001、B2001）
• C列：生成当前ID的递增后值，公式：=LEFT(A1,1)&RIGHT(A1,4)+1
• D列：验证递增值是否已存在，公式：=COUNTIF($A$1:$A$9,C1)
• 最终定位：通过=INDEX($C$1:$D$9,MATCH(0,$D$1:$D$9,0),1)找到第一个未被占用的ID

单公式高效解法 #

以下分两种场景提供单公式方案，无需辅助列，直接输出结果：

场景1：针对指定前缀查找（如仅找A1/B2开头的ID） #

假设ID格式为「固定前缀+4位数字」（如A10001），以查找A1前缀为例，公式如下：

=TEXT(MAX(IF(LEFT($A$1:$A$9,2)="A1",--RIGHT($A$1:$A$9,LEN($A$1:$A$9)-2),0))+1,"A10000")

注：Excel 2019及更早版本需按Ctrl+Shift+Enter执行数组计算；Excel 365/2021可直接回车。

逻辑说明：

1. LEFT($A$1:$A$9,2)="A1"：筛选出所有A1前缀的ID
2. --RIGHT($A$1:$A$9,LEN($A$1:$A$9)-2)：提取前缀后的数字部分并转为数值
3. MAX(...)：找到该前缀下的最大数字值，加1得到下一个可用数字
4. TEXT(...)：将数字重新拼接为指定格式的ID

示例结果：

• 查找A1前缀：若现有ID为A1001、A1002、A1003，公式返回A1004
• 查找B2前缀：若现有ID仅为B2001，公式返回B2002

场景2：全局查找所有ID中缺失的最小可用ID #

如果需要从所有ID中找到最小的未被使用的ID（不限制前缀），可使用以下公式：

=TEXT(MIN(IF(COUNTIF($A$1:$A$9,LEFT($A$1:$A$9,2)&TEXT(ROW($1:$9999),"0000"))=0,LEFT($A$1:$A$9,2)&TEXT(ROW($1:$9999),"0000"),""))

注：同样需按数组公式规则执行，可根据ID数字部分的长度调整ROW($1:$9999)和"0000"的位数。

方案优势 #

• 无需辅助列，减少工作表冗余
• 直接单次计算得到结果，数据量较大时比分步方案更高效
• 可灵活适配不同前缀长度、数字位数的ID格式

内容的提问来源于stack exchange，提问作者Maki