Excel 根据分类及组内序号进行编码

例题描述和简单分析

Excel 记录课程数据，未排序，部分如下：

	A	B	C
1	Course	Date	Time
2	Word	1-Sep-20	9:00
3	Word	1-Sep-20	9:00
4	PowerPoint	1-Sep-20	9:00
5	Word	1-Sep-20	12:00
6	PowerPoint	1-Sep-20	12:00
7	Excel	1-Sep-20	12:00
8	Word	1-Sep-20	12:00

现在要新增一个编码列 Batch ID，使 Course\Date\Time 相同的记录 Batch ID 也相同。编码规则是：Course 的前 3 个字母 + 序号。数据按 Course 分大组后，每大组数据再按 Date 和 Time 分小组，编码中的序号即大组内各小组的序号。

	A	B	C	D
1	Course	Date	Time	Batch ID
2	Word	1-Sep-20	9:00	Wor001
3	Word	1-Sep-20	9:00	Wor001
4	PowerPoint	1-Sep-20	9:00	Pow001
5	Word	1-Sep-20	12:00	Wor002
6	PowerPoint	1-Sep-20	12:00	Pow002
7	Excel	1-Sep-20	12:00	Exc001
8	Word	1-Sep-20	12:00	Wor002

上面涉及多层分组后的计算，以及组内序号的使用。

解法及简要说明

使用 Excel 插件 SPL XLL

在空白单元格写入公式：

=spl("=(t=E(?).group(Course).(~.group(Date,Time)),t.conj(~.news(~;Course,Date,Time,left(Course,3)/string(t.~.#,""000""):'Batch ID')))",A1:C8)

如图：

简要说明：

按Course分组，每组再按Date、Time进行第二层分组。

在大组内先计算各小组，按规则生成新列Batch ID，再合并小组，最后合并大组。其中A2.~.#表示每个小组在大组内的编号。

上述算法可生成符合要求的 Batch ID，但记录顺序发生了变化，如果想保持原序，可在分组前新增行号列，合并后再按行号列排序。