【文档说明】EXCEL培训1数据汇总与图表展示.pptx，共(199)页，6.871 MB，由精品优选上传

转载请保留链接：https://www.ichengzhen.cn/view-267565.html

以下为本文档部分文字说明：

EXCEL讲座主讲人：罗亚非北京工业大学经济与管理学院E-mail：luoyafei@bjut.edu.cn第一讲数据的组织与整理EXCEL数据库表格的样式与特点数据库是一个数据区域同一字段的取值类型相同数据库中的每一行称为一个记录在多数情况下，数据

库中还应有一个条件区域EXCEL数据库表格的遵循准则一个数据库表格要单独占一个工作表，避免多个数据库表格放在一个工作表上数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开避免在数据库中间放置空白行或空白列，任意

两行的内容不能完全相同重要的固定数据(如编号、姓名)设置为左侧字段，移动滚动条时可将其锁定字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别条件区域不要放在数据库的数据区域下方将数据库表格转换为EXCEL表为了更加容易地管理和分析一组相关数据，可以将Excel数据

库表格单元格区域转换为Excel表(这里的Excel表是一种特指，它是一系列包含相关数据的行和列。在Excel2007以前的版本，将其称之为“Excel列表”)。Excel表在数据分析中具有非常重要的用途。数据库表格转化为EXCEL表的格式具体操作步骤如下：将活动单元格放到中数据

库表格的任意一个单元格中单击“插入”→“表”→“表”命令按钮，弹出“创建表”对话框在“创建表”对话框中，系统自动选取单元格所在的连续区域为表数据的来源单击“确定”按钮，数据库表格区域变成了Excel表的格式EXCEL表与数据库表格区域比较，提供了很多附

加功能：在默认情况下，表中的每一列都在标题行中启用了筛选功能，以便可以快速筛选表数据或对其进行排序在表格最后行一行的下面，增加了一个汇总行，方便数据汇总Excel表的各个数据行，设计成了不同颜色底纹的间隔效果，便于查看行数据使用表的右下角

的大小调整控点，可以拖动表，使其大小满足需要当在Excel表上单击鼠标左键时，系统还会适时弹出“表工具”选项卡，该选项卡下面提供了很多可以用来对Excel表进行相关设置的命令按钮导入文本格式的文件问题分析：要将文本格式的固定资产数据表文件导入到Excel表中，只需先启动Excel软件，然后执行

“打开”文件的操作，设置打开文件的类型为“文本文件”格式，然后从文本格式的固定资产数据表文件的所在位置找到打开即可。具体操作步骤如下：启动Excel2007软件单击“Office按钮”一“打开”命令按钮，弹出“打开”对话框从“

查找范围”下拉列表框找到需要打开文本文件所在的文件夹，从“文件类型”下拉列表中选择“文本文件”，从中可以看到需要打开的“固定资产数据表”文件，然后用鼠标单击该文本文件的图标启动了“文本导入向导”，该向导共包括三个步骤导入文本格式的文

件步骤1固定资产数据文本文件导入举例导入文本格式的文件步骤2导入文本格式的文件步骤3数据的排序Excel中数据排序的基本规则排序有升序或降序两种方式。以按升序排序为例，Excel使用如下排序规则：数字按从最小的负数到最大的正数排序。字母按照英文字

母A～z和a～z的先后顺序排序。在对文本进行排序时，Excel从左到右一个字符一个字符地进行排序比较，若两个文本的第一个字符相同，则比较第二个字符，若第二个也相同，则比较第三个……一旦比较出大小，则不再比较后面的

字符。特殊符号以及包含数字的文本，升序按如下排列：0~9!”#$%&()*,./:;?@[\]^_'{|}~+<=>A~Za~z在逻辑值中，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中，实现数据透视分析的工具是数据透视表和数据透视图。其中，数据透视表用来对大量的统计数据进行快速汇总和建立交叉列表，本讲概括性地介绍数据透视表的基本操作。统计数据透视分析的作用下面首先通过一个实例

，来说明对统计数据进行透视分析的作用与处理结果顺达电脑公司2009年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是一个数组或对一组数值的引用，需要为它计算频率。如果dat

a_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函数实现单条件汇总举例某家电销售公司2009年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)2009年1月中旬的销售额；(2)2009年1月上旬或2月份以后的销售额。问题分析：对于以上两种数据的统计汇

总，都包含两个条件，所以单纯应用SUMIF函数已经无法解决，下面通过使用SUM和IF函数的组合来解决这个问题。利用SUM和IF函数联合实现多条件汇总操作步骤如下：(1)设计表格，并输入相关数据和文字。(2)将光标定位到E20单元格，输入如

下公式：=SUM(IF((B2：B17>=DATEVALUE("2009-1-11"))*(B2：B17<=DATEVALUE("2009-1-20")),E2：E17))(3)按下(ctrl+Shift+Enter)组合键，构造数组公式，求出2009年1月中旬销售额。

(4)将光标定位到E21单元格，输入如下公式，=SUM(IF((B2：B17<=DATEVALUE("2009-1-10"))+(B2：B17>=DATEVALUE("2009-2-1")),E2：E17

))(5)按下(Ctrl+Shift+Enter)组合键，构造数组公式，求出2009年1月上旬，或2月份以后的销售额。利用SUM和IF函数联合实现多条件汇总说明：（1）这里的两个公式中，IF函数都包括了两个参数，他们之间用“×”连接的表示二者之间是“与”的关系，也就是两个条件都

必须同时满足才能进行求和；用“+”连接的表示二者之间是“或”的关系，也就是两个条件只要满足其中的一个就可以进行求和运算，“×”和“+”的选取要根据实际需要而定。(2)在上述公式中，要注意日期的使用方法：其中DATEVALUE函数的功能是将以文本表示的日期转换为一个序

