EXCEL培训1数据汇总与图表展示

1、EXCELEXCEL讲座讲座主讲人：罗亚非主讲人：罗亚非北京工业大学经济与管理学院北京工业大学经济与管理学院E-mailE-mail：第一讲第一讲数据数据的组织与整理的组织与整理EXCELEXCEL数据库表格的样式与特点数据库表格的样式与特点数据库是一个数据区域数据库是一个数据区域同一字段的取值类型相同同一字段的取值类型相同数据库中的每一行称为一个记录数据库中的每一行称为一个记录在多数情况下，数据库中还应有一个条件区域在多数情况下，数据库中还应有一个条件区域EXCELEXCEL数据库表格的遵循准则数据库表格的遵循准则一个数据库表格要单独占一个工作表，避免多个数一个数据库表格要单独占一个工作表，

2、避免多个数据库表格放在一个工作表上据库表格放在一个工作表上数据记录紧接在字段名下面，不要使用空白行将字数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开段名和第一条记录数据分开避免在数据库中间放置空白行或空白列，任意两行避免在数据库中间放置空白行或空白列，任意两行的内容不能完全相同的内容不能完全相同重要的固定数据重要的固定数据( (如编号、姓名如编号、姓名) )设置为左侧字段，移设置为左侧字段，移动滚动条时可将其锁定动滚动条时可将其锁定字段名的字体、对齐方式、格式、边框等样式，应字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别当与其他数据的格式相区别条件区

3、域不要放在数据库的数据区域下方条件区域不要放在数据库的数据区域下方将数据库表格转换为将数据库表格转换为EXCELEXCEL表表为了更加容易地管理和分析一组相关数据，可以将为了更加容易地管理和分析一组相关数据，可以将ExcelExcel数据库表格单元格区域转换为数据库表格单元格区域转换为 Excel Excel表表( (这里的这里的ExcelExcel表是一种特指，它是一系列包含相关数据的行表是一种特指，它是一系列包含相关数据的行和列。在和列。在Excel 2007Excel 2007以前的版本，将其称之为以前的版本，将其称之为“Excel“Excel列表列表”) ”)。ExcelExcel表在

4、数据分析中具有非常重要表在数据分析中具有非常重要的用途。的用途。数据库表格转化为数据库表格转化为EXCELEXCEL表的格式表的格式具体操作步骤如下：具体操作步骤如下：将将活动单元格活动单元格放到中数据库表格的任意一个单元格放到中数据库表格的任意一个单元格中中单击单击“ “插入插入” ” “ “表表” ” “ “表表” ”命令按钮，弹出命令按钮，弹出 “ “创建表创建表” ”对话框对话框在在“ “创建表创建表” ”对话框中，系统自动选取单元格所在的连对话框中，系统自动选取单元格所在的连续区域为表数据的来源续区域为表数据的来源单击单击“ “确定确定” ”按钮，数据库表格区域变成了按钮，数据库表格

5、区域变成了ExcelExcel表的表的格式格式EXCELEXCEL表与数据库表格区域比较，提供了很表与数据库表格区域比较，提供了很多附加功能多附加功能：在默认情况下，表中的每一列都在标题行中启用了在默认情况下，表中的每一列都在标题行中启用了筛选功能，以便可以快速筛选表数据或对其进行排筛选功能，以便可以快速筛选表数据或对其进行排序序在表格最后行一行的下面，增加了一个汇总行，方在表格最后行一行的下面，增加了一个汇总行，方便数据汇总便数据汇总ExcelExcel表的各个数据行，设计成了不同颜色底纹的间表的各个数据行，设计成了不同颜色底纹的间隔效果，便于查看行数据隔效果，便于查看行数据使用表的右下角的

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

7、执行“ “打开打开” ”文件的操作，设置打开文件的类型为文件的操作，设置打开文件的类型为“ “文本文文本文件件” ”格式，然后从文本格式的固定资产数据表文件的格式，然后从文本格式的固定资产数据表文件的所在位置找到打开即可。所在位置找到打开即可。具体操作步骤如下：具体操作步骤如下：启动启动Excel2007Excel2007软件软件单击单击“Office“Office按钮按钮” ”一一“ “打开打开” ”命令按钮，弹出命令按钮，弹出“ “打开打开” ”对话框对话框从从“ “查找范围查找范围” ”下拉列表框找到需要打开文本文件所在的文下拉列表框找到需要打开文本文件所在的文件夹，从件夹，从“ “文件

8、类型文件类型” ”下拉列表中选择下拉列表中选择“ “文本文件文本文件” ”，从中可，从中可以看到需要打开的以看到需要打开的“ “固定资产数据表固定资产数据表” ”文件，然后用鼠标单文件，然后用鼠标单击该文本文件的图标击该文本文件的图标启动了启动了“ “文本导入向导文本导入向导” ”，该向导共包括三个步骤，该向导共包括三个步骤导入文本格式的文件导入文本格式的文件步骤步骤1 1固定资产数据文本文件导入举例固定资产数据文本文件导入举例导入文本格式的文件导入文本格式的文件步骤步骤2 2导入文本格式的文件导入文本格式的文件步骤步骤3 3数据的排序数据的排序ExcelExcel中数据排序的基本规则中数据排

