第1章O ffice Excel 2010高级应用技术1.1学生成绩管理一、考查知识点数据清单、格式设置、公式与函数、排序、筛选、分类汇总和图表等级知识，利用多种公式和函数对单元格中的数据进行运算；对各种数据可以进行格式设置；分析汇总各单元格中的数据信息，把相关数据用统计图表的形式表示，运用Excel 2010电子表格强大的数据管理功能，进行相应的数据处理和分析。
二、知识点解析1、创建学生信息表，如图1-1所示。
图1-1原始工作表2、操作内容：（1）将首行合并居中，并将标题“学生信息表”设置为“黑体”，“20号”，将其他数据清单数据设置为“宋体”，“12号”，“水平居中”。
（2）将数据表中的行高设为“20磅”，列宽设为“最合适的列宽”，数据表中的单元格垂直和水平对齐方式均设定为“居中”。
（3）设置各科成绩的数据输入有效性，要求成绩输入的范围为0~100，当鼠标停留在成绩的单元格上时，提示信息为“成绩范围：0~100”。
（4）将每门课程中不及格的成绩用红色、加粗字体显示。
（5）用AVERAGE函数求出每位学生的平均分，并用IF函数根据平均分对学生做出评价，60分及以上为“合格”，60分以下为“不合格”。
（6）根据“身份证号码”字段计算每位学生的年龄。
（7）用统计函数完成对“平均分”中各分数段人数统计，把结果分别填入相应的单元格，并根据各等级的比例生成三维饼图，放在统计表下。
（8）插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表”；在“学生信息表”中筛选出平均成绩大于等于85分的学生记录，将其复制到“优秀学生信息表”中，“学生信息表”中的记录还原。
（9）建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制到该表中，按“组别”字段升序排序。
（10）按A、B两个组进行分类汇总，分别计算出A、B组的“数学”、“英语”、“物理”、“化学”平均分。
3、操作分析与过程（1）输入数据表中的原始数据。
（2）选择D3至G23区域，单击“数据”选项卡“数据工具”组中的“数据有效性”命令，弹出如图1-2所示的对话框，选择“设置”选项卡进行“有效性条件”设置，在“输入信息”选项卡中进行相关输入信息的设置。
图1-2 设置“数据有效性”对话框（3）选择D3至G23区域，单击“开始”选项卡“样式”组中的“条件格式”按钮，选择“突出显示单元格规则”→“其他规则”命令，弹出“新建格式规则”对话框，设置单元格数值为“小于60”，再单击“格式”对话框，设置字体为“红色”、“加粗”，如图1-3所示，单击“确定”按钮。
图1-3条件格式设置（4）在单元格H3中输入公式“=A VERAGE(D3:G3)”，计算该学生的平均分，利用Excel 2010的序列填充功能，求出其他学生的平均分，如图1-4所示。
图1-4计算“平均分”（5）在单元格I3中输入公式“=YEAR(NOW())-MID(C3,7,4)”，计算该学生的年龄，利用Excel 2010的序列填充功能，求出其他学生年龄，如图1-5所示。
图1-5计算“年龄”（6）在单元格J3中输入公式“=IF(H3>=60,"合格","不合格")”，计算该学生的等级，利用Excel 2010的序列填充功能，求出其他学生的等级，如图1-6所示。
图1-6计算“等级”（7）在单元格D26中输入公式“=COUNTIF(H3:H23,"<60")”，计算“0-59分”的人数；在单元格E26中输入公式“=COUNTIF(H3:H23,">=60")-COUNTIF(H3:H23,">=85")”，计算“60-84分”的人数；在单元格F26中输入公式“=COUNTIF(H3:H23,">=85")”，计算“≥85分”的人数；在单元格D27中输入公式“=D26/COUNT($H$3:$H$23)”，计算出该等级所占比例，其它依次类推，如图1-7所示。
图1-7统计各分数段人数（8）选中C25至F27，单击“插入”选项卡“图表”组中的“饼图”按钮，选择“三维饼图”命令，根据各等级的比例生成如图1-8所示的三维饼图，放在统计表下。
图1-8各分数段人数所占比例图表（9）插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表”（略）；返回原始工作表，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，各字段旁出现下拉按钮。
单击“平均分”字段的下拉按钮，在弹出的下拉菜单中执行“数字筛选”→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如图1-9所示。
筛选出平均成绩大于等于85分的学生记录，将这些记录复制到“优秀学生信息表”中，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，取消“学生信息表”中的记录筛选。
图1-9设置“自动筛选”对话框（10）建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制到该表中，按“组别”字段升序排序，单击“数据”选项卡“分级显示”组中的“分类汇总”命令，按“组别”进行分类汇总，分别计算出A、B组的“数学”、“英语”、“物理”、“化学”平均分，如图1-10所示，单击“确定”按钮，得到汇总结果。
图1-10设置“分类汇总”对话框1.2毕业设计成绩管理一、项目介绍毕业设计成绩信息记录了毕业设计的基本情况，随着高校办学规模的扩大，各专业学生数量和毕业设计题目数量都在增加，毕业设计（毕业实习）指导教师在学生毕业设计与毕业结束后，需要给学生评定成绩，成绩是毕业设计（毕业实习）整个过程的最终体现。
通过计算机采用Excel进行快速评分与计算，并得出相关的统计信息，可节省教师统计与计算的时间。
通过该案例的学习可掌握Excel 2010工作表多表间的数据选择和表示、多表间的公式与函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。
二、操作数据【实战训练】图1-11基本信息表数据内容【实战训练】图1-12基础分表数据内容三、操作要求（1）以“07010101”为起始学号，在“学号”一列为每位同学填充学号；（2）从身份证号码中自动提取性别填入“性别”一列，提取方法：若第17位为奇数，表示男性，若为偶属，表示女性（假设身份证统一为18位）；（3）从身份证号码中提取出生日期填入“出生日期”一列，格式为“××××年××月××日”，提示：18为身份证号码，7~10位为出生年份（4位），11~12位为出生月份，13~14位为出生日期。
（注意：不能用单元格格式设置）；（4）在“手机短号”一列填充每位同学的手机短号码，填充方法为每一位同学手机长号后四位前加上“66”；（5）在“基础成绩”一列填充每个同学的基础成绩，基础成绩由“基础成绩表”中查找不同设计题目所对应的基础成绩；（6）在“总成绩”一列计算每位同学的总成绩，算法为：总成绩=基础成绩+设计成绩*30%+论文成绩*30%+实习成绩*10%，结果保留1位小数；（7）如果毕业总成绩在85分以上，则为优秀毕业生，在“是否为优秀毕业生”一列填充对应的学生为“优秀毕业生”；（8）如果某学生毕业设计成绩、毕业论文成绩与毕业实习成绩中有一项缺少，则在其“备注”一列填充“成绩缺少，不予毕业”；（9）分别统计末提交毕业设计与毕业论文、未参加毕业实习的同学个数（该项成绩为空则为未提交），分别填入E63、E64、E65；（10）在不改变现在顺序的前提下，统计总成绩前五名的平均总成绩，结果填入E73；（11）对“基本信息表”按“仓库管理系统、人事管理系统、图书管理系统、旅游网站、公司企事业网站”的顺序进行排序；（12）用“合并计算”分别统计出每个毕业设计题目所有同学的设计成绩、论文成绩、实习成绩、总成绩的平均分，结果保留1位小数，并将结果保留在sheet3，并将sheet3重新命名为“不同毕业设计成绩信息”；（13）用数据库函数计算女生优秀毕业生个数，填入E67（提示：自己书写条件区域）；（14）用高级筛选功能筛选出设计成绩、论文成绩、实习成绩、总成绩都及格的学生，放入“各项成绩都及格的学生”工作表；（15）根据“基本信息表”建立数据透视表，显示各个毕业设计题目总成绩分段人数及占总人数的比例，要求：a、页字段为：毕业设计题目；b、行字段为：总成绩分数段；c、数据区为：每个分数段的人数及占该设计题目总人数的百分比；d、放入“各题目总成绩分段人数及比例”工作表；（16）在“不同毕业设计成绩信息表”工作表中，以各个毕业设计题目所有同学的设计成绩、论文成绩、实习成绩、总成绩的平均分制作柱形圆锥图，并加以修饰，结果如下图1-13所示：【实战训练】图1-13平均分柱形圆锥图（17）对“基本信息表”进行设置：a、设置整个工作表字体为10号，行高为16，列为最适合的列宽，对齐方式为水平居中与垂直居中，外边框为双线，内边框为单实线；b、页面设置为横向，纸张为B4，居中方式为水平居中；c、眉中部设置为“07计算机应用技术1班毕业成绩表”，右部为制表日期；页脚右部插入页码；d、设置打印区域为A1:O60，每页都显示标题行；四、操作分析与过程操作要求（1）英文状态下，在A2单元格输入“’07010101”，然后按回车。
把鼠标放在A2单元格填充柄上，如图1-14所示。
双击填充柄填充A3-A60单元格数据或当鼠标变成十字形状时，按着鼠标左键不放，向下填充A3-A60单元格数据。
【实战训练】图1-14 A2单元格填充柄操作要求（2）在C2单元格中输入公式“=IF(MOD(MID(D2,17,1),2)=0,"女","男")”，计算该学生的性别，利用Excel 2010的序列填充功能，求出其他学生年性别。
如图1-15所示。
【实战训练】图1-15计算学生的性别操作要求（3）CONCATENATE 函数可将最多 255 个文本字符串联接成一个文本字符串，MID函数可返回从指定位置开始的特定数目的字符串。
在E2单元格中输入公式“=CONCATENATE(MID(D2,7,4),"年",MID(D2,11,2),"月",MID(D2,13,2),"日")”，计算该学生的出生日期，利用Excel 2010的序列填充功能，求出其他学出生日期，如图1-16所示。
【实战训练】图1-16计算学出生日期也可通过单击菜单“公式”→“插入函数”，插入CONCATENATE函数，在CONCATENATE 函数对话框中，输入如图1-17参数信息，单击确定按钮。