Excel在经济管理中的高级应用（3）筛选、分类汇总及合并计算.ppt

1、Excel在经济管理中的高级应用（3）,数据筛选、排序与分类汇总,数据列表的概念,数据列表是一个矩形表格，表中单元格没有进行过合并。 数据列表的一行数据叫做一条记录 数据列表的一列数据叫做一个字段， 数据列表的每一列可以有一个名字字段名，如果一个数据列表有字段名，则一定是在数据表列的第一行。,一行数据叫做一条记录,一列叫做一个字段,字段名,数据列表表名,数据筛选,数据筛选是对一个数据列表，筛选出满足给定条件的数据行（记录）。对不满足条件的数据行（记录）进行隐藏，使其不显示出来。,只显示单位是“财政局”的职工的信息,数据筛选分为自动筛选和高级筛选2种,数据筛选自动筛选,自动筛选是Excel在数据

2、列表每一个字段的字段名傍边显示一个下拉列表箭头。通过鼠标点击下拉列表按钮拉出一个下拉列表，选择下拉列表中的选项就可设置筛选条件。条件设置完成后，就会自动隐藏不满足条件的记录只显示满足条件的记录。,筛选条件设置下拉列表,数据筛选自动筛选,一、自动筛选的操作步骤： 1、使用菜单命令：“数据”“筛选”“自动筛选” 2、点击字段名傍边的下拉列表箭头，拉出筛选条件下拉表框，设置筛选条件。,二、筛选条件下拉列表及筛选条件的设置 1、筛选条件下拉列表中的选项 升序排列 数据列表中的记录根据本列的值按升序自动排列 降序排列 数据列表中的记录根据本列的值按降序自动排列 全部 去掉本列的筛选条件 前10个 如果本

3、列是数值型字段，则可以显示本列数据值最大 或最小值前10个（或指定个数）对应的记录 (自定义)根据本列的数据， 利用关系运算(大于、小于，等于，不等于，)设置筛选条件。 空白 只显示本列值为空的记录 非空白 只显示本列值为非空的记录,数据筛选自动筛选,筛选基本工资最大的前n条记录,1、鼠标单击“(前10个)”,2、选最大的或最小的前n个,3、选择显示的记录条数,数据筛选自动筛选,筛选工资表中“基本工资”在400元到800元之间的所有记录。 利用“（自定义）”设置筛选条件,1、选 (自定义),2、设置筛选条件,数据筛选自动筛选,按单位和基本工资2个字段设置自动筛选条件： 单位：税务局 基本工资：

4、400元800元 筛选出同时满足2个条件的记录,1、设置“单位”筛选条件为“税务局”,2、设置“基本工资”筛选条件为“400基本工资800,数据筛选高级筛选,在自动筛选中进行数据筛选时，有时不能满足筛选出满足条件的记录。例如，要筛选出工资表中的或者是税务局的职工或者基本工资在800元以上的所有职工的记录，则自动筛选就不能实现。因为，自动筛选对不同的列同时设置筛选条件时，不同列的筛选条件是“逻辑与”的关系，即当在”单位“字段设置筛选条件为”税务局“，而在“基本工资”字段设置筛选条件为“大于800元”时，Excel将认为筛选条件为：单位为税务局且基本工资大于800元的职工记录。这种情况就需要使用E

5、xcel的高级筛选功能。,高级筛选是先设置一个数据筛选条件区域，然后根据筛选条件区域中设置的筛选条件筛选出数据列表中满足条件的记录。,数据筛选高级筛选,高级筛选条件区域的格式：,筛选条件区域是一个矩形区域（2行或2行以上），条件区域的第1行是字段名，说明要对根据哪个字段设置筛选条件，第2行之后的各行说明具体的筛选条件。 条件区域中某1列的第1行与该列的第2行构成一个筛选条件，第1行与该列的第3行构成第二个筛选条件，。 同一列中的多个筛选条件是“逻辑或”的关系；不同列中的筛选条件是“逻辑与”的关系。,数据筛选高级筛选,高级筛选条件区域的格式,高级筛选中的关系运算符,数据筛选高级筛选,例根据工资表

