任务实施及数据分析步骤

1.数据汇总

在本书提供的“Excel素材”文件夹中将“上半年图书销售情况素材”工作簿复制一份，并重命名为“上半年图书销售情况”。打开“上半年图书销售情况”工作簿，将“第一季度图书销售情况”工作表中的“A2：L85”单元格区域复制并粘贴到“Sheet3”中以Al开始的单元格区域，将“第二季度图书销售情况”工作表中的“A3：L84”单元格区域复制到“Sheet3”中以A85开始的单元格区域，适当更改列宽使单元格内容可以完全显示出来，将“Sheet3”工作表更名为“2022上半年图书销售情况”。

2.去除重复的订单

步骤1：条件显示编号重复的订单。在“2022上半年图书销售情况”工作表中选中A列，选择“开始”选项卡→“样式”组→“条件格式”→“突出显示单元格规则（H）”→“重复值（D）...”，打开如图4-107所示的“重复值”对话框，在“设置为”下拉列表中选择“自定义格式...”，在打开的“设置单元格格式”对话框中设置字体颜色为“标准色，紫色”，字形设置为“加粗”，单击“确定”按钮，返回到“重复值”对话框，再单击“确定”按钮。

图4-107　“重复值”对话框

图4-108　“排序”对话框

步骤2：按订单编号颜色进行排序。选中A列任意单元格，选择“数据”选项卡→“排序和筛选”组→“排序”按钮，打开如图4-108所示的“排序”对话框，在“主要关键字”下拉列表中选择“订单编号”，在“排序依据”下拉列表中选择“字体颜色”，在“次序”下拉列表中选择“紫色”和“在顶端”，单击“确定”按钮。按颜色排序的结果如图4-109所示。

图4-109　按颜色排序的结果

图4-110　“删除重复项”对话框

步骤3：去除编号重复的订单。单击“数据”选项卡→“数据工具”组→“删除重复项”按钮，打开如图4-110所示的“删除重复值”对话框，单击“取消全选（U）”按钮，勾选“订单编号”复选框，单击“确定”对话框，弹出如图4-111所示的提示框，单击“确定”按钮。

3.增加“销售月份”列

将数据按“销售日期”升序排列，在“销售日期”列右侧插入一个新列，在K1单元格中输入销售，在K2单元格中输入“=MONTH（J2）＆"月"”，按【Enter】键，双击K2单元格填充柄完成“销售日期”列的填充。

说明：公式中所有的字符均是半角字符。

4.利用多重分类汇总进行数据分析

步骤1：多条件排序。复制“2022上半年图书销售情况”工作表放置在最后，重命名为“数据分析”。在“数据分析”工作表中按主要关键字“书店名称”升序、次要关键字“销售月份”升序、第三关键字“图书类型”升序进行排序。

说明：所有排序依据均为“单元格值”。

步骤2：多级分类汇总。选中数据区域的任意单元格，选择“数据”选项卡→“分级显示”组→“分类汇总”，在打开的“分类汇总”对话框中，“分类字段（A）：”选择“书店名称”，“汇总方式（U）：”选择“求和”，勾选“选定汇总项（D）：”列表框中的“销量”复选框，取消其他复选框的选定，单击“确定”按钮。

选中数据区域的任意单元格，选择“数据”选项卡→“分级显示”组→“分类汇总”，在打开的“分类汇总”对话框中，“分类字段（A）：”选择“销售月份”，“汇总方式（U）：”选择“求和”，勾选“选定汇总项（D）：”列表框中的“销量”复选框，取消“替换当前分类汇总（C）”复选框，单击“确定”按钮。

选中数据区域的任意单元格，选择“数据”选项卡→“分级显示”组→“分类汇总”，在打开的“分类汇总”对话框中，“分类字段（A）：”选择“图书类型”，“汇总方式（U）：”选择“求和”，勾选“选定汇总项（D）：”列表框中的“销量”复选框，取消“替换当前分类汇总”复选框，单击“确定”按钮。

选中“A”、“D”、“E”、“F”、“G”、“H”、“J”、“L”、“M”列，选择“开始”选项卡→“单元格”组→“格式”→“隐藏和取消隐藏（U）”-“隐藏列（C）”，如图4-112所示。分别选择分级显示列表中的2、3，按级别显示分类汇总结果。分级显示结果如图4-113、图4-114所示。

图4-111　删除重复项提示

图4-112　隐藏列

图4-113　分级显示2

图4-114　分级显示3

5.利用SUMIFS函数进行数据分析

