C21-【计算机二级】Excel操作题(21)整理和分析销售情况统计表数据

销售经理小李通过Excel制作了销售情况统计表，根据下列要求帮助他对数据进行整理和分析。

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

2.在“销售情况表”工作表中，自动调整表格数据区域的列宽、行高，将第1行的行高设置为第2行行高的2倍；设置表格区域各单元格内容水平垂直均居中，更改文本“鹏程公司销售情况表格”的字体、并加大其字号；将数据区域套用内置表格样式“表样式中等深浅27”，并退出筛选状态。

①自动调整表格数据区域的列宽、行高

全选表格，在“格式”下拉菜单中选择自动调整行高/自动调整列宽

②将第1行的行高设置为第2行行高的2倍

第2行的行高为15.8

将第1行的行高设置为31.6

③设置表格区域各单元格内容水平垂直均居中

④更改文本“鹏程公司销售情况表格”的字体、并加大其字号

⑤将数据区域套用内置表格样式“表样式中等深浅27”，并退出筛选状态

全选表格，套用表格格式

取消勾选筛选按钮

3.对工作表进行页面设置，指定纸张大小为A4、横向，调整整个工作表为1页宽、1页高，并在整个页面水平居中。

• 考点1：调整页面设置

4.将“性别”列中的“M"替换为“男”、“F”替换为“女”﹔将“预购数量”、“成交数量”两列中的空白单元格填充数字0。

①将“性别”列中的“M"替换为“男”、“F”替换为“女”

• 考点2：查找替换

②将“预购数量”、“成交数量”两列中的空白单元格填充数字0

• 考点3：定位条件

再打开查找替换，将空值全部替换为0

5.将“咨询日期”的月、日均显示为2位，如“2014-1-5"应显示为“2014-01-05”，并依据日期、时间先后顺序对数据排序。

①将“咨询日期”的月、日均显示为2位，如“2014-1-5"应显示为“2014-01-05”

• 考点4：设置单元格格式

②依据日期、时间先后顺序对数据排序

• 考点5：多条件排序

使用自定义排序，主要关键字为“咨询日期”，次要关键字为“咨询时间”

6.在“咨询商品编码”与“预购类型”之间插入新列，列标题为“商品单价”，利用公式将工作表“商品单价”中对应的价格填入该列。

①在“咨询商品编码”与“预购类型”之间插入新列，列标题为“商品单价”

②利用公式将工作表“商品单价”中对应的价格填入该列

• 考点6：VLOOKUP函数

注意要把“商品单价”列的单元格格式改为数值，否则显示不出来

7.在“成交数量”与“销售经理”之间插入新列，列标题为“成交金额”，根据“成交数量”和“商品单价”、利用公式计算并填入“成交金额”。

①在“成交数量”与“销售经理”之间插入新列，列标题为“成交金额”

②根据“成交数量”和“商品单价”、利用公式计算并填入“成交金额”

• 考点7：简单计算

8.参照文档“透视表示例.png”自新工作表“商品销售透视表”的A3单元格开始为销售数据生成数据透视表。要求按示例更改字段标题、数字格式以及透视表样式，结果应保持显示全部销售经理销售情况的状态。

透视表示例

①创建数据透视表

• 考点8：创建数据透视表

将新工作表命名为“商品销售透视表”

将销售经理拖动到筛选器，将商品咨询编码拖动到行，将预购类型拖动到列，将成交金额拖动到值

*注意：成交金额要拖动两次

②按照样例图的要求，修改表格的标签和样式

注意到样例图中有合并单元格的操作，但是在数据表当中无法直接用开始选项卡下的“合并后居中”

• 考点9：在数据透视表中合并单元格

右键→数据透视表选项

勾选“合并且居中排列带标签的单元格”

• 考点10：修改单元格格式

将金额设置为数值格式，使用千位分隔符，保留0位小数

修改占比的“值显示方式“为”列汇总百分比“

给数据透视表套用一个表格样式

9.在工作表“月统计表”中，利用公式计算每位销售经理每月的成交金额，并填入对应位置，同时计算“总和”列、“总计”行。

• 考点11：SUMIFS函数

同一列只需要修改第三个参数，即人名，所以可以直接拖动

到了第二个月以后，日期需要两个条件来限定

用SUM函数求和

10.在工作表“月统计表”的G3:O20区域中，插入与“销售经理成交金额按月统计表”数据对应的二维“堆积柱形图”，参照文档“图表示例.png”设置图表的图例、图表标题、横纵坐标轴、数据标签等，并更换图表颜色及样式。

图表示例

• 考点12：插入堆积柱形图

参照样例图，修改图表的系列名称

修改图表颜色

修改图表样式

删除图表标题，将图例放置在右侧

设置左侧的坐标轴

Ⅰ、右键选择“设置坐标轴格式“

Ⅱ、设置坐标轴的最大最小值和单位

Ⅲ、显示单位为10000，刻度线在内部

Ⅳ、调整线条的颜色和宽度

Ⅴ、设置数值类别

Ⅵ、修改标签颜色

设置一个效果

Ⅶ、设置横坐标轴的颜色和宽度

修改横坐标轴的字体和颜色

设置网格线的格式，颜色改为深蓝色

添加数据标签

修改数据标签的颜色，一月份是绿色，二月份是红色，三月份是黄色

Ctrl+S快速保存