9、序的基本规则排序有升序或降序两种方式。以按升序排序为例，排序有升序或降序两种方式。以按升序排序为例，ExcelExcel使用如下排序规则：使用如下排序规则：数字按从最小的负数到最大的正数排序。数字按从最小的负数到最大的正数排序。字母按照英文字母字母按照英文字母A Az z和和a az z的先后顺序排序。的先后顺序排序。在对文本进行排序时，在对文本进行排序时，ExcelExcel从左到右一个字符一个字符地进行从左到右一个字符一个字符地进行排序比较，若两个文本的第一个字符相同，则比较第二个字符排序比较，若两个文本的第一个字符相同，则比较第二个字符，若第二个也相同，则比较第三个，若第二个也相同，则比

10、较第三个一旦比较出大小，则不一旦比较出大小，则不再比较后面的字符。再比较后面的字符。特殊符号以及包含数字的文本，升序按如下排列：特殊符号以及包含数字的文本，升序按如下排列： 09 !”# $ % & () 09 !”# $ % & () * * , . / : ; ? _ | + AZ az , . / : ; ? _ | + AZ az 在逻辑值中，在逻辑值中，FALSE(FALSE(相当于相当于0)0)排在排在TRUE(TRUE(相当于相当于1)1)之前。之前。 所有错误值的优先级等效；空格总是排在最后。所有错误值的优先级等效；空格总是排在最后。 汉字的排序可以按笔画，也可按汉语拼音的字典

11、顺序。汉字的排序可以按笔画，也可按汉语拼音的字典顺序。使用工具按钮对数字单关键字使用工具按钮对数字单关键字排排序序按单个关键字排序就是根据数据表中某一列内容进按单个关键字排序就是根据数据表中某一列内容进行排序，包括行排序，包括“ “升序升序” ”和和“ “降序降序” ”两种方式。实现此功能两种方式。实现此功能的最好方法就是采用工具按钮法。的最好方法就是采用工具按钮法。操作时，只要将光标置于待排序的列中，然后运行操作时，只要将光标置于待排序的列中，然后运行“ “数据数据” ” “ “排序和筛选排序和筛选” ” “ “升序升序” ”按钮或按钮或“ “降序降序” ”按钮即按钮即可。可。利用菜单法对汉

12、字字段按笔画排序利用菜单法对汉字字段按笔画排序ExcelExcel中对数据库中排序的第二种方法就是中对数据库中排序的第二种方法就是“ “菜单法菜单法” ”，操作时，只要运行，操作时，只要运行“ “数据数据” ” “ “排序和筛选排序和筛选” ” “ “排序排序” ”按钮，然后在随之出现的对话框中进行相应选项设按钮，然后在随之出现的对话框中进行相应选项设计即可。计即可。 汉字的排序方式有汉字的排序方式有“ “字母字母” ”序和序和“ “笔画笔画” ”序两种。序两种。“ “字字母母” ”序是按汉字拼音排序；序是按汉字拼音排序；“ “笔画笔画” ”序是按照笔画的多序是按照笔画的多少排序，在升序方式中

13、，笔画少的在前面，笔画多少排序，在升序方式中，笔画少的在前面，笔画多的在后面。的在后面。实现具有多个关键字的复杂排序实现具有多个关键字的复杂排序海达公司某次招聘工作进入总经理筛选阶段的海达公司某次招聘工作进入总经理筛选阶段的1515名名应聘人员的成绩表，现在需要对这应聘人员的成绩表，现在需要对这1313人进行成绩排人进行成绩排序，相关的规则是序，相关的规则是：先按先按“ “总评成绩总评成绩” ”排名相同者再按排名相同者再按“ “面试成绩面试成绩” ”排名排名再按再按“笔试合计笔试合计”排名排名, ,以上排名都是按由大到小排列以上排名都是按由大到小排列如果按照上述成绩最终仍然无法区分，则作为并列

14、名次如果按照上述成绩最终仍然无法区分，则作为并列名次处理，但是二人在名单上的出现顺序，需要按照汉字拼处理，但是二人在名单上的出现顺序，需要按照汉字拼音顺序的升序排列音顺序的升序排列问题分析：本问题包含四个排序关键字段。要达到问题分析：本问题包含四个排序关键字段。要达到以上目的，需进行多关键字排序以上目的，需进行多关键字排序操作步骤如下：操作步骤如下：选择整个数据表所在单元格区域，然后运行选择整个数据表所在单元格区域，然后运行“ “数据数据” ” “ “排排序和筛选序和筛选” ” “ “排序排序” ”按钮命令。在弹出的按钮命令。在弹出的“ “排序排序” ”对话框中对话框中：“ “主要关键字主要关

15、键字” ”选择选择“ “总评成绩总评成绩” ”，“ “排序依据排序依据” ”用默认的用默认的“ “数数值值” ”，“ “次序次序” ”从下拉列表中选择从下拉列表中选择“ “降序降序” ”单击单击“ “添加条件添加条件” ”按钮，设置次要关键字：按钮，设置次要关键字：“ “次要关键字次要关键字” ”选选“ “面试成绩面试成绩” ”，“ “排序依据排序依据” ”用默认用默认“ “数值数值” ”，“ “次序次序” ”从下拉列从下拉列表中选择表中选择“ “降序降序” ” 。再依次单击再依次单击“ “添加条件添加条件” ”按钮，设置其余次要关键字，最终按钮，设置其余次要关键字，最终效果效果单击单击“ “

16、确定确定” ”按钮后，最后得到按照指定关键字排序的结果按钮后，最后得到按照指定关键字排序的结果实例见数据排序实例见数据排序排序函数排序函数RANKRANK的应用的应用在在ExcelExcel中，还有一些与排序相关的函数。其中最为中，还有一些与排序相关的函数。其中最为常用的就是常用的就是RANKRANK函数函数 RANK RANK函数的功能是用来返回一个数值在一组数值函数的功能是用来返回一个数值在一组数值中的排位，其语法格式为：中的排位，其语法格式为： RANK(number, ref RANK(number, ref，order)order) 该函数共包括三个参数，其中：该函数共包括三个参数，

