第10讲 Excel 的数据处理与分析文档格式.docx

第10讲 Excel 的数据处理与分析文档格式.docx

《第10讲 Excel 的数据处理与分析文档格式.docx》由会员分享，可在线阅读，更多相关《第10讲 Excel 的数据处理与分析文档格式.docx（20页珍藏版）》请在冰点文库上搜索。

汉字：

一种是按照汉语拼音的字典顺序；

一种是按照笔画顺序。

例如：

“A100”和“A1”相比，前者第三个字符是“0”，后者没有第三个字符，所以按照升序排列的顺序为：

A1，A100；

“安乡办”与“王二”按照拼音升序排序时，“安乡办”在前；

按照笔画升序排序时，“王二”排在“安乡办”之前。

1.1.2数值排序

1.降序

【例1.1】某班级期末成绩已经计算出了每位同学的平均分，需要按照成绩从高到低排列，以便为前三名同学发放奖学金。

操作方法：

步骤二单击定位在“平均分”数据列中的任意单元格中，单击“开始”选项区中“排序和筛选”按钮右侧下拉列表中的“降序”命令（也可以单击“数据”选项区中的“降序”按钮命令）

图1.1“开始”选项中的“排序和筛选”命令

图1.2“数据”选项区中的“降序”按钮

步骤三得到如所示有序成绩表。

图1.3按照平均分降序排列的有序成绩表

2.升序

方法相同。

1.1.3汉字与字符排序

按照拼音：

实行对应位置字符相比较的方法，依次比较拼音字母，直到分出大小（如果两个汉字的拼音字母全部都相同，则这两个汉字的大小就相同）。

按照笔画：

实行对应位置字符相比较的方法，依次比较笔画多少，直到分出大小。

1.按照汉语拼音排序

汉字排序的默认方法。

直接单击“升序”或者“降序”按钮命令即可。

2.按照笔画排序

单击排序数据表中任意单元格，在“开始”选项卡“编辑”组中单击“排序和筛选”按钮右侧下拉列表中的“自定义排序”命令，弹出“排序”对话框。

图1.4设置排序方式

3.多关键字排序

在默认情况下“排序”对话框中只有设置“主要关键字”一个条件，单击“添加条件”按钮后，Excel2010就会在对话框中添加一个“次要关键字”，最多可以添加至64个排序关键字。

【例1.1】按照“平均分”降序排序，如果两人“平均分”相同，再按照“文学”降序排序。

图1.5多关键字排序

1.1.4自定义排序次序

按照人们指定的一种特殊次序排序，例如月份、星期、日期等。

【例1.1】某大学有7个学院（计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院），对其下属的各个学院进行综合排名，排名的因素要考虑到学院成立的日期、现有师生规模、未来的发展前景等，排名结果如图1.6所示。

图1.6按照排名次序排列各学院

如果表格中数据（此处指的是二级学院的数量）比较简单，可以按照排名次序在输入数据的时候完成，但是对于一个已经建立好的数据表，则可以人为指定数据的排列次序。

步骤二单击“文件”标签卡，然后单击“选项”按钮，打开如图1.7所示“Excel选项”面板，单击“常规”下的“编辑自定义列表…”按钮，弹出“选项”对话框。

步骤三在工作表中的B3:

B9区域输入“计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院”，单击“导入”按钮，将一个用户自定义序列添加到了Excel2010系统中（也可以在图1.8中的“输入序列”文本框中直接输入自定义的序列）

图1.7“高级”选项中的“编辑自定义列表”按钮

图1.8添加自定义序列

当需要按照自定义序列输入二级学院的名称时，则只需要输入第一个二级学院名称，然后复制得到所有二级学院名称。

1.1.5账号、零件号等排序

账号、社会保险号、服务号、零件号等一般由几部分组成。

例如，有两个零件号“ZK-999-10”和“ZK-1000-10”，在比较大小的时候，会得出“ZK-999-10”>

“ZK-1000-10”的错误结论。

因为这两个编号只能按照文本的方式进行比较大小，根据字符比较规则，两个编码的前三个字符都是“ZK-”，所以进行第四个字符的比较，9大于1，所以得出上面的错误结论。

解决方法有两种：

方法一，确保同类编号中的任何部分都有相同的长度。

例如，将“ZK-999-10”输入为“ZK-0999-10”，就不会出错了；

方法二，将编号的不同部分输入在不同列的单元格中，如所示。

在排序时，对编号所涉及的三列同时进行排序，结果就不会出错，最后用“&

”运算符将排序后的各部分组合起来。

