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:f46400. 数据筛选高级筛选:【列表区域】：$A$1:$I$46，【条件区域】：$K$8:$L$10， 确定。所用知识点：高级筛选的方法，注意：列表区域和条件区域的选择。7. 选中整个区域，先进行排序，后汇总：数据分类汇总高级筛选：分类字段【专业】，汇总方法【求和】，选定汇总项【总分】确定。所用知识点：分类汇总的方法。注意：先排序，后汇总。第二题:在一个工作表中，输入一个班级的英语成绩单，要求进行如下操作：（1）定义成绩的有效范围为0-100，设置输入信息及出错警告（2）统计及格与不及格人数，并圈释不及格的数据（3）设置学号为12位的数（4）用countif函数设置学号的唯一性，如有重复录入的学生，用黄色标记重复录入的学生（5）使用条件格式将成绩大于90分的学生用绿色显示（6）绘制直方图1. 数据有效性：【设置】：允许:整数，数据:介于0100，【出错警告】，勾选警告确定。 所用知识点：数据有效性及出错警告的设置。2. 及格人数：选中D5单元格，输入函数公式：=countif(B2:B25,=60),按Enter。 不及格人数：选中E5单元格，输入函数公式：=countif(B2:B25,1】，格式【黄色】确定。所用知识点：条件格式的用法及countif函数的用法.5. 选中成绩列单元格，格式条件格式【单元格数值】【大于】【90】，格式【绿色】确定.所用知识点:条件格式的设置。6. 选中整个区域，点工具栏中的【表格向导】，在图表类型中选择图形类型，点击“下一步”，出现图2，点“下一步”，出现图3，再点“下一步”，在图4中选择：【作为新工作表插入】，最后点击【完成】。 图1图2 图3图4 所用知识点：直方图的绘制方法。三 、某商场四季度产品销售情况表如下，请用合适的公式填空，写明详细操作过程。售货员姓名产品10月11月12月销售总额销量总额单价销量销售额单价销量销售额单价销量销售额王五彩电301035203010李开空调102015202020黄二电冰箱201025302520张三中微波炉301025103530黄明彩电102015201530左拉空调102015201530国轲电冰箱202015102520具体步骤： 【销售量】：同时选中E5:E11， H5:H11， K5:K11,在K5单元格内输入：=I5*j5, 按Ctrl+Enter。 【销量总额】：选中L5：L11单元格，输入公式：=E5:E11+H5:H11+K5:K11,按Ctrl+Shift+Enter.【销售总量】：选中M