6、，设置高级筛选中的筛选条件区域,1、筛选出姓名为“张车车”的职工的记录,2、基本工资大于500元的所有职工的记录,3、基本工资在500至800元之间的所有职工记录,4、基本工资不在500至800元之间的所有职工的记录,或：,5、税务局基本工资在500至800元之间的所有职工,数据筛选高级筛选,例根据工资表，设置高级筛选中的筛选条件区域,数据筛选高级筛选,高级筛选的操作步骤：,1、建立数据列表（例如工资表）,2、根据数据筛选要求，建立数据筛选条件区域（在数据列表的上下左右均可）,3、使用菜单命令“数据”“筛选”“高级筛选”,4、在高级筛选对话框中，输入数据列表的区域引用和数据筛选条件区域的引用,

7、数据筛选高级筛选例题,1、建立数据列表（例如工资表）,例对工资表进行筛选,数据筛选高级筛选例题,2、根据数据筛选要求，建立数据筛选条件区域（在数据列表的上下左右均可）,数据筛选高级筛选例题,3、使用菜单命令“数据”“筛选”“高级筛选”,数据筛选高级筛选例题,4、在高级筛选对话框中，输入数据列表的区域引用和数据筛选条件区域的引用,1、鼠标单击此处,2、找到数据列表的左上角单元格，然后鼠标单元,数据筛选高级筛选例题,4、在高级筛选对话框中，输入数据列表的区域引用和数据筛选条件区域的引用,1、拖动垂直滑块和水平滑块，找到数据列表的右下角,1、拖动垂直滑块和水平滑块，找到数据列表的右下角,2、先按住键

8、盘的shift键不放，然后用鼠标单击数据列表的右下角单元格,3、数据列表的区域引用自动出现在此处,数据筛选高级筛选例题,4、在高级筛选对话框中，输入数据列表的区域引用和数据筛选条件区域的引用,1、鼠标点击“条件区域”,2、拖到滑块找到条件区域在工作表上的位置,2、拖到滑块找到条件区域在工作表上的位置,3、用鼠标拖动的方法选择条件区域,4、鼠标单击“确定”,数据筛选高级筛选例题,筛选结果,数据筛选高级筛选例题,5、恢复原始数据列表 使用菜单命令：“数据”“筛选”“全部显示”,数据筛选高级筛选例题2,例筛选出成绩表中，有不及格科目的所有学生的记录,数据筛选高级筛选例题2,例筛选出成绩表中，有不及格

9、科目的所有学生的记录 因为只要有一科的成绩不及格，就是满足条件的记录，也就是说，只要“网络”课成绩不及格，或“法基”课成绩不及格，。所以各个条件是“逻辑或”的关系。根据前面所述，在条件为逻辑与的情况下，各个条件不能在同一行，所以条件区域的设置如下：,数据筛选高级筛选例题2,筛选结果,数据筛选高级筛选例题2,例筛选出成绩表中各科成绩均及格的所有学生的记录,数据筛选高级筛选例题2,例筛选出成绩表中各科成绩均及格的所有学生的记录 因为要求每一科的成绩都要及格，也就是说，只要“网络”课成绩及格，并且“法基”课成绩也要及格，。所以各个条件是“逻辑与”的关系。根据前面所述，条件为“逻辑与”时，条件应在同一

10、行，所以条件区域的设置如下：,数据筛选高级筛选例题2,筛选结果,排序,排序是根据数据列表中的某一个列（叫关键字）或几列的值，重新排列数据列表中各行（记录）的顺序。,数据列表的排序可以根据一个关键字进行排序，也可根据2个或3个关键字进行排序。 如果根据2个以上的关键字进行排序，则Excel将先按第1个关键字对数据列表中的记录重新排序，在此基础上，再对第1个关键字值相同的记录按第2个关键字进行排序，以此类推。 例如，在工资表中，可以先按单位（主要关键字）对所有的记录进行排序，使同一个单位的职工记录排在相邻的连续区域中；然后可以再按基本工资（次要关键字）的高低使同一个单位的职工记录再按基本工资的高低

