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

视频讲解

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

题目要求

第38套

小郑是某企业财务部门的工作人员，现在需要使用Excel设计财务报销表格。根据下列要求，帮助小郑运用已有的原始数据完成相关工作。

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

2．在“差旅费报销”工作表中完成下列任务：

① 将A1单元格中的标题内容在A1:K1单元格区域中跨列居中对齐（不要合并单元格）。

② 创建一个新的单元格样式，名为“表格标题”，字号为16，颜色为标准蓝色，应用于A1单元格，并适当调整行高。

③ 在单元格区域I3:I22使用公式计算住宿费的实际报销金额，规则如下：

1. 在不同城市每天住宿费报销的最高标准可以从工作表“城市分级”中查询；
2. 每次出差报销的最高额度为相应城市的日住宿标准×出差天数（返回日期-出发日期）；
3. “住宿费-报销金额”取“住宿费-发票金额”与每次出差报销的最高额度两者中的较低者。

④ 在单元格区域J3:J22使用公式计算每位员工的补助金额，计算方法为补助标准×出差天数（返回日期-出发日期），每天的补助标准可以在“职务级别”工作表中查询。

⑤ 在单元格区域K3:K22使用公式计算每位员工的报销金额，报销金额="交通费"+"住宿费-报销金额"+"补助金额"，在K23单元格计算报销金额的总和。

⑥ 在单元格区域I3:I22使用条件格式，对"住宿费-发票金额"大于"住宿费-报销金额"的单元格应用标准红色字体。

⑦ 在单元格区域A3:K22使用条件格式，对出差天数（返回日期-出发日期）大于等于5天的记录行应用标准绿色字体（如果某个单元格中两种条件格式规则发生冲突，优先应用第⑥项中的规则）。

3．在“费用合计”和“车辆使用费报销”工作表中，对A1单元格应用单元格样式“表格标题”，并设置为与下方表格等宽的跨列居中格式。

4．在“费用合计”工作表中完成下列任务：

① 在单元格C4和C5中，分别建立公式，使其值等于“差旅费报销”工作表的单元格K23和“车辆使用费报销”工作表的单元格H21。

② 在单元格C6中使用函数计算单元格C4和C5之和。

③ 在单元格D4中建立超链接，显示的文字为“填写请点击！”，并在单击时可以跳转到工作表“差旅费报销”的A3单元格。

④ 在B2单元格中，建立数据验证规则，可以通过下拉菜单填入以下项目：市场部、物流部、财务部、行政部、采购部，并最终显示文本“市场部”。

⑤ 在单元格D5中，通过函数进行设置，如果单元格B2中的内容为“行政部”或“物流部”，则显示为单击时可以跳转到工作表“车辆使用费报销”A3单元格的超链接，显示的文本为“填写请点击！”，如果是其他部门则显示文本“无需填写！”。

5．在工作表“差旅费报销”和工作表“车辆使用费报销”的A1:C1单元格区域中插入内置的左箭头形状，并在其中输入文本“返回主表”，为形状添加超链接，在单击形状时，可以跳转到“费用合计”工作表的单元格A1。

6．在工作表“差旅费报销”中，保护工作表（不要使用密码，否则整个模块不得分），以便I3:K22单元格区域以及K23单元格可以选中但无法编辑，也无法看到其中的公式，其他单元格都可以正常编辑。

7．进行设置，取消显示工作表标签，且活动工作表为“费用合计”。

文字解析

第38组

1．【解析】打开考生文件夹下的素材文档“Excel.xlsx”。

2．【解析】打开“差旅费报销”工作表

① 步骤1：选中A1:K1单元格区域，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框；

步骤2：单击“对齐”选项卡，在水平对齐中选中“跨列居中”，单击“确定”按钮。

② 步骤1：单击【开始】|【样式】分组中的其他按钮，在下拉列表中单击“新建单元格样式”命令，打开“样式”对话框；

步骤2：将样式名称改为“表格标题”，单击“格式”按钮，打开“设置单元格格式”对话框，单击“字体”选项卡，设置字号为“16”、字体颜色为“蓝色”，单击“确定”按钮，再次单击“确定”按钮；

