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

2025年MSOffice高级应用计算机二级考试真题卷（Excel数据分析）考试时间：______分钟总分：______分姓名：______一、Excel基本操作与数据处理要求：熟练掌握Excel的基本操作，包括数据输入、格式化、排序、筛选、数据验证等，并能对数据进行简单的统计分析。1.在Excel中创建一个工作簿，包含三个工作表：Sheet1、Sheet2、Sheet3。2.在Sheet1中输入以下数据：|姓名|性别|年龄|学号||----|----|----|----||张三|男|20|20250001||李四|女|22|20250002||王五|男|21|20250003||赵六|女|23|20250004||周七|男|20|20250005|3.在Sheet2中，将Sheet1中的数据复制一份。4.对Sheet2中的数据进行以下操作：a.将性别列的“男”替换为“男同学”，“女”替换为“女同学”。b.对年龄列进行升序排序。c.在姓名列中筛选出“张”字开头的记录。d.对学号列应用数据验证，允许输入数字，输入范围为1-20250005。e.计算年龄列的平均值、最大值、最小值。f.在Sheet2的底部插入一个新行，包含以下数据：|姓名|性别|年龄|学号||----|----|----|----||王老师|男|45|20251001|5.在Sheet3中，将Sheet1中的数据复制一份。6.对Sheet3中的数据进行以下操作：a.将性别列的“男”替换为“男生”，“女”替换为“女生”。b.在姓名列中筛选出“李”字开头的记录。c.对学号列应用数据验证，允许输入数字，输入范围为1-20250005。d.计算年龄列的平均值、最大值、最小值。二、Excel函数与公式应用要求：熟练掌握Excel中的常用函数，并能运用公式解决实际问题。1.在Sheet1中，计算以下数据：|姓名|学号|成绩||----|----|----||张三|20250001|90||李四|20250002|85||王五|20250003|95||赵六|20250004|80||周七|20250005|75|2.使用公式计算以下内容：a.成绩大于90的学生人数。b.成绩低于80的学生人数。c.成绩最高的学生姓名。d.成绩最低的学生姓名。e.成绩平均分。f.成绩标准差。3.在Sheet2中，将Sheet1中的成绩数据复制一份。4.使用公式计算以下内容：a.成绩大于90的学生人数。b.成绩低于80的学生人数。c.成绩最高的学生姓名。d.成绩最低的学生姓名。e.成绩平均分。f.成绩标准差。5.在Sheet3中，将Sheet1中的成绩数据复制一份。6.使用公式计算以下内容：a.成绩大于90的学生人数。b.成绩低于80的学生人数。c.成绩最高的学生姓名。d.成绩最低的学生姓名。e.成绩平均分。f.成绩标准差。三、Excel图表制作与分析要求：熟练掌握Excel中的图表制作方法，并能对数据进行直观分析。1.在Sheet1中，将以下数据输入到表格中：|学科|分数||----|----||数学|80||英语|90||物理|85||化学|95||生物|75|2.在Sheet1中，根据以上数据制作以下图表：a.折线图：展示各学科分数随时间的变化趋势。b.饼图：展示各学科分数在总分数中的占比。c.柱状图：展示各学科分数的对比情况。3.在Sheet2中，将Sheet1中的数据复制一份。4.在Sheet2中，根据以上数据制作以下图表：a.折线图：展示各学科分数随时间的变化趋势。b.饼图：展示各学科分数在总分数中的占比。c.柱状图：展示各学科分数的对比情况。5.在Sheet3中，将Sheet1中的数据复制一份。6.在Sheet3中，根据以上数据制作以下图表：a.折线图：展示各学科分数随时间的变化趋势。b.饼图：展示各学科分数在总分数中的占比。c.柱状图：展示各学科分数的对比情况。四、Excel高级功能应用要求：掌握Excel的高级功能，包括条件格式、数据透视表、宏等，并能运用这些功能提高数据处理效率。1.在Sheet1中，对以下数据设置条件格式：|学号|姓名|成绩||----|----|----||20250001|张三|90||20250002|李四|85||20250003|王五|95||20250004|赵六|80||20250005|周七|75|a.成绩高于90分的记录，背景色设置为绿色。b.成绩低于80分的记录，背景色设置为红色。c.成绩为85分和95分的记录，字体颜色设置为蓝色。2.在Sheet2中，根据Sheet1的数据创建一个数据透视表，包含以下字段：a.学号b.姓名c.成绩d.学科（假设学科信息在另一张工作表中）3.在Sheet3中，使用宏功能自动将Sheet1中的成绩数据按照学号排序，并筛选出成绩大于90分的学生。五、Excel数据分析与展示要求：运用Excel的数据分析工具，对数据进行深入分析，并通过图表进行展示。1.在Sheet1中，对以下销售数据进行分析：|产品|销售额（万元）|销售量（件）||----|--------------|------------||A|100|200||B|150|300||C|200|400||D|250|500||E|300|600|a.计算每个产品的销售额占比。b.计算每个产品的销售量占比。c.制作一个复合饼图，展示每个产品的销售额占比和销售量占比。2.在Sheet2中，根据Sheet1的数据创建一个数据透视表，包含以下字段：a.产品b.销售额（万元）c.销售量（件）3.在Sheet3中，使用图表功能制作以下图表：a.柱状图：展示每个产品的销售额。b.折线图：展示每个产品的销售量随时间的变化趋势。六、Excel数据共享与保护要求：掌握Excel的数据共享和保护方法，确保数据的安全性和可靠性。1.在Sheet1中，设置以下数据共享权限：a.允许所有用户查看数据。b.允许特定用户编辑数据。c.设置密码保护工作簿，防止未授权用户访问。2.在Sheet2中，将Sheet1的数据复制一份，并对以下数据进行加密：a.学号b.姓名c.成绩3.在Sheet3中，设置以下数据保护措施：a.对工作表中的公式进行隐藏。b.对工作表中的公式进行锁定，防止用户修改。c.对工作簿进行数字签名，确保数据来源可靠。本次试卷答案如下：一、Excel基本操作与数据处理1.在Excel中创建一个工作簿，包含三个工作表：Sheet1、Sheet2、Sheet3。解析：打开Excel软件，点击“文件”菜单，选择“新建”，在模板中选择“空白工作簿”，创建三个工作表。2.在Sheet1中输入以下数据：|姓名|性别|年龄|学号||----|----|----|----||张三|男|20|20250001||李四|女|22|20250002||王五|男|21|20250003||赵六|女|23|20250004||周七|男|20|20250005|解析：在Sheet1的A1单元格输入“姓名”，在B1单元格输入“性别”，在C1单元格输入“年龄”，在D1单元格输入“学号”。然后选中A2到D5单元格区域，输入上述数据。3.在Sheet2中，将Sheet1中的数据复制一份。解析：在Sheet1中选中A1到D5单元格区域，右键点击选择“复制”，切换到Sheet2，右键点击任意空白单元格选择“粘贴”。4.对Sheet2中的数据进行以下操作：a.将性别列的“男”替换为“男同学”，“女”替换为“女同学”。解析：选中性别列（B2到B5），点击“开始”选项卡下的“查找和选择”按钮，选择“替换”，在“查找内容”框中输入“男”，在“替换为”框中输入“男同学”，点击“全部替换”。重复此操作替换“女”为“女同学”。b.对年龄列进行升序排序。解析：选中年龄列（C2到C5），点击“数据”选项卡下的“排序”，在“排序依据”中选择“年龄”，点击“确定”。c.在姓名列中筛选出“张”字开头的记录。解析：选中姓名列（A2到A5），点击“开始”选项卡下的“筛选”，点击姓名列的筛选箭头，选择“文本筛选”，在“自定义自动筛选”中输入“张*”，点击“确定”。d.对学号列应用数据验证，允许输入数字，输入范围为1-20250005。解析：选中学号列（D2到D5），点击“数据”选项卡下的“数据验证”，在“设置”选项卡中，选择“整数”，设置“最小值”为1，“最大值”为20250005，点击“确定”。e.计算年龄列的平均值、最大值、最小值。解析：在Sheet2的底部插入一个新行（A6到D6），在A6单元格输入“年龄平均值”，在B6单元格输入公式“=AVERAGE(C2:C5)”，在C6单元格输入“年龄最大值”，在D6单元格输入公式“=MAX(C2:C5)”，在E6单元格输入“年龄最小值”，在F6单元格输入公式“=MIN(C2:C5)”。f.在Sheet2的底部插入一个新行，包含以下数据：|姓名|性别|年龄|学号||----|----|----|----||王老师|男|45|20251001|解析：在Sheet2的底部插入一个新行（A7到D7），输入上述数据。5.在Sheet3中，将Sheet1中的数据复制一份。解析：在Sheet1中选中A1到D5单元格区域，右键点击选择“复制”，切换到Sheet3，右键点击任意空白单元格选择“粘贴”。6.对Sheet3中的数据进行以下操作：a.将性别列的“男”替换为“男生”，“女”替换为“女生”。解析：选中性别列（B2到B5），点击“开始”选项卡下的“查找和选择”按钮，选择“替换”，在“查找内容”框中输入“男”，在“替换为”框中输入“男生”，点击“全部替换”。重复此操作替换“女”为“女生”。b.在姓名列中筛选出“李”字开头的记录。解析：选中姓名列（A2到A5），点击“开始”选项卡下的“筛选”，点击姓名列的筛选箭头，选择“文本筛选”，在“自定义自动筛选”中输入“李*”，点击“确定”。c.对学号列应用数据验证，允许输入数字，输入范围为1-20250005。解析：选中学号列（D2到D5），点击“数据”选项卡下的“数据验证”，在“设置”选项卡中，选择“整数”，设置“最小值”为1，“最大值”为20250005，点击“确定”。d.计算年龄列的平均值、最大值、最小值。解析：在Sheet3的底部插入一个新行（A6到D6），在A6单元格输入“年龄平均值”，在B6单元格输入公式“=AVERAGE(C2:C5)”，在C6单元格输入“年龄最大值”，在D6单元格输入公式“=MAX(C2:C5)”，在E6单元格输入“年龄最小值”，在F6单元格输入公式“=MIN(C2:C5)”。二、Excel函数与公式应用1.在Sheet1中，计算以下数据：|学号|姓名|成绩||----|----|----||20250001|张三|90||20250002|李四|85||20250003|王五|95||20250004|赵六|80||20250005|周七|75|解析：在Sheet1的G2单元格输入“成绩大于90的学生人数”，在G3单元格输入公式“=COUNTIF(E2:E5,">90")”，在H2单元格输入“成绩低于80的学生人数”，在H3单元格输入公式“=COUNTIF(E2:E5,"<80")”，在I2单元格输入“成绩最高的学生姓名”，在I3单元格输入公式“=INDEX(F2:F5,MATCH(MAX(E2:E5),E2:E5,0))”，在J2单元格输入“成绩最低的学生姓名”，在J3单元格输入公式“=INDEX(F2:F5,MATCH(MIN(E2:E5),E2:E5,0))”，在K2单元格输入“成绩平均分”，在K3单元格输入公式“=AVERAGE(E2:E5)”，在L2单元格输入“成绩标准差”，在L3单元格输入公式“=STDEVA(E2:E5)”。2.使用公式计算以下内容：a.成绩大于90的学生人数。解析：使用COUNTIF函数计算成绩大于90的记录数。b.成绩低于80的学生人数。解析：使用COUNTIF函数计算成绩低于80的记录数。c.成绩最高的学生姓名。解析：使用INDEX和MATCH函数找到成绩最高的记录的姓名。d.成绩最低的学生姓名。解析：使用INDEX和MATCH函数找到成绩最低的记录的姓名。e.成绩平均分。解析：使用AVERAGE函数计算成绩的平均值。f.成绩标准差。解析：使用STDEVA函数计算成绩的标准差。3.在Sheet2中，将Sheet1中的成绩数据复制一份。解析：在Sheet1中选中E2到E5单元格区域，右键点击选择“复制”，切换到Sheet2，右键点击任意空白单元格选择“粘贴”。4.使用公式计算以下内容：a.成绩大于90的学生人数。解析：使用COUNTIF函数计算成绩大于90的记录数。b.成绩低于80的学生人数。解析：使用COUNTIF函数计算成绩低于80的记录数。c.成绩最高的学生姓名。解析：使用INDEX和MATCH函数找到成绩最高的记录的姓名。d.成绩最低的学生姓名。解析：使用INDEX和MATCH函数找到成绩最低的记录的姓名。e.成绩平均分。解析：使用AVERAGE函数计算成绩的平均值。f.成绩标准差。解析：使用STDEVA函数计算成绩的标准差。5.在Sheet3中，将Sheet1中的成绩数据复制一份。解析：在Sheet1中选中E2到E5单元格区域，右键点击选择“复制”，切换到Sheet3，右键点击任意空白单元格选择“粘贴”。6.使用公式计算以下内容：a.成绩大于90的学生人数。解析：使用COUNTIF函数计算成绩大于90的记录数。b.成绩低于80的学生人数。解析：使用COUNTIF函数计算成绩低于80的记录数。c.成绩最高的学生姓名。解析：使用INDEX和MATCH函数找到成绩最高的记录的姓名。d.成绩最低的学生姓名。解析：使用INDEX和MATCH函数找到成绩最低的记录的姓名。e.成绩平均分。解析：使用AVERAGE函数计算成绩的平均值。f.成绩标准差。解析：使用STDEVA函数计算成绩的标准差。三、Excel图表制作与分析1.在Sheet1中，将以下数据输入到表格中：|学科|分数||----|----||数学|80||英语|90||物理|85||化学|95||生物|75|解析：在Sheet1的A1单元格输入“学科”，在B1单元格输入“分数”。然后分别输入上述数据。2.在Sheet1中，根据以上数据制作以下图表：a.折线图：展示各学科分数随时间的变化趋势。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“折线图”，选择合适的折线图样式。b.饼图：展示各学科分数在总分数中的占比。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“饼图”，选择合适的饼图样式。c.柱状图：展示各学科分数的对比情况。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“柱状图”，选择合适的柱状图样式。3.在Sheet2中，将Sheet1中的数据复制一份。解析：在Sheet1中选中A1到B6单元格区域，右键点击选择“复制”，切换到Sheet2，右键点击任意空白单元格选择“粘贴”。4.在Sheet2中，根据以上数据制作以下图表：a.折线图：展示各学科分数随时间的变化趋势。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“折线图”，选择合适的折线图样式。b.饼图：展示各学科分数在总分数中的占比。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“饼图”，选择合适的饼图样式。c.柱状图：展示各学科分数的对比情况。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“柱状图”，选择合适的柱状图样式。5.在Sheet3中，将Sheet1中的数据复制一份。解析：在Sheet1中选中A1到B6单元格区域，右键点击选择“复制”，切换到Sheet3，右键点击任意空白单元格选择“粘贴”。6.在Sheet3中，根据以上数据制作以下图表：a.折线图：展示各学科分数随时间的变化趋势。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“折线图”，选择合适的折线图样式。b.饼图：展示各学科分数在总分数中的占比。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“饼图”，选择合适的饼图样式。c.柱状图：展示各学科分数的对比情况。解析：选中B2到B6单元格区域，点击“插入”选项卡下的“柱状图”，选择合适的柱状图样式。四、Excel高级功能应用1.在Sheet1中，对以下数据设置条件格式：|学号|姓名|成绩||----|----|----||20250001|张三|90||20250002|李四|85||20250003|王五|95||20250004|赵六|80||20250005|周七|75|解析：选中成绩列（C2到C5），点击“开始”选项卡下的“条件格式”，选择“新建规则”，选择“使用公式确定要设置格式的单元格”，在“格式值等于以下公式时”框中输入“>90”，点击“格式”，选择合适的背景色，点击“确定”。重复此操作设置成绩低于80分的条件格式。2.在Sheet2中，根据Sheet1的数据创建一个数据透视表，包含以下字段：a.学号b.姓名c.成绩d.学科（假设学科信息在另一张工作表中）解析：在Sheet1中选中A1到D5单元格区域，点击“插入”选项卡下的“数据透视表”，在“创建数据透视表”对话框中，选择“新工作表”，点击“确定”。在数据透视表字段列表中，将“学号”拖到“行”区域，将“姓名”拖到“列”区域，将“成绩”拖到“值”区域，将“学科”拖到“值”区域。3.在Sheet3中，使用宏功能自动将Sheet1中的成绩数据按照学号排序，并筛选出成绩大于90分的学生。解析：在Sheet1中选中E2到E5单元格区域，点击“开发工具”选项卡下的“宏”，选择“录制新宏”，在“宏名”框中输入“自动排序筛选”，点击“确定”。在录制宏的过程中，依次点击“数据”选项卡下的“排序”，选择“按学号升序排序”，然后点击“筛选”，选择“学号”，在“数字筛选”中选择“大于”，输入“90”，点击“确定”。完成录制后，点击“停止录制”，在“宏”对话框中，点击“关闭”。在Sheet1中，点击“开发工具”选项卡下的“宏”，选择“自动排序筛选”，即可执行宏操作。五、Excel数据分析与展示1.在Sheet1中，对以下销售数据进行分析：|产品|销售额（万元）|销售量（件）||----|--------------|------------||A|100|200||B|150|300||C|200|400||D|250|500||E|300|600|解析：在Sheet1的G2单元格输入“每个产品的销售额占比”，在G3单元格