2016年3月计算机二级excel答案及解析

第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须都保存在考生文件夹下。 在考生文件夹下打开文档EXCEL.XLSX。 【背景素材】

财务部助理小王需要向主管汇报2013年度公司差旅报销情况，现在请按照如下需求，在EXCEL.XLSX文档中完成工作：

（1）在\费用报销管理\工作表\日期\列的所有单元格中，标注每个报销日期属于星期几，例如日期为\年1月20日\的单元格应显示为 \年1月20日 星期日\，日期为\年1月21日\的单元格应显示为\年1月21日 星期一\。

（2）如果\日期\列中的日期为星期六或星期日，则在\是否加班\列的单元格中显示\是\，否则显示\否\（必须使用公式）。

（3）使用公式统计每个活动地点所在的省份或直辖市，并将其填写在\地区\列所对应的单元格中，例如\北京市\、\浙江省\。 （4）依据\费用类别编号\列内容，使用VLOOKUP函数，生成\费用类别\列内容。对照关系参考\费用类别\工作表。

（5）在\差旅成本分析报告\工作表B3单元格中，统计2013年第二季度发生在北京市的差旅费用总金额。

（6）在\差旅成本分析报告\工作表B4单元格中，统计2013年员工钱顺卓报销的火车票费用总额。

（7）在\差旅成本分析报告\工作表B5单元格中，统计2013年差旅费用中，飞机票费用占所有报销费用的比例，并保留2位小数。

（8）在\差旅成本分析报告\工作表B6单元格中，统计2013年发生在周末（星期六和星期日）的通讯补助总金额。

答疑：（1）【解题步骤】

步骤1：打开考生文件夹下的EXCEL.XLSX。

步骤2：在\费用报销管理\工作表中，选中\日期\数据列，单击鼠标右键，在弹出的快捷菜单中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。切换至\数字\选项卡，在\分类\列表框中选择\自定义\命令，在右侧的\示例\组中\类型\列表框中输入\年\月\日\。设置完毕后单击\确定\按钮即可。 （2）【解题步骤】

步骤：在\费用报销管理\工作表的H3单元格中输入\是\否\， 表示在星期六或者星期日情况下显示\是\，否则显示\否\，按\键确认。然后向下填充公式到最后一个日期即可完成设置。 （3）【解题步骤】

步骤：在\费用报销管理\工作表的D3单元格中输入\， 表示取当前文字左侧的前三个字符，按\键确认。然后向下填充公式到最后一个日

期即可完成设置。 （4）【解题步骤】

步骤：在\费用报销管理\工作表的F3单元格中输入\费用类别!$A$3:$B$12,2,FALSE)\，按\后完成\费用类别\的填充。然后向下填充公式到最后一个日期即可完成设置。 （5）【解题步骤】

步骤：选中A列单元格，在【编辑】选项组中，单击\排序和筛选\下拉菜单中的\升序\按钮，在弹出的\排序提醒\对话框中单击\排序\按钮。在\差旅成本分析报告\工作表的B3单元格中输入\费用报销管理!D74:D340=\北京市\费用报销管理!G74:G340)\，按\键确认。 （6）【解题步骤】

步骤：在\差旅成本分析报告\工作表的B4单元格中输入\费用报销管理!B3:B401=\钱顺卓\费用报销管理!F3:F401=\火车票\费用报销管理!G3:G401\，按\键确认。 （7）【解题步骤】

步骤：在\差旅成本分析报告\工作表的B5单元格中输入\费用报销管理!F3:F401=\飞机票\费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)\，按\键确认，并设置数字格式，保留两位小数。 （8）【解题步骤】

步骤：在\差旅成本分析报告\工作表的B6单元格中输入\费用报销管理!H3:H401=\是\费用报销管理!F3:F401=\通讯补助\费用报销管理!G3:G401)\，按\键确认。

第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须都保存在考生文件夹下。

期末考试结束了，初三（14）班的班主任助理王老师需要对本班学生的各科考试成绩进行统计分析，并为每个学生制作一份成绩通知单下发给家长。按照下列要求完成该班的成绩统计工作并按原文件名进行保存： 1. 打开工作簿“学生成绩.xlsx”，在最左侧插入一个空白工作表，重命名为“初三学生档案”，并将该工作表标签颜色设为“紫色(标准色)”。 2. 将以制表符分隔的文本文件“学生档案.txt”自A1单元格开始导入到工作表“初三学生档案”中，注意不得改变原始数据的排列顺序。将第1列数据从左到右依次分成“学号”和“姓名”两列显示。最后创建一个名为“档案”、包含数据区域A1:G56、包含标题的表，同时删除外部链接。 3. 在工作表“初三学生档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”、出生日期“××××年××月××日”和年龄。其中：身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；身份证号的第7~14位代表出生年月日；年龄需要按周岁计算，满1年才计1岁。最后适当调整工作表的行高和列宽、对齐方式等，以方便阅读。 4. 参考工作表“初三学生档案”，在工作表“语文”中输入与学号对应的“姓名”；

按照平时、期中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中；按成绩由高到低的顺序统计每个学生的“学期成绩”排名并按“第n名”的形式填入“班级名次”列中；按照下列条件填写“期末总评”：

语文、数学的学期成绩 其他科目的学期成绩 期末总评 ≥102 ≥90 优秀 ≥84 ≥75 良好 ≥72 ≥60 及格 〈72 〈60 不合格

5. 将工作表“语文”的格式全部应用到其他科目工作表中，包括行高（各行行高均为22默认单位）和列宽（各列列宽均为14默认单位）。并按上述4. 中的要求依次输入或统计其他科目的“姓名”、“学期成绩”、“班级名次”和“期末总评”。

6. 分别将各科的“学期成绩”引入到工作表“期末总成绩”的相应列中，在工作表“期末总成绩”中依次引入姓名、计算各科的平均分、每个学生的总分，并按成绩由高到底的顺序统计每个学生的总分排名、并以1、2、3……形式标识名次，最后将所有成绩的数字格式设为数值、保留两位小数。

7. 在工作表“期末总成绩”中分别用红色（标准色）和加粗格式标出各科第一名成绩。同时将前10名的总分成绩用浅蓝色填充。

8. 调整工作表“期末总成绩”的页面布局以便打印：纸张方向为横向，缩减打印输出使得所有列只占一个页面宽（但不得缩小列宽），水平居中打印在纸上。 答疑：1.【解题步骤】

步骤：打开素材文件“学生成绩.xlsx”，单击工作表最右侧的“插入工作表”按钮，然后双击工作表标签，将其重命名为“初三学生档案”。在该工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”，在弹出的级联菜单中选择标准色中的“紫色”。 2.【解题步骤】

步骤1：选中A1单元格，单击【数据】选项卡下【获取外部数据】组中的“自文本”按钮，弹出“导入文本文件”对话框，在该对话框中选择考生文件夹下的“学生档案.txt”选项，然后单击“导入”按钮。

步骤2：在弹出的对话框中选择“分隔符号”单选按钮，将“文件原始格式”设置为“54936：简体中文（GB18030）”。单击“下一步”按钮，只勾选“分隔符”列表中的“Tab键”复选项。然后单击“下一步”按钮，选中“身份证号码”列，然后点击“文本”单选按钮，单击“完成”按钮，在弹出的对话框中保持默认，单击“确定”按钮。

步骤3：选中B列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。然后选中A1单元格，将光标置于“学号”和“名字”之间，按3次空格键，然后选中A列单元格，单击【数据工具】组中的“分列”按钮，在弹出的对话框中选择“固定宽度”单选按钮，单击“下一步”按钮，然后建立分列线。单击“下一步”按钮，保持默认设置，单击“完成”按钮。

步骤4：选中A1:G56单元格，单击【开始】选项卡下【样式】组中的“套用表格格式”下拉按钮，在弹出的下拉列表中选择“表样式中等深浅2”。

