EXCEL中常用函数及使用方法.docx

EXCEL中常用函数及使用方法.docx

《EXCEL中常用函数及使用方法.docx》由会员分享，可在线阅读，更多相关《EXCEL中常用函数及使用方法.docx（12页珍藏版）》请在冰豆网上搜索。

EXCEL中常用函数及使用方法

EXCEL中常用函数及使用方法

欧阳学文

Excel函数一共有11类：

数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

1.数据库函数

当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。

例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1,000且小于2,500的行或记录的总数。

MicrosoftExcel共有12个工作表函数用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions，也称为D函数，每个函数均有三个相同的参数：

database、field和criteria。

这些参数指向数据库函数所使用的工作表区域。

其中参数database为工作表上包含数据清单的区域。

参数field为需要汇总的列的标志。

参数criteria为工作表上包含指定条件的区域。

2.日期与时间函数

通过日期与时间函数，可以在公式中分析和处理日期值和时间值。

3.工程函数

工程工作表函数用于工程分析。

这类函数中的大多数可分为三种类型：

对复数进行处理的函数、在不同的数字系统（如十进制系统、十六进制系统、八进制系统和二进制系统）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。

4.财务函数

财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。

财务函数中常见的参数：

未来值（fv）在所有付款发生后的投资或贷款的价值。

期间数（nper）投资的总支付期间数。

付款（pmt）对于一项投资或贷款的定期支付数额。

现值（pv）在投资期初的投资或贷款的价值。

例如，贷款的现值为所借入的本金数额。

利率（rate）投资或贷款的利率或贴现率。

类型（type）付款期间内进行支付的间隔，如在月初或月末。

5.信息函数

可以使用信息工作表函数确定存储在单元格中的数据的类型。

信息函数包含一组称为IS的工作表函数，在单元格满足条件时返回TRUE。

例如，如果单元格包含一个偶数值，ISEVEN工作表函数返回TRUE。

如果需要确定某个单元格区域中是否存在空白单元格，可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数，或者使用ISBLANK工作表函数确定区域中的某个单元格是否为空。

6.逻辑函数

使用逻辑函数可以进行真假值判断，或者进行复合检验。

例如，可以使用IF函数确定条件为真还是假，并由此返回不同的数值。

7.查询和引用函数

当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。

例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用VLOOKUP工作表函数。

如果需要确定数据清单中数值的位置，可以使用MATCH工作表函数。

8.数学和三角函数

通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。

9.统计函数

统计工作表函数用于对数据区域进行统计分析。

例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和y轴截距，或构成直线的实际点数值。

10.文本函数

通过文本函数，可以在公式中处理文字串。

例如，可以改变大小写或确定文字串的长度。

可以将日期插入文字串或连接在文字串上。

下面的公式为一个示例，借以说明如何使用函数TODAY和函数TEXT来创建一条信息，该信息包含着当前日期并将日期以"ddmmyy"的格式表示。

11.用户自定义函数

如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。

这些函数，称为用户自定义函数，可以通过使用VisualBasicforApplications来创建。

下面介绍EXCEL中常用函数及使用方法

1、AND函数

函数名称：

AND

主要功能：

返回逻辑值：

如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。

使用格式：

AND（logical1,logical2,...）参数说明：

Logical1,Logical2,Logical3……：

表示待测试的条件值或表达式，最多这30个。

应用举例：

在C5单元格输入公式：

=AND（A5>=60,B5>=60），确认。

如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。

特别提醒：

如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!

”或“#NAME”。

2、OR函数

函数名称：

OR

主要功能：

返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回函数结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。

使用格式：

OR（logical1,logical2,...）

参数说明：

Logical1,Logical2,Logical3……：

表示待测试的条件值或表达式，最多这30个。

应用举例：

在C62单元格输入公式：

=OR（A62>=60,B62>=60），确认。

如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60，如果返回FALSE，说明A62和B62中的数值都小于60。

特别提醒：

如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!

”或“#NAME”。

3、ABS函数

函数名称：

ABS

主要功能：

求出相应数字的绝对值。

使用格式：

ABS（number）参数说明：

number代表需要求绝对值的数值或引用的单元格。

应用举例：

如果在B2单元格中输入公式：

=ABS（A2），则在A2单元格中无论输入正数（如100）还是负数（如100），B2中均显示出正数（如100）。

特别提醒：

如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！

”。

4、INT函数

函数名称：

INT

主要功能：

将数值向下取整为最接近的整数。

使用格式：

INT（number）参数说明：

number表示需要取整的数值或包含数值的引用单元格。

应用举例：

输入公式：

=INT（18.89），确认后显示出18。

特别提醒：

在取整时，不进行四舍五入；如果输入的公式为=INT（18.89），则返回结果为19。

5、AVERAGE函数

函数名称：

AVERAGE主要功能：

求出所有参数的算术平均值。

使用格式：

AVERAGE（number1,number2,……）

参数说明：

number1,number2,……：

需要求平均值的数值或引用单元格（区域），参数不超过30个。