步骤1：建立“图书销售统计”工作表。插入一张新的工作表，命名为“图书销售统计”，输入如图4-115所示的数据。选择“开始”选项卡→“样式”组→“套用表格格式”下拉列表中的“冰蓝，表样式中等深浅23”格式，打开“套用表格格式”对话框，选择表数据的来源为“$A$1：$G$5”单元格区域，勾选“表包含标题”复选框，单击“确定”按钮。选择“表格工具|设计”选项卡→“工具”组→“转换为区域”将表格转化为普通区域。

图4-115　图书销售统计表样式

步骤2：统计成功书店各月的销量。选中B2单元格，选择编辑栏上的插入函数按钮，打开“插入函数”对话框，在该对话框的“或选择类别（C）：”下拉列表中选择“数学与三角函数”，在“选择函数（N）：”下拉列表中选择“SUMIFS”函数，单击“确定”按钮，打开SUMIFS函数的“函数参数”对话框，如图4-116所示。光标置于“Sum_range”文本框中，选择“2022上半年图书销售情况”工作表中的“I2：1158”单元格区域，按【F4】键转换成单元格绝对引用。光标置于“Criteria_rangel”文本框中，选择“2022上半年图书销售情况”工作表中的“B2：B158”单元格区域，按【F4】键转换成单元格绝对引用。光标置于“Criterial”文本框中，选择“图书销售统计”工作表中的A2单元格区域，按【F4】键转换成单元格绝对引用。光标置于“Criteria_range2”文本框中，选择“2022上半年图书销售情况”工作表中的“K2：K158”单元格区域，按【F4】键转换成单元格绝对引用。光标置于“Criteria2”文本框中，选择“图书销售统计”工作表中的“B1”单元格。单击“确定”按钮完成SUMIFS函数的输入。此时编辑栏中显示的公式为“=SUMIFS（'2022上半年图书销售情况'！$I$2：$I$158，'2022上半年图书销售情况'！$B$2：$B$158，$A$2，'2022上半年图书销售情况'！$K$2：$K$158，B1）”。此时SUMIFS的“函数参数”对话框如图4-116所示。拖动“B2”单元格的填充柄至G2单元格，成功书店各月的销量统计完毕。

图4-116　SUMIFS的“函数参数”对话框

步骤3：统计各书店各月的销量。选中B2单元格，在编辑栏中复制公式，按【Esc】键后，单击B3单元格，按【Ctrl+V】快捷键进行粘贴，此时B2单元格的公式粘贴至B3单元格。将公式中第三个参数更改为“$A$3”，按【Enter】键结束输入，拖动B3单元格填充柄至G3，完成各书店各月销量统计。使用同样的方法通过复制公式和分别更改公式第三个参数为“$A$4”和“$A$5”，计算教育书店和行知书店各月的销量统计，计算完成后的数据如图4-117所示。

说明：计算时，注意更改教育书店和行知书店第三个参数的值；SUMIFS函数的用法详见“扩展知识”。

图4-117　教育书店和行知书店的销量统计

步骤4：在“图书销售统计”工作表的H1单元格中输入“销量趋势”，选中H2单元格，单击“插入”选项卡→“迷你图”组→“折线迷你图”，打开如图4-118所示的“创建迷你图”对话框，选择“数据范围（D）”为“B2：G2”，单击“确定”按钮，拖动“H2”单元格填充柄至“H5”单元格。添加折线迷你图的效果如图4-119所示。

6.利用数据透视表统计数据

（1）建立各个书店销量总和及销售额平均值的数据透视表。

图4-118　“创建迷你图”对话框

(www.xing528.com)

图4-119　添加折线迷你图的效果

步骤1：创建空的数据透视表。单击“2022上半年图书销售情况”工作表标签，选中数据区域的任意单元格，单击“插入”选项卡→“表格”组→“数据透视表”，打开如图4-120所示的“创建数据透视表”对话框，单击“选择一个表或区域（S）”单选按钮，数据区域选择“'2022上半年图书销售情况'！$A$1：$M$158”，选择“新工作表”单选按钮，单击“确定”按钮（默认设置是正确的，一般不需要更改）。此时生成一个新的工作表，重命名为“按书店分类”。在“按书店分类”工作表中出现如图4-121所示的数据透视表区域和“数据透视表字段”窗格。

图4-120　“创建数据透视表”对话框

图4-121　空数据透视表

步骤2：完善数据透视表。在“数据透视表字段”窗格中将“选择要添加到报表的字段：”列表中的“书店名称”字段拖曳至“行”列表框中，将“销量”字段拖曳至“值”列表框中，将“销售额”字段拖曳至“值”列表框中，单击“值”列表框中的“求和项：销售额”字段，在弹出的列表中选择“值字段设置（N）...”，打开如图4-122所示的“值字段设置”对话框，在“选择用于汇总所选字段数据的”下的“计算类型”列表中选择“平均值”，单击“数字格式（N）”按钮，在打开的“设置单元格格式”对话框中选择“数值”，小数位数设为“2，单击“确定”按钮返回“值字段设置”对话框。单击“确定”按钮，设置好的数据透视表如图4-123所示。

