excel第四讲-数据处理与分析功能2

数据的筛选
数据管理时经常需要从众多的数据中挑选出一部分满足条件的记录进行处理。

筛选的功能是在庞大的数据中筛选出满足指定条件的记录，便于用户对特定的数据进行编辑和修改，是Excel提供的最为实用的数据处理功能之一。

筛选区域仅显示满足条件的行，该条件由用户针对某列指定。

Microsoft Excel 提供了两种筛选区域的命令：自动筛选和高级筛选。

与排序不同，筛选并不重排区域。

筛选只是暂时隐藏不必显示的行。

它们可以将那些符合条件的记录显示在工作表中，而将其他不满足条件的记录隐藏起来；或者将筛选出来的记录送到指定位置存放，而原数据表不动。

一、自动筛选
执行“数据”/“筛选”/“自动筛选”命令，使“自动筛选”项为选中状态（打上对号），即可进行自动筛选。

自动筛选非常方便，通过它可以实现以下的筛选操作：
对某一字段筛选出符合某特定值的记录——单击需要筛选字段的筛选器箭头，从下拉菜单中直接选择某特定值即可。

例如：“筛选出学历为硕士的记录”
对同一字段进行“与”运算和“或”运算——单击需要筛选字段的筛选器箭头，从下拉菜单中选择“自定义”，在弹出对话框中进行设置即可。

例如：“筛选出学历为硕士或博士的记录“
”筛选出入职年份在2009-2010之间的记录”
对不同字段之间进行“与”运算——只要通过多次进行自动筛选即可。

例如：“筛选出学历为硕士并且专业为会计的记录”
可以筛选出最大／最小的若干个／若干百分比）记录——只要单击需要筛选字段的筛选器箭头，从下拉菜单中选择“前10个””即可。

例如：“筛选出年龄最大的5个记录”
但是，自动筛选无法实现多个字段之间的“或”运算，这时就需要使用高级筛选
操作步骤
一、自动筛选数据
“自动筛选”适用于简单条件。

例1：查看“喜客多超市”和“学林便利”销售数量在5以下或者20以上的产品的销售情况，操作要点如下：
1、复制工作表用于查询操作。

由于多项分析工作都需要基于原始的销售数据一览表来进行，因此为了保证原有的销售统计数据不被更改，可以复制工作表用于查询、分类汇总分析等操作。

在“销售数据一览表”工作表上单击鼠标右键，选择“移动或复制工作表”命令。

设置移动或复制工作表的位置，并选中“建立副本”复选框。

单击“确定”按钮即可建立名为“销售记录汇总（2）”的工作表，在该工作表上双击将其重命名为“销售查询”。

2、选中数据编辑区域任意单元格。

3、单击“数据”——“排序和筛选”——“筛选”，为各个列标识添加筛选功能（右侧出现下拉按钮）
4、单击“客户名称”列标识右侧的下拉按钮，打开下拉菜单，在文本筛选中，去掉全选，然后选中要查看的客户名称，如下图所示
5、单击“数量”列标识右侧的下拉按钮，打开下拉菜单，选中要查看的数量，此处选择“数字筛选”——“自定义”打开“自定义自动筛选方式”对话框设置筛选条件。

二、高级筛选
高级筛选可以一次性对多个条件进行筛选。

当指定与两列或两列以上有关的筛选条件及连接符【或】；例如，在成绩统计表中找出有一门及以上不及格的学员的记录，不能用自动筛选实现。

高级筛选与自动筛选不同，它要求在一个工作表区域内单独指定筛选条件，称为条件区域。

条件区域由条件标题行和条件表达式组成，如果条件表达式是
在同一行的不同单元格中，Excel 适用AND运算符连接，表示将返回匹配所有单元格中条件的数据，如果表达式是在条件区域中的不同行中，Excel适用OR 运算符连接，表示匹配任何一个单元格中条件的数据都将返回。

条件区域要与数据区域分开（即和数据区域隔开至少一行或一列）。

使用高级筛选的注意事项：
高级筛选必须指定一个条件区域。

如果“条件区域”与数据库表格在一张工作表上，筛选前，确定光标放
置到数据库中某一单元格上。

执行“将筛选结果复制到其他位置”时，在“复制到”文本框中输入或选取将来要放置位置的左上角单元格即可，不要指定某区域。

条件区域可以定义多个条件，以便用来筛选符合多个条件的记录。

例2，在成绩统计表中找出有一门以上不及格的学员的记录
1、复制工作表用于查询操作。

在“培训成绩统计”工作表上单击鼠标右键，选择“移动或复制工作表”命令。

设置移动或复制工作表的位置，并选中“建立副本”复选框。

单击“确定”按钮即可建立名为“培训成绩统计（2）”的工作表，在该工作表上双击将其重命名为“不及格查询”。

2、建立条件区域
3、选中数据区域A3：K24，单击数据——排序和筛选——高级，打开“高级筛选”对话框。

4、在“方式”下，选中“将筛选结果复制到其他位置”复选项。

