《EXCEL数据处理》-第二部分 管理数据.ppt

第4章,数据的排序与筛选,2,本章目录,1.数据的排序2.数据排序操作的应用技巧3.数据的筛选小结,3,1.数据的排序,1.1排序的规则排序有升序或降序两种方式。升序就是从小到大的顺序排列，降序反之。排序时，必须首先确定排序的依据。以升序排序为例，Excel使用如下排序规则：（1）数字按从最小的负数到最大的正数排序；（2）字母按照英文字母AZ和az的先后顺序排序。（3）在对文本进行排序时，Excel从左到右一个字符一个字符地进行排序比较。（4）特殊符号以及包含数字的文本，升序按如下排列：09（空格）!#$%?_|+AZaz（5）在逻辑值中，FALSE（相当于0）排在TRUE（相当于1）之前；（6）所有错误值的优先级等效；（7）空格总是排在最后；（8）汉字的排序可以按笔画，也可按汉语拼音的字典顺序。,4,1.2简单排序：对单一条件排序,按单一条件（单个关键字）排序就是根据数据表中某一列的内容进行排序，包括“升序”和“降序”两种方式。其功能实现的最好方法就是采用工具按钮法，操作时，只要将光标置于待排序的列中；然后单击常用工具栏上“升序”按钮或“降序”按钮即可。【数据】-【排序和筛选】-【排序】-【主要关键字】-【排序依据】-【数值】等说明：按某一列数据作为关键字排序时，只需单击该列中任一单元格，而不用全选该列数据。这样排序之后，主要关键字所在的列数据是按照顺序排列的，每条记录的内容全部都按照关键字的顺序而调整；而当操作者全选该列数据时，本列顺序虽然可以按照要求进行了排列，但其余各列的内容会保持不动，这将造成“张冠李戴”的问题出现。,5,【数据】-【排序和筛选】-【排序】-【主要关键字】-【排序依据】-【数值】（员工工资表1）,6,1.3复杂排序：多个条件进行排序（员工工资表2）,【数据】-【排序和筛选】-【排序】-【主要关键字】-【排序依据】-【数值】-【次序】【添加条件】-【次要关键字】,7,1.4自定义次序排序（员工工资表3）,在管理实践中，有时数据处理需要进行的排序工作可能要求非常特殊，既不是按照数值的大小排序，也不是按照汉字的字母顺序或笔画顺序排序，而是按一种特殊次序进行排列。这种例子并不少见，如常见的月份排序、星期排序、季度排序、日期排序，以及某些约定俗成的排序方式（如国务院下属各部委的约定排名，总是国防部、外交部在最前）。自定义次序排序是通过设置“自定义序列”进行的。,8,9,10,【数据】-【排序】-【次序】-【自定义排序】,自定义排序：根据单元格颜色、字体颜色、单元格图标等进行排序,11,1.3其它,数据排序操作时，选择“数据”/“排序”命令，然后在随之出现的对话框中进行选项设计即可。区分大小写按列排序、按行排序字母排序、笔画排序（默认为按照字母顺序）,12,2.数据排序操作的应用技巧,2.1实现排序后仍能快速返回原来的顺序数据清单排序后，原来的数据顺序将被打乱，而有时候还想让它能够恢复到原来的顺序。对于该问题，如果是以下两种情况，还比较好处理：第一：操作刚刚完成，直接按下【Ctrl+Z】撤消操作即可；第二：如果原来的表格本身已经有了一个排序索引字段（如学号、职工编号，日期型数），只要按照原来那个排序索引字段再重新排序一次即可。但是，如果不是上述两种情况，则需要采用引入辅助列的方法通过以下步骤来实现。,13,2.2排序函数应用:RANK、LARGE和SMALL(),1RANK函数及其应用RANK函数用来返回一个数值在一组数值中的排位，其语法格式为：RANK(number,ref,order)该函数共包括三个参数，其中：Rank(A1,A1:A10,0)Number为需要找到排位的数字；Ref为包含一组数字的数组或引用；Order为一数字，指明排位的方式，为0或省略，按降序排列排位，不为零，按升序排列进行排位。2LARGE和SMALL函数及其应用LARGE函数和SMALL函数的功能分别是用来返回一个数据集中第k个最大值和第k个最小值，它们语法格式分别为：LARGE(array,k)和SMALL(array,k)。,14,2.3隐藏部分数据使其不参与排序,默认情况下排序都是针对数据区域中所有数据，但在实际数据处理中，有时不想让一些数据参与排序-先将不需要排序的数据行隐藏。数据隐藏是针对整行（或列）的，所以不要只选取包含有内容的数据区域。说明：选取包括所有隐藏行的整个区域，然后单击右键菜单中的“取消隐藏”，则隐藏的所有行将一下次全部显示。,15,2.4用数据排序法删除数据区域内的空行,有时候，处于数据分区显示或者错误操作等原因，数据区域中可能包含很多空行。这些空行的存在，会影响到对数据的分析。对这些空行如果一行一行地操作，非常费时。利用“数据排序时，空格总是排在最后”这一排序规则，则可以通过数据排序的方法删除数据区域中的所有空行，并且不打乱数据区域中各行数据的顺序。,16,3.数据的筛选,需根据指定条件从众多数据中筛选特定记录。Excel三种筛选方法：可将符合条件的记录显示，而将不满足条件的记录隐藏起来；或者将筛选出来的记录送到指定位置存放，而原数据表不动。自动筛选：在工作表中只显示满足给定条件的数据，分简单的条件筛选和对指定数据的筛选。条件筛选：根据系统指定的条件对工作表中的数据进行筛选自定义筛选高级筛选,17,3.1自动筛选的操作（员工工资表4）,执行“数据-筛选-数字筛选自定义自动筛选”命令。选择显示所有数据，则取消筛选退出筛选：再次点击“数据筛选”,18,3.2高级筛选的操作（员工工资表4）,自动筛选非常方便，可以实现以下的操作：对某一字段筛选出符合某特定值的记录单击需要筛选字段的筛选器箭头，从下拉菜单中直接选择某特定值即可。对同一字段进行“与”运算和“或”运算单击需要筛选字段的筛选器箭头，从下拉菜单中选择“自定义”，在弹出对话框中进行设置即可。对不同字段之间进行“与”运算只要通过多次进行自动筛选即可。可以筛选出最大最小的若干个若干百分比）记录只要单击需要筛选字段的筛选器箭头，从下拉菜单中选择“前10个”即可。但是，自动筛选无法实现多个字段之间的“或”运算，这时就需要使用高级筛选要进行高级筛选，必须首先设置筛选条件区域。,19,条件区域的设置实例及其图形化解释,20,返回高级筛选-确定可以多设几个条件选择数据清除，即可清除筛选,21,Excel小技巧,输入当前日期【ctrl】+【；】-不随实际日期发生变化=today()-会根据实际日期发生变化输入当前时间【ctrl】+【shift】+【；】-不随实际日期发生变化=now()-会根据实际日期发生变化取消重复记录数据-删除重复项高级筛选-数据-排序和筛选选择不重复的记录巧用记录单查记录,22,文件选项快速访问工具栏不在功能区中的命令记录单快速工具栏条件,23,小结,本章介绍数据排序和筛选操作。熟练使用这些操作，可以方便地对Excel数据表中的数据进行重新组织和管理。Excel可以进行单关键字排序，多关键字排序;甚至通过进行多次多关键字排序,还可以实现排序的关键字段突破“排序”对话框给出的仅有三个条件的设置；另外，通过在“排序选项”对话框中进行选择，可以实现对汉字按照笔画排序，通过设置“自定义序列”可以实现按照自己指定的特殊次序排序。数据筛选可以从一个工作表中过滤出需要的数据，筛选分为高级筛选和自动筛选。通过本章学习，读者应能熟练地掌握数据排序和筛选操作，特别要熟悉如何对中文姓名按照姓氏笔画排序，如何对超过3个关键字段的数据排序，如何进行高级筛选。,第5章,数据的汇总与透视,25,本章目录,1.利用相关函数进行数据汇总2.数据库表格的分类汇总3.特定情形下的数据汇总方法4.数据的透视分析小结,26,1.利用相关函数进行数据汇总(了解),数据汇总是最频繁的操作之一SUM函数满足一定条件的数据汇总方式SUMIF函数SUM函数组合数组SUMPRODUCT函数DSUM函数SUM函数与IF函数组合应用它们各有相应的应用场合，读者应灵活应用。,27,1.1利用SUMIF函数实现单条件汇总,SUMIF函数的功能是根据指定条件对若干单元格求和，其语法格式如下：SUMIF（条件判断区域，条件，求和区域）例题员工工资表5注意：条件用半角英文状态的引号,28,1.2SUM组合函数组,要实现对两个以上的条件求和，可使用SUM函数组合数组(员工工资表5)或联合SUM函数和IF函数来实现(课后练习)。最后按ctrl+shift+enter,29,2数据库表格的分类汇总,2.1分类汇总的基本知识分类汇总是在Excel数据库表格或者数据清单中快捷地汇总数据的方法，通过分级显示和分类汇总，可以从大量的数据信息中按照某些特殊的需要提出有用的信息。总之，分类汇总为汇总数据提供了非常灵活有用的方式，它主要可以实现以下功能：（1）在数据库表格上显示一组数据的分类汇总及全体总和。（2）在数据库表格上显示多组数据的分类汇总及全体总和。（3）在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）、求总体方差等。,30,2.2分类汇总的建立,在执行分类汇总命令之前，首先应该对数据库进行排序，将数据库中关键字相同的一些记录集中到一起。当对数据库排序之后，就可以对数据库进行数据分类汇总。运行“数据”“分类汇总”命令，出现“分类汇总”对话框，然后进行分类汇总的设置操作。,31,全部删除，即可清除分类汇总,32,2.3多重分类汇总的操作(了解),上面介绍的分类汇总仅仅只是按照“品名”关键字对“合计金额”进行了一种汇总运算。实际上。在同一分类汇总级上可能不只进行一种汇总运算。为此，Excel还提供了对同一分类进行多重汇总的功能，这就是所谓的多重分类汇总。若要在同一汇总表中显示两个以上的汇总数据时，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行即可。说明：每增加一次分类汇总，汇总结果左侧按钮就会增加一层。,33,2.4嵌套分类汇总的操作(了解),嵌套分类汇总，是在一个已经按照某一个关键字建立好分类汇总的汇总表中，再按照另一个关键字进行另一种分类汇总，这里要求的是两次的分类汇总关键字不同。建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余的以此类推。在进行嵌套分类汇总时，有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余的以此类推。说明：嵌套分类汇总与多重分类汇总的相同点在于二者都需要进行多次的分类汇总操作；区别在于后者每次的汇总关键字都相同，而前者每次的分类汇总关键字却不一样。,34,3.特定情形下的数据汇总方法,3.1数据累加汇总处理将一列数据从上往下（或者自左向右）进行累加汇总，是数据管理中一种常见的操作。该问题解决可以使用SUM函数，但是在单元格区域的引用中第一个单元格应为绝对引用，而第二个单元格地址应为相对引用。练习：病人信息表D2=SUM($B$2:B2)练习计算累积死亡病例,35,3.2不连续区域的数据汇总实验耗材等采购清单2,36,3.3对含有错误值的单元格区域进行汇总（实验耗材等采购清单3）,当利用汇总函数对进行数据汇总时，如果单元格中存在错误值，则函数或者公式的结果将会出现错误。对于这类问题，需要使用错误判断函数IFERROR进行判断，并利用数组公式生成一个不含有错误值的数组，然后才能得到正确的结果。切记：输入完公式按【shift】+【ctrl】+enter,37,4数据的透视分析,数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在Excel中的实现工具是数据透视表（图）。数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，Excel中还可以根据数据透视表制作不同格式的数据透视报告。,38,4.1数据透视表的创建,选择【插入】-【数据透视表和数据透视图】,39,4.2创建数据透视表时的相关操作,(1)“布局”对话框的使用,40,4.2创建数据透视表时的相关操作,(2)汇总方式的修改,41,4.2创建数据透视表时的相关操作,(3)使用透视表筛选数据实验耗材采购清单3,42,4.3创建数据透视图,创建数据透视图，关键是第一步要选“数据透视图”；如果数据透视表已制作好，要制作与之对应的数据透视图，在数据透视表上单击右键，从快捷菜单上单击“数据透视图”即可。,43,4.4编辑数据透视表,对于制作好的数据透视表，有时还需要进行编辑操作。编辑数据透视表包括对其布局的修改、格式化表中的数据、透视表的复制等。创建数据透视表后，Excel自动打开如下图所示的“数据透视表”工具栏以及“数据透视表字段列表”对话框，可通过使用“数据透视表”工具栏，并借助于“数据透视表字段列表”对话框，对数据透视表进行修改。,44,4.5修改数据透视表的布局,(1)行、列字段互换。(2)在字段内移动数据项(3)隐藏和显示数据(4)添加字段(5)删除字段(6)设置数据透视表选项,45,4.6新数据透视表中数据显示形式的修改,在数据透视表中，数据的默认显示形式是以数值形式显示，但这不是固定不变的，可以修改数据透视表中数据的显示形式，如显示为小数、百分数或其他需要的形式。单击右键-数字格式若行标签为日期格式的数据时，可以选择透视表中的日期，然后单击右键，选择组合，按月、季度、年进行数据统计分析。,46,4.7数据