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)

=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 公式代替。