17、其中： number number为需要找到排位的数字为需要找到排位的数字refref为包含一组数字的数组或引用为包含一组数字的数组或引用 order order为一数字，指明排位方式为一数字，指明排位方式(0(0或省略，按降序排位或省略，按降序排位，不为，不为0 0，按升序排位，按升序排位) )。见排位实例见排位实例数据的筛选数据的筛选根据需要，有时要按指定条件从数据库中筛选特定根据需要，有时要按指定条件从数据库中筛选特定记录。记录。ExcelExcel提供三种筛选方法：自动筛选、自定义提供三种筛选方法：自动筛选、自定义筛选和高级筛选。筛选和高级筛选。自动筛选的操作实例自动筛选的操作实例利用

18、自动筛选，从考试报名人员信息表中，筛选出利用自动筛选，从考试报名人员信息表中，筛选出具有博士究生学历，并且是男性的报名人员基本信具有博士究生学历，并且是男性的报名人员基本信息。息。问题分析：本问题属于较简单的条件筛选问题，使问题分析：本问题属于较简单的条件筛选问题，使用自动筛选操作即可。它能将那些合条件的记录显用自动筛选操作即可。它能将那些合条件的记录显示在工作表中，而将不满足条件的记录暂时隐藏。示在工作表中，而将不满足条件的记录暂时隐藏。自动筛选的操作实例自动筛选的操作实例操作步骤如下：操作步骤如下：将光标定位到需要筛选数据表中的任意一个单元格中。将光标定位到需要筛选数据表中的任意一个单元格

19、中。运行运行“ “数据数据” ” “ “排序和筛选排序和筛选” ” “ “筛选筛选” ”命令按钮，此时在命令按钮，此时在数据表的每个字段右侧，均出现了筛选箭头，单击需要数据表的每个字段右侧，均出现了筛选箭头，单击需要设置筛选条件字段的筛选箭头，会弹出一个下拉列表设置筛选条件字段的筛选箭头，会弹出一个下拉列表其中可以用来设置条件格式，设置了性别字段为其中可以用来设置条件格式，设置了性别字段为“ “男男” ”的条的条件。件。单击单击“ “确定确定” ”按钮，性别为按钮，性别为“ “男男” ”的数据记录被筛选出来。在的数据记录被筛选出来。在所筛选出来数据的所筛选出来数据的再对再对“学历学历”字段设置

20、条件字段设置条件“博士博士”单击单击“确定确定 按钮，题目中要求的报名人员中按钮，题目中要求的报名人员中“ “男博士男博士” ”的的基本信息被筛选出来基本信息被筛选出来自定义筛选的操作实例自定义筛选的操作实例利用自定义筛选，从考试报名人员信息表中，筛选利用自定义筛选，从考试报名人员信息表中，筛选出出3030岁以下，最近两年研究生学历岁以下，最近两年研究生学历( (包括硕士、博士包括硕士、博士) )毕业的人员信息。毕业的人员信息。 问题分析：本问题包括年龄、毕业时间、学历共三问题分析：本问题包括年龄、毕业时间、学历共三个筛选条件，它们分别与表格中的出生日期、毕业个筛选条件，它们分别与表格中的出生

21、日期、毕业年份和学历字段有一定关系，相互之间为年份和学历字段有一定关系，相互之间为“ “与与” ”的关系的关系。可以按照先后顺序执行三次不同的自定义筛选。可以按照先后顺序执行三次不同的自定义筛选，下面就采用自定义筛选的方法实现。下面就采用自定义筛选的方法实现。自定义筛选的操作实例自定义筛选的操作实例具体操作步骤如下：具体操作步骤如下：按照先为数据表进行自动筛选。按照先为数据表进行自动筛选。单击单击“ “出生日期出生日期”字段的筛选箭头，从弹出的下拉列表中字段的筛选箭头，从弹出的下拉列表中，依次单击，依次单击“日期筛选日期筛选” “之后之后”菜单命令菜单命令在弹出的对话框中，在相应文本框中输入日

22、期在弹出的对话框中，在相应文本框中输入日期“1979-1-1”“1979-1-1”单击单击“ “确定确定” ”按钮，按钮，3030岁以下的报名人员被筛选出来岁以下的报名人员被筛选出来在在以上以上筛选结果之上，再用自定义筛选的方法依次筛选筛选结果之上，再用自定义筛选的方法依次筛选其他两个条件，设置的两个其他两个条件，设置的两个“ “自定义自动筛选方式自定义自动筛选方式” ”对话框对话框，“ “最近两年毕业最近两年毕业” ”的自定义筛选条件的自定义筛选条件 ，“ “研究生研究生” ”的自定的自定义筛选条件义筛选条件以上所有条件设置完毕后，最后满足的考生被筛选出来以上所有条件设置完毕后，最后满足的考

23、生被筛选出来自动筛选与自定义筛选功能总结自动筛选与自定义筛选功能总结从前面的几个例子可以看到，自动筛选和自定义筛从前面的几个例子可以看到，自动筛选和自定义筛选可以实现以下的筛选操作：选可以实现以下的筛选操作：对某一个字段筛选出符合特定值的记录对某一个字段筛选出符合特定值的记录此时只要单此时只要单击需要筛选字段的筛选箭头，然后从下拉菜单中直接单击需要筛选字段的筛选箭头，然后从下拉菜单中直接单击选中特定值即可。击选中特定值即可。对同一个字段进行逻辑对同一个字段进行逻辑“ “与与” ”和逻辑和逻辑“ “或或” ”运算运算此时只要此时只要单击需要筛选字段的筛选箭头，然后从下拉菜单中选择单击需要筛选字段