步骤3：选中A1单元格，单击【样式】分组中的“表格标题”样式；

步骤4：拖动第一行下边框，适当加大行高。

③ 步骤1：选中“I3”单元格，用VLOOKUP函数查找对应城市的每天住宿费报销的最高标准：=VLOOKUP(F3,城市分级!$A$1:$B$356,2,0)；

步骤2：使用上一步算出的日住宿标准*出差天数：=VLOOKUP(F3,城市分级!$A$1:$B$356,2,0)*(E3-D3)；

步骤3：使用if函数判断计算出的“住宿费-报销金额”与“住宿费-发票金额”哪个底，选取低的作为报销金额：=IF(VLOOKUP(F3,城市分级!$A$1:$B$356,2,0)*(E3-D3)<H3,VLOOKUP(F3,城市分级!$A$1:$B$356,2,0)*(E3-D3),H3)；

步骤4：拖动I3智能填充柄到I22，完成其他行计算。

④ 步骤1：选中“J3”单元格，用VLOOKUP函数查找每位员工的补助金额：=VLOOKUP(C3,职务级别!$A$1:$B$5,2,0)；

步骤2：使用上一步算出的每位员工的补助金额*出差天数：=VLOOKUP(C3,职务级别!$A$1:$B$5,2,0)*(E3-D3)；

步骤3：拖动J3智能填充柄到J22，完成其他行计算。

⑤ 步骤1：选中“K3”单元格，输入计算报销金额公式：=G3+I3+J3；

步骤2：拖动K3智能填充柄到K22，完成其他行计算；

步骤3：选中“K23”单元格，单击【开始】|【编辑】分组中的“自动求和”按钮，计算出报销金额的总和。

⑥ 步骤1：选中I3:I22单元格区域，单击【开始】|【样式】分组中的“条件格式”下拉按钮，在列表中单击“新建规则”命令，打开“新建格式规则”对话框；

步骤2：在“选择规则类型”中选中“使用公式确定要设置格式的单元格”，在“为符合次公式的值设置格式”中输入公式“=$H3>$I3”，单击“格式”按钮，打开“设置单元格格式”对话框；

步骤3：在“字体”选项卡下设置字体颜色为“红色”，单击“确定”按钮，再次单击“确定”按钮，然后单击“应用”按钮，在单击“确定”按钮。

⑦ 步骤1：选中A3:K22单元格区域，单击【开始】|【样式】分组中的“条件格式”下拉按钮，在列表中单击“新建规则”命令，打开“新建格式规则”对话框；

步骤2：在“选择规则类型”中选中“使用公式确定要设置格式的单元格”，在“为符合次公式的值设置格式”中输入公式“=($E3-$D3)>=5”，单击“格式”按钮，打开“设置单元格格式”对话框；

步骤3：在“字体”选项卡下设置字体颜色为“绿色”，单击“确定”按钮，再次单击“确定”按钮。

步骤4：在“条件格式规则管理器”对话框中，选中新插入的规则，然后单击“下移”按钮，将其移动到最下面，然后单击“应用”按钮，在单击“确定”按钮。

3．【解析】步骤1：选中“费用合计”工作表中的A1单元格，单击【开始】|【样式】分组中的“表格标题”样式；

步骤2：选中A1:F1单元格区域，单击【开始】|【数字】分组中的对话框器启动器，打开“设置单元格格式”对话框，在“对齐”选项卡中，设置水平对齐为“跨列居中”；

步骤3：选中“车辆使用费报销”工作表中的A1单元格，单击【开始】|【样式】分组中的“表格标题”样式；

步骤4：选中A1:H1单元格区域，单击【开始】|【数字】分组中的对话框器启动器，打开“设置单元格格式”对话框，在“对齐”选项卡中，设置水平对齐为“跨列居中”。

4．【解析】进入“费用合计”工作表。

① 步骤1：在C4单元格中输入公式：=差旅费报销!K23；

步骤2：在C5单元格中输入公式：=车辆使用费报销!H21。

