如何基于条件自动清理Excel中的LMS培训报告数据？

针对你需要清理特定课程培训数据、标记员工完成状态的需求，下面分**公式法（快速上手）和Power Query法（自动化批量处理）**两种方案，以及对应的搜索关键词：

核心思路 #

1. 先筛选出E列为「Assessment and Escalation Class」的目标课程数据，排除无关内容
2. 按员工（A列）分组，判断该员工是否存在「Completed」状态记录
3. 对每组员工保留符合规则的单行数据，标记对应状态并保留指定日期

方法一：公式法（适合新手快速操作） #

步骤1：添加辅助列判断员工是否完成课程 #

在空白列（比如K列）输入公式，下拉填充：

=COUNTIFS($A:$A,A2,$G:$G,"Completed",$E:$E,"Assessment and Escalation Class")>0

• 作用：返回TRUE表示当前员工有该课程的「Completed」记录，FALSE则无

步骤2：获取最新完成日期（针对已完成员工） #

在L列输入公式，下拉填充：

=IF(K2,MAXIFS($H:$H,$A:$A,A2,$G:$G,"Completed",$E:$E,"Assessment and Escalation Class"),"")

• 作用：如果员工已完成课程，返回该员工最新的培训开始日期；未完成则留空

步骤3：筛选并保留目标行 #

1. 筛选K列为TRUE的行，再筛选H列等于对应L列值的行，这些是需要保留的已完成员工记录，在J列标记「Complete」，删除其他重复行
2. 筛选K列为FALSE的行，按A列删除重复值（保留任意一行），在J列标记「Incomplete」

方法二：Power Query法（自动化批量处理，适合重复导出的报告） #

如果需要定期处理这类报告，Power Query能一键完成所有步骤，无需手动操作：

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

选中数据区域→点击「数据」选项卡→「从表格/区域」（勾选「我的表格有标题」）

步骤2：筛选目标课程 #

在Power Query编辑器中，点击E列的筛选按钮→只勾选「Assessment and Escalation Class」

步骤3：按员工分组并聚合数据 #

1. 点击「转换」选项卡→「分组依据」
2. 分组依据选择「User Full Name」（A列），然后添加以下聚合列：
   • 状态：操作选「自定义」，公式填：

     =if List.Contains([G], "Completed") then "Complete" else "Incomplete"
     

   • 最新培训日期：操作选「最大值」，列选择「Training Start Date」（H列）
   • 其他需要保留的列（比如员工ID等）：操作选「第一个值」，列选择对应列名

步骤4：加载回Excel #

点击「关闭并上载」，即可得到处理好的结果：每个员工一行，自动标记状态，保留最新日期，无重复行

搜索关键词（方便你自行拓展学习） #

• Excel COUNTIFS 多条件计数
• Excel MAXIFS 多条件取最大值
• Excel 删除重复值 按条件保留
• Power Query 分组聚合 自定义逻辑
• Excel 培训状态批量标记

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