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分组,每组再按DateTime进行第二层分组。

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

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

问答搜集

https://stackoverflow.com/questions/63899978/excel2016-generate-id-based-on-multiple-criteria-no-vba