Excel公式与函数.ppt

《Excel公式与函数.ppt》由会员分享，可在线阅读，更多相关《Excel公式与函数.ppt（46页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel Excel 电子表格电子表格公式与函数公式与函数一、单元格引用一、单元格引用本此课的内容：本此课的内容：二、常用函数的介绍二、常用函数的介绍一、单元格引用一、单元格引用引子引子 显然，这种使用原数据直接参与运算显然，这种使用原数据直接参与运算的方法不方便！的方法不方便！改用单元格地址的形式试试看！改用单元格地址的形式试试看！=25+128=b3+c3地址地址引用引用 如果参加运算的参数个数较多时，如果参加运算的参数个数较多时，可以采用区域地址的形式可以采用区域地址的形式=sum(b2:I2)标识工作表上的单元格或单元格区域，指明标识工作表上的单元格或单元格区域，指明公式中所用数据的

2、位置公式中所用数据的位置引用的作用引用的作用u ExcelExcel的公式与函数中的参数，可以是单元格地的公式与函数中的参数，可以是单元格地址或区域地址，以代表对应单元格或区域中的内址或区域地址，以代表对应单元格或区域中的内容。当地址中的内容改变时，公式的结果会自动容。当地址中的内容改变时，公式的结果会自动随之改变随之改变u ExcelExcel中的地址概念，类似于数学中的变量概念，中的地址概念，类似于数学中的变量概念，能给运算带来方便能给运算带来方便u在公式中输入单元格（区域）地址最精确的方法在公式中输入单元格（区域）地址最精确的方法是使用指针选定。是使用指针选定。引用的概念引用的概念单元格

3、引用的三种类型单元格引用的三种类型 相对引用，绝对引用，混合引用相对引用，绝对引用，混合引用类型类型地址地址例子例子相对引用相对引用相对地址相对地址=B3+E5绝对引用绝对引用绝对地址绝对地址=$B$3+$E$5混合引用混合引用混合地址混合地址=$B3+E$5相对地址相对地址v格式：格式：采用区域地址的常规写法采用区域地址的常规写法 如：如：=C2*D2-F2,=average(a1:a10)v特点：特点：在复制该公式到其他单元格后，公式在复制该公式到其他单元格后，公式中的相对地址会发生相应的变化。中的相对地址会发生相应的变化。移动移动该公式到其他单元格时，相对地址该公式到其他单元格时，相对地

4、址仍保持原样，不会自行改变。仍保持原样，不会自行改变。注意注意绝对地址绝对地址绝对地址绝对地址v格式：格式：列、行号前面都加上符号列、行号前面都加上符号【$】如：如：=$B$2,=sum($A$1:$A$10)v特点：特点：公式中引用的绝对地址，无论是复制或移动公式中引用的绝对地址，无论是复制或移动该公式到其他单元格，其中的绝对地址都保该公式到其他单元格，其中的绝对地址都保持原样，不会自行调整持原样，不会自行调整(地址被地址被锁定锁定了了)。混合地址混合地址v特点特点：在复制或移动包含混合地址的公式时，在复制或移动包含混合地址的公式时，“绝对的部分绝对的部分”不变，不变，“相对的部分相对的部分

5、”会变。会变。v格式：格式：地址引用中固定行不固定列或固定列地址引用中固定行不固定列或固定列不固定行。不固定行。相对列号绝对相对列号绝对行号行号 例例 B$6,A$4,C$5:F$8B$6,A$4,C$5:F$8绝对绝对列号列号相对相对行号行号 例例$B6,$A4,$C5:$F8$B6,$A4,$C5:$F8 在输入或编辑公式时，可以利用在输入或编辑公式时，可以利用【F4F4】键变换单元格地址的引用方式。首先选中公键变换单元格地址的引用方式。首先选中公式中的某单元格地址，然后每按一次【式中的某单元格地址，然后每按一次【F4F4】键，其地址就按相对、绝对、混合方式循环键，其地址就按相对、绝对、混

