1、1,第三章 常用函数,Excel 2007 数据分析 及其应用,2,第三章 常用函数,本章内容：介绍Excel 2007函数的基本概念及常用函数的使用方法，并举例加以说明。常用函数包括：数学和三角函数、逻辑函数、日期和时间函数、文本函数、查找和引用函数 教学目标：通过本章的学习，使同学们对Excel 2007的函数有个大致的了解，能够使用函数解决一些实际问题,3,第三章 常用函数,函数的概述 数学和三角函数 逻辑函数 日期和时间函数 文本函数查找和引用函数,第1节 函数的概述,函数的基本概念 函数的分类 使用函数 使用函数的优势 获得Excel 函数的帮助,预先定义，执行计算、分析等处理数据任
2、务的特殊公式,分为12类、356个函数,直接使用、利用“插入函数”对话框,简化公式、实现特殊运算、实现智能判断、提高工作效率,4,单元格内的工具提示使用“函数参数”对话框使用Excel帮助,如果函数以公式的形式出现，请在函数名称前面键入等号 =,函数的结构 SUM(A10,B5:B10,50,37),5,1.1 函数的基本概念,函数名,参数，可以是常量、逻辑值、数组、错误值或单元格引用等,Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式,函数与公式的区别 函数与公式既有区别又互相联系。函数是Excel预先定义好的特殊公式，而公式由用户自行设计对工作表进行计算和处
3、理的计算式 如：“=SUM(E1:H1)*A1+26”,6,1.1 函数的基本概念,Excel 2007的函数是指预先定义好，执行计算、分析等处理数据任务的特殊公式,函数,89个ATP(分析工具包)函数，这些函数现在是Excel默认安装的一部分5个新的通用函数7个新的多维数据集函数，这些函数有助人们连接多维数据库,7,1.2 函数的分类,Excel 2007提供了356个函数。它还包含下面的内容,8,1.2 函数的分类,Excel 2007提供了的函数。可分为12类：,直接使用函数 在单元格中直接使用函数，是以“=”开始，然后输入函数名及函数所需要的参数，函数的参数用括号括起来，各参数之间用逗
4、号分隔 例如：现有我国不同地区的西红柿价格数据，如果计算天津地区的西红柿平均价格,9,1.3 使用函数,使用AVERAGE (number1, number2,.)函数,10,1.3 使用函数,在要得到结果的单元格内直接输入“=AVERAGE(B3:B7)”,计算所有地区的西红柿平均价格,11,1.3 使用函数,利用“插入函数”对话框使用函数,12,1.3 使用函数,选中要显示结果的单元格,在“公式”选项卡“函数库”组，单击“插入函数”,利用“插入函数”对话框使用函数,13,1.3 使用函数,在“或选择类别”下拉列表中选择要使用函数所在的类别，这里选择“常用函数”，然后再“选择函数”列表中找到
5、求平均值的函数AVERAGE,利用“插入函数”对话框使用函数,14,1.3 使用函数,简化公式 函数可以使一些复杂的公式更易于使用，使复杂的数学表达式输入更加简化。比如，要计算B3:D7区域中数据的平均值时，使用公式计算量大，需要写成：“=(B3+B4+B5+B6+B7+ C3+C4+C5+C6+C7+D3+D4+D5+D6+D7)/15”而使用函数将相当简单，只要写成：“=AVERAGE(B3:D7)”,15,1.4 使用函数的优势,实现特殊运算 在日常的工作中，有一些计算使用公式不但复杂，而且不具有特定的专业知识还无法表达，那么就可以使用现有的函数进行特殊运算 例如，按揭贷款的等额分期偿还
6、额的计算必须用到高等数学的知识，而使用PMT函数(计算在固定利率下，贷款的等额分期偿还额)就相当简单 PMT(rate,nper,pv,fv,type),16,1.4 使用函数的优势,贷款利率,付款期总数,未来值,现值,付款时间,实现特殊运算,17,1.4 使用函数的优势,贷款利率,付款期总数,现值,实现智能判断 用函数还可以实现智能判断 例如，在学校，老师统计学生的考试成绩，如果，其成绩大于等于80则为“优秀”,如果成绩大于等于59且小于80则为“及格”,否则为“不及格”。这类问题用人工计算很不方便，可以使用函数来进行自动化处理,18,1.4 使用函数的优势,实现智能判断,19,1.4 使用
7、函数的优势,提高工作效率 在学校的期末总结中，常会需要按学生的考试成绩进行排位的问题，当然可以使用“排序”按钮来完成，但是这样做比较麻烦，如果使用排位函数RANK就比较方便，尤其是纪录很多、相同分数很多的情况下，不用RANK函数其工作量不可想像 RANK(number,ref,order),20,1.4 使用函数的优势,需要排位的数字,引用的区域，绝对地址,排位的方式,提高工作效率,21,1.4 使用函数的优势,由于Excel 2007的函数众多，功能很强，在课中无法全面的介绍这些函数，所以我们要使用Excel 的帮助文件。Excel 2007 提供了很好的帮助文件。Excel有三个级别的帮助
8、：单元格内的工具提示、“函数参数”对话框和Excel的帮助。函数参数对话框是寻找帮助的最好办法。使用单元格内的工具提示 在任何单元格内，都可以输入等号、函数名称和左括号。Excel的工具提示将显示预期的参数,22,1.5 获得Excel 函数的帮助,使用“函数参数”对话框 “函数参数”对话框可以逐步帮助你建立函数,23,1.5 获得Excel 函数的帮助,每个独立参数的结果,当前参数的介绍,函数的描述,要求的参数,函数的初步结果,帮助的链接,使用Excel帮助 有关函数的帮助非常全面。在每个函数的帮助主题中都可以找到下面的内容 函数的语法 “注释”提供了函数的疑难解答 实例 相关函数,24,1
9、.5 获得Excel 函数的帮助,数学函数 基本数学函数 舍人和取整函数 求和函数 三角函数,25,第2节 数学和三角函数,26,2.1 数学函数-基本数学函数,27,2.1 数学函数-基本数学函数,举例：用函数RAND函数为学生随机排号 学校组织考试时，经常需要将学生的学号秩序打乱，并随机编排考号，使用RAND随机函数可以方便的实现以上功能。 RAND函数的格式： RAND(),在单元格D2内输入公式“=RAND()”并向下填充，这样在D列各单元格内生存了0-1之间的任意数,28,2.1 数学函数-基本数学函数,举例：用函数RAND函数为学生随机排号 按列D “排序”,可以看到B、C二列已无
10、序排列,再在A2单元格中输入第一个考号,并按序列向下填充,将D列删除或清空数据,29,2.1 数学函数-舍人和取整函数,30,2.1 数学函数-舍人和取整函数,举例: odd, even,功能：将数据舍入为奇数或偶数 odd(n) : 将n向绝对值增大方向舍入为最接近的奇数 even(n): 将n向绝对值增大方向舍入为最接近的偶数 odd(31.3) 产生结果 33 even(-5.4) 产生结果 -6,31,2.1 数学函数-求和函数,32,2.1 数学函数-求和函数,举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和,33,2.1 数学函数-求和函数,举例：某学校的考试成绩统计表，要
11、统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 SUMIF(range，criteria，sum_range),条件判断的单元格区域,判定条件,需要求和的单元格、区域或引用,34,2.1 数学函数-求和函数,举例：某学校的考试成绩统计表，要统计女同学的英语成绩总和。此例问题可以使用SUMIF函数 在求和的单元格输入公式： “=SUMIF(B2:B4,”=女”,F2:F9)” 即得到结果,35,2.1 数学函数-求和函数,举例：COUNTIF 功能：为满足指定条件的单元计数 格式：Countif( 计数区域, “计数条件” ) = countif（D2:D9,“80”）,36,2.2
12、三角函数,逻辑函数是用来判断真假值，或者进行复合检验的Excel函数。在Excel中提供了七种逻辑函数,37,第3节 逻辑函数,38,第3节 逻辑函数,举例：IF 在学校的管理工作中，常常要对学生的考试成绩进行统计分析。假定现有某班级的考试成绩，我们按照各科平均分对学生的考试成绩进行综合评定。评定规则：如果平均分超过90时，评定为优秀，如果平均分小于90并且超过60则认为是合格的，否则记作不合格,39,第3节 逻辑函数,举例：根据规则，我们在综合评定中使用IF函数,IF(logical_test,value_if_true,value_if_false),logical_test 为 TRUE
13、 时返回的值,测试表达式,logical_test 为 FALSE时返回的值,40,第3节 逻辑函数,举例：根据规则，我们在综合评定中使用IF函数 (以B7为例）： “ =IF(B660,IF(B690,”优秀“,”合格“),”不合格“)”,41,第3节 逻辑函数,为英语成绩建立五级分转换1、if(E260,if(E270,if(E280, if(E290,优秀，良好)，中等)，及格)，不及格）,42,第3节 逻辑函数,举例：OR AND OR (logical1,logical2,logicaln) 功能：当只要有一个参数的计算结果为TRUE时,OR函数的计算结果为TRUE, 否则，计算结果
14、为FALSE AND (logical1,logical2,logicaln) 功能：当所有参数的计算结果都是TRUE时,AND函数的计算结果为TRUE, 否则，计算结果为FALSE,43,第3节 逻辑函数,举例：OR AND,44,第3节 逻辑函数,举例：OR AND,45,第4节日期和时间函数,46,第4节日期和时间函数,举例:显示当前的日期与时间,47,第5节 文本函数,48,第5节 文本函数,举例：LEFT RIGHT MID LEFT功能：返回文本字符串中左边的几个字符语法：LEFT(text,num_chars)参数：text是包含要提取字符的文本串。num_chars指定要由 L
15、EFT 所提取的字符数 MID功能：返回文本字符串中从指定位置开始的特定数目的字符语法：MID(text,start_num,num_chars)参数：text是包含要提取字符的文本串。start_num是文本中要提取的第一个字符的位置。num_chars指定从文本串中返回字符的个数,49,第5节 文本函数,举例：LEFT RIGHT MID RIGHT功能：返回文本字符串中右边的几个字符语法：RIGHT(text,num_chars)参数：text是包含要提取字符的文本串。num_chars指定希望 RIGHT 提取的字符数,50,第5节 文本函数,举例： 从字符串 http:/ 分别取出字
16、符 http :/“ “ /“ 的具体函数写法为:LEFT(http:/ http :/RIGHT(http:/ ,8,14)= ,查找函数 引用函数,51,第6节 查找和引用函数,52,第6节 查找和引用函数-查找函数,53,第6节 查找和引用函数-查找函数,举例：现有学生信息表,如果我想查找某个学生的年龄，可以用LOOKUP函数,LOOKUP(lookup_value,lookup_vector,result_vector),指定查找对象,指定定位区域,指定查找返回结果区域,要求lookup_vector和result_vector的区域必须对等,54,第6节 查找和引用函数-查找函数,举例：现有学生信息表,如果我想查找某个学生的年龄，公式“=LOOKUP(A5,A3:A8,B3:B8)”返回22(张雷的年龄)公式“=LOOKUP(A5,A3:A8,c3:c8)”返回男(张雷的性别),55,第6节 查找和引用函数-引用函数,
