精心设计公式,轻松打印报表

《精心设计公式,轻松打印报表》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、精心设计公式，轻松打印报表王东生吕杰（吉林省东丰县第二中学136300）考试是学校教育教学活动的重要环节。每次考试结束后，都要对学生成绩进行统计并形成各种统计报表打印出来。然而许多成绩统计软件只是形成了Excel成绩统计数据表，并不能直接用来打印输出，还需要做进一步的整理、排版等工作才能打印出美观、漂亮的成绩统计报表。整理、排版过程需要耗费很长时间，使成绩不能在第一时间公布出去。如果学校的办学规模较大，一个年级有二十多个教学班，光是各班成绩单报表的排版整理就要花费很长时间。怎样能让教务处工作人员从大量的重复工作中解脱出来呢？限于篇幅，本文仅对班级成绩单报表统计打印

2、的设计过程进行详细阐述（以Excel2003为例），以起到举一反三、抛砖引玉的作用。首先，我们在一个工作簿中建立两个工作表，一个命名为“上分表”，一个命名为“班级报表”。图1“上分表”表格标题行包括如下内容：班内序号、姓名、班级、语文、数学、英语、物理、化学、生物、政治、历史、地理、总分、班级名次、年级名次、科类等（如图1）。该表是用来上分的，上分时只需按考场名单顺序填入，并将考生的班级填写准确即可，不用对名单进行排序。（考前可按考场顺序将姓名及班级粘贴到表中，会使上分更方便一些）。图2“班级报表”我们设计成了如图2所示的样式。是在B4纸上分成左右两部分横向排版的

3、一张空表（就像Word的分栏），它是班级成绩单打印报表的模板，我们会通过公式引用“上分表”中的数据来打印各个班级的成绩单报表。上分工作完成后，我们在“上分表”的M2单元格输入公式：=SUM(D2:L2)，并向下填充至最后一名考生，求出每名考生的总分；在“上分表”的N2单元格输入数组公式：=SUM(($C$2:$C$1497=C2)*($M$2:$M$1497>M2))+1，公式输入完成后先不要做任何操作，保持光标还在闪动时，使用Ctrl+Shift+Enter组合键完成数组公式的输入（以下提到的数组公式均按此法输入），并向下填充至最后一名考生，求出每名考生的班级名

4、次；在“上分表”的O2单元格输入数组公式：=SUM(($P$2:$P$1497=P2)*($M$2:$M$1497>M2))+1,并向下填充至最后一名考生，求出每名考生的年级名次。年级名次在这里没有使用RANK函数，是因为上分表中包含文科和理科两类考生，这个公式是对考生在文科和理科两类中分别进行排名。在“上分表”的A2单元格输入数组公式：=TEXT(C2,"00")&TEXT(SUM((C$2:C$1497=C2)*(M$2:M$1497>M2))+SUM((C$2:C2=C2)*(M$2:M2=M2)),"00")，并向下填充至最后一名考生。公式先求出每名考生总

5、分在班级内排名的自然顺序，即保证总分相同的学生排名按分数高低和名单先后顺序排出自然序号，然后将求得的这个自然序号用TEXT函数转换成两位的数字，并与已转换为两位数字的班号，通过连接运算符“&”，产生四位数字的代码，即“班内序号”。此公式的目的是将求得的结果作为“班级报表”按条件查找的对象，为“班级报表”数据的正确引用打基础。上述三个数组公式，均使用了SUM函数，我们也可以不使用数组公式，采用普通公式，将这三个数组公式中的SUM函数换成SUMPRODUCT函数就可以了。图3“上分表”中的公式设置完成后，“班级报表”就可以引用该表中的数据了。在已经排好版的空的“班级报

6、表”中，我们在最上部插入三行，选中A1:AA3区域，通过“格式→单元格→图案”选择自己喜欢的颜色设置单元格背景颜色（这里使用灰色）。依次选中A2:D2区域、E2:M2区域、O2:Q2区域、T2:V2区域，并依次合并单元格。选中E2:M2合并完成后的区域，按住Ctrl键，单击鼠标左键点选R2单元格和W2单元格，将同时选中的这三个单元格背景颜色设置为白色。在A2:D2的合并区域中输入“请输入考试名称”、在O2:Q2的合并区域中输入“请选择科类”、在T2:V2的合并区域中输入“请选择班级”，调整好以上三处的字体大小和颜色。为使界面更美观一些，可把第1行和第3行的行高设得

7、小一点。（如图3）选中“班级报表”R2单元格，点击“数据→有效性→设置选项卡”，在“允许”下拉列表中选择“序列”，在“数据来源”输入框中输入“文科,理科,不分文理”（注意中间的逗号为英文逗号），单击“确定”完成R2单元格下拉按钮的设置。点击下拉按钮，会出现下拉列表：“文科”、“理科”、“不分文理”，供我们选择。图4在“班级报表”中，从AB7单元格开始向下填充数字1至30（假设一个年级30个班，文科为1至6班，理科为7至30班）。选中“班级报表”的W2单元格，点击“数据→有效性→设置选项卡”，在“允许”下拉列表中选择“序列”，在“数据来源”输入框中输入公式：=IF(

8、$R$2=