6、合方式循环变化一次变化一次如何在三种引用之间中切换如何在三种引用之间中切换如何在三种引用之间中切换如何在三种引用之间中切换实实例例格式：格式：工作表名工作表名！单元格地址单元格地址例如：例如：在在“销售销售1”工作表的工作表的B18单元格单元格求出第一、求出第一、二季总的销售量二季总的销售量 =B14+销售销售2!B14如果要如果要如果要如果要引用不引用不引用不引用不同工作簿同工作簿同工作簿同工作簿的单的单的单的单元格呢元格呢元格呢元格呢?如何引用其他工作表中的单元格如何引用其他工作表中的单元格如何引用其他工作表中的单元格如何引用其他工作表中的单元格注意事项注意事项n当用户当用户插入或删除插入

7、或删除若干单元格若干单元格(或行或行/列列)，会对包含了地址引用的公式产生一定的影响。会对包含了地址引用的公式产生一定的影响。即公式中的地址会作出相应改变，以保持原即公式中的地址会作出相应改变，以保持原公式的有效性。公式的有效性。n当当公式出错时，系统会给出公式出错时，系统会给出错误信息错误信息，常见出错类型及其原因见课本常见出错类型及其原因见课本(P126,表表4.5）A、=C3+$D5B、=D7+$E9C、=E7+$D9D、=E7+$D51、如果、如果B3单元格中的公式为单元格中的公式为“=C3+$D5”复制到同一复制到同一工作表的工作表的D7单元格，该单元格公式为单元格，该单元格公式为_

8、。CA、=AVERAGE（C1：F6)B、=AVERAGE（B1：E6)C、=AVERAGE（B1：F6)D、=AVERAGE（C1：G6)2、已知、已知A1单元格中的公式为：单元格中的公式为：=AVERAGE(B1:F6)将将B列删除之后，列删除之后，A1的公式将调整为的公式将调整为_B使用函数使用函数函数的组成是：函数的组成是：=函数名函数名(参数参数1，参数，参数2，参数，参数n)参数个数可以参数个数可以多达多达30个个参数的形式参数的形式：数值常量数值常量字符串常量字符串常量(以一对半角双引号括住以一对半角双引号括住)逻辑值常量逻辑值常量(TRUE与与FALSE，或或1与与0)单元格或

9、区域地址单元格或区域地址表达式或函数表达式或函数(即函数的嵌套即函数的嵌套)有些函数是不带参数的（即无参函数）。有些函数是不带参数的（即无参函数）。实实例例vExcel提供了数量众多、功能强大的函数提供了数量众多、功能强大的函数来满足运算的要求，这些函数有：来满足运算的要求，这些函数有：vExcel函数的两种输入方法：函数的两种输入方法：（一）直接输入法；（二）粘贴函数法。一）直接输入法；（二）粘贴函数法。数学与三角函数、日期与时间函数、统计函数、数学与三角函数、日期与时间函数、统计函数、逻辑函数、文本函数、逻辑函数、文本函数、数据库函数、财务函数、数据库函数、财务函数、查找与引用函数、信息函

10、数、工程函数等查找与引用函数、信息函数、工程函数等1 1 1 1、统计函数统计函数统计函数统计函数函数名称函数名称功能功能SUM(number1,number2,)Average(number1,number2,)Max(number1,number2,)Min(number1,number2,)Count(number1,number2,)相似的函数还有？相似的函数还有？Countif(Range,Criteria)Sumif(Range,Criteria,Sum_range)着重讨论下面几个统计函数：着重讨论下面几个统计函数：vCOUNT(number1,number2,)功能功能：统计参

11、数组中数值单元格的个数。统计参数组中数值单元格的个数。vCOUNTA(number1,number2,)功能功能：统计参数组中非空白单元格的个数。统计参数组中非空白单元格的个数。vCOUNTBLANK(number1,number2,)功能功能：统计参数组中空白单元格的个数。统计参数组中空白单元格的个数。（1）计数函数）计数函数实实例例vCOUNTIF(Range,Criteria)功能功能：统计统计 Range 区域中满足指定条件区域中满足指定条件Criteria 的单的单元格个数。元格个数。说明说明：range被统计数据所在的区域。被统计数据所在的区域。criteria统计条件。其形式可以

