《Excel篇第二节内容.ppt》由会员分享，可在线阅读，更多相关《Excel篇第二节内容.ppt（55页珍藏版）》请在金锄头文库上搜索。
1、课程名称：办公软件应用课程名称：办公软件应用 主讲教师：杨立影 所在单位：信息技术系 Email：本课程主要内容本课程主要内容 Word 篇篇 Excel 篇篇 PPT 篇篇 1. 1.2 Excel2 Excel数据处理数据处理u1.2.3 1.2.3 数据分类汇总数据分类汇总&在处理含有大量数据的工作表时，往在处理含有大量数据的工作表时，往往需要对特定的数据进行汇总计算。往需要对特定的数据进行汇总计算。ExcelExcel的分类汇总功能可以用来协助的分类汇总功能可以用来协助进行某列数据的求和、平均值、最大进行某列数据的求和、平均值、最大值、最小值等运算。值、最小值等运算。3 1. 1.2
2、Excel2 Excel数据处理数据处理u1.2.3 1.2.3 数据分类汇总数据分类汇总&进行分类汇总前，进行分类汇总前，必须保证数据表的必须保证数据表的各列有列标题各列有列标题，并且同一列中应该包，并且同一列中应该包含相似的数据，同时在含相似的数据，同时在数据区域中没数据区域中没有空行或者空列有空行或者空列。&要进行分类汇总的数据列已经排好序，要进行分类汇总的数据列已经排好序，这个排序的列标题称为分类汇总关键这个排序的列标题称为分类汇总关键字。字。4 1. 1.2 Excel2 Excel数据处理数据处理u1.2.3 1.2.3 数据分类汇总数据分类汇总&例题：在某公司的员工基本信例题：在
3、某公司的员工基本信息表中，为了方便统计各个部息表中，为了方便统计各个部门的员工基本收入情况，现需门的员工基本收入情况，现需要对该表进行分类汇总。具体要对该表进行分类汇总。具体要求如下：要求如下：5 1. 1.2 Excel2 Excel数据处理数据处理u1.2.3 1.2.3 数据分类汇总数据分类汇总&具体要求如下：具体要求如下：统计各个部门员工的平均年龄统计各个部门员工的平均年龄统计各个部门员工的平均工资统计各个部门员工的平均工资统计各个部门男、女员工奖金的统计各个部门男、女员工奖金的最大值最大值将分类汇总结果分页打印出来将分类汇总结果分页打印出来6 1. 1.2 Excel2 Excel数
4、据处理数据处理u1.2.3 1.2.3 数据分类汇总数据分类汇总&解析：解析：&从题目的要求来看，从题目的要求来看，1 1和和2 2属于简属于简单的分类汇总，可以放在一起进单的分类汇总，可以放在一起进行操作；要求行操作；要求3 3属于嵌套分类汇总，属于嵌套分类汇总，有两个层次，第一层是有两个层次，第一层是“部门部门”，第二层是，第二层是“性别性别”；要求；要求4 4属于属于分类汇总的显示。分类汇总的显示。7 1. 1.2 Excel2 Excel数据处理数据处理u1.2.3 1.2.3 数据分类汇总数据分类汇总&提示：提示：&在进行分类汇总的时候，在进行分类汇总的时候，分类字分类字段必须是已经
5、排好序的字段段必须是已经排好序的字段，否，否则最后的结果是不正确的。则最后的结果是不正确的。&汇总方式必须与汇总项匹配汇总方式必须与汇总项匹配，如，如对年龄和工资可以求平均值、最对年龄和工资可以求平均值、最大值等，而对部门则不能。大值等，而对部门则不能。8 1. 1.2 Excel2 Excel数据处理数据处理u1.2.4 1.2.4 条件格式条件格式u例题：在例例题：在例1.2.41.2.4表中记录了某一家表中记录了某一家庭一年的经济支出状况，为了更清楚庭一年的经济支出状况，为了更清楚的显示该家庭本年度的支出情况，请的显示该家庭本年度的支出情况，请对超过对超过30003000元的支出标注红色
6、背景，元的支出标注红色背景，1000100030003000元之间的标准蓝色背景，元之间的标准蓝色背景，低于低于10001000元的支出标注绿色背景。元的支出标注绿色背景。9 1. 1. Excel Excel安全设置与数据保护安全设置与数据保护u对工作表的保护对工作表的保护u对工作簿的保护对工作簿的保护u对单元格的保护对单元格的保护10 1. 1. Excel Excel安全设置与数据保护安全设置与数据保护u对工作表的保护对工作表的保护u方法如下：方法如下：u选择选择“工具工具”“保护保护”“保保护工作表护工作表”命令；命令；u输入密码并确认，确定即可。输入密码并确认，确定即可。11 1.
7、1. Excel Excel安全设置与数据保护安全设置与数据保护u对工作簿的保护对工作簿的保护u方法一：通过方法一：通过“工具工具”“保护保护”“保护工作簿保护工作簿”命令；命令；u方法二：通过方法二：通过“工具工具”“选项选项”“安全性安全性”选项卡设置。选项卡设置。12 1. 1. Excel Excel安全设置与数据保护安全设置与数据保护u对单元格的保护对单元格的保护u例题：对电信公司用户基本信息例题：对电信公司用户基本信息表进行保护，具体要求如下：表进行保护，具体要求如下：u整个工作表中，只有整个工作表中，只有1 1、2 2、3 3月话月话费部分可以修改，其他部分不允费部分可以修改，其
8、他部分不允许修改；许修改；u分别对工作表和工作簿进行保护分别对工作表和工作簿进行保护13 1. 1. Excel Excel安全设置与数据保护安全设置与数据保护u对单元格的保护对单元格的保护u提示：提示：u单元格的保护设置有单元格的保护设置有“锁定锁定”和和“隐藏隐藏”两个选项；两个选项；u对单元格的保护必须要在整个工对单元格的保护必须要在整个工作表被保护的前提下才能完全实作表被保护的前提下才能完全实现其功能。现其功能。14 1. 1. Excel Excel安全设置与数据保护安全设置与数据保护u对单元格的保护对单元格的保护u操作步骤：操作步骤：1)1)在单元格格式对话框中取消在单元格格式对话
9、框中取消“锁锁定定”复选框；复选框；2)2)单击单击“工具工具”“保护保护”“允允许用户编辑区域许用户编辑区域”，新建一个区，新建一个区域，应用并保护工作表。域，应用并保护工作表。15 1. 1.4 Excel4 Excel发展特征发展特征u随着人们对日常数据处理工作提出了随着人们对日常数据处理工作提出了更高的要求更高的要求,Excel,Excel也呈现出新的发展也呈现出新的发展特征。特征。XMLXML作为一种常见的数据存储格作为一种常见的数据存储格式，可以实现各种不同格式数据之间式，可以实现各种不同格式数据之间的平滑连接。本节主要介绍的平滑连接。本节主要介绍XMLXML数据结数据结构在构在E
10、xcelExcel中的应用。中的应用。16 1. 1.4 Excel4 Excel发展特征发展特征u1.4.1 XML1.4.1 XML数据结构数据结构uXMLXML是是Extensible Markup Language的缩写，以为可扩展标记的缩写，以为可扩展标记语言。语言。XMLXML是由是由W3CW3C设计的用来自动设计的用来自动描述数据信息的一种新的标准语言。描述数据信息的一种新的标准语言。17 1. 1.4 Excel4 Excel发展特征发展特征u1.4.1 XML1.4.1 XML数据结构数据结构u与我们所熟悉的与我们所熟悉的HTMLHTML相比，相比，XMLXML没没有固定的标
11、记，也不能描述网页具有固定的标记，也不能描述网页具体的外观，它只是描述内容的数据体的外观，它只是描述内容的数据形式和结构形式和结构。18 1. 1.4 Excel4 Excel发展特征发展特征u1.4.1 XML1.4.1 XML数据结构数据结构uXMLXML数据主要由数据主要由5 5个部分组成：个部分组成：uXMLXML文件头说明部分文件头说明部分u文档类型定义部分文档类型定义部分uXMLXML文件中的元素文件中的元素uXMLXML文件中的属性文件中的属性u元素和属性的取值元素和属性的取值19 1. 1.4 Excel4 Excel发展特征发展特征u1.4.1 XML1.4.1 XML数据结
12、构数据结构uXMLXML主要有以下的特点：主要有以下的特点：u半结构化半结构化u自描述性自描述性u可扩展性可扩展性u灵活性灵活性20 1. 1.4 Excel4 Excel发展特征发展特征u1.4.2 XML1.4.2 XML在在ExcelExcel中的运用中的运用uXMLXML作为一种常见的数据存储格式，作为一种常见的数据存储格式，可以实现各种不同格式数据之间的可以实现各种不同格式数据之间的平滑的连接。在平滑的连接。在ExcelExcel中可以通过中可以通过XMLXML实现不同的实现不同的ExcelExcel文件之间的连文件之间的连接，以及接，以及ExcelExcel与其他格式数据文与其他格
13、式数据文件之间的连接。件之间的连接。21 1. 1.4 Excel4 Excel发展特征发展特征u1.4.2 XML1.4.2 XML在在ExcelExcel中的运用中的运用u例题：夜莺软件公司将其员工信例题：夜莺软件公司将其员工信息保存在息保存在AccessAccess数据库中，现在要数据库中，现在要求每个月打印员工工资表，请从数求每个月打印员工工资表，请从数据库中自动调出员工基本信息表，据库中自动调出员工基本信息表，保存为保存为ExcelExcel格式，并且数据库中格式，并且数据库中的信息修改能够自动反映在的信息修改能够自动反映在ExcelExcel数据表中。数据表中。22 1. 1.4
14、Excel4 Excel发展特征发展特征u1.4.2 XML1.4.2 XML在在ExcelExcel中的运用中的运用u操作步骤如下：操作步骤如下：1)将将Access数据库文件导出为数据库文件导出为XML文件文件 选择选择“文件文件”“导出导出”命令；命令；2)将将XML文件导入到文件导入到Excel中中 选择选择“数据数据”“XML”“导入导入”命命令；令；23n2.1 函数基础知识函数基础知识n2.2 函数的应用函数的应用 第节第节 ExcelExcel高级函数运用高级函数运用24 2.1 2.1 函数基础知识函数基础知识uExcelExcel中函数分为内置函数和扩展中函数分为内置函数和
15、扩展函数两大类。只要启动了函数两大类。只要启动了Excel，用户就可以使用内置函数。而对用户就可以使用内置函数。而对于扩展函数，需要通过于扩展函数，需要通过“工具工具”“加载宏加载宏”菜单命令加载，然菜单命令加载，然后才能使用。后才能使用。25 2.1 2.1 函数基础知识函数基础知识u函数的结构：函数的结构：u以函数名开始，后面是左圆括号、以函数名开始，后面是左圆括号、以逗号分隔的参数和右圆括号。以逗号分隔的参数和右圆括号。如下图所示：如下图所示：26 2.1 2.1 函数基础知识函数基础知识u嵌套函数：嵌套函数：u将某一函数作为另一函数的参数将某一函数作为另一函数的参数使用时，称为嵌套函数
16、。如下所使用时，称为嵌套函数。如下所示：示：=IF(SUM(E3:G15)500,MAX(E3:G15),0)27 2.1 2.1 函数基础知识函数基础知识u嵌套函数：嵌套函数：u对于嵌套函数的使用，必须注意对于嵌套函数的使用，必须注意以下两个问题：以下两个问题：u当嵌套的函数作为参数使用时，当嵌套的函数作为参数使用时，返回的数值类型必须与参数的类返回的数值类型必须与参数的类型一致。型一致。u嵌套函数级数的限制。嵌套函数级数的限制。28 2.1 2.1 函数基础知识函数基础知识u函数的种类：函数的种类：uExcelExcel函数一共有函数一共有1111类，分别为：类，分别为：u数据库函数、日期
17、与时间函数数据库函数、日期与时间函数u工程函数、财务函数工程函数、财务函数u信息函数、逻辑函数信息函数、逻辑函数u查询和引用函数、统计函数查询和引用函数、统计函数u数学和三角函数、文本函数数学和三角函数、文本函数u自定义函数自定义函数29 2.2 2.2 函数的应用函数的应用u日期与时间函数日期与时间函数1.DATE 用途：返回代表特定日期的序列号用法：DATE(year,month,day)例子：公式“=DATE(2006,12,30)” 返回2006-12-30.30 2.2 2.2 函数的应用函数的应用u日期与时间函数日期与时间函数2.DATEVALUE 用途：返回date_text所表
18、示的日期的 序列号。用法：DATEVALUE(date_text)例子：公式“=DATEVALUE(“2006/12/30”)” 返回39081.31 2.2 2.2 函数的应用函数的应用u日期与时间函数日期与时间函数3.NOW 用途：返回当前日期和时间。语法：NOW()4.TODAY用途：返回系统当前日期语法：TODAY()32 2.2 2.2 函数的应用函数的应用u文本函数的应用文本函数的应用&所谓文本函数，就是可以在公式中处理文字串的函数。例如，可以改变大小写或确定文字串的长度，可以替换某些字符或去掉某些字符等。33 2.2 2.2 函数的应用函数的应用u文本函数的应用文本函数的应用1.
19、LOWER 用途:将一个文字串中的所有大写字母 转换为小写字母。用法：LOWER(text)例子：若A1=“Excel”，则使用下面的公式“=lower(A1)”后返回“excel”.34 2.2 2.2 函数的应用函数的应用u文本函数的应用文本函数的应用2.UPPER 用途:将文本转换为大写形式。用法：UPPER(text)例子：若A1=“Excel”，则使用公式“=upper(A1)”后返回“EXCEL”.35 2.2 2.2 函数的应用函数的应用u文本函数的应用文本函数的应用3.LEN或LENB 用途:LEN返回文本串的字符数。LENB返回文本串中所有字符的字节数。用法：LEN(text
20、)或LENB(text)例子：若A1=“统计分析人员”，则使用公式“=len(A1)”后返回6，使用公式“=lenb(A1)”后返回12。36 2.2 2.2 函数的应用函数的应用u文本函数的应用文本函数的应用4.SUBSTITUTE用途:在文字串中用new_text代替old_text。用法：SUBSTITUTE(new_text,old_text,num)37 2.2 2.2 函数的应用函数的应用u文本函数的应用文本函数的应用5.CONCATENATE用途:将若干文字串合并到一个文字串中，其功能与“&”运算符相同。用法：CONCATENATE(text1, text2,.)例如：A1=“e
21、xcel”，A2=“和统计”，则公式“=concatenate(A1,A2)”返回“excel和统计”。38 2.2 2.2 函数的应用函数的应用u查询引用函数的应用查询引用函数的应用&查询引用函数是Excel中另一类常会用到的函数，尤其是在制作动态的Excel图表过程中，这类函数非常好用。在介绍查询引用函数前，先来了解一下引用的知识。39 2.2 2.2 函数的应用函数的应用u查询引用函数的应用查询引用函数的应用&引用的作用&在Excel中引用的作用在于标识工作表上的单元格区域，并指明公式中所使用的数据的位置。40 2.2 2.2 函数的应用函数的应用u查询引用函数的应用查询引用函数的应用&
22、引用的含义&外部引用：不同工作簿中的单元格的引用称为外部引用。&远程引用：引用其他程序中的数据称为远程引用。41 2.2 2.2 函数的应用函数的应用u查询引用函数的应用查询引用函数的应用&引用的含义&相对引用：在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。&绝对引用：如果在复制公式时不希望Excel调整引用，则使用绝对引用，即加入美元符号，如$C$1。42 2.2 2.2 函数的应用函数的应用u查询引用函数的应用查询引用函数的应用&引用的表示方法 &A1和R1C1两种引用样式&A1(默认)：引用字母标志列和数字标志行。对单元格的引用如“C24”，对区域的引用如
23、“C3:F9”。43 2.2 2.2 函数的应用函数的应用u查询引用函数的应用查询引用函数的应用&引用的表示方法 &R1C1引用样式：使用R+行数字和C+列数字来指示单元格的位置。&例如，单元格绝对引用R1C1与A1样式中的绝对引用$A$1等价。如果活动单元格是A1，则单元格相对引用R1C1将引用下面一行和右面一列的单元格44 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&1.ADDRESS函数 &用途：用来按照给定的行号和列号建立一个文本类型的单元格地址。&语法：address(row_num,column_num,abs_num,a1,sheet_text)45
24、2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&1.ADDRESS函数 &参数：row_num表示在单元格中引用的行号,column_num表示引用的列号,abs_num指明返回的引用类型：1代表绝对引用，2代表绝对行号相对列号，3代表相对行号绝对列号，4为相对引用。A1为true，返回A1样式的引用，a1为false返回R1C1样式的引用。sheet_text为文本，指明作为外部引用的工作表的名称。46 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&1.ADDRESS函数 &例子47 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询
25、引用函数介绍&2.AREAS函数&用途：用于返回引用中包含区域的个数，其中区域表示连续的单元格组或者某个单元格。&语法：areas(reference)&参数：reference表示对某个单元格或者单元格区域的引用。48 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&2.AREAS函数&例子49 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&3.CHOOSE函数&用途：被用于从给定的参数中返回指定的值。&语法：choose(index_num,value1,value2,)&参数：reference表示对某个单元格或者单元格区域的引用。50
26、2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&3.CHOOSE函数&参数：index_num用来指明待选参数的序号，必须是129之间的数字或者是包含数字129的公式或单元格引用。如果index_num为1，则返回value1，以此类推。51 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&3.CHOOSE函数&例1：52 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&3.CHOOSE函数&例2：53 2.2 2.2 函数的应用函数的应用u查询引用函数介绍查询引用函数介绍&3.CHOOSE函数&例2：54 上机练习上机练习n练习使用练习使用Excel中的函数中的函数n制作一个电子万年历制作一个电子万年历55
