Excel基于2-3个单元格生成Bolt ID及工作表比对问题求助

一、在第一个工作表生成符合规则的Bolt ID #

方法1：Excel动态数组公式（适用于365/2021版本） #

假设你的数据列是：A=Program ID，B=Step Number，C=Batch Size，在D2单元格输入以下公式，自动生成所有Bolt ID并展开行：

=LET(
    data_range, A2:C100,  // 替换成你的实际数据范围
    prog_id, INDEX(data_range,,1),
    step_num, INDEX(data_range,,2),
    batch_size, INDEX(data_range,,3),
    repeat_times, IF(batch_size=0, 1, batch_size),
    total_rows, SUM(repeat_times),
    row_seq, SEQUENCE(total_rows),
    group_cum, SCAN(0, repeat_times, LAMBDA(acc, curr, acc+curr)),
    match_group, XMATCH(row_seq, group_cum, 1),
    base_id, INDEX(prog_id&"_"&step_num, match_group),
    suffix, IF(INDEX(batch_size, match_group)=0, "", "_"&row_seq - INDEX(group_cum, match_group) + 1),
    base_id&suffix
)

公式逻辑：

• 计算每一行需要生成的ID数量（Batch Size=0时生成1个，否则生成对应数量）
• 生成总行数序列，通过累计分组匹配到原始数据行
• 拼接基础ID和序号后缀，得到最终Bolt ID

方法2：Power Query（适合大量数据批量处理） #

1. 选中第一个工作表的数据区域，点击「数据」选项卡 → 「从表格/区域」导入Power Query
2. 添加自定义列，输入公式生成重复序列：

   = if [Batch Size] = 0 then {1} else {1..[Batch Size]}
   

3. 点击自定义列右侧的展开按钮，选择「展开到新行」
4. 添加Bolt ID列，输入公式：

   = [Program ID] & "_" & [Step Number] & if [Batch Size] = 0 then "" else "_" & Text.From([自定义列])
   

5. 删除多余的自定义列，点击「关闭并上载」，得到展开后的带Bolt ID的新工作表

二、比对两个工作表排查差异 #

方法1：用XLOOKUP快速匹配并标记缺失/差异 #

在第一个工作表的展开表中新增一列（比如E列），输入公式匹配第二个工作表的对应数据：

=XLOOKUP([@Bolt ID], Sheet2!$D:$D, Sheet2!$A:$Z, "第二个表无匹配", 0)

• 替换Sheet2!$D:$D为第二个工作表中Bolt ID所在列
• 替换Sheet2!$A:$Z为需要比对的目标数据列范围
• 返回"第二个表无匹配"的行即为第一个表有但第二个表缺失的条目；反过来在第二个表用同样公式可找到重复/多余的条目

方法2：Power Query合并查询（可视化排查） #

1. 将两个工作表都导入Power Query
2. 点击「合并查询」→ 「合并查询作为新查询」
3. 选择两个表的Bolt ID作为匹配键，合并类型选择「完全外部」
4. 展开合并后的列，查看结果：
   • 第一个表的列为空的行：第二个表存在但第一个表缺失/重复的条目
   • 第二个表的列为空的行：第一个表存在但第二个表缺失的条目
5. 可添加自定义列标记差异：

   = if [第一个表的Bolt ID] = null then "仅在第二个表存在" else if [第二个表的Bolt ID] = null then "仅在第一个表存在" else "匹配"
   

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