12、为数字、表达统计条件。其形式可以为数字、表达式或字符，例如，条件可以表示为式或字符，例如，条件可以表示为 32、“=32”、“32”、“优秀优秀”。还可以是条件所在的单元格地址。还可以是条件所在的单元格地址（2）条件计数函数）条件计数函数=COUNTIF(I3:I22,=80)统计区域统计区域统计条件统计条件注意：此处的双引号必须用注意：此处的双引号必须用半角半角状态的符号状态的符号实实例例vSUMIF(Range,Criteria,Sum_range)功能功能：根据根据 Range 区域中的指定条件区域中的指定条件 Criteria，则对则对Sum_range相应的单元格求和。相应的单元格求

13、和。说明：说明：range用于条件判断的区域。用于条件判断的区域。criteria 判定条件，可以是数字、表达式或字判定条件，可以是数字、表达式或字符形式。符形式。sum_range 进行求和运算的实际区域。进行求和运算的实际区域。（2）条件求和函数）条件求和函数实实例例函数名称函数名称功能功能Left(text,number)它和它和LeftBLeftB的区别是什么？的区别是什么？(对汉字字符的处理上不同对汉字字符的处理上不同)Right(text,number)Mid(text,number1,number2,)Len(text)Replace(old_text,start_num,num

14、_chars,new_text)在字符串中替换字符在字符串中替换字符Search(text1,text2,num)搜索搜索text1在在text2中中首次首次出现出现的位置的位置,它与它与findfind的区别是的区别是?Lower(text)与此相反的有与此相反的有Upper(text)2 2 2 2、字符函数字符函数字符函数字符函数vLEFT(text,number)功能功能：返回字符串返回字符串 text 左边的左边的 number 个字符。个字符。例如：例如：=LEFT(middle school,4)的结果是的结果是 middvRIGHT(text,number)功能功能：返回字符串

15、：返回字符串 text 右边的右边的 number 个字符。个字符。例例：=RIGHT(middle school,6)的结果是的结果是schoolMID(text,num1,num2)功能功能：返回字符串：返回字符串 text 中，从第中，从第 num1 个字个字符开始的符开始的 num2个字符。个字符。例例：=MID(middle school,3,4)的结果是的结果是ddleLEN(text)功能功能：返回字符串：返回字符串 text 的字符个数的字符个数。例例：=LEN(school )的结果是的结果是6实实例例3 3、逻辑函数逻辑函数函数名称函数名称功能功能例子例子IF(Logica

16、l_test,v1,v2)当当Logical条件式取值为条件式取值为True时，返回时，返回V1的结果，为的结果，为False时，返回时，返回V2的结果的结果=if(G5=80,”A”,”B）AND（Logical1,Logical2,.)当所有的参数值都为当所有的参数值都为TRUE时，返回时，返回TRUE；否则返回；否则返回FALSE=and(1+54,102)OR（Logical1,Logical2,.)当所有的参数值都为当所有的参数值都为FALSE时，返回时，返回FALSE;否则返回否则返回TRUE=or(1+54,10232,abc,xxx),abc,xxx)的运算结果是的运算结果是a

17、bcabc=if(if(4 4+3=5+3=5,abc,xxx),abc,xxx)的运算结果是的运算结果是xxxxxx =if(=if(3=33=3,abc,xxx)abc,xxx)的运算结果是的运算结果是abcabc=if(=if(2-32+32-32+3,abc,xxx),abc,xxx)的运算结果是的运算结果是xxxxxx自自学学材材料料if if if if 函数例子函数例子函数例子函数例子 =if(=if(1 1,0,1),0,1)的运算结果是的运算结果是0 0 =if(=if(0 0,1,0),1,0)的运算结果是的运算结果是0 0 =if(=if(0 0,1,2),1,2)的运算

