Excel在学校中的应用16-成绩通知条

Excel在学校中的应用16-成绩通知条
3.4 成绩通知条
案例背景
在上一节中创建的成绩汇总表的基础上，本节准备将本班学生的期末考试成绩制作成成绩通知条，以便在家长会上向学生家长发放本班学生的期中、期末考试以及学期总评成绩。

关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。

●基础知识定位、横向排序、辅助列排序、自动筛选
●函数应用：IF函数，MOD函数，ROW函数，COLUMN函数，INDEX函数的应用。

最终效果展示
3.3.1创建成绩通知条
Step 1打开工作表
双击3.3节的“成绩汇总表.xls”打开该工作簿。

Step2复制单元格区域
选中单元格区域A1:Q44，然后单击常用工具栏的“复制”按钮。

Step3新建工作簿
单击常用工具栏的“新建”按钮，新建一个空白工作簿。

Step4粘贴单元格区域
①选中A1单元格，按组合键粘贴。

②单击常用工具栏的“粘贴”按钮的下箭头按钮，在弹出的下拉菜单中选择“选择性粘贴”。

③在弹出的“选择性粘贴”对话框中勾选“列宽”单选按钮，然后单击确定按钮。

Step5重名工作表、保存工作簿
①将工作表重命名为“成绩通知条”，并删除多余工作表。

②按组合键，保存工作簿文件“成绩通知条.xls”。

Step6添加新的字段名
①单击行标4选中第4行，单击右键从弹出的下拉菜单中左键单击“插入”，随即插入新的第4行，并调整行高为40。

②单击菜单“格式”→“单元格”，从弹出的“单元格格式”对话框中切换到“对齐”“选项卡，在文本控制”选项框中勾选“自动换行”。

③选中A4单元格，输入公式“=A3”，选中B4单元格，输入公式“=B3”。

④选中C4单元格，输入公式“=C2&C3”
⑤光标放到C4单元格右下角向右拖曳填充柄至Q4单元格，松开鼠标即完成新的字段名设置。

⑥选中A4:Q4单元格区域，然后单击常用工具栏的“复制”按钮，然后单击常用工具栏的“粘贴”按钮的下箭头按钮，在弹出的下拉菜单中选择“选择性粘贴”。

在弹出的“选择性粘贴”对话框中勾选“数值”单选按钮，然后单击“确定”按钮即可完成公式的隐藏（这时保证下一步上删除第2：3行时，第4行单元格不出现错误值#REF！）。

⑦选中并删除第2：3行，即可删除原先的字段名。

Step7添加辅助列
①在R3单元格输入“1”R4单元格输入“2”. ②选中R3:R4单元格区域，右键双击R4单元格右下角的填充柄即可完成R5:R44单元格区域的自然数序列的填充。

③选中R3:R44单元格区域，按组合键复制，选中R45单元格，按组合键粘贴，再选中R85单元格，按组合键粘贴.。

Step8复制、粘贴单元格区域
①选中A2:Q2单元格区域，按组合键复制。

②选中A85:Q125，按组合键粘贴
Step9.升序排序
单击R3单元格，然后单击“常用“工具栏中的”升序排序“按钮。

效果如下图所示。

Step10调整行高
选中1：125单元格区域，单击菜单“格式“→”行“→”最合适的行高“，完成行高的调整。

Step11修改表头名称
将原表头“考试成绩汇总表“改为”2005-2006第一学期初三2 班成绩条“。

Step12定位“空值“快速输入相同值
①选中A1：Q1单元格区域，按组合键复制。

②单击菜单“编辑“→”定位“弹出”定位“对话框。

③单击“定位条件“按钮弹出“定位条件“对话框。

④在“选择“组合框中单击“空值“单选按钮，单击”确定“按钮返回”定位“对话框，然后单击确定按钮就定位了空值单元格。

⑤按组合键粘贴，表头标题行”2005-2006第一学期初三2 班成绩条“就全部被粘贴到“空行“处了。

Step13删除辅助列
右键单击R列列标，在弹出的快捷菜单中选中“删除“。

Step14设置“成绩条“格式
①选中A1：Q1单元格区域，单击常用工具栏“合并及居中“按钮。

②选中A1：Q3单元格区域，单击常用工具栏“格式刷“按钮。

③光标选中A4:Q123单元格区域，即可将A1：Q3单元格区域复制到A4:Q123单元格区域。

