讲解视频
真题练习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电子表格。