11、有序排列。,排序的操作步骤：,1、选择要进行排序的数据列表,2、使用菜单命令： “数据”“排序”,3、设置排序的主要关键字、次要关键字、第三关键字等，以及每一个排序关键字是升序排列或降序排列,排序例题,例将工资表中同一个单位的职工的记录排在一起,排序例题,操作步骤： 1、使用菜单命令“数据”“排序”,排序例题,2、设置主关键字为“单位”,1、选择主关键字,2、说明按升序排列,排序例题,例将同一单位的职工在工资表中排在一起，且同一单位的职工按基本工资从高到低的顺序排列。 主要关键字为“单位”，“升序” 次要关键字为“基本工资”，“降序”,分类汇总,分类汇总是根据数据列表中的某个关键字段对数据行（

12、记录）进行分类，关键字段值相同的记录为同一类记录。分类汇总就是对数据列表中各类记录的数据进行求和、求平均值、求最大值、最小值等。所以说是分类汇总，实际可以分类进行多种计算。,Excel的“分类汇总”功能，可以通过与用户对话选择的方式对数据进行汇总、自动建立分级显示，并在数据列表中插入汇总行和分类汇总行。在进行分类汇总时，Excel会自动在数据列表底部插入一个总计行。,要进行分类汇总的数据列表，在汇总之前必须按要分类的关键字段对数据行（记录）进行排序，然后才能运用分类汇总命令对数据列表中的数据进行汇总，否则汇总结果将不正确。,分类汇总,分类汇总的操作步骤：,二、对数据列表中的数据进行分类汇总,1

13、、选择要进行分类汇总的数据列表中的所有数据,2、使用菜单命令：“数据”“分类汇总”,3、在“分类汇总”对话中进行汇总参数选择： “分类字段”：说明按哪一个字段进行分类汇总 “汇总方式”：有如下几种汇总方式： 求和按关键字值分类计算选定数值字段的和及该字段的总和。 求平均值按关键字值分类计算选定数值字段的平均值和及该字段的总平均值 计数按关键字值分类计算选定字段的记录数据和该字段的总记录数据 求最大值按关键字值分类计算选定数值字段的最大值和及该字段的总最大值 求最小值按关键字值分类计算选定数值字段的最小值和及该字段的总最小值 “选定汇总项”选择需要进行汇总计算的字段（可以选择可多字段） （在字段

14、名前有者，说明要对该字段按选定的“汇总方式”进行汇总）,一、对数据列表中的数据按需要分类汇总的关键字进行排序 如要统计各单位的平均工资，则“单位”就是关键字。,分类汇总,例根据工资表，汇总计算各单位的工资总和及全县的工资总和,操作步骤： 1、使用“排序”命令，使原始数据表按记录按单位重新排序 2、使用“分类汇总”命令 3、在分类汇总对话框中，选择分类字段为“单位”，汇总方式为“求和”，汇总项选择“应发工资”和“实发工资”,分类汇总,一、以“单位”为关键字进行排序,分类汇总,二、使用菜单命令“数据”“分类汇总”,分类汇总,三、选择“分类汇总”命令的参数,1、使用菜单命令：“数据”“命令”,2、选

15、择“分类字段”,3、选择“汇总方式”,分类汇总,三、选择“分类汇总”命令的参数,4、选择“分类汇总项”,分类汇总,按“单位”汇总各单位的“应发工资”和“实发工资”后的结果,自动插入单位汇总行 （2级数据）,自动插入“总计”行 （1级数据）,选择了2个汇总字段 就有2个汇总数据,原始数据为3级数据,说明显示所有3个级别的数据,分类汇总,只显示1级和2级汇总数据,鼠标单击“2”，说明只显示1级和2级汇总数据,分类汇总,例求各单位的平均工资和各单位书报费相同的职工的平均工资。 本例题要2次使用“分类汇总”命令才以能完成，其具体步骤为： 1、将原始数据表按主要关键字为”单位“，次要关键字为”书报费“进

