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

第3章公式、名字与函数Excel数据处理与统计初步第4版3.4函数简介1、函数的概念函数是能够完成特定功能的程序。在Excel中，它是系统预定义的一些公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算，然后把计算的结果存放在某个单元格中。在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组或工作表的信息3.4函数简介2、Excel函数分类分类功能简介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数3.7

逻辑函数1、Excel的比较运算符比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么“正确”，要么“错误”，不可能有第三种结果比较运算符含义示例=等于a1=3>大于a1>b1<小于a1<1>=大于等于a1>=60比较运算符含义示例<=小于等于a1<=”dd”<>不等于a1<>03.7

逻辑函数2AND、NOT、OR、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，TRUE，则AND(B1:B3)=FALSE，但OR(B1:B3)=TRUE。AND(2,2+3=5)=TRUE3.8数学和三角函数1、概述Excel提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。2、常见的数学函数MOD、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(array1,array2)……3.8数学和三角函数3、ROUND(number,n)四舍五入函数，返回某个数字按指定位数取整后的数字。例如，ROUND(2.15,1)=2.2ROUND(-1.475,2)=-1.48ROUND(21.5,0)=22ROUND(21.5,-1)=20ROUND(21.5,-2)=03.8数学和三角函数3、数学函数应用案例【例1】用随机函数产生大量的实验数据，计算排名。问题：有一张工资工作表，结构如图所示。现以此表为例说明随机函数和数组的结合使用。3.8数学和三角函数产生日期（1）在B2单元格中输入参加工作的最早时间，即在B2单元格中输入“1970/1/1”。（2）选中要产生日期的单元格区域，如B3:B8。（3）输入公式“=B2+RAND()*1825”。（4）按Ctrl+Enter键。（5）将B3:B8格式化为需要的日期格式。产生加班时间，计算加班工资（1）选中C2:C8单元格区域。（2）输入公式“=INT(RAND()*600)”。（3）按Ctrl+Enter键。3.8数学和三角函数产生基本工资假设基本工资在800~3000这个范围内，其产生方法如下：（1）选择D2:D8单元格区域。（2）输入公式“=800+int(RAND()*2200)”。（3）按Ctrl+Enter键。产生其它数据假设图中的其他数据都在1000以内，其产生方法如下：（1）选择E2:H8单元格区域。（2）输入公式：“=int(RAND()*1000)”。（3）按Ctrl+Enter键3.9日期及时间函数1、Excel处理日期的方式MicrosoftExcel将日期存储为序列号（称为序列值），即一系列连续的数字编号，每一个数字编号代表一个日期。在默认情况下，数字1代表1900年1月1日，2代表1900年1月2日，……39,448代表2008年1月1日，因为从1900年1月1日到2008年1月1日正好39,448天。Excel将时间存储为小数，因为时间被看作天的一部分。时间也被存为序列号，此序列号以秒为单位递增。因为一天共有：24*60*60=86400秒，所以1/86400代表的时间是：00:00:01，2/86400代表的时间是：00:00:02……。日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。3.9日期及时间函数2．DATE函数用法DATE(year,month,day)功能DATE函数利用所给的参数，构造一个日期序列数例如DATE(2005,3,21)的结果是2005-3-213.9日期及时间函数4．TODAY、NOW函数用法TODAY()NOW()功能TODAY函数返回系统的当前日期。NOW函数计算当前日期和时间。3.9日期及时间函数5．WEEKDAY用法WEEKDAY(serial_number,return_type)其中：serial_number代表要查找的日期，或日期的系列数，以了解该日期为星期几；return_type确定返回值类型的数字，。功能WEEKDAY计算给定的日期是星期几3.9日期及时间函数6、NETWORKDAYS函数用法NETWORKDAYS(start_date,end_date,holidays)其中start_date表示代表开始日期，end_date为终止日期，holidays表示不在工作日历中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。3.9日期及时间函数（1）在D2输入公式：=INT(((C2-B2)*24*60+30)/60)将此公式向下填充复制（2）在E2输入公式：=WEEKDAY(B2)将此公式向下填充复制（3）在F2输入公式：

=IF(OR(E3=1,E3=7),"是","")若加班时间是节日，可直接在对应单元格中输入节日名称将此公式向下填充复制3.9日期及时间函数

（4）在J2输入公式：=YEAR(NOW())-YEAR(I2)将此公式向下填充复制weekday返回值类型如下：1或者省略——数字1（星期天）到数字7（星期六）2——数字1（星期一）到数字7（星期天）3——数字0（星期一）到数字6（星期天）3.10字符函数1、概述Excel提供了接近30个文本、字符方面的函数，用这些函数对工作表中的文本数据进行查找、替换、取子串、数字与文本的转换，以及数据的格式化操作等都有十分重要的作用。2、常用文本类函数RIGHT(text,n)该函数从text文本的右边取出n个字符。LEFT(text,n)该函数从text文本的左边取出n个字符3.10字符函数TEXT(value,format_text)该函数指定的格式码format_text将数值型数据Value格式化为文本。其中value可为数字、结果为数字的计算公式、包含数字的单元格引用。format_text可为任何数字格式（有关格式码请参考第2章的相关内容）。VALUE(text)将数字形式的文本转换成数字。比如：VALUE("32")=32，VALUE("09")=93.10字符函数案例【例】文本函数应用举例某银行要建立下图3.所示的用户资料表。其中的出生日期从身份证号码中提取；如果是男性，其尊称就是“X先生”，如果是女性，其尊称就是“X女士”，X是用户的姓氏；存款帐号是出生日期（去掉出生年的前两位数字）；存款密码是身份证号码的最后6位数字；最后要找出解放西路的用户姓名，以便给他们发新的银行卡。3.10字符函数（4）生成存款密码在H2中输入下面的公式，并向下复制，产生所有人的存款密码。=RIGHT(D2,6)（5）找出解放西路的用户姓名在I2单元格中输入下面的公式，并向下复制=IF(SEARCH("解放西路",C2),A2)3.11错误信息函数1、Excel的常见错误信息错误值错误原因#####单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了一个负值，就会产生#####错误。#VALUE!

1.在需要数字或逻辑值时输入了文本，MicrosoftExcel不能将文本转换为正确的数据类型。#DIV/O!

1.输入的公式中包含明显的除数为零（0），如=5/0。2.#NAME?

1.在公式中输入文本时没有使用双引号。Microsoft将其解释为名称，但这些名字没有定义。2.函数名的拼写错误。#N/A

1.内部函数或自定义工作表函数中缺少一个或多个参数。2.在数组公式中，所用参数的行数或列数与包含数组公式的区域的行数或列数不一致。3.在没有排序的数据表中使用了VLOOKUP，HLOOKUP或MATCH工作表函数查找数值#REF!删除了公式中所引用的单元或单元格区域#NUM!1.由公式产生的数字太大或太小，2.在需要数字参数的函数中使用了非数字参数#NULL!在公式的两个区域中加入了空格从而求交叉区域，但实际上这两个区域无重叠区域。3.11错误信息函数2、Excel错误信息函数函数名函数功能ISBLANK（X）判定X是否为空白单元格。ISERR（X）判定X是否为任意错误值（除去#N/A）。ISERROR（X）判定X是否为任意错误值（#N/A、#VA