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("=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 公式代替。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。