第10讲 Excel 的数据处理与分析.docx

1、第10讲 Excel 的数据处理与分析第1讲 Excel 2010的数据处理与分析1.1 数据的排序排序有助于快速直观地显示、组织和查找所需数据，有助于决策报表的快速制作和更好地理解数据，是数据分析不可缺少的常见操作。1.1.1 排序规则在Excel中可以对一列或者多列中的数据按照字母、数字、日期或人为指定的顺序对数据表进行排序。排序的方式有两种：升序和降序。升序：就是按照从小到大的顺序排列数据，数字按照0，1，2，9的顺序排列；字符按照A，B，Z和a，b，z的顺序排列。降序：与升序相反。在进行升序排序时，Excel中的次序如下：数字字母逻辑值错误值空格。对于同类数据，其排序规则如下：数字：从

2、最小的负数到最大的正数进行排序。字母：按照AZ和az的顺序排序逻辑值：False（相当于0）排在True（相当于1）之前。错误值和空格：所有错误值得优先级相同，空格始终排在前面。汉字：一种是按照汉语拼音的字典顺序；一种是按照笔画顺序。例如：“A100”和“A1”相比，前者第三个字符是“0”，后者没有第三个字符，所以按照升序排列的顺序为：A1，A100；例如：“安乡办”与“王二”按照拼音升序排序时，“安乡办”在前；按照笔画升序排序时，“王二”排在“安乡办”之前。1.1.2 数值排序1. 降序【例1.1】 某班级期末成绩已经计算出了每位同学的平均分，需要按照成绩从高到低排列，以便为前三名同学发放奖

3、学金。操作方法：步骤二 单击定位在“平均分”数据列中的任意单元格中，单击“开始”选项区中“排序和筛选”按钮右侧下拉列表中的“降序”命令（也可以单击“数据”选项区中的“降序”按钮命令）图 1.1 “开始”选项中的“排序和筛选”命令图 1.2 “数据”选项区中的“降序”按钮步骤三 得到如所示有序成绩表。图 1.3 按照平均分降序排列的有序成绩表2. 升序方法相同。1.1.3 汉字与字符排序按照拼音：实行对应位置字符相比较的方法，依次比较拼音字母，直到分出大小（如果两个汉字的拼音字母全部都相同，则这两个汉字的大小就相同）。按照笔画：实行对应位置字符相比较的方法，依次比较笔画多少，直到分出大小。1.

4、按照汉语拼音排序汉字排序的默认方法。直接单击“升序”或者“降序”按钮命令即可。2. 按照笔画排序单击排序数据表中任意单元格，在“开始”选项卡“编辑”组中单击“排序和筛选”按钮右侧下拉列表中的“自定义排序”命令，弹出“排序”对话框。图 1.4 设置排序方式3. 多关键字排序在默认情况下“排序”对话框中只有设置“主要关键字”一个条件，单击“添加条件”按钮后，Excel 2010就会在对话框中添加一个“次要关键字”，最多可以添加至64个排序关键字。【例1.1】 按照“平均分”降序排序，如果两人“平均分”相同，再按照“文学”降序排序。图 1.5 多关键字排序1.1.4 自定义排序次序按照人们指定的一种

5、特殊次序排序，例如月份、星期、日期等。【例1.1】 某大学有7个学院（计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院），对其下属的各个学院进行综合排名，排名的因素要考虑到学院成立的日期、现有师生规模、未来的发展前景等，排名结果如图 1.6所示。图 1.6 按照排名次序排列各学院如果表格中数据（此处指的是二级学院的数量）比较简单，可以按照排名次序在输入数据的时候完成，但是对于一个已经建立好的数据表，则可以人为指定数据的排列次序。步骤二 单击“文件”标签卡，然后单击“选项”按钮，打开如图 1.7所示“Excel选项”面板，单击“常规”下的“编辑自定义列表”按钮，弹出“选项”

6、对话框。步骤三 在工作表中的B3:B9区域输入“计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院”，单击“导入”按钮，将一个用户自定义序列添加到了Excel 2010系统中（也可以在图 1.8中的“输入序列”文本框中直接输入自定义的序列）图 1.7 “高级”选项中的“编辑自定义列表”按钮图 1.8 添加自定义序列当需要按照自定义序列输入二级学院的名称时，则只需要输入第一个二级学院名称，然后复制得到所有二级学院名称。1.1.5 账号、零件号等排序账号、社会保险号、服务号、零件号等一般由几部分组成。例如，有两个零件号“ZK-999-10”和“ZK-1000-10”，在比较大小

