科技公司销售情况分析：商品汇总

任务介绍

小王收到一季度销售情况表，经理要求小王把这个表按销售额排序，按条件筛选出商品的销售情况，并把各销售部一季度的平均销售额汇总后给他。小王对Excel的排序、筛选和分类汇总功能比较熟悉，很快完成了任务。

任务分析

◆通过“排序”对话框实现按销售数量对商品的销售额进行降序排列。

◆通过在“排序”对话框中自定义排序序列，实现对先按月升序排序，在按商品销售额进行降序排列。

◆通过自动筛选功能可以完成对1月份的华硕-R417电脑销售情况。

◆应用高级筛选功能，将第一销售部1月份销售数量超过100台的销售数据及“索尼-EA35”在3月份的销售情况进行列表显示（设置筛选条件区域）。

◆通过分类汇总统计各销售部1～3月的平均销售额。

知识准备

Excel不仅具有较强数据计算的能力，还具有对数据进行排序、筛选、分类汇总等数据管理功能。虽然Excel的数据管理功能不适合大规模的数据管理，但相对一般数据库，其操作方便、直观、高效，更适合数据量不大的普通用户使用。

1.数据清单的概念

数据清单又称为数据列表，数据清单在Excel中的具体体现就是工作表中的一个区域，即一张二维表。在数据清单中有以下约定。

1）记录

数据清单区域中的一行称为记录。一个记录占据一行，记录间不允许有空行。第一行为表头，由若干个字段名组成，字段名又称数据项名称。数据项名称与第一行记录间不能留空行，数据清单中不允许有内容完全相同的两行。

2）字段

数据清单区域中的一列称为字段，其中第一行为字段名。每一字段必须是性质相同、类型相同的数据。

图4-4-1　数据清单示例

如图4-4-1所示是一个数据清单示例。其中A1：G1为字段名行。A2：G2为其中一个记录，B2：B11为一字段（没有包括字段名），它们都是由教师姓名构成。

2.数据排序

在Excel中可以根据一列或多列的数据按升序或降序对数据清单进行排序。排序的依据可以是数据文本、数值、日期和时间的升序或降序，也可以是自定义序列和格式（包括单元格颜色、文本颜色）。

1）简单排序

简单排序是指对单一字段按升序或降序排列，其操作方法比较简单：

（1）首先选中参与排序区域（不仅是作为排序依据的字段），使当前单元格在作为排序依据的字段上。

（2）然后单击工具栏的“”或“”按钮来快速地升序或降序排列。

2）多关键字排序

如果数据清单中排序依据的字段有多个时，就需使用多关键字排序，方法是：

（1）选中参与排序区域，确保所有排序依据的字段都在其中。

（2）在“数据”选项卡的“排序和筛选”组中，单击“排序”。这时将显示“排序”对话框。

（3）在“排序”对话框中选择“主要关键字”，然后在“排序依据”下拉列表框中选择排序依据（“数值”“单元格字体”“单元格颜色”“单元格图标”）。

（4）根据需要，在依次选择一个或多个“次要关键字”，进行相应设置后单击“确定”按钮。

3.数据筛选

数据筛选就是指将数据清单中符合条件的记录显示出来，并隐藏不符合条件的记录。当筛选条件被删除时，隐藏的记录便又恢复显示。

筛选有两种方式：自动筛选和高级筛选。

1）自动筛选

使用自动筛选可以创建三种筛选类型：按值列表、按格式或按条件。对于每个单元格区域或列表来说，这三种筛选类型是互斥的。

自动筛选的操作步骤是：

（1）选择工作表中用于筛选的数据清单（一般包括字段名）。

（2）在“数据”选项卡上的“排序和筛选”组中，单击“筛选”。这时每个字段名的旁边将出现一个下拉箭头按钮。

（3）单击某个字段名旁边的下拉箭头按钮，在下拉列表框中选择所要筛选的确切值，或者自定义筛选条件，即可完成自动筛选。

2）高级筛选

当筛选条件较复杂时，用“自动筛选”功能就不便于达到目的，这时可用“高级筛选”功能。

4.分类汇总

分类汇总是Excel的一项重要功能。就是对数据清单按某字段进行分类，然后再进行求和、平均、计数等汇总运算。分类汇总命令的主要步骤如下：

1）首先必须对分类的列数据进行排序。

2）选择工作表中用于分类汇总的数据清单。

3）在“数据”选项卡上的“分级显示”组中，单击“分类汇总”，打开“分类汇总”对话框，如图4-4-2所示。

图4-4-2　分类汇总对话框

4）在打开的“分类汇总”对话框中，设置有关选项：在“分类字段”下拉列表中选择分类排序字段，如选择“销售部门”；在“汇总方式”下拉列表中，选择汇总计算方式，如选择“平均值”；在“选定汇总项”中，选择需要汇总的字段，如选择“销售金额”。单击“确定”按钮。

5）在分类汇总结果中，单击工作表左边的“”按钮，可以仅显示汇总值而隐藏原始数据库的数据，这时工作表左边变为“”按钮；再次单击“”按钮，将恢复显示隐藏的原始数据。如果要取消分类汇总，重复上述操作，在打开的“分类汇总”对话框中单击“全部删除”按钮即可。

任务实施

1.打开配套素材“Excel2010”→“某科技公司商品销售表.xlsx”。

2.在当前打开的文件中复制标签为“某科技公司商品销售表A”的表，重命名为“某科技公司商品销售表-排序”，按销售数量降序排。鼠标单击字段“销售数量”，然后单击“数据”选项卡，在功能区选择“排序和筛选”组的降序。(www.xing528.com)