24、的筛选箭头，然后从下拉菜单中选择不同的筛选条件，或者不同的筛选条件，或者“ “自定义筛选自定义筛选” ”命令菜单，在弹出的命令菜单，在弹出的“ “自定义自动筛选方式自定义自动筛选方式” ”对话框中进行设置即可。对话框中进行设置即可。在不同字段间进行逻辑在不同字段间进行逻辑“ “与与” ”运算运算进行多次自动筛选或进行多次自动筛选或者自定义筛选即可。者自定义筛选即可。自动筛选与自定义筛选功能总结自动筛选与自定义筛选功能总结对于数值型字段，可以筛选出最大对于数值型字段，可以筛选出最大( (小小) )的若干个的若干个( (若干百若干百分比分比) )记录记录要单击需要筛选数值字段的筛选箭头，从要单击需

25、要筛选数值字段的筛选箭头，从下拉菜单中单击下拉菜单中单击“ “数字筛选数字筛选” ”命令菜单，然后再从出现的子命令菜单，然后再从出现的子菜单中选择菜单中选择“1010个最大的值个最大的值” ”，最后在弹出的，最后在弹出的“ “自动筛选自动筛选前前1010个个” ”对话框中进行设置即可。对话框中进行设置即可。高级筛选操作实例高级筛选操作实例自动筛选无法实现多个字段间的自动筛选无法实现多个字段间的“ “或或” ”运算，并且它将运算，并且它将筛选出来的记录，仍然显示在原来的表格中，而不筛选出来的记录，仍然显示在原来的表格中，而不能使其显示到一个新的地方。要解决上述问题，就能使其显示到一个新的地方。要

26、解决上述问题，就需要高级筛选。需要高级筛选。 如果要进行高级筛选，首先必须在该数据库表格上如果要进行高级筛选，首先必须在该数据库表格上方设置筛选条件区域。方设置筛选条件区域。见数据筛选见数据筛选高级筛选操作实例高级筛选操作实例在公务员考试报名人员信息表中，该市地税局会计在公务员考试报名人员信息表中，该市地税局会计处的一位领导对报考人员中女博士的情况，以及那处的一位领导对报考人员中女博士的情况，以及那些所学专业为些所学专业为“ “会计会计” ”，或者报考职位为，或者报考职位为“ “会计会计” ”的人员的人员比较感兴趣，现在需要帮她筛选出满足上述条件的比较感兴趣，现在需要帮她筛选出满足上述条件的人

27、员记录。人员记录。问题分析：该问题包括问题分析：该问题包括4 4个条件，牵涉个条件，牵涉4 4个字段，并个字段，并且这且这4 4个条件之间并不全是逻辑个条件之间并不全是逻辑“ “与与” ”的关系。因此，的关系。因此，无法使用自动筛选，而只能依靠高级筛选来完成。无法使用自动筛选，而只能依靠高级筛选来完成。高级筛选操作实例高级筛选操作实例 操作步骤如下：操作步骤如下：在在“ “人员报名信息表人员报名信息表” ”表格的上方插入几个空行，根据本例表格的上方插入几个空行，根据本例筛选的实际需要逻辑关系，建立高级筛选的条件区域筛选的实际需要逻辑关系，建立高级筛选的条件区域A1A1：D4D4区域区域单击单击

28、“ “报名人员信息表报名人员信息表” ”数据库表格中的任一单元格数据库表格中的任一单元格单击单击“ “数据数据” ” “ “排序与筛选排序与筛选” ” 高级高级” ”按钮，弹出按钮，弹出“ “高级筛高级筛选选” ”对话框对话框，需要进行需要进行“ “高级筛选高级筛选” ”对话框的设置对话框的设置：在在“ “方式方式” ”选项区中，单击选中选项区中，单击选中“ “将筛选结果复制到其他位置将筛选结果复制到其他位置” ”：在在“ “列表区域列表区域” ”框中，系统自动选取了要筛选数据所在区域框中，系统自动选取了要筛选数据所在区域，这这是因为步骤是因为步骤(2)(2)将光标置于数据库表格中将光标置于数

29、据库表格中在在“ “条件区域条件区域” ”框中，输入设置好的包含筛选条件的区域框中，输入设置好的包含筛选条件的区域“$A$1“$A$1：$D$4”$D$4”。在在“ “复制到复制到” ”文本框中输入时，首先将光标在其中定位，然后用文本框中输入时，首先将光标在其中定位，然后用鼠标在数据表下方指定一个单元格，该单元格将作为放置筛选鼠标在数据表下方指定一个单元格，该单元格将作为放置筛选结果区域的左上角位置，筛选结果将在它的下方和右方排列。结果区域的左上角位置，筛选结果将在它的下方和右方排列。例如：例如：选取了选取了A41A41单元格作为筛选结果的左上角单元格作为筛选结果的左上角单击单击“ “确定确定

30、”按钮，高级筛选结果出现按钮，高级筛选结果出现第二讲第二讲统计数据的透视分析统计数据的透视分析数据透视表数据透视表所谓对统计数据进行透视分析，就是从统计数据库所谓对统计数据进行透视分析，就是从统计数据库表格的特定字段中概括信息，从而表格的特定字段中概括信息，从而可可以方便地从多以方便地从多个角度来查看、分析、汇总、统计数据，并帮助统个角度来查看、分析、汇总、统计数据，并帮助统计人员快速做出决策。计人员快速做出决策。在在ExcelExcel中，实现数据透视分析的工具是数据透视表中，实现数据透视分析的工具是数据透视表和数据透视图。其中，数据透视表用来对大量的统和数据透视图。其中，数据透视表用来对大

