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

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

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

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

4、TRUE、FALSE函数函数用法用法AND(x1,x2, ,x30)OR(x1, x2, ,x30)NOT(logical)案例案例AND(TRUE,TRUE)=TRUE，OR(TRUE,TRUE)=TRUEAND(TRUE,FALSE,TRUE, TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。如果如果B1，B2，B3单元格中的值为单元格中的值为 TRUE，FALSE，T R U E ， 则， 则 A N D ( B 1 : B 3 ) = F A L S E ， 但， 但OR(B1:B3)=TRUE。AND(2,2+3=5)=TRUE3.8 数学和三角函

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

6、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】用随机函数产生大量的实验数据，】用随机函数产生大量的实验数据，计算排名。计算排名。问题：有一张工资工作表，结构如图所示。问题：有一张工资工作表，结构如图所示。现以此表为例说明随机函数和数组的结合现以此表为例说明随机函数和数组的结合使用。使用。 3.8 数学和三角函数数

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

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

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

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

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

13、3YEAR，MONTH，DAY函数函数用法用法YEAR(serial_number)MONTH(serial_number)DAY(serial_number)其中的其中的serial_number是一个日期或数字。是一个日期或数字。功能功能YEAR函数返回某日期的年份。函数返回某日期的年份。MONTH函数返回以系列数表示的日期中的函数返回以系列数表示的日期中的月份。月份。DAY函数返回以系列数表示的某日期的天数，函数返回以系列数表示的某日期的天数，用整数用整数131表示。表示。 3.9日期及时间函数日期及时间函数4TODAY、NOW函数函数用法用法TODAY( )NOW( )功能功能TODA

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

15、ETWORKDAYS(start_date, end_date, holidays) 其中其中start_date表示代表开始日期，表示代表开始日期，end_date为终止日期，为终止日期， holidays表示不在工作日历表示不在工作日历中的一个或多个日期所构成的可选区域，中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。如元旦节、五一节、春节。 3.9日期及时间函数日期及时间函数n案例案例n【例】某公司将每个员工的加班时间记录在【例】某公司将每个员工的加班时间记录在Excel的工作表中，便于计算加班工资。加班的工作表中，便于计算加班工资。加班工资按小时计算，若加班时间不足工资按小

16、时计算，若加班时间不足1小时，但小时，但超过半小时由按超过半小时由按1计时计算，不足半小时则不计时计算，不足半小时则不算加班时间。平时的加班工资每小时算加班时间。平时的加班工资每小时10元，节元，节假时则加倍为假时则加倍为20元每小时元每小时 。n假设员工的加班时间如图的假设员工的加班时间如图的A1:C11所示，现所示，现要计算加班时长要计算加班时长D列），判断加班时间是星列），判断加班时间是星期几和节假日期几和节假日E、F列），计算工龄列），计算工龄J列）列）3.9日期及时间函数日期及时间函数n（1在在D2输入公式：输入公式： n =INT(C2-B2)*24*60+30)/60)n 将此公

17、式向下填充复制将此公式向下填充复制n（2在在E2输入公式：输入公式：n =WEEKDAY(B2)n 将此公式向下填充复制将此公式向下填充复制n （3在在F2输入公式：输入公式：n =IF(OR(E3=1,E3=7),是是,)n 若加班时间是节日，可直接在对应单元格中输入节若加班时间是节日，可直接在对应单元格中输入节日日 称号称号n 将此公式向下填充复制将此公式向下填充复制3.9日期及时间函数日期及时间函数 （4在在J2输入公式：输入公式： =YEAR(NOW()-YEAR(I2) 将此公式向下填充复制将此公式向下填充复制 weekday返回值类型如下：返回值类型如下： 1或者省略或者省略 数字

18、数字1星期天到数字星期天到数字7星期六）星期六） 2 数字数字1星期一到数字星期一到数字7星期天）星期天） 3 数字数字0星期一到数字星期一到数字6星期天）星期天）3.10 字符函数字符函数1、概述、概述Excel提供了接近提供了接近30个文本、字符方面的函个文本、字符方面的函数，用这些函数对工作表中的文本数据进数，用这些函数对工作表中的文本数据进行查找、交换、取子串、数字与文本的转行查找、交换、取子串、数字与文本的转换，以及数据的格式化操作等都有十分重换，以及数据的格式化操作等都有十分重要的作用要的作用 。2、常用文本类函数、常用文本类函数RIGHT(text,n)该函数从该函数从text文

19、本的右边取出文本的右边取出n个字符。个字符。LEFT(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)n该函数从text1的第n个字符位置开始查找text1中是否包含text2，若找就返回text2在text1中的起始位置。若没有找到则返回错误值“#value!”3.10 字符函数字符函数nTEXT(

20、value,format_text)n该函数指定的格式码format_text将数值型数据Value格式化为文本。其中value可为数字、结果为数字的计算公式、包含数字的单元格引用。nformat_text可为任何数字格式有关格式码请参考第2章的相关内容）。nVALUE(text)n将数字形式的文本转换成数字。n比如：VALUE(32)=32，VALUE(09)=9 3.10 字符函数字符函数n案例案例n【例】文本函数应用举例【例】文本函数应用举例 某银行要建立下某银行要建立下图图3.所示的用户资料表。所示的用户资料表。n其中的出生日期从身份证号码中提取；其中的出生日期从身份证号码中提取；n如

21、果是男性，其尊称就是如果是男性，其尊称就是“X先生先生”，如果，如果是女性，其尊称就是是女性，其尊称就是“X女士女士”，X是用户是用户的姓氏；的姓氏；n存款帐号是出生日期去掉出生年的前两存款帐号是出生日期去掉出生年的前两位数字）；位数字）；n存款密码是身份证号码的最后存款密码是身份证号码的最后6位数字；位数字；n最后要找出解放西路的用户姓名，以便给最后要找出解放西路的用户姓名，以便给他们发新的银行卡。他们发新的银行卡。3.10 字符函数字符函数n用文本类函数产生用文本类函数产生E2:I9区域的数据。区域的数据。3.10 字符函数字符函数（1产生出生日期产生出生日期在在E2单元格输入公式，然后将

22、它向下复制此单元格输入公式，然后将它向下复制此公式就能够生成所有人的出生日期。公式就能够生成所有人的出生日期。=MID(D2,7,4)&年年&VALUE(MID(D2,11,2)&月月&MID(D2,13,2)&日日“（2生成用户的尊称生成用户的尊称在在F2单元格输入下面的公式，并将此公式向单元格输入下面的公式，并将此公式向下复制，生成所有人的尊称。下复制，生成所有人的尊称。=LEFT(A2,1)&IF(B2=女女,女士女士,先生先生)（3生成存款帐号生成存款帐号在在G2单元格输入下面的公式，并将此公式向单元格输入下面的公式，并将此公式向下复制，

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

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