当前位置: 首页 > >

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

发布时间:

Excel数据处理与统计初步 第4版 数据处理与统计初步 版

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

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

3.4 函数简介 函数简介
3、函数调用 、
函数的语法
函数名(参数 参数2, 参数3,…) 函数名 参数1, 参数 参数 参数

在公式中调用函数
嵌套函 数

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)

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

3.7 逻辑函数
2 AND、NOT、OR、TRUE、FALSE函数 、 、 、 、 函数
用法
AND(x1,x2, …,x30) OR(x1, x2, …,x30) NOT(logical)

案例
AND(TRUE,TRUE)=TRUE, , OR(TRUE,TRUE)=TRUE AND(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)=TRUE

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) ……

3.8 数学和三角函数 数学和三角函数
1、RAND() 、 随机函数,产生一个大于等于0小于 小于1均匀 随机函数,产生一个大于等于 小于 均匀 分布的随机数,每次计算工作表时, 分布的随机数,每次计算工作表时,都将 返回一个新的数值。例如, 返回一个新的数值。例如,RAND()*(ba)+a,RAND()*100 , 2、INT(x) 、 取整函数,返回实数x舍入后的整数值 舍入后的整数值。 取整函数,返回实数 舍入后的整数值。例 如,INT(8.9)=8,INT(-8.9)=-9 ,

3.8 数学和三角函数 数学和三角函数
3、ROUND(number,n) 、 四舍五入函数, 四舍五入函数,返回某个数字按指定位数 取整后的数字。例如, 取整后的数字。例如, ROUND(2.15,1)=2.2 ROUND(-1.475,2)=-1.48 ROUND(21.5,0)=22 ROUND(21.5,-1)=20 ROUND(21.5,-2)=0

3.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 数学和三角函数 数学和三角函数
个小时加班,( 分钟) (4)一个月共 个小时加班,(共600分钟) )一个月共10个小时加班,(共 分钟 每小时加班工资10元 每小时加班工资 元。 中输入公式“ 在E2中输入公式“=ROUND(C2/60,0)*10” 中输入公式 ” 其中ROUND 函数将加班时间四舍五入到个位。 函数将加班时间四舍五入到个位。 其中

3.8 数学和三角函数 数学和三角函数
产生基本工资
假设基本工资在800~3 000这个范围内,其产生方法如下: 这个范围内, 假设基本工资在 这个范围内 其产生方法如下:

(1)选择D2:D8单元格区域。 )选择 单元格区域。 单元格区域 (2)输入公式“=800+int(RAND()*2200)”。 )输入公式“ 。 (3)按Ctrl +Enter键。 ) 键

产生其它数据
假设图中的其他数据都在1 以内, 假设图中的其他数据都在 000以内,其产生方法如下: 以内 其产生方法如下:

单元格区域。 (1)选择 )选择E2:H8单元格区域。 单元格区域 (2)输入公式:“=int(RAND()*1000)”。 )输入公式: 。 (3)按Ctrl +Enter键 ) 键

3.9日期及时间函数 日期及时间函数 日期
1、Excel处理日期的方式 、 处理日期的方式
Microsoft Excel 将日期存储为序列号(称为序列值),即一系 将日期存储为序列号(称为序列值), ),即一系 列连续的数字编号,每一个数字编号代表一个日期。 列连续的数字编号,每一个数字编号代表一个日期。在默认情况 数字1代表 代表1900 年 1 月1 日,2代表 代表1900年1月2 下,数字 代表 代表 年 月 代表2008 年 1 月 1 日,因为从 因为从1900 年 1 月 日,……39,448代表 代表 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-21 的结果是

3.9日期及时间函数 日期及时间函数 日期
3.YEAR,MONTH,DAY函数 . , , 函数
用法
YEAR(serial_number) MONTH(serial_number) DAY(serial_number) 其中的serial_number是一个日期或数字。 其中的 是一个日期或数字。 是一个日期或数字