3.在当前打开的文件中复制标签为“某科技公司商品销售表A”的表，重命名为“某科技公司商品销售表-自动排序”，先按月份升序排序，当月份相同时按销售金额降序排序。鼠标单击“数据”选项卡，在功能区选择“排序和筛选”模块的降序，打开如下图4-4-3所示。

图4-4-3　自动排序

4.查看一月份华硕-R417电脑销售情况。在当前打开的文件中复制标签为“某科技公司商品销售表A”的表，重命名为“某科技公司商品销售表-筛选”。鼠标单击“数据”选项卡，在功能区单击“排序和筛选”模块的筛选按钮。这时每个字段名的旁边将出现一个下拉箭头按钮。分别单击“月份”和“商品名称”下拉箭头，打开如下图4-4-4所示。

图4-4-4　自动筛选

5.应用高级筛选功能将第一销售部1月份销售数量超过100台的销售数据及“索尼-EA35”在3月份的销售情况进行列表显示。

1）在当前打开的文件中复制标签为“某科技公司商品销售表A”的表，重命名为“某科技公司商品销售表-高级筛选”。首先设置筛选条件区域如下图4-4-5所示。

2）单击“数据”选项卡，在功能区单击“排序和筛选”模块的高级筛选按钮打开如下图4-4-6所示的对话框。在对话框中选择将筛选结果复制到其他位置，列表区域选择数据表A2：F18区域，条件区域选择上面1）步中条件区域。其中，如果选中“在原有区域显示筛选结果”按钮，Excel可把不符合筛选条件的数据行在原数据区域暂时隐藏起来。选择“将筛选结果复制到其他位置”，可把符合条件的数据行复制到工作表的指定位置。

图4-4-5　设置筛选条件区域

图4-4-6　设置高级筛选区域

3）筛选结果如图4-4-7所示。

图4-4-7　高级筛选结果

6.通过分类汇总统计各销售部1～3月的平均销售额。

1）在当前打开的文件中复制标签为“某科技公司商品销售表A”的表，重命名为“某科技公司商品销售表-分类汇总”。

2）按分类字段排序，先按“销售部门”升序排序。

3）在“数据”选项卡上的“分级显示”组中，单击“分类汇总”，打开“分类汇总”对话框，如图4-4-8所示。

图4-4-8　分类汇总

4）在打开的“分类汇总”对话框中，设置有关选项：在“分类字段”下拉列表中选择分类排序字段中选择“销售部门”；在“汇总方式”下拉列表中选择“平均值”；在“选定汇总项”中，选择需要汇总的字段“销售金额”。最后单击“确定”按钮，完成操作。如图4-4-9所示。

图4-4-9　分类汇总结果

知识拓展

1.数据透视表与透视图

1）创建数据透视表

数据透视表是在要合计较大的列表并对每个数字进行多种比较时使用。简单地说：分类汇总适合于按一个字段进行分类，对一个或多个字段进行汇总的情况；而数据透视表适合于用户要求按多个字段进行分类汇总情况。

例如，对图4-4-10所示的某科技公司销售表，既按“销售部门”分类，又要按“月份”分类对销售金额统计平均值，这时就可利用数据透视表来解决。

图4-4-10　某科技公司销售表

数据透视表的建立的步骤是：

（1）单击“插入”选项卡“表格”组中的“数据透视表”。

（2）在“创建数据透视表”对话框中，选中“选择一个表或区域”。若要将数据透视表放置在新工作表中，并以单元格A1为起始位置，则选择“新建工作表”单选框。若要将数据透视表放在现有工作表中的特定位置，则选择“现有工作表”，然后在“位置”框中指定放置数据透视表的单元格区域的第一个单元格。

（3）在“数据透视表字段列表”对话框，如图4-4-11所示，执行下面的操作：

*若要将字段放置到布局部分的默认区域中，在字段部分中选中相应字段名称旁的复选框。默认情况下，非数值字段会添加到“行标签”区域，数值字段会添加到“数值”区域，而日期和时间层级则会添加到“列标签”区域。

图4-4-11　“数据透视表字段列表”对话框

*若要将字段放置到布局部分的特定区域中，在字段部分中右键单击相应的字段名称，然后选择“添加到报表筛选”“添加到列标签”“添加到行标签”或“添加到数值”。

在默认情况下，数据区的汇总字段如果是数值型字段，则对其求和，否则计数。单击“数值”对应字段，在下拉菜单中选择汇总方式。透视表结果如图4-4-12所示。

图4-4-12　透视表结果

2）创建数据透视图

数据透视图以图形形式，更加形象地表现数据透视表中的汇总数据，它的作用与普通图表是一样的。数据透视图与作为其数据源的数据透视表是相关联的，透视表中更改会同步反映在数据透视图中。

数据透视图与标准图表组成元素基本相同，编辑与格式化的方式也相似。与普通图表的区别除了数据源是透视表以外，还有就是当创建数据透视图时，数据透视图的图表区中将显示字段筛选器，以便对基本数据进行排序和筛选。

数据透视图的创建步骤为：

*选定已创建好的数据透视表，将其作为数据透视图的数据源。

*在“数据透视表工具”的“选项”选项卡的“工具”组中单击“数据透视图”按钮，打开“插入图表”对话框。

*选择相应的图表类型和图表子类型。

*单击“确定”生成数据透视图。如图4-4-13所数据透视图生成后，可以通过图表区中的字段筛选器，可以更改图表中显示的数据。还可以通过“数据透视图工具”中的“设计”“布局”“格式”和“分析”四个选项卡，可以对透视图进行修饰和设置。

图4-4-13　数据透视图示例