为表格中各Account Nr.的唯一Partner分配编号的公式需求

适用场景 #

表格为层级结构不可排序，需基于Account Nr.分组，为每个组内首次出现的Partner分配递增编号（重复出现的Partner沿用首次编号）。示例：

• Account Nr.2 对应的 Partner 按顺序为 C、B → 编号 1、2
• Account Nr.4 对应的 Partner 按顺序为 F、G、A → 编号 1、2、3

解决方案 #

1. 旧版Excel（不支持动态数组） #

假设Account Nr.在A列、Partner在B列，数据从第2行开始，在C2单元格输入以下公式，下拉填充即可：

=SUMPRODUCT(($A$2:A2=A2)*(MATCH($B$2:B2,$B$2:B2,0)=ROW($B$2:B2)-ROW($B$2)+1))

公式解释：

• $A$2:A2=A2：筛选当前行及以上、与当前行同账户的记录
• MATCH($B$2:B2,$B$2:B2,0)=ROW($B$2:B2)-ROW($B$2)+1：判断该行的Partner是否为首次出现（MATCH返回该Partner首次出现的位置，若等于当前行在范围中的位置，则为首次出现）
• SUMPRODUCT：将两个条件的结果相乘后求和，得到当前Partner在对应账户下的递增编号

2. Excel 365/2021（支持动态数组） #

使用动态数组公式，输入到C2单元格后会自动溢出填充整列，无需下拉：

=BYROW(A2:A100, LAMBDA(acc,
    BYROW(B2:B100, LAMBDA(ptn,
        LET(
            group, FILTER(B2:B100, A2:A100=acc),
            pos, XMATCH(ptn, group),
            COUNT(UNIQUE(INDEX(group, SEQUENCE(pos))))
        )
    ))
))

注意：将公式中的A2:A100和B2:B100替换为你实际的数据范围。

公式解释：

• BYROW：逐行处理每一条记录
• FILTER(B2:B100, A2:A100=acc)：提取当前账户对应的所有Partner列表（保持原顺序）
• XMATCH(ptn, group)：找到当前Partner在组内的位置
• COUNT(UNIQUE(INDEX(group, SEQUENCE(pos))))：统计从组内第一条到当前位置的唯一Partner数量，即为递增编号

为什么COUNTIF+FILTER不适用？ #

旧版Excel中COUNTIF不支持数组作为参数，而FILTER返回的是数组结果，因此组合后无法正常计算。上面的方案规避了数组参数的限制，同时保留了原表格的层级顺序。

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