EXCEL培训数据汇总及图表展示.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,EXCEL培训数据汇总及图表展示,EXCEL培训数据汇总及图表展示EXCEL培训数据汇总及图表展示第一讲数据的组织与整理,第一讲,数据的组织与整理,EXCEL,数据库表格的样式与特点,数据库是一个数据区域,同一字段的取值类型一样,数据库中的每一行称为一个记录,在多数情况下，数据库中还应有一个条件区域,EXCEL数据库表格的遵循准那么,一个数据库表格要单独占一个工作表，防止多个数据库表格放在一个工作表上,数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开,防止在数据库中间放置空白行或空白列，任意两行的内容不能完全一样,重要的固定数据(如编号、姓名)设置为左侧字段，移动滚动条时可将其锁定,字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别,条件区域不要放在数据库的数据区域下方,将数据库表格转换为,EXCEL,表,为了更加容易地管理和分析一组相关数据，可以将Excel数据库表格单元格区域转换为 Excel表(这里的Excel表是一种特指，它是一系列包含相关数据的行和列。在Excel 2007以前的版本，将其称之为“Excel列表)。Excel表在数据分析中具有非常重要的用途。,数据库表格转化为,EXCEL,表的格式,具体操作步骤如下：,将活动单元格放到中数据库表格的任意一个单元格中,单击“插入“表“表命令按钮，弹出“创立表对话框,在“创立表对话框中，系统自动选取单元格所在的连续区域为表数据的来源,单击“确定按钮，数据库表格区域变成了Excel表的格式,EXCEL表与数据库表格区域比较，提供了很多附加功能：,在默认情况下，表中的每一列都在标题行中启用了筛选功能，以便可以快速筛选表数据或对其进展排序,在表格最后行一行的下面，增加了一个汇总行，方便数据汇总,Excel表的各个数据行，设计成了不同颜色底纹的间隔效果，便于查看行数据,使用表的右下角的大小调整控点，可以拖动表，使其大小满足需要,当在Excel表上单击鼠标左键时，系统还会适时弹出“表工具选项卡，该选项卡下面提供了很多可以用来对Excel表进展相关设置的命令按钮,导入文本格式的文件,问题分析：要将文本格式的固定资产数据表文件导入到Excel表中，只需先启动Excel软件，然后执行“翻开文件的操作，设置翻开文件的类型为“文本文件格式，然后从文本格式的固定资产数据表文件的所在位置找到翻开即可。,具体操作步骤如下：,启动Excel2007软件,单击“Office按钮一“翻开命令按钮，弹出“翻开对话框,从“查找范围下拉列表框找到需要翻开文本文件所在的文件夹，从“文件类型下拉列表中选择“文本文件，从中可以看到需要翻开的“固定资产数据表文件，然后用鼠标单击该文本文件的图标,启动了“文本导入向导，该向导共包括三个步骤,导入文本格式的文件,步骤,1,固定资产数据文本文件导入举例,导入文本格式的文件,步骤,2,导入文本格式的文件,步骤,3,数据的排序,Excel中数据排序的根本规那么,排序有升序或降序两种方式。以按升序排序为例，Excel使用如下排序规那么：,数字按从最小的负数到最大的正数排序。,字母按照英文字母Az和az的先后顺序排序。,在对文本进展排序时，Excel从左到右一个字符一个字符地进展排序比较，假设两个文本的第一个字符一样，那么比较第二个字符，假设第二个也一样，那么比较第三个一旦比较出大小，那么不再比较后面的字符。,特殊符号以及包含数字的文本，升序按如下排列：,09 !#$%?_|+AZ az,在逻辑值中，FALSE(相当于0)排在TRUE(相当于1)之前。,所有错误值的优先级等效；空格总是排在最后。,汉字的排序可以按笔画，也可按汉语拼音的字典顺序。,使用工具按钮对数字单关键字,排,序,按单个关键字排序就是根据数据表中某一列内容进展排序，包括“升序和“降序两种方式。实现此功能的最好方法就是采用工具按钮法。,操作时，只要将光标置于待排序的列中，然后运行,“数据“排序和筛选“升序按钮或“降序按钮即可。,利用菜单法对汉字字段按笔画排序,Excel中对数据库中排序的第二种方法就是“菜单法，操作时，只要运行“数据“排序和筛选“排序按钮，然后在随之出现的对话框中进展相应选项设计即可。,汉字的排序方式有“字母序和“笔画序两种。“字母序是按汉字拼音排序；“笔画序是按照笔画的多少排序，在升序方式中，笔画少的在前面，笔画多的在后面。,实现具有多个关键字的复杂排序,海达公司某次招聘工作进入总经理筛选阶段的15名应聘人员的成绩表，现在需要对这13人进展成绩排序，相关的规那么是：,先按“总评成绩排名一样者再按“面试成绩排名,再按“笔试合计排名,以上排名都是按由大到小排列,如果按照上述成绩最终仍然无法区分，那么作为并列名次处理，但是二人在名单上的出现顺序，需要按照汉字拼音顺序的升序排列,问题分析：本问题包含四个排序关键字段。要到达以上目的，需进展多关键字排序,操作步骤如下：,选择整个数据表所在单元格区域，然后运行“数据“排序和筛选“排序按钮命令。在弹出的“排序对话框中：“主要关键字选择“总评成绩，“排序依据用默认的“数值，“次序从下拉列表中选择“降序,单击“添加条件按钮，设置次要关键字：“次要关键字选“面试成绩，“排序依据用默认“数值，“次序从下拉列表中选择“降序。,再依次单击“添加条件按钮，设置其余次要关键字，最终效果,单击“确定按钮后，最后得到按照指定关键字排序的结果,实例见数据排序,排序函数,RANK,的应用,在,Excel,中，还有一些与排序相关的函数。其中最为常用的就是,RANK,函数,RANK,函数的功能是用来返回一个数值在一组数值中的排位，其语法格式为：,RANK(number,ref,，,order),该函数共包括三个参数，其中：,number,为需要找到排位的数字,ref,为包含一组数字的数组或引用,order,为一数字，指明排位方式,(0,或省略，按降序排位，不为,0,，按升序排位,),。,见排位实例,数据的筛选,根据需要，有时要按指定条件从数据库中筛选特定记录。,Excel,提供三种筛选方法：自动筛选、自定义筛选和高级筛选。,自动筛选的操作实例,利用自动筛选，从考试报名人员信息表中，筛选出具有博士究生学历，并且是男性的报名人员根本信息。,问题分析：本问题属于较简单的条件筛选问题，使用自动筛选操作即可。它能将那些合条件的记录显示在工作表中，而将不满足条件的记录暂时隐藏。,自动筛选的操作实例,操作步骤如下：,将光标定位到需要筛选数据表中的任意一个单元格中。,运行“数据“排序和筛选“筛选命令按钮，此时在数据表的每个字段右侧，均出现了筛选箭头，单击需要设置筛选条件字段的筛选箭头，会弹出一个下拉列表,其中可以用来设置条件格式，设置了性别字段为“男的条件。,单击“确定按钮，性别为“男的数据记录被筛选出来。在所筛选出来数据的,再对“学历字段设置条件“博士,单击“确定按钮，题目中要求的报名人员中“男博士的根本信息被筛选出来,自定义筛选的操作实例,利用自定义筛选，从考试报名人员信息表中，筛选出30岁以下，最近两年研究生学历(包括硕士、博士)毕业的人员信息。,问题分析：本问题包括年龄、毕业时间、学历共三个筛选条件，它们分别与表格中的出生日期、毕业年份和学历字段有一定关系，相互之间为“与的关系。可以按照先后顺序执行三次不同的自定义筛选，下面就采用自定义筛选的方法实现。,自定义筛选的操作实例,具体操作步骤如下：,按照先为数据表进展自动筛选。,单击“出生日期字段的筛选箭头，从弹出的下拉列表中，依次单击“日期筛选“之后菜单命令,在弹出的对话框中，在相应文本框中输入日期“1979-1-1,单击“确定按钮，30岁以下的报名人员被筛选出来,在以上筛选结果之上，再用自定义筛选的方法依次筛选其他两个条件，设置的两个“自定义自动筛选方式对话框，“最近两年毕业的自定义筛选条件，“研究生的自定义筛选条件,以上所有条件设置完毕后，最后满足的考生被筛选出来,自动筛选与自定义筛选功能总结,从前面的几个例子可以看到，自动筛选和自定义筛选可以实现以下的筛选操作：,对某一个字段筛选出符合特定值的记录此时只要单击需要筛选字段的筛选箭头，然后从下拉菜单中直接单击选中特定值即可。,对同一个字段进展逻辑“与和逻辑“或运算此时只要单击需要筛选字段的筛选箭头，然后从下拉菜单中选择不同的筛选条件，或者“自定义筛选命令菜单，在弹出的“自定义自动筛选方式对话框中进展设置即可。,在不同字段间进展逻辑“与运算进展屡次自动筛选或者自定义筛选即可。,自动筛选与自定义筛选功能总结,对于数值型字段，可以筛选出最大(小)的假设干个(假设干百分比)记录要单击需要筛选数值字段的筛选箭头，从下拉菜单中单击“数字筛选命令菜单，然后再从出现的子菜单中选择“10个最大的值，最后在弹出的“自动筛选前10个对话框中进展设置即可。,高级筛选操作实例,自动筛选无法实现多个字段间的“或运算，并且它将筛选出来的记录，仍然显示在原来的表格中，而不能使其显示到一个新的地方。要解决上述问题，就需要高级筛选。,如果要进展高级筛选，首先必须在该数据库表格上方设置筛选条件区域。,见数据筛选,高级筛选操作实例,在公务员考试报名人员信息表中，该市地税局会计处的一位领导对报考人员中女博士的情况，以及那些所学专业为“会计，或者报考职位为“会计的人员比较感兴趣，现在需要帮她筛选出满足上述条件的人员记录。,问题分析：该问题包括4个条件，牵涉4个字段，并且这4个条件之间并不全是逻辑“与的关系。因此，无法使用自动筛选，而只能依靠高级筛选来完成。,高级筛选操作实例,操作步骤如下：,在“人员报名信息表表格的上方插入几个空行，根据本例筛选的实际需要逻辑关系，建立高级筛选的条件区域A1：D4区域,单击“报名人员信息表数据库表格中的任一单元格,单击“数据“排序与筛选 高级按钮，弹出“高级筛选对话框，需要进展“高级筛选对话框的设置：,在“方式选项区中，单击选中“将筛选结果复制到其他位置：,在“列表区域框中，系统自动选取了要筛选数据所在区域，这是因为步骤(2)将光标置于数据库表格中,在“条件区域框中，输入设置好的包含筛选条件的区域“$A$1：$D$4。,在“复制到文本框中输入时，首先将光标在其中定位，然后用鼠标在数据表下方指定一个单元格，该单元格将作为放置筛选结果区域的左上角位置，筛选结果将在它的下方和右方排列。例如：选取了A41单元格作为筛选结果的左上角,单击“确定按钮，高级筛选结果出现,第二讲,统计数据的透视分析,数据透视表,所谓对统计数据进展透视分析，就是从统计数据库表格的特定字段中概括信息，从而可以方便地从多个角度来查看、分析、汇总、统计数据，并帮助统计人员快速做出决策。,在Excel中，实现数据透视分析的工具是数据透视表和数据透视图。其中，数据透视表用来对大量的统计数据进展快速汇总和建立穿插列表，本讲概括性地介绍数据透视表的根本操作。,统计数据透视分析的作用,下面首先通过一个实例，来说明对统计数据进展透视分析的作用与处理结果,顺达电脑公司2021年1月份的散件销售记录表，现在到了月底，需要对这些数据汇总整理和统计分析。下面分析数据透视分析对其的重要价值。,问题分析：为屏幕显示和实例讲解方便，仅选取局部数据，实际数据行要非常多。可以看出，该表就是一个“流水账，在平时进展销售数据快速录入时，非常方便。但是，想从该表格中得到汇总数据和统计结果，需要利用相关公式，这是相比照较繁琐的。,统计数据透视分析的作用,例如，到了月底，公司经理想获取如下信息，从该表格中几乎是难于直接获取的。,(1)本月各种不同品名散件的各自销售额是多少?销售额最大的是哪种散件?,(2)所有业务员中，销售额最高和最低的人员分别是谁?业绩相差多少?,(3)各客户销售额占总销售额的百分比分别是多少?销售额最多的是哪一家客户?,(4)各种不同散件的销售百分比是多少?其中各个客户的销售额百分比又是多少?,以上4个问题的解决，在Excel中比较快捷的方法就是利用数据透视表来实现。,数据透视表,1,业务员,|,客户对品名汇总,数据透视表,2,客户,|,业务员对品名求百分比,数据透视表的创立1,操作步骤如下：,(1)选取表格中的任一个单元格，或将插入点放在该表的任一单元格中。,(2)单击“插入“表 数据透视表按钮右侧的箭头，从弹出的下拉列表中，单击“数据透视表选项，出现“创立数据透视表对话框。在本对话框中，可以设置创立数据透视表的数据源和需要将创立的数据透视表放置的位置。,(3)单击“确定按钮，系统将自动插入“Sheet4工作表。,(4)“数据透视表字段列表任务窗格中的“选择要添加到报表的字段区域，选择需要添加到数据透视表的字段，这里选中“销售日期、“客户名称、“业务员、“品名和“合计金额复选框，并通过拖动分别将它们放置到“报表筛选、“行标签、“列标签和“数值区域中,(5)添加了相应字段后，创立的数据透视表如图2-65左边所示。在表格的各个工程下面有工程汇总信息，在最右边和最下边还给出了汇总的销售数据,(6)在透视表中先后选择了“业务员和“客户名称两个字段作为列标签，所以它们进展了分层次的显示，当单击对应业务员名字前面的“-符号时，可以将其后面的具体客户名称折叠隐藏。,通过单击所有业务员前面的“符号，将所有客户名称折叠隐藏,如果再单击“+符号，还可以再将各业务员对不同客户的销售数据详细地显示出来,数据透视表的创立2,数据透视表的创立3,创立数据透视表,数据透视表的创立4,创立结果,创立数据透视表的本卷须知,1注意数据表格的格式,制作数据透视表的数据表格，需要是数据库格式的表格，否那么无法得到相应结果,2创立之前定好光标的位置,在制作数据透视表之前，将光标置于数据区域中间某一单元格中，这样系统就能够智能化地选取数据源所在的整个区域，省去选取操作步骤,创立数据透视表的本卷须知,3注意数据透视表的组成局部,一个完整的数据透视表，是由以下几个不同的区域组成的，要学会合理地进展设置,页字段：数据透视表中被指定为页方向的源数据库中的字段，供筛选用。,行字段：数据透视表中被指定为行方向的源数据库中的字段，可以有多个。,列字段：数据透视表中被指定为列方向的源数据库中的字段，可以有多个。,数据字段：含数据的源数据库中的字段项。,创立数据透视表的本卷须知,4,拖动字段的过程是可逆的,在本例的操作步骤,(4),中，需要将相应的字段拖动到不同的位置。其实，这个操作过程是可逆的，可以将拖动错误的内容撤销掉,5,合理拖动字段的位置,各个字段的拖动位置，与数据分析的目的关系密切，这直接决定了将来制作出来的数据透视表样式。,合理拖动字段位置,创立数据透视表的本卷须知,6可以对数据作一定的筛选,根据数据透视表，可以进展一定的数据筛选。操作时，单击相关字段右侧箭头，从下拉列表选择需要筛选的内容即可。例如，按照客户名称进展了筛选(只选硕星公司)之后的效果,7可以对分析的结果进展排序,在数据透视表中，可以对相关字段进展排序。例如对按照业务员姓名的字典顺序，进展升序排列后的显示效果,筛选结果,排序结果,创立数据透视表的本卷须知,8可以修改数据的汇总方式,在数据透视表中，可以修改数据的汇总方式。操作时，在单元格A3(其中包含了“求和项：合计金额内容)上单击鼠标右键，从弹出的快捷菜单中选择“数据汇总依据菜单项，然后从“数据汇总依据的子菜单中，将当前的方式由“求和修改为“计数即可。,改变汇总方式的结果,创立数据透视表的本卷须知,9可以修改数据的显示形式,在数据透视表中，可以修改数据的显示形式。操作方法为：在A3单元格上单击鼠标右键，从弹出的快捷菜单中单击“值字段设置菜单项，弹出“值字段设置对话框，选择其中的“占总和的百分比，然后单击“确定按钮，即可得到按“占总和百分比方式显示数据的数据透视表效果。,创立数据透视表的本卷须知,改变数据显示形式的结果,数据分组与频率计算,在数据分组与频率计算方面，Excel中有个FREQUENCY函数。它能计算数值在某个区域内的出现频率，然后返回一个垂直数组。,FREQUENCY函数的语法格式为：,FREQUENCY(data_array,bins_array),参数Data_array是一个数组或对一组数值的引用，需要为它计算频率。如果data _array 中不包含任何数值，函数FREQUENCY将返回一个零数组。,参数Bins_array是一个区间数组或对区间的引用，该区间用于对data_array中的数值进展分组。Bins_array中不包含任何数值，函数FREQUENCY返回的值与data_array中的元素个数相等。,统计数据分组与频率计算,某公司3月份销售人员的业绩提成金额表,问题分析：对于这个问题，重点讲解FREQUENCY函数的使用及其本卷须知,本例的具体操作步骤如下：,(1)先建立数据表，其中的D3：D27是下面应用 FREQUENCY函数的第一个参数需要进展分组统计个数的数据区域。,(2)建立F2：H8区域内的表格框架，其中F3：F8局部为输入的描述性文字，起到一定的注释作用，与FREQUENCY函数的应用没有直接关系；G4：G8区域用来准备作为 FREQUENCY函数的第2个参数区间数组，它是根据F3：F8局部的文字说明来进展划分的；H3：H8局部是作为将来放置FREQUENCY函数返回结果的单元格区域。,统计数据分组与频率计算,(3)选定H2：H8单元格区域，输入公式“=FREQUENCY(D3：D27,G4：G8)，然后按下(Ctrl+Shift+Enter)组合键，作为数组公式确认输入。得到各个区间的人数。,(4)选取F3：F8以及H3：H8单元格区域，制作一个饼图，并进展一定的格式设置,经过以上操作之后，题目所要求的销售提成个数统计以及饼图就制作完毕,统计数据分组与频率计算,统计数据分组与频率计算,说 明：,(1)FREQUENCY,必须以数组公式输入，这是由于该函数的返回结果就是一个数组。,(2),一般选择区间数组旁的单元格区域作为返回结果的位置，但是返回数组中的元素个数要比区间数组元素个数多,1,个，多这,1,个表示最上面区间之上的数值个数。,第三讲,数据汇总统计,指定条件的汇总统计,单击“开场 “编辑“自动求和按钮，调用SUM函数，可以快速实现数据的无条件求和汇总。但是，在实际工作中，经常还需要进展条件汇总。本讲就通过实例，介绍通过SUM函数与IF函数组合，以及利用各种SUM类函数进展指定条件的汇总统计。,利用,SUMIF,函数实现单条件汇总,SUMIF函数的功能是根据指定条件对假设干单元格求和，其语法格式如下：,SUMIF(条件判断区域，条件，求和区域),该函数需要三个参数，其中的“条件在形式上可以是数字、表达式或者文本，例如条件可以表达为：100，“8000，“笔记本电脑(注意其中的引号，需要是英文引号)等。,利用,SUMIF,函数实现单条件汇总,举例,某家电销售公司2021年2月份前20天的产品销售记录流水账。而H1单元格中的数值，是根据SUMIF函数汇总出的洗衣机总销售额，H6：H10单元格区域是根据SUMIF函数汇总出的每个业务员的个人总销售额。,问题分析：本例的汇总属于单条件汇总，用SUMIF函数可完成任务。,利用,SUMIF,函数实现单条件汇总,举例,利用,SUMIF,函数实现单条件汇总,举例,操作步骤如下：,(1)按照设计表格，并输入相关数据和文字,(2)在H2单元格中输入如下公式，求洗衣机总销售额,=SUMIF(D2：D21,洗衣机,E2：E21),(3)在H6单元格中输入如下公式，求出“张姗姗的总销售额。,=SUMIF($C$2：$C$21,G6,$E$2：$E$21),(4)选定H6单元格，向下一直拖动到H10单元格，求出所有人员的总销售额。,利用,SUM,和,IF,函数联合实现多条件汇总,某电脑公司的销售记录清单，现在假设需要统计汇总：,(1)2021年1月中旬的销售额；,(2)2021年1月上旬或2月份以后的销售额。,问题分析：对于以上两种数据的统计汇总，都包含两个条件，所以单纯应用SUMIF函数已经无法解决，下面通过使用SUM和IF函数的组合来解决这个问题。,利用,SUM,和,IF,函数联合实现多条件汇总,操作步骤如下：,(1)设计表格，并输入相关数据和文字。,(2)将光标定位到E20单元格，输入如下公式：,=SUM(IF(B2：B17=DATEVALUE(2021-1-11)*(B2：B17组合键，否那么将返回错误值“#VALUE。,利用,SUM,和,IF,函数联合实现多条件汇总,利用,SUMPRODUCT,函数实现多条件汇总,SUMPRODUCT函数的功能是计算几个数组之间对应元素乘积之和，语法格式如下：,SUMPRODUCT(数组1，数组2，数组3，),其中数组的个数不定，但是各数组的维数必须一样，否那么将返回错误值“#VALUE。,某公司采购部在一次采购活动中，所采购商品的采购数量、单价和协议折扣，现在要求计算采购商品的总金额。,问题分析：对于本例中采购商品总金额的计算，利用SUMPRODUCT函数进展计算非常方便，借助于数组公式很快就能汇总统计出所需结果。,利用,SUMPRODUCT,函数实现多条件汇总,操作步骤如下：,(1)将光标定位到B20中，可以随意确定单元格。,(2)输入公式“=SUMPRODUCT(B2：B17,C2：C17,1-D2：D17)。,(3)按下(Ctrl+Shift+Enter)组合键，确认数组公式。,即可在B20单元格计算出来本次采购商品的总金额，,利用,SUMPRODUCT,函数实现多条件汇总,利用,SUMPRODUCT,函数实现多条件汇总,说明,从本例可以看出，,SUMPRODUCT,函数可以计算几个数组之间对应元素乘积之和。其实，它还可以用于多条件的求和。,利用,SUMIFS,函数实现多重条件的数据汇总,SUMIFS函数是Excel2007中新增的一个特色的统计汇总函数，它解决了SUNIF函数只能设置一个条件的局限性，可以设置多个不同的条件。其语法格式如下：,SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2)，,其中：,求和区域进展求和的单元格区域，包括数字或包含数字的名称、数组或引用。,条件区域1，条件区域2，是计算关联条件的区域，最多可以设置127个。,条件1，条件2，是数字、表达式、单元格引用或文本形式的条件，要与前面的条件区域相对应，最多可以设置127个，格式要求与SUMIF函数的一样。,利用,SUMIFS,函数实现多重条件的数据汇总,某公司工程工程部人员的薪资根本数据。现在要求在J28单元格中汇总出“第1工程组中，工龄在20年以上的男工程师根本工资总额。,问题分析：这里要汇总“第1工程组中工龄在20年以上的男工程师根本工资总额，其中包括了4个不同的条件(工程组、性别、职称、工龄)，用SUMIF显示无法解决，而不管用SUM与IF函数组合，还是用SUMPRODUCT函数，公式都较复杂。对于这种有多个条件，并且多个条件之间是逻辑“与关系的情况，利用SUMIFS函数可方便地解决。,利用,SUMIFS,函数实现多重条件的数据汇总,本例中，其实只要在J28单元格中输入如下公式即可：=SUMIFS(H2：H26,C2：C26,第1工程组，D2：D26，男,E2：E26,工程师，F2：F26，20),利用,SUMIFS,函数实现多重条件的数据汇总,SUMIFS函数使用时，需要注意以下几个事项：,(1)只有当求和区域中的每一单元格满足为其指定的所有关联条件时，才对这些单元格进展求和，也就是说各个条件之间是严格的逻辑“与关系。,(2)求和区域中包含 TRUE的单元格计算为1，包含FALSE的计算为0。,(3)与SUMIF函数中的条件区域和条件参数不同，SUMIFS中每个条件区域的大小和形状必须与求和区完全一样。,(4)SUMIFS和SUMIF的参数顺序不同。具体而讲，“求和区域参数在SUMIFS中是第一个参数，而在SUMIF中那么是第三个参数,利用DSUM函数对数据库表格进展多条件汇总,Excel中为数据处理提供了12个数据库函数，这些函数都以D开头，也称为D函数。其中DSUM函数的功能就是对数据库表格进展多条件汇总。其语法格式如下：,利用DSUM函数对数据库表格进展多条件汇总,DSUM(database,field,criteria),该函数的功能是：按照criteria(条件区域)的条件，从database(数据库区域)中查找数据，将找到满足条件的记录中对应field(字段名)的内容汇总，作为结果。,database为构成数据库的单元格区域；,field是database中的列标题，可以是常量文本(如“产量)，也可以是数据列在,database中所在位置的列序号：1表示第一列，2表示第二列，3代表第三列：,criteria称为条件区域，该区域应为一个矩形区域，它的正确设置非常重要。,利用DSUM函数对数据库表格进展多条件汇总,某公司的产品销售记录清单，现在要求进展如下操作：,(1)汇总东北区在3月份的销售额；,(2)汇总东北区和西北区在2月份的销售额；,(3)计算华中区的陈回春对KJ-21/4的总销售额。,问题分析：本例中的条件汇总的实现，可通过DSUM函数来完成。,利用DSUM函数对数据库表格进展多条件汇总,操作步骤如下：,(1)设置表格，并输入相关文字，进展表格格式设计。,(2)为第一个问题设置条件区域。本问题需要设置三个条件(一个“销售区域，两个用来确定3月区间的“销售日期条件)，分别在I4、J4、K4单元格中输入“销售区域、“销售日期、“销售日期，在I5、J5、K5单元格中分别输入各自上面对应列标志应该满足条件的文本或条件表达式，即“东北区、“=2021-3-1、“2021-4-1(后面两个条件的交集，正好就是3月应满足的条件)。,(3)将光标定位于J6单元格，输入公式“=DSUM(A1：G28，销售额,I4：K5)，然后按(Enter)键确认公式，即可在J6单元格中汇总出东北区在3月份的销售额。,利用DSUM函数对数据库表格进展多条件汇总,(4)按照与上面类似的方法解决第二个问题。该问题的条件区域包括三行，因为本问题中汇总的数据之间存在着“或的关系，所以条件要输入到不同的行中，本问题中，J14单元格的输入公式为“=DSUM(A1：G28，销售额，I11：K13)。,(5)再按照与上面类似的方法解决第三个问题。该问题的条件区域包括三列两行，每列各有一个与其他不同的列标志，因为本问题中汇总的条件共有三个不同列标题确定的三个条件，而这些数据之间存在着“与的关系，所以三个条件要输入到同一行中。本问题中，J20单元格的输入公式为“=DSUM(A1：G28，销售额,I18：K19)。,以上所有操作完成后，得到如以下图所示的最终结果。,利用DSUM函数对数据库表格进展多条件汇总,数据的分类汇总统计,在对数据库表格进展数据统计时，经常需要按照某些指定列的数据取值要求，对数据库格式表格按分类统计汇总。例如在“公司工资表中，按照财务部、人事部、销售部等分类汇总各部门人员的奖金总和；在“商品销售表中，按照商品种类或者业务员姓名，汇总统计各自对应的销售总额。为此，Excel提供了“分类汇总功能，它通过“数据 “分级显示“分类汇总命令来实现。本局部通过多个实例来讲解其具体操作。,分类汇总的根本知识,分类汇总是在,Excel,数据库表格或者数据清单中快捷地汇总数据的方法，通过分级显示和分类汇总，可以从大量的数据信息中按照某些特殊的需要提出有用的信息。,数据的分类汇总统计,某电脑配件公司2021年1月份的销售流水账(为操作方便，此处只是保存了局部数据，其实实际数据非常多)。到了月底，公司领导想得到如下信息：,(1)每一个业务员的销售总额以及销售额最高的业务员姓名。,(2)每一个客户的月份总购置金额，以及各个客户主要倾向于购置哪些产品。,(3)每一类电脑散件的总销售情况，以及在总销售额中的比例。,(4)在本月中，哪个时间区间销售情况最好。,数据的分类汇总统计,以上信息直接从该数据库表格直接获取几乎是不可能的，即便是通过设置公式进展统计和计算也比较困难。但是，使用分类汇总，轻点几下鼠标，就可以快捷高效得出结果。,总之，使用分类汇总，能够在数据库适当位置加上统计结果，使其变得清晰易懂。,单层分类汇总的建立,对于上面的实例，数据汇总可按业务员进展，也可按照品名进展，还可按照需要的其他字段进展。在执行分类汇总命令之前，首先应该对数据进展排序，将数据库中关键字一样的一些记录集中到一起。当对数据排序之后，就可以对数据库进展数据分类汇总。,按照品名进展分类汇总为例，说明分类汇总的操作。,单层分类汇总的建立,具体操作步骤如下：,(1)对需分类汇总的字段排序，使一样的记录集中。本例将光标定位到“品名列中某一单元格，然后单击“开场一“编辑一“排序和筛选按钮，然后从弹出的下拉菜单中选择并单击“升序菜单项，即可实现数据按照“品名升序排列。,(2)选定数据库中任意一个单元格。,(3)运行“数据“分析显示“分类汇总命令，出现“分类汇总对话框。在本对话框中进展如下操作：,单击“分类字段下拉列表按钮，选择“品名字段作为分类汇总的字段。,在“汇总方式下拉列表中，选择需要的统计函数。分类汇总可以支持求和、平均数、最大、最小、计数、乘积等共计11种函数。根据需要，本例选择“求和。,在“选定汇总项列表中，选中需要对其汇总计算的字段前面的复选框。本例中选上“合计金额复选框即可。,单层分类汇总的建立,(4)选择汇总数据的保存方式，可以看出，有以下三种方式可选：,替换当前分类汇总：选择这种方式时，新的分类汇总会取代以前旧的分类汇总。,每组数据分页：选择这种方式时，各种不同的分类数据将分页保存。,汇总结果显示在数据下方：选择这种方式时，原数据下方会显示汇总计算的结果。上述三种方式可同时选中，Excel默认的选择是选中第一项和第三项。,(5)单击“确定按钮，可以得到分类汇总结果。,单层分类汇总的建立,结果显示可以看出，分类汇总为汇总数据提供了非常灵活有用的方式，它主要可以实现以下功能：,(1),在数据库表格上显示一组数据的分类汇总及全体总和。,(2),在数据库表格上显示多组数据的分类汇总及全体总和。,(3),在分组数据上求和、统计个数、求平均数、求最大,/,小值、求总体方差等计算。,(4),为了实现汇总数据的分组方式和打印处理，还可以设置每组数据分页放置。,单层分类汇总的建立,对“品名列进展分类汇总结果,单层分类汇总的建立,按“品名分类，显示到2级的结果,单层分类汇总的建立,(1)分类汇总的显示效果还可以进展去除。操作时，先翻开“分类汇总对话框，然后单击左下角的“全部删除按钮即可(只是删除汇总结果，原数据将完全保存)。,(2)为了保险起见，在分类汇总之前最好先做好原数据库的备份工作。,(3)在进展分类汇总之前，按照需要分类的字段进展排序非常重要，否那么一样工程将无法在分类汇总的结果中计算到一起，如以下图所示。,单层分类汇总的建立,未按分类字段排序的结果,多重分类汇总的操作,上面介绍的分类汇总仅仅只是按照“品名关键字对“合计金额进展了一种汇总运算。实际上。在同一分类汇总级上可能不只进展一种汇总运算。为此，Excel还提供了对同一分类进展多重汇总的功能，这就是所谓的多重分类汇总。,假设要在同一汇总表中显示两个以上的汇总数据时，只需对同一数据清单进展两次不同的汇总运算即可。需要注意的是，第二次分类汇总必须在第一次的汇总结果上进展。,多重分类汇总的操作,对于前例，假设想按照客户分类汇总，汇总时，一方面对各个客户的“合计金额求和，另一方面还要求求出对每一个客户的交易次数，这就属于多重分类汇总。,其具体操作步骤如下：,(1)对数据库表格，先按照“客户关键字进展排序。,(2)以“客户名称为分类汇总关键字，“求和为汇总方式，汇总字段为“合计金额，对数据表格进展第一次分类汇总。,(3)仍以“客户名称为分类汇总关键字，“计数为汇总方式，汇总字段为“业务员，对数据表格进展第二次分类汇总。在本次设置“分类汇总对话框时，要取消对话框中“替换当前分类汇总(C)复选框的设置,多重分类汇总的操作,经过上述分类汇总后，最后的多重分类汇总，显示效果如图,4-14,所示,(,注意此时左侧的显示按钮已经有了四个层次，分别对应,l,、,2,、,3,、,4,，本图显示到了级别,3),。,嵌套分类汇总的操作,所谓嵌套分类汇总，就是在一个已经按照某一个关键字建立好分类汇总的汇总表中，再按照另一个关键字进展另一种分类汇总，这里要求的是两次的分类汇总关键字不同。,建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余的依此类推。,在进展嵌套分类汇总时，有几层嵌套汇总就需要进展几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果上操作，其余的依此类推。,嵌套分类汇总的操作,嵌套分类汇总与多重分类汇总的一样点在于二者都需要进展屡次的分类汇总操作；区别在于后者每次的汇总关键字都一样，而前者每次的分类汇总关键字却不一样。,在前面的例子中，假设现在要先按照“业务员，然后再按照“品名进展分类汇总，用来汇总各散件的销售数量和销售总金额，就属于嵌套分类汇总。,嵌套分类汇总的操作,其具体操作步骤如下：,(1)以“业务员为第一关键字，“品名为第二关键字对数据表格进展排序。,(2)以“业务员为分类汇总关键字，“求和为汇总方式，汇总字段为“合计金额，对数据表格进展第一次分类汇总。,(3)以“品名为分类汇总关键字，“求和为汇总方式，汇总字段为“合计金额，对数据表格进展第二次分类汇总。，在本次设置“分类汇总对话框时，要取消对话框中“替换当前分类汇总(C)复选框的设置。,经过以上的操作，最终的嵌套分类汇总效果，如以下图所示。,嵌套分类汇总的操作,对筛选数据做动态汇总,在Excel中，分类汇总运算其实使用了一个SUBTOTAL函数，该函数的格式如下：,SUBTOTAL(function_num，ref1，ref2,.),其中：,Function num为1到11(包含隐藏值)或101到111(忽略隐藏值)之间的数字，指定使用何种函数进展分类汇总计算，各自对应函数关系如下表所示。,ref1、ref2为要进展分类汇总计算的l到254个区域或引用。,合理地使用SUBTOTAL函数，并适当地结合数据筛选操作，可以方便、灵活地对动态数据区域进展数据汇总运算。,对筛选数据做动态汇总,Function_num,（包含隐藏值）,Function_num,（忽略隐藏值）,对应函数,函数功能,1,101,AVERAGE,求均值,2,102,COUNT,求含数字单元格的个数,3,103,COUNTA,求所有单元格的个数,4,104,MAX,求最大值,5,105,MIN,求最小值,6,106,PRODUCT,求乘积,7,107,STDEV,求标准差,8,108,STDEVP,求总体标准差,9,109,SUM,求和,10,110,VAR,求方差,11,111,VARP,求总体方差,对筛选数据做动态汇总,某单