1.2数据筛选

定义：

找出并且显示满足一定条件的几行或者几列数据，将工作表中所有不满足条件的数据行暂时隐藏起来。

Excel2010支持按照多个列进行筛选，筛选是累加的，后一次筛选在前一次的筛选结果中进行，从而减少数据的子集。

筛选之后得到的数据，可以直接复制、查找、编辑、设置格式、制作图表和打印。

两种筛选方式：

自动筛选和高级筛选。

说明：

筛选时标题行出现的下拉三角箭头，在打印的时候会自动隐藏，打印结果与正常报表一样。

1.2.1自动筛选

【例1.1】某公司的人员档案是按照招聘的次序建立的，如果需要分别得到如下信息：

已婚人员的名单、各部门的人员档案、工资高于1200的人员名单、奖金高于1000的人员名单、各种学历的人员名单……那么需要怎样的数据处理呢？

图1.9自动筛选前的报表

图1.10筛选已婚人员名单

自定义筛选：

当然，并非所有的筛选条件都这样简单，有时需要按照数值的大小进行筛选，有时需要筛选出同时满足两个条件的数据…….这些都需要通过自定义筛选条件来实现。

如果筛选关键字对应的列是数值型数据，可以通过“数字筛选”命令指定自定义筛选条件，实现灵活的数据查询。

【例1.2】在如图1.9所示的职工档案表中，筛选年龄最大的三名职工。

【例1.3】在“电脑及耗材销售表”工作表中查询所有姓“张”的销售代表的数据。

图1.11自定义自动筛选方式

在“销售代表”下拉列表的“文本筛选”框内输入“张*”（用*代表任意多个字符），单击“确定”按钮，得到所有姓“张”的销售代表数据，如图1.12所示。

图1.12所有“张”姓销售代表数据筛选结果（部分）

知识链接：

自定义自动筛选方式中的“与”和“或”

如图114所示的对话框中，可以设置两个用“与”或用“或”连接的条件。

例如，要查询销售金额在5000元（含5000元）到12000元（含12000元）之间的数据，可以按照图116所示进行设置即可得到相应筛选结果。

图1.13

图1.14使用“与”自定义自动筛选方式

1.2.2高级筛选

自动筛选只能完成简单的数据筛选，如果筛选条件比较复杂，就需要采用高级筛选来完成。

使用高级筛选可以对某列或者多列应用多个筛选条件，这些条件既可以是与条件，也可以是或条件，或与条件，与或条件的组合使用，还可以使用计算条件。

为了使用此功能，在工作表的数据清单上方，需要建立条件区域，为此至少需要留有三个空行，而且数据清单必须有列标。

1.条件区域

“条件区域”为包含一组搜索条件的单元格区域，其中含有一个条件标志行，同时至少有一行用来定义搜索条件。

条件可以是文本、数值、比较式或者计算公式。

在Excel中条件区域的构造规则是：

●同一列中的条件表示“或”，即OR（只要其中某一个单元格的条件成立，则整个条件就成立）；

●同一行中的条件表示“与”，即AND（只有所有单元格中的条件都成立，整个条件才成立）。

在“电脑及耗材销售表”中，我们要在四种情况下对原始数据进行高级筛选，需要创建四个条件区域。

●或条件：

查看仅包含“宋晓”、“张默”和“刘思琪”三位销售代表的数据；

●与条件：

查看仅包含【类别】为“畅想系列”、【品名】为“家用电脑”、【销售代表】为“张默”以及【销售金额】为“>

1000”的数据；

●或与条件：

显示所有在【类别】列中包含“家用电脑”、或在【销售代表】列中包含“张默”或在【销售金额】列中包含“>

2000”的数据行；

●与或条件：

显示所有在【销售代表】列中包含“张默”且【销售金额】>

2000且【品名】为“大明投影仪”的数据行，同时也显示【销售代表】列中包含“宋晓”且【销售金额】>

2000且【品名】为“大明投影仪”的数据行。

图1.15创建条件区域

【例1.1】查询仅包含“宋晓”、“张默”和“刘思琪”三位销售代表的数据。

如果要求某一列数据满足两个以上的“或”条件，就需要创建一个在一列上的条件区域，从上到下逐行输入各个条件，这种建立在同一列上的条件是逻辑“或”的关系，相当于OR。

具体操作方法如下：

步骤二在数据区域上方插入多个空行，在A3:

A6单元格区域中构建如图1.16所示“1.或条件”条件区域。

图1.16创建条件区域