列号数字。(3)使用SUM和IF函数组合进行多条件求和时，必须按照数组公式输入。所以，在公式输完后，不要忘记按下(Ctrl+Shift+Enter>组合键，否则将返回错误值“#VALUE"。利用SUM和IF函数联合实现多条件汇总利用SUMPR

ODUCT函数实现多条件汇总SUMPRODUCT函数的功能是计算几个数组之间对应元素乘积之和，语法格式如下：SUMPRODUCT(数组1，数组2，数组3，……)其中数组的个数不定，但是各数组的维数必须相同，否则将返回错

误值“#VALUE"。某公司采购部在一次采购活动中，所采购商品的采购数量、单价和协议折扣，现在要求计算采购商品的总金额。问题分析：对于本例中采购商品总金额的计算，利用SUMPRODUCT函数进行计算非常方便，借助于数组公式很快就能汇总统计出所需结果。利用SUMPRODUCT函数实现多条件汇总

操作步骤如下：(1)将光标定位到B20中，（可以随意确定单元格）。(2)输入公式“=SUMPRODUCT(B2：B17,C2：C17,1-D2：D17)”。(3)按下(Ctrl+Shift+Enter)组合键，确认数组公式。即可在B20单元格计算出来本次采购商品的总金额，利用SUMPRO

DUCT函数实现多条件汇总利用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代表第三列……：crite

ria称为条件区域，该区域应为一个矩形区域，它的正确设置非常重要。利用DSUM函数对数据库表格进行多条件汇总某公司的产品销售记录清单，现在要求进行如下操作：(1)汇总东北区在3月份的销售额；(2)汇总东北区和西北区在2月份的销售额；(3)计算华中区的陈回春对KJ-

21/4的总销售额。问题分析：本例中的条件汇总的实现，可通过DSUM函数来完成。利用DSUM函数对数据库表格进行多条件汇总操作步骤如下：(1)设置表格，并输入相关文字，进行表格格式设计。(2)为第一个问题

设置条件区域。本问题需要设置三个条件(一个“销售区域”，两个用来确定3月区间的“销售日期”条件)，分别在I4、J4、K4单元格中输入“销售区域”、“销售日期”、“销售日期”，在I5、J5、K5单元格中分别输入各自上面对应列标志应该

满足条件的文本或条件表达式，即“东北区”、“>=2009-3-1”、“<2009-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数据库表格或者数据清单中快捷地汇总数据的方法，通过分级显示和分类汇总，可以从大量的数据信息中按照某些特殊的需要提出有用的信息。数据的分类汇总统计某电脑配件

公司2009年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（忽略隐藏值）对应函数函数功能1101AVERAGE

求均值2102COUNT求含数字单元格的个数3103COUNTA求所有单元格的个数4104MAX求最大值5105MIN求最小值6106PRODUCT求乘积7107STDEV求标准差8108STDEVP求总体标准差9109SUM求和10110VAR求方差11111VARP求总

体方差对筛选数据做动态汇总某单位部部分员工的工资数据表，并且对该表进行了数据筛选操作，最下面18行中显示的是人数的统计和相关项目的合计数据。现在需要实现如下功能：在部门名称中选择不同部门时，“合计

”行对应的数字进行动态变化，以便快速确定各个不同的人数以及相关项目的合计数字。问题分析：要实现上述功能，可以考虑使用SUBTOTAL函数。对筛选数据做动态汇总具体操作步骤如下：(1)在C18中输入公式“=SUBTOTAL(3，C3：C17)&"人"”，求合计人数。(2)在D18中

输入公式“=SUBTOTAL(9，D3：D17)”，求基本工资的合计数。(3)选定D18单元格，一直向右拖动复制到M18单元格，求出其余项目的合计数。经过以上的操作后，当在部门名称中相应数据进行筛选时，第18行的合计数将会自动地发生变化。如下

图示，就是在选择“财务部”后合计数发生的相应变化情况。对筛选数据做动态汇总多表数据的合并汇总在实际数据统计中，有时数据被存放到不同的工作表中，这些工作表可在同一个工作簿中，也可来源于不同的工作簿，它们格式基本相同，只是由于所表示的数据因为时间、部门、地点、使用者

的不同而进行了分类；但到一定时间，还需要对这些数据表进行合并汇总，将合并汇总的统计结果放到某一个主工作簿的相关工作表中。多表数据的合并汇总也就是说，对数据进行合并计算就是组合数据，以便能够更容易地对数据进行定期或不定期的更新和汇总。例如，一家企业集团，开始时为了分散管理的方便，分别

将各个子公司的销售信息存放到了不同的工作簿中，到年终销售统计时，可以采用多表合并的方式把各个工作簿中的信息再合并到一个主工作簿中。这就是一个明显的多表数据合并问题。利用三维引用公式实现多表汇总进行多表数据汇总，最好理解的方法就是通过公式的“三维引用”来实现。如下图

所示，图中标签名分别为“1号”到“5号”的工作表其表结构与“本月汇总”工作表相同。它们分别用来保存整个月份中各个日期的产品数据，而“本月汇总”是对它们的合并汇总，并且随着时间的推移，图中“空白表”工作表前面可以不断增加新的工作表，如“6号”、“7

号”……，而“本月汇总”中数字也将自动更新。问题分析：本例中，由于“本月汇总”工作表与前面的所有工作表结构完全相同，所以其中汇总数据的计算，可通过三维引用的SUM函数来实现。利用三维引用公式实现多表汇总具体操作步骤

如下：(1)将光标定位到“本月汇总”工作表的B2单元格，在编辑栏中输入“=SUM()”，然后再将光标置于两个括号中间。(2)点击“1号”工作表标签，然后按下(Shift)键，再点击“空白表”工作表标签。(3)单击任意一个日期工作表或者“空白表”中

的B2单元格。(4)回到编辑栏，可以看到编辑栏中公式为“=SUM(’1号：空白表’!B2)”，按(Enter)键，在“本月汇总”工作表的B2单元格中的对应数据被汇总出来。(5)选取“本月汇总”工作表中的B2单元格，向下拖动复制到B8单元格，得到第1分公司各个产品的汇总数据。(6

)选取“本月汇总”工作表中的B2：B8单元格区域，向右拖动复制到F2：F8单元格区域，求出各个分公司对应各种产品的汇总数据。利用三维引用公式实现多表汇总说明：(1)公式“=SUM(’1号：空白表'!B2)”中的符号“”是Excel自动添加的，该公式表示将从“1号”一

直到“空白表”之间所有工作表中的B2单元格数据进行求和。(2)本例中“空白表”工作表的设置，其实只是起到一个辅助作用，目的就在于：当在它前面再插入新的工作表后，该公式的合并范围自动扩展，从而使合并汇总结果总保持最新状态；如果不这样做，则上面的公式将为“=SUM('1号：5号

'!B2)”，当在“5号”工作表后再增加新的工作表“6号”、“7号”……时，“本月汇总”的数据无法自动更新。(3)上面利用SUM函数进行多表合并汇总的方法，只适用于多张工作表具有相同格式的情况；当需要汇总的表格格式不完全一样时，读者则可以考虑如何利用SUMIF函数来实现。利用三维引用公式

实现多表汇总按照位置进行数据的合并计算在Excel2007中，单击“数据”→“数据工具”→“合并计算”命令，会弹出“合并计算”对话框，根据需要合并的多张工作表格式的不同，此处的“合并计算”可以分为两种：一种是按位置进行的“合并计

算”(适用于多张待合并表格的行标题或列标题的顺序和位置完全相同)；另一种是按分类进行的“合并计算”(适用于多张待合并表格具有相同的行标题或列标题，但是以不同的顺序和位置来组织数据)。按照位置进行数据的合并计算某集团五个分公司各自的月份经营数据已经得到，分别保存在“第一分公司”到“第

五分公司”的各个工作表中。这些数据表格都有着与图中可见的“第一分公司”表数据区域完全相同的行标题和列标题，顺序也完全一样。现在到了年末，集团公司想对这些分公司的数据进行合并汇总，以便了解集团整体运营情况。问题分析：显然，以

上问题符合按照位置进行“合并计算”的条件。下面就介绍利用Excel的“合并计算”命令，按位置对上述表格进行合并汇总。按照位置进行数据的合并计算操作步骤如下：(1)在“第五分公司”工作表后面插入一个新工作表“集团合计”，并按照与“第一分公司

”工作表完全相同的位置和顺序，复制得到“集团合计”工作表的行、列标题。(2)选取“集团合计”工作表的B2．G13单元格区域。(3)运行“数据”→“数据工具”→“合并计算”命令，弹出“合并计算”对话框，其中的函数就选用默认的“求和”。(4)在“合并计算”对话框中，将光标置于

“引用位置”下的文本框中，然后选取“第一分公司”工作表的B2：G13区域，使其出现到“引用位置”下的文本框中，最后按下“添加”按钮，“合并计算”对话框中第一个引用位置添加完成，效果如下图所示。按照位置进行数据的合并计算(5)按照与步骤(4)相同的方法，将“第二分公司”到“第五分公司”工作表中

的数据依次添加到“合并计算”的引用位置：并将“创建指向源数据的链接”复选框选中，最后的“合并计算”对话框设置完毕。(6)单击“确定”按钮，五张表格的合并汇总结果被求出，按照位置进行数据的合并计算说明：(1)上图“标签位置”下的复选框不要选中，它们只适用于按分类进行的合并计算。(2)Exce

l的合并计算，并不只是进行求和汇总，它还包括诸如求和、求平均数、计数统计、求标准差等其他汇总运算。在“合并计算”对话框中的“函数”组合框里面，可选定不同的汇总函数。下表列出了这些函数的名称与含义。按照位置进行数据的合并计算

函数含义函数含义SUM求和COUNT计数AVERAGE求平均值STDDEV求标准偏差MAX求最大值STDDEVP求总体标准偏差MIN求最小值VAR求方差PRODUCT求对应单元格的乘积VARP总体方差按照位置进行数据的合并计算按照分类进行数据的合并计算如果需要合并的各个工作表中的数据区域具有相同

的行标题或列标题，但是它们是以不同的方式组织的(比如位置不同或者顺序不同)，则可按分类进行合并计算。某电器制造公司三个分公司各自的月份销售数据。为了了解整个公司各种电器产品的整体销售情况，现在需要对该

三张数据表进行合并汇总。从表中可以看出，这三个数据表的列标题内容是一样的，但是顺序不同。所以，不能直接按位置进行合并计算，而只能按分类进行合并计算。按照分类进行数据的合并计算按照分类进行数据的合并计算操作步骤如下：(1)在“天津”工作表后面插入一个新工作表“合

计”，在其中A1、A2、B2分别输入“全公司合计销售情况”、“品名”和“销售额”，然后选定A1：B1单元格，进行“合并居中”操作，这样就做好了“合计”工作表中的数据表标题和数据表的行标题。(2)将光标定位到“合计”工作表的A3单元格。(3)单击“数据

”→“数据工具”→“合并计算”命令，弹出“合并计算”对话框，其中：函数仍用默认的“求和”，同时将“标签位置”下的“最左列”复选框选中。(4)用“按位置”合并计算相同的方法，添加引用位置，最终效果如下图所示。按照分类进行数据的合并计算按分类“合并计算”得到的“合计”工作表按照分类进行

数据的合并计算说明：按分类进行合并计算时，各工作表中相关的行标题和列标题的名称必须完全一样才会被合并到一起。例如：有的表格输入了“笔记本电脑”，而另外的输入了“笔记本计算机”，则它们将不会被合并计算

。另外，还特别需要注意的是，当行标题和列标题为英文或者拼音时，必须确保在所有源区域中都以相同的拼写和大小写形式输入。例如，不同的表格设计者在表示“年度平均”时，行标题分别使用了“Annual-Avg、“AnnaalAvg”、“Ann

ual_Avg”或“AnnumlAverage”，它们都是不相同的，不会被合并计算。数据合并统计汇总的方法对比本节介绍数据合并的三种汇总统计方法，各有适用情况，其对比可总结成表：数据情形适用方法按同样的顺序排列所有工作表中的数据，并将它们放在同一位置中按位置进行合并计算以不同的方式

组织各个数据工作表中的数据，但是它们使用了相同的行标题和列标题名称，这样能够与主工作表中的数据进行匹配按分类进行合并计算在公式中使用对要合并计算的其他工作表的某一个单元格引用或进行三维公式引用(没有可依赖的相同数据位置或一

致的分类标准)通过公式进行合并计算特定情形的汇总统计--统计数据的累加汇总介绍几种特定情境下的数据汇总统计方法，大家应该掌握其方法与技巧。将一列数据从上往下(或一行数据自左向右)累加汇总，是数据统计中一种常见操作。它适用于统计那些需要汇总的不固定行(或列)，

并且想看其随时间而变化的情形。表格中B列和C列为2009年某种传染病的病例报告人数和死亡报告人数，现在需要在D列和E列汇总出每个报告日的累计病例人数和累计死亡人数。特定情形的汇总统计--统计数据的累加汇

总问题分析：该问题就属于上面所说的累加汇总，其中相关累计数据的汇总计算，可以使用SUM函数。但是需要注意：在作为SUM函数变量的单元格区域引用中，第一个单元格应为绝对引用(就是第一个数据所在位置的单元格)，而第二个单元格地址应为相对引用(它要

随累计到的位置而变化)。要实现以上功能，操作步骤如下：(1)在D2单元格中，输入公式“=SUM($B$2：B2)”。(2)在E2单元格中，输入公式“=SUM($C$2：C2)”。(3)选定D2：E2单元格区域，向下拖动，将其中的公式复制到所需

行，得到每天对应的累计病例人数和累计死亡人数，结果如下图所示特定情形的汇总统计--统计数据的累加汇总特定情形的汇总统计--统计数据的动态汇总在实际工作中，有时需要对一个不断更新的数据源进行动态汇总，比如要

求出到某一指定日期或者当天为止，某一个统计指标的累计数据。此时可以考虑使用SUMIF函数。如表所示，A和B列单元格区域给出了为某产品5月份的日销售量记录，现在需在E1单元格中动态显示当前日期，E2单元格中显示当天销售数量，E3单元格中显示截至当天为止的本月累计销售数量，E6单元

格中显示出来截止到当前日期，本月销售计划的完成百分比情况(其中的E5单元格已经事先输入了本月的销售计划)。特定情形的汇总统计--统计数据的动态汇总问题分析：该问题中E3单元格中本月累计销售量的统计汇总，就属于上面的动态区域

汇总，所以可考虑应用SUMIF函数来解决。特定情形的汇总统计--统计数据的动态汇总要实现以上功能，操作步骤如下：(1)采用指定区域定义名称的方法，将A列和B列命名为“日期”和“销售量”。(2)在El中输入公式“=TODAY()”，用来显示当天日期。(3)在E2中输入公式“=SUM

IF(日期，"="&E1，销售量)”，获得当天销售量。(4)在E3中输入公式“=SUMIF(日期，"<"&E1，销售量)”，计算到当天为止的本月累计销售量。(5)在E6中输入公式“=E3/E5”，计算到当天为止的销售计划完成百分比。(6)将E6单元格设置为带两位小数的百分比格式。另外，为

醒目起见，为上面的计算结果所在的单元格设置字体颜色和单元格填充色。最终的效果如下图所示。特定情形的汇总统计--统计数据的动态汇总特定情形的汇总统计--统计数据的动态汇总本实例利用了SUNIF函数和指定名称进行动态数据区域汇总，其实E3中累计销售量的计算还有其他方法，这

里介绍两种。方法一：如果上面表格中A列的日期是连续排列的，中间没有间隔的情况(就像表格中前20天的情况一样)，那么B列中销售量所在区域就是固定的(应为B2：B32)，此时E3中公式可以设置为“=SUM(B2：B32)，，即

可，没有输入的数据按0进行计算。方法二：如果上面表格中A列的日期不一定连续，也就是当天如果没有销售量就不再输入，此时可以使用公式“=SUM(B：B)”，其中(B：B)表示整个B列，当然此时B列中的所有

单元格都不要输入任何其他无关的内容。特定情形的汇总统计--不连续区域的统计汇总在很多情况下，要进行的数据统计汇总，都是针对某一个连续区域的。但是，有时候根据实际需要，也可能需要对一些不连续区域进行统计汇总。下面通一个过实例来说明。东星公司2008年下半年销售计划完成情况

汇总表，其中每个月的数据都包括“计划指标”和“完成情况”两行数据，现在需要计算下半年合计的“计划指标”和“完成情况”，并根据这两个数据，计算出各个销售区域的计划指标“完成比例”。另外，还需要在H列中汇总统计出对应于各个月份的总计数据。特定情形的汇总统计--不连续区域的统计汇总问题分析：该实

例中下半年合计“计划指标”和“完成情况”的计算，其实就是不连续区域的数据汇总问题，因为这里需要合并的各月“计划指标”和“完成情况”是间隔排列的。此时，如果一个一个单元格进行相加是非常繁琐的。为此可以考虑一个便捷的方法。那就是通过巧妙地应用B列中

的标题文字“计划指标”和“完成情况”，然后利用SUMIF函数，计算得出上半年合计的“计划指标”和“完成情况”。特定情形的汇总统计--不连续区域的统计汇总特定情形的汇总统计--不连续区域的统计汇总具体操作步骤如下：(1)在C15单元格中输入公式“=SUMIF($B$3：$B$

14，"计划指标",C3：C14)”，得出下半年“东北区”的合计计划指标。(2)在C16单元格中输入公式“=SUMIF($B$3：$B$14，"完成情况",C3：C14)”，得出上半年“东北区”的合计完成

情况。(3)选定C15：C16单元格区域，向右拖动，复制其公式一直到G15：G16单元格区域，得到其他各个销售区域在2008年下半年的合计计划指标和合计完成情况。特定情形的汇总统计--不连续区域的统计汇总(4)在H3单元格中输入公式“=SUM(C3：G3)”，得到7月份各个区

域计划指标总额。(5)选定H3单元格，向下拖动，复制公式一直到H16单元格，得到各个月份以及下半年合计的计划指标和完成情况的合计数据。(6)在C17单元格中输入公式“=C16/C15”，得出上半年“东北区”的完成比例。(7)选定C17单元格，向右拖动，复制公式一直到H17，得出

其他销售区域以及各区域销售总计数据在上半年的完成比例情况。(8)选择C17：H17单元格区域，将其单元格的数字格式设置为带两位小数的百分比格式。(9)为了屏幕显示的美观，为以上计算得到的结果单元格，设置适当字体和填充色。特定情形的汇总统计--不连续区域的统计汇总经过以上操作后，本例的最后制

作效果如图所示。特定情形的汇总统计—对含错误值区域的数据汇总当对数据区域统计汇总时，如果数据区中存在错误值，那么统计结果将会出现错误。特定情形的汇总统计—对含错误值区域的数据汇总如上图所示，在对D2：D11单元格区域利用SUM函数进行数据汇总时，由于D4和D7单元格中存在错误值，

SUM函数无法得到正确的统计汇总结果。问题分析：对于这类问题，需要使用错误判断函数ISERROR进行判断，并利用数组公式生成一个不再包含错误值的数组，然后才能得到正确的结果。特定情形的汇总统计—对含错误值区域的数据汇总具体操作

步骤如下：(1)将光标定位到需要放置汇总结果的单元格D14中。(2)输入公式“=SUM(IF(ISERROR(D2：D11)，0,D2：D11))”。(3)按下(Ctrl+Shift+Enter)组合键，确认数组公式的

输入。之后，需要统计的结果被汇总出来，如下图所示。当然，其中忽略了出错的数据。特定情形的汇总统计—对含错误值区域的数据汇总特定情形的汇总统计—对含错误值区域的数据汇总上例中，B4和C7单元格中的错误原因，其实在于它们中间均出现了应该输入数字“0”，但是却错误地输成了字

母“O”的问题。这种错误比较隐蔽，如果开始无法找出，就先按照上面的方法进行汇总，以便先“屏蔽”掉这些错误，随后再详细查找错误原因。特定情形的汇总统计—对区域中若干最大/小值的汇总在数据统计中，有时需要汇总数据区域中前若干个最大/小值之和。下面看一实

例。EXCEL表中，A2：E21单元格区域为某市100家规模以上企业员工人数一览表。根据统计工作的实际需求，现在需要在H3单元格和H4单元格中统计汇总出人数最多的5家企业员工总数和其占100家企业总人数的比例；同时，需要在H8单元格和H9单元

格中汇总人数最少的20家企业的员工总数和其占100家企业总人数的比例。问题分析：要实现以上汇总计算功能，需要在SUM函数中，嵌套使用排序函数LARGE和SMALL函数来实现，并借助于常量数组来实现。特定情形的汇总统计—对区域中若干最大/小值的汇总特定情形的汇总统计—对区域中若干最大/小值的

汇总具体操作步骤如下：(1)采用定义名称的方法，将A2：E21单元格区域定义为名称“数据区”。(2)在H3单元格中先输入公式“=SUM(LARGE(数据区，{1，2，3，4，5}))”。上述公式中，首先利用LARGE函数将数据区中前5个最大的数

据找出来，并形成一个数组(注意常量数组外面用{}括起来)，然后利用SUM函数对这些数据进行求和运算。(3)在H4单元格中输入公式“=H3/SUM(数据区)”，并将H4单元格的数字格式设置为带两位小数的百分比格式。特定情形的汇总统计—对区域中若干最大/小值的

汇总(4)在H8单元格中输入如下公式。=SUM(SMALL(数据{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))(5)将H4单元格中的公式复制到H9单元格中。本例最后

统计汇总得到的结果，如下图所示。第四讲EXCEL图表制作常用统计图表类型的样式与作用在各种统计分析活动中，使用较多的统计图表类型有柱形图、条形图、折线图、饼图、面积图、圆环图、XY散点图、雷达图、气泡图等几种

，下面分别举例说明各自作用。1．柱形图柱形图用于显示某段时间内数据的变化，或比较各数据项之问的差异。其分类在水平方向组织(一般为日期或时间)，而数值在垂直方向组织，以便强调相对于时间的变化。另外，在实际管理应用中，柱形图还有一

些变形，例如：堆积柱形图显示了单个数据项与整体的关系，三维透视的柱形图可比较两个坐标轴上的数据点等。1．柱形图2．条形图条形图也用于显示各数据之问的比较。与柱形图不同的是，其分类在垂直方向，而数值在水平方向，以使观察者的注意力集中在数值的比较上，而不在时间上。另外，堆积条形图显示了单个数

据与整体的关系，可以把不同项目之间的关系描述得更清楚。2．条形图3．折线图折线图主要用于显示各数据随时间而发生变化的趋势情况。折线图的横坐标几乎总是表现为时间，比如：年份、季度、月份、日期等。折线图中的子图表类型也很多，有带(或者不带)数据标记的

折线图表、堆积数据点折线图，以及三维折线图表等不同类型。3．折线图4．饼图4．饼图饼图用于显示组成数据系列的各数据项与数据项总和的比例。当只有一个数据系列，并且用于强调整体中的各个组成部分占整体的百分比时，使用饼图就最为合适。在饼图中，要使小扇区更容易查看，还可将它们组织为

饼状图中的一个数据项，然后将该数据项在主图表旁边的小饼状图或小条形图中拆分显示，这就是复合饼图。4．饼图5．面积图5．面积图面积图用于显示不同数据系列之间的对比关系，它可以强调数量随时间而变化的程度，也可用于引起人们对总值趋势的注意；同时，通过显示所绘制的值的总和，它也能显示

数据与整体的关系。5．面积图6．XY散点图XY散点图可用于显示若干数据系列中各数值之间的关系。散点图有两个数值轴，沿水平轴方向显示一组数值数据，沿垂直轴方向显示另一组数值数据。散点图将这些数值合并到单一数据点上显示。它通常用于科学实验、相关分析、回归分析等。6．XY散点图7．圆环图

圆环图用于显示若干数据系列中各单个数据与数据汇总之间的关系，而前面介绍的饼图只能显示一个系列。如图所示，就用来显示男女不同职称教师比例的圆环图。8．雷达图雷达图比较若干数据系列的聚合值。例如，在企业进行竞争对手分析时，往往是从多个指标角度来进行综合性分析，以便

比较自己和各个竞争对手的综合竞争力。此时，将自身企业和竞争对手各个方面的指标数据，做成雷达图，就可以看清楚各自的优势所在。下图所示就是用来对比自身企业和三家竞争对手企业主要指标的两个雷达图。8．雷达图9．气泡图气泡图与XY散点图功能类似，但是XY散点图只能表示

两个变量之间(x值和y值)的关系，而气泡图可以表示三个变量之间(x值、y值和气泡大小)的关系。制作气泡图的时候，需要在数据表上第一列中列出x值，在相邻列中列出相应的y值和气泡大小的值。9．气泡图复杂统计图表的制作--组

合图表的制作在实际统计分析中，Excel2007中的标准图表有时无法满足要求，需要根据实际情况，来自己制作一些格式要求比较复杂的统计图表。本讲介绍几种复杂统计图表的制作。组合图表就是在一个统计图表中表示两个甚至多个数据系列，而且不同的数据系列用不同

的图表类型来表示。利用组合图能够很方便地比较多个数据系列之间的异同。例如：根据数据表数据制作的一个“柱形一折线”组合数据图表，其中：数据系列“预计”显示的为一个柱形图，而另一个数据系列“实际”显示的则为一个折线图；从中可清晰地

看出“预计”与“实际”数值的差别。复杂统计图表的制作--组合图表的制作请根据表格中的三行数据，制作一个“柱形一折线一面积”组合图表，三行数据分别采用对应的不同图表类型。问题分析：上面的组合图表中包含了三种图表类型，而利用标准图表类型显然无法一次做好(虽然以前的Excel2003“自定义图表类

型”提供了组合图，但是也只有“柱形一折线”双组合图表)。为此，考虑先将三种数据系列全部做成一种图表类型，然后再修改数据系列对应的图表类型，最终得到需要的“柱形一折线一面积”组合图表。复杂统计图表的制作

--组合图表的制作根据以上分析，下面将先做柱形图，然后再改造为“柱形一折线一面积”组合图表。具体操作步骤如下：(1)选取图5-45中数据表所在的A2：G5单元格区域。(2)单击“插入”一“图表”一“柱形图”按钮下边的箭头，从类型中选择一种，快速制作出一个柱形图。(3)在制作好图5—4

7a的“实际工作日”数据系列上，单击鼠标右键，从快捷菜单中选择“更改系列图表类型”菜单项，然后从弹出的“更改图表类型”中选择折线图里面的第四种“带有数据标记的折线图”按钮，最后单击“确定”按钮。这样操作之后，变

为了“柱形一折线”组合图表效果。复杂统计图表的制作--组合图表的制作(4)按照与步骤(3)相同的方法，再将“柱形一折线”组合图中的“变动的天数”数据系列更改为面积图。最终改造出来的“柱形一折线一面积”效果如下图所示，然后再设置横坐标轴标题的文字排列方式(修改为“横排”，并调整图表宽度)，即可得

到本例要求效果。复杂统计图表的制作--组合图表的制作复杂统计图表的制作--双轴图表的制作所谓双轴图表。是指在一个统计图表中，在主、次坐标轴上分别绘制一个或者多个数据系列，它们可以采用两种不同的坐标值来度量数据。当一个数据图表涉及多个数据系列，并且不同数据系列的数值相差悬殊时，比较合理的图表方案

就是建立双轴图表。复杂统计图表的制作--双轴图表的制作例如：数据表格表示的是A市2009年3月上旬新建商品房销售情况表，其中包括总销售面积和销售均价，请根据表中的数据制作适当的统计图表，用来方便地对比分析这10天中间

总销售面积和销售均价的变化情况。问题分析：上面数据表中，每天总销售面积和销售均价数值差别太大，如果直接制作柱形图，采用相同的数值刻度，则根本无法对比每天的销售均价。为此，考虑先将其改造为“柱形一折线”组合图表，然后再在次

坐标轴上设置销售均价数据系列，构造双轴图表。复杂统计图表的制作--双轴图表的制作根据以上分析，下面将先做柱形图，然后改造为“柱形一折线”组合图表，最后再将折线图表对应的数据系列显示到次坐标轴上，构造为

双轴的“柱形一折线”组合图表。具体操作步骤如下：(1)选取数据表所在的A2：K4单元格区域。(2)单击“插入”→“图表”→“柱形图”按钮下边的箭头，从类型中选择一种，快速制作出一个柱形图。可以看出，该图表根

本无法对比每天的销售均价。(3)在柱形图上，按照5组合图的方法，将其改造为“柱形一折线”组合图表，其中“销售均价”数据系列改造为折线图。复杂统计图表的制作--双轴图表的制作(4)在上面“销售均价”数据系列所在折线图上单击右键，从快捷菜单中单

击“设置数据系列格式”选项，然后从弹出的“设置数据系列格式”对话框中，选取“系列选项”选项卡，设置系列绘制在“次坐标轴”。至此，双轴“柱形一折线”组合图表已经制作出来，然后再添加必要的图表标题文字以及两个纵坐标轴标题文字，并进行字

体设置后，最终改造出来的效果如下图所示。复杂统计图表的制作--双轴图表的制作复杂统计图表的制作--双轴图表的制作说明：在Excel2003版本中，图表类型中除了有“标准类型”，也提供有“自定义图表类型”，从“自定义图表类型”中就可以选

择组合图表和双轴图表。但是在Excel2007版本中，只是给出了标准的图表类型，并未提供“自定义类型”，不过当用户建立了一个双轴图之后，便可以把其保存在图表的自定义模板类型中，供以后再用。复杂统计图表的制作--复合条饼图的制作利用复合条饼图，可以对数据做更深层次的分析。下面通过

实例来讲解其制作步骤。数据表为“净爽牌”洗衣机的某一批产品，在售出一年后对客户维修的情况反馈，其中包括了换货、无修理以及修理(包括次数的统计)。现在，请根据该数据表制作一个饼图，以便对比换货、无修理与

修理三种情况之间的比例关系，同时对修理次数之间的对比关系也能够清晰地看到。复杂统计图表的制作--复合条饼图的制作问题分析：根据上面的数据表，可以快速将其制作成一般的饼图五种情况列在一个表格中，数据对比不很清晰：没有按照题目的要求显示出已经更换过、没有修理过、已经修理过三种情况之

间的比例关系。另外，修理次数之间的比例关系在图中查看也不方便。为此，需要根据原数据表，来制作复合条饼图。复杂统计图表的制作--复合条饼图的制作根据以上分析，下面将根据数据表制作复合条饼图。具体操作步骤如下：(1)选中数据表的A2：B7单元格区域，然后单击“插入”→“图表”→“饼图”按钮下

边的箭头，找寻其中的“复合条饼图”按钮(2)单击“复合条饼图”按钮后，很快就创建好一个复合条饼图(3)建立的默认图表并未达到目的。下面先增加数据标签。操作时，在主饼图上单击鼠标右键，从快捷菜单上运行“添

加数据标签”，为图表加上数据标签复杂统计图表的制作--复合条饼图的制作(4)上面添加的数据标签是“值”，并不是题目所需要的“百分比”。为此，需要设置数据标签的格式，操作时，在主饼图上单击鼠标右键，从快捷菜单上运行“设置数据标签格式”选项，在随后出

现的“设置数据标签格式”对话框上，选择“系列选项”选项卡，将其中的“百分比”和“显示引导线”复选框选上，取消选中“值”复选框，其余保持默认的设置。本步骤结束后，得到复合条饼图效果。(5)本例中需要把表格中的最后3行

，也就是对应修理次数的3行设置到堆积条形图中，但是默认的图表只将最后2行放到其中，为此需要重新设置。操作方法为：在主饼图上单击鼠标右键，从快捷菜单上运行“设置数据系列格式”选项，在随后出现的“设置数据系列格式”对话框上，选择“系列

选项”选项卡，将“第二绘图区包含最后一个”的值在微调按钮中，由默认的2调整为3。复杂统计图表的制作--复合条饼图的制作经过以上的所有操作，最终优化后的复合条饼图效果如图所示。复杂统计图表的制作--复合条饼图的制作几种特殊用途

统计图表的制作在统计分析中，有时需要制作一些特殊用途的统计图表，例如：用于抽样数据分组统计的累计频率与直方图、用于产品质量统计中使用的帕累托图和质量控制图、用于反映某一地区财富差异程度的洛伦茨曲线等。累计频率与直方图的制作累计频率与直方图在进行统计

分析的时候非常有用。在这种图形上，数据按照一定的间距分组，在直方图上显示了各个分组中的样本频率个数，而各分组的累计百分比通过折线图来表示。下面通过一个实例的操作，介绍累计频率与直方图的制作方法与设置技巧。数据表为某实时信息系统随

机测试100次的系统响应时间，请根据该表制作一个如下图所示的系统响应时间的累计频率与直方图。累计频率与直方图的制作累计频率与直方图的制作问题分析：观察可以发现，系统相应时问范围在150～180之间，因此可按照该总间距j进行数据分组设计(可以分为160及以下，161～165，166～170

，171～175，176～180，180以上几组)，然后利用FREQUENCY函数可以计算出各分组中的样本个数，而累计百分比的计算通过设计一定的公式即可快速得到；最后根据个数和累计百分比制作要求的图表。根据以上分析，下面制作题目要求的累计频率与直方图。累计频率与直方图的制作

操作步骤如下：(1)在数据表中L2：N8单元格区域所示，设置制作累计频率与直方图的源数据。其中：L3：L8单元格区域是根据原表设计的分组间距数字，供FREQUENCY函数使用。M3：M8单元格区域中数据的计算应用FREQUENCY函数。操

作时候先选中M3：M8单元格区域，然后输入公式“=FREQUENCY(A2：J11,L3：L7)”，最后再按下(Ctrl+Shift+Enter)确认数组公式输入，即可得到全部单元格中的频率个数。累计频率与直方

图的制作N3单元格的公式为“=SUM(M$3：M3)/SUM($M$3：$M$8)”(注意其中不同单元格方式的引用)，输入完成后按下(Enter)键确认。然后再选中N3单元格，向下拖动其填充柄，通过复制

公式，得到N4：N8单元格区域中所有的累计百分比数字。(2)选中L2：N8单元格区域，按照前面介绍过的方法，制作一个双轴的“柱形一折线”组合图表。(3)在图表上，频率的图表类型还是一般的条形图，而不是直方图(

直方图各数据组之间没有间距，并且紧挨着分类轴放置)。下面还需要对它进行改造。累计频率与直方图的制作操作方法为：在条形图表上单击鼠标右键，从快捷菜单中选择“设置数据系列格式”菜单项，打开“设置数据系列格式”对话框，在“系列选项”选项卡中，将“分类

间距”通过调整游标设置为“0％”(其目的就是将条形图之间的间距去掉)；在“边框颜色”选项卡中，选择“实线”，并将颜色设置为黑色。(4)上述设置完成后，上面的图表发生了一定的变化。对上面的图表再进行一定的优化设置，就得到了本

题开始所要求的图表效果。累计频率与直方图的制作累计频率与直方图制作的关键有三点：(1)设置好分组间距(2)利用FREQUENCY函数构造数组公式计算各组中的频率个数，利用公式计算累计百分比(注意单元格的引用方式)(3)设置数据系列中分类

之间间距为“0％”，并设置好实线边框颜色帕累托图的制作帕累托图与上面介绍的累计频率与直方图比较类似，但是它的条形图是严格地按照由大到小的顺序排列，它在产品质量管理中经常用到。下面通过一个实例介绍其制作方法。数据表为某公司A品牌电脑2009年3月份在保修期内产

品质量问题的统计分析。请根据这些数据，制作一个帕累托图，以便分析这些质量问题。问题分析：要制作帕累托图，其实只要将上面表格中的各种质量问题的数量按照降序进行排列。根据排列后的数据表，制作上面介绍的累计频率与直方图即可。帕累托图的制作具体操作步骤如下：(1)设置用来制

作帕累托图的数据。其中的主要操作包括：首先将上面的统计表复制一份，放置到图5-72所示的单元格区域位置。其次，对复制后的数据表按照“数量”一列进行降序排列。最后，在后面的“累计百分比”一列计算出各种质量问题的累计百分比数字，其中：第一个单元格D22的公式为：“=SUM(C$2

2：C22)/$C$35”，C35单元格中为计算出的合计数。(2)选中B21：D34单元格区域，按照上面介绍的方法，制作一个累计频率与直方图，其实这时的图表就已经是帕累托图的样式效果。帕累托图的制作(3)在图表上，对分类轴上的标题文字，以及各数据点之

间的边框线进行设置。操作时，在图表分类轴上单击鼠标右键，从快捷菜单中选择“设置坐标轴格式”菜单项，打开“设置坐标轴格式”对话框，在“对齐方式”选项卡中，从“文字方向”列表框中选择“竖排”；在“线型”选项卡中，将边框线的宽度设置为“3磅”，其余按照默认设置。(4)上述设置完成后，图表发生

一定的变化，最终的帕累托图效果如下图所示。帕累托图的制作洛伦茨曲线的制作洛伦茨曲线是20世纪初美国经济统计学家洛伦茨最早确定的用来描述某地区或国家财富分配性质的一种曲线，其实质就是一种带有平滑线的散点图，其横轴是累计人口百分比，纵轴为累计收入百分比。当一个国家或地区的财富完全平均分配时，收入

分配程度的曲线应该与对角线重合，而绝大多数地方都不可能是平均的——而是绝大多数人占有较少的财富，而较少的人占有绝大多数的财富。于是，一般的洛伦茨曲线都靠近下横轴和右纵轴，其越靠近则该地区的财富分配越不平均。洛伦茨曲线的制作数据表为2008年某地区的户数和收入情况。请根据

这些数据，绘制该地区的洛伦茨曲线，以便分析该地区的财富分配平均化程度的情况。问题分析：要制作洛伦茨曲线，只要计算出来累计人口百分比和累计收入百分比，然后根据二者的对应数值，制作带平滑线的散点图即可。(1)设置用来制作洛伦茨曲

线的数据表，并输入原始数据。(2)输入计算公式，计算出来相关数值。其中各个相关单元格中的计算公式都可以由本行最前面或者本列最上面单元格中的公式复制而得到，注意其中相关单元格中引用方式的选择，其中的具体操作方法不再介绍

。洛伦茨曲线的制作洛伦茨曲线的制作(3)选中图中的El：F14单元格区域，制作一个带平滑线的散点图。(4)为图中的图形添加对角线(直接绘制即可)。从下图可以看出，该地区人口的收入差异非常大，洛伦茨曲线距离下面的横轴和右边的纵轴很近，财

富分配的不公平程度很高，其中较低收入的44％户数仅仅占有全部收入的3％多一点儿，而有较高收入的1．5％户数的收入就占有全部收入的接近50％。洛伦茨曲线的制作洛伦茨曲线的制作以上制作洛伦茨曲线的方法，

关键点有三个：(1)必须设法得到一个排序后的户数(降序)和收入(升序)数据表(2)通过公式计算出累计户数百分比和累积收入百分比的数字(3)最后根据累计户数百分比和累积收入百分比的数字，制作XY散点图。帕累托图的制作以上制作帕累托图的方法，关键点有三个

：(1)必须设法得到一个排序后的数据表(2)通过公式计算出累计百分比的数字(3)对于分类轴标签文字要设计得尽量精炼，如果实在无法精炼，就需要制作成竖排的文字效果，切不可做成斜体效果另外，利用Excel“数据分析”加载项中的“直方图”分析工具，也可以快速地制作上面的直方图和累积频率图

，甚至还可以直接制作出帕累托图，具体操作这里不再介绍。