16、行排序 2、使用”分类汇总”命令，以”单位“为分类字段，求各单位的平均工资，得到各单位的平均工资汇总表 3、在得到的各单位平均工资汇总表的基础上，再次使用”分类汇总“命令，但在“分类汇总”命令对话框中，要使“替换当前分类汇总”选项无效,分类汇总可以嵌套，数据列表按一个主关键字分类汇总后，还可再按次要关键字进行汇总。具体操作为： 1、按主关键进行分类汇总 2、按次要关键字进行分类汇总（此时必须将“分类汇总”对话框中“替换当前分类汇总”条目前的取消掉）,分类汇总,一、以“单位”为主要关键字，以“书报费”为次要关键字，对原始数据表进行排序,分类汇总,二、以“单位”为分类字段，求“实发工资”的平均值,

17、分类汇总,三、在第一次分类汇总的基础上，以“书报费”为分类字段，求“实发工资”的平均值,“替换当前分类汇总”的勾必须去掉,分类汇总,四、二次汇总后的结果,分类汇总,去掉分类汇总数据，恢复分类前的数据列表 对于已进行分类汇总的数据列表，如果想去掉分类汇总数据，恢复分类前的数据列表，同样是使用分类汇总命令，但这时需要选择分类汇总命令对话框中的“全部删除”命令选项。,2、选择“全部删除”恢复分类汇总前的数据列表,1、选择“数据”“分类汇总”命令,数据列表的合并计算,合并计算是指对多个数据列表中的数据，根据指定的关键字，对关键字值相同的记录的数据值字段进行汇总计算得到多个数据表列的汇总数据从面形成一个

18、汇总数据表的方法。,Excel在经济管理中的高级应用（4）,合并计算,合并计算,合并计算是指对多个数据列表中的数据，根据指定的关键字，对关键字值相同的记录的数据值字段进行汇总计算得到多个数据表列的汇总数据从面形成一个汇总数据表的方法。,根据数据列表中记录排列的方式不同，报表的合并计算分为2种：按行可并计算和按列合并计算。 按行合并计算是根据数据列表中最左列的值，将各数据列表中最左列值相同的记录合并计算后得到合并报表中一行数据； 按列合并计算是根据数据列表第1行（首行）的值，将各数据列表中第1行的值相同的列合并计算为合并报表中的一列数据。,合并计算,“合并计算”命令主要通过对话框来输入有关的计算

19、参数： 1、函数 说明通过哪种计算（求和、求平均值、）对原始报表进行合并计算后得到合并报表。 2、引用位置 要用到的原始报表的引用位置，通过“添加”按钮添加到“所有引用位置”中 3、所有引用位置 一个引用位置就是一张原始报表，说明要用哪些报表进行合并计算 4、标签位置 说明合并报表的第1行（首行）和最左列是否出现标签（即列的名称和行的名称） 5、创建连至源数据的链接 先将各报表的按类数据逐条引用到合并报表的报表中，再进行合并计算，得到的合并报表中的数据将随原始报表中的数据的变化而自动重新计算。,合并计算的种类,合并计算,例1朋友图书公司有甲、乙、丙三个门市部，每个门市部都有自己的销售报表，现需

20、要汇总三个门市部的报表，得到本公司的销售汇总表,按行合并计算根据数据列表最左列的数据值，将各原始数据列表中最左列有相同数据值的记录合并计算到，得到合并报表中的一行的数据,合并计算,合并计算,例2朋友图书公司有甲、乙、丙三个门市部，每个门市部都有自己的销售报表，现需要汇总三个门市部的报表，得到本公司的销售汇总表,按列合并计算根据数据列表第1行（首行）的值，将各数据列表中第1行的值相同的列合并计算为合并报表中的一列数据。,合并计算,1、把3张原始报表的同类数据逐条引用表汇总表中,2、通过求和公式计算合并结果,3、合并计算的结果,例3创建连至源数据链接的合并报表,合并计算的步骤,合并计算的步骤： 1、建立原始数据表（可以在同一张