案例1-2 数据分析

目的与要求

（一）案例目的

掌握在Excel中进行排序、筛选、分类汇总和使用数据透视表的方法。

（二）案例要求

掌握数据清单的含义，能够按照数据清单的要求组织数据。

内容及数据来源

某股票从2003年1月1日到2003年1月31日所有具有成交数量的股票交易信息，如图1-11所示。

要求根据上表所提供的信息进行如下的操作：

（1）首先将数据按照日期的降序进行排序。

（2）按照主关键字星期降序，次要关键字日期升序和第三关键字成交量升序的方式进行排序。

（3）通过自动筛选查找所有星期四的记录，筛选完成后删除筛选结果。

（4）利用高级筛选功能，筛选出1月中星期四成交量大于100万的，或者是星期五收盘价大于7元的记录。

（5）通过分类汇总的方式计算一个星期中的每一天的平均成交量。

（6）通过数据透视表计算一个星期中的每一天的平均成交量。

操作指导

1. 数据排序

用户对数据排序的对象通常是数据清单。所谓数据清单就是工作表中数据的排列方式类似数据库中表，第一行是字段名，从第二行开始是记录。数据清单中不能存在空行。

（1）打开Excel2007，在工作表中输入上图所示的数据。

（2）在工作表中选中A列任意一个有数据的单元格，打开“数据”选项卡，执行“排序和筛选”组中的降序排序命令，对上表所示信息按照日期从大到小的顺序进行排列。

说明：

排序的对象通常是数据清单，但并不意味着不是数据清单就无法排序。通常用户的记录是比较规则的，但是用户的表头未必是严格的字段名的样式，可能会有多行，如果出现这样的情况，可以选择表头的最末行，再打开“数据”选项卡，执行“排序和筛选”组中的降序排序命令。

（3）选中数据清单中任意一个单元格，打开“数据”选项卡，执行“排序和筛选”组中的“排序”命令，打开“排序”对话框，如图1-12所示。在“主要关键字”下拉列表框中选择“星期”按降序排列，单击“排序”对话框上的“添加条件”按钮，在“次要关键字”下拉列表框中选择“日期”按升序排列，在下一个“次要关键字”下拉列表框中选择“成交量”按升序排列，单击“确定”按钮，完成对排序条件的设置。

说明：

多个关键字排序的时候，首先按照主要关键字排序，在首要关键字相同的情况下按照次要关键字排序，在首要关键字和次要关键字都相同的情况下按照第三关键字排序。排序的时候Excel默认数据清单是有标题行的，标题行不会参与排序。排序后的结果部分内容，如图1-13所示。

2. 自动筛选

（1）选中数据清单中任意一个包含数据的单元格，打开“数据”选项卡，执行“排序和筛选”组中的“筛选”命令，完成自动筛选的设置，如图1-14所示。

（2）单击H1的下三角按钮，选择4，则显示所有星期四的交易记录。

（3）打开“数据”选项卡，再次执行“排序和筛选”组中的“筛选”命令，完成取消自动筛选状态的设置。

3. 高级筛选

假设要筛选1月中星期四成交量大于100万的，或者是星期五收盘价大于7元的记录，操作步骤如下。

（1）在 A23:C25 单元格区域中输入如图1-15所示的条件，完成高级筛选条件的设置。

说明：

高级筛选条件中的字段名要和数据清单中的字段名完全一致，最好是从数据清单中将字段名复制过来。在同一行中各个条件之间的关系是并且的关系，例如在第24行，其条件就是表示要筛选出星期四并且交易量大于100万的记录，行与行之间的关系是或者的关系，整个条件区域的条件就是星期四成交量大于100万的，或者是星期五收盘价大于7元。

（2）选中数据清单中任意一个包含数据的单元格，打开“数据”选项卡，执行“排序和筛选”组中的“高级”命令，打开“高级筛选”对话框，如图1-16所示。在列表区域显示的是“A1:H21”单元额区域，单击条件区域后的折叠按钮，选择“A23:C25”单元格区域，完成高级筛选的设置，单击“确定”按钮，将在原来的数据清单区域显示筛选结果。

说明：

在数据清单中任意选择一个单元格，那么打开高级筛选对话框的时候，Excel 会自动选择将选中单元格所在的数据清单作为列表区域。经过高级筛选后的结果如图1-17所示。

（3）打开“数据”选项卡，再次执行“排序和筛选”中的“清除”命令，完成取消筛选的结果。

4. 分类汇总

一个字段的值通常有重复的，这些重复的值就构成了很多类，例如对于星期这个字段，就会有几个重复的值。分类汇总能够针对其中的每一天进行汇总。分类汇总在操作之前要进行排序操作。

（1）选中H列中任意一个有数据的单元格，打开“数据”选项卡，执行“排序和筛选”组内的升序命令，完成将数据清单按照星期的顺序从小到大的进行排列。

说明：

分类汇总之前要对分类的字段进行排序。

（2）打开“数据”选项卡，执行“分级显示”中的“分类汇总”命令，打开“分类汇总”对话框，如图1-18所示。在“分类字段”下拉列表框中选择“星期”字段，在“汇总方式”下拉列表框中选择“平均值”的汇总方式，在“选定汇总项”中勾选“成交量”复选框，单击“确定”按钮，完成分类汇总。

说明：

分类汇总之后的结果如图1-19和图1-20所示。

（3）打开“数据”选项卡，执行“分级显示”中的“分类汇总”命令，打开“分类汇总”对话框，单击“全部删除”按钮，完成取消“分类汇总”。

5. 数据透视表

数据透视表是一种快速高效的分析方法，它是一个非常复杂的工具，本案例仅仅介绍了最简单的使用方法。通过数据透视表能够快速的计算一个星期中的每一天的平均成交量。使用数据透视表之前不需要对数据进行排序操作，用户只需要保证所分析的数据表是一个数据清单即可。具体的操作步骤如下所示：

（1）选中数据清单中任意一个单元格，打开“插入”选项卡，执行“表”组中的“数据透视表”命令，打开“创建数据透视表”对话框，选择A1:H21单元格区域，如图1-21所示。单击“确定”按钮，完成数据源区域设置。

（2）在如图1-22所示的“数据透视表字段列表”对话框中，将“星期”字段拖动到行标签内，将“成交量”字段放置到数值区域，完成对数据透视表布局设计。

说明：

在上图中行标签的含义就是将星期这个字段中不同的值放在行上，实际上是不同的星期数据形成了一列。同理列标签的含义就是该字段中所有不同的值放在列上，不同的数据形成了一行，最终的结果如图1-23所示。要注意的是用户生成的数据透视表不一定和下图所示的完全一致，但是其操作方法是一样的。

（3）双击“求和项:成交量”所在的单元格，打开如图1-24所示的“值字段设置”对话框，将计算类型更改为“平均值”，单击“确定”按钮，完成对计算类型的更改。

使用数据透视表的最终结果如图1-25所示，该结果与分类汇总的计算结果是一致的。