Excel篇第二节内容.ppt

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