5、默认情况下，“列表区域”以选中你光标所在的数据区域，如果用户需要修改，可以单击“列表区域”右侧的拾取器按钮来进行选取。

6、单击“条件区域”右侧的拾取器，按钮，通过“高级筛选——条件区域”对话框，在工作表中选取输入的筛选条件单元格区域，并单击拾取器按钮返回到“高级筛选”对话框中。

7、单击“复制到”右侧的拾取器按钮，选取筛选数据复制到的区域的起始单元格。

8、全部设置完成后，单击“确定”按钮。

三、数据分类汇总
分类汇总
分类汇总是指将数据按指定的类进行汇总分析，在进行分类汇总前先要对所汇总数据按分类关键字进行排序。

分类汇总可以将数据按照不同的类别进行统计。

分类汇总不需要输入公式，也不需要使用函数，Excel将自动处理并插入分类结果。

在对数据分类汇总之前，我们要明确三个问题，第一个问题：分类的依据（也称分类字段）是什么？第二个问题：汇总的对象是什么？第三个问题：汇总的方式是什么？
例3，统计各个品种产品的交易数量和交易金额的总和。

操作要点：
1、利用“销售数据一览表”表复制产生“产品交易汇总表”
2、选中分类关键字“品种”排序
3、单击“数据”——“分级显示”——“分类汇总”，打开“分类汇总”
对话框。

设置“分类字段”、“汇总方式”和“选定汇总项”。

4、
5、设置完成后，单击“确定”按钮即可按指定条件进行分类汇总。

例4，统计各客户销售数量，交易金额的总和。

操作要点：
利用“销售数据一览表”表复制产生“产品交易汇总表2”
选中分类关键字“客户名称”排序
1、单击“数据”——“分级显示”——“分类汇总”，打开“分类汇总”
对话框。

设置“分类字段”、“汇总方式”和“选定汇总项”。

2、
设置完成后，单击“确定”按钮即可按指定条件进行分类汇总。

合并计算
Excel 2010中若要汇总和报告多个单独工作表的结果，可以将每个单独工作表中的数据合并计算到一个主工作表中。

这些工作表可以与主工作表在同一个工
作簿中，也可以位于其他工作簿中。

对数据进行合并计算就是组合数据，以便能够更容易地对数据进行定期或不定期的更新和汇总，对于单表来说其功能同按条件汇总函数SUMIF、A VERAGEIF等，但使用合并计算，可以实现对多个表的汇总，并且不需要写函数，更为快速。

例如，如果您有一个用于每个地区办事处开支数据的工作表，可使用合并计算将这些开支数据合并到公司的开支工作表中。

这个主工作表中可以包含整个企业的销售总额和平均值、当前的库存水平和销售额最高的产品。

例：假如销售数据表分别存在两个工作表内，汇总两个表中各各品种的销售数量和交易金额的和。

分析：若采用SUMIF函数，那么用于条件判断的单元格区域与需要求和的实际单元格区域均有两个区域，而这两个区域是不连续的，在表示区域范围是要用到“,”号，而“,”在函数中表示参数分隔符，因此采用SUMIF无法进行汇总计算。

而采用合并计算则可以完成多表的汇总计算。

步骤：
（1）选中一个单元格作为存放结果的起始单元格，选择“数据”选项卡上“数据工具”组中的“合并计算”命令。

（2）在图3.14中，在“引用位置”框中输入或选取区域，单击“添加”按钮，同样的操作将另一个区域。

（3）选中“首行”“最左列”复选框，单击“确定”按钮，选择区域的“首行”为汇总标题，按照最左列的内容进行汇总计算各项总和。

数据透视表
数据透视表示一种可以从源数据列表中快速提取并汇总大量数据的交互式表格。

使用数据透视表可以汇总、分析、浏览数据以及呈现汇总数据，达到深入分析数值数据，从不同的教徒察看数据，并对相似数据的数字进行比较的目的。

数据透视表功能能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格，合并计算、分类汇总只能根据单列分类汇总，如果需要根据多列进行汇总，必须先将两列合并为一列再进行汇总，而数据透视表可以实现按多列汇总。

例：根据销售数据一览表统计各个客户每种品种的销售量。

分析：如果采用分类汇总或者合并计算，只能按单一列的内容进行汇总，显然无法完成要求，数据透视表可以按多列进行分类汇总。

步骤：
（1）首先光标定位在要存放结果的空白单元格。

（2）进入“插入”选项卡，单击“数据透视表”，在对话框中设置，选择整个数据区域或表，单击“确定”按钮。

（3）在对话框中，按住鼠标左键拖动，选择“客户名称”在“行标签”，按住鼠标左键拖动“品种”到“列标签”，，按住鼠标左键拖动“数量”
到“数值”，在“数值”区，单击“求和项”，在弹出的对话框中选择
“值字段设置”可以更改汇总方式，比如可以更改为求平均值等。

（4）单击“确定”按钮，在数据透视表结果还可单击“行标签”或“列标签”进行数据筛选。