应用举例：

在B8单元格中输入公式：

=AVERAGE（B7:

D7,F7:

H7,7,8），确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。

特别提醒：

如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。

6、SUM函数

函数名称：

SUM

主要功能：

计算所有参数数值的和。

使用格式：

SUM（Number1,Number2……）

参数说明：

Number1、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（B1:

B1000，"中级"，F1:

F1000）”，其中“B1:

B1000”为提供逻辑判断依据的单元格区域，"中级"为判断条件，就是仅仅统计B1:

B1000区域中职称为“中级”的单元格，F1:

F1000为实际求和的单元格区域。

8、COUNT

用途：

返回数字参DATEDIF数的个数。

它可以统计数组或单元格区域中含有数字的单元格个数。

语法：

COUNT（value1，value2，...）。

参数：

Value1，value2，...是包含或引用各种类型数据的参数（1～30个），其中只有数字类型的数据才能被统计。

实例：

如果A1=90、A2=人数、A3=″″、A4=54、A5=36，则公式“=COUNT（A1:

A5）”返回3。

9、COUNTA

用途：

返回参数组中非空值的数目。

利用函数COUNTA可以计算数组或单元格区域中数据项的个数。

语法：

COUNTA（value1，value2，...）

说明：

Value1，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的单元格数目。

特别提醒：

允许引用的单元格区域中有空白单元格出现。

11、IF函数

函数名称：

IF

主要功能：

根据对指定条件的逻辑判断的真假结果，返回相对应的内容。

使用格式：

=IF（Logical,Value_if_true,Value_if_false）

参数说明：

Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。

应用举例：

C29单元格中输入公式：

在=IF（C26>=18,"符合要求","不符合要求"），确信以后，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。

特别提醒：

本文中类似“在C29单元格中输入公式”中指定的单元格，读者在使用时，并不需要受其约束，此处只是配合本文所附的实例需要而给出的相应单元格。

12、COLUMN函数

函数名称：

COLUMN

主要功能：

显示所引用单元格的列标号值。

使用格式：

COLUMN（reference）参数说明：

reference为引用的单元格。

应用举例：

C11单元格中输入公式：

在=COLUMN（B11），确认后显示为2（即B列）。

特别提醒：

如果在B11单元格中输入公式：

=COLUMN（），也显示出2；与之相对应的还有一个返回行标号值的函数——ROW（reference）。

13、CONCATENATE函数

函数名称：

CONCATENATE

主要功能：

将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。

使用格式：

CONCATENATE（Text1，Text……）

参数说明：

Text1、Text2……为需要连接的字符文本或引用的单元格。

应用举例：

在C14单元格中输入公式：

=CONCATENATE（A14,"@",B14,".com"），确认后，即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。

特别提醒：

如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：

=A14&"@"&B14&".com"，也能达到相同的目的。

14、DATE函数

函数名称：

DATE

主要功能：

给出指定数值的日期。

使用格式：

DATE（year,month,day）

参数说明：

year为指定的年份数值（小于9999）；month为指定的月份数值（可以大于12）；day为指定的天数。

应用举例：

在C20单元格中输入公式：

=DATE（,13,35），确认后，显示出24。

特别提醒：

由于上述公式中，月份为13，多了一个月，顺延至年1月；天数为35，比年1月的实际天数又多了4天，故又顺延至年2月4日。

15、DATEDIF函数

函数名称：

DATEDIF

主要功能：

计算返回两个日期参数的差值。

使用格式：

=DATEDIF（date1,date2,"y"）、=DATEDIF（date1,date2,"m"）、=DATEDIF（date1,date2,"d"）

参数说明：

date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的（年、月、天）数。

应用举例：

在C23单元格中输入公式：

