第四章公式与函数

第四章公式与函数第1页，共61页，2023年，2月20日，星期三应用公式逻辑函数公式编辑Excel公式与函数使用函数综合练习数学函数统计函数日期与时间函数文本函数财务函数其他函数第2页，共61页，2023年，2月20日，星期三应用公式公式介绍公式类型数值公式字符公式逻辑公式引用运算公式中各运算符的优先级第3页，共61页，2023年，2月20日，星期三公式介绍与数学公式类似，对数据进行各种四则运算。以“=”开头，后面接各种表达式例:求计算机、英语和数学三门功课的总分输入方法:在单元格中输入，“回车”确认在编辑栏内输入并确认第4页，共61页，2023年，2月20日，星期三公式类型数值公式:对数字或单元格内的数字进行计算。(包括日期运算)字符公式:对字符或单元格内的字符进行合并运算。逻辑公式:进行比较运算，用于构造条件。第5页，共61页，2023年，2月20日，星期三数值公式算术运算符:+，－，*，/，^，%例:求总分总分＝平时成绩*30%+期末成绩*70%求给定数(如:2)的立方注意：EXCEL中使用的标点符号都是英文状态的标点符号第6页，共61页，2023年，2月20日，星期三字符公式文本运算符:用“&”进行字符的连接运算。例:产生1月份~12月份说明:对于字符常量要加双引号，单元格或区域内的数值数据可直接引用。第7页，共61页，2023年，2月20日，星期三逻辑公式比较运算符:>,<,>=,<=,=,<>例:比较两门功课考试分数的高低。说明:逻辑公式均用在条件判断式中，产生结果TRUE或FALSE。第8页，共61页，2023年，2月20日，星期三引用运算引用运算符:区域运算符(:)，交叉运算符(空格)，联合运算符(,)。例:count()A1:C1A1,C1A1:D4D4:F7第9页，共61页，2023年，2月20日，星期三各运算符的优先级公式处理复杂数据时，用到的多个运算符的优先级是不同的。数学运算符>字符串运算符>比较运算符默认:根据公式中运算符的特定顺序从左到右计算公式。Q:如何更改求值的顺序？第10页，共61页，2023年，2月20日，星期三公式编辑公式的复制和移动单元格地址引用不同引用在公式复制时的变化确定公式的引用形式使用公式出错信息及原因第11页，共61页，2023年，2月20日，星期三公式的复制和移动介绍公式通常引用单元格计算数据，当对公式进行复制时就不是一般数据的简单复制。例:求出所有同学的总分。例:求出所有女同学的总分。方法一:填充句柄方法二:粘贴方法三:选择性粘贴第12页，共61页，2023年，2月20日，星期三单元格地址引用介绍EXCEL中单元格地址有不同的表示方法，可以直接用相应的地址表示。单元格地址引用类型(三大地址)相对地址:如A3,A4:A8等形式；绝对地址:如$A$3,$a$4:$A$4等形式；混合地址:如$A3,A$3等形式。引用转换:F4不同的引用方式只有在公式复制时有不同的作用。第13页，共61页，2023年，2月20日，星期三不同引用在公式复制时的变化公式单元格(A1)目的单元格(C10)A1=B1+C1C10=D10+E10A1=$B$1+$C$1C10=$B$1+$C$1A1=$B1+C$1C10=$B10+E$1第14页，共61页，2023年，2月20日，星期三确定公式的引用形式例:利用公式复制产生九九乘法表准备:在B35:J35中输入了9个数1~9在A36:A44中输入了9个数1~9要求:在B2单元格中输入一个公式，然后将该公式复制产生九九乘法表第15页，共61页，2023年，2月20日，星期三使用公式出错信息及原因出错信息说明#DIV/0!公式中出现0作除数，可能是空单元格作除数引起的。#NAME?引用了Excel不能识别的文本，输入错误或使用了未定义的名称#NULL!在不相交的区域中指定了一个交集#NUM!在公式或函数中使用了不适当的数字，如不在函数定义域中的数字#REF!单元格引用无效，如引用的单元格不存在#VALUE!错误的参数或运算对象，如数据的类型出错######数值长度超过了单元格列宽第16页，共61页，2023年，2月20日，星期三使用函数函数基础使用函数举例函数的结构第17页，共61页，2023年，2月20日，星期三函数基础函数是一些预定义的公式，方便用户使用。函数运算以“=”开头。函数输入有两种方法:手工直接输入法粘贴函数法第18页，共61页，2023年，2月20日，星期三函数的结构等号表示要输入一个公式或函数进行计算函数名左右括号表示函数的开始和结束参数＝SUM(a1,25,”25”,b5:d7)说明:

