如何在Excel中生成按班级规则的8位GUID并保留学生最新记录？

需求说明 #

• 生成8位唯一ID（GUID）：班级为JSS1/JSS2/JSS3时，ID以5开头；班级为SS1/SS2/SS3时，ID以2开头。
• 同一学生的GUID需保持一致，不受学年、班级变化影响。
• 仅保留每个学生的最新学年记录。

转换前表格 #

转换后表格 #

实现方法 #

方法一：使用Power Query（推荐，自动化处理） #

Power Query可高效完成去重、排序和ID生成，步骤如下：

1. 导入数据到Power Query：选中数据区域 → 点击「数据」选项卡 → 「从表格/区域」（Excel 2016及以上版本）。
2. 保留学生最新记录：
   • 点击「转换」选项卡 → 「分组依据」，分组列选NAME，新列名设为最新记录，操作选「所有行」。
   • 点击最新记录列的展开箭头 → 选「高级」→ 添加排序规则：按YEAR降序，仅保留第一行。
3. 生成班级前缀：
   • 添加自定义列，公式：if Text.StartsWith([CLASS], "JSS") then "5" else "2"，命名为前缀。
4. 生成唯一GUID：
   • 添加自定义列，用Text.From(RandBetween(1000000, 9999999))生成7位随机后缀，命名为后缀。
   • 合并前缀和后缀列，公式：[前缀] & [后缀]，命名为GUID。
5. 整理并加载数据：调整列顺序（将GUID移至StudentID后），点击「关闭并上载」。

方法二：使用公式+辅助列（手动处理） #

若不想用Power Query，可通过辅助列实现：

1. 标记最新记录：
   • 添加辅助列是否最新，公式：=IF([@YEAR]=MAXIFS([YEAR],[NAME],[@NAME]),"是","否")。
   • 筛选出是否最新为「是」的记录，复制到新工作表。
2. 生成班级前缀：
   • 添加列前缀，公式：=IF(LEFT([@CLASS],3)="JSS","5","2")。
3. 生成统一GUID：
   • 先对姓名去重，给每个唯一姓名分配固定的7位数字后缀（可结合ROW函数生成）。
   • 用VLOOKUP([@NAME], 去重姓名表!$A:$B,2,FALSE)匹配对应后缀，最后合并前缀与后缀：[@前缀] & 匹配到的后缀。

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