Excel 根据标题行分组汇总，不增加辅助列
有多个结构相同但行数不同的Excel表格，第2行是标题行，最后一行是汇总行，纵向布局，如下是4个表格：
A |
B |
C |
D |
|
1 |
Name |
Entered Calls |
Accepted Calls |
Accept % |
2 |
Team Leader A |
|||
3 |
Agent 1 |
100 |
50 |
50% |
4 |
Agent 2 |
100 |
60 |
60% |
5 |
Agent 3 |
100 |
70 |
70% |
6 |
Agent 4 |
100 |
80 |
80% |
7 |
Agent 5 |
100 |
90 |
90% |
8 |
Totals |
500 |
350 |
70% |
9 |
Name |
Entered Calls |
Accepted Calls |
Accept % |
10 |
Team Leader B |
|||
11 |
Agent 6 |
101 |
50 |
50% |
12 |
Agent 7 |
100 |
60 |
60% |
13 |
Agent 8 |
100 |
70 |
70% |
14 |
Agent 9 |
100 |
80 |
80% |
15 |
Agent 10 |
100 |
90 |
90% |
16 |
Totals |
501 |
350 |
70% |
17 |
Name |
Entered Calls |
Accepted Calls |
Accept % |
18 |
Team Leader C |
|||
19 |
Agent 11 |
102 |
50 |
50% |
20 |
Agent 12 |
100 |
60 |
60% |
21 |
Agent 13 |
100 |
70 |
70% |
22 |
Agent 14 |
100 |
80 |
80% |
23 |
Agent 15 |
100 |
90 |
90% |
24 |
Totals |
502 |
350 |
70% |
25 |
Name |
Entered Calls |
Accepted Calls |
Accept % |
26 |
Team Leader D |
|||
27 |
Agent 16 |
104 |
50 |
50% |
28 |
Agent 17 |
100 |
60 |
60% |
29 |
Agent 18 |
100 |
70 |
70% |
30 |
Agent 19 |
100 |
80 |
80% |
31 |
Agent 20 |
100 |
90 |
90% |
32 |
Totals |
504 |
350 |
70% |
不增加辅助列，按标题行分组汇总，最后一行是总计：
A |
B |
C |
D |
|
1 |
Entered Calls |
Accepted Calls |
Accept % |
|
2 |
Team Leader A |
500 |
350 |
0.7 |
3 |
Team Leader B |
501 |
350 |
0.7 |
4 |
Team Leader C |
502 |
350 |
0.7 |
5 |
Team Leader D |
504 |
350 |
0.7 |
6 |
Totals |
2007 |
1400 |
0.7 |
使用 SPL XLL，输入公式：
使用 SPL XLL，输入公式：=spl("=d=?.group@i(~(1)==""Name"").(t=~.m(-1),~(2)(1)|t.m(2:)), d| [[""Totals"",d.sum(~(2)),d.sum(~(3)),d.avg(~(4))]]",A1:D32)
group@i会在条件满足时生成新分组，~ 表示当前组，~.m(-1) 表示最后的成员（行）。后面的代码用来总计，可以用 Excel 公式代替。
https://stackoverflow.com/questions/78415314/sumifs-rows-based-on-header
英文版