31、量的统计数据进行快速汇总和建立交叉列表，本计数据进行快速汇总和建立交叉列表，本讲讲概括性概括性地介绍数据透视表的基本操作地介绍数据透视表的基本操作。统计数据透视分析的作用统计数据透视分析的作用下面首先通过一个实例，来说明对统计数据进行透下面首先通过一个实例，来说明对统计数据进行透视分析的作用与处理结果视分析的作用与处理结果顺达电脑公司顺达电脑公司20092009年年1 1月份的散件销售记录表，现在月份的散件销售记录表，现在到了月底，需要对这些数据汇总整理和统计分析。到了月底，需要对这些数据汇总整理和统计分析。下面分析数据透视分析对其的重要价值。下面分析数据透视分析对其的重要价值。问题分析：为屏

32、幕显示和实例讲解方便，仅选取部问题分析：为屏幕显示和实例讲解方便，仅选取部分数据，实际数据行要非常多。可以看出，该表就分数据，实际数据行要非常多。可以看出，该表就是一个是一个“ “流水账流水账” ”，在平时进行销售数据快速录入时，在平时进行销售数据快速录入时，非常方便。但是，想从该表格中得到汇总数据和统非常方便。但是，想从该表格中得到汇总数据和统计结果，需要利用相关公式，这是相对比较繁琐的计结果，需要利用相关公式，这是相对比较繁琐的。统计数据透视分析的作用统计数据透视分析的作用例如，到了月底，公司经理想获取如下信息，从该例如，到了月底，公司经理想获取如下信息，从该表格中几乎是难于直接获取的。表

33、格中几乎是难于直接获取的。 (1) (1)本月各种不同品名散件的各自销售额是多少本月各种不同品名散件的各自销售额是多少? ?销售额销售额最大的是哪种散件最大的是哪种散件? ?(2)(2)所有业务员中，销售额最高和最低的人员分别是谁所有业务员中，销售额最高和最低的人员分别是谁? ?业业绩相差多少绩相差多少? ?(3)(3)各客户销售额占总销售额的百分比分别是多少各客户销售额占总销售额的百分比分别是多少? ?销售额销售额最多的是哪一家客户最多的是哪一家客户? ?(4)(4)各种不同散件的销售百分比是多少各种不同散件的销售百分比是多少? ?其中各个客户的销其中各个客户的销售额百分比又是多少售额百分比

34、又是多少? ? 以上以上4 4个问题的解决，在个问题的解决，在ExcelExcel中比较快捷的方法中比较快捷的方法就是利用数据透视表来实现。就是利用数据透视表来实现。数据透视表数据透视表1 1业务员|客户对品名汇总数据透视表数据透视表2 2客户客户| |业务员对品名求百分比业务员对品名求百分比数据透视表的创建数据透视表的创建1 1操作步骤如下：操作步骤如下： (1) (1)选取表格中的任一个单元格，或将插入点放在该表的选取表格中的任一个单元格，或将插入点放在该表的任一单元格中。任一单元格中。 (2) (2)单击单击“ “插入插入” ” “ “表表” ” 数据透视表数据透视表” ”按钮右侧的箭头

35、，按钮右侧的箭头，从弹出的下拉列表中，单击从弹出的下拉列表中，单击“ “数据透视表数据透视表”选项，出现选项，出现 “ “创建数据透视表创建数据透视表” ”对话框。在本对话框中，可以设置创建对话框。在本对话框中，可以设置创建数据透视表的数据源和需要将创建的数据透视表放置的数据透视表的数据源和需要将创建的数据透视表放置的位置。位置。(3)(3)单击单击“ “确定确定” ”按钮，系统将自动插入按钮，系统将自动插入“Sheet4”“Sheet4”工作表工作表。 (4) (4) “ “数据透视表字段列表数据透视表字段列表” ”任务窗格中的任务窗格中的“ “选择要添加到选择要添加到报表的字段报表的字段”

36、 ”区域，选择需要添加到数据透视表的字段，区域，选择需要添加到数据透视表的字段，这里选中这里选中“ “销售日期销售日期” ”、“ “客户名称客户名称” ”、“ “业务员业务员” ”、“ “品名品名” ”和和“ “合计金额合计金额” ”复选框，并通过拖动分别将它们放置到复选框，并通过拖动分别将它们放置到“ “报表筛报表筛选选” ”、“ “行标签行标签” ”、“ “列标签列标签” ”和和“ “数值数值” ”区域中区域中 (5) (5)添加了相应字段后，创建的数据透视表如图添加了相应字段后，创建的数据透视表如图2-652-65左边左边所示。在表格的各个项目下面有项目汇总信息，在最右所示。在表格的各个

37、项目下面有项目汇总信息，在最右边和最下边还给出了汇总的销售数据边和最下边还给出了汇总的销售数据 (6) (6)在在透视表中透视表中先后选择了先后选择了“ “业务员业务员” ”和和“ “客户名称客户名称” ”两个字两个字段作为列标签，所以它们进行了分层次的显示，当单击段作为列标签，所以它们进行了分层次的显示，当单击对应业务员名字前面的对应业务员名字前面的“-”“-”符号时，可以将其后面的具体符号时，可以将其后面的具体客户名称折叠隐藏。客户名称折叠隐藏。通过单击所有业务员前面的通过单击所有业务员前面的“”“”符号，将所有客户名称折符号，将所有客户名称折叠隐藏叠隐藏如果再单击如果再单击 “+”“+”