功能
YEAR函数返回某日期的年份。 函数返回某日期的年份。 函数返回某日期的年份 MONTH函数返回以系列数表示的日期中的月份。 函数返回以系列数表示的日期中的月份。 函数返回以系列数表示的日期中的月份 DAY函数返回以系列数表示的某日期的天数,用整 函数返回以系列数表示的某日期的天数, 函数返回以系列数表示的某日期的天数 表示。 数1~31表示。 表示

3.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日期及时间函数 日期及时间函数 日期
案例
【例】某公司将每个员工的加班时间记录在Excel的工作表 某公司将每个员工的加班时间记录在 的工作表 便于计算加班工资。加班工资按小时计算, 中,便于计算加班工资。加班工资按小时计算,若加班时 间不足1小时 但超过半小时由按1计时计算 小时, 计时计算, 间不足 小时,但超过半小时由按 计时计算,不足半小时 则不算加班时间。*时的加班工资每小时10元 则不算加班时间。*时的加班工资每小时 元,节假时则 加倍为20元每小时 加倍为 元每小时 。

假设员工的加班时间如图的A1:C11所示,现要计算加班时 所示, 假设员工的加班时间如图的 所示 判断加班时间是星期几和节假日( 、 列 长(D列),判断加班时间是星期几和节假日(E、F列), 列),判断加班时间是星期几和节假日 计算工龄( 列 计算工龄(J列)

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 字符函数 字符函数
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!”

3.10 字符函数 字符函数
TEXT(value,format_text)
该函数指定的格式码format_text将数值型数据 将数值型数据 该函数指定的格式码 Value格式化为文本。其中value可为数字、结果 格式化为文本。其中 可为数字、 格式化为文本 可为数字 为数字的计算公式、包含数字的单元格引用。 为数字的计算公式、包含数字的单元格引用。 format_text可为任何数字格式(有关格式码请参 可为任何数字格式( 可为任何数字格式 考第2章的相关内容 章的相关内容)。 考第 章的相关内容)。

VALUE(text)
将数字形式的文本转换成数字。 将数字形式的文本转换成数字。 比如: 比如:VALUE("32")=32,VALUE("09")=9 ,

3.10 字符函数 字符函数
案例
某银行要建立下图3. 【例】文本函数应用举例 某银行要建立下图 所示的用户资料表。 所示的用户资料表。
其中的出生日期从身份证号码中提取; 其中的出生日期从身份证号码中提取; 出生日期从身份证号码中提取 如果是男性,其尊称就是“ 先生 先生” 如果是女性, 如果是男性,其尊称就是“X先生”,如果是女性, 其尊称就是“ 女士 女士” 是用户的姓氏; 其尊称就是“X女士”,X是用户的姓氏; 是用户的姓氏 存款帐号是出生日期 去掉出生年的前两位数字); 是出生日期( 存款帐号是出生日期(去掉出生年的前两位数字); 存款密码是身份证号码的最后 位数字; 是身份证号码的最后6位数字 存款密码是身份证号码的最后 位数字; 最后要找出解放西路的用户姓名, 找出解放西路的用户姓名 最后要找出解放西路的用户姓名,以便给他们发新 的银行卡。 的银行卡。

3.10 字符函数 字符函数
用文本类函数产生E2:I9区域的数据。 用文本类函数产生 区域的数据。 区域的数据

3.10 字符函数 字符函数
(1)产生出生日期 )
单元格输入公式, 在E2单元格输入公式,然后将它向下复制此公式就能够生成所有 单元格输入公式 人的出生日期。 人的出生日期。 =MID(D2,7,4)&"年"&VALUE(MID(D2,11,2))&"月 年 月 "&MID(D2,13,2)&"日“ 日

(2)生成用户的尊称 )
F2单元格输入下面的公式 并将此公式向下复制, 单元格输入下面的公式, 在F2单元格输入下面的公式,并将此公式向下复制,生成所有人 的尊称。 的尊称。 =LEFT(A2,1)&IF(B2="女","女士 先生 女士","先生 女 女士 先生")

(3)生成存款帐号 )
单元格输入下面的公式, 在G2单元格输入下面的公式,并将此公式向下复制, 单元格输入下面的公式 并将此公式向下复制, 生成所有人的存款帐号。 生成所有人的存款帐号。 =TEXT(E2,"yymmdd")

