2025年计算机二级MSOffice高级应用考试真题卷（Excel高级数据分析案例）VIP

2025年计算机二级MSOffice高级应用考试真题卷（Excel高级数据分析案例）考试时间：______分钟总分：______分姓名：______一、Excel基本操作与应用要求：熟练掌握Excel的基本操作，包括单元格的编辑、格式设置、公式的应用等。1.在以下单元格中输入公式，计算每个学生的总分：-A1:姓名-B1:语文成绩-C1:数学成绩-D1:英语成绩-E1:总分-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:932.根据以下条件，对数据进行筛选：-语文成绩高于85分-数学成绩低于90分3.对以下数据进行排序：-按总分从高到低排序4.在以下单元格中输入公式，计算每个学生的平均分：-A5:平均分-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:935.在以下单元格中输入公式，计算每个学生的及格率：-A6:及格率-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:936.在以下单元格中输入公式，计算每个学生的优秀率：-A7:优秀率-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:937.在以下单元格中输入公式，计算每个学生的不及格率：-A8:不及格率-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:938.在以下单元格中输入公式，计算每个学生的优秀人数：-A9:优秀人数-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:939.在以下单元格中输入公式，计算每个学生的不及格人数：-A10:不及格人数-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:9310.在以下单元格中输入公式，计算每个学生的及格人数：-A11:及格人数-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:93二、Excel图表制作与应用要求：熟练掌握Excel图表制作，包括柱状图、折线图、饼图等。1.根据以下数据，制作一个柱状图，展示每个学生的总分：-A1:姓名-B1:总分-数据如下：-A2:张三-B2:85-A3:李四-B3:78-A4:王五-B4:922.根据以下数据，制作一个折线图，展示每个学生的成绩趋势：-A1:姓名-B1:语文成绩-C1:数学成绩-D1:英语成绩-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:933.根据以下数据，制作一个饼图，展示每个学生的成绩占比：-A1:姓名-B1:语文成绩-C1:数学成绩-D1:英语成绩-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:934.根据以下数据，制作一个组合图表，展示每个学生的语文、数学、英语成绩：-A1:姓名-B1:语文成绩-C1:数学成绩-D1:英语成绩-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:935.根据以下数据，制作一个散点图，展示每个学生的总分与平均分的关系：-A1:姓名-B1:总分-C1:平均分-数据如下：-A2:张三-B2:85-C2:88-A3:李四-B3:78-C3:82-A4:王五-B4:92-C4:936.根据以下数据，制作一个条形图，展示每个学生的及格人数：-A1:姓名-B1:及格人数-数据如下：-A2:张三-B2:2-A3:李四-B3:1-A4:王五-B4:37.根据以下数据，制作一个环形图，展示每个学生的优秀率：-A1:姓名-B1:优秀率-数据如下：-A2:张三-B2:0.2-A3:李四-B3:0.1-A4:王五-B4:0.38.根据以下数据，制作一个雷达图，展示每个学生的语文、数学、英语成绩：-A1:姓名-B1:语文成绩-C1:数学成绩-D1:英语成绩-数据如下：-A2:张三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:939.根据以下数据，制作一个气泡图，展示每个学生的总分与平均分的关系：-A1:姓名-B1:总分-C1:平均分-数据如下：-A2:张三-B2:85-C2:88-A3:李四-B3:78-C3:82-A4:王五-B4:92-C4:9310.根据以下数据，制作一个面积图，展示每个学生的及格人数：-A1:姓名-B1:及格人数-数据如下：-A2:张三-B2:2-A3:李四-B3:1-A4:王五-B4:3四、Excel数据透视表与数据分析要求：熟练掌握Excel数据透视表的应用，能够进行数据的分类汇总、分析等。1.建立一个数据透视表，展示每个学生的总分、平均分、及格人数。-数据源如下：-A1:姓名-B1:总分-C1:平均分-D1:及格人数-数据如下：-A2:张三-B2:85-C2:88-D2:2-A3:李四-B3:78-C3:82-D3:1-A4:王五-B4:92-C4:93-D4:32.在数据透视表中，对总分进行排序，从高到低。3.在数据透视表中，计算每个科目的平均分。4.在数据透视表中，筛选出总分大于90分的学生。5.在数据透视表中，计算每个学生的及格率。6.在数据透视表中，计算每个学生的优秀率。五、Excel高级函数应用要求：熟练掌握Excel的高级函数，如VLOOKUP、HLOOKUP、SUMIF、COUNTIF等。1.使用VLOOKUP函数，从以下数据中查找张三的总分：-A1:姓名-B1:总分-数据如下：-A2:张三-B2:85-A3:李四-B3:78-A4:王五-B4:922.使用HLOOKUP函数，从以下数据中查找语文成绩大于85分的学生姓名：-A1:姓名-B1:语文成绩-数据如下：-A2:张三-B2:85-A3:李四-B3:78-A4:王五-B4:923.使用SUMIF函数，计算语文成绩大于85分的学生总分。-数据如下：-A2:张三-B2:85-A3:李四-B3:78-A4:王五-B4:924.使用COUNTIF函数，计算数学成绩大于90分的学生人数。-数据如下：-A2:张三-B2:85-A3:李四-B3:78-A4:王五-B4:925.使用VLOOKUP和HLOOKUP函数组合，查找姓名为李四的数学成绩。-数据如下：-A1:姓名-B1:数学成绩-数据如下：-A2:张三-B2:85-A3:李四-B3:90-A4:王五-B4:926.使用SUMIF和COUNTIF函数组合，计算数学成绩大于90分的学生总分和人数。六、Excel数据验证与条件格式要求：熟练掌握Excel的数据验证和条件格式功能，能够对数据进行验证和格式化。1.在以下单元格中设置数据验证，只允许输入1到10之间的整数：-数据如下：-A2:5-A3:15-A4:82.在以下单元格中设置条件格式，当数值大于90时，字体颜色为红色：-数据如下：-A2:85-A3:92-A4:783.在以下单元格中设置数据验证，只允许输入邮箱地址格式：-数据如下：-A2:zhangsan@-A3:lisi@163.com-A4:wangwu@4.在以下单元格中设置条件格式，当数值为空时，背景色为黄色：-数据如下：-A2:-A3:85-A4:925.在以下单元格中设置数据验证，只允许输入日期格式：-数据如下：-A2:2025-01-01-A3:2025-01-15-A4:2025-01-206.在以下单元格中设置条件格式，当数值小于60时，字体颜色为绿色：-数据如下：-A2:58-A3:65-A4:72本次试卷答案如下：一、Excel基本操作与应用1.解析：在E2单元格中输入公式`=SUM(B2:D2)`，将B2、C2、D2单元格的数值相加，得到张三的总分。将此公式向下拖动应用到其他学生。2.解析：在Excel的“数据”选项卡中，点击“筛选”按钮，然后分别对语文成绩和数学成绩进行筛选，筛选条件分别为“大于85”和“小于90”。3.解析：在Excel的“数据”选项卡中，点击“排序”按钮，选择“主要关键字”为“总分”，排序方式为“降序”。4.解析：在E5单元格中输入公式`=AVERAGE(B2:D2)`，将B2、C2、D2单元格的数值求平均值，得到张三的平均分。将此公式向下拖动应用到其他学生。5.解析：在E6单元格中输入公式`=COUNTIF(B2:D2,">=60")/COUNT(B2:D2)`，计算及格人数（分数大于等于60的人数）除以总人数，得到张三的及格率。将此公式向下拖动应用到其他学生。6.解析：在E7单元格中输入公式`=COUNTIF(B2:D2,">=85")/COUNT(B2:D2)`，计算优秀人数（分数大于等于85的人数）除以总人数，得到张三的优秀率。将此公式向下拖动应用到其他学生。7.解析：在E8单元格中输入公式`=COUNTIF(B2:D2,"<60")/COUNT(B2:D2)`，计算不及格人数（分数小于60的人数）除以总人数，得到张三的不及格率。将此公式向下拖动应用到其他学生。8.解析：在E9单元格中输入公式`=COUNTIF(B2:D2,">=85")/COUNT(B2:D2)`，计算优秀人数（分数大于等于85的人数）除以总人数，得到张三的优秀人数。将此公式向下拖动应用到其他学生。9.解析：在E10单元格中输入公式`=COUNTIF(B2:D2,"<60")/COUNT(B2:D2)`，计算不及格人数（分数小于60的人数）除以总人数，得到张三的不及格人数。将此公式向下拖动应用到其他学生。10.解析：在E11单元格中输入公式`=COUNTIF(B2:D2,">=60")/COUNT(B2:D2)`，计算及格人数（分数大于等于60的人数）除以总人数，得到张三的及格人数。将此公式向下拖动应用到其他学生。二、Excel图表制作与应用1.解析：在B2:B5单元格中输入学生的姓名，在C2:C5单元格中输入对应学生的总分，然后在C2单元格中插入柱状图，选择C2:C5单元格作为数据源。2.解析：在B2:B5单元格中输入学生的姓名，在C2:D5单元格中分别输入语文、数学、英语成绩，然后在C2:D5单元格中插入折线图，选择C2:D5单元格作为数据源。3.解析：在B2:B5单元格中输入学生的姓名，在C2:D5单元格中分别输入语文、数学、英语成绩，然后在C2:D5单元格中插入饼图，选择C2:D5单元格作为数据源。4.解析：在B2:B5单元格中输入学生的姓名，在C2:D5单元格中分别输入语文、数学、英语成绩，然后在C2:D5单元格中插入组合图表，选择C2:D5单元格作为数据源，并设置图表类型为“簇状柱形图”和“折线图”。5.解析：在B2:B5单元格中输入学生的姓名，在C2:D5单元格中分别输入总分和平均分，然后在C2:D5单元格中插入散点图，选择C2:D5单元格作为数据源。6.解析：在B2:B5单元格中输入学生的姓名，在C2:C5单元格中输入及格人数，然后在C2:C5单元格中插入条形图，选择C2:C5单元格作为数据源。7.解析：在B2:B5单元格中输入学生的姓名，在C2:C5单元格中输入优秀率，然后在C2:C5单元格中插入环形图，选择C2:C5单元格作为数据源。8.解析：在B2:B5单元格中输入学生的姓名，在C2:D5单元格中分别输入语文、数学、英语成绩，然后在C2:D5单元格中插入雷达图，选择C2:D5单元格作为数据源。9.解析：在B2:B5单元格中输入学生的姓名，在C2:D5单元格中分别输入总分和平均分，然后在C2:D5单元格中插入气泡图，选择C2:D5单元格作为数据源。10.解析：在B2:B5单元格中输入学生的姓名，在C2:C5单元格中输入及格人数，然后在C2:C5单元格中插入面积图，选择C2:C5单元格作为数据源。三、Excel数据透视表与数据分析1.解析：选中数据源，在“插入”选项卡中选择“数据透视表”，在弹出的对话框中指定数据透视表放置的位置，然后按照提示设置数据透视表字段，包括姓名、总分、平均分、及格人数。2.解析：在数据透视表中，选择“总分”字段，然后点击“排序”按钮，选择“降序”。3.解析：在数据透视表中，选择“总分”字段，然后点击“值字段设置”按钮，选择“平均值”作为汇总方式。4.解析：在数据透视表中，选择“总分”字段，然后点击“筛选”按钮，选择“大于90”。5.解析：在数据透视表中，选择“及格人数”字段，然后点击“值字段设置”按钮，选择“计数”作为汇总方式。四、Excel高级函数应用1.解析：在新的单元格中输入公式`=VLOOKUP("张三",A:B,2,FALSE)`，其中A:B是包含姓名和总分的列，2表示返回第二个字段（即总分），FALSE表示进行精确匹配。2.解析：在新的单元格中输入公式`=HLOOKUP(85,A:B,2,FALSE)`，其中A:B是包含姓名和语文成绩的列，2表示返回第二个字段（即语文成绩），FALSE表示进行精确匹配。3.解析：在新的单元格中输入公式`=SUMIF(B:B,">85",C:C)`，其中B:B是包含语文成绩的列