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 个到最后一个成员。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。