第四章公式与函数精选文档.ppt

第四章公式与函数本讲稿第一页，共六十一页应用公式应用公式逻辑函数逻辑函数公式编辑公式编辑ExcelExcel公式与函数公式与函数使用函数使用函数综合练习综合练习数学函数数学函数统计函数统计函数日期与时间函数日期与时间函数文本函数文本函数财务函数财务函数其他函数其他函数本讲稿第二页，共六十一页应应用用公公式式l公式介绍公式介绍l公式类型公式类型l数值公式数值公式l字符公式字符公式l逻辑公式逻辑公式l引用运算引用运算l公式中各运算符的优先级公式中各运算符的优先级本讲稿第三页，共六十一页公式介绍公式介绍l与数学公式类似，对与数学公式类似，对数据数据进行各种四则运算。进行各种四则运算。l以以“=”开头，后面接各种开头，后面接各种表达式表达式例例:求计算机求计算机、英语和数学三门功课的总分、英语和数学三门功课的总分l输入方法输入方法:在单元格中输入，在单元格中输入，“回车回车”确认确认在编辑栏内输入并确认在编辑栏内输入并确认本讲稿第四页，共六十一页公式类型公式类型l数数值值公公式式:对对数数字字或或单单元元格格内内的的数数字字进进行行计计算算。(包括日期运算包括日期运算)l字字符符公公式式:对对字字符符或或单单元元格格内内的的字字符符进进行行合合并运算。并运算。l逻辑公式逻辑公式:进行比较运算，用于构造条件。进行比较运算，用于构造条件。本讲稿第五页，共六十一页数值公式数值公式l算术运算符算术运算符:+，*，/，%l例例:求总分求总分l总分总分平时成绩平时成绩*30%+期末成绩期末成绩*70%l求给定数求给定数(如如:2)的立方的立方注意：注意：EXCEL中使用的标点符号都是英文中使用的标点符号都是英文状态的标点符号状态的标点符号本讲稿第六页，共六十一页字符公式字符公式l文本运算符文本运算符:用用“&”进行字符的连接运算。进行字符的连接运算。l例例:产生产生1月份月份12月份月份l说明说明:对于对于字符常量字符常量要加要加双引号双引号，单元格或区域内，单元格或区域内的数值数据可直接引用。的数值数据可直接引用。本讲稿第七页，共六十一页逻辑公式逻辑公式l比较运算符比较运算符:,=,=,=,l例例:比较两门功课考试分数的高低。比较两门功课考试分数的高低。l说明说明:逻辑公式均用在条件判断式中，产生结果逻辑公式均用在条件判断式中，产生结果TRUE或或FALSE。本讲稿第八页，共六十一页引用运算引用运算l引用运算符引用运算符:区域运算符区域运算符(:)，交叉运算符，交叉运算符(空空格格)，联合运算符，联合运算符(,)。l例例:count()A1:C1A1,C1A1:D4D4:F7本讲稿第九页，共六十一页各运算符的优先级各运算符的优先级l公式处理复杂数据时，用到的多个运算符的优先公式处理复杂数据时，用到的多个运算符的优先级是不同的。级是不同的。l数学运算符数学运算符字符串运算符字符串运算符比较运算符比较运算符l默认默认:根据公式中运算符的根据公式中运算符的特定顺序特定顺序从左到右从左到右计算公式。计算公式。lQ:如何更改求值的顺序？如何更改求值的顺序？本讲稿第十页，共六十一页公公式式编编辑辑l公式的复制和移动公式的复制和移动l单元格地址引用单元格地址引用l不同引用在公式复制时的变化不同引用在公式复制时的变化l确定公式的引用形式确定公式的引用形式l使用公式出错信息及原因使用公式出错信息及原因本讲稿第十一页，共六十一页公式的复制和移动介绍公式的复制和移动介绍l公式通常公式通常引用引用单元格计算数据，当对公式进行单元格计算数据，当对公式进行复制时就不是一般数据的简单复制。复制时就不是一般数据的简单复制。l例例:求出所有同学的总分。求出所有同学的总分。l例例:求出所有女同学的总分。求出所有女同学的总分。l方法一方法一:填充句柄填充句柄l方法二方法二:粘贴粘贴l方法三方法三:选择性粘贴选择性粘贴本讲稿第十二页，共六十一页单元格地址引用介绍单元格地址引用介绍lEXCEL中中单单元元格格地地址址有有不不同同的的表表示示方方法法，可可以以直直接用相应的地址表示。接用相应的地址表示。l单元格地址引用类型单元格地址引用类型(三大地址三大地址)相对地址相对地址:如如A3,A4:A8等形式；等形式；绝对地址绝对地址:如如$A$3,$a$4:$A$4等形式；等形式；混合地址混合地址:如如$A3,A$3等形式。等形式。引用转换引用转换:F4l不同的引用方式只有在不同的引用方式只有在公式复制公式复制时有不同的作用。时有不同的作用。本讲稿第十三页，共六十一页不同引用在公式复制时的变化不同引用在公式复制时的变化公式单元格(A1)目的单元格(C10)A1=B1+C1C10=D10+E10A1=$B$1+$C$1C10=$B$1+$C$1A1=$B1+C$1C10=$B10+E$1本讲稿第十四页，共六十一页确定公式的引用形式确定公式的引用形式l例例:利用公式复制产生九九乘法表利用公式复制产生九九乘法表l准备准备:在在B35:J35中输入了中输入了9个数个数19在在A36:A44中输入了中输入了9个数个数19l要求要求:在在B2单元格中输入一个公式，然后将单元格中输入一个公式，然后将该公式复制产生九九乘法表该公式复制产生九九乘法表本讲稿第十五页，共六十一页使用公式出错信息及原因使用公式出错信息及原因出错信息说明#DIV/0!公式中出现0作除数，可能是空单元格作除数引起的。#NAME?引用了Excel不能识别的文本，输入错误或使用了未定义的名称#NULL!在不相交的区域中指定了一个交集#NUM!在公式或函数中使用了不适当的数字，如不在函数定义域中的数字#REF!单元格引用无效，如引用的单元格不存在#VALUE!错误的参数或运算对象，如数据的类型出错#数值长度超过了单元格列宽本讲稿第十六页，共六十一页使使用用函函数数l函数基础函数基础l使用函数举例使用函数举例l函数的结构函数的结构本讲稿第十七页，共六十一页函数基础函数基础l函数是一些预定义的公式，方便用户使用。函数是一些预定义的公式，方便用户使用。l函数运算以函数运算以“=”开头。开头。l函数输入有两种方法函数输入有两种方法:手工直接输入法手工直接输入法粘贴函数法粘贴函数法本讲稿第十八页，共六十一页函数的结构函数的结构等号表示要输入一个公式或函数进行计算等号表示要输入一个公式或函数进行计算函数名函数名左右括号表示函数的开始和结束左右括号表示函数的开始和结束参数参数SUM(a1,25,SUM(a1,25,”2525”,b5:d7),b5:d7)说明说明:l 参数的个数可以是参数的个数可以是0 0个或多个个或多个l 不管函数是否有参数，其括号不管函数是否有参数，其括号()()不能少不能少l 参数可以是具体值、含有值的单元格地址或区域地址参数可以是具体值、含有值的单元格地址或区域地址形式等。形式等。本讲稿第十九页，共六十一页使用函数举例使用函数举例l例例:求三门功课的总分求三门功课的总分利用求和函数利用求和函数:SUM()l输入函数输入函数:直接输入法直接输入法:SUM(B31:D31)利用函数向导利用函数向导(粘贴函数法粘贴函数法)本讲稿第二十页，共六十一页数数 学学 与与 三三 角角 函函 数数ABS()函数函数INT()函数函数PI()函数函数ROUND()函数函数MOD()函数函数RAND()函数函数SQRT()函数函数SIN()函数函数综合应用综合应用本讲稿第二十一页，共六十一页ABS()函数函数l功能功能:返回参数的绝对值。返回参数的绝对值。l语法语法:ABS(number)Number需要计算其绝对值的实数需要计算其绝对值的实数Q:如何用如何用IF函数函数代替？代替？本讲稿第二十二页，共六十一页INT()函数函数l功能功能:返回接近于实数的最大整数。返回接近于实数的最大整数。l语法语法INT(number)lnumber:需要进行取整处理的实数。需要进行取整处理的实数。l例如例如:int(8.9)=?int(-8.9)=?lQ:如何用公式返回单元格中正实数的小数部分？如何用公式返回单元格中正实数的小数部分？本讲稿第二十三页，共六十一页PI()函数函数l功能功能:返回圆周率返回圆周率的值的值:3.14159265358979l语法语法:PI()l说明说明:此函数无须参数，但函数名后面的括号此函数无须参数，但函数名后面的括号不能少。不能少。本讲稿第二十四页，共六十一页ROUND()函数函数l功能功能:对指定数值进行四舍五入并保留指定小数位对指定数值进行四舍五入并保留指定小数位数。数。l语法语法:ROUND(number，num_digits)number:需要进行舍入的数字。需要进行舍入的数字。Num_digits:指定保留的小数位数。指定保留的小数位数。l例例:round(88.56,1)=88.6round(88.56,-1)=90本讲稿第二十五页，共六十一页MOD()函数函数l功能功能:返回两数相除的余数。返回两数相除的余数。l语法语法:MOD(number，divisor)Number为被除数为被除数、divisor为除数。为除数。除数为除数为0，则返回错误值，则返回错误值#div/0!lQ:1222211除以除以111？本讲稿第二十六页，共六十一页RAND()函数函数l功能功能:返回一个返回一个0,1)均匀分布的均匀分布的随机数随机数。l语法语法:RAND()无参数无参数如何将公式永久性地改为随机数？如何将公式永久性地改为随机数？保持编辑状态，然后按保持编辑状态，然后按F9l生成生成a，b之间的随机整数之间的随机整数:lINT(RAND()*(ba1)a)lQ:如何产生银行帐号的初始密码如何产生银行帐号的初始密码(六位六位)本讲稿第二十七页，共六十一页SQRT()函数函数l功能功能:返回给定返回给定正数正数的平方根。的平方根。l语法语法:SQRT(number)number:为负则函数返回错误值为负则函数返回错误值:#Num！lQ:如何用简单的公式代替？如何用简单的公式代替？lA1(1/2)本讲稿第二十八页，共六十一页SIN()函数函数l功能功能:返回给定返回给定角度角度的正弦值。的正弦值。l语法语法:SIN(number)l注意注意:Number以以弧度弧度表示。表示。lQ:角度如何转化成弧度？角度如何转化成弧度？本讲稿第二十九页，共六十一页SUM函数函数l功能功能:返回参数表中所有参数之和。返回参数表中所有参数之和。l语法语法:SUM(number1,number2，)l注意注意:如参数为逻辑值，则如参数为逻辑值，则TRUE和和FALSE分分别转换为别转换为1和和0；文本型数字转换为数值型数字；文本型数字转换为数值型数字；而引用的单元格中出现空白单元格、逻辑值、而引用的单元格中出现空白单元格、逻辑值、文本型数字该参数将被忽略。文本型数字该参数将被忽略。本讲稿第三十页，共六十一页SUMIF函数函数l功能功能:根据指定条件对若干单元格求和。根据指定条件对若干单元格求和。l语法语法:SUMIF(range,criteria,sum_range)l例如例如:l求所有工程师基本工资之和。求所有工程师基本工资之和。本讲稿第三十一页，共六十一页综合应用综合应用l例:x=46.057l使用数学函数计算:本讲稿第三十二页，共六十一页统统计计函函数数average()函数函数count()countif()函数函数本讲稿第三十三页，共六十一页average()函数函数l功能功能:返回参数平均值返回参数平均值(算术平均算术平均)l语法语法:average(number1,number2,number30)lMax():返回给定参数表中的最大值。返回给定参数表中的最大值。lMin():返回给定参数表中的最小值返回给定参数表中的最小值l例例:求学生平均成绩、最高分和最低分。求学生平均成绩、最高分和最低分。本讲稿第三十四页，共六十一页count()函数函数l功能功能:返回参数中返回参数中数值型数据数值型数据的个数。的个数。l语法语法:count(value1,value30)Value1,:是包含或引用各种类型数据的是包含或引用各种类型数据的参数。参数。只有数字类型的数据才被计算。只有数字类型的数据才被计算。lCounta:返回参数中非空单元格个数返回参数中非空单元格个数l例例:统计学生人数统计学生人数本讲稿第三十五页，共六十一页countif()函数函数l功能功能:计算区域中计算区域中满足给定条件满足给定条件的单元格的个数。的单元格的个数。l语法语法:COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格为需要计算其中满足条件的单元格数目的单元格区域。数目的单元格区域。Criteria为确定哪些单元格将被计算在内为确定哪些单元格将被计算在内的条件。的条件。l例例:统计男学生人数统计男学生人数本讲稿第三十六页，共六十一页文文 本本 函函 数数UPPER()函数函数LEFT()函数函数MID()函数函数LEN()函数综合应用函数综合应用本讲稿第三十七页，共六十一页UPPER()函数函数l功能功能:将文本转化成大写字母。将文本转化成大写字母。l语法语法:UPPER(text)text:要转化为小写字母的文本串。要转化为小写字母的文本串。函数并不改变文本串中的非文本字符。函数并不改变文本串中的非文本字符。l例例:Upper(“Exercise1”)结果结果:EXERCISE1lLOWER(text)函数函数:将文本转化成小写字母将文本转化成小写字母本讲稿第三十八页，共六十一页LEFT()函数函数l功能功能:返回字符串最左端的子字符串。返回字符串最左端的子字符串。l语法语法:(text,num_chars)text:包含要提取包含要提取字符字符的文字串。的文字串。Num_chars:指定函数所要返回的字符串长度指定函数所要返回的字符串长度说明说明:省略省略num-chars则假设其值等于则假设其值等于1。l例例:取字符串取字符串A1:”信息信息information”lleft(A1)为信，为信，left(A1,5)为为信息信息inflRIGHT:返回某一文本串中最右侧的子字符串。返回某一文本串中最右侧的子字符串。本讲稿第三十九页，共六十一页MID()函数函数l功能功能:从文本串的某一指定位置开始，返回指定长从文本串的某一指定位置开始，返回指定长度的子字符串。度的子字符串。l语法语法:MID(text,start_num,num_chars)text:用于从中提取字符的文本串。用于从中提取字符的文本串。Start_num:要从文本串中提取字符的起始位置。要从文本串中提取字符的起始位置。Num_chars:指定要返回字符的个数。指定要返回字符的个数。l例例:取字符串取字符串A1:”信息信息information”mid(A1,4,2)结果结果:fo本讲稿第四十页，共六十一页LEN()函数函数l功能功能:返回文本字符串中的字符数。返回文本字符串中的字符数。lLENB返回文本字符串中用于代表字符的字节数。返回文本字符串中用于代表字符的字节数。l语法语法:LEN(text)Text是要查找其长度的文本是要查找其长度的文本(包含空格包含空格)。l例例:取字符串取字符串A1:”信息信息information”=LEN(A1)结果结果:13=LENB(A1)结果结果:15说明说明:汉字占两个字节，英文字母占一个字节汉字占两个字节，英文字母占一个字节本讲稿第四十一页，共六十一页综合应用综合应用l使用文本函数时常用到文本的连接符号使用文本函数时常用到文本的连接符号“&”。l例例:用公式将字符串左数第三个字符取出来。用公式将字符串左数第三个字符取出来。l例例:用公式将字符串右数第三个字符取出来。用公式将字符串右数第三个字符取出来。本讲稿第四十二页，共六十一页日日 期期 与与 时时 间间 函函 数数l日期与时间函数介绍日期与时间函数介绍ldate与与time函数函数ltoday与与now函数函数lyear、month与与day函数函数l综合应用综合应用本讲稿第四十三页，共六十一页日期与时间函数介绍日期与时间函数介绍l日期日期/时间数据本质上是数值型数据，该数字称为时间数据本质上是数值型数据，该数字称为序序列数列数。l序列数序列数:小数点左边的数字表示日期，从小数点左边的数字表示日期，从1900年年1月月1日到所日到所输入日期之间的总天数。输入日期之间的总天数。小数点右边的数字表示时间。小数点右边的数字表示时间。例如，序列数例如，序列数367.5表示表示1901年年1月月1日晚日晚12时。时。l作用作用:常作为日期时间函数的返回值或参数。常作为日期时间函数的返回值或参数。本讲稿第四十四页，共六十一页date与与time函数函数l功能功能:返回某一特定返回某一特定日期日期的序列数。的序列数。l语法语法:DATE(year,month,day)Year介于介于1900到到9999之间的一个数字。之间的一个数字。Month代表月份的数字。代表月份的数字。Day代表在该月份中第几天的数字。代表在该月份中第几天的数字。l例例:date(2006,04,10)结果为日期结果为日期:2006/04/10ltime():返回某一特定返回某一特定时间时间的序列数，为小数的序列数，为小数本讲稿第四十五页，共六十一页today与与now函数函数l功能功能:返回系统返回系统当天日期当天日期的序列数。的序列数。l语法语法:TODAY()无参数无参数例例:today()结果为日期结果为日期:2006/11/27l插入静态的日期和时间插入静态的日期和时间:当前日期当前日期:Ctrl+;当前时间当前时间:Ctrl+Shift+；当前日期和时间按当前日期和时间按Ctrl+;，然后按空格键，最后，然后按空格键，最后按按Ctrl+Shift+;lnow():返回系统返回系统当前日期和时间当前日期和时间的序列数。的序列数。本讲稿第四十六页，共六十一页year、month与与day函数函数l功能功能:返回指定日期的年数、月份数和天数。返回指定日期的年数、月份数和天数。l语法语法:YEAR(serial_number)Serial_number可可以以为为数数字字、文文本本，如如“15-Apr-1993”或或“4-15-1993”。l例例1:计算年龄计算年龄(虚岁虚岁)l例例2:用公式得到月份加用公式得到月份加1的日期的日期l年年龄龄的的计计算算方方法法主主要要有有：虚虚岁岁年年龄龄。中中国国在在习习惯惯上上常常用用的的年年龄龄计计算算方方法法，按按出出生生后后所所经经历历的的日日历历年年头头计计算算,即即生生下下来来就就算算1岁岁,以以后后每每过过一一次次新新年年便便增增加加1岁岁。一一般般按按农农历历新新年年算算，也也有有按按公公历历算算的。的。本讲稿第四十七页，共六十一页日期时间函数综合应用日期时间函数综合应用l例例:计算周岁年龄。计算周岁年龄。l什么是周岁？什么是周岁？l周岁年龄。又称实足年龄，指从出生到计算时为止，共经周岁年龄。又称实足年龄，指从出生到计算时为止，共经历的周年数或生日数。例如，历的周年数或生日数。例如，1990年年7月月1日零时进行人口普日零时进行人口普查登记，一个查登记，一个1989年年12月月15日出生的婴儿，按虚岁计算是日出生的婴儿，按虚岁计算是2岁，实岁，实际刚刚际刚刚6个多月，还未过一次生日，按周岁计算应为不满个多月，还未过一次生日，按周岁计算应为不满1周岁，周岁，即即0岁。周岁年龄比虚岁年龄常常小岁。周岁年龄比虚岁年龄常常小12岁岁,它能正确反映人们的它能正确反映人们的实际生存年岁实际生存年岁,是人口统计中最常用的年龄计算方法。是人口统计中最常用的年龄计算方法。本讲稿第四十八页，共六十一页逻逻辑辑函函数数lIF()函数函数l函数的嵌套函数的嵌套lAND()函数函数lOR()函数函数lAND、OR、NOT综合比较综合比较本讲稿第四十九页，共六十一页IF()函数函数l功能功能:对对比较条件式比较条件式Logical-test进行测试，如果条件为进行测试，如果条件为逻辑值逻辑值TRUE，则取，则取value-if-true的值；否则取的值；否则取value-if-false的值。的值。l语法语法:IF(logical-test，value-if-true，value-if-false)l例例:判断考试成绩是否及格判断考试成绩是否及格l条件条件:考试成绩考试成绩=60为及格，否则为不及格。为及格，否则为不及格。本讲稿第五十页，共六十一页嵌套嵌套IF函数函数l在某些情况下，可能需要将某函数作为另一函数在某些情况下，可能需要将某函数作为另一函数的的参数参数使用。使用。l例例:判断成绩的等级判断成绩的等级l条件条件:考试成绩考试成绩=85为优秀，为优秀，84考试成绩考试成绩=60为中等，为中等，考试成绩考试成绩=60为及格为及格,否则为不及格。否则为不及格。本讲稿第五十二页，共六十一页OR()函数函数l功能功能:在其参数组中，任何一个参数逻辑值为在其参数组中，任何一个参数逻辑值为TRUE，即返回，即返回TRUE，全为，全为FALSE，才返回，才返回FALSE。l语法语法:OR(logical1,logical2,.)Logical1,logical2,.为需要进行检验的为需要进行检验的1到到30个个条件，分别为条件，分别为TRUE或或FALSE。l例例:判断成绩是否及格判断成绩是否及格l条件条件:机试和笔试任意机试和笔试任意60为不及格为不及格,否则为及格。否则为及格。本讲稿第五十三页，共六十一页AND、OR、NOT综合比较综合比较本讲稿第五十四页，共六十一页财财务务函函数数lPV函数函数lPMT函数函数本讲稿第五十五页，共六十一页PV函数函数l功能：用于计算固定偿还能力下的可贷款总数。功能：用于计算固定偿还能力下的可贷款总数。l语法：语法：PV(rate,nper,pmt,fv,type)l例如：某用户想商业贷款购房，月偿还能力为例如：某用户想商业贷款购房，月偿还能力为3500元元/月，目前月利率为千分之月，目前月利率为千分之5。本讲稿第五十六页，共六十一页PMT函数函数l功能：用于计算贷款后每期需偿还的金额。功能：用于计算贷款后每期需偿还的金额。l语法：语法：PMT(rate,nper,pv,fv,type)本讲稿第五十七页，共六十一页其其他他函函数数lFREQUENCY函数函数lRANK函数函数本讲稿第五十八页，共六十一页FREQUENCY函数函数l功能：将区域功能：将区域range1中的数据按垂直区域中的数据按垂直区域range2（分段点）进行频率分布的统计，统（分段点）进行频率分布的统计，统计结果放在计结果放在range2右边列的对应位置。右边列的对应位置。l语法语法(range1,range2)l例如：统计计算机成绩在例如：统计计算机成绩在059，6069，7079，8089，90100各区间中的人数各区间中的人数本讲稿第五十九页，共六十一页RANK函数函数l功能：返回单元格功能：返回单元格Number在一个垂直区域在一个垂直区域range中的排位名次，中的排位名次，rank-way是排位的方式。是排位的方式。l语法：语法：RANK(number,range,rank-way)l例如：求各位同学计算机成绩在整个班级中的例如：求各位同学计算机成绩在整个班级中的名次。名次。本讲稿第六十页，共六十一页公式与函数综合练习题公式与函数综合练习题1.根据根据A列中的学生成绩，在列中的学生成绩，在B列中对应单元格中列中对应单元格中计算学生的成绩绩点，绩点的确定方法如下计算学生的成绩绩点，绩点的确定方法如下:成绩成绩60分时绩点为分时绩点为1，成绩每增加，成绩每增加1分绩点增加分绩点增加0.1；成绩小于成绩小于60分时绩点为分时绩点为0。2.用公式在区域用公式在区域D2:D101中填入相应信息中填入相应信息:当所在行当所在行的的C列单元格中的数值为整数时，其结果取值为列单元格中的数值为整数时，其结果取值为1，否则为，否则为0。3.用公式在区域用公式在区域G2:G101的对应单元格中根据区域的对应单元格中根据区域E2:F101中的数据填入相应信息中的数据填入相应信息:如果性别为男，如果性别为男，则填入某先生，如果性别为女，则填入某小姐。则填入某先生，如果性别为女，则填入某小姐。如如:“熊先生，许小姐熊先生，许小姐”。本讲稿第六十一页，共六十一页