Excel 将分组头信息填入组内明细行

Excel由多个纵向的分组表组成，组之间由空白行隔开，每组第1、2行的第2格是分组表头，第3行是列头，第1列和第6列数据是空白的：

	A	B	C	D	E	F
1		ATLANTIC SPIRIT
2		Looe
3	Vessel	Species	Size	Kg	Date	Location
4		POLLACK	2	2.5	23/04/2024
5		POLLACK	3	18.8	23/04/2024
6		POLLACK	41	5.4	23/04/2024
7		LING	3	1.9	23/04/2024
8		WHITING	2	0.4	23/04/2024
9
10		BEADY EYE
11		Plymouth
12	Vessel	Species	Size	Kg	Date	Location
13		BASS	4	15.7	23/04/2024
14		BASS	5	3.2	23/04/2024
15
16		BOY JACK
17		Plymouth
18	Vessel	Species	Size	Kg	Date	Location
19		PLAICE	1	0.8	23/04/2024
20		BLONDE RAY	1	14.3	23/04/2024
21		BLONDE RAY	3	1.6	23/04/2024
22		SPOTTED RAY	5	1.2	23/04/2024
23		THORNBACK RAY	1	6.3	23/04/2024
24		THORNBACK RAY	2	15.7	23/04/2024
25		THORNBACK RAY	3	10.9	23/04/2024
26		THORNBACK RAY	4	2.6	23/04/2024
27		LOBSTER	1	2.7	23/04/2024
28		LOBSTER	2	1.1	23/04/2024
29		RAY BACKS	1	42.1	23/04/2024

需要把每组第1、2行的分组表头填入第1列和第6列：

	A	B	C	D	E	F
1		ATLANTIC SPIRIT
2		Looe
3	Vessel	Species	Size	Kg	Date	Location
4	ATLANTIC SPIRIT	POLLACK	2	2.5	23/04/2024	Looe
5	ATLANTIC SPIRIT	POLLACK	3	18.8	23/04/2024	Looe
6	ATLANTIC SPIRIT	POLLACK	41	5.4	23/04/2024	Looe
7	ATLANTIC SPIRIT	LING	3	1.9	23/04/2024	Looe
8	ATLANTIC SPIRIT	WHITING	2	0.4	23/04/2024	Looe
9
10		BEADY EYE
11		Plymouth
12	Vessel	Species	Size	Kg	Date	Location
13	BEADY EYE	BASS	4	15.7	23/04/2024	Plymouth
14	BEADY EYE	BASS	5	3.2	23/04/2024	Plymouth
15
16		BOY JACK
17		Plymouth
18	Vessel	Species	Size	Kg	Date	Location
19	BOY JACK	PLAICE	1	0.8	23/04/2024	Plymouth
20	BOY JACK	BLONDE RAY	1	14.3	23/04/2024	Plymouth
21	BOY JACK	BLONDE RAY	3	1.6	23/04/2024	Plymouth
22	BOY JACK	SPOTTED RAY	5	1.2	23/04/2024	Plymouth
23	BOY JACK	THORNBACK RAY	1	6.3	23/04/2024	Plymouth
24	BOY JACK	THORNBACK RAY	2	15.7	23/04/2024	Plymouth
25	BOY JACK	THORNBACK RAY	3	10.9	23/04/2024	Plymouth
26	BOY JACK	THORNBACK RAY	4	2.6	23/04/2024	Plymouth
27	BOY JACK	LOBSTER	1	2.7	23/04/2024	Plymouth
28	BOY JACK	LOBSTER	2	1.1	23/04/2024	Plymouth
29	BOY JACK	RAY BACKS	1	42.1	23/04/2024	Plymouth

使用 SPL XLL，输入公式：

=spl("=t=?.group@i(!~.ifn()),k=1,t.run(t1=~(k)(2),t6=~(k+1)(2),~.m(k+3:).run(~(1)=t1,~(6)=t6),k=2),t.conj()",A1:F29)

函数 group@i 按条件进行分组，ifn()返回第 1 个非空成员，~ 是当前成员，~(6) 是当前成员的第 6 个下级成员，m(k+1:) 是第 k+1 个到最后一个成员。