步骤5：在弹出的对话框中勾选“表包含标题”复选框，单击“确定”按钮，然后再在弹出的对话框中选择“是”按钮。在【设计】选项卡下【属性】组中将“表

名称”设置为档案。3.【解题步骤】 步骤1：选中D2单元格，在该单元格内输入函数“=IF(MOD(MID(C2,17,1),2)=1,\男\女\”，按Enter键完成操作。然后利用自动的填充功能对其他单元格进行填充。

步骤2：选中E2单元格，在该单元格内输入函数“=--TEXT(MID(C2,7,8),\”，按Enter键完成操作，利用自动填充功能对剩余的单元格进行填充。然后选择E2：E56单元格，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项。切换至“数字”选项卡，将“分类”设置为“日期”，然后单击“确定”按钮。

步骤3：选中F2单元格，在该单元格内输入函数“=DATEDIF(--TEXT(MID(C2,7,8),\”，按Enter键，利用自动的填充功能对其他单元格进行填充。

步骤4：选中A1：G56区域，单击【开始】选项卡下【对齐方式】组中的“居中”按钮。适当调整表格的行高和列宽。 4.【解题步骤】

步骤1：进入到“语文”工作表中，选择B2单元格，在该单元格内输入函数“=VLOOKUP(A2,初三学生档案!$A$2:$B$56,2,0)”，按Enter键完成操作。然后利用自动的填充功能对其他单元格进行填充。

步骤2：选择F2单元格，在该单元格中输入函数“=SUM(C2*30%)+(D2*30%)+(E2*40%)”，按Enter键确认操作。 步骤3：选择G2单元格，在该单元格内输入函数“=\第\名\”，然后利用自动填充功能对其他单元格进行填充。

步骤4：选择H2单元格，在该单元格中输入公式“=IF(F2>=102,\优秀\良好\及格\及格\不及格\”，按Enter键完成操作，然后利用自动填充对其他单元格进行填充。 5.【解题步骤】

步骤1：选择“语文”工作表中A1：H45单元格区域，按Ctrl+C键进行复制，进入到“数学”工作表中，选择A1：H45区域，单击鼠标右键，在弹出的快捷菜单中选择“粘贴选项”下的“格式”按钮。

步骤2：继续选择“数学”工作表中的A1：H45区域，单击【开始】选项卡下【单元格】组中的“格式”下拉按钮，在弹出的下拉列表中选择“行高”选项，在弹出的对话框中将“行高”设置为22，单击“确定”按钮。单击“格式”下拉按钮，在弹出的下拉列表中选择“列宽”选项，在弹出的对话框中将“列宽”设置为14，单击“确定”按钮。 步骤3：使用同样的方法为其他科目的工作表设置相同的格式，包括行高和列宽。 步骤4：将“语文”工作表中的公式粘贴到数学科目工作表中的对应的单元格内，然后利用自动填充功能对单元格进行填充。

步骤5：在“英语”工作表中的H2单元格中输入公式“=IF(F2>=90,\优秀\良好\及格\及格\不及格\”，按Enter键完成操作，然后利用自动填充对其他单元格进行填充。 步骤6：将“英语”工作表H2单元格中的公式粘贴到“物理”、“化学”、“品德”、“历史”工作表中的H2单元格中，然后利用自动填充功能对其他单元格进行填充。

6.【解题步骤】

步骤1：进入到“期末总成绩”工作表中，选择B3单元格，在该单元格内输入公式“=VLOOKUP(A3,初三学生档案!$A$2:$B$56,2,0)”，按Enter键完成操作，然后利用自动填充功能将其填充至B46单元格。

步骤2：选择C3单元格，在该单元格内输入公式“=VLOOKUP(A3,语文!$A$2:$F$45,6,0)”，按Enter键完成操作，然后利用自动填充功能将其填充至C46单元格。

步骤3：选择D3单元格，在该单元格内输入公式“=VLOOKUP(A3,数学!$A$2:$F$45,6,0)”，按Enter键完成操作，然后利用自动填充功能将其填充至D46单元格。

步骤4：使用相同的的方法为其他科目填充平均分。选择J3单元格，在该单元格内输入公式“=SUM(C3:I3)”，按Enter键，然后利用自动填充功能将其填充至J46单元格。

步骤5：选择A3：K46单元格，单击【开始】选项卡【编辑】组中“排序和筛选”下拉按钮，在弹出的下拉列表中选择“自定义排序”选项，弹出“排序”对话框，在该对话框中将“主要关键字”设置为“总分”，将“排序依据”设置为“数值”，将“次序”设置为“降序”，单击“确定”按钮。

步骤6：在K3单元格内输入数字1，然后按住Ctrl键，利用自动填充功能将其填充至K46单元格。 步骤7：选择C47单元格，在该单元格内输入公式“=AVERAGE(C3:C46)”，按Enter键完成操作，利用自动填充功能进行将其填充至J47单元格。

步骤8：选择C3：J47单元格，在选择的单元格内单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项。在弹出的对话框中选择“数字”选项卡，将“分类”设置为数值，将“小数位数”设置为2，单击“确定”按钮。 7.【解题步骤】

步骤1： 选择C3：C46单元格，单击【开始】选项卡下【样式】组中的“条件格式”按钮，在弹出的下拉列表中选择“新建规则”选项，在弹出的对话框中将“选择规则类型”设置为“仅对排名靠前或靠后的数值设置格式”，然后将“编辑规则说明”设置为“前”、“1”。

步骤2：单击“格式”按钮，在弹出的对话框中将“字形”设置为加粗，将“颜色”设置为标准色中的“红色”，单击两次“确定”按钮。按同样的操作方式为其他六科分别用红色和加粗标出各科第一名成绩。

步骤3：选择J3：J12单元格，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项，在弹出的对话框中切换至“填充”选项卡，然后单击“浅蓝”颜色块，单击“确定”按钮。 8.【解题步骤】

步骤1：在【页面边距】选项卡下【页面设置】组中单击对话框启动器按钮，在弹出的对话框中切换至“页边距”选项卡勾选“居中方式”选项组中的“水平”复选框。

步骤2：切换至“页面”选项卡，将“方向”设置为横向。选择“缩放”选项组下的“调整为”单选按钮，将其设置为1页宽1页高，单击“确定”按钮。 第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

小林是北京某师范大学财务处的会计，计算机系计算机基础室提交了该教研室2012年的课程授课情况，希望财务处尽快核算并发放他们室的课时费。请根据考生文件夹下\素材.xlsx\中的各种情况，帮助小林核算出计算机基础室2012年度每个教员的课时费情况。具体要求如下：

1.将\素材.xlsx\另存为\课时费.xlsx\的文件，所有的操作基于此新保存好的文件。

2.将\课时费统计表\标签颜色更改为红色，将第一行根据表格情况合并为一个单元格，并设置合适的字体、字号，使其成为该工作表的标题。对A2：I22区域套用合适的中等深浅的、带标题行的表格格式。前6列对齐方式设为居中；其余与数值和金额有关的列，标题为居中，值为右对齐，学时数为整数，金额为货币样式并保留2位小数。

3.\课时费统计表\中的F至I列中的空白内容必须采用公式的方式计算结果。根据\教师基本信息\工作表和\课时费标准\工作表计算\职称\和\课时标准\列内容，根据\授课信息表\和\课程基本信息\工作表计算\学时数\列内容，最后完成\课时费\列的计算。【提示：建议对\授课信息表\中的数据按姓名排序后增加\学时数\列，并通过VLOOKUP查询\课程基本信息\表获得相应的值。】

4.为\课时费统计表\创建一个数据透视表，保存在新的工作表中。其中报表筛选条件为\年度\，列标签为\教研室\，行标签为\职称\，求和项为\课时费\。并在该透视表下方的A12：F24区域内插入一个饼图，显示计算机基础室课时费对职称的分布情况。并将该工作表命名为\数据透视图\，表标签颜色为蓝色。 5.保存\课时费.xlsx\文件。

答疑：1.【解题步骤】

步骤1：打开素材文件\素材.xlsx\，单击【文件】选项卡下的\另存为\按钮将此文件另存为\课时费.xlsx\的文件。 2.【解题步骤】

步骤1：右击\课时费统计表\，在弹出的列表中的\工作表标签颜色\中的\主体颜色\中选择\红色\。

步骤2：在\课时费统计表\表中，选中第一行，单击鼠标右键，在弹出的下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。在\对齐\选项卡下的\文本控制\组中，勾选\合并单元格\；切换至\字体\选项卡，在\字体\下拉列表中选择一种合适的字体，此处我们选择\黑体\。在\字号\下拉列表中选择一种合适的字号，此处我们选择\。

步骤3：选中A2：I22区域，在【开始】选项卡下的【样式】组中单击\套用表格格式\按钮，在打开的下拉列表中选择一种恰当的样式。按照题意，此处我们选择\表样式中等深浅5\。

步骤4：此时弹出\套用表格式\对话框，勾选\表包含标题\复选框。 步骤5：最后单击\确定\按钮即可。

步骤6：选中前6列，单击鼠标右键，在弹出的下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。在\对齐\选项卡下的\文本对齐方式\组的\水平对齐\下拉列表框中选择\居中\，而后单击\确定\即可。

步骤7：根据题意，选中与数值和金额有关的列标题，单击鼠标右键，在弹出的

下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。在\对齐\选项卡下的\文本对齐方式\组的\水平对齐\下拉列表框中选择\居中\。设置完毕后单击\确定\按钮即可。

步骤8：然后再选中与数值和金额有关的列，按照同样的方式打开\设置单元格格式\对话框，在\对齐\选项卡下的\文本对齐方式\组的\水平对齐\下拉列表框中选择\靠右（缩进）\。设置完毕后单击\确定\按钮即可。

步骤9：选中\学时数\所在的列，单击鼠标右键在弹出的的下拉列表中选择\设置单元格格式\命令。在弹出的\设置单元格格式\对话框中切换至\数字\选项卡。在\分类\中选择\数值\，在右侧的\小数位数\微调框中选择\。设置完毕后单击\确定\按钮即可。

步骤10：选中\课时费\和\课时标准\所在的列，按照同样的方式打开\设置单元格格式\对话框。切换至\数字\选项卡，在\分类\中选择\货币\，在右侧的\小数位数\微调框中选择\。设置完毕后单击\确定\按钮即可。 3.【解题步骤】 步骤1：在采用公式的方式计算\课时费统计表\中的F至L列中的空白内容之前，为了方便结果的计算，我们先对\教师基本信息\工作表和\课时费标准\工作表的数据区域定义名称。首先切换至\教师基本信息\表中，选中数据区域，单击鼠标右键，在弹出的下拉列表中选择\定义名称\命令，打开\新建名称\对话框。在\名称\中输入\教师信息\后单击确定即可。

步骤2：按照同样的方式为\课时费标准\定义名称为\费用标准\。

步骤3：先根据\教师基本信息\表计算\课时费统计表\中\职称\列的内容。选中\课时费统计表\中的F3单元格，输入公式\教师信息,5,FALSE)\后按\键即可将对应的职称数据引用至\课时费统计表\中。

步骤4：按照根据\教师基本信息\计算\课时费统计表\中\职称\列的内容同样的方式来计算\课时标准\列的内容，此处不再赘述。

步骤5：现在根据\授课信息表\和\课程基本信息\工作表计算\学时数\列内容。按照同样的方式先对\课程基本信息\的数据区域定义名称，此处定义为\课程信息\。

步骤6：再对\授课信息表\中的数据按姓名排序。选中\姓名\列，单击【数据】选项卡下【排序和筛选】组中的\升序\按钮，打开\排序提醒\对话框，默认选项，然后单击\排序\按钮即可。

步骤7：然后在\授课信息表\的F2单元格中增加\学时数\列，即输入\学时数\字样。

步骤8：再根据\教师基本信息\计算\课时费统计表\中\职称\列的内容同样的方式引用\课程基本信息\中的学时数数据至对应的\授课信息表\中。

步骤9：最后我们来计算\课时费统计表\中的\学时数\列。选中H3单元格，输入公式\授课信息表!$D$3:$D$72,E3,授课信息表!$F$3:$F$72)\，然后按\键即可。

步骤10：在I3单元格中输入公式\即可完成课时费的计算。 4．【解题步骤】

步骤1：在创建数据透视表之前，要保证数据区域必须要有列标题，并且该区域中没有空行。 步骤2：选中\课时费统计表\工作表的数据区域，在【插入】选项卡下的【表格】组中单击\数据透视表\按钮，打开\创建数据透视表\对话框。

步骤3：在\选择一个表或区域\项下的\表/区域\框显示当前已选择的数据源区域。此处对默认选择不作更改。

步骤4：指定数据透视表存放的位置：选中\新工作表\，单击\确定\按钮。 步骤5：Excel会将空的数据透视表添加到指定位置，并在右侧显示\数据透视表字段列表\窗口。 步骤6：根据题意，选择要添加到报表的字段。将\年度\拖曳至\报表筛选\条件，\教研室\拖曳至\列标签\，\职称\拖曳至\行标签\，\课时费\拖曳至\数值\中求和。

步骤7：单击数据透视表区域中的任意单元格，而后在\数据透视表工具\的\选项\选项卡下，单击\工具\组中的\数据透视图\按钮，打开\插入图表\对话框。根据题意，此处我们选择\饼图\选项。 步骤8 ：单击\确定\按钮后返回数据透视工作表中，根据题意，拖动饼图至A12：F24单元格内即可。

步骤9： 为该工作表命名。双击数据透视表的工作表名，重新输入\数据透视图\字样。

步骤10：鼠标右击表名\数据透视图\，在弹出的快捷菜单中选择\工作表标签颜色\级联菜单\主体颜色\中的\蓝色\。

步骤11：单击“保存”按钮保存“课时费.xlsx”文件。

第2题：（电子表格题，本题30分）

小李是公司的出纳，单位没有购买财务软件，因此她只能用手工记账。为了节省时间并保证记账的准确性，小李使用Excel编制银行存款日记账。 请根据该公司九月份的\银行流水账表格.docx\，并按照下述要求，在Excel中建立银行存款日记账：

1. 按照表中所示依次输入原始数据，其中：在\月\列中以填充的方式输入\九\，将表中的数值的格式设为数值、保留2位小数。 2 .输入并填充公式：在\余额\列输入计算公式，余额=上期余额+本期借方-本期贷方，以自动填充方式生成其他公式。

3.\方向列中\只能有借、贷、平三种选择，首先用数据有效性控制该列的输入范围为借、贷、平三种中的一种，然后通过IF函数输入\方向\列内容，判断条件如下所列：

余额 大于0 等于0 小于0 方向 借 平 贷 4.设置格式：将第一行中的各个标题居中显示；为数据列表自动套用格式后将其转换为区域。 5.通过分类汇总，按日计算借方、贷方发生额总计并将汇总行放于明细数据下方。 6.以文件名\银行存款日记账.xlsx\进行保存。

答疑：1.【解题步骤】

步骤1：首先我们按照素材中\银行流水账表格.docx\所示依次输入原始数据。 步骤2：然后我们在\月\列中以填充的方式输入\九\。将鼠标光标放置A2单元格右下角的填充柄处，待鼠标变成黑色十字形状后拖住不放，向下填充直至A6

单元格处。 步骤3：，将表中数值格式设为数值、保留2位小数。此处我们选择E3单元格，单击鼠标右键在弹出的的下拉列表中选择\设置单元格格式\命令。

步骤4：在弹出的\设置单元格格式\对话框中切换至\数字\选项卡。在\分类\中选择\数值\，在右侧的\小数位数\微调框中选择\。 步骤5：单击\确定\按钮后即可完成设置。

步骤6：按照同样的方式分别为其他数值的格式设为数值、保留2位小数。 2.【解题步骤】 步骤1：根据题意，在H3单元格中输入余额的计算公式\，按\键确认即可得出本期余额的计算结果。

步骤2：其他余额的计算采用自动填充公式的方式。选中H3单元格，将鼠标光标放置H3单元格右下角的填充柄处，待鼠标变成黑色十字形状后拖住不放，向下填充直至H6单元格处。 3.【解题步骤】

步骤1：选定\方向\列中的G2：G6区域，在【数据】选项卡下的【数据工具】组中，单击\数据有效性\按钮，打开\数据有效性\对话框。

步骤2：切换至\设置\选项卡，在\允许\下拉列表框中选择\序列\命令；在\来源\文本框中输入\借,贷,平\（注意要用英文输入状态下的逗号分隔），勾选\忽略空值\和\提供下拉菜单\两个复选框。

步骤3：再切换至\输入信息\选项卡，勾选\选定单元格显示输入信息\复选框，在\输入信息\中输入\请在这里选择\。

步骤4：单击\确定\按钮后即可在\方向\列中看到实际效果。

步骤5：根据题意，通过IF函数输入\方向\列内容。在\单元格中输入\平\借\贷\，然后按\键确认。

步骤6：按照同样的方式通过IF函数向方向列的其他单元格内输入内容。 4.【解题步骤】

步骤1：选中A1：H1单元格，在【开始】菜单下的【对齐方式】组中单击\居中\按钮，即可将第一行中的各个标题居中显示。

步骤2：选中数据区域，在【开始】菜单下的【样式】组中单击\套用表格格式\按钮，在弹出的下拉列表中选中一种自动套用的格式。此处，我们选择\中等深浅\组中的\表样式中等深浅2\。

步骤3：单击鼠标选中后弹出\套用表格式\对话框，在此对话框中勾选\表包含标题\复选框。

步骤4：单击\确定\按钮后即可为数据列表套用自动表格样式。

步骤5：选中表格，在【表格工具】的【设计】选项卡下，单击【工具】组中的\转换为区域\按钮。

步骤6：单击\是\按钮即可将表格转换为普通区域。 5.【解题步骤】

步骤1：选中数据区域，在【数据】选项卡下的【分级显示】组中，单击\分类汇总\按钮。

步骤2：弹出\分类汇总\对话框，在\分类字段\中选择\日\，在\汇总方式\中选择\求和\，在\选定汇总项\中勾选\本期借方\和\本期贷方\。 步骤3：单击\确定\按钮后即可看到实际汇总效果。 6．【解题步骤】

步骤：单击功能区中的【文件】选项卡，在打开的后台视图中单击\另存为\按钮，在随后弹出的\另存为\对话框中以文件名\银行存款日记账.xlsx\进行保存。

第2题：（电子表格题，本题30分）

小赵是某书店的销售人员，负责计算机类图书的销售情况，并按月份上报分析结果。2013年1月份时，她需要将2012年12月份的销售情况进行汇总，请根据提供的\文件帮助她完成下列工作：

1.对\进行如下设置：将\销售统计\表中的\单价\列数值的格式设为会计专用、保留2位小数。

2.对数据表\销售统计\进行适当的格式化，操作如下：合并A1：E1单元格，为标题名\月份计算机图书销售情况统计表\设置适当的对齐方式、字体、字号以及颜色；为数据区域设置边框底纹以使工作表更加美观等。

3.将工作表\销量\中的区域B3:D16定义名称为\销量信息\。在\销售统计\表中的\销量\列右侧增加一列\销售额\，根据\销售额=销量×单价\构建公式计算出各类图书的销售额。要求在公式中通过VLOOKUP函数自动在工作表\销量\中查找相关商品的具体销量。并在公式中引用所定义的名称\销量信息\。 4.为\销售统计\工作表创建一个数据透视表，放在一个名为\数据透视分析\的新的工作表中。

5.为数据透视表数据创建一个类型为饼图的数据透视图，设置数据标签显示在外侧，将图表的标题改为\月份计算机图书销量\。

6.将工作表另存为\计算机类图书12月份销售情况统计.xlsx\文件。 答疑：1.【解题步骤】

步骤1：将表中数值格式设为会计专用、保留2位小数。此处我们选择C4单元格，单击鼠标右键在弹出的的下拉列表中选择\设置单元格格式\命令。

步骤2：在弹出的\设置单元格格式\对话框中切换至\数字\选项卡。在\分类\中选择\会计专用\，在右侧的\小数位数\微调框中选择\。 步骤3：单击\确定\按钮后即可完成设置。

步骤4：按照同样的方式分别为其他数值的格式设为会计专用、保留2位小数。 2.【解题步骤】

步骤1：在\销售统计\表中，选中A1：E1单元格，单击鼠标右键，在弹出的下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。在\对齐\选项卡下的\文本控制\组中，勾选\合并单元格\；在\文本对齐方式\组的\水平对齐\选项下选择\居中\。而后单击\确定\即可。

步骤2：按照同样的方式打开\设置单元格格式\对话框，切换至\字体\选项卡，在\字体\下拉列表中选择一种合适的字体，此处我们选择\黑体\。在\字号\下拉列表中选择一种合适的字号，此处我们选择\。在\颜色\下拉列表中选择合适的颜色，此处我们选择\红色\，单击\确定\按钮后即可看到实际效果。

步骤3：选中数据区域，单击鼠标右键，在弹出的下拉菜单中选择\设置单元格格式\命令。

步骤4：在打开的\设置单元格格式\对话框中，切换至\边框\选项卡，在\预置\组中单击\外边框\按钮后即可在\边框\组中预览效果。

步骤5：再切换至\填充\选项卡下，在\背景色\组中选择一种合适的填充颜色。 步骤6：单击\确定\按钮后即可在工作表中看到实际效果。

4.引用\工龄工资\工作表中的数据来计算\员工档案表\工作表员工的工龄工资，在\基础工资\列中，计算每个人的基础工资。（基础工资=基本工资+工龄工资） 5. 根据\员工档案表\工作表中的工资数据，统计所有人的基础工资总额，并将其填写在\统计报告\工作表的B2单元格中。

6. 根据\员工档案表\工作表中的工资数据，统计职务为项目经理的基本工资总额，并将其填写在\统计报告\工作表的B3单元格中。

7. 根据\员工档案表\工作表中的数据，统计东方公司本科生平均基本工资，并将其填写在\统计报告\工作表的B4单元格中。

8. 通过分类汇总功能求出每个职务的平均基本工资。 9. 创建一个饼图，对每个员工的基本工资进行比较，并将该图表放置在\统计报告\中。

10. 保存\文件。 答疑：1.【解题步骤】

步骤1：启动考生文件下的\，打开\员工档案\工作表。

步骤2：选中所有工资列单元格，单击【开始】选项卡下【单元格】组中的\格式\下拉按钮，在弹出的下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。在\数字\选项卡\分类\组中选择\数值\，在小数位数微调框中设置小数位数为\。设置完毕后单击\确定\按钮即可。

步骤3：选中所有单元格内容，单击【开始】选项卡下【单元格】组中的\格式\下拉按钮，在弹出的下拉列表中选择“自动调整行高”命令。

步骤4：单击【开始】选项卡下【单元格】组中的\格式\下拉按钮，按照设置行高同样的方式选择“自动调整列宽”命令。 2.【解题步骤】

步骤：在“员工档案”表工作表中G3单元格中输入\年\月\日\，按\键确认，然后向下填充公式到最后一个员工，并适当调整该列的列宽。 3.【解题步骤】

步骤：在“员工档案”表的J3单元格中输入\，表示当前日期减去入职时间的余额除以365天后再向下取整，按Enter键确认，然后向下填充公式到最后一个员工。 4.【解题步骤】

步骤1：在“员工档案”表的L3单元格中输入\工龄工资!$B$3\，按\键确认，然后向下填充公式到最后一个员工。

步骤2：在M3单元格中输入\，按\键确认，然后向下填充公式到最后一个员工。 5.【解题步骤】 步骤：在\统计报告\工作表中的B2单元格中输入\员工档案!M3:M37)\，按\键确认。 6.【解题步骤】

步骤：在\统计报告\工作表中的B3单元格中输入\员工档案!K6+员工档案!K7\按Enter键确认。 7.【解题步骤】 步骤：设置\统计报告\工作表中的B4单元格格式为2为小数，然后在B4单元格中输入\员工档案!H3:H37,\本科\，员工档案!K3:K37)\，按Enter

键确认。

8.【解题步骤】

步骤1：选中E38，单击【数据】选项卡下【分级显示】组中的\分类汇总\按钮，弹出\分类汇总\对话框。单击\分类字段\组中的下拉按钮选择\职务\，单击\汇总方式\组中的下拉按钮选择\平均值\，在\选定汇总项\组中勾选\基本工资\复选框。

步骤2：单击\确定\按钮即可看到实际效果。 9.【解题步骤】

步骤1：同时选中每个职务平均基本工资所在的单元格，单击【插入】选项卡下【图表】组中的\饼图\按钮，选择\分离型饼图\命令。

步骤2：右击图表区，选择\选择数据\命令，弹出\选择数据源\对话框，选中\水平（分类）轴标签\下的\，单击\编辑\按钮，弹出\轴标签\对话框，在\轴标签区域\中输入\部门经理,人事行政经理,文秘,项目经理,销售经理,研发经理,员工,总经理\。

步骤3：单击\确定\按钮。

步骤4：剪切该图复制到\统计报告\工作表中。 10.【解题步骤】

步骤：单击\保存\按钮，保存\文件。第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须保存在考生文件夹下

小王今年毕业后，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门经理提供销售信息的分析和汇总。 请你根据销售统计表（“Excel.xlsx” 文件），按照如下要求完成统计和分析工作：

1. 将“sheet1”工作表命名为“销售情况”，将“sheet2”命名为“图书定价”。 2. 在“图书名称”列右侧插入一个空列，输入列标题为“单价”。

3. 将工作表标题跨列合并后居中并适当调整其字体、加大字号，并改变字体颜色。设置数据表对齐方式及单价和小计的数值格式（保留2位小数）。根据图书编号，请在“销售情况”工作表的“单价”列中，使用VLOOKUP函数完成图书单价的填充。“单价”和“图书编号”的对应关系在“图书定价”工作表中。 4.运用公式计算工作表“销售情况”中H列的小计。

5. 为工作表“销售情况”中的销售数据创建一个数据透视表，放置在一个名为“数据透视分析”的新工作表中，要求针对各书店比较各类书每天的销售额。其中：书店名称为列标签，日期和图书名称为行标签，并对销售额求和。

6. 根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。 7. 保存“Excel.xlsx”文件。 答疑：1.【解题步骤】

步骤1：启动考生文件夹下的\。 步骤2：双击\工作表名，待\呈编辑状态后输入\销售情况\即可，按照同样的方式将\命名为\图书定价\。

2.【解题步骤】

步骤1：在“销售情况”工作表中，选中\销量（本）\所在的列，单击鼠标右键，在弹出的的列表中选择\插入\命令。

步骤2：工作表中随即出现新插入的一列。 步骤3：选中F2单元格，输入\单价\二字。 3.【解题步骤】

步骤1：在“销售情况”工作表中选中A1：H1单元格，单击鼠标右键，在弹出的下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。在\对齐\选项卡下的\文本控制\组中，勾选\合并单元格\复选框；在\文本对齐方式\组的\水平对齐\选项下选择\居中\，而后单击\确定\即可。

步骤2：按照同样的方式打开\设置单元格格式\对话框，切换至\字体\选项卡，在\字体\下拉列表中选择一种合适的字体，此处我们选择\微软雅黑\。在\字号\下拉列表中选择一种合适的字号，此处我们选择\。在\颜色\下拉列表中选择合适的颜色，此处我们选择\标准色\中的\蓝色\。单击\确定\按钮。

步骤3：将光标置于数据区域任一位置，按Ctrl+A键选中整个数据区域，在【开始】选项卡下的【对齐方式】组中选择合适的对齐方式，此处我们选择\居中\。 步骤4：同时选中\单价\和\小计\列，单击鼠标右键，在弹出的下拉列表中选择\设置单元格格式\命令，弹出\设置单元格格式\对话框。切换至\数字\选项卡，在\分类\下拉列表中选择\数值\，在右侧的小数位数微调框中输入\，设置完毕后单击\确定\按钮即可。 4.【解题步骤】

步骤1：在\销售情况\工作表的F3单元格中输入\图书定价!$A$3:$C$19,3,FALSE)\，按\键确认，然后向下填充公式到最后一个数据行。

步骤2：在\销售情况\工作表的H3单元格中输入\，按\键确认，然后向下填充公式到最后一个数据行。 5【.解题步骤】

步骤1：在\销售情况\工作表中选中数据区域，在【插入】选项卡下的【表格】组中单击\数据透视表\按钮，打开\创建数据透视表\对话框，在\选择放置数据透视表的位置\选择\新工作表\单选按钮。

步骤2：单击\确定\按钮后即可看到实际效果。 步骤3：双击\，重命名为\数据透视分析\。

步骤4：将鼠标放置于\书店名称\上，待鼠标箭头变为双向十字箭头后拖动鼠标到\列标签\中，按照同样的方式拖动\日期\和\图书名称\到\行标签\中，拖动\小计\至\数值\中。 6.【解题步骤】

步骤1：选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的\柱形图\按钮，在弹出的下拉列表中选择\簇状柱形图\命令。

步骤2：在\数据透视图\中单击\书店名称\右侧下三角按钮，在下拉列表中只选择\博达书店\复选框。 步骤3：单击\确定\按钮。 7.【解题步骤】

步骤：单击\保存\按钮，保存\文件。第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须都保存在考生文件夹下。

小赵是一名参加工作不久的大学生。他习惯使用Excel表格来记录每月的个人开支情况，在2013年底，小赵将每个月各类支出的明细数据录入了文件名为“开支明细表.xlsx”的Excel工作簿文档中。请你根据下列要求帮助小赵对明细表进行整理和分析： 1.在工作表“小赵的美好生活”的第一行添加表标题“小赵2013年开支明细表”，并通过合并单元格，放于整个表的上端、居中。 2.将工作表应用一种主题，并增大字号，适当加大行高列宽，设置居中对齐方式，除表标题“小赵2013年开支明细表”外为工作表分别增加恰当的边框和底纹以使工作表更加美观。

3.将每月各类支出及总支出对应的单元格数据类型都设为“货币”类型，无小数、有人民币货币符号。

4.通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。

5.利用“条件格式”功能：将月单项开支金额中大于1000元的数据所在单元格以不同的字体颜色与填充颜色突出显示；将月总支出额中大于月均总支出110%的数据所在单元格以另一种颜色显示，所用颜色深浅以不遮挡数据为宜。 6.在“年月”与“服装服饰”列之间插入新列“季度”，数据根据月份由函数生成，例如：1至3月对应“1季度”、4至6月对应“2季度”…… 7.复制工作表“小赵的美好生活”，将副本放置到原表右侧；改变该副本表标签的颜色，并重命名为“按季度汇总”；删除“月均开销”对应行。

8.通过分类汇总功能，按季度升序求出每个季度各类开支的月均支出金额。 9.在“按季度汇总”工作表后面新建名为“折线图”的工作表，在该工作表中以分类汇总结果为基础，创建一个带数据标记的折线图，水平轴标签为各类开支，对各类开支的季度平均支出进行比较，给每类开支的最高季度月均支出值添加数据标签。

答疑：1. 【解题步骤】

步骤1：打开考生文件夹下的“开支明细表. xlsx”素材文件。

步骤2：选择“小赵美好生活”工作表，在工作表中选择【A1:M1】单元格，切换到【开始】选项卡，单击【对齐方式】下的【合并后居中】按钮。输入“小赵2013年开支明细表”文字，按Enter键完成输入。

2. 【解题步骤】

步骤1：选择工作表标签，单击鼠标右键，在弹出的快捷菜单中选择【工作表标签颜色】，为工作表标签添加“橙色”主题。

步骤2：选择【A1：M 1】单元格，将【字号】设置为“18”，将【行高】设置为

“35”，将【列宽】设置为“16”。选择【A2：M15】单元格，将【字号】设置为“12”，将【行高】设置为“18”，【列宽】设置为“16”。

步骤3：选择【A2:M15】单元格，切换到【开始】选项卡，在【对齐方式】选项组中单击对话框启动器按钮，弹出【设置单元格格式】对话框，切换到【对齐】选项卡，将【水平对齐】设置为“居中”。

步骤4：切换到【边框】选项卡，选择默认线条样式，将颜色设置为“标准色”中的“深蓝”，在【预置】选项组中单击【外边框】和【内部】按钮。

步骤5：切换到【填充】选项卡，选择一种背景颜色，单击“确定”按钮。

3. 【解题步骤】

步骤：选择B3:M15，在选定内容上单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】，弹出【设置单元格格式】对话框，切换至【数字】选项卡，在【分类】下选择【货币】，将【小数位数】设置为0，确定【货币符号】为人民币符号（默认就是），单击“确定”按钮即可。

4. 【解题步骤】

步骤1：选择M3单元格，输入“=SUM(B3:L3)”后按Enter键确认，拖动M3单元格的填充柄填充至M15单元格；选择B3单元格，输入“=AVERAGE(B3:B14)”后按Enter键确认，拖动B15单元格的填充柄填充至L15单元格。 步骤2：选择【A2:M14】，切换至【数据】选项卡，在【排序和筛选】选项组中单击【排序】按钮，弹出【排序】对话框，在【主要关键字】中选择【总支出】，在【次序】中选择【升序】，单击“确定”按钮。

5. 【解题步骤】

步骤1：选择【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。

步骤2：选择【M3:M14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在弹出的下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“=$M$15*110%”，设置颜色为“黄填充色深黄色文本”，单击“确定”按钮。

6. 【解题步骤】

步骤1：选择B列，鼠标定位在列号上，单击右键，在弹出的快捷菜单中选择【插入】按钮，选择B2单元格，输入文本“季度”。

步骤2：选择B3单元格，输入“=\第\季度\”，按

Enter键确认。拖动B3单元格的填充柄将其填充至B14单元格。

7. 【解题步骤】

步骤1：在“小赵的美好生活”工作表标签处单击鼠标右键，在弹出的快捷菜单中选择【移动或复制】，勾选【建立副本】，选择【（移至最后）】，单击“确定”按钮。

步骤2：在“小赵的美好生活（2）”标签处单击鼠标右键，在弹出的快捷菜单中选择工作表标签颜色，为工作表标签添加【红色】主题。

步骤3：在“小赵的美好生活（2）”标签处单击鼠标右键选择【重命名】，输入文本【按季度汇总】；选择【按季度汇总】工作表的第15行，鼠标定位在行号处，单击鼠标右键，在弹出的快捷菜单中选择“删除”按钮。

8. 【解题步骤】

步骤：选择【按季度汇总】工作表的【A2：N14】单元格，切换至【数据】选项卡，选择【分级显示】选项组下的【分类汇总】按钮，弹出【分类汇总】对话框，在【分类字段】中选择【季度】、在【汇总方式】中选择【平均值】，在【选定汇总项中】不勾选【年月】、【季度】、【总支出】，其余全选，单击“确定”按钮。

9. 【解题步骤】

步骤1：单击【按季度汇总】工作表左侧的标签数字“2”（在全选按钮左侧）。 步骤2：选择【B2:M24】单元格，切换至【插入】选项卡，在【图表】选项组中单击【折线图】下拉按钮，在弹出的下拉列表中选择【带数据标记的折线图】。 步骤3：选择图表，切换至【图表工具】下的【设计】选项卡，选择【数据】选项组中的【切换行/列】，使图例为各个季度。

步骤4：在图表上单击鼠标右键，在弹出的快捷菜单中选择【移动图表】，弹出【移动图表】对话框，选中【新工作表】按钮，输入工作表名称【折线图】，单击“确定”按钮。

步骤5：选择“折线图”工作表标签，单击鼠标右键，在弹出的快捷菜单中选择【工作表标签颜色】，为工作表标签添加【蓝色】主题，在标签处单击鼠标右键选择【移动或复制】按钮，在弹出的“移动或复制工作表”对话框中勾选【移至最后】复选框，单击“确定”按钮。保存工作表“开支明细表.xlsx”。 第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须都保存在考生文件夹下。

为让利消费者，提供更优惠的服务，某大型收费停车场规划调整收费标准，拟从原来“不足15分钟按15分钟收费”调整为“不足15分钟部分不收费”的收费政策。市场部抽取了5月26日至6月1日的停车收费记录进行数据分析，以

期掌握该项政策调整后营业额的变化情况。请根据考生文件夹下“素材.xlsx”中的各种表格，帮助市场分析员小罗完成此项工作。具体要求如下： 1.将“素材.xlsx”文件另存为“停车场收费政策调整情况分析.xlsx”，所有的操作基于此新保存好的文件。

2.在“停车收费记录”表中，涉及金额的单元格格式均设置为保留2位的数值类型。依据“收费标准”表，利用公式将收费标准对应的金额填入“停车收费记录”表中的“收费标准”列；利用出场日期、时间与进场日期、时间的关系，计算“停放时间”列，单元格格式为时间类型的“XX时XX分”。

3.依据停放时间和收费标准，计算当前收费金额并填入“收费金额”列；计算拟采用的收费政策的预计收费金额并填入“拟收费金额”列；计算拟调整后的收费与当前收费之间的差值并填入“差值”列。

4.将“停车收费记录”表中的内容套用表格格式“表样式中等深浅12”，并添加汇总行，最后三列“收费金额”、“拟收费金额”和“差值”汇总值均为求和。 5.在“收费金额”列中，将单次停车收费达到100元的单元格突出显示为黄底红字的货币类型。

6.新建名为“数据透视分析”的表，在该表中创建3个数据透视表，起始位置分别为A3、A11、A19单元格。第一个透视表的行标签为“车型”，列标签为“进场日期”，求和项为“收费金额”，可以提供当前的每天收费情况；第二个透视表的行标签为“车型”，列标签为“进场日期”，求和项为“拟收费金额”，可以提供调整收费政策后的每天收费情况；第三个透视表行标签为“车型”，列标签为“进场日期”，求和项为“差值”，可以提供收费政策调整后每天的收费变化情况。

答疑：1. 【解题步骤】

步骤1：启动Microsoft Excel 2010软件，打开考生文件夹下的“素材.xlsx”文件。

步骤2：将文档另存为到考生文件夹下，将其命名为“停车场收费政策调整情况分析.xlsx”，

2. 【解题步骤】

步骤1：首先选中E、K、L、M列单元格，切换至【开始】选项卡，在【数字】选项组中单击右侧的对话框启动器按钮，打开【设置单元格格式】对话框，在【数字】选项卡的【分类】中选择【数值】，在【小数点位数】的右侧输入“2”，单击“确定”按钮。

步骤2：选择“停车收费记录”表中的E2单元格，输入“=VLOOKUP(C2,收费标准!A$3:B$5,2,0)”，然后按Enter键，即可完成运算，然后向下拖动将数据进行填充。

步骤3：首先选中J列单元格，然后切换至【开始】选项卡，单击【数字】选项组中的对话框启动器按钮，打开【设置单元格格式】对话框，在【分类】选项组中选择【时间】，将【时间】类型设置为“XX时XX分”，单击“确定”按钮。

步骤4：计算停放时间，首先利用DATEDIF计算日期的差值，乘以24；再加上进场时间和出场时间的差值。即在J2单元格中输入“=DATEDIF(F2,H2,\”，并向下拖动将数据进行填充。

3. 【解题步骤】

步骤1：计算收费金额，在K2单元格中输入公式 “=E2*(TRUNC((HOUR(J2)*60+MINUTE(J2))/15)+1)”，并向下自动填充单元格。

步骤2：计算拟收费金额，在L2单元格中输入公式“=E2*TRUNC((HOUR(J2)*60+MINUTE(J2))/15)”，并向下自动填充单元格。

步骤3：计算差值，在M2单元格中输入公式“=K2-L2”，并向下自动填充单元格。

4. 【解题步骤】

步骤1：选择【A1：M550】单元格区域，切换至【开始】选项卡，单击【样式】选项组中【套用表格格式】的下拉按钮，在下拉样式选项中选择【表样式中等深浅12】。

步骤2：选择K551单元格，输入公式 “=sum(k2:k550)”或“=SUM([收费金额])”。按Enter键，即可完成求和运算。

步骤3：选择K551单元格，使用复制公式完成然后L551、M551单元格的求和运算。

5. 【解题步骤】

步骤1：首先选择【收费金额】列单元格，然后切换至【开始】选项卡，选择【样式】选项组中的【条件格式】下拉按钮，在弹出的下拉列表中选择【突出显示单元格规则】-【大于】按钮。

步骤2：在打开的【大于】对话框中，将【数值】设置为“100”，单击【设置为】右侧的下三角按钮，在弹出的下拉列表中选择【自定义格式】。

步骤3：在弹出的【设置单元格格式】对话框中，切换至【字体】选项卡，将颜色设置为“红色”。

步骤4：切换至【填充】选项卡，将【背景颜色】设置为“黄色”，单击 “确定”按钮。返回到【大于】对话框，再次单击“确定”按钮。

6. 【解题步骤】

步骤1：选择“停车收费记录”内【C2：M550】单元格区域内容。

步骤2：切换至【插入】选项卡，单击【表格】下的【数据透视表】按钮，弹出【创建数据透视表】对话框,单击“确定”按钮后，进入数据透视表设计窗口。

步骤3：在【数据透视表字段列表】中拖动“车型”到行标签处，拖动“进场日期”到列标签，拖动“收费金额”到数值区。将表置于现工作表A3为起点的单元格区域内。

步骤4：同样的方法得到第二和第三个数据透视表。第二个透视表的行标签为“车型”，列标签为“进场日期”，数值项为“拟收费金额”，可以提供调整收费政策后的每天收费情况；第三个透视表行标签为“车型”，列标签为“进场日期”，数值项为“差值”，可以提供收费政策调整后每天的收费变化情况。

第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须都保存在考生文件夹下。

销售部助理小王需要针对2012年和2013年的公司产品销售情况进行统计分析，以便制订新的销售计划和工作任务。现在，请按照如下需求完成工作： 1.打开“Excel_素材.xlsx”文件，将其另存为“Excel.xlsx”，之后所有的操作均在“Excel.xlsx”文件中进行。

2.在“订单明细”工作表中，删除订单编号重复的记录(保留第一次出现的那条记录），但须保持原订单明细的记录顺序。

3.在“订单明细”工作表的“单价”列中，利用VLOOKUP公式计算并填写相对应图书的单价金额。图书名称与图书单价的对应关系可参考工作表“图书定价”。 4.如果每订单的图书销量超过40本（含40本），则按照图书单价的9.3折进行销售；否则按照图书单价的原价进行销售。按照此规则，计算并填写“订单明细”工作表中每笔订单的“销售额小计”，保留2位小数。要求该工作表中的金额以显示精度参与后续的统计计算。

5.根据“订单明细”工作表的“发货地址”列信息，并参考“城市对照”工作表中省市与销售区域的对应关系，计算并填写“订单明细”工作表中每笔订单的“所属区域”。

6.根据“订单明细”工作表中的销售记录，分别创建名为“北区”、“南区”、“西区”和“东区”的工作表，这4个工作表中分别统计本销售区域各类图书的累计销售金额，统计格式请参考“Excel_素材.xlsx”文件中的“统计样例”工作表。将这4个工作表中的金额设置为带千分位的、保留两位小数的数值格式。 7.在“统计报告”工作表中，分别根据“统计项目”列的描述，计算并填写所对应的“统计数据”单元格中的信息。 答疑：1. 【解题步骤】

步骤1：启动Microsoft Excel 2010 软件，打开考生文件夹下的“Excel素材.xlsx”文件, 将其另存为“Excel.xlsx”。

2. 【解题步骤】

步骤1：在“订单明细”工作表中按Ctrl+A组合键选择所有表格，切换至【数据】选项卡，单击【数据工具】选项组中的【删除重复项】按钮，在弹出对话框中单击全选，单击“确定”按钮。

3. 【解题步骤】

步骤1：在“订单明细”工作表E3单元格输入“=VLOOKUP([@图书名称],表2,2,0)”，按Enter键计算结果，并拖动填充柄向下自动填充单元格。

4. 【解题步骤】

步骤1：在“订单明细”工作表I3单元格输入“=IF([@销量（本）]>=40,[@单价]*[@销量（本）]*0.93,[@单价]*[@销量（本）])”，按Enter键计算结果，并拖动填充柄向下自动填充单元格。

5. 【解题步骤】

步骤1：在“订单明细”工作表的H3单元格中，输入“=VLOOKUP(MID([@发货地址],1,3),表3,2,0)”。按Enter键计算结果，并拖动填充柄向下自动填充单元格。

6. 【解题步骤】

步骤1：单击“插入工作表”按钮，分别创建4个新的工作表。移动工作表到“统计样例”工作表前，分别重命名为“北区”、“南区”、“西区”和“东区”。

步骤2：在“北区”工作表中，切换至【插入】选项卡，单击【表格】选项组中的【数据透视表】下拉按钮，在弹出的【创建数据透视表】对话框中，勾选【选择一个表或区域】单选按钮，在“表/区域”中输入“表1”，位置为“现有工作表”，单击“确定”按钮。

步骤3：将【图书名称】拖拽至【行标签】，将【所属区域】拖拽至【列标签】，将【销售额小计】拖拽至【数值】。展开列标签，取消勾选“北区”外其他3个区，单击“确定”按钮。

步骤4：切换至【数据透视表工具】-【设计】选项卡，在【布局】选项组中，单击【总计】按钮，在弹出的下拉列表中单击【仅对列启用】；单击【报表布局】按钮，在弹出的下拉列表中选择【以大纲形式显示】。

步骤5：选中数据区域B列，切换到【开始】选项卡，单击【数字】选项组的对话框启动器按钮，在弹出的“设置单元格格式”对话框中选择“分类”组中的“数值”，勾选“使用千分位分隔符”，“小数位数”设为“2”，单击“确定”按钮。

步骤6：按以上方法分别完成“南区”、“西区”和“东区”工作表的设置。

7. 【解题步骤】

步骤1：在“统计报告”工作表B3单元格输入“=SUMIFS(表1[销售额小计],表1[日期],\表1[日期],\”。然后选择【B4:B7】单元格，按Delete键删除。

步骤2：在【统计报告】工作表B4单元格输入“=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D7,表1[日期],\表1[日期],\”。

步骤3：在【统计报告】工作表B5单元格输入“=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],\表1[日期],\”。

步骤4：在【统计报告】工作表B6单元格输入“=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],\表1[日期],\”。

步骤5：在【统计报告】工作表B7单元格输入“=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],\表1[日期],\表1[销售额小计],表1[日期],\表1[日期],\”，设置数字格式为百分比，保留两位小数。 第2题：（电子表格题，本题30分）

请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。

注意：以下的文件必须都保存在考生文件夹下。

小李是某政法学院教务处的工作人员，为更好地掌握各个教学班级学习的整体情况，教务处领导要求她制作成绩分析表。请根据考生文件夹下\素材.xlsx\文件，帮助小李完成学生期末成绩分析表的制作。具体要求如下：

1. 将\素材.xlsx\另存为\成绩分析.xlsx\的文件，所有的操作基于此新保存好的文件。

2. 在\法一\、\法二\、\法三\、\法四\工作表中表格内容的右侧，分别按序插入\总分\、\平均分\、\班内排名\列；并在这四个工作表表格内容的最下面增加\平均分\行。所有列的对齐方式设为居中，其中\班内排名\列数值格式为整数，其他成绩统计列的数值均保留1位小数。

3. 为\法一\、\法二\、\法三\、\法四\工作表内容套用\表样式中等深浅 15\的表格格式，并设置表包含标题。

4. 在\法一\、\法二\、\法三\、\法四\工作表中，利用公式分别计算\总分\、\平均分\、\班内排名\列的值和最后一行\平均分\的值。对学生成绩不及格（小于60）的单元格突出显示为\橙色（标准色）填充色，红色（标准色）文本\格式。

5. 在\总体情况表\工作表中，更改工作表标签为红色，并将工作表内容套用\表样式中等深浅 15\的表格格式，设置表包含标题；将所有列的对齐方式设为居中；并设置\排名\列数值格式为整数，其他成绩列的数值格式保留1位小数。 6. 在\总体情况表\工作表B3:J6单元格区域内，计算填充各班级每门课程的平均成绩；并计算\总分\、\平均分\、\总平均分\、\排名\所对应单元格的值。 7. 依据各课程的班级平均分，在\总体情况表\工作表A9:M30区域内插入二维的簇状柱形图，水平簇标签为各班级名称，图例项为各课程名称。

8. 将该文件中所有工作表的第一行根据表格内容合并为一个单元格，并改变默认的字体、字号，使其成为当前工作表的标题。 9. 保存\成绩分析.xlsx\文件。\答疑：1. 【解题步骤】

步骤1：打开考生文件夹下的\素材.xlsx\素材文件。

步骤2：单击【文件】选项卡下的\另存为\按钮，弹出【另存为】对话框，在该对话框中将\文件名\设为\成绩分析.xlsx\，将其保存于考生文件夹下。

2. 【解题步骤】

步骤1： 在\法一\工作表中，在L2单元格中输入文字\总分\，在M2单元格中输入文字\平均分\，在N2单元格中输入文字\班内排名\，在A28单元格，中输入文字为\平均分\。

步骤2：选中A1：N28单元格区域，单击【开始】选项卡下【对齐方式】选项组中的居中按钮，使所有列的对齐方式都为居中。

步骤3：选中N3：N27单元格区域，单击【开始】选项卡下【数字】选项组中的对话框启动器按钮，弹出【设置单元格格式】对话框，在【数字】选项卡中【分类】列表框中选择\数值\选项，将\小数位数\设置为0，单击\确定\按钮。

步骤4：选中C3：M28】单元格，单击【开始】选项卡下【数字】选项组中的对话框启动器按钮，弹出【设置单元格格式】对话框，在【数字】选项卡中【分类】列表框中选择\数值\选项，将\小数位数\设置为1，单击\确定\按钮。

步骤5：按步骤1-4相同的方法，，依次设置\法二\、\法三\、\法四\工作表的内容。

3. 【解题步骤】

步骤1：在\法一\工作表中选择A2：N28单元格，单击【开始】选项卡- \样式\选项组中的\套用表格格式\按钮，在下方选择\表样式中等深浅 15\的表格格式。

步骤2：在弹出的\套用表格式\对话框中勾选\表包含标题（M）\复选框，单击\确定\按钮。

步骤3：按步骤1-2相同的方法，为 \法二\、\法三\、\法四\工作表内容套用\表样式中等深浅 15\的表格格式，并设置表包含标题。

4. 【解题步骤】

步骤1：在L3单元格中输入公式：=SUM(C3:K3)，拖动右下角的填充柄向下填充到L27单元格，即可得到个人总分。

步骤2：在M3单元格中输入公式：=AVERAGE(C3:K3)，拖动右下角的填充柄向下填充到M27单元格，即可的到个人平均分。

步骤3：在N3单元格中输入公式：=RANK(L3,$L$3:$L$27,0), 拖动右下角的填充柄向下填充到N27单元格，即可得到班内排名。

步骤4：在C28单元格中输入公式：=AVERAGE(C3:C27)， 拖动右下角的填充柄向右填充到M28单元格，即可得到各科平均分。

步骤5：选中C3:K27单元格区域，单击【开始】选项卡-【样式】选项组中的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【小于】，弹出\小于\对话框，在【为小于以下值的单元格设置格式】文本框中输入\，单击【设置为】右侧的下三角按钮，在弹出的下拉菜单中选择【自定义格式】命令。

步骤6：弹出【设置单元格格式】对话框，切换至【字体】选项卡，将颜色设置为\红色\。

步骤7：切换至【填充】选项卡，将背景色设置为\橙色\，单击\确定\按钮。返回到\小于\对话框，再次单击\确定\按钮。

步骤8：按步骤1-5相同的方法，为 \法二\、\法三\、\法四\工作表计算成绩和设置格式。

5. 【解题步骤】

步骤1：在\总体情况表\工作表标签上单击鼠标右键，在弹出的快捷菜单中选择【工作表标签颜色】命令级联菜单栏中的\红色\。

步骤2：选择A2：M7单元格区域，单击【开始】选项卡-【样式】选项组中的【套用表格格式】按钮，在下方选择【表样式中等深浅 15】的表格格式。并在弹出的【套用表格式】对话框中勾选【表包含标题（M）】复选框，单击\确定\按钮即可。

步骤3：使用前面讲过的方法，将所有列的对齐方式设为居中，设置\排名\列数值格式为整数，设置其他成绩列的数值格式保留1位小数。

6. 【解题步骤】

步骤1：在\法一\工作表中，选中区域C28:K28,按Ctrl+C组合键进行复制，切换到\总体情况表\中，在B3单元格中，单击【开始】选项卡-【剪贴板】选项组中\粘贴\按钮，在下拉列表中选择\粘贴数值\中的\值\粘贴，即可填充\法一\班每门课程的平均成绩。

步骤2：按照同样的方法，填充\法二\法三\法四\班每门课程的平均成绩。

步骤3：在K3单元格中输入公式：=SUM(B3:J3)，拖动右下角的填充柄向下填充到K6单元格，即可得到班级总分。

步骤4：在L3单元格中输入公式：=AVERAGE(B3:J3)，拖动右下角的填充柄向下填充到L6单元格，即可得到班级平均分。

步骤5：在M3单元格中输入公式：=RANK(K3,$K$3:$K$6,0), 拖动右下角的填充柄向下填充到M6单元格，即可得到班级排名。

步骤6：在B7单元格中输入公式：=AVERAGE(B3:B6)， 拖动右下角的填充柄向

右填充到L7单元格，即可得到总平均分。

7. 【解题步骤】

步骤1：在\总体情况表\中选择A2：J6单元格区域，单击【插入】选项卡【图表】选项组中的【柱形图】按钮，在弹出的下拉列表中选择二维柱形图中的簇状柱形图。此时，水平簇标签为各班级名称，图例项为各课程名称。

步骤2：拖动图形边缘，调整大小，使其放置于工作表的A9：M30区域中。

8. 【解题步骤】

步骤1：切换至\法一\工作表，选中A1：N1单元格区域，单击【开始】选项卡-【对齐方式】选项组中的\合并后居中\按钮，并改变字体、字号。

步骤2：使用同样的方法，设置\法二\、\法三\、\法四\工作表的标题。

步骤3：在工作表\总体情况表\中，单元格区域为A1：M1，其他设置相同。

9. 【解题步骤】

步骤：设置完成后单击\保存\按钮，即可保存\成绩分析.xlsx\文件。\

本文来源：https://www.bwwdw.com/article/6vy5.html