作者：绿色风
分类：
时间：2022-08-18
浏览：88
楼主
亡者天下 |
Q：下图所示：如何将四个表格的成绩汇总，并且汇总之后，能够实现多级菜单联动？
A：首先通过SQL将四个表格进行汇总，再使用Excel2010中的切片器功能实现多级联动。
- select "一班" as 班级, * from [Sheet1$A2:B5] union all
- select "一班" as 班级, * from [Sheet1$C2:D5] union all
- select "一班" as 班级, * from [Sheet1$E2:F5] union all
- select "二班" as 班级, * from [Sheet1$G2:H5] union all
- select "二班" as 班级, * from [Sheet1$I2:J5] union all
- select "二班" as 班级, * from [Sheet1$K2:L5] union all
- select "三班" as 班级, * from [Sheet1$A7:B10] union all
- select "三班" as 班级, * from [Sheet1$C7:D10] union all
- select "三班" as 班级, * from [Sheet1$E7:F10] union all
- select "四班" as 班级, * from [Sheet1$G7:H10] union all
- select "四班" as 班级, * from [Sheet1$I7:J10] union all
- select "四班" as 班级, * from [Sheet1$K7:L10]
得到汇总后的数据透视表，如下：
再使用切片器功能，即可实现多级联动
 没有班主任的汇总.rar
|
2楼
亡者天下 |
在增加了新的一行——班主任之后，又该如何解决呢？
只要稍微改动SQL语句即可，同样实现四个字段多级联动
- select "一班" as 班级,"李某" as 班主任, * from [Sheet1$A3:B6] union all
- select "一班" as 班级,"李某" as 班主任, * from [Sheet1$C3:D6] union all
- select "一班" as 班级,"李某" as 班主任, * from [Sheet1$E3:F6] union all
- select "二班" as 班级,"王某" as 班主任, * from [Sheet1$G3:H6] union all
- select "二班" as 班级,"王某" as 班主任, * from [Sheet1$I3:J6] union all
- select "二班" as 班级,"王某" as 班主任, * from [Sheet1$K3:L6] union all
- select "三班" as 班级,"刘某" as 班主任, * from [Sheet1$A9:B12] union all
- select "三班" as 班级,"刘某" as 班主任, * from [Sheet1$C9:D12] union all
- select "三班" as 班级,"刘某" as 班主任, * from [Sheet1$E9:F12] union all
- select "四班" as 班级,"张某" as 班主任, * from [Sheet1$G9:H12] union all
- select "四班" as 班级,"张某" as 班主任, * from [Sheet1$I9:J12] union all
- select "四班" as 班级,"张某" as 班主任, * from [Sheet1$K9:L12]
增加班主任之后.rar
|
3楼
、紫陌 |
菜鸟
表示压力好大 |
4楼
亡者天下 |
补充了一个动画！
增加班主任之后.rar
|
免责声明
有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，
经与ExcelTip.Net站长Apolloh商议并征得其同意，
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，
供有需要的人士查询使用，也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议，
可与本人沟通提出，或于本站点留言，我们会尽快处理。
在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一