Excel数据处理与统计初步_第4版_第3章_公式名字与函数.ppt

《Excel数据处理与统计初步_第4版_第3章_公式名字与函数.ppt》由会员分享，可在线阅读，更多相关《Excel数据处理与统计初步_第4版_第3章_公式名字与函数.ppt（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel数据处理与统计初步数据处理与统计初步 第第4版版3.4 函数函数简介简介1、函数的概念、函数的概念n函数是函数是能够完成特定功能的能够完成特定功能的程序程序。在。在Excel中，中，它是系统它是系统预定义预定义的一些的一些公式公式，它们使用一些称，它们使用一些称为为参数参数的特定数值按特定的的特定数值按特定的顺序顺序或结构进行计或结构进行计算，然后把计算的算，然后把计算的结果结果存放在某个单元格中。存放在某个单元格中。n在大多数情况下，函数的计算结果是数值。当在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组然，它也可以返回文本、引用、逻辑值、数组或工作

2、表的信息或工作表的信息 3.4 函数函数简介简介n2、Excel函数分类函数分类分 类功 能 简 介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数3.4 函数函数简

3、介简介3、函数调用、函数调用 n函数的语法函数的语法 n函数名函数名(参数参数1,参数参数2,参数参数3,)n在公式中调用函数在公式中调用函数n=IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函嵌套函数数3.7 逻辑函数逻辑函数n1、Excel的比较运算符的比较运算符n比较运算又称关系运算，就是人们常说的比较式。比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么比较运算只有两种不同的结果，要么“正确正确”，要，要么么“错误错误”，不可能有第三种结果，不可能有第三种结果 比较运算符含 义示 例=等于a1=3大于a1b1小于a1=大于等于a1=60