38、符号，还可以再将各业务员对不同客户的符号，还可以再将各业务员对不同客户的销售数据详细地显示出来销售数据详细地显示出来数据透视表的创建数据透视表的创建2 2数据透视表的创建数据透视表的创建3 3创建数据透视表创建数据透视表数据透视表的创建数据透视表的创建4 4创建结果创建数据透视表的注意事项创建数据透视表的注意事项1 1注意数据表格的格式注意数据表格的格式 制作数据透视表的数据表格，需要是数据库格式制作数据透视表的数据表格，需要是数据库格式的表格，否则无法得到相应结果的表格，否则无法得到相应结果2 2创建之前定好光标的位置创建之前定好光标的位置 在制作数据透视表之前，将光标置于数据区域中在制作数

39、据透视表之前，将光标置于数据区域中间某一单元格中，这样系统就能够智能化地选取数间某一单元格中，这样系统就能够智能化地选取数据源所在的整个区域，省去选取操作步骤据源所在的整个区域，省去选取操作步骤创建数据透视表的注意事项创建数据透视表的注意事项3 3注意数据透视表的组成部分注意数据透视表的组成部分 一个完整的数据透视表，是由以下几个不同的区一个完整的数据透视表，是由以下几个不同的区域组成的，要学会合理地进行设置域组成的，要学会合理地进行设置页字段：数据透视表中被指定为页方向的源数据库中的页字段：数据透视表中被指定为页方向的源数据库中的字段，供筛选用。字段，供筛选用。行字段：数据透视表中被指定为行

40、方向的源数据库中的行字段：数据透视表中被指定为行方向的源数据库中的字段，可以有多个。字段，可以有多个。列字段：数据透视表中被指定为列方向的源数据库中的列字段：数据透视表中被指定为列方向的源数据库中的字段，可以有多个。字段，可以有多个。数据字段：含数据的源数据库中的字段项。数据字段：含数据的源数据库中的字段项。创建数据透视表的注意事项创建数据透视表的注意事项 4 4拖动字段的过程是可逆的拖动字段的过程是可逆的 在本例的操作步骤在本例的操作步骤(4)(4)中，需要将相应的字段拖动到不中，需要将相应的字段拖动到不同的位置。其实，这个操作过程是可逆的，可以将拖动同的位置。其实，这个操作过程是可逆的，可

41、以将拖动错误的内容撤销掉错误的内容撤销掉5 5合理拖动字段的位置合理拖动字段的位置 各个字段的拖动位置，与数据分析的目的关系密切，各个字段的拖动位置，与数据分析的目的关系密切，这直接决定了将来制作出来的数据透视表样式。这直接决定了将来制作出来的数据透视表样式。合理拖动字段位置合理拖动字段位置创建数据透视表的注意事项创建数据透视表的注意事项6 6可以对数据作一定的筛选可以对数据作一定的筛选 根据数据透视表，可以进行一定的数据筛选。操作根据数据透视表，可以进行一定的数据筛选。操作时，单击相关字段右侧箭头，从下拉列表选择需要时，单击相关字段右侧箭头，从下拉列表选择需要筛选的内容即可。例如，按照客户名

42、称进行了筛选筛选的内容即可。例如，按照客户名称进行了筛选( (只选硕星公司只选硕星公司) )之后的效果之后的效果7 7可以对分析的结果进行排序可以对分析的结果进行排序 在数据透视表中，可以对相关字段进行排序。例如在数据透视表中，可以对相关字段进行排序。例如对按照业务员姓名的字典顺序，进行升序排列后的对按照业务员姓名的字典顺序，进行升序排列后的显示效果显示效果筛选结果筛选结果排序结果排序结果创建数据透视表的注意事项创建数据透视表的注意事项8 8可以修改数据的汇总方式可以修改数据的汇总方式 在数据透视表中，可以修改数据的汇总方式。操在数据透视表中，可以修改数据的汇总方式。操作时，在单元格作时，在单

43、元格A3(A3(其中包含了其中包含了“ “求和项：合计金额求和项：合计金额” ”内容内容) )上单击鼠标右键，从弹出的快捷菜单中选择上单击鼠标右键，从弹出的快捷菜单中选择“ “数数据汇总依据据汇总依据” ”菜单项，然后从菜单项，然后从“ “数据汇总依据数据汇总依据” ”的子菜的子菜单中，将当前的方式由单中，将当前的方式由“ “求和求和” ”修改为修改为“ “计数计数” ”即可。即可。改变汇总方式的结果改变汇总方式的结果创建数据透视表的注意事项创建数据透视表的注意事项 9 9可以修改数据的显示形式可以修改数据的显示形式 在数据透视表中，可以修改数据的显示形式。操作在数据透视表中，可以修改数据的显

44、示形式。操作方法为：在方法为：在A3A3单元格上单击鼠标右键，从弹出的快单元格上单击鼠标右键，从弹出的快捷菜单中单击捷菜单中单击“ “值字段设置值字段设置” ”菜单项，弹出菜单项，弹出“ “值字段设值字段设置置” ”对话框，选择其中的对话框，选择其中的“ “占总和的百分比占总和的百分比” ”，然后单，然后单击击“ “确定确定” ”按钮，即可得到按按钮，即可得到按“ “占总和百分比占总和百分比” ”方式显示方式显示数据的数据透视表效果。数据的数据透视表效果。创建数据透视表的注意事项创建数据透视表的注意事项改变数据显示形式的结果改变数据显示形式的结果数据分组与频率计算数据分组与频率计算在数据分组与