3.10 字符函数 字符函数
(4)生成存款密码 )
中输入下面的公式, 在H2中输入下面的公式,并向下复制,产生所有人的存款密码。 中输入下面的公式 并向下复制,产生所有人的存款密码。 =RIGHT(D2,6)

(5)找出解放西路的用户姓名 )
单元格中输入下面的公式, 在I2单元格中输入下面的公式,并向下复制 单元格中输入下面的公式 =IF(SEARCH("解放西路 =IF(SEARCH("解放西路",C2),A2) 解放西路",C2),A2)

3.11 错误信息函数 错误信息函数
1、 Excel的常见错误信息 、 的常见错误信息
错误值 ##### #VALUE! #DIV/O! #NAME? 错误原因 单元格所含的数字、日期或时间比单元格宽,或者单元格的日期、时间公式产生了一个负值, 单元格所含的数字、日期或时间比单元格宽,或者单元格的日期、时间公式产生了一个负值, 错误。 就会产生 ##### 错误。 1. 在需要数字或逻辑值时输入了文本,Microsoft Excel 不能将文本转换为正确的数据类型。 在需要数字或逻辑值时输入了文本, 不能将文本转换为正确的数据类型。 1. 输入的公式中包含明显的除数为零(0),如 =5/0。2. 输入的公式中包含明显的除数为零( ),如 。 ), 1. 在公式中输入文本时没有使用双引号。Microsoft 将其解释为名称,但这些名字没有定义。 在公式中输入文本时没有使用双引号。 将其解释为名称,但这些名字没有定义。 2. 函数名的拼写错误。 函数名的拼写错误。 1. 内部函数或自定义工作表函数中缺少一个或多个参数。2. 在数组公式中,所用参数的行数 内部函数或自定义工作表函数中缺少一个或多个参数。 在数组公式中, 或列数与包含数组公式的区域的行数或列数不一致。 或列数与包含数组公式的区域的行数或列数不一致。3. 在没有排序的数据表中使用了 VLOOKUP,HLOOKUP或MATCH工作表函数查找数值 , 或 工作表函数查找数值 删除了公式中所引用的单元或单元格区域 1. 由公式产生的数字太大或太小,2. 在需要数字参数的函数中使用了非数字参数 由公式产生的数字太大或太小, 在公式的两个区域中加入了空格从而求交叉区域,但实际上这两个区域无重叠区域。 在公式的两个区域中加入了空格从而求交叉区域,但实际上这两个区域无重叠区域。

#N/A #REF! #NUM! #NULL!

3.11 错误信息函数 错误信息函数
2、 Excel错误信息函数 、 错误信息函数
函数名 ISBLANK(X) ( ) ISERR(X) ( ) ISERROR(X) ( ) ISLOGICAL(X) ( ) ISNA(X) ( ) ISNONTEXT(X) ( ) ISNUMBER(X) ( ) ISREF(X) ( ) ISTEXT(X) ( ) 函数功能 判定X是否为空白单元格。 判定 是否为空白单元格。 是否为空白单元格 判定X是否为任意错误值( 判定 是否为任意错误值(除去 #N/A)。 是否为任意错误值 )。 判定X是否为任意错误值( 判定 是否为任意错误值(#N/A、#VALUE!、#REF!、 是否为任意错误值 、 、 、 #DIV/0!、#NUM!、#NAME? 或 #NULL!)。 、 、 )。 判定X是否为逻辑值。 判定 是否为逻辑值。 是否为逻辑值 判定X是否为错误值 判定 是否为错误值 #N/A(值不存在)。 (值不存在)。 判定X是否不是文本的任意项( 判定 是否不是文本的任意项(注意此函数在值为空白单元 是否不是文本的任意项 格时返回 TRUE)。 )。 判定X是否为数字。 判定 是否为数字。 是否为数字 判定X是否为引用。 判定 是否为引用。 是否为引用 判定X是否为文本。 判定 是否为文本。 是否为文本

The End




友情链接: