真题练习25-Excel电子表格-全国计算机等级考试二级MS Office高级应用与设计考试【汪老师的个人课堂】

讲解视频

真题练习25-Excel电子表格_哔哩哔哩_bilibili

题库下载

全国计算机等级考试题库下载（用电脑下载安装）：请点击

题目要求

李晓玲是某企业的采购部门员工，现在需要使用Excel来分析采购成本并进行辅助决策。根据下列要求，帮助她运用已有的数据完成这项工作。

1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。

2．在“成本分析”工作表的单元格区域F3:F15，使用公式计算不同订货量下的年订货成本，公式为“年订货成本=（年需求量/订货量）×单次订货成本”，计算结果应用货币格式并显示为保留整数。

3．在“成本分析”工作表的单元格区域G3:G15，使用公式计算不同订货量下的年存储成本，公式为“年存储成本=单位年存储成本×订货量×0.5”，计算结果应用货币格式并显示为保留整数。

4．在“成本分析”工作表的单元格区域H3:H15，使用公式计算不同订货量下的年总成本，公式为“年总成本=年订货成本+年储存成本”，计算结果应用货币格式并显示为保留整数。

5．为“成本分析”工作表的单元格区域E2:H15套用一种表格格式，并将表名称修改为“成本分析”；根据表“成本分析”中的数据，在单元格区域J2:Q18中创建图表，图表类型为“带平滑线的散点图”，并根据“图表参考效果.png”中的效果设置图表的标题内容、图例位置、网格线样式、垂直轴和水平轴的大小值、刻度单位和刻度线。

6．如文档“换行样式.png”所示，将工作表“经济订货批量分析”中B2:B5单元格区域的内容分为两行显示并居中对齐（保持字号不变），括号中的内容（含括号）显示于第2行，然后适当调整B列的列宽。

7．在工作表“经济订货批量分析”的C5单元格计算经济订货批量的值，公式为

，计算结果显示为保留整数。

8．在工作表“经济订货批量分析”的单元格区域B7:M27创建模拟运算表，模拟不同的年需求量和单位年储存成本所对应的不同经济订货批量；其中C7:M7为年需求量可能的变化值，B8:B27为单位年储存成本可能的变化值；设置模拟运算结果的数字格式，令其显示为保留整数。

9．对工作表“经济订货批量分析”的单元格区域C8:M27应用条件格式，将所有小于等于750且大于等于650的值所在单元格的底纹设置为红色，字体颜色设置为“白色，背景1”。

10．在工作表“经济订货批量分析”中，将单元格区域C2:C4作为可变单元格，按照下表要求创建方案（最终显示的方案为“需求持平”）。

方案名称	单元格C2	单元格C3	单元格C4
需求下降	10000	600	35
需求持平	15000	500	30
需求上升	20000	450	27

11．在工作表“经济订货批量分析”中，按照下表要求为单元格C2:C5定义名称。

C2	年需求量
C3	单次订货成本
C4	单位年储存成本
C5	经济订货批量

12．在工作表“经济订货批量分析”中，以C5单元格为结果单元格创建方案摘要，并将新生成的“方案摘要”工作表置于工作表“经济订货批量分析”右侧。

13．在“方案摘要”工作表中，将单元格区域B2:G10设置为打印区域，纸张方向设置为横向，缩放比例设置为正常尺寸的200%，打印内容在页面中水平和垂直方向都居中对齐，在页眉正中央添加文字“不同方案比较分析”，并将页眉到上边距的距离值设置为3。

文字解析

第25组

1．打开考生文件夹，然后双击里面的“Excel.xlsx”文件。

2．选中成本分析工作表中的F3单元格，根据公式“年订货成本=（年需求量/订货量）×单次订货成本”，输入公式“=($C$2/E3)*$C$3”。拖动F3单元格的智能填充句柄，直到F15单元格。

3．选中成本分析工作表中的G3单元格，根据公式“年存储成本=单位年存储成本×订货量×0.5”，输入公式“=$C$4*E3*0.5”，拖动G3单元格的智能填充句柄，直到G15单元格。

4．步骤1：选中成本分析工作表中的H3单元格，根据公式“年总成本=年订货成本+年储存成本”，输入公式“=F3+G3”，拖动H3单元格的智能填充句柄，直到H15单元格。

步骤2：选中F3:H15区域，单击右键在快捷菜单中选“设置单元格格式”命令，打开“设置单元格格式”对话框。选择“数字”|“分类”中的“货币”，将“小数位数”设置为0，单击“确定”按钮。

5．步骤1：选中E2:H15单元格区域，单击“开始”|“样式”分组中的“套用表格样式”向下箭头，在列表中选择一种样式（例如：“浅色”中选择“表样式浅色2”）。

步骤2：在“表格工具”|“设计”|“属性”分组中的“表名称”文本框中输入“成本分析”。

步骤3：选中单元格区域E2:H15，单击“插入”|“图表”分组中的“散点图”向下箭头，在其列表中选择“带平滑线的散点图”。

步骤4：选中生成的图表，单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下来按钮，在列表中单击“图表标题”级联菜单中的“图表上方”命令。输入图表标题“采购成本分析”。

步骤5：选中生成的图表，单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中单击“图例”级联菜单中的“底部”命令。

步骤6：选中纵坐轴，单击右键，在快捷菜单中选择“设置坐标轴格式”命令，打开“设置坐标轴格式”对话框。在“坐标轴选项”中设置“边界”中的“最小值”为“0”，“最大值”为“45000”，“单位”中的大”为“9000”，单击“关闭”按钮。

步骤7：选中横坐轴，单击右键，在快捷菜单中选择“设置坐标轴格式”命令，打开“设置坐标轴格式”对话框，在“坐标轴选项”中设置“边界”中的“最小值”为“200”，“最大值”为“1400”，“单位”中的“大”为“300”，单击“关闭”按钮。

步骤8：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下来按钮，在列表中单击“网格线”级联菜单中的“更多横网格线选项”命令，打开“设置主要网格线格式”对话框。

步骤9：选择“线条”中“短划线类型”列表中的“虚线”（第3种线型），单击“关闭”按钮。将图表拖动到单元格区域J2:Q18中。

6．步骤1：选中工作表“经济订货批量分析”的B2单元格中内容，将光标放置在“年需求量”的后面，然后按<Alt+Enter>组合键，实现内容的换行。依次类推，再分别选择B3:B5单元格中的内容，按键盘上的<Alt+Enter>，实现内容的换行。

步骤2：选中B2:B5单元格区域，单击“开始”|“对齐方式”分组中的“居中”按钮。

步骤3：将光标放在B列与C列之间，当光标变为左右的箭头时，拖动鼠标，将B列的宽度调整到合适的位置。

7．步骤1：将光标定位于C5单元格，根据公式经济订货量=2×年需求量×单次订货成本单位年储存成本，在其中输入公式“=SQRT((2*$C$2*$C$3)/$C$4)”，按回车键。

步骤2：选中C5单元格，单击鼠标右键，在快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，在“分类”中选择“数值”，设置“小数位数”为0，单击“确定”按钮。

【知识拓展】SQRT函数

功能：返回正平方根；语法：SQRT(number)；说明：Number 要计算平方根的数。如果参数Number为负值，函数SQRT返回错误值 #Num!。

8．步骤1：选中“C8”单元格，输入公式：=SQRT((2*$C$7*$C$3)/$B8)，然后按回车键。

步骤2：选中“D8”单元格，输入公式：=SQRT((2*$D$7*$C$3)/$B8)，然后按回车键。

步骤3：选中“E8”单元格，输入公式：=SQRT((2*$E$7*$C$3)/$B8)，然后按回车键。

步骤4：选中“F8”单元格，输入公式：=SQRT((2*$F$7*$C$3)/$B8)，然后按回车键。

步骤5：选中“G8”单元格，输入公式：=SQRT((2*$G$7*$C$3)/$B8)，然后按回车键。

步骤6：选中“H8”单元格，输入公式：=SQRT((2*$H$7*$C$3)/$B8)，然后按回车键。

步骤7：选中“I8”单元格，输入公式：=SQRT((2*$I$7*$C$3)/$B8)，然后按回车键。

步骤8：选中“J8”单元格，输入公式：=SQRT((2*$J$7*$C$3)/$B8)，然后按回车键。

步骤9：选中“K8”单元格，输入公式：=SQRT((2*$K$7*$C$3)/$B8)，然后按回车键。

步骤10：选中“L8”单元格，输入公式：=SQRT((2*$L$7*$C$3)/$B8)，然后按回车键。

步骤11：选中“M8”单元格，输入公式：=SQRT((2*$M$7*$C$3)/ $B8)，然后按回车键。

步骤12：选中C8:M8单元格区域，双击M8单元格下方的智能填充句柄，即可完成其他单元格填充。

步骤13：选中C8:M27单元格区域，在“开始”|“数字”分组中多次单击“减少小数位”按钮，直到没有小数点为止。

9．步骤1：选中C8:M27单元格区域，单击“开始”|“样式”分组中的“条件格式”向下箭头，在列表中选择“突出显示单元格规则”|“其它规则”，打开“新建格式规则”对话框。

步骤2：在“选择规则类型”列表中选择“只为包含以下内容的单元格设置格式”，在“编辑规则说明”中的第一个输入框中选中“单元格值”、第二个列表中选择“介于”、第三个输入框中输入“650”、第四个输入框中输入“750”。

步骤3：单击“格式”按钮。选中“填充”选项卡，在背景色中选择“红色”，选中“字体”选项卡中“颜色”下拉列表中的“白色，背景1”。单击两次“确定”按钮。

10．步骤1：单击“数据”|“数据工具”分组中的“模拟分析”下拉按钮，在下拉列表中单击“方案管理器”命令，弹出“方案管理器”对话框。

步骤2：单击“添加”按钮，在打开的“添加方案”对话框中的“方案名称”中输入“需求下降”，在“可变单元格”中选择单元格区域为“C2:C4”，单击“确定”按钮。

步骤3：在弹出的“方案变量值”对话框中，C2单元格后面输入“10000”、C3单元格后面输入“600”、C4单元格中输入“35”，单击“确定”按钮。这就添加完成了一个方案。

步骤4：采用同样的方法创建另外两个方案。

步骤5：单击选中方案中的“需求持平”，单击“显示”按钮即可将“需求持平”方案显示出来。单击“关闭”按钮。

11．选中“C2”单元格，在单元格名称文本框中输入“年需求量”，然后按回车键。选中单元格C3，在单元格名称文本框中输入“单次订货成本”，然后按回车键。选中单元格C4，在单元格名称文本框中输入“单位年储存成本”，然后按回车键。选中单元格C5，在单元格名称文本框中输入“单经济订货批量”，然后按回车键。

12．步骤1：单击“数据”|“数据工具”分组中的“模拟分析”下拉按钮，在下拉列表中单击“方案管理器”命令，弹出“方案管理器”对话框。

步骤2：单击“摘要”按钮，打开“方案摘要”对话框。在报表类型中选中“方案摘要”单选按钮，结果单元格设置为“C5”，单击“确定”按钮。

步骤3：此时在“经济订货批量分析”工作表左侧自动生成了一名为“方案摘要”的工作表。在这个工作表上按住鼠标左键，然后拖动至“经济订货批量分析”工作表右侧，再松开鼠标即可。

13．步骤1：选“方案摘要”工作表中的B2:G10单元格区域。单击“页面布局”|“页面设置”分组中的“打印区域”按钮向下箭头，在列表中选择“设置打印区域”命令。

步骤2：单击“纸张方向”向下箭头，在列表中选择“横向”命令。

步骤3：单击“页面设置”启动器，打开“页面设置”对话框。在“调整为合适大小”分组中设置“缩放比例”为“200%”。单击“页边距”选项卡，在“居中方式”中勾选中“水平”和“垂直”复选框。

步骤4：单击“页眉/页脚”选项卡。单击“自定义页眉”按钮，打开“页眉”对话框。在“中”文本框中输入“不同方案比较分析”，单击“确定”按钮。单击“页边距”选项卡，将“页眉”文本框中的值设置为“3”，单击“确定”按钮。

保存并关闭Excel.xlsx电子表格。