按指定规则为百万级重复邮箱记录生成优先级标识

一、Excel公式实现（适配Excel 365/2021或小批量数据） #

假设字段对应列：

• 邮箱：E列（当前行单元格为E2）
• Campaign ID：C列（非空即视为有效）
• 修改日期：I列（标准日期格式）

动态数组公式（推荐，无需数组快捷键） #

=LET(
    currentEmail, E2,
    hasCampaign, C2<>"",
    currentDate, I2,
    sameEmail, E:E=currentEmail,
    score, IF(sameEmail, IF(C:C<>"",2,1)+I:I, 0),
    maxScore, MAX(score),
    IF(IF(hasCampaign,2,1)+currentDate=maxScore, 1, 0)
)

逻辑说明：

• 用LET封装变量，简化公式结构同时提升计算效率
• 为每条记录计算优先级得分：有Campaign ID的基础分设为2，无则为1，叠加修改日期的序列号（日期越新，数值越大）
• 同一邮箱分组内，得分最高的记录标记为1，其余为0

旧版Excel兼容公式（需按Ctrl+Shift+Enter执行数组计算） #

=IF(MAX(IF(E:E=E2,IF(C:C<>"",2,1)+I:I))=IF(C2<>"",2,1)+I2,1,0)

二、Power Query实现（专为百万级大数据量优化） #

Excel公式处理100万条数据会出现明显卡顿，Power Query是更高效的解决方案：

1. 选中数据区域，点击「数据」选项卡 → 「从表格/区域」导入Power Query编辑器
2. 按以下步骤操作：
   • 点击「转换」选项卡 → 「分组依据」：分组列选「邮箱」，操作选「所有行」，新列名设为GroupedRows
   • 添加自定义列，输入公式：

     Table.Sort([GroupedRows],{{"Campaign ID", Order.Descending}, {"修改日期", Order.Descending}}){0}
     

     （逻辑：对每个邮箱分组的记录，先按「Campaign ID非空优先」排序，再按「修改日期降序」排序，取首条即为优先级最高的记录）
   • 展开自定义列的所有字段，添加条件列：判断当前行与展开的优先级行是否完全匹配，匹配则返回1，否则返回0
   • 调整列顺序后，点击「关闭并上载」将结果导入Excel

核心优势： #

• 处理百万级数据的速度远快于Excel公式
• 操作可复用，后续数据更新只需点击「刷新」即可重新计算

原公式问题复盘 #

你之前使用的=IF(COUNTIFS(E:E,E2,I:I,"<="&I2)=COUNTIF(E:E,E2),1,0)存在两个关键缺陷：

• 完全忽略了「优先选择含Campaign ID的记录」的核心规则
• 逻辑上是标记同一邮箱中日期最早的记录，与需求的「选择修改日期最新」完全相反

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