参数的个数可以是0个或多个不管函数是否有参数，其括号()不能少参数可以是具体值、含有值的单元格地址或区域地址形式等。第19页，共61页，2023年，2月20日，星期三使用函数举例例:求三门功课的总分利用求和函数:SUM()输入函数:直接输入法:＝SUM(B31:D31)利用函数向导(粘贴函数法)第20页，共61页，2023年，2月20日，星期三数学与三角函数ABS()函数INT()函数PI()函数ROUND()函数MOD()函数RAND()函数SQRT()函数SIN()函数综合应用第21页，共61页，2023年，2月20日，星期三ABS()函数功能:返回参数的绝对值。语法:ABS(number)

Number需要计算其绝对值的实数Q:如何用IF函数代替？第22页，共61页，2023年，2月20日，星期三INT()函数功能:返回接近于实数的最大整数。语法INT(number)number:需要进行取整处理的实数。例如:int(8.9)=?int(-8.9)=?Q:如何用公式返回单元格中正实数的小数部分？第23页，共61页，2023年，2月20日，星期三PI()函数功能:返回圆周率π的值:3.14159265358979语法:PI()说明:此函数无须参数，但函数名后面的括号不能少。第24页，共61页，2023年，2月20日，星期三ROUND()函数功能:对指定数值进行四舍五入并保留指定小数位数。语法:ROUND(number，num_digits)number:需要进行舍入的数字。Num_digits:指定保留的小数位数。例:round(88.56,1)=88.6round(88.56,-1)=90第25页，共61页，2023年，2月20日，星期三MOD()函数功能:返回两数相除的余数。语法:MOD(number，divisor)Number为被除数、divisor为除数。除数为0，则返回错误值#div/0!Q:1222211除以111？第26页，共61页，2023年，2月20日，星期三RAND()函数功能:返回一个〔0,1)均匀分布的随机数。语法:RAND()无参数如何将公式永久性地改为随机数？保持编辑状态，然后按F9生成〔a，b〕之间的随机整数:＝INT(RAND()*(b－a＋1)＋a)Q:如何产生银行帐号的初始密码(六位)第27页，共61页，2023年，2月20日，星期三SQRT()函数功能:返回给定正数的平方根。语法:SQRT(number)

number:为负则函数返回错误值:#Num！Q:如何用简单的公式代替？＝A1^(1/2)第28页，共61页，2023年，2月20日，星期三SIN()函数功能:返回给定角度的正弦值。语法:SIN(number)

注意:Number以弧度表示。Q:角度如何转化成弧度？第29页，共61页，2023年，2月20日，星期三SUM函数功能:返回参数表中所有参数之和。语法:SUM(number1,number2，…)注意:如参数为逻辑值，则TRUE和FALSE分别转换为1和0；文本型数字转换为数值型数字；而引用的单元格中出现空白单元格、逻辑值、文本型数字该参数将被忽略。第30页，共61页，2023年，2月20日，星期三SUMIF函数功能:根据指定条件对若干单元格求和。语法:SUMIF(range,criteria,sum_range)例如:求所有工程师基本工资之和。第31页，共61页，2023年，2月20日，星期三综合应用例:x=46.057使用数学函数计算:第32页，共61页，2023年，2月20日，星期三统计函数average()函数count()countif()函数第33页，共61页，2023年，2月20日，星期三average()函数功能:返回参数平均值(算术平均)语法:average(number1,number2,…,number30)Max():返回给定参数表中的最大值。Min():返回给定参数表中的最小值例:求学生平均成绩、最高分和最低分。第34页，共61页，2023年，2月20日，星期三count()函数功能:返回参数中数值型数据的个数。语法:count(value1,…,value30)Value1,…:是包含或引用各种类型数据的参数。只有数字类型的数据才被计算。Counta:返回参数中非空单元格个数例:统计学生人数第35页，共61页，2023年，2月20日，星期三countif()函数功能:计算区域中满足给定条件的单元格的个数。语法:COUNTIF(range,criteria)Range

为需要计算其中满足条件的单元格数目的单元格区域。Criteria

为确定哪些单元格将被计算在内的条件。例:统计男学生人数第36页，共61页，2023年，2月20日，星期三文本函数UPPER()函数LEFT()函数MID()函数LEN()函数综合应用第37页，共61页，2023年，2月20日，星期三UPPER()函数功能:将文本转化成大写字母。语法:UPPER(text)text:要转化为小写字母的文本串。函数并不改变文本串中的非文本字符。例:Upper(“Exercise1”)结果:EXERCISE1LOWER(text)函数:将文本转化成小写字母第38页，共61页，2023年，2月20日，星期三LEFT()函数功能:返回字符串最左端的子字符串。语法:(text,num_chars)text:包含要提取字符的文字串。Num_chars:指定函数所要返回的字符串长度说明:省略num-chars则假设其值等于1。例:取字符串A1:”信息information”＝left(A1)为信，left(A1,5)为信息infRIGHT:返回某一文本串中最右侧的子字符串。第39页，共61页，2023年，2月20日，星期三MID()函数功能:从文本串的某一指定位置开始，返回指定长度的子字符串。语法:MID(text,start_num,num_chars)text:用于从中提取字符的文本串。Start_num:要从文本串中提取字符的起始位置。Num_chars:指定要返回字符的个数。例:取字符串A1:”信息information”＝mid(A1,4,2)结果:fo第40页，共61页，2023年，2月20日，星期三LEN()函数功能:返回文本字符串中的字符数。LENB返回文本字符串中用于代表字符的字节数。语法:LEN(text)Text

