班主任通过Excel制作成绩单技巧大全
1.工作表的操作
在“学生成绩表.xlsx”工作簿中将“Sheet1”工作表重命名为“多媒体091班成绩表”
按下图输入红线以上的内容
打开“学生成绩表-素材.xlsx”，复制内容到“学生成绩表.xlsx”相应位置
![](https://wkrtcs.bdimg.com/rtcs/image?w=553.60&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"553.60","h":"198.00","dataType":"png","c":"word/media/image1.png","imgOriW":699,"imgOriH":250})
2.设置工作表行、列
在标题行下方插入一行，设置行高为7.50
将“200907710202”一行移至“200907710203”一行的上方
删除“200907710210”行上方的一行(空行)
![](https://wkrtcs.bdimg.com/rtcs/image?w=553.47&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"553.47","h":"191.47","dataType":"png","c":"word/media/image2.png","imgOriW":685,"imgOriH":237})
3.设置单元格格式
将单元格区域B1:J1合并及居中；设置字体为华文行楷，字号为20，字体颜色为绿色
将单元格区域E4:I43应用数值型，保留两位小数
将单元格区域B3:J43的对齐方式设置为水平居中，字号为11
为单元格区域B3:J3设置浅绿色的底纹
设置表格边框线：将单元格区域B3:J40的外边框与内边框分别设置为绿色的双实线与单实线
插入批注：为“0”（E13）单元格插入批注“缺考”
![](https://wkrtcs.bdimg.com/rtcs/image?w=553.53&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"553.53","h":"197.00","dataType":"png","c":"word/media/image3.png","imgOriW":680,"imgOriH":242})
4.复制工作表
将“多媒体091班成绩表”工作表复制到Sheet2工作表中
5.设置打印标题
在Sheet2工作表的第15行前插入分页线；设置1—3行为打印标题
删除A列，调整C列宽度为5，D—I列宽度为8
![](https://wkrtcs.bdimg.com/rtcs/image?w=527.00&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"527.00","h":"257.00","dataType":"png","c":"word/media/image4.png","imgOriW":527,"imgOriH":257})
6.统计总分与各科平均分，求出各科最高与最低分数
使用函数SUM( )求出各位同学的总分
使用函数AVERAGE( )、MAX( )、MIN( )计算各科平均分、最高分、最低分
![](https://wkrtcs.bdimg.com/rtcs/image?w=553.53&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"553.53","h":"351.93","dataType":"jpeg","c":"word/media/image5.jpeg","imgOriW":637,"imgOriH":405})
7.按总分填入相应名次
1）使用RANK（）函数计算名次
![](https://wkrtcs.bdimg.com/rtcs/image?w=553.27&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"553.27","h":"199.20","dataType":"png","c":"word/media/image6.png","imgOriW":661,"imgOriH":238})
2）利用排序操作得到名次
1在名次后添加一列“序号”，利用自动填充填入“1、2、3……”
2按“总分”从高到低对成绩表排序
3填入相应名次
4按“序号”从低到高排序，在删除“序号”列
8.统计各科各分数段人数
1）在Sheet2中制作表格“多媒体091成绩分布表”，并设置格式
![](https://wkrtcs.bdimg.com/rtcs/image?w=509.00&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"509.00","h":"202.00","dataType":"png","c":"word/media/image7.png","imgOriW":509,"imgOriH":202})
2）使用函数COUNTIFS( )统计各科各分数段人数
![](https://wkrtcs.bdimg.com/rtcs/image?w=497.00&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_4","w":"497.00","h":"589.00","dataType":"jpeg","c":"word/media/image8.jpeg","imgOriW":497,"imgOriH":589})
9.制作多媒体091成绩分布图
1）使用“多媒体091成绩分布表”数据，生成三维簇状柱形图
![](https://wkrtcs.bdimg.com/rtcs/image?w=553.67&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_2","w":"553.67","h":"359.87","dataType":"png","c":"word/media/image9.png","imgOriW":620,"imgOriH":403})
2）交换坐标轴上的数据，更改图表布局为“布局9”，输入标题，并设置格式
![](https://wkrtcs.bdimg.com/rtcs/image?w=566.67&md5sum=fd366dbeca2f660b7ca5ecaf470a7e17&sign=70b5111666&rtcs_flag=2&rtcs_ver=4&l=webapp&bucketNum=250&ipr={"t":"img","r":"r_1","w":"566.67","h":"49.33","dataType":"gif","c":"word/media/image10.gif"})
推荐一个老师必备的在线工具 易查分，可以将电子表格转换为在线查询，方便家长查询自己孩子成绩，老师省心，家长放心，百度 易查分 即可
本文来源：https://www.2haoxitong.net/k/doc/7a4bb8e3b5daa58da0116c175f0e7cd185251864.html
文档为doc格式