7、的时候，会得出 “ZK-999-10”“ZK-1000-10” 的错误结论。因为这两个编号只能按照文本的方式进行比较大小，根据字符比较规则，两个编码的前三个字符都是“ZK-”，所以进行第四个字符的比较，9大于1，所以得出上面的错误结论。解决方法有两种：方法一，确保同类编号中的任何部分都有相同的长度。例如，将“ZK-999-10”输入为“ZK-0999-10”，就不会出错了；方法二，将编号的不同部分输入在不同列的单元格中，如所示。在排序时，对编号所涉及的三列同时进行排序，结果就不会出错，最后用“&”运算符将排序后的各部分组合起来。1.2 数据筛选定义：找出并且显示满足一定条件的几行或者几列数据，

8、将工作表中所有不满足条件的数据行暂时隐藏起来。Excel 2010支持按照多个列进行筛选，筛选是累加的，后一次筛选在前一次的筛选结果中进行，从而减少数据的子集。筛选之后得到的数据，可以直接复制、查找、编辑、设置格式、制作图表和打印。两种筛选方式：自动筛选和高级筛选。说明：筛选时标题行出现的下拉三角箭头，在打印的时候会自动隐藏，打印结果与正常报表一样。1.2.1 自动筛选【例1.1】 某公司的人员档案是按照招聘的次序建立的，如果需要分别得到如下信息：已婚人员的名单、各部门的人员档案、工资高于1200的人员名单、奖金高于1000的人员名单、各种学历的人员名单那么需要怎样的数据处理呢？图 1.9 自

9、动筛选前的报表图 1.10 筛选已婚人员名单自定义筛选：当然，并非所有的筛选条件都这样简单，有时需要按照数值的大小进行筛选，有时需要筛选出同时满足两个条件的数据.这些都需要通过自定义筛选条件来实现。如果筛选关键字对应的列是数值型数据，可以通过“数字筛选”命令指定自定义筛选条件，实现灵活的数据查询。【例1.2】 在如图 1.9所示的职工档案表中，筛选年龄最大的三名职工。【例1.3】 在“电脑及耗材销售表”工作表中查询所有姓“张”的销售代表的数据。图 1.11 自定义自动筛选方式在“销售代表”下拉列表的“文本筛选”框内输入“张*”（用*代表任意多个字符），单击“确定”按钮，得到所有姓“张”的销售代

10、表数据，如图 1.12所示。图 1.12 所有“张”姓销售代表数据筛选结果（部分）知识链接：自定义自动筛选方式中的“与”和“或”如图 114所示的对话框中，可以设置两个用“与”或用“或”连接的条件。例如，要查询销售金额在5000元（含5000元）到12000元（含12000元）之间的数据，可以按照图 116所示进行设置即可得到相应筛选结果。图 1.13 图 1.14 使用“与”自定义自动筛选方式1.2.2 高级筛选自动筛选只能完成简单的数据筛选，如果筛选条件比较复杂，就需要采用高级筛选来完成。使用高级筛选可以对某列或者多列应用多个筛选条件，这些条件既可以是与条件，也可以是或条件，或与条件，与或

11、条件的组合使用，还可以使用计算条件。为了使用此功能，在工作表的数据清单上方，需要建立条件区域，为此至少需要留有三个空行，而且数据清单必须有列标。1. 条件区域“条件区域”为包含一组搜索条件的单元格区域，其中含有一个条件标志行，同时至少有一行用来定义搜索条件。条件可以是文本、数值、比较式或者计算公式。在Excel中条件区域的构造规则是： 同一列中的条件表示“或”，即OR（只要其中某一个单元格的条件成立，则整个条件就成立）； 同一行中的条件表示“与”，即AND（只有所有单元格中的条件都成立，整个条件才成立）。在“电脑及耗材销售表”中，我们要在四种情况下对原始数据进行高级筛选，需要创建四个条件区域。

12、 或条件：查看仅包含“宋晓”、“张默”和“刘思琪”三位销售代表的数据； 与条件：查看仅包含【类别】为“畅想系列”、【品名】为“家用电脑”、【销售代表】为“张默”以及【销售金额】为“1000”的数据； 或与条件：显示所有在【类别】列中包含“家用电脑”、或在【销售代表】列中包含“张默”或在【销售金额】列中包含“2000”的数据行； 与或条件：显示所有在【销售代表】列中包含“张默”且【销售金额】2000且【品名】为“大明投影仪”的数据行，同时也显示【销售代表】列中包含“宋晓”且【销售金额】2000且【品名】为“大明投影仪”的数据行。图 1.15 创建条件区域【例1.1】 查询仅包含“宋晓”、“张默”

