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

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

逻辑函数1、Excel的比较运算符比较运算又称关系运算，就是人们常说的比较式。比较运算只有两种不同的结果，要么“正确”，要么“错误”，不可能有第三种结果比较运算符含义示例=等于a1=3>大于a1>b1<小于a1<1>=大于等于a1>=60比较运算符含义示例<=小于等于a1<=”dd”<>不等于a1<>0Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第4页!3.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)……Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第5页!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)=0Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第6页!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键。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第7页!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键Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第8页!3.9日期及时间函数2．DATE函数用法DATE(year,month,day)功能DATE函数利用所给的参数，构造一个日期序列数例如DATE(2005,3,21)的结果是2005-3-21Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第9页!3.9日期及时间函数4．TODAY、NOW函数用法TODAY()NOW()功能TODAY函数返回系统的当前日期。NOW函数计算当前日期和时间。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第10页!3.9日期及时间函数6、NETWORKDAYS函数用法NETWORKDAYS(start_date,end_date,holidays)其中start_date表示代表开始日期，end_date为终止日期，holidays表示不在工作日历中的一个或多个日期所构成的可选区域，如元旦节、五一节、春节。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第11页!3.9日期及时间函数（1）在D2输入公式：=INT(((C2-B2)*24*60+30)/60)将此公式向下填充复制（2）在E2输入公式：=WEEKDAY(B2)将此公式向下填充复制（3）在F2输入公式：

=IF(OR(E3=1,E3=7),"是","")若加班时间是节日，可直接在对应单元格中输入节日名称将此公式向下填充复制Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第12页!3.10字符函数1、概述Excel提供了接近30个文本、字符方面的函数，用这些函数对工作表中的文本数据进行查找、替换、取子串、数字与文本的转换，以及数据的格式化操作等都有十分重要的作用。2、常用文本类函数RIGHT(text,n)该函数从text文本的右边取出n个字符。LEFT(text,n)该函数从text文本的左边取出n个字符Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第13页!3.10字符函数TEXT(value,format_text)该函数指定的格式码format_text将数值型数据Value格式化为文本。其中value可为数字、结果为数字的计算公式、包含数字的单元格引用。format_text可为任何数字格式（有关格式码请参考第2章的相关内容）。VALUE(text)将数字形式的文本转换成数字。比如：VALUE("32")=32，VALUE("09")=9Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第14页!3.10字符函数用文本类函数产生E2:I9区域的数据。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第15页!3.10字符函数（4）生成存款密码在H2中输入下面的公式，并向下复制，产生所有人的存款密码。=RIGHT(D2,6)（5）找出解放西路的用户姓名在I2单元格中输入下面的公式，并向下复制=IF(SEARCH("解放西路",C2),A2)Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第16页!3.11错误信息函数2、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是否为文本。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第17页!3.4函数简介3、函数调用函数的语法函数名(参数1,参数2,参数3,…)在公式中调用函数

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)嵌套函数Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第18页!3.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)=TRUEExcel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第19页!3.8数学和三角函数1、RAND()随机函数，产生一个大于等于0小于1均匀分布的随机数，每次计算工作表时，都将返回一个新的数值。例如，RAND()*(b-a)+a，RAND()*1002、INT(x)取整函数，返回实数x舍入后的整数值。例如，INT(8.9)=8，INT(-8.9)=-9Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第20页!3.8数学和三角函数3、数学函数应用案例【例1】用随机函数产生大量的实验数据，计算排名。问题：有一张工资工作表，结构如图所示。现以此表为例说明随机函数和数组的结合使用。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第21页!3.8数学和三角函数（4）一个月共10个小时加班，（共600分钟）

每小时加班工资10元。在E2中输入公式“=ROUND(C2/60,0)*10”

其中ROUND函数将加班时间四舍五入到个位。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第22页!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……。日期和时间都是数值，因此它们也可以进行各种运算。如果要计算两个日期之间的差值，可以用一个日期减去另一个日期。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第23页!3.9日期及时间函数3．YEAR，MONTH，DAY函数用法YEAR(serial_number)MONTH(serial_number)DAY(serial_number)其中的serial_number是一个日期或数字。功能YEAR函数返回某日期的年份。MONTH函数返回以系列数表示的日期中的月份。DAY函数返回以系列数表示的某日期的天数，用整数1~31表示。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第24页!3.9日期及时间函数5．WEEKDAY用法WEEKDAY(serial_number,return_type)其中：serial_number代表要查找的日期，或日期的系列数，以了解该日期为星期几；return_type确定返回值类型的数字，。功能WEEKDAY计算给定的日期是星期几Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第25页!3.9日期及时间函数案例【例】某公司将每个员工的加班时间记录在Excel的工作表中，便于计算加班工资。加班工资按小时计算，若加班时间不足1小时，但超过半小时由按1计时计算，不足半小时则不算加班时间。平时的加班工资每小时10元，节假时则加倍为20元每小时。假设员工的加班时间如图的A1:C11所示，现要计算加班时长（D列），判断加班时间是星期几和节假日（E、F列），计算工龄（J列）Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第26页!3.9日期及时间函数

（4）在J2输入公式：=YEAR(NOW())-YEAR(I2)将此公式向下填充复制weekday返回值类型如下：1或者省略——数字1（星期天）到数字7（星期六）2——数字1（星期一）到数字7（星期天）3——数字0（星期一）到数字6（星期天）Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第27页!3.10字符函数MID(text,n,m)该函数从text文本的第n个字符起，取出m个字符。REPLACE(text1,n,m,text2)该函数用text2替换text1文本的第n个位置开始的m个字符。SEARCH(text1,text2,n)该函数从text1的第n个字符位置开始查找text1中是否包含text2，若找就返回text2在text1中的起始位置。若没有找到则返回错误值“#value!”Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第28页!3.10字符函数案例【例】文本函数应用举例某银行要建立下图3.所示的用户资料表。其中的出生日期从身份证号码中提取；如果是男性，其尊称就是“X先生”，如果是女性，其尊称就是“X女士”，X是用户的姓氏；存款帐号是出生日期（去掉出生年的前两位数字）；存款密码是身份证号码的最后6位数字；最后要找出解放西路的用户姓名，以便给他们发新的银行卡。Excel数据处理与统计初步_第4版_第3章_公式名字与函数共32页，您现在浏览的是第29页!3.10字符函数（1）产生出生日期在E2单元格输入公式，然后将它向下复制此公式就能够生成所有人的出生日期。=MID(D2,7,4)&"年"&VALUE(MID(D2,11,2))&"月"&MID(D2,13,2)&"日“（2）生成用户的尊称在F2单元格输入下面的公式，并将此公式向下复制，生成所有人的尊称