4、比较运算符含 义示 例=小于等于a1=”dd”不等于a103.7 逻辑函数逻辑函数2 AND、NOT、OR、TRUE、FALSE函数函数n用法用法nAND(x1,x2,x30)nOR(x1,x2,x30)nNOT(logical)n案例案例nAND(TRUE,TRUE)=TRUE，nOR(TRUE,TRUE)=TRUEnAND(TRUE,FALSE,TRUE,TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。n如果如果B1，B2，B3单元格中的值为单元格中的值为 TRUE，FALSE，TRUE，则则AND(B1:B3)=FALSE，但，但OR(B1:B3)=T

5、RUE。nAND(2,2+3=5)=TRUE3.8 数学数学和三角函数和三角函数n1、概述、概述nExcel提供了许多数学和三角函数，它们能够提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。返回到输入公式的单元格中。2、常见的数学函数、常见的数学函数nMOD、TRUNC、ABS、SQRT、SIN、ASIN(n1)，ACOS(n1)，ATAN(n2)、EXP(n)、POWER(x,n)、LN(n)、FACT(n)、LOG(n,base

6、)、MINVERSE(array)、MMULT(array1,array2)3.8 数学数学和三角函数和三角函数n1、RAND()n随机函数，产生一个大于等于随机函数，产生一个大于等于0小于小于1均匀均匀分布的随机数，每次计算工作表时，都将分布的随机数，每次计算工作表时，都将返回一个新的数值。例如，返回一个新的数值。例如，RAND()*(b-a)+a，RAND()*100n2、INT(x)n取整函数，返回实数取整函数，返回实数x舍入后的整数值。例舍入后的整数值。例如，如，INT(8.9)=8，INT(-8.9)=-93.8 数学数学和三角函数和三角函数n3、ROUND(number,n)n四舍

7、五入函数，返回某个数字按指定位数四舍五入函数，返回某个数字按指定位数取整后的数字。例如，取整后的数字。例如，ROUND(2.15,1)=2.2nROUND(-1.475,2)=-1.48nROUND(21.5,0)=22nROUND(21.5,-1)=20nROUND(21.5,-2)=03.8 数学数学和三角函数和三角函数3、数学函数应用案例、数学函数应用案例【例例1】用随机函数产生大量的实验数据，计算排名。用随机函数产生大量的实验数据，计算排名。问问题题：有有一一张张工工资资工工作作表表，结结构构如如图图所所示示。现现以以此表为例说明随机函数和数组的结合使用。此表为例说明随机函数和数组的结

8、合使用。3.8 数学数学和三角函数和三角函数n产生日期产生日期（1）在）在B2单元格中输入参加工作的最早时间，即在单元格中输入参加工作的最早时间，即在B2单元格中输入单元格中输入“1970/1/1”。（2）选中要产生日期的单元格区域，如）选中要产生日期的单元格区域，如B3:B8。（3）输入公式）输入公式“=B2+RAND()*1825”。（4）按）按Ctrl+Enter键。键。（5）将）将B3:B8格式化为需要的日期格式。格式化为需要的日期格式。n产生加班时间，计算加班工资产生加班时间，计算加班工资n（1）选中）选中C2:C8单元格区域。单元格区域。n（2）输入公式）输入公式“=INT(RAN

9、D()*600)”。n（3）按）按Ctrl+Enter键。键。3.8 数学数学和三角函数和三角函数n（4）一个月共）一个月共10个小时加班，（共个小时加班，（共600分钟）分钟）n 每小时加班工资每小时加班工资10元。元。n 在在E2中输入公式中输入公式“=ROUND(C2/60,0)*10”n 其中其中ROUND 函数将加班时间四舍五入到个位。函数将加班时间四舍五入到个位。3.8 数学数学和三角函数和三角函数n产生基本工资产生基本工资假设基本工资在假设基本工资在8003 000这个范围内，其产生方法如下：这个范围内，其产生方法如下：（1）选择）选择D2:D8单元格区域。单元格区域。（2）输入

10、公式）输入公式“=800+int(RAND()*2200)”。（3）按）按Ctrl+Enter键。键。n产生其它数据产生其它数据假设图中的其他数据都在假设图中的其他数据都在1 000以内，其产生方法如下：以内，其产生方法如下：（1）选择）选择E2:H8单元格区域。单元格区域。（2）输入公式：）输入公式：“=int(RAND()*1000)”。（3）按）按Ctrl+Enter键键 3.9日期日期及时间函数及时间函数 1、Excel处理日期的方式处理日期的方式nMicrosoft Excel 将日期存储为序列号（称为序列值），即一系将日期存储为序列号（称为序列值），即一系列连续的数字编号，每一个数

11、字编号代表一个日期。在默认情况列连续的数字编号，每一个数字编号代表一个日期。在默认情况下，数字下，数字1代表代表1900 年年 1 月月1 日，日，2代表代表1900年年1月月2日，日，39,448代表代表2008 年年 1 月月 1 日，因为从日，因为从1900 年年 1 月月 1 日到日到2008 年年 1 月月 1 日正好日正好 39,448 天。天。nExcel 将时间存储为小数，因为时间被看作天的一部分。时间也将时间存储为小数，因为时间被看作天的一部分。时间也被存为序列号，此序列号以秒为单位递增。因为一天共有：被存为序列号，此序列号以秒为单位递增。因为一天共有：24*60*60=86

12、400秒，所以秒，所以1/86400代表的时间是：代表的时间是：00:00:01，2/86400代表的时间是：代表的时间是：00:00:02。n日期和时间都是数值，因此它们也可以进行各种运算。日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。去另一个日期。3.9日期日期及时间函数及时间函数2DATE函数函数n用法用法nDATE(year,month,day)n功能功能nDATE函数利用所给的参数，构造一个日期序列数函数利用所给的参数，构造一个日期序列数 n例如例如nDATE(2005,3,2

13、1)的结果是的结果是2005-3-213.9日期日期及时间函数及时间函数3YEAR，MONTH，DAY函数函数n用法用法nYEAR(serial_number)nMONTH(serial_number)nDAY(serial_number)n其中的其中的serial_number是一个日期或数字。是一个日期或数字。n功能功能nYEAR函数返回某日期的年份。函数返回某日期的年份。nMONTH函数返回以系列数表示的日期中的月份。函数返回以系列数表示的日期中的月份。nDAY函数返回以系列数表示的某日期的天数，用整函数返回以系列数表示的某日期的天数，用整数数131表示。表示。3.9日期日期及时间函数及

14、时间函数4TODAY、NOW函数函数n用法用法nTODAY()nNOW()n功能功能nTODAY函数返回系统的当前日期。函数返回系统的当前日期。nNOW函数计算当前日期和时间。函数计算当前日期和时间。3.9日期日期及时间函数及时间函数5WEEKDAYn用法用法nWEEKDAY(serial_number,return_type)n其中：其中：nserial_number代表要查找的日期，或日期的系列代表要查找的日期，或日期的系列数，以了解该日期为星期几；数，以了解该日期为星期几；return_type确定返确定返回值类型的数字，。回值类型的数字，。n功能功能nWEEKDAY计算给定的日期是星期

15、几计算给定的日期是星期几 3.9日期日期及时间函数及时间函数6、NETWORKDAYS函数函数n用法用法nNETWORKDAYS(start_date,end_date,holidays)n其中其中nstart_date表示代表开始日期，表示代表开始日期，end_date为终止为终止日期，日期，holidays表示不在工作日历中的一个或多个表示不在工作日历中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。日期所构成的可选区域，如元旦节、五一节、春节。3.9日期日期及时间函数及时间函数n案例案例n【例例】某公司将每个员工的加班时间记录在某公司将每个员工的加班时间记录在Excel的工作表

16、的工作表中，便于计算加班工资。加班工资按小时计算，若加班时中，便于计算加班工资。加班工资按小时计算，若加班时间不足间不足1小时，但超过半小时由按小时，但超过半小时由按1计时计算，不足半小时计时计算，不足半小时则不算加班时间。平时的加班工资每小时则不算加班时间。平时的加班工资每小时10元，节假时则元，节假时则加倍为加倍为20元每小时元每小时。n假设员工的加班时间如图的假设员工的加班时间如图的A1:C11所示，现要计算加班时所示，现要计算加班时长（长（D列），判断加班时间是星期几和节假日（列），判断加班时间是星期几和节假日（E、F列），列），计算工龄（计算工龄（J列）列）3.9日期日期及时间函数及

17、时间函数n（1）在）在D2输入公式：输入公式：=INT(C2-B2)*24*60+30)/60)将此公式向下填充复制将此公式向下填充复制n（2）在）在E2输入公式：输入公式：=WEEKDAY(B2)将此公式向下填充复制将此公式向下填充复制 （3）在）在F2输入公式：输入公式：=IF(OR(E3=1,E3=7),是是,)若加班时间是节日，可直接在对应单元格中输入节日若加班时间是节日，可直接在对应单元格中输入节日 名称名称 将此公式向下填充复制将此公式向下填充复制3.9日期日期及时间函数及时间函数 （4）在）在J2输入公式：输入公式：=YEAR(NOW()-YEAR(I2)将此公式向下填充复制将此

18、公式向下填充复制 weekday返回值类型如下：返回值类型如下：1或者省略或者省略 数字数字1（星期天）到数字（星期天）到数字7（星期六）（星期六）2 数字数字1（星期一）到数字（星期一）到数字7（星期天）（星期天）3 数字数字0（星期一）到数字（星期一）到数字6（星期天）（星期天）3.10 字符字符函数函数1、概述、概述nExcel提供了接近提供了接近30个文本、字符方面的函数，个文本、字符方面的函数，用这些函数对工作表中的文本数据进行查找、用这些函数对工作表中的文本数据进行查找、替换、取子串、数字与文本的转换，以及数据替换、取子串、数字与文本的转换，以及数据的格式化操作等都有十分重要的作用

19、的格式化操作等都有十分重要的作用。2、常用文本类函数、常用文本类函数nRIGHT(text,n)该函数从该函数从text文本的右边取出文本的右边取出n个字符。个字符。nLEFT(text,n)该函数从该函数从text文本的左边取出文本的左边取出n个字符个字符3.10 字符字符函数函数nMID(text,n,m)n该函数从该函数从text文本的第文本的第n个字符起，取出个字符起，取出m个字符。个字符。nREPLACE(text1,n,m,text2)n该函数用该函数用text2替换替换text1文本的第文本的第n个位置开始的个位置开始的m个字符。个字符。nSEARCH(text1,text2,n

20、)n该函数从该函数从text1的第的第n个字符位置开始查找个字符位置开始查找text1中中是否包含是否包含text2，若找就返回，若找就返回text2在在text1中的起中的起始位置。若没有找到则返回错误值始位置。若没有找到则返回错误值“#value!”3.10 字符字符函数函数nTEXT(value,format_text)n该函数指定的格式码该函数指定的格式码format_text将数值型数据将数值型数据Value格式化为文本。其中格式化为文本。其中value可为数字、结果为可为数字、结果为数字的计算公式、包含数字的单元格引用。数字的计算公式、包含数字的单元格引用。nformat_text

21、可为任何数字格式（可为任何数字格式（有关格式码请参有关格式码请参考第考第2章的相关内容章的相关内容）。）。nVALUE(text)n将数字形式的文本转换成数字。将数字形式的文本转换成数字。n比如：比如：VALUE(32)=32，VALUE(09)=9 3.10 字符字符函数函数n案例案例n【例例】文本函数应用举例文本函数应用举例 某银行要建立下图某银行要建立下图3.所示的用户资料表。所示的用户资料表。n其中的其中的出生日期出生日期从身份证号码中提取；从身份证号码中提取；n如果是男性，其尊称就是如果是男性，其尊称就是“X先生先生”，如果是女性，如果是女性，其尊称就是其尊称就是“X女士女士”，X是

22、用户的姓氏；是用户的姓氏；n存款帐号存款帐号是出生日期（去掉出生年的前两位数字）；是出生日期（去掉出生年的前两位数字）；n存款密码存款密码是身份证号码的最后是身份证号码的最后6位数字；位数字；n最后要最后要找出找出解放西路的用户姓名，以便给他们发新解放西路的用户姓名，以便给他们发新的银行卡。的银行卡。3.10 字符字符函数函数n用文本类函数产生用文本类函数产生E2:I9区域的数据。区域的数据。3.10 字符字符函数函数（1）产生出生日期）产生出生日期n在在E2单元格输入公式，然后将它向下复制此公式就能够生成所有单元格输入公式，然后将它向下复制此公式就能够生成所有人的出生日期。人的出生日期。n=

23、MID(D2,7,4)&年年&VALUE(MID(D2,11,2)&月月&MID(D2,13,2)&日日“（2）生成用户的尊称）生成用户的尊称n在在F2单元格输入下面的公式，并将此公式向下复制，生成所有人单元格输入下面的公式，并将此公式向下复制，生成所有人的尊称。的尊称。n=LEFT(A2,1)&IF(B2=女女,女士女士,先生先生)（3）生成存款帐号）生成存款帐号n在在G2单元格输入下面的公式，并将此公式向下复制，单元格输入下面的公式，并将此公式向下复制，生成所有人的存款帐号。生成所有人的存款帐号。n=TEXT(E2,yymmdd)3.10 字符字符函数函数n（4）生成存款密码）生成存款密码

24、n在在H2中输入下面的公式，并向下复制，产生所有人的存款密码。中输入下面的公式，并向下复制，产生所有人的存款密码。n=RIGHT(D2,6)n（5）找出解放西路的用户姓名）找出解放西路的用户姓名n在在I2单元格中输入下面的公式，并向下复制单元格中输入下面的公式，并向下复制n=IF(SEARCH(解放西路解放西路,C2),A2)3.11 错误信息错误信息函数函数1、Excel的常见错误信息的常见错误信息 错错 误误 值值错错 误误 原原 因因#单单元格所含的数字、日期或元格所含的数字、日期或时间时间比比单单元格元格宽宽，或者，或者单单元格的日期、元格的日期、时间时间公式公式产产生了一个生了一个负

25、值负值，就会就会产产生生#错误错误。#VALUE!1.在需要数字或在需要数字或逻辑值时输逻辑值时输入了文本，入了文本，Microsoft Excel 不能将文本不能将文本转换为转换为正确的数据正确的数据类类型。型。#DIV/O!1.输输入的公式中包含明入的公式中包含明显显的除数的除数为为零（零（0），如），如=5/0。2.#NAME?1.在公式中在公式中输输入文本入文本时时没有使用双引号。没有使用双引号。Microsoft 将其解将其解释为释为名称，但名称，但这这些名字没有定些名字没有定义义。2.函数名的拼写函数名的拼写错误错误。#N/A 1.内部函数或自定内部函数或自定义义工作表函数中缺少一

26、个或多个参数。工作表函数中缺少一个或多个参数。2.在数在数组组公式中，所用参数的行数公式中，所用参数的行数或列数与包含数或列数与包含数组组公式的区域的行数或列数不一致。公式的区域的行数或列数不一致。3.在没有排序的数据表中使用了在没有排序的数据表中使用了VLOOKUP，HLOOKUP或或MATCH工作表函数工作表函数查查找数找数值值#REF!删删除了公式中所引用的除了公式中所引用的单单元或元或单单元格区域元格区域#NUM!1.由公式由公式产产生的数字太大或太小，生的数字太大或太小，2.在需要数字参数的函数中使用了非数字参数在需要数字参数的函数中使用了非数字参数#NULL!在公式的两个区域中加入

27、了空格从而求交叉区域，但在公式的两个区域中加入了空格从而求交叉区域，但实际实际上上这这两个区域无重叠区域。两个区域无重叠区域。3.11 错误信息错误信息函数函数n2、Excel错误信息函数错误信息函数 函数名函数名函数功能函数功能ISBLANK（X）判定判定X是否是否为为空白空白单单元格。元格。ISERR（X）判定判定X是否是否为为任意任意错误值错误值（除去（除去#N/A）。）。ISERROR（X）判定判定X是否是否为为任意任意错误值错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或或#NULL!）。）。ISLOGICAL（X）判定判定X是否是否为逻辑值为逻辑值。ISNA（X）判定判定X是否是否为错误值为错误值#N/A（值值不存在）。不存在）。ISNONTEXT（X）判定判定X是否不是文本的任意是否不是文本的任意项项（注意此函数在（注意此函数在值为值为空白空白单单元元格格时时返回返回 TRUE）。）。ISNUMBER（X）判定判定X是否是否为为数字。数字。ISREF（X）判定判定X是否是否为为引用。引用。ISTEXT（X）判定判定X是否是否为为文本。文本。The End