根据分类及组内序号进行编码
例题描述和简单分析
data.xlsx记录课程数据,未排序,部分如下:
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 |
上面涉及多层分组后的计算,以及组内序号的使用。
解法及简要说明
在集算器中编写脚本,如下所示:
A |
|
1 |
=file("040/data.xlsx").xlsimport@t() |
2 |
=A1.group(Course).(~.group(Date,Time)) |
3 |
=A2.conj(~.news(~;Course,Date,Time,left(Course,3)/string(A2.~.#,"000"):'Batch ID')) |
4 |
=file("040/result.xlsx").xlsexport@t(A3) |
A1,A4:读/写Excel文件
A2:按Course分组,每组再按Date、Time进行第二层分组。
A3:在大组内先计算各小组,按规则生成新列Batch ID,再合并小组,最后合并大组。其中A2.~.#表示每个小组在大组内的编号。
上述算法可生成符合要求的 Batch ID,但记录顺序发生了变化,如果想保持原序,可在分组前新增行号列,合并后再按行号列排序。
https://stackoverflow.com/questions/63899978/excel2016-generate-id-based-on-multiple-criteria-no-vba
英文版