Excel基于列重复值判断user/device license的公式求助（2.2万行）

核心规则 #

• 若A列条目唯一（该值在A列仅出现1次），对应B列标记 device license
• 同一A列条目下，前5个出现的行标记 User license，第6个及之后的行标记 device license

适合22000行的高效公式 #

方案1：Excel 365/2021 动态数组公式（性能最优）

直接在B2单元格输入以下公式，会自动填充到所有行：

=LET(
    groups, A2:A22001,
    row_idx, ROW(groups) - ROW(A2) + 1,
    group_rank, BYROW(groups, LAMBDA(val, SUM(--(groups = val) * (row_idx <= ROW(val) - ROW(A2) + 1)))),
    group_total, BYROW(groups, LAMBDA(val, SUM(--(groups = val)))),
    IF(group_total = 1, "device license", IF(group_rank <= 5, "User license", "device license"))
)

公式说明：

• 用LET定义变量减少重复计算，大幅提升22000行数据的处理速度
• group_rank：计算当前行是对应A列值的第几次出现
• group_total：计算对应A列值在整列的总出现次数
• 最后通过双层IF匹配规则输出结果

方案2：兼容旧版Excel的普通公式

如果使用非365版本的Excel，在B2单元格输入以下公式后下拉填充：

=IF(COUNTIF($A$2:$A$22001,A2)=1,"device license",IF(COUNTIF($A$2:$A2,A2)<=5,"User license","device license"))

注意：旧版公式会重复计算整列数据，22000行可能会有轻微卡顿，建议先将A列数据复制为值再使用。

示例验证 #

针对你提供的示例数据，应用公式后结果如下：

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