步骤三单击数据区域任意单元格，切换至“数据”选项卡，单击“排序和筛选”组中的“高级”按钮，弹出如图1.18所示“高级筛选”对话框，系统自动给出了列表区域；

图1.17“高级”筛选按钮

图1.18高级筛选（或条件）

步骤四单击“条件区域”右侧的设置按钮（也可以用鼠标拾取单元格区域），选定$A$3:

$A$6条件区域；

步骤五单击此按钮返回“高级筛选”对话框，最后单击“确定”按钮结束操作，得到只有“宋晓”、“张默”和“刘思琪”三人数据的高级筛选结果。

【例1.1】查看仅包含“类别”为“畅想系列”、“品名”为“家用电脑”、“销售代表”为“张默”以及“销售金额”为“>

1000”的数据。

要查找同时满足不同数据列中某个条件的数据，应该建立一个在同一行中输入所有条件的条件区域（如图1.16所示“2.与条件”）。

输入在同一行中的条件是逻辑“与”的关系，相当于AND。

步骤和方法：

步骤六按照C3:

F3单元格条件区域进行高级筛选结果如图1.19所示。

图1.19高级筛选（与条件）

【例1.1】查找所有在“类别”列中包含“家用电脑”、或在“销售代表”列中包含“张默”或在“销售金额”列中包含“>

2000”的数据行。

要查找满足多列数据条件的数据，应该建立一个将不同列的条件输入在不同行中的条件区域（如图1.16所示“3.或条件”）。

在不同列、不同行中的条件仍然是“或”条件。

按照H3:

J6单元格条件区域进行高级筛选，如图1.20所示。

图1.20高级筛选（或条件）

【例1.2】查询“销售代表”为“张默”、“销售金额”>

2000同时“品名”为“大明投影仪”的数据行，同时也显示“销售代表”为“宋晓”、“销售金额”>

2000同时“品名”为“大明投影仪”的数据行。

要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，应在各行中输入条件（如图1.16“4.与或复合条件”所示）。

操作步骤和方法：

按照L3:

N6单元格条件区域进行高级筛选

图1.22高级筛选（与或复合条件）

2.使用计算条件进行高级筛选

前面三位筛选能够满足大多数需要，但是还有一类筛选它并不能够实现。

例如，要找出数据清单中高于平均“销售金额”的数据行（类似的还有找出学生成绩表中高于平均分的学生记录、找出工资表中高于或低于平均收入的职工档案等），因为平均销售金额不是一个已知数据，需要根据数据清单进行计算。

但是，假如先计算出平均值，再用计算结果进行筛选，这样当然可以完成工作，但如果数据清单发生了变化（例如，增加记录行、修改某人的销售额等），这个筛选结果就不准确了。

实际上，Excel也可以将公式的计算结果作为条件使用，从而进行高级筛选，完成上述数据查找，但必须根据一定的规则创建包含计算条件的条件区域。

步骤一将“电脑及耗材销售表”复制到sheet3中，将其重命名为“电脑及耗材销售表（使用计算条件）”。

步骤二如图122所示，在数据表的上面构建条件区域。

在H3单元格中输入中间值计算公式“=MEDIAN（G6:

G215）”；

在A3单元格中输入筛选条件“=G6>

$H$3”，确定之后分别得到图示结果。

图1.23使用计算条件构建条件区域

步骤三激活数据表区域，执行【数据】|【筛选】|【高级筛选】菜单命令，打开【高级筛选】对话框，切换至【将筛选结果复制到其他位置】；

选择图122中计算结果“FALSE”所在单元格区域“$A$2：

$F$3”为条件区域；

选择“$K$5”作为放置复制筛选结果区域的左上角单元格。

如图123所示。

图1.24建立基于计算条件的高级筛选

步骤四单击【确定】按钮，得到如图123所示的基于计算条件的高级筛选结果。

在上述筛选条件中包含一个平均值计算公式，使用计算条件进行高级筛选要遵循以下原则：

●计算条件的列标题（A2单元格）可以是任何文本，也可以什么都不输，但不能与数据清单中的任一列标题（A5:

I5单元格区域）相同，这一点正好与前面讨论的条件区域相反。

●必须以绝对引用的方式引用数据清单之外的单元格（$H$3单元格中的销售金额中间值）。

●必须以相对引用的方式引用数据清单之内的数据单元格（G6单元格）。

在【高级筛选】对话框中进行操作时，若筛选后要隐藏不符合条件的数据行，并让筛选的结果显示在数据清单中，可切换【在原有区域显示筛选结果】单选按扭。

