2024年3月23日发(作者：)
Excel 电子表格软件的使用---为题目类型
小李今年毕业后，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门
经理提供销售信息的分析和汇总。 请你根据销售数据报表(“Excel．xlsx”文件)，按照
如下要求完成统计和分析工作：
1.
请对“订单明细”工作表进行格式调整，通过套用表格格式方法将所有的销售记录调整
为一致的外观格式，并将“单价”列和“小计”列所包含的单元格调整为“会计专用”(人
民币)数字格式。
2.
根据图书编号，请在“订单明细”工作表的“图书名称”列中，使用 VLOOKUP 函数完成
图书名称的自动填充。“图书名称”和“图书编号”的对应关系在“编号对照”工作表
中。
3.
根据图书编号，请在“订单明细”工作表的“单价”列中，使用 VLOOKUP 函数完成图书
单价的自动填充。“单价”和“图书编号”的对应关系在“编号对照”工作表中。
4.
在“订单明细”工作表的“小计”列中，计算每笔订单的销售额。
5.
根据“订单明细”工作表中的销售数据，统计所有订单的总销售金额，并将其填写在
“统计报告”工作表的 B3 单元格中。
6.
根据“订单明细”工作表中的销售数据，统计《MS Office 高级应用》图书在 2012 年的
总销售额，并将其填写在“统计报告”工作表的 B4 单元格中。
7.
根据“订单明细”工作表中的销售数据，统计隆华书店在 2011 年第 3 季度的总销售额，
并将其填写在“统计报告”工作表的 B5 单元格中。
8.
根据“订单明细”工作表中的销售数据，统计隆华书店在 2011 年的每月平均销售额(保
留 2 位小数)，并将其填写在“统计报告”工作表的 B6 单元格中。保存“Excel．xlsx”文
件。
行政部李强负责本公司员工档案的日常管理，以及员工每年各项基本社会保险费用的计
算。按照下列要求帮助李强完成相关数据的整理、计算、统计和分析工作：
10.
将考生文件夹下的工作簿文档“Excel 素材．xlsx”另存为“Excel．xlsx”
(“．xlsx”为文件扩展名)，之后所有的操作均基于此文件，否则不得分。操作过程中，
不可以随意改变原工作表素材数据的顺序。
11.
在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对： ①
中国公民的身份证号由 18 位组成，最后一位即第 18 位为校验码，通过前 17 位计算得出。
第 18 位校验码的计算方法是： 将身份证的前 17 位数分别与对应系数相乘，将乘积之和除
以 11，所得余数与最后一位校验码一一对应。从第 1 位到第 17 位的对应系数以及余数与校
验码对应关系参见工作表“校对参数”中所列。 ②首先在工作表“身份证校对”中将身份号
的 18 位数字自左向右分拆到对应列。 ③通过前 17 位数字以及工作表“校对参数”中的校对
系数计算出校验码，填入 V 列中。 ④将原证号的第 18 位与计算出的校验码进行对
比，比对结果填入 W 列，要求比对相符时输入文本“正确”，不符时输入“错误”。 ⑤如果
校对结果错误，则通过设置条件格式将错误证号所在的数据行以“红色”文字、浅绿类型
的颜色填充。
12.
在工作表“员工档案”中，按照下列要求对员工档案数据表进行完善： ①输入每位员
工的身份证号，员工编号与身份证号的对应关系见工作表“身份证校对”。如果已校对出
错误，应将正确的身份证号填写人工作表“员工档案”中(假设所有错误号码都是由于最后
一位校验码输错导致的)。 ②计算每位员工截止 2016 年 12 月 31 日的年龄，每满一年才计
算一岁，一年按 365 天计算。 ③在“工作状态”列的空白单元格中填入文本“在职”。
④计算每位员工在本公司工作的工龄，要求不足半年按半年计、超过半年按一年计，一年
按 365 天计算，保留一位小数。其中，“在职”员工的工龄计算截止于 2016 年 12 月 31 日，
离职和退休人员计算截止于各自离职或退休的时间。 ⑤计算每位员工的工龄工资，公式：
工龄工资=本公司工龄×50。 ⑥计算员工的工资总额，公式：工资总额=工龄工资+签约工
资+上年月均奖金。
13.
在工作表“社保计算”中，按照下列要求计算每个员工本年度每月应缴社保金额： ①
依据工作表“员工档案”中的数据，筛选出所有“在职”员工的“员工编号”、“姓名”
和“工资总额”三列数据，依次填入 B、C、D 中，并按员工编号由小到大排序。 ②本市上
年职工平均月工资为 7086 元，首先将其定义为常量“人均月工资”，然后依据下列规则计算
出每位员工的“社保基数”填入相应 E 列中，计算时需要在公式中调用新定义的常量“人
均月工资”：社保基数最低为人均月工资 7086 元的 60％，最高为人均月工资 7086 元
的 3 倍：
14.
以工作表“社保计算”的结果为数据源，参照下列图 1 所示样例，自新工作表“透视分
析”的 A3 单元格开始生成数据透视表，要求如下： ①列标题应与示例图相同； ②按图中所
示调整工资总额的数字格式； ③改变数据透视表样式。
Excel 电子表格软件的使用---为题目类型
小李今年毕业后，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门
经理提供销售信息的分析和汇总。 请你根据销售数据报表(“Excel．xlsx”文件)，按照
如下要求完成统计和分析工作：
【正确答案】
1.
请对“订单明细”工作表进行格式调整，通过套用表格格式方法将所有的销售记录调整
为一致的外观格式，并将“单价”列和“小计”列所包含的单元格调整为“会计专用”(人
民币)数字格式。
【正确答案】(1)套用表格格式 在“订单明细”工作表中选中数据表(A1：H636)，在“开
始”选项卡“样式”组中单击“套用表格格式”按钮，在展开的列袭中选择一种样式即
可。 (2)设置数字格式 在“订单明细”工作表中选中“单价”列和“小计”列，单击“开
始”选项卡“数字”组的对话框启动器，打开“设置单元格格式”对话框，在“数字”选
项卡“分类”列表中选择“会计专用”项，在“货币符号”下拉框中选择“￥”符号即
可。
2.
根据图书编号，请在“订单明细”工作表的“图书名称”列中，使用 VLOOKUP 函数完成
图书名称的自动填充。“图书名称”和“图书编号”的对应关系在“编号对照”工作表
中。
【正确答案】①在“订单明细”工作表中，选择 E3 单元格，输入公式“=VLOOKuP(D3，表
2[#全部]，2,0)”。 ②复制 E3 单元格中的公式到该列其他单元格中即可。 VLOOKUP 是一
个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的 值。本题
中“=VLOOKUP(D3,表 2[#全部]，2，0)”的含义如下： 参数 1 一查找目标： “D3”。将在参
数 2 指定区域的第 1 列中查找与 D3 相同的单元格。 参数 2 一查找范围： “表 2[#全部]”
表示第 2 个工作表(即“编号对照”工作表)中数据表的全部区域(即 A2： C19 区域)。注意：
查找目标一定要在该区域的第一列。 参数 3 一返回值的列数：“2”表示参数 2 中工作表的
第 2 列。如果在参数 2 中找到与参数 1 相同的单元格，则返回第 2 列的内容。 参数 4 一精确
OR 模糊查找：“0”。最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，
模糊即包含的意思。第 4 个参数如果指定值是 0 或FALSE 就表示精确查找，而值为 1 或
TRUE 时则表示模糊。注意：在使用 VLOOKUP 时不要把这个参数给漏掉了，如果缺少这个参数，
则会默认为模糊查找。
3.
根据图书编号，请在“订单明细”工作表的“单价”列中，使用 VLOOKUP 函数完成图书
单价的自动填充。“单价”和“图书编号”的对应关系在“编号对照”工作表中。
【正确答案】①在“订单明细”工作表中，选择 F3 单元格，输入公式“=VLOOKUP(D3，表
2[#全部]，3,0)”。 ②拖动 F3 单元格右下角的智能填充句柄，一直拖动到 F636 单元格上，
即可将 F3 单元格中的公式复制到该列其他单元格中。 本题使用 VLOOKUP 函数的方法
同上一小题类似，只是要返回值由“编号对照”工作表的第 2 列(“图书名称”列)换成了
第 3 列(“定价”列)。
4.
在“订单明细”工作表的“小计”列中，计算每笔订单的销售额。
【正确答案】①在“订单明细”工作表中，选择 H3 单元格，输入公式“=G3*F3”。 ②拖
动 H3 单元格右下角的智能填充句柄，一直拖动到 H636 单元格上，即可将 H3 单元格中的公
式复制到该列其他单元格中。
5.
根据“订单明细”工作表中的销售数据，统计所有订单的总销售金额，并将其填写在
“统计报告”工作表的 B3 单元格中。
【正确答案】在“统计报告”工作表中，选择 B3 单元格，输入公式“=SUM(订单明细!H3：
H636)”。
6.
根据“订单明细”工作表中的销售数据，统计《MS Office 高级应用》图书在 2012 年的
总销售额，并将其填写在“统计报告”工作表的 B4 单元格中。
【正确答案】在“统计报告”工作表中，选择 B4 单元格，输入公式“=SUMIFS(表 3[小
计]，表 3[图书名称],”《MS Office 高级应用》”，表 3[日期]，I->=2012 年 1 月 1
日”)”。 SUMIFS 函数的语法是：SUMIFS(求和区域，条件区域 1，条件 1，[条件区域 2，
条件 2]…．) 参数 1 一求和区域：即计算此区域内符合条件的单元格数据。 参数 2 一条
件区域、参数 3 一条件：参数 2 和参数 3 是一个组合，表示一个条件。还可以增加更多的
条件。如本题中“表 3[图书名称]”，《MS Office 高级应用》"是一组两个参数，表示一
个条件；“表 3[日期]，”>=2012 年 1 月 1 日””是另一组参数，表示第 2 个条件。
7.
根据“订单明细”工作表中的销售数据，统计隆华书店在 2011 年第 3 季度的总销售额，
并将其填写在“统计报告”工作表的 B5 单元格中。
【正确答案】在“统计报告”工作表中，选择 B5 单元格，输入公式“=SUMIFS(表 3[小
计]，表 3[书店名称]，“隆华书店”，表 3[日期]”>=2011 年 7 月 1 日”，表 3[日
期]，”<=2011 年 9 月 30 日”)”。 本题中有 3 组不同的求和条件。
8.
根据“订单明细”工作表中的销售数据，统计隆华书店在 2011 年的每月平均销售额(保
留 2 位小数)，并将其填写在“统计报告”工作表的 B6 单元格中。保存“Excel．xlsx”文
件。
【正确答案】注意本题要求不是求数据的平均值，而是求月平均值。可以先使用 SUMIFS 函
数求和，再计算月平均值(除以 12)。 在“统计报告”工作表中，选择 B6 单元格，输入公
式“=SUMIFS(表 3[小计]，表 3[书店名称]，“隆华书店”，表 3[日期]”>=2011 年 1 月 1
日”，表 3[日期]”，1<=2011 年 12 月 31 日”)／12”。 保存为“Excel．xlsx”文件。
行政部李强负责本公司员工档案的日常管理，以及员工每年各项基本社会保险费用的计
算。按照下列要求帮助李强完成相关数据的整理、计算、统计和分析工作：
【正确答案】
10.
将考生文件夹下的工作簿文档“Excel 素材．xlsx”另存为“Excel．xlsx”
(“．xlsx”为文件扩展名)，之后所有的操作均基于此文件，否则不得分。操作过程中，
不可以随意改变原工作表素材数据的顺序。
【正确答案】步骤 1：打开考生文件夹下的“．Excel 素材．xlsx”文件。 步骤 2：单击
【文件】选项卡下的“另存为”按钮，弹出“另存为”对话框，在该对话框中将“文件
名”修改为“Excel”，将其保存于考生文件夹下。
11.
在工作表“身份证校对”中按照下列规则及要求对员工的身份证号进行正误校对： ①
中国公民的身份证号由 18 位组成，最后一位即第 18 位为校验码，通过前 17 位计算得出。
第 18 位校验码的计算方法是： 将身份证的前 17 位数分别与对应系数相乘，将乘积之和除
以 11，所得余数与最后一位校验码一一对应。从第 1 位到第 17 位的对应系数以及余数与校
验码对应关系参见工作表“校对参数”中所列。 ②首先在工作表“身份证校对”中将身份号
的 18 位数字自左向右分拆到对应列。 ③通过前 17 位数字以及工作表“校对参数”中的校对
系数计算出校验码，填入 V 列中。 ④将原证号的第 18 位与计算出的校验码进行对比，比对
结果填入 W 列，要求比对相符时输入文本“正确”，不符时输入“错误”。 ⑤如果校对结果
错误，则通过设置条件格式将错误证号所在的数据行以“红色”文字、浅绿类型的颜色填
充。
【正确答案】步骤 1：在“身份证校对”工作表的 D3 单元格中输入公式“=MID($C3，
COLUMN(D2)－3，1)”，输入完成后按键盘上的 Enter 键结束输入，向右拖动填充句柄填充
到 U3 单元格，然后双击 U3 单元格的填充句柄向下自动填充到 U122 单元格(此处是按行进行
填充)。 步骤 2：在 V3 单元格中输入公式“=TEXT(VLOOKUP(MOD(SUMPRODUCT(D3：T3*校对
参数!$E$5：$U$5)，11)，校对参数!$B$5：$C$15，2，0)，”@”)”，输入完成后按键盘
上的 Enter 键结束输入，向下拖动填充句柄填充到 V122 单元格。 步骤 3：在’W3 单元格中
输入公式“=IF(U3=V3，”正确”，”错误”)”，输入完成后按键盘上的 Enter 键结束输入，向下
拖动填充句柄填充 W122 单元格。 步骤 4：选中 W3 单元格，单击【开始】选项卡下【样式】
功能组中的“条件格式”按钮，在下拉列表中选择“新建规则”命令，弹出“新建格式规则”
对话框，在“选择规格类型”列表框中选择“使用公式确定要设置格式的单元格”，在下方的
编辑框中输入公式“=IF($W3="错误"，TRUE，FALSE)”；单击 “格式”按钮，弹出“设置单
元格格式”对话框，在“字体”选项卡下将“字体颜色”设置为“标准色／红色”，在“填充”
选项卡下将“背景颜色”设置为“浅绿色”，如图
2．9 所示，最后单击“确定”按钮关闭对话框。
发布评论