13、和“刘思琪”三位销售代表的数据。如果要求某一列数据满足两个以上的“或”条件，就需要创建一个在一列上的条件区域，从上到下逐行输入各个条件，这种建立在同一列上的条件是逻辑“或”的关系，相当于OR。具体操作方法如下：步骤二 在数据区域上方插入多个空行，在A3:A6单元格区域中构建如图 1.16所示“1.或条件”条件区域。图 1.16 创建条件区域步骤三 单击数据区域任意单元格，切换至“数据”选项卡，单击“排序和筛选”组中的“高级”按钮，弹出如图 1.18所示“高级筛选”对话框，系统自动给出了列表区域；图 1.17 “高级”筛选按钮图 1.18 高级筛选（或条件）步骤四 单击“条件区域”右侧的设置按钮

14、（也可以用鼠标拾取单元格区域），选定$A$3:$A$6条件区域；步骤五 单击此按钮返回“高级筛选”对话框，最后单击“确定”按钮结束操作，得到只有“宋晓”、“张默”和“刘思琪”三人数据的高级筛选结果。【例1.1】 查看仅包含“类别”为“畅想系列”、“品名”为“家用电脑”、“销售代表”为“张默”以及“销售金额”为“1000”的数据。要查找同时满足不同数据列中某个条件的数据，应该建立一个在同一行中输入所有条件的条件区域（如图 1.16所示“2.与条件”）。输入在同一行中的条件是逻辑“与”的关系，相当于AND。步骤和方法：步骤六 按照C3:F3单元格条件区域进行高级筛选结果如图 1.19所示。图 1.

15、19 高级筛选（与条件）【例1.1】 查找所有在“类别”列中包含“家用电脑”、或在“销售代表”列中包含“张默”或在“销售金额”列中包含“2000”的数据行。要查找满足多列数据条件的数据，应该建立一个将不同列的条件输入在不同行中的条件区域（如图 1.16所示“3.或条件”）。在不同列、不同行中的条件仍然是“或”条件。步骤和方法：按照H3:J6单元格条件区域进行高级筛选，如图 1.20所示。图 1.20 高级筛选（或条件）【例1.2】 查询“销售代表”为“张默”、“销售金额”2000同时“品名”为“大明投影仪”的数据行，同时也显示“销售代表”为“宋晓”、“销售金额”2000同时“品名”为“大明投影

16、仪”的数据行。要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，应在各行中输入条件（如图 1.16“4.与或复合条件”所示）。操作步骤和方法：按照L3:N6单元格条件区域进行高级筛选图 1.22 高级筛选（与或复合条件）2. 使用计算条件进行高级筛选前面三位筛选能够满足大多数需要，但是还有一类筛选它并不能够实现。例如，要找出数据清单中高于平均“销售金额”的数据行（类似的还有找出学生成绩表中高于平均分的学生记录、找出工资表中高于或低于平均收入的职工档案等），因为平均销售金额不是一个已知数据，需要根据数据清单进行计算。但是，假如先计算出平均值，再用计算结果进行筛选，这样当然可以完成

17、工作，但如果数据清单发生了变化（例如，增加记录行、修改某人的销售额等），这个筛选结果就不准确了。实际上，Excel也可以将公式的计算结果作为条件使用，从而进行高级筛选，完成上述数据查找，但必须根据一定的规则创建包含计算条件的条件区域。具体操作方法如下：步骤一 将“电脑及耗材销售表”复制到sheet3中，将其重命名为“电脑及耗材销售表（使用计算条件）”。步骤二 如图 122所示，在数据表的上面构建条件区域。在H3单元格中输入中间值计算公式“=MEDIAN(G6:G215)”；在A3单元格中输入筛选条件“=G6$H$3”，确定之后分别得到图示结果。图 1.23 使用计算条件构建条件区域步骤三 激活

18、数据表区域，执行【数据】|【筛选】|【高级筛选】菜单命令，打开【高级筛选】对话框，切换至【将筛选结果复制到其他位置】；选择图 122中计算结果“FALSE”所在单元格区域“$A$2：$F$3”为条件区域；选择“$K$5”作为放置复制筛选结果区域的左上角单元格。如图 123所示。图 1.24 建立基于计算条件的高级筛选步骤四 单击【确定】按钮，得到如图 123所示的基于计算条件的高级筛选结果。说明：在上述筛选条件中包含一个平均值计算公式，使用计算条件进行高级筛选要遵循以下原则： 计算条件的列标题（A2单元格）可以是任何文本，也可以什么都不输，但不能与数据清单中的任一列标题（A5:I5单元格区域）