45、频率计算方面，在数据分组与频率计算方面，ExcelExcel中有个中有个FREQUENCYFREQUENCY函数。它能计算数值在某个区域内的函数。它能计算数值在某个区域内的出现频率，然后返回一个垂直数组。出现频率，然后返回一个垂直数组。FREQUENCYFREQUENCY函数的语法格式为：函数的语法格式为： FREQUENCY(data_array,bins_array) FREQUENCY(data_array,bins_array)参数参数Data_arrayData_array是一个数组或对一组数值的引用，需要是一个数组或对一组数值的引用，需要为它计算频率。如果为它计算频率。如果data

46、 _array data _array 中不包含任何数值，中不包含任何数值，函数函数FREQUENCYFREQUENCY将返回一个零数组。将返回一个零数组。 参数参数Bins_arrayBins_array是一个区间数组或对区间的引用，该区是一个区间数组或对区间的引用，该区间用于对间用于对data_arraydata_array中的数值进行分组。中的数值进行分组。Bins_arrayBins_array中中不包含任何数值，函数不包含任何数值，函数FREQUENCYFREQUENCY返回的值与返回的值与data_arraydata_array中的元素个数相等。中的元素个数相等。统计数据分组与频率

47、计算统计数据分组与频率计算某公司某公司3 3月份销售人员的业绩提成金额表月份销售人员的业绩提成金额表问题分析：对于这个问题，重点讲解问题分析：对于这个问题，重点讲解FREQUENCYFREQUENCY函函数的使用及其注意事项数的使用及其注意事项本例的具体操作步骤如下：本例的具体操作步骤如下： (1) (1) 先建立数据表，其中的先建立数据表，其中的D3D3：D27D27是下面应用是下面应用 FREQUENCYFREQUENCY函数的第一个参数函数的第一个参数需要进行分组统计个需要进行分组统计个数的数据区域。数的数据区域。 (2) (2)建立建立F2F2：H8H8区域内的表格框架，其中区域内的表

48、格框架，其中F3F3：F8F8部分为输部分为输入的描述性文字，起到一定的注释作用入的描述性文字，起到一定的注释作用，与，与FREQUENCYFREQUENCY函数的应用没有直接关系；函数的应用没有直接关系；G4G4：G8G8区域用来准备作为区域用来准备作为 FREQUENCYFREQUENCY函数的第函数的第2 2个参数个参数区间数组，它是根据区间数组，它是根据F3F3：F8F8部分的文字说明来进行划分的；部分的文字说明来进行划分的；H3H3：H8H8部分是作为将部分是作为将来放置来放置FREQUENCYFREQUENCY函数返回结果的单元格区域。函数返回结果的单元格区域。统计数据分组与频率计

49、算统计数据分组与频率计算(3)(3)选定选定H2H2：H8H8单元格区域，输入公式单元格区域，输入公式“=FREQUENCY(D3“=FREQUENCY(D3：D27,G4D27,G4：G8)”G8)”，然后按下，然后按下(Ctrl+Shift+Enter)(Ctrl+Shift+Enter)组合键，作为数组公式确认输入。得组合键，作为数组公式确认输入。得到各个区间的人数。到各个区间的人数。(4)(4)选取选取F3F3：F8F8以及以及H3H3：H8H8单元格区域，制作一个饼图单元格区域，制作一个饼图，并进行一定的格式设置，并进行一定的格式设置 经过以上操作之后，题目所要求的销售提成个数经过以

50、上操作之后，题目所要求的销售提成个数统计以及饼图就制作完毕统计以及饼图就制作完毕统计数据分组与频率计算统计数据分组与频率计算统计数据分组与频率计算统计数据分组与频率计算说说 明：明：(1)FREQUENCY(1)FREQUENCY必须以数组公式输入，这是由于该必须以数组公式输入，这是由于该函数的返回结果就是一个数组。函数的返回结果就是一个数组。(2)(2)一般选择区间数组旁的单元格区域作为返回结果一般选择区间数组旁的单元格区域作为返回结果的位置，但是返回数组中的元素个数要比区间数组的位置，但是返回数组中的元素个数要比区间数组元素个数多元素个数多1 1个，多这个，多这1 1个表示最上面区间之上的

51、数个表示最上面区间之上的数值个数。值个数。第三讲第三讲 数据汇总统计数据汇总统计指定条件的汇总统计指定条件的汇总统计单击单击“ “开始开始” ” “ “编辑编辑” ” “ “自动求和自动求和” ”按钮，调用按钮，调用SUMSUM函数，可以快速实现数据的无条件求和汇总。但是函数，可以快速实现数据的无条件求和汇总。但是，在实际工作中，经常还需要进行条件汇总。本，在实际工作中，经常还需要进行条件汇总。本讲讲就通过实例，介绍通过就通过实例，介绍通过SUMSUM函数与函数与IFIF函数组合，以函数组合，以及利用各种及利用各种SUMSUM类函数进行指定条件的汇总统计。类函数进行指定条件的汇总统计。利用利用