18、结果是的运算结果是?=if(=if(1 1,2,3),2,3)的运算结果是的运算结果是?=if(=if(3 3,6,9),6,9)的运算结果是的运算结果是?=if(0,=if(0,if(1,2,3)if(1,2,3),if(4,5,6)if(4,5,6)的结果是的结果是?思考：思考：=if(1,if(2,=if(1,if(2,if(3,4,5)if(3,4,5),if(6,7,8),9),if(6,7,8),9)呢？呢？自自学学材材料料IF(,)IF函数带三个参数函数带三个参数 此处一般放此处一般放公式公式公式公式如果公式如果公式的运算结的运算结果为真果为真如果公式运算结果为假如果公式运算结果

19、为假IF(logical_test,value_if_true,value_if_false)功能功能：logical_test 为为“TRUE”时存放时存放 value_if_true 值；当逻辑表达式值；当逻辑表达式 logical_test 为为“FALSE”时时存放存放value_if_false 值。值。注意注意：IF 函数可嵌套函数可嵌套，能完成复杂的条件测试。，能完成复杂的条件测试。实实例例使用使用IF函数时需要注意：函数时需要注意：1、IF函数的参数个数为三个，函数的参数个数为三个，缺一不可缺一不可。2、当、当IF函数的参数是字符型时，用函数的参数是字符型时，用半角半角双引号双

20、引号括起来。括起来。3、在、在IF函数嵌套使用时，注意左函数嵌套使用时，注意左括号括号和和右括号的搭配使用。右括号的搭配使用。if if 函数例子函数例子u =if(if(truetrue,abc,xxx,abc,xxx)的运算结果是的运算结果是abcabcu =if(if(falsefalse,abc,xxx,abc,xxx)的运算结果是的运算结果是xxxxxxu=if(if(3232,abc,xxx),abc,xxx)的运算结果是的运算结果是abcabcu=if(if(4 4+3=5+3=5,abc,xxx),abc,xxx)的运算结果是的运算结果是xxxxxxu =if(=if(3=33

21、=3,abc,xxx)abc,xxx)的运算结果是的运算结果是abcabcu=if(=if(2-32+32-32+3,abc,xxx),abc,xxx)的运算结果是的运算结果是xxxxxx自自学学材材料料if if if if 函数例子函数例子函数例子函数例子u =if(=if(1 1,0,1),0,1)的运算结果是的运算结果是0 0u =if(=if(0 0,1,0),1,0)的运算结果是的运算结果是0 0u =if(=if(0 0,1,2),1,2)的运算结果是的运算结果是?u =if(=if(1 1,2,3),2,3)的运算结果是的运算结果是?u =if(=if(3 3,6,9),6,9

22、)的运算结果是的运算结果是?u=if(0,=if(0,if(1,2,3)if(1,2,3),if(4,5,6)if(4,5,6)的结果是的结果是?思考：思考：=if(1,=if(1,if(2,if(2,if(3,4,5)if(3,4,5),if(6,7,8)if(6,7,8),9),9)呢？呢？自自学学材材料料数学函数名称数学函数名称功能功能应用举例应用举例ABS(number)返回绝对值返回绝对值=ABS(-24.65)PRODUCT(number1,number2,)各参数相乘各参数相乘=PRODUCT(1,2,3,4)SQRT(number)返回正的平方根值返回正的平方根值=SQRT(A

23、BS(-16)MOD(number1,number2)返回两数相除后的余返回两数相除后的余数数=MOD(26,4)RAND()返回返回0,1)范围内的随范围内的随机数机数=RAND()PI()返回返回的值的值=3*3*PI()LOG10(number)返回以返回以10为底的对数为底的对数值值=LOG10(1000)4 4 4 4、数学函数、数学函数、数学函数、数学函数vROUND(number1,number2)功能功能：把数值把数值number1四舍五入到指定的小数四舍五入到指定的小数位数位数(number2)。例：例：=ROUND(35.78,1)的结果是的结果是35.8vINT(numb

24、er)功能功能：取数值取数值number的整数部分的整数部分(返回结果是一返回结果是一个小于个小于number的最大整数的最大整数)。例：例：=INT(15.63)的结果是的结果是15 =INT(-15.63)的结果是的结果是-16(参数必须参数必须是数值型是数值型)函数名称函数名称功能功能例子例子YEAR(date)计算序列数计算序列数date的年份值的年份值=YEAE(37509)2002 MONTH(date)计算序列数计算序列数date的月份值的月份值=MONTH(37509)9DAY（(date)计算序列数计算序列数date的当月日期的当月日期=DAY(37509)10DATE(ye

25、ar,month,day)计算某一日期的序列数计算某一日期的序列数=DATE(2002,9,10)37509TODAY()返回系统的当前日期返回系统的当前日期=TODAY()NOW()返回系统的当前日期和时间返回系统的当前日期和时间=NOW()5.5.5.5.日期函数日期函数日期函数日期函数YEAR(date)功能功能：返回指定日期：返回指定日期date的年份。的年份。例例：=YEAR(1998-4-23 )的结果是的结果是1998。=YEAR(35908)的结果是的结果是1998；=YEAR(D12)的结果是的结果是1998；参数是参数是日期日期或日期所对应或日期所对应的的序列数，序列数，或

26、单元格地址或单元格地址1998-4-23D12MONTH(date)功能功能：返回指定日期：返回指定日期date的月份。的月份。例例：=MONTH(1998-4-23)的结果是的结果是4。DAY(date)功能功能：返回指定日期：返回指定日期date的日子。的日子。例例：=DAY(1998-4-23)的结果是的结果是23。NOW()功能功能：无参数函数，返回系统的日期及：无参数函数，返回系统的日期及时间。时间。例例：=NOW()的结果是的结果是2001-10-13 9:17。TODAY()功能功能：无参数函数，返回系统的日期。：无参数函数，返回系统的日期。例例：=TODAY()的结果是的结果是

27、2001-10-13。年龄的计算年龄的计算设定：某人的出生日期置于设定：某人的出生日期置于D3单元格，截止统计日期单元格，截止统计日期置于置于B9单元格。单元格。虚岁虚岁的计算：的计算：=year(b9)-year(d3)足岁足岁的计算：的计算：=year(b9)-year(d3)+if(b9=date(year(b9),month(d3),day(d3),0,-1)注注 函数函数date(year(b9),month(d3),day(d3)date(year(b9),month(d3),day(d3)的作用是：的作用是：构造一个特殊的日期构造一个特殊的日期-年份与统计年份相同，但月份年份与统

28、计年份相同，但月份及日子却与出生月份、日子相同。这个特殊日期与统计日期及日子却与出生月份、日子相同。这个特殊日期与统计日期b9b9比较（年份不需作比较），即可得出，到统计日期为止，比较（年份不需作比较），即可得出，到统计日期为止，某人是否已过了生日（是否已过了出生月份或出生日子）某人是否已过了生日（是否已过了出生月份或出生日子）月龄与周龄的计算月龄与周龄的计算月龄与周龄的计算月龄与周龄的计算设定：某人的出生日期置于设定：某人的出生日期置于D3单元格，截止统计日期置于单元格，截止统计日期置于B9单元格。单元格。月龄月龄的计算：的计算：=(year(b9)-year(d3)*12+month(b9

29、)-month(d3)足月足月的计算：的计算：=(year(b9)-year(d3)*12+month(b9)-month(d3)+if(b9=date(year(b9),month(d3),day(d3),0,-1)注：月龄计算，主要用于按参加工作月数计发奖金，新生注：月龄计算，主要用于按参加工作月数计发奖金，新生儿出生时间计算等问题。儿出生时间计算等问题。周龄的计算：周龄的计算：=(b9-d3)/7 (=(b9-d3)/7 (用用roundround函数进行舍入操作函数进行舍入操作)天数的计算：天数的计算：=b9-d3=b9-d31、把、把 写成写成Excel表达式是表达式是2、已知函数、

30、已知函数TODAY（）的值是（）的值是1999-12-10，则函数，则函数MONTH(TODAY()的值是的值是3、下列取值相同的表达式是、下列取值相同的表达式是A、T&S 与与“T”+“S”B、“T”&“S”与与“T”+“S”C、“T”+“S”与与“S”&“T”D、“T”&“S”与与LEFT（“TSST”，2）答案答案4、若、若B2单元格存放的是数据单元格存放的是数据1，则公式，则公式=AND(B2=1,OR(B2,0),NOT(B20)的值为的值为 。A、TRUE B、FALSE C、1 D、05、已知函数、已知函数=DATE(93,10,1)=34243,则函数则函数=MONTH(DATE(93,MONTH(34243)+2,1)的结果是的结果是 。A、10 B、12 C、1 D、3答案答案1、=4*PI()*(1+5/SQRT(7)2、123、D4、B5、B返回返回