19、相同，这一点正好与前面讨论的条件区域相反。 必须以绝对引用的方式引用数据清单之外的单元格（$H$3单元格中的销售金额中间值）。 必须以相对引用的方式引用数据清单之内的数据单元格（G6单元格）。在【高级筛选】对话框中进行操作时，若筛选后要隐藏不符合条件的数据行，并让筛选的结果显示在数据清单中，可切换【在原有区域显示筛选结果】单选按扭。若要将符合条件的数据行复制到工作表的其他位置，则需要切换【将筛选结果复制到其他位置】单选按钮，并通过【复制到】编辑框指定粘贴区域的左上角，从而设置复制位置。在实际工作中，将筛选结果从数据表中独立出来的方式很有用，因为这样可以对同一数据表进行多次不同条件的筛选操作，将

20、每次筛选的结果存放在不同的区域，便于对数据进行分析以及制作不同形式的报表。知识链接：条件区域条件区域一般用于高级筛选或数据库函数中。在一般情况下，应该将条件区域构建在数据清单的开头或者旁边（最好是数据清单的前面），不要再数据清单的下面构建条件区域。并非不允许这样做，其原因是如果数据清单要添加数据行，可能会覆盖条件区域，引起不必要的麻烦。要建立条件区域的数据清单必须要有标题行（如图 121所示的A11:I11单元格区域），否则就构造不了该图中的各条件区域。注意：在进行高级筛选时，可以将某个区域命名为Criteria。此时【条件区域】框中就会自动出现对该区域的引用。也可以将要筛选的数据区域命名为D

21、atabase，并将要粘贴行的区域命名为Extract，这样，Excel就会让这些区域自动出现在【列表区域】和【复制到】框中。1.3 数据的分类与汇总如果一个工作表中数据行太多，就很难从中看清某些信息。要想将相同类型的数据统计出来，就需要对数据进行分类汇总。分类汇总能够对工作表数据按照不同的类别进行汇总统计（求总和、平均值、最大值、最小值、统计个数等）。分类汇总采用分级显示的方式展示工作表数据，它可以折叠或展开工作表的数据行（或列），快速创建各种汇总报告。Excel 2010允许多达8层的分级显示。1.3.1 分类汇总概述1.3.2 建立分类汇总1. 分类汇总的准备工作在使用分类汇总之前，要确

22、定以下两件事情已经做好： 要进行分类汇总的数据表的各列必须有列标题； 必须对要进行分类汇总的数据列排序。这个排序的列标题称为分类汇总关键字，在进行分类汇总时，只能指定已经排序的列标题作为汇总关键字。2. 建立分类汇总【例1.1】 将各类别电脑及耗材的“销售金额”总和分别进行汇总，具体操作方法如下：步骤二 按照关键字“类别”进行排序。步骤三 单击数据区域任意单元格，在“数据”选项卡中】|【分类汇总】菜单命令，弹出如图 1.22所示【分类汇总】对话框。图 1.25 按照“类别”对“销售金额”进行求和分类汇总步骤四 在【分类字段】选项下选择“类别”，【汇总方式】选项下选择“求和”，【选定汇总项】选项

23、下选定“销售金额”，单击【确定】按钮，得到如图 124所示分类汇总结果，从中可以清楚地看到各类别商品的销售金额总和。知识链接：分级按钮在图 124中，左上角有3个按钮（），按钮表示1级汇总，显示全部的销售金额汇总；按钮表示2级汇总，显示各类别的全部销售金额汇总；按钮表示3级汇总，显示各类别的销售金额的汇总明细及汇总额（即图 124所示的汇总结果）。图 124中，左边的滑动按钮为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时变为显示明细按钮，再单击按钮，则将显示本级的全部明细数据，同时变为。1.3.3 高级分类汇总1.3.4 嵌套分类汇总在上述自动分类汇总的结果上，还可以再进行分类汇总，

24、两次分类汇总的关键字可以相同，也可以不同。如果两次关键字不同，需要事先按照第一关键字和第二关键字对数据表重新排序。另外在进行第二次分类汇总时，要取消对话框中【替换当前分类汇总】复选框的勾选，最终可以得到嵌套分类汇总的结果。1.3.5 删除分类汇总如果不再需要分类汇总结果，将数据表恢复到分类汇总之前的状态，可在图 124所示对话框中单击【全部删除】，即可撤消分类汇总。1.4 数据透视表数据透视表是一种快速汇总大量数据的交互式方法，使用数据透视表可以深入分析数值数据，对数据表进行汇总、分析、浏览和提供摘要数据。1.4.1 数据透视表概述具有以下常用功能： 对数值数据进行分类汇总和聚合，按照分类和子分类对数据进行汇总，创建自定义计算和公式； 展开和折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细； 将行移动到列或者将列移动到行（或“透视”），以查看源数据的不同组合与汇总的结果； 对最有用的和最关注的数据子集进行筛选、排序和分组以及有条件地设置格式，突出显示重要信息； 提供简明、带有批注的联机报表或者打印表。