(Excel高级应用作业.docVIP

Excel 高 级 应 用 第一题：输入三个专业（计算机、机械、测绘）的45个同学的5门成绩（大语、高数、 英语、计算机、物理），进行下列操作： （1）求每个人的总分及平均分（注：各科成绩均为百分制） （2）按专业不同排名，相同专业内按总成绩由高到低排名 （3）统计所有男生和女生人数 （4）将高数成绩按优（90-100）、良（80-90）、中（70-80）、及格（60-70）和差（0-60）划分等级 （5）通过名字的指定，能直接查询某一同学的英语成绩。 （6）筛选出计算机专业或者总分400的学生 （7）按专业对学生的总分求和进行一级分类汇总 1. 总分: 选中H列，输入公式= C2:C46+D2:D46+E2:E46+F2:F46+G2:G46， 按Ctrl+Shift+Enter。 平均分：选中I列，输入公式=H2:H46/5,按Ctrl+Shift+Enter。 所用知识点：相对引用，多行多列的对应相加。 2. 选中整个表格，点“数据 ——排序——关键字选择【专业】，次关键字【总分】， 降序——确定”。 所用知识点：按关键字，次关键字来给数据的排序。 3. 男生人数：选定L2单元格，输入公式：=countif(B2:B46,“男”)，按Enter。 女生人数：选定M2单元格，输入公式：=countif(B2:B46,“女”)，按Enter。 所用知识点：用countif函数统计特定数据的个数。 4. 选中K2:k46区域，输入公式=if(f2:f46=80,良,if(f2:f46=70,中,if（f2:f46=60, 及格,if(f2:f4660,差））））)，按Ctrl+Shift+Enter。 所用知识点：If函数的应用。 5. 选中区域，在编辑栏左端输入：刘威 英语——按Enter。 所用知识点：名字的指定及按名字查询数据的方法。 6. 在K8,K9单元格分别输入专业，计算机，在L8，L10单元格输入总分，400. 数据——筛选——高级筛选:【列表区域】：$A$1:$I$46，【条件区域】：$K$8:$L$10， ——确定。 所用知识点：高级筛选的方法，注意：列表区域和条件区域的选择。 7. 选中整个区域，先进行排序，后汇总：数据——分类汇总——高级筛选： 分类字段【专业】，汇总方法【求和】，选定汇总项【总分】——确定。 所用知识点：分类汇总的方法。注意：先排序，后汇总。 第二题:在一个工作表中，输入一个班级的英语成绩单，要求进行如下操作： （1）定义成绩的有效范围为0-100，设置输入信息及出错警告 （2）统计及格与不及格人数，并圈释不及格的数据 （3）设置学号为12位的数 （4）用countif函数设置学号的唯一性，如有重复录入的学生，用黄色标记重复录入的学生 （5）使用条件格式将成绩大于90分的学生用绿色显示 （6）绘制直方图 1. 数据——有效性：【设置】：[允许]:整数，[数据]:介于0——100，【出错警告】， 勾选警告——确定。 所用知识点：数据有效性及出错警告的设置。 2. 及格人数：选中D5单元格，输入函数公式：=countif(B2:B25,=60),按Enter。 不及格人数：选中E5单元格，输入函数公式：=countif(B2:B25,60),按Enter。 圈释不及格人的分数：选中英语成绩的区域C2:C25，然后选数据的有效性，在设置中的允许选：整数，数据选：大于，最小值为59，最后确定，再选工具中的公式审核，再选显示公式审核工具栏，最后选圈释无效数据 。如图 所用知识点：用countif函数进行统计和圈释数据方法。 3. 选中B2:B25,点鼠标右键——设置单元格格式——数字【文本】——确定 输入学号。 所用知识点：数值的输入，注意所输的数值的格式，具体的在单元格格式内设置。 4. 选中学号列单元格，格式——条件格式——【公式】输入函数 【=countif($B$2:$B$25,$B2)1】，格式【黄色】——确定。 所用知识点：条件格式的用法及countif函数的用法. 5. 选中成绩列单元格，格式——条件格式——【单元格数值】【大于】【90】，格式【绿色】——确定. 所用知识点:条件格式的设置。 6. 选中整个区域，点工