④单击R列列标选中R列，按组合键选中R列和它右边的所有列，单击菜单“编辑“→”清除“→”格式“。

即可清除所选区域的所有边框。

至此“成绩条“全部制作好了，效果如下图。

本案例“成绩条“的制作方法没有使用复杂的函数嵌套来完成，只是通过添加辅助列，然后重新排序的方法完成了“成绩条“的制作。

虽然操作过程稍显繁琐，但是设计思路简单，便于理解。

在实际工作中”添加辅助列“的方法会经常用到，请学习者在实际工作中注重实践操作。

案例拓展
本案例向大家介绍一种使用函数嵌套设计公式制作“成绩条“的的方法。

Step 1新建工作簿、重命名工作表
单击常用工具栏的“新建”按钮，新建一个空白工作簿。

将工作表“sheet1“重命名为”成绩条“，工作表”sheet2“重命名为；“成绩汇总表”。

选中A1单元格，然后将上面案例（图7）中“成绩汇总表”的粘贴过来。

保存文件，文件名为“成绩条.xls”。

Step2制作成绩条
①光标切换到“成绩条”选中A1单元格，输入公式：
“IF(MOD(ROW(),3)=1,"",IF(MOD(ROW(),3)=2,
成绩汇总表!A$2,INDEX(成绩汇总表!$A:$Q,(ROW()+4)/3+1,COLUMN())))”
②向右拖曳A1 单元格右下角的填充柄至Q1单元格，然后选中A1:Q1单元格区域，向下拖曳填充柄至Q3(第三行)第一个学生成绩条的初步制作。

③选中A3:Q3单元格区域，向下拖曳Q3单元格右下角的填充柄至Q123单元格即可完成其余学生的成绩条的初步制作。

Step3添加“成绩条”表头
①在A1单元格输入“2005-2006第一学期初三2 班成绩条”，按组合键复制。

②按键，光标陆续选中单元格A4，A7，…，A122，按组合键粘贴。

③选中A1:Q1单元格区域，并单击常用工具栏“合并及居中”按钮，并将字体，字形，字号设为“黑粗14号”。

④选中A1:Q3单元格区域，并单击常用工具栏“格式刷”按钮，光标选中A4:Q122单元格区域即可将A1:Q3单元格区域的格式复制到A4:Q122单元格区域。

案例公式解析
本案例中“成绩条”工作表B列中所有单元格中的公式均为：
=IF(MOD(ROW(),3)=1,"",IF(MOD(ROW(),3)=2,
成绩汇总表!B$2,INDEX(成绩汇总表!$A:$Q,(ROW()+4)/3+1,COLUMN())))
我们以B1单元格为例来解析公式，概括的说这个嵌套的IF函数的含义为首先判断B1单元格所在的行号除以3的余数是否等于1，如果是单元格就返回空值（这是为了后面插入标题预留的空行），否则进入嵌套的第二个IF 函数，然后判断B1单元格所在的行号除以3的余数是否等于2，如果是则单元格返回的是“成绩汇总表”中单元格B2中的值，否则的话B 1单元格所在的行号除以3的余数一定是0，这时返回“成绩汇总表”中单元格区域A：Q 中指定行号为（ROW()+4)/3+1，列号为单元格B1列号的单元格的值。

其中公式中的行号和列号分别为：“（ROW()+4)/3+1”和COLUMN()
这时化简后“行号”为（1+4）/3+1=10/3,这个数小于4，同时大于3，INDEX函数将此数向下取整默认为3，“列号”为：2，也就是返回成绩汇总表”中单元格区域A：Q中的第三行第2列B3单元格的值“周飞”。

公式里INDEX函数的行号和列号之所以不直接输入具体数字，而是使用ROW和COLUMN函数与数字运算的组合在这里主要是考虑到复制填充公式的需要，公式里设计的具体的ROW和COLUMN函数与数字运算的组合就像我们在中学数学中数列的通项公式一样。

也只有观察清楚规律，求出通项公式，才能通过横向和纵向复制填充公式完成全部”成绩条”制作，达到提高工作效率的目的。

但是看出“通项公式”对于初学者来说往往不是一次完成的，可以首先在几个相邻单元格输入公式，公式设计首先应注意正确，具体地说公式返回的内容正确，然后再观察规律，格局实际情况使用ROW和COLUMN函数与数字运算的组合设计出“通项”，以便复制填充公式。