如何在Excel中检测照片编号序列的缺失与重复？

假设你的照片编号数据在A列（A2开始，A1为表头），条目为单个数字或X-Y格式的范围，以下是两种高效检测方案：

一、公式法（适合小数据量） #

步骤1：拆分起始/结束编号 #

• 在B2输入起始号提取公式，下拉填充：

  =IFERROR(--LEFT(A2,FIND("-",A2)-1),--A2)
  

  作用：如果条目是范围，提取-左侧的数字；如果是单个数字，直接转换为数值。
• 在C2输入结束号提取公式，下拉填充：

  =IFERROR(--RIGHT(A2,LEN(A2)-FIND("-",A2)),--A2)
  

  作用：如果条目是范围，提取-右侧的数字；如果是单个数字，与起始号一致。

步骤2：按起始号排序 #

选中B列，点击「数据」→「升序」，确保所有条目按起始编号顺序排列。

步骤3：检测重复与间隙 #

• D2单元格输入"无异常"（第一条数据无前置条目）
• D3单元格输入检测公式，下拉填充：

  =IF(B3<=C2,"重复："&MIN(B3,C2)&" 出现在行"&ROW()&"和行"&ROW()-1,IF(B3>C2+1,"间隙："&C2+1&"至"&B3-1,"无异常"))
  

  逻辑说明：
  • 若当前起始号 ≤ 上一条结束号：判定为重复，提示重复编号及对应行号
  • 若当前起始号 > 上一条结束号+1：判定为间隙，提示缺失的编号范围
  • 其余情况为连续正常

二、Power Query法（适合大数据量） #

步骤1：导入数据到Power Query #

选中A列数据，点击「数据」→「从表格/区域」，确认数据导入编辑器。

步骤2：拆分起始/结束编号 #

添加两个自定义列：

• 自定义列「起始号」：

  = if Text.Contains([列1], "-") then Number.From(Text.BeforeDelimiter([列1], "-")) else Number.From([列1])
  

• 自定义列「结束号」：

  = if Text.Contains([列1], "-") then Number.From(Text.AfterDelimiter([列1], "-")) else Number.From([列1])
  

步骤3：排序并添加索引 #

• 点击「起始号」列标题，选择升序排序
• 点击「添加列」→「索引列」→「从0开始」

步骤4：检测异常 #

添加自定义列「检测结果」：

= if [索引] = 0 then "无异常" else if [起始号] <= #"添加索引"{[索引]-1}[结束号] then "重复：" & Number.ToText(List.Min({[起始号], #"添加索引"{[索引]-1}[结束号]})) & " 出现在当前行和上一行" else if [起始号] > #"添加索引"{[索引]-1}[结束号]+1 then "间隙：" & Number.ToText(#"添加索引"{[索引]-1}[结束号]+1) & "至" & Number.ToText([起始号]-1) else "无异常"

步骤5：导出结果 #

点击「关闭并上载」，将处理结果导入Excel表格，即可查看所有异常提示。

注意事项 #

• 确保A列无非数字/横杠的多余字符，否则公式或Power Query会报错，可提前用=ISNUMBER(--SUBSTITUTE(A2,"-",""))验证数据格式。
• 若存在跨多行的重复或间隙，逐行检测会依次提示异常。

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