若要将符合条件的数据行复制到工作表的其他位置，则需要切换【将筛选结果复制到其他位置】单选按钮，并通过【复制到】编辑框指定粘贴区域的左上角，从而设置复制位置。

在实际工作中，将筛选结果从数据表中独立出来的方式很有用，因为这样可以对同一数据表进行多次不同条件的筛选操作，将每次筛选的结果存放在不同的区域，便于对数据进行分析以及制作不同形式的报表。

条件区域

条件区域一般用于高级筛选或数据库函数中。

在一般情况下，应该将条件区域构建在数据清单的开头或者旁边（最好是数据清单的前面），不要再数据清单的下面构建条件区域。

并非不允许这样做，其原因是如果数据清单要添加数据行，可能会覆盖条件区域，引起不必要的麻烦。

要建立条件区域的数据清单必须要有标题行（如图121所示的A11:

I11单元格区域），否则就构造不了该图中的各条件区域。

注意：

在进行高级筛选时，可以将某个区域命名为Criteria。

此时【条件区域】框中就会自动出现对该区域的引用。

也可以将要筛选的数据区域命名为Database，并将要粘贴行的区域命名为Extract，这样，Excel就会让这些区域自动出现在【列表区域】和【复制到】框中。

1.3数据的分类与汇总

如果一个工作表中数据行太多，就很难从中看清某些信息。

要想将相同类型的数据统计出来，就需要对数据进行分类汇总。

分类汇总能够对工作表数据按照不同的类别进行汇总统计（求总和、平均值、最大值、最小值、统计个数等）。

分类汇总采用分级显示的方式展示工作表数据，它可以折叠或展开工作表的数据行（或列），快速创建各种汇总报告。

Excel2010允许多达8层的分级显示。

1.3.1分类汇总概述

1.3.2建立分类汇总

1.分类汇总的准备工作

在使用分类汇总之前，要确定以下两件事情已经做好：

●要进行分类汇总的数据表的各列必须有列标题；

●必须对要进行分类汇总的数据列排序。

这个排序的列标题称为分类汇总关键字，在进行分类汇总时，只能指定已经排序的列标题作为汇总关键字。

2.建立分类汇总

【例1.1】将各类别电脑及耗材的“销售金额”总和分别进行汇总，具体操作方法如下：

步骤二按照关键字“类别”进行排序。

步骤三单击数据区域任意单元格，在“数据”选项卡中】|【分类汇总】菜单命令，弹出如图1.22所示【分类汇总】对话框。

图1.25按照“类别”对“销售金额”进行求和分类汇总

步骤四在【分类字段】选项下选择“类别”，【汇总方式】选项下选择“求和”，【选定汇总项】选项下选定“销售金额”，单击【确定】按钮，得到如图124所示分类汇总结果，从中可以清楚地看到各类别商品的销售金额总和。

分级按钮

在图124中，左上角有3个按钮（

），按钮

表示1级汇总，显示全部的销售金额汇总；

按钮

表示2级汇总，显示各类别的全部销售金额汇总；

表示3级汇总，显示各类别的销售金额的汇总明细及汇总额（即图124所示的汇总结果）。

图124中，左边的滑动按钮

为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时

变为显示明细按钮

，再单击

按钮，则将显示本级的全部明细数据，同时

变为

。

1.3.3高级分类汇总

1.3.4嵌套分类汇总

在上述自动分类汇总的结果上，还可以再进行分类汇总，两次分类汇总的关键字可以相同，也可以不同。

如果两次关键字不同，需要事先按照第一关键字和第二关键字对数据表重新排序。

另外在进行第二次分类汇总时，要取消对话框中【替换当前分类汇总】复选框的勾选，最终可以得到嵌套分类汇总的结果。

1.3.5删除分类汇总

如果不再需要分类汇总结果，将数据表恢复到分类汇总之前的状态，可在图124所示对话框中单击【全部删除】，即可撤消分类汇总。

1.4数据透视表

数据透视表是一种快速汇总大量数据的交互式方法，使用数据透视表可以深入分析数值数据，对数据表进行汇总、分析、浏览和提供摘要数据。

1.4.1数据透视表概述

具有以下常用功能：

●对数值数据进行分类汇总和聚合，按照分类和子分类对数据进行汇总，创建自定义计算和公式；

●展开和折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细；

●将行移动到列或者将列移动到行（或“透视”），以查看源数据的不同组合与汇总的结果；

●对最有用的和最关注的数据子集进行筛选、排序和分组以及有条件地设置格式，突出显示重要信息；

●提供简明、带有批注的联机报表或者打印表。