图4-122　“值字段设置“对话框

图4-123　数据透视表

（2）建立各个书店按月统计销量总和及销售额平均值的数据透视表。

单击上一步建立的数据透视表区域的任意单元格，在“数据透视表字段”窗格中将“销售日期”字段拖至“行”列表框中，选择“数据透视表工具|设计”选项卡→“布局”组→“报表布局”下拉列表中的“以大纲形式显示（O）”，如图4-124所示。选择“数据透视表工具设计”选项卡→“数据透视表样式”下拉列表中的“浅蓝，数据透视表样式中等深浅2”样式。选择“数据透视表工具|分析”选项卡→“操作”组下的“移动数据透视表”，打开如图4-125所示的对话框，选择“现有工作表（E）”单选按钮，将光标置于“位置（L）：”文本框中，单击“A1”单元格，单击“确定”按钮。按书店月份分类的数据透视表如图4-126所示。

图4-124　报表布局设置

图4-125　“移动数据透视表”对话框

图4-126　按书店月份分类的数据透视表

图4-127　更改数据透视表名称

（3）建立各书店按月、图书类型、图书名称统计销售利润总和的数据透视表。

步骤1：建立空数据透视表。打开“2022上半年图书销售情况”工作表，选中数据区域的任意单元格，单击“插入”选项卡→“表格”组→“数据透视表”，打开“创建数据透视表”对话框，选择默认值，单击“确定”按钮后生成一个新的工作表，重命名为“销售利润统计”。

步骤2：完善数据透视表。在“数据透视表字段”窗格中将“书店名称”、“销售日期”、“图书名称”字段名依次拖至“行”列表框中，将“图书类型”字段拖至“列”列表框中，将“销售利润”拖至“值”列表框中，设置“报表布局”为“以大纲形式显示（O）”，设置“数据透视表样式”为“冰蓝，数据透视表样式中等深浅9”。在“数据透视表工具|分析”选项卡→“数据透视表”组→“数据透视表”文本框中更改数据透视表名称为“销售利润统计”，如图4-127所示。生成后的数据透视表如图4-128所示。

图4-128　生成的数据透视表

步骤3：数据透视表筛选。在“数据透视表字段”窗格中将“图书名称”拖至“筛选”列表框中作为筛选字段，单击B1单元格的下拉箭头，打开如图4-129所示的对话框，选择“《C语言程序设计》”，单击“确定”按钮，选择“数据透视表工具|设计”选项卡→“布局”组→“总计”下拉列表中的“仅对列启用（C）”。《C语言程序设计》销售情况如图4-130所示。

说明：选择不同的图书名称，可以查看其在各书店各月的销售利润情况。

图4-129　筛选对话框

图4-130　《C语言程序设计》销售情况

7.利用数据透视图进行数据分析

（1）建立各书店各月销售利润的数据透视图。

步骤1：建立空数据透视图。选中“2022上半年图书销售情况”工作表数据区域中的任意一个单元格，单击“插入”选项卡→“图表”组→“数据透视图”按钮，打开如图4-131所示的“创建数据透视图”对话框，使用默认选项，单击“确定”按钮，将新建的工作表重命名为“销售利润统计图”。

图4-131　“创建数据透视图”对话框

图4-132　销售利润数据透视图

步骤2：完善数据透视图。单击生成的空数据透视图，在“数据透视图字段”窗格中将“书店名称”和“销售日期”字段拖至“轴（类别）”列表框中，将“销售利润”字段拖至“值”列表框中，建立如图4-132所示的销售利润数据透视图。单击生成的数据透视图，选择“数据透视图工具|设计”选项卡→“数据”组→“切换行/列”按钮，将数据透视图左上角拖曳至A7单元格，调整右下角至K30单元格，生成的数据透视图如图4-133所示。单击数据透视图右侧的“书店名称”或“销售日期”的下拉箭头对数据进行筛选，可以使数据透视图的数据按筛选要求部分显示。

图4-133　生成的数据透视图

（2）利用数据透视表建立数据透视图。

在“按书店分类”工作表中选中数据透视表里的任意一个单元格，选择“插入”选项卡→“图表”组→“插入饼图或圆环图”下拉列表中的“三维饼图”，在生成的数据透视图右侧的“书店名称”下拉列表中只勾选“教育书店”复选框，如图4-134所示。给饼图设置“类别名称”、“百分比”和“显示引导线”的数据标签，设置后的图表如图4-135所示。“图书类型”、“图书名称”、“书店名称”及“销售日期”字段均可根据需要进行筛选，数据透视图随之变化。

图4-134　筛选书店名称

图4-135　教育书店销量统计