=DATEDIF（A23,TODAY（）,"y"），确认后返回系统当前日期[用TODAY（）表示）与A23单元格中日期的差值，并返回相差的年数。

特别提醒：

这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。

16、DAY函数

函数名称：

DAY

主要功能：

求出指定日期或引用单元格中的日期的天数。

使用格式：

DAY（serial_number）参数说明：

serial_number代表指定的日期或引用的单元格。

应用举例：

输入公式：

=DAY（"1218"），确认后，显示出18。

特别提醒：

如果是给定的日期，请包含在英文双引号中。

17、MONTH函数

函数名称：

MONTH

主要功能：

求出指定日期或引用单元格中的日期的月份。

使用格式：

MONTH（serial_number）参数说明：

serial_number代表指定的日期或引用的单元格。

应用举例：

输入公式：

=MONTH（"1218"），确认后，显示出12。

特别提醒：

如果是给定的日期，请包含在英文双引号中；如果将上述公式修改为：

=YEAR（"1218"），则返回年份对应的值“”。

18、NOW函数

函数名称：

NOW

主要功能：

给出当前系统日期和时间。

使用格式：

NOW（）

参数说明：

该函数不需要参数。

应用举例：

输入公式：

=NOW（），确认后即刻显示出当前系统日期和时间。

如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。

特别提醒：

显示出来的日期和时间格式，可以通过单元格格式进行重新设置。

19、TODAY函数

函数名称：

TODAY

主要功能：

给出系统日期。

使用格式：

TODAY（）

参数说明：

该函数不需要参数。

应用举例：

输入公式：

=TODAY（），确认后即刻显示出系统日期和时间。

如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。

特别提醒：

显示出来的日期格式，可以通过单元格格式进行重新设置。

20.FIND

用途：

FIND用于查找其它文本串（within_text）内的文本串（find_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=软件报，则公式“=FIND（"软件"，A1，1）”返回1。

21.LEFT或LEFTB

LEFT从一个文本字符串的第一个字符开始，截取指定数目的字符。

截取数据

用途：

根据指定的字符数返回文本串中的第一个或前几个字符。

此函数用于双字节字符。

语法：

LEFT（text，num_chars）或LEFTB（text，num_bytes）。

参数：

Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。

Num_bytes按字节数指定由LEFTB提取的字符数。

实例：

如果A1=电脑爱好者，则LEFT（A1，2）返回“电脑”，LEFTB（A1，2）返回“电”。

22.LEN或LENB

LEN统计文本字符串中字符数目。

字符统计

用途：

LEN返回文本串的字符数。

LENB返回文本串中所有字符的字节数。

语法：

LEN（text）或LENB（text）。

参数：

Text待要查找其长度的文本。

注意：

此函数用于双字节字符，且空格也将作为字符进行统计。

实例：

如果A1=电脑爱好者，则公式“=LEN（A1）”返回5，=LENB（A1）返回10。

23.MID或MIDB

用途：

MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。

MIDB返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。

MIDB函数可以用于双字节字符。

语法：

MID（text，start_num，num_chars）或MIDB（text，start_num，num_bytes）。

参数：

Text是包含要提取字符的文本串。

Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推；Num_chars指定希望MID从文本中返回字符的个数；Num_bytes指定希望MIDB从文本中按字节返回字符的个数。

实例：

如果a1=电子计算机，则公式“=MID（A1，3，2）”返回“计算”，=MIDB（A1，3，2）返回“子”。

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=学习的革命，则公式“=RIGHT（A1，2）”返回“革命”，=RIGHTB（A1，2）返回“命”。

25、MOD函数

函数名称：

MOD

主要功能：

求出两数相除的余数。

使用格式：

MOD（number,divisor）

参数说明：

number代表被除数；divisor代表除数。

应用举例：

输入公式：

=MOD（13,4），确认后显示出结果“1”。

特别提醒：

如果divisor参数为零，则显示错误值“#DIV/0!

”；MOD函数可以借用函数INT来表示：

上述公式可以修改为：

=134*INT（13/4）。

26、LEN函数

函数名称：

LEN

主要功能：

统计文本字符串中字符数目。

使用格式：

LEN（text）

参数说明：

text表示要统计的文本字符串。

应用举例：

假定A40单元格中保存了“我今年28岁”的字符串，我们在C40单元格中输入公式：

=LEN（A40），确认后即显示出统计结果“6”。

特别提醒：

LEN要统计时，无论中全角字符，还是半角字符，每个字符均计为“1”；与之相对应的一个函数——LENB，在统计时半角字符计为“1”，全角字符计为“2”。

27、MAX函数

函数名称：

MAX

主要功能：

求出一组数中的最大值。

使用格式：

MAX（number1,number2……）

参数说明：

number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个。

应用举例：

输入公式：

=MAX（E44:

J44,7,8,9,10），确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最大值。

特别提醒：

如果参数中有文本或逻辑值，则忽略。

特别提醒：

公式中各参数间，要用英文状态下的逗号“,”隔开。

28.LARGE

用途：

返回某一数据集中的某个最大值。

可以使用LARGE函数查询考试分数集中第一、第二、第三等的得分。

语法：

LARGE（array，k）

参数：

Array为需要从中查询第k个最大值的数组或数据区域，K为返回值在数组或数据单元格区域里的位置（即名次）。

实例：

如果B1=59、B2=70、B3=80、B4=90、B5=89、B6=84、B7=92，，则公式“=LARGE（B1，B7，2）”返回90。

29、MIN函数

函数名称：

MIN

主要功能：

求出一组数中的最小值。

使用格式：

MIN（number1,number2……）

参数说明：

number1,number2……代表需要求最小值的数值或引用单元格（区域），参数不超过30个。

应用举例：

输入公式：

=MIN（E44:

J44,7,8,9,10），确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最小值。

特别提醒：

如果参数中有文本或逻辑值，则忽略。

30.LOOKUP

用途：

返回向量（单行区域或单列区域）或数组中的数值。

该函数有两种语法形式：

向量和数组，其向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；其数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

语法1（向量形式）：

LOOKUP（lookup_value，lookup_vector，result_vector）

语法2（数组形式）：

LOOKUP（lookup_value，array）。

参数1（向量形式）：

Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。

Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。

Looku