EXCEL中常用函数及使用方法
EXCEL中常用函数及使用方法
Excel函数一共有11类:数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻借函数.查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自泄义函数。1?数据库函数
当需要分析数据淸单中的数值是否符合特主条件时,可以使用数据库工作表函数/列如, 在一个包含销售信息的数据淸单中,可以计算出所有销售数值大于1,000且小于2,500的行或记录的总数o Microsoft Excel共有12个工作表函数用于对存储在数据淸单或数拯库中的数拯进行分析,这些函数的统一名称为Dfunctions,也称为D函数,每个函数均有三个相同的参数:databasex field和criteria。这些参数指向数据库函数所使用的工作表区域。其中参数database为工作表上包含数据淸单的区域。参数field为需要汇总的列的标志。参数criteria 为工作表上包含指能条件的区域。
2?日期与时间函数
通过日期与时间函数,可以在公式中分析和处理日期值和时间值。
3.工程函数
工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型:对复数进行处理的函数、在不同的数字系统(如十进制系统、十六进制系统.八进制系统和二进制系统)间进行数值转换的函数.在不同的度量系统中进行数值转换的函数。
4?财务函数
财务函数可以进行一般的财务计算,如确泄贷款的支付额、投资的未来值或净现值,以及债券或息票的价值。财务函数中常见的参数:
未来值(fv)—在所有付款发生后的投资或贷款的价值。
期间数(nper) 一投资的总支付期间数。
付款(pmt)-对于一项投资或贷款的左期支付数额。
现值(pv)—在投资期初的投资或贷款的价值。例如,贷款的现值为所借入的本金数额。
利率(rate)-投资或贷款的利率或贴现率。
类型(type)-付款期间内进行支付的间隔,如在月初或月末。
5?信息函数
可以使用信息工作表函数确左存储在单元格中的数据的类型。信息函数包含一组称为 IS
的工作表函数,在单元格满足条件时返回TRUE.例如,如果单元格包含一个偶数值, ISEVEN工作表函数返回TRUE。如果需要确定某个单元格区威中是否存在空白单元格,可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数,或者使用ISBLANK 工作表函数确定区域中的某个单元格是否为空。
6.逻辑函数
使用逻辑函数可以进行真假值判断,或者进行复合检验。例如.可以使用IF函数确定条件为真还是假,并由此返回不同的数值。
7.査询和引用函数
当需要在数拯淸单或表格中查找特泄数值,或者需要查找某一单元格的引用时,可以使用查询和引用工作表函数。例如,如果需要在表格中查找与第一列中的值相匹配的数值,可以使用VL00KUP工作表函数。如果需要确龙数据淸单中数值的位宜,可以使用MATCH工作表函数。
&数学和三角函数
通过数学和三角函数,可以处理简单的计算,例如对数字取整、计算单元格区域中的数值总和或复杂计算。
9.统计函数
统计工作表函数用于对数据区域进行统计分析。例如,统计工作表函数可以提供由一组给左值绘制岀的直线的相关信息,如直线的斜率和y轴截距,或构成直线的实际点数值。
10.文本函数
通过文本函数,可以在公式中处理文字串。例如,可以改变大小写或确肚文字串的长度。可以将日期插入文字串或连接在文字串上。下面的公式为一个示例,借以说明如何使用函数TODAY和函数TEXT来创建一条信息,该信息包含着当前日期并将日期以"dd-mm-yy"的格式表
示。
11?用户自定义函数
如果要在公式或计算中使用特别复杂的汁算,而工作表函数又无法满足需要,则需要创建用户自泄义函数。这些函数,称为用戸自定义函数,可以通过使用Visual Basic for Applications 来创建。
下面介绍EXCEL中常用函数及使用方法
1、AND函数
函数名称:AND
主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE) ”,则返回逻辑“真(TRUE) ”,反之返回逻辑“假(FALSE) ”。
使用格式:AND(logicall, logical, ...)参数说明: Logical 1, Logical2, Logical3 ..................... :表示待测试的条件值或表达式,
最多这30个。
应用举例:在C5单元格输入公式:=AND(A5>=60, B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回 FALSE,说
明A5和B5中的数值至少有一个小于60。
特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE! ” 或“#NAME”。
2、OR函数
函数名称:0R
主要功能:返回逻辑值,仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻辑“假(FALSE) ”,否则都返回逻辑“真(TRUE) ”。
使用格式:0R(logicall, logical2,...)
参数说明:Logicall, Logical2, Logical3 . :表示待测试的条件值或表达式,最多这30个。应用举例:在C62单元格输入公式:
=0R(A62>=60,B62>=60),确认。如果 C62 中返回 TRUE,说明 A62 和
B62中的数值至少有一个大于或等于60,如果返回FALSE,说明A62 和
B62中的数值都小于60o
特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE! ” 或“#NAME”。
3、ABS函数
函数名称:ABS
主要功能:求出相应数字的绝对值。
使用格式:ABS(number)参数说明:number代表需要求绝对值的数值或引用的单元格。
应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如T00),B2中均显示出正数(如100)。特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值"#VALUE! ” o
4、INT函数
函数名称:INT
主要功能:将数值向下取整为最接近的整数。
使用格式:INT (number)参数说明:number表示需要取整的数值或包含数值的
引用单元格。
应用举例:输入公式:=INT(1 & 89),确认后显示出18。
特别提醒:在取整时,不进行四舍五入;如果输入的公式为=INT(-18. 89),则返回结果为-19。
5、AVERAGE 函数
函数名称:AVERAGE主要功能:求出所有参数的算术平均值。
使用格式:AVERAGE (number 1, numb er 2, )
参数说明:numberl, number2,……:需要求平均值的数值或引用单元格(区域),参数不超过30个。
应用举例:在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7, 7, 8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。特别提醒:如果引用区域中包含“0"值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。
6、SUM函数
函数名称:SUM
主要功能:计算所有参数数值的和。
使用格式:SUM (Number 1, Numb er 2 )
参数说明:Number 1. Number2……代表需要计算的值,可以是具体的数值、引用的单元格(区域)、逻辑值等。
应用举例:如图7所示,在D64单元格中输入公式:=SUM(D2:D63),确认后即可求出语文的总分。
特别提醒:如果参数为数巔引用,只有其中的数字将被计?算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略;如果将上述公式修改
为:=SUM(LARGE(D2:D63, {1, 2, 3, 4, 5})),则可以求出前 5 名成
绩的和。
7、SUMIF
用途:根拯指左条件对若干单元格、区域或引用求和。
语法:SUMIF(range, criteria, sum_range)
参数:Range为用于条件判断的单元庙区域,Criteria是由数字、逻辑表达式等组成的判泄条件,Sum.range为需要求和的单元格、区域或引用。
实例:某单位统讣工资报表中职称为“中级'’的员工工资总额。假设工资总额存放在工作表
的F列,员工职称存放在工作表B列。则公式为“ =SUMIF(Bl:B1000,"中级S Fl:F1000)", 其中“Bl:B100(T为提供逻辑判断依据的单元格区域,〃中级”为判断条件,就是仅仅统计
Bl:B1000区域中职称为“中级”的单元格,Fl:F1000为实际求和的单元格区域。
8、COUNT
用途:返回数字参DATEDIF数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。
语法:COUNT (valueL value2,???)?
参数:Valuel, value2, ?…是包含或引用各种类型数据的参数(1?30个),其中只有数字类型的数拯才能被统计。
实例:如果 A1 二90、A2二人数、A3二"".A4=54> A5=36,则公式“=COUNT(A1:A5) ” 返回3。
9、COUNTA
用途:返回参数组中非空值的数目。利用函数COUNTA可以讣算数组或单元格区域中数据项的个数。
语法:COUNTA(valueL value2> ...)
说明:Valueb value2,???所要计数的值,参数个数为1?30个。在这种情况下的参数可以是任何类型,它们包括空格但不包括空白单元格。如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略匚如果不需要统讣逻辑:值、文字或错误值,则应该使用COUNT 函数。
实例:如果A1二6.28、A2=3. 74,其余单元格为空,则公式“ =COUNTA(A1:A7) 的计算结果等于2。
10、COUNTIF 函数
函数名称:COUNTIF
主要功能:统计某个单元格区域中符合指定条件的单元格数目。
使用格式:COUNTIF(Range, Criteria)参数说明:Range代表要统讣的单元格区域;Criteria表示指定的条件表达式。
应用举例:在C17单元格中输入公式:=COUNTIF(B1:B13, ">=80"),确认后,即可统计出B1至B13单元格区域中,数值大于等于80的单元格数目。特别提醒:允许引用的单元格区域中有空口单元格出现。
IK IF函数
函数名称:IF
主要功能:根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
使用格式:=IF (Logical, Value_if_true, Value_if_false)
参数说明:Logical代表逻辑朝断襄达式;Value_if_true表示当判断条件为逻辑“真(TRUE) ”时的显示内容,如果忽略返回“TRUE” ;
Value.if.false表示当判断条件为逻辑“假(FALSE) ”时的显示内
容,如果忽略返回“FALSE” o
应用举例:C29单元格中输入公式:在二IF(C26>二18,"符合要求〃,"不符合要求。,确信以后,如果C26单元格中的数值大于或等于18,则C29 单
元格显示“符合要求”字样,反之显示“不符合要求”字样。
特别提醒:本文中类似“在C29单元格中输入公式”中指定的单元格,读者在使用时,并不需要受其约束,此处只是配合本文所附的实例需要而给出
的相应单元格。
12、COLUMN 函数
函数名称:COLUMN
主要功能:显示所引用单元格的列标号值。
使用格式:COLUMN (reference)参数说明:reference为引用的单元格。
应用举例:C11单元格中输入公式:在二COLUMN(Bll),确认后显示为2 (即 B 列)。
特别提醒:如果在BU单元格中输入公式:二COLUMN(),也显示出2:与之相对应的还有一个返回行标号值的函数-- ROW (reference)。
13、CONCATENATE 函数
函数名称:CONCATENATE
主要功能:将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。使用格式:CONCATENATE (Text 1, Text .. )
参数说明:Textl. Text2……为需要连接的字符文本或引用的单元格。应用举例:在C14单元格中输入公式:二CONCATENATE
确认后,即可将A14单元格中字符..B14单元格中的字符和.连接成
一个整体,显示在C14单元格中。
特别提醒:如果参数不是引用的单元格,且为文本格式的,请给参数加上英文状态下的双引号,如果将上述公式改为:二A14&\也能达到相同的
目的。
14、DATE 函数
函数名称:DATE
主要功能:给出指定数值的日期。
使用格式:DATE (year, month, day)
参数说明:year为指定的年份数值(小于9999); month为指定的月份数值(可以大于12) ; day为指定的天数。
应用举例:在C20单元格中输入公式:二DATE (2003, 13, 35),确认后,显示出 2004-2-4o
特别提醒:由于上述公式中,月份为13,多了一个月,顺延至2004年1月;天数为35,比2004年1月的实际天数乂多了 4天,故又顺延至 2004
年2月4日。
15、DATEDIF 函数
函数名称:DATEDIF
主要功能:计算返回两个日期参数的差值。
使用格式:二DATEDIF (ddtel,date2,"y")、二DATEDIF (datel, ddte2, "m")、 =DATEDIF(datel, date2,
参数说明:datel代表前面一个日期,date2代表后面一个日期;y (m、d)要求返回两个日期相差的(年、月、天)数。
应用举例:在C23单元格中输入公式:=DATEDIF(A23, TODAY(), "y"),确认后返回系统当前日期[用TODAY()表示)与A23单元格中日期的差值,并
返回相差的年数。
特别提醒:这是Excel中的一个隐藏函数,在函数向导中是找不到的,可以直接输入使用,对于计算年龄、工龄等非常有效。
16、DAY函数
函数名称:DAY
主要功能:求出指定日期或引用单元格中的日期的天数。
使用格式:DAY(serial_number)参数说明:serial_number代表指定的日期或引用的单元格。
应用举例:输入公式:二DAY(〃2003-12-18〃),确认后,显示出18。
特别提醒:如果是给定的日期,请包含在英文双引号中。
17、MONTH 函数
函数名称:MONTH
主要功能:求出指定日期或引用单元格中的日期的月份。
使用格式:MONTH(serial_number)参数说明:serial_number代表指定的日期或引用的单元啟
应用举例:输入公式:二M0NTHC2003-12-18"),确认后,显示出12。
特别提醒:如果是给定的日期,请包含在英文双引号中;如果将上述公式修改为:二YEAR("2003-12-18"),则返回年份对应的值“2003”。
18、NOW函数
函数名称:NOW
主要功能:给出当前系统日期和时间。
使用格式:NOW()
参数说明:该函数不需要参数。
应用举例:输入公式:=NOW(),确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变,只要按一下F9功能键,即可让其随之改变。特别提醒:显示出来的日期和时间格式,可以通过单元格格式进行重新设置。19、TODAY 函数
函数名称:TODAY
主要功能:给出系统日期。
使用格式:TODAY ()
参数说明:该函数不需要参数。
应用举例:输入公式:=TODAY(),确认后即刻显示岀系统日期和时间。如果系统日期和时间发生了改变,只要按一下F9功能键,即可让其随之改变。特别提醒:显示出来的日期格式,可以通过单元格格式进行重新设置。
20、F IND
用途:FIND用于查找其它文本串(within_text)内的文本串(f ind_text),并从within_text 的首字符开始返回find_text的起始位嘗编号。此函数适用于双字节字符,它区分大小写但不允许使用通配符。
语法:FIND(find_text, within_text> start_num),参数:Find_text 是待查找的目标文本:Within_text是包含待查找文本的源文本:Start_num指左从其开始进行查找的字符,即within_text中编号为1的字符。如果忽略start_num?则假设其为1。
实例:如果A1二软件报,则公式“二FINDC软件S Ab 1)"返回1。
2] LEFT 或LEFTB
LEFT从一个文本字符串的第一个字符开始,截取指怎数目的字符。 ----------- 截取数据
用途:根据指左的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法:LEFT (text, num_cha:rs)或 LEFTB (text, num_bytes)。
参数:Text是包含要提取字符的文本串:Num.chars指左函数要提取的字符数,它必须大于或等于S Num.bytes按字节数指定由LEFTB提取的字符数。
实例;如呆/1二也册暧好玄―则LEFT@L 2)返也“也册厂,丄阿班粗,2)返
世:也:上
LEN 或LENB
LEN统计文本字符串中字符数目。------------ 字符统计
用途:LEN返回文本串的字符数。LENB返回文本串中所有字符的字节数。
语法:LEN(text)或 LENB(text)。
参数:Text待要査找其长度的文本。
注意:此函数用于双字节字符,且空格也将作为字符进行统讣。
实例:如果A1二电脑爱好者,则公式“=LEN(A1)?返回5, =LENB(A 1)返回10。
23.MID 或MIDB
用途:MID返回文本串中从指左位置开始的特左数目的字符,该数目由用户指左。MIDB返回文本串中从指立位宜开始的特定数目的字符,该数目由用户指泄。MIDB函数可以用于双字节字符。语法:MID (text I start_numi num_cha:rs)或MIDB (text, start_num? num_bytes) o 参
数:Text是包含要提取字符的文玉串。Start.num是文本中要艇取的第一个字符的位置, 文本
中第一个字符的start_num为1,以此类推:Num_chars指立希望MID从文本中返回字符的个数:Num.bytes指定希望MIDB从文本中按字节返回字符的个数。
实例:如果al二电子计算机,则公式"=MID(A1, 3, 2)"返回“计算J =MIDB(A1, 3, 2) 返回“子” o
24.RIGHT 或RIGHTB
用途:RIGHT根据所指立的字符数返回文本串中最后一个或多个字符。RIGHTB根据所指疋的字
节数返回文本串中最后一个或多个字符。
语法:RIGHT (text, num_chars)? RIGHTB (text, num_bytes)。
参数:Text是包含要提取字符的文本串;Num.chars 希望RIGHT提取的字符数,它必须大于或
等于0。如果num_chars大于文本长度,则RIGHT返回所有文本。如果忽略num_chars, 则假定
其为1° Num.bytes指定欲提取字符的字节数。
实例:如果A1二学习的革命,则公式BRIGHT(A1, 2)”返回“革命” ,=RIGHTB(A1, 2)返回“命”
o
25、MOD函数
函数名称:MOD
主要功能:求出两数相除的余数。
使用格式:MOD (number, divisor)
参数说明:number代表被除数;divisor代表除数。
应用举例:输入公式:=MOD(13,4),确认后显示出结果"1" o
特别提醒:如果divisor参数为零,则显示错误值“#DIV/0!” ; MOD函数可以借用函数INT来表示:上述公式可以修改为:二13-
4M\T(13/4)。
26、LEN函数
函数名称:LEN
主要功能:统计文本字符串中字符数
使用格式:LEN(text)
参数说明:text表示要统计的文本字符吊。
应用举例:假定A40单元格中保存了“我今年28岁”的字符串,我们在C40 单元格中输入公式:=LEN(A40),确认后即显示出统计结果“6” o
特别提醒:LEN要统计时,无论中全角字符,还是半角字符,每个字符均计为“1” ;与之相对应的一个函数一一LENB,在统计时半角字符计为
“1”,全角字符计为“2”。
27、MAX函数
函数名称:MAX
主要功能:求出一组数中的最大值。
使用格式:MAX (number 1, numb er 2 )
参数说明:numberl, number2……代表需要求最大值的数值或引用单元格(区域),参数不超过30个。
应用举例:输入公式:=MAX(E44:J44, 7, 8, 9,10),确认后即可显示出E44至J44 单元和区域和数值7, 8, 9, 10中的最大值。
特别提醒:如果参数中有文本或逻辑值,则忽略。
特别提醒:公式中各参数间,要用英文状态下的逗号“,”隔开。
2& LARGE
用途:返回某一数拯集中的某个最大值。可以使用LARGE函数查询考试分数集中第一.第二、第
三等的得分。
语法:LARGE (arrays k)
参数:Array为需要从中查询第k个最大值的数组或数据区域,K为返回值在数组或数据单元格
区域里的位置(即轲次)°
实例:如果 B1 二59、B2=70> B3=80. B4=90. B5=89、B6=84. B7=92,,则公式“二LARGE (Bl,