② 在C6单元格中输入公式：=SUM(C4:C5)。

③ 步骤1：选中D4单元格，单击【插入】|【链接】分组中的“链接”按钮，打开“插入超链接”对话框；

步骤2：选中“链接到”中的“本文档中的位置”，在“或在此文档中选择一个位置”中选中“差旅费报销”，将“要显示的文字”中内容修改为“填写请点击！”，将“请键入单元格引用”中内容修改为“A3”，单击“确定”按钮。

④ 步骤1：选中B2单元格，单击【数据】|【数据工具】分组中的“数据验证”下拉按钮，在列表中单击“数据验证”命令，打开“数据验证”对话框；

步骤2：在“允许”中选中“序列”，然后再“来源”中输入“市场部,物流部,财务部,行政部,采购部”，单击“确定”按钮；

步骤3：单击B2单元格的下拉按钮，选中“市场部”。

⑤ 本题可使用公式：=IF(OR(B2="行政部",B2="物流部"),HYPERLINK("#车辆使用费报销!A3","填写请点击！"),"无需填写！")。

其中：OR函数确定两个满足条件，即B2单元格是否选中了“行政部或物流部”；IF函数用于判定是否在D5单元格中显示相应内容；HYPERLINK函数用于显示超链接到工作表“车辆使用费报销”A3单元格。

5．【解析】步骤1：选中“差旅费报销”工作表，单击【插入】|【插图】分组中的“形状”下拉按钮，在列表中选中“箭头汇总”中的“箭头：左”形状，然后在A1:C1单元格区域内回执一个向左箭头形状；

步骤2：选中新绘制的箭头形状，然后右键单击形状，在弹出菜单中单击“编辑文字”命令，然后输入内容“返回主表”，在【开始】|【对齐方式】中选中水平居中和垂直居中按钮；

步骤3：单击【插入】|【链接】分组中的“链接”按钮，打开“插入超链接”对话框，选中“链接到”中的“本文档中的位置”，在“或在此文档中选择一个位置”中选中“费用合计”，将“请键入单元格引用”中内容修改为“A1”，单击“确定”按钮；

步骤4：选中“车辆使用费报销”工作表，单击【插入】|【插图】分组中的“形状”下拉按钮，在列表中选中“箭头汇总”中的“箭头：左”形状，然后在A1:C1单元格区域内回执一个向左箭头形状；

步骤5：选中新绘制的箭头形状，然后右键单击形状，在弹出菜单中单击“编辑文字”命令，然后输入内容“返回主表”，在【开始】|【对齐方式】中选中水平居中和垂直居中按钮；

步骤6：单击【插入】|【链接】分组中的“链接”按钮，打开“插入超链接”对话框，选中“链接到”中的“本文档中的位置”，在“或在此文档中选择一个位置”中选中“费用合计”，将“请键入单元格引用”中内容修改为“A1”，单击“确定”按钮。

6．【解析】步骤1：选中“差旅费报销”工作表中的I3:K22单元格区域以及K23单元格，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框，在“保护”选项卡中选中“隐藏”复选框，单击“确定”按钮；

步骤2：单击【审阅】|【保护】分组中的“允许编辑区域”按钮，打开“允许用户编辑区域”对话框，单击“新建”按钮，打开“新区域”对话框，在“引用单元格”中选中“A3:H22”单元格区域，单击“确定”按钮，返回“允许用户编辑区域”对话框；

步骤3：采用同样的方法，设置除I3:K22单元格区域以及K23单元格外其他单元格区域允许编辑；

步骤4：单击“保护工作表”按钮，打开“保护工作表”对话框，在“允许次工作表的所有用户进行”中选中“选定锁定单元格”和“选定解除锁定的单元格”复选框，单击“确定”按钮。

7．【解析】步骤1：选中“费用合计”工作表作为当前工作表；

步骤2：单击【文件】|【选项】命令，打开“Excel选项”对话框，在左侧选中“高级”，在右侧取消“此工作簿的显示选项”分类中的“显示工作表标签”复选框的选中，单击“确定”按钮。

保存并关闭Excel.xlsx工作簿。