52、SUMIF函数实现单条件汇总函数实现单条件汇总SUMIFSUMIF函数的功能是根据指定条件对若干单元格求函数的功能是根据指定条件对若干单元格求和，其语法格式如下：和，其语法格式如下： SUMIF( SUMIF(条件判断区域，条件，求和区域条件判断区域，条件，求和区域) ) 该函数需要三个参数，其中的该函数需要三个参数，其中的“ “条件条件” ”在形式上可以在形式上可以是数字、表达式或者文本，例如条件可以表达为：是数字、表达式或者文本，例如条件可以表达为：100100，“8000”“8000”，“ “笔记本电脑笔记本电脑”( ”(注意其中的引号注意其中的引号，需需要是英文引号要是英文引号) )等

53、。等。利用利用SUMIF函数实现单条件汇总函数实现单条件汇总举例举例某家电销售公司某家电销售公司20092009年年2 2月份前月份前2020天的产品销售记录天的产品销售记录流水账。而流水账。而H1H1单元格中的数值，是根据单元格中的数值，是根据SUMIFSUMIF函数函数汇总出的洗衣机总销售额，汇总出的洗衣机总销售额，H6H6：H10H10单元格区域是单元格区域是根据根据SUMIFSUMIF函数汇总出的每个业务员的个人总销售函数汇总出的每个业务员的个人总销售额。额。 问题分析：本例的汇总属于单条件汇总，用问题分析：本例的汇总属于单条件汇总，用SUMIFSUMIF函数可完成任务。函数可完成任务

54、。利用利用SUMIF函数实现单条件汇总函数实现单条件汇总举例举例利用利用SUMIF函数实现单条件汇总函数实现单条件汇总举例举例操作步骤如下：操作步骤如下：(1)(1)按照设计表格，并输入相关数据和文字按照设计表格，并输入相关数据和文字(2)(2)在在H2H2单元格中输入如下公式，求洗衣机总销售额单元格中输入如下公式，求洗衣机总销售额 =SUMIF(D2 =SUMIF(D2：D21,D21,洗衣机洗衣机,E2,E2：E21)E21)(3)(3)在在H6H6单元格中输入如下公式，求出单元格中输入如下公式，求出“ “张姗姗张姗姗” ”的总销售的总销售额。额。=SUMIF($C$2=SUMIF($C$

55、2：$C$21,G6,$E$2$C$21,G6,$E$2：$E$21)$E$21)(4)(4)选定选定H6H6单元格，向下一直拖动到单元格，向下一直拖动到H10H10单元格，求出所单元格，求出所有人员的总销售额。有人员的总销售额。利用利用SUM和和IF函数联合实现多条件汇总函数联合实现多条件汇总某电脑公司的销售记录清单，现在假如需要统计汇某电脑公司的销售记录清单，现在假如需要统计汇总：总：(1)2009(1)2009年年1 1月中旬的销售额；月中旬的销售额；(2)2009(2)2009年年1 1月上旬或月上旬或2 2月份以后的销售额。月份以后的销售额。问题分析：对于以上两种数据的统计汇总，都包

56、含问题分析：对于以上两种数据的统计汇总，都包含两个条件，所以单纯应用两个条件，所以单纯应用SUMIFSUMIF函数已经无法解决函数已经无法解决，下面通过使用，下面通过使用SUMSUM和和IFIF函数的组合来解决这个问函数的组合来解决这个问题。题。利用利用SUM和和IF函数联合实现多条件汇总函数联合实现多条件汇总 操作步骤如下：操作步骤如下：(1)(1) 设计表格，并输入相关数据和文字。设计表格，并输入相关数据和文字。(2)(2)将光标定位到将光标定位到E20E20单元格，输入如下公式：单元格，输入如下公式：=SUM(IF(B2=SUM(IF(B2：B17=DATEVALUE(2009-1-B1

57、7=DATEVALUE(2009-1-11)11)* *(B2(B2：B17=DATEVALUE(2009-1-20),E2B17=DATEVALUE(2009-1-20),E2：E17)E17)(3)(3)按下按下(ctrl+Shift+Enter)(ctrl+Shift+Enter)组合键，构造数组公式，求出组合键，构造数组公式，求出20092009年年1 1月中旬销售额。月中旬销售额。(4)(4)将光标定位到将光标定位到E21E21单元格，输入如下公式，单元格，输入如下公式，=SUM(IF(B2=SUM(IF(B2：B17=DATEVALUE(2009-1-B17=DATEVALUE(2

58、009-2-1),E2B17=DATEVALUE(2009-2-1),E2：E17)E17)(5)(5)按下按下(Ctrl+Shift+Enter)(Ctrl+Shift+Enter)组合键，构造数组公式，求组合键，构造数组公式，求出出20092009年年1 1月上旬，或月上旬，或2 2月份以后的销售额。月份以后的销售额。利用利用SUMSUM和和IFIF函数联合实现多条件汇总函数联合实现多条件汇总说明：说明：（1 1）这里的两个公式中，）这里的两个公式中，IFIF函数都包括了两个参数，他函数都包括了两个参数，他们之间用们之间用 “ “” ” 连接的表示二者之间是连接的表示二者之间是“ “与与”

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

60、号数字。(3)(3)使用使用SUMSUM和和IFIF函数组合进行多条件求和时，必须按照函数组合进行多条件求和时，必须按照数组公式输入。所以，在公式输完后，不要忘记按下数组公式输入。所以，在公式输完后，不要忘记按下(Ctrl+Shift+Enter(Ctrl+Shift+Enter组合键，否则将返回错误值组合键，否则将返回错误值“#VALUE“#VALUE。利用利用SUM和和IF函数联合实现多条件汇总函数联合实现多条件汇总利用利用SUMPRODUCT函数实现多条件汇总函数实现多条件汇总SUMPRODUCTSUMPRODUCT函数的功能是计算几个数组之间对函数的功能是计算几个数组之间对应元素乘积之