4.4.2 操作步骤
4.4.2 操作步骤
从前面的分析可以看出，本案例要解决以下2个问题：
（1）在“录取表”中，计算出每位已录取学生的成绩，并计算出每位被录取学生所在系。
（2）统计出各系各专业录取人数和各科录取的平均分。
通常情况下，如果不借助于其他方法的帮助要想在Excel中解决这个问题，只能到“投档表”工作表中一条一条地查找每位考生的“语文”、“数学” 和“英语”成绩。在Excel中有一个函数，就是专门为解决这类问题而设计的，这个函数就是V LOOKUP。VLOOKUP函数的功能是：在数据区域的第1列中查找指定的数值，并返回数据区域当前行中指定列处的数值。而统计各系的录取情况，则可以用分类汇总来实现。
先在“投档表”工作中创建一个“成绩”数据区域，目的是为了在VLOOKUP函数的table_array参数中使用这个区域名称。
1.名称的定义
在“投档表”中创建“成绩”区域
第1步：将“考生号”列移至“语文”列前面。
第2步：选中“考生号”、“语文”、“数学”、“英语”列的数据区域，如图4-4-4所示。
图4-4-4 数据区域的选择
图4-4-5 “定义名称”对话框
第3步：在菜单栏中选择“插入”→“名称”→“定义”命令，打开“定义名称”对话框，在名称框中输入“成绩”，单击“确定”按钮，“成绩”数据区域名称创建完成，如图4-4-5所示。
注意
在“录取表”中是按“考生号”到“成绩”数据区域中进行查找的，所以，在定义“成绩”数据区域时，一定要把“考生号”定义在区域第一列。
如果要删除定义的数据区域，应在“定义名称”对话框中删除。方法是：在菜单栏中选择“插入”→“名称”→“定义”命令，打开定义名称对话框，然后删除已定义的数据区域。
定义数据区域名称还可以先选择要定义的区域，然后在名称框中输入定义的名称，最后按回车键确认。如图4-4-6。
2.查找与引用函数VLOOKUP
用VLOOKUP函数查找录取学生的各科成绩。
第1步：打开“录取表（素材）.xls”，另存为“录取表.xls”。在“录取表”工作表中的“录取专业”列之后插入三列，分别命名为“语文”、“数学”、“英语”。
第2步：在“录取表”中选择目标单元格E3，打开“插入函数”对话框，在“或选择类别”下拉列表中选择“查找与引用”，在“选择函数”列表框中选择“VLOOKUP”，单击“确定”按钮，弹出“函数参数”对话框。
第3步：由于要根据“考生号”查找对应的成绩，所以V LOOKUP函数的第一个参数应该选择考生号所在的单元格“A3”。
第4步：单击“函数参数”对话框的第二个参数对应的文本框，接着在菜单栏中选择“插入”→“名称”→“粘贴”命令，打开“粘贴名称”对话框，选择粘贴名称“成绩”，单击“确定”按钮，区域名称“成绩”被插入到VLOOKUP函数的第二个参数的位置，如图4-4-7所示。用这种方法在公式中输入区域名称既快捷又不容易出错。
图4-4-6 定义数据区域名称
图4-4-7 “粘贴名称”对话框
第5步：“函数参数”对话框中第三个参数是决定VLOOKUP函数找到匹配的考生号所在行后，该行的哪列数据被返回，由于“语文”成绩存放在“成绩”区域的第2列，所以这里通过键盘输入数字“2”
第6步：由于要求考生号精确匹配，所以最后一个参数输入false，如图4-4-8所示。单击“确定”按钮，可以看到V LOOK U P函数找到了考生“蒋俊湘”的语文成绩是“119”。 如图4-4-9所示。 E3单元格中最后的公式是“=V LOOKUP（A 3，成绩，2，FALSE）”。
第7步：将E3单元格的公式复制到F3并改为“=V LOOK UP（A 3，成绩，3，FALSE）”，复制到G3并改为“=VLOOKUP（A3，成绩，4，FALSE）”。
第8步：选中E3:G3单元格区域，将鼠标指向G3单元格右下角的填充柄，当鼠标指针变成
时，双击填充柄。在“录取表”工作表中就得到其他录取学生“语文”、“数学”、“英语”3门课程的成绩。
图4-4-8 设置“函数参数”对话框的4个参数
图4-4-9 VLOOKUP函数执行结果
根据学生的录取专业，用VLOOKUP函数查找录取学生的所在系。
第1步：在“录取表”工作表的H2单元格中输入“系别”
第2步：在H3单元格中中输入公式“=VLOOKUP（D3，专业系别表!A$3:B$11，2，FALSE）”，双击H3单元格的填充柄，复制公式。
3.分类汇总
在对录取表中录取人数和平均分进行统计之前，先创建2个“录取表”工作表的副本。
创建“录取表”工作表的副本。
鼠标右键单击“录取表”工作表标签，在弹出的快捷菜单中选择“移动或复制工作表”，打开“移动或复制工作表”对话框，选中“建立副本” ，如图4-4-10所示。执行两次，建立“录取表”工作表的2个副本：录取表（2）、录取表（3）。
“分类汇总”含有两层意思：按什么分类－“系别”和对什么汇总－“姓名”。
在进行“分类汇总”之前，应先按要分类的“系别”列进行排序，目的是为了把“系别”相同的记录放到一起，然后再对要汇总的列“姓名”和“语文、”“数学”、“英语”分别进行计数和求平均值汇总。
在“录取表（2）”工作表中，用“分类汇总”计算各系的录取人数。
第1步：在“录取表（2）”工作表中，按“系别”字段进行排序，方法如下。①单击“录取表（2）”工作表中数据区任意一个单元格。
②在菜单栏中选择“数据”→“排序”命令，打开“排序”对话框，在“主要关键字”下拉列表中选择“系别”，选中“升序”单选按钮，如图4-4-11所示。
③单击“确定”按钮，“录取表（2）”工作表记录已按“系别”升序排列。
如图4-4-10 建立工作表副本
如图4-4-11 “排序”对话框
第2步：用“分类汇总”统计各系的录取人数，方法如下。
①单击“录取表（2）”工作表数据清单中任意一个单元格。
②在菜单栏中选择“数据”→“分类汇总”命令，打开“分类汇总”对话框。
③在“分类字段”下拉列表中选择“系别”，在“汇总方式”下拉列表框选择“计数”，在“选定汇总项”列表框中选择“姓名”，如图4-4-12所示。单击“确定”按钮。
图4-4-12 “分类汇总”对话框
④单击分级显示符号
，隐藏分类汇总表中的明细数据，结果如图4-4-13所示。
图4-4-13 按“系别”汇总人数
在上面的数据处理中，用“分类汇总”得到了各系的录取人数。如果要统计各个系各个专业的每门课程的录取平均分。这个问题可以用“嵌套分类汇总”来解决。
在“录取表（3）”工作表中，用“嵌套分类汇总”计算各系各专业的每门课程的录取平均分。
第1步：单击“录取表（3）”工作表数据清单中任意一个单元格。
第2步：在菜单栏中选择“数据”→“排序”命令，打开“排序”对话框。“主要关键字”选择“系别”，“次要关键字”选择“录取专业”，单击“确定”按钮。
第3步：在菜单栏中选择“数据”→“分类汇总”命令，打开“分类汇总”对话框。“分类字段”选择“系别”，“汇总方式”为“平均值”，“汇总选项”选择“语文”、“数学”、“英语”。单击“确定”按钮。
第4步：在前面分类汇总的基础上，用同样的方法再进行第二次“分类汇总”，其中，“分类字段”为“录取专业”，“汇总方式”为“平均值”，“汇总选项”选择“语文”、“数学”、“英语”。注意：这时不要选中“替换当前分类汇总”复选框。如图4-4-14所示。汇总结果如图4-4-15所示。
4-4-14 对各“录取专业”的每门课程分类汇总
图4-4-15 各系、各录取专业的每门课程分类汇总