是要查找其长度的文本(包含空格)。例:取字符串A1:”信息information”=LEN(A1)结果:13=LENB(A1)结果:15说明:汉字占两个字节，英文字母占一个字节第41页，共61页，2023年，2月20日，星期三综合应用使用文本函数时常用到文本的连接符号“&”。例:用公式将字符串左数第三个字符取出来。例:用公式将字符串右数第三个字符取出来。第42页，共61页，2023年，2月20日，星期三日期与时间函数日期与时间函数介绍date与time函数today与now函数year、month与day函数综合应用第43页，共61页，2023年，2月20日，星期三日期与时间函数介绍日期/时间数据本质上是数值型数据，该数字称为序列数。序列数:小数点左边的数字表示日期，从1900年1月1日到所输入日期之间的总天数。小数点右边的数字表示时间。例如，序列数367.5表示1901年1月1日晚12时。作用:常作为日期时间函数的返回值或参数。第44页，共61页，2023年，2月20日，星期三date与time函数功能:返回某一特定日期的序列数。语法:DATE(year,month,day)Year介于1900到9999之间的一个数字。Month代表月份的数字。Day代表在该月份中第几天的数字。例:＝date(2006,04,10)结果为日期:2006/04/10time():返回某一特定时间的序列数，为小数第45页，共61页，2023年，2月20日，星期三today与now函数功能:返回系统当天日期的序列数。语法:TODAY()－－－无参数例:＝today()结果为日期:2006/11/27插入静态的日期和时间:当前日期:Ctrl+;当前时间:Ctrl+Shift+；当前日期和时间按Ctrl+;，然后按空格键，最后按Ctrl+Shift+;now():返回系统当前日期和时间的序列数。第46页，共61页，2023年，2月20日，星期三year、month与day函数功能:返回指定日期的年数、月份数和天数。语法:YEAR(serial_number)Serial_number可以为数字、文本，如“15-Apr-1993”或“4-15-1993”。例1:计算年龄(虚岁)例2:用公式得到月份加1的日期年龄的计算方法主要有：①虚岁年龄。中国在习惯上常用的年龄计算方法，按出生后所经历的日历年头计算,即生下来就算1岁,以后每过一次新年便增加1岁。一般按农历新年算，也有按公历算的。第47页，共61页，2023年，2月20日，星期三日期时间函数综合应用例:计算周岁年龄。什么是周岁？②周岁年龄。又称实足年龄，指从出生到计算时为止，共经历的周年数或生日数。例如，1990年7月1日零时进行人口普查登记，一个1989年12月15日出生的婴儿，按虚岁计算是2岁，实际刚刚6个多月，还未过一次生日，按周岁计算应为不满1周岁，即0岁。周岁年龄比虚岁年龄常常小1～2岁,它能正确反映人们的实际生存年岁,是人口统计中最常用的年龄计算方法。

第48页，共61页，2023年，2月20日，星期三逻辑函数IF()函数函数的嵌套AND()函数OR()函数AND、OR、NOT综合比较第49页，共61页，2023年，2月20日，星期三IF()函数功能:对比较条件式Logical-test进行测试，如果条件为逻辑值TRUE，则取value-if-true的值；否则取value-if-false的值。语法:IF(logical-test，value-if-true，value-if-false)例:判断考试成绩是否及格条件:考试成绩>=60为及格，否则为不及格。第50页，共61页，2023年，2月20日，星期三嵌套IF函数在某些情况下，可能需要将某函数作为另一函数的参数使用。例:判断成绩的等级条件:考试成绩>=85为优秀，84>考试成绩>=60为中等，考试成绩<60为差第51页，共61页，2023年，2月20日，星期三AND()函数功能:所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。语法:AND(logical1,logical2,...)Logical1,logical2,...待检测的1～30个条件值，各条件值或为TRUE，或为FALSE。例:判断成绩是否及格条件:机试和笔试都>=60为及格,否则为不及格。第52页，共61页，2023年，2月20日，星期三OR()函数功能:在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE，全为FALSE，才返回FALSE。语法:OR(logical1,logical2,...)Logical1,logical2,...为需要进行检验的1到30个条件，分别为TRUE或FALSE。例:判断成绩是否及格条件:机试和笔试任意<60为不及格,否则为及格。第53页，共61页，2023年，2月20日，星期三AND、OR、NOT综合比较第54页，共61页，2023年，2月20日，星期三财务函数PV函数PMT函数第55页，共61页，2023年，2月20日，星期三PV函数功能：用于计算固定偿还能力下的可贷款总数