2025年MSOffice高级应用计算机二级考试试卷：Excel数据透视表与图表优化应用试题

2025年MSOffice高级应用计算机二级考试试卷：Excel数据透视表与图表优化应用试题考试时间：______分钟总分：______分姓名：______一、Excel数据透视表与图表优化应用操作题要求：请根据以下要求，在Excel中完成数据透视表与图表的创建、编辑和优化。1.在Excel中打开一个包含销售数据的表格，包含以下字段：产品名称、销售日期、销售额、销售区域。2.创建一个数据透视表，包含以下字段：-报表筛选：销售日期-行：产品名称-列：销售区域-值：销售额（求和）3.修改数据透视表，添加以下计算字段：-计算字段名称：同比增长率，计算公式为：（当前年销售额/上一年销售额）-1-计算字段名称：环比增长率，计算公式为：（当前月销售额/上一月销售额）-14.优化数据透视表，设置以下格式：-销售额：使用货币格式，保留两位小数-同比增长率：使用百分比格式，保留两位小数-环比增长率：使用百分比格式，保留两位小数5.创建一个柱形图，以产品名称为横轴，销售额为纵轴。6.修改柱形图，设置以下格式：-图例：显示在图表右侧-标题：销售额对比图-横轴标题：产品名称-纵轴标题：销售额7.将数据透视表和柱形图保存为一个新的Excel工作簿。二、Excel高级图表制作与应用题要求：请根据以下要求，在Excel中完成高级图表的制作与应用。1.在Excel中打开一个包含学生成绩数据的表格，包含以下字段：姓名、科目、成绩、班级。2.创建一个组合图表，包含以下元素：-柱形图：以班级为横轴，成绩为纵轴-折线图：以姓名为横轴，成绩为纵轴3.修改组合图表，设置以下格式：-图例：显示在图表下方-标题：学生成绩分析图-横轴标题：姓名-纵轴标题：成绩-柱形图与折线图颜色：使用不同颜色区分4.创建一个饼图，展示各科目成绩分布情况。5.修改饼图，设置以下格式：-标题：各科目成绩分布图-标签：显示在饼图外侧-颜色：使用不同颜色区分各科目6.将数据透视表、组合图表和饼图保存为一个新的Excel工作簿。三、Excel公式与函数应用题要求：请根据以下要求，在Excel中完成公式与函数的应用。1.在Excel中打开一个包含学生成绩数据的表格，包含以下字段：姓名、科目、成绩、班级。2.使用公式计算以下结果：-计算每位学生的平均成绩-计算每位学生的最高成绩-计算每位学生的最低成绩3.使用函数计算以下结果：-使用SUM函数计算所有学生的总成绩-使用AVERAGE函数计算所有学生的平均成绩-使用MAX函数计算所有学生的最高成绩-使用MIN函数计算所有学生的最低成绩4.修改公式与函数，设置以下格式：-成绩：使用分数格式，保留两位小数-总成绩、平均成绩、最高成绩、最低成绩：使用分数格式，保留两位小数5.将计算结果保存为一个新的Excel工作簿。四、Excel宏与VBA编程基础应用题要求：请根据以下要求，在Excel中使用宏和VBA编写程序，实现以下功能。1.编写一个宏，用于将当前工作表中所有单元格的字体设置为“宋体”，字号为12。2.使用VBA编写程序，实现以下功能：-当用户在单元格A1输入数据时，自动在单元格A2显示该数据的平方值。-当用户在单元格B1输入数据时，自动在单元格B2显示该数据的立方值。3.将编写的宏和VBA程序保存为一个新的Excel工作簿。五、Excel高级数据验证与保护应用题要求：请根据以下要求，在Excel中设置数据验证和保护功能。1.在Excel中打开一个包含员工信息的表格，包含以下字段：姓名、部门、职位、入职日期、离职日期。2.对“部门”字段设置数据验证，要求只能选择以下部门：财务部、销售部、人力资源部、技术部。3.对“入职日期”和“离职日期”字段设置数据验证，要求输入的日期格式为“YYYY-MM-DD”，并且离职日期不能早于入职日期。4.对整个工作表设置保护，允许用户编辑以下字段：姓名、职位、入职日期、离职日期。5.将设置数据验证和保护功能后的工作表保存为一个新的Excel工作簿。六、Excel条件格式与数据排序应用题要求：请根据以下要求，在Excel中设置条件格式和数据排序。1.在Excel中打开一个包含学生成绩数据的表格，包含以下字段：姓名、科目、成绩、班级。2.对“成绩”字段设置条件格式，要求：-成绩大于等于90分的单元格背景色设置为绿色-成绩在80分至89分之间的单元格背景色设置为黄色-成绩低于80分的单元格背景色设置为红色3.对整个工作表进行数据排序，按照“班级”字段升序排列，若班级相同，则按照“成绩”字段降序排列。4.将设置条件格式和数据排序后的工作表保存为一个新的Excel工作簿。本次试卷答案如下：一、Excel数据透视表与图表优化应用操作题1.在Excel中打开一个包含销售数据的表格，包含以下字段：产品名称、销售日期、销售额、销售区域。2.创建一个数据透视表，包含以下字段：-报表筛选：销售日期-行：产品名称-列：销售区域-值：销售额（求和）3.修改数据透视表，添加以下计算字段：-计算字段名称：同比增长率，计算公式为：（当前年销售额/上一年销售额）-1-计算字段名称：环比增长率，计算公式为：（当前月销售额/上一月销售额）-14.优化数据透视表，设置以下格式：-销售额：使用货币格式，保留两位小数-同比增长率：使用百分比格式，保留两位小数-环比增长率：使用百分比格式，保留两位小数5.创建一个柱形图，以产品名称为横轴，销售额为纵轴。6.修改柱形图，设置以下格式：-图例：显示在图表右侧-标题：销售额对比图-横轴标题：产品名称-纵轴标题：销售额7.将数据透视表和柱形图保存为一个新的Excel工作簿。解析：1.打开Excel，选择“数据”选项卡，点击“数据透视表”。2.在“创建数据透视表”对话框中，选择数据源区域，点击“确定”。3.在“数据透视表字段列表”中，将“销售日期”拖到“报表筛选”区域，将“产品名称”拖到“行”区域，将“销售区域”拖到“列”区域，将“销售额”拖到“值”区域。4.在“值”区域，点击“插入字段”按钮，选择“计算字段”，输入字段名称和公式，点击“确定”。5.在“值”区域，右键点击“销售额”，选择“设置单元格格式”，在“数字”选项卡中选择“货币”，点击“确定”。6.在“值”区域，右键点击“同比增长率”，选择“设置单元格格式”，在“数字”选项卡中选择“百分比”，点击“确定”。7.在“值”区域，右键点击“环比增长率”，选择“设置单元格格式”，在“数字”选项卡中选择“百分比”，点击“确定”。8.选择“插入”选项卡，点击“柱形图”，选择“簇状柱形图”，点击“确定”。9.右键点击柱形图，选择“设置图表格式”，在“图表元素”中勾选“图例”，点击“确定”。10.在图表上右键点击，选择“图表工具”，点击“设计”，在“图表样式”中选择一个合适的样式，点击“确定”。11.保存工作簿。二、Excel高级图表制作与应用题1.在Excel中打开一个包含学生成绩数据的表格，包含以下字段：姓名、科目、成绩、班级。2.创建一个组合图表，包含以下元素：-柱形图：以班级为横轴，成绩为纵轴-折线图：以姓名为横轴，成绩为纵轴3.修改组合图表，设置以下格式：-图例：显示在图表下方-标题：学生成绩分析图-横轴标题：姓名-纵轴标题：成绩-柱形图与折线图颜色：使用不同颜色区分4.创建一个饼图，展示各科目成绩分布情况。5.修改饼图，设置以下格式：-标题：各科目成绩分布图-标签：显示在饼图外侧-颜色：使用不同颜色区分各科目6.将数据透视表、组合图表和饼图保存为一个新的Excel工作簿。解析：1.打开Excel，选择“数据”选项卡，点击“数据透视表”。2.在“创建数据透视表”对话框中，选择数据源区域，点击“确定”。3.在“数据透视表字段列表”中，将“班级”拖到“行”区域，将“成绩”拖到“值”区域。4.右键点击“值”区域，选择“插入字段”按钮，选择“计算字段”，输入字段名称和公式，点击“确定”。5.选择“插入”选项卡，点击“组合图表”，选择“簇状柱形图+折线图”，点击“确定”。6.右键点击组合图表，选择“设置图表格式”，在“图表元素”中勾选“图例”，点击“确定”。7.在图表上右键点击，选择“图表工具”，点击“设计”，在“图表样式”中选择一个合适的样式，点击“确定”。8.选择“插入”选项卡，点击“饼图”，选择“三维饼图”，点击“确定”。9.右键点击饼图，选择“设置图表格式”，在“图表元素”中勾选“数据标签”，点击“确定”。10.保存工作簿。三、Excel公式与函数应用题1.在Excel中打开一个包含学生成绩数据的表格，包含以下字段：姓名、科目、成绩、班级。2.使用公式计算以下结果：-计算每位学生的平均成绩-计算每位学生的最高成绩-计算每位学生的最低成绩3.使用函数计算以下结果：-使用SUM函数计算所有学生的总成绩-使用AVERAGE函数计算所有学生的平均成绩-使用MAX函数计算所有学生的最高成绩-使用MIN函数计算所有学生的最低成绩4.修改公式与函数，设置以下格式：-成绩：使用分数格式，保留两位小数-总成绩、平均成绩、最高成绩、最低成绩：使用分数格式，保留两位小数5.将计算结果保存为一个新的Excel工作簿。解析：1.在Excel中打开学生成绩数据表格。2.在每个学生的成绩下方，使用公式计算平均成绩，例如：=AVERAGE(C2:C10)。3.使用公式计算每个学生的最高成绩，例如：=MAX(C2:C10)。4.使用公式计算每个学生的最低成绩，例如：=MIN(C2:C10)。5.使用SUM函数计算所有学生的总成绩，例如：=SUM(C2:C10)。6.使用AVERAGE函数计算所有学生的平均成绩，例如：=AVERAGE(C2:C10)。7.使用MAX函数计算所有学生的最高成绩，例如：=MAX(C2:C10)。8.使用MIN函数计算所有学生的最低成绩，例如：=MIN(C2:C10)。9.修改公式格式，将成绩、总成绩、平均成绩、最高成绩、最低成绩设置为分数格式，保留两位小数。10.保存工作簿。四、Excel宏与VBA编程基础应用题1.编写一个宏，用于将当前工作表中所有单元格的字体设置为“宋体”，字号为12。2.使用VBA编写程序，实现以下功能：-当用户在单元格A1输入数据时，自动在单元格A2显示该数据的平方值。-当用户在单元格B1输入数据时，自动在单元格B2显示该数据的立方值。3.将编写的宏和VBA程序保存为一个新的Excel工作簿。解析：1.打开Excel，选择“视图”选项卡，点击“宏”，选择“录制宏”。2.在“录制宏”对话框中，输入宏名称，选择保存位置，点击“确定”。3.在工作表中，选择所有单元格，右键点击，选择“设置单元格格式”，在“字体”选项卡中选择“宋体”，字号设置为12，点击“确定”。4.点击“停止录制”按钮，关闭“录制宏”对话框。5.使用VBA编写程序，代码如下：SubSetFont()DimwsAsWorksheetSetws=ActiveSheetws.Cells.Font.Name="宋体"ws.Cells.Font.Size=12EndSub6.在VBA编辑器中，插入一个新模块，将上述代码复制粘贴到模块中。7.使用VBA编写程序，实现A1输入数据时自动在A2显示平方值，代码如下：PrivateSubWorksheet_Change(ByValTargetAsRange)IfTarget.Address="$A$1"ThenTarget.Offset(1,0).Value=Target.Value^2EndIfEndSub8.在VBA编辑器中，选择“视图”选项卡，点击“工作表”，选择A1所在的单元格。9.使用VBA编写程序，实现B1输入数据时自动在B2显示立方值，代码如下：PrivateSubWorksheet_Change(ByValTargetAsRange)IfTarget.Address="$B$1"ThenTarget.Offset(1,0).Value=Target.Value^3EndIfEndSub10.在VBA编辑器中，选择“视图”选项卡，点击“工作表”，选择B1所在的单元格。11.保存工作簿。五、Excel高级数据验证与保护应用题1.在Excel中打开一个包含员工信息的表格，包含以下字段：姓名、部门、职位、入职日期、离职日期。2.对“部门”字段设置数据验证，要求只能选择以下部门：财务部、销售部、人力资源部、技术部。3.对“入职日期”和“离职日期”字段设置数据验证，要求输入的日期格式为“YYYY-MM-DD”，并且离职日期不能早于入职日期。4.对整个工作表设置保护，允许用户编辑以下字段：姓名、职位、入职日期、离职日期。5.将设置数据验证和保护功能后的工作表保存为一个新的Excel工作簿。解析：1.打开Excel，选择“数据”选项卡，点击“数据验证”。2.在“设置”选项卡中，选择“允许”为“序列”，在“来源”中输入“财务部,销售部,人力资源部,技术部”，点击“确定”。3.对“入职日期”和“离职日期”字段，选择“数据验证”，在“设置”选项卡中，选择“允许”为“日期”，在“数据”中选中“介于”，在“开始日期”和“结束日期”中输入“1900-01-01”和当前日期，点击“确定”。4.在“数据验证”中，选择“出错警告”选项卡，根据需要设置错误警告内容和样式，点击“确定”。5.选择“审阅”选项卡，点击“保护工作