最新Excel函数宝典.docx

最新Excel函数宝典.docx

《最新Excel函数宝典.docx》由会员分享，可在线阅读，更多相关《最新Excel函数宝典.docx（54页珍藏版）》请在冰豆网上搜索。

最新Excel函数宝典

Excel函数简介

一、什么是函数

什么是参数？

参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用。

给定的参数必须能产生有效的值。

参数也可以是常量、公式或其它函数。

参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等：

1.数组--用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。

在MicrosoftExcel有两类数组：

区域数组和常量数组。

区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式；常量数组将一组给定的常量用作某个公式中的参数。

2.单元格引用--用于表示单元格在工作表所处位置的坐标值。

例如，显示在第B列和第3行交叉处的单元格，其引用形式为"B3"。

3.常量--常量是直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。

例如，日期10/9/96、数字210和文本"QuarterlyEarnings"都是常量。

公式或由公式得出的数值都不是常量。

函数是否可以是多重的呢？

也就是说一个函数是否可以是另一个函数的参数呢？

当然可以，这就是嵌套函数的含义。

所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。

如图所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。

这个公式的含义是：

如果单元格F2到F5的平均值大于50，则求G2到G5的和，否则显示数值0。

函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。

如果函数以公式的形式出现，请在函数名称前面键入等号（=）

公式选项板--帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。

单击编辑栏中的"编辑公式"按钮，或是单击"常用"工具栏中的"粘贴函数"按钮之后，就会在编辑栏下面出现公式选项板。

整个过程如图3所示。

什么是公式？

函数与公式既有区别又互相联系。

如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式。

以公式“=SUM（E1:

H1）*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。

上式中的“SUM（E1:

H1）”是函数，“A1”则是对单元格A1的引用（使用其中存储的数据），“26”则是常量，“*”和“+”则是算术运算符（另外还有比较运算符、文本运算符和引用运算符）

二、使用函数的步骤

1.单击需要输入函数的单元格

2.点击fx

3.从弹出的菜单中选择所需要的函数

三、函数的种类

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

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

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

database、field和criteria，这些参数指向数据库函数所使用的工作表区域。

其中参数database为工作表上包含数据清单的区域；参数field为需要汇总的列的标志；参数criteria为工作表上包含指定条件的区域。

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

3.工程函数---工程工作表函数用于工程分析。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

函数名

功能

ABS

求出参数的绝对值。

AND

“与”运算，返回逻辑值，仅当所有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。

AVERAGE

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

COLUMN

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

CONCATENATE

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

COUNTIF

统计某个单元格区域中符合指定条件的单元格数目。

DATE

给出指定数值的日期。

DATEDIF

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

DAY

计算参数中指定日期或引用单元格中的日期天数。

DCOUNT

返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。

FREQUENCY

以一列垂直数组返回某个区域中数据的频率分布。

IF

根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。

INDEX

返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。

INT

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

ISERROR

用于测试函数式返回的数值是否有错。

如果有错，该函数返回TRUE，反之返回FALSE。

LEFT

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

LEN

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

MATCH

返回在指定方式下与指定数值匹配的数组中元素的相应位置。

MAX

求出一组数中的最大值。

MID

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

MIN

求出一组数中的最小值。

MOD

求出两数相除的余数。

MONTH

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

NOW

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

OR

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

RANK

返回某一数值在一列数值中的相对于其他数值的排位。

RIGHT

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

SUBTOTAL

返回列表或数据库中的分类汇总。

SUM

求出一组数值的和。

SUMIF

计算符合指定条件的单元格区域内的数值和。

TEXT

根据指定的数值格式将相应的数字转换为文本形式

TODAY

给出系统日期

VALUE

将一个代表数值的文本型字符串转换为数值型。

VLOOKUP

在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值

WEEKDAY

给出指定日期的对应的星期数。

Excel逻辑函数

一、AND、OR、NOT函数

（一）AND函数

所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。

简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。

语法为AND（logical1,logical2,...），其中Logical1,logical2,...表示待检测的1到30个条件值，各条件值可能为TRUE，可能为FALSE。

参数必须是逻辑值，或者包含逻辑值的数组或引用。

举例说明：

1、在B2单元格中输入数字50，在C2中写公式=AND（B2>30,B2<60）。

由于B2等于50的确大于30、小于60。

所以两个条件值（logical）均为真，则返回结果为TRUE。

2、如果B1-B3单元格中的值为TRUE、FALSE、TRUE，显然三个参数并不都为真，所以在B4单元格中的公式=AND（B1:

B3）等于FALSE

（二）OR函数

OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。

它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。

而OR函数仅需其中任何一个为真即可为真。

比如，上面的示例2，如果在B4单元格中的公式写为=OR（B1:

B3）则结果等于TRUE

（三）NOT函数

NOT函数用于对参数值求反。

当要确保一个值不等于某一特定值时，可以使用NOT函数。

简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.

比如NOT（2+2=4），由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。

二、IF函数

（一）IF函数说明

IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。

它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。

它的语法为IF（logical_test,value_if_true,value_if_false）。

其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。

本参数可使用任何比较运算符。

Value_if_true显示logical_test为TRUE时返回的值，Value_if_true也可以是其他公式。

Value_if_false显示logical_test为FALSE时返回的值。

Value_if_false也可以是其他公式。

简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。

IF函数可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

Excel还提供了可根据某一条件来分析数据的其他函数。

例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。

如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

（二）IF函数应用

1、输出带有公式的空白表单

以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的数据尚未填写，在总计栏（以单元格G5为例）公式为：

=SUM（C5:

F5）

我们看到计算为0的结果。

如果这样的表格打印出来就页面的美观来看显示是不令人满意的。

是否有办法去掉总计栏中的0呢？

你可能会说，不写公式不就行了。

当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。

如何实现呢？

只需将总计栏中的公式（仅以单元格G5为例）改写成：

=IF（SUM（C5:

F5）,SUM（C5:

F5）,""）

通俗的解释就是：

如果SUM（C5:

F5）不等于零，则在单元格中显示SUM（C5:

F5）的结果，否则显示字符串。

（1）SUM（C5:

F5）不等于零的正规写法是SUM（C5:

F5）<>0，在EXCEL中可以省略<>0；

（2）""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。

2、不同的条件返回不同的结果

我们设定按照平均分判断该学生成绩是否合格的规则。

如果各科平均分超过60分则认为是合格的，否则记作不合格：

=IF（B11>60,"合格","不合格"）

语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。

3、多层嵌套函数的应用（Excel的IF函数最多允许七重嵌套）

我们设定综合评定的规则为当各科平均分超过90时，评定为优秀

=IF（F11>60,IF（AND（F11>90）,"优秀","合格"）,"不合格"）

语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND（F11>90），实际上可以仅写F11>90），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。

（三）根据条件计算值

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。

比如在成绩表中计算每位学生取得优秀成绩的课程数。

在工资表中求出所有基本工资在2000元以上的员工数。

语法形式为COUNTIF（range,criteria）。

其中Range为需要计算其中满足条件的单元格数目的单元格区域。

Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为32、"32"、">32"、"apples"。

1、成绩表

=COUNTIF（B4:

B10,">90"）

语法解释为，计算B4到B10这个范围中有多少个数值大于90的单元格

2、销售业绩表

销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。

比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。

按销售人员汇总表

如图10所示的表完全是利用函数计算的方法自动汇总的数据。

首先建立一个按照销售人员汇总的表单样式，如图所示。

然后分别计算订单数、订单总额、销售奖金。

（1）订单数--用COUNTIF计算销售人员的订单数。

=COUNTIF（$C$2:

$C$13,A17）

语法解释为计算单元格A17（即销售人员ANNIE）在"销售人员"清单$C$2:

$C$13的范围内出现的次数，这个出现的次数即可认为是该销售人员ANNIE的订单数。

（2）订单总额--用SUMIF汇总每个销售人员的销售额。

=SUMIF（$C$2:

$C$13,A17,$B$2:

$B$13）

此公式在"销售人员"清单$C$2:

$C$13中检查单元格A17中的文本（即销售人员ANNIE），然后计算"订单金额"列（$B$2:

$B$13）中相应量的和。

这个相应量的和就是销售人员ANNIE的订单总额。

（3）销售奖金--用IF根据订单总额决定每次销售应获得的奖金。

假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。

根据这一规则仍以销售人员ANNIE为例说明。

公式为：

=IF（C17<50000,10%,15%）*C17---如果订单总额小于50000则奖金为10%；如果订单总额大于等于50000，则奖金为15%。

Excel统计函数

在介绍统计函数之前，请大家先看一下附表中的函数名称。

是不是发现有些函数是很类似的，只是在名称中多了一个字母A？

比如，AVERAGE与AVERAGEA；COUNT与COUNTA。

基本上，名称中带A的函数在统计时不仅统计数字，而且文本和逻辑值（如TRUE和FALSE）也将计算在内

一、用于求平均值的统计函数AVERAGE、TRIMMEAN

1、求参数的算术平均值函数AVERAGE

语法形式为AVERAGE（number1,number2,...）

其中Number1,number2,...为要计算平均值的1～30个参数。

这些参数可以是数字，或者是涉及数字的名称、数组或引用。

如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。

但是，如果单元格包含零值则计算在内。

2、求数据集的内部平均值TRIMMEAN

函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。

当希望在分析中剔除一部分数据的计算时，可以使用此函数。

比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算。

语法形式为TRIMMEAN（array,percent）

其中Array为需要进行筛选并求平均值的数组或数据区域。

Percent为计算时所要除去的数据点的比例，例如，如果percent=0.2，在20个数据点的集合中，就要除去4个数据点（20x0.2），头部除去2个，尾部除去2个。

函数TRIMMEAN将除去的数据点数目向下舍为最接近的2的倍数。

二、用于求单元格个数的统计函数COUNT

语法形式为COUNT（value1,value2,...）

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

函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。

如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。

如果要统计逻辑值、文字或错误值，应当使用函数COUNTA:

要计算一共有多少评委参与评分（用函数COUNTA），以及有几个评委给出了有效分数（用函数COUNT）。

三、求区域中数据的频率分布FREQUENCY

由于函数FREQUENCY返回一个数组，必须以数组公式的形式输入。

语法形式为FREQUENCY（data_array,bins_array）

其中Data_array为一数组或对一组数值的引用，用来计算频率。

如果data_array中不包含任何数值，函数FREQUENCY返回零数组。

Bins_array为一数组或对数组区域的引用，设定对data_array进行频率计算的分段点。

如果bins_array中不包含任何数值，函数FREQUENCY返回data_array元素的数目。

以计算某公司的员工年龄分布情况为例说明。

在工作表里列出了员工的年龄。

这些年龄为28、25、31、21、44、33、22和35，并分别输入到单元格C4:

C11。

这一列年龄就是data_array。

Bins_array是另一列用来对年龄分组的区间值。

在本例中，bins_array是指C13:

C16单元格，分别含有值25、30、35、和40。

以数组形式输入函数FREQUENCY，就可以计算出年龄在25岁以下、26~30岁、31~35岁、36~40岁和40岁以上各区间中的数目。

本例中选择了5个垂直相邻的单元格后，即以数组公式输入下面的公式。

返回的数组中的元素个数比bins_array（数组）中的元素个数多1。

第五个数字1表示大于最高间隔（40）的数值（44）的个数。

函数FREQUENCY忽略空白单元格和文本值。

{=FREQUENCY（C4:

C11,C13:

C16）}等于{2;2;2;1;1}

四、一组用于求数据集的满足不同要求的数值的函数

1、求数据集的最大值MAX与最小值MIN

这两个函数MAX、MIN就是用来求解数据集的极值（即最大值、最小值）。

函数的用法非常简单。

语法形式为函数（number1,number2,...），其中Number1,number2,...为需要找出最大数值的1到30个数值。

如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。

因此如果逻辑值和文本不能忽略，请使用带A的函数MAXA或者MINA来代替。

2、求数据集中第K个最大值LARGE与第k个最小值SMALL

这两个函数LARGE、SMALL与MAX、MIN非常相像，区别在于它们返回的不是极值，而是第K个值。

语法形式为：

函数（array,k），其中Array为需要找到第k个最小值的数组或数字型数据区域。

K为返回的数据在数组或数据区域里的位置（如果是LARGE为从大到小排，若为SMALL函数则从小到大排）。

说到这，大家可以想得到吧。

如果K=1或者K=n（假定数据集中有n个数据）的时候，是不是就可以返回数据集的最大值或者最小值了呢。

3、求数据集中的中位数MEDIAN

MEDIAN函数返回给定数值集合的中位数。

所谓中位数是指在一组数据中居于中间的数，换句话说，在这组数据中，有一半的数据比它大，有一半的数据比它小。

语法形式为MEDIAN（number1,number2,...）其中Number1,number2,...是需要找出中位数的1到30个数字参数。

如果数组或引用参数中包含有文字、逻辑值或空白单元格，则忽略这些值，但是其值为零的单元格会计算在内。

需要注意的是，如果参数集合中包含有偶数个数字，函数MEDIAN将返回位于中间的两个数的平均值。

4、求数据集中出现频率最多的数MODE

MODE函数用来返回在某一数组或数据区域中出现频率最多的数值。

跟MEDIAN一样，MODE也是一个位置测量函数。

语法形式为MODE（number1,number2,...）其中Number1,number2,...是用于众数（众数指在一组数值中出现频率最高的数值）计算的1到30个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。

5、以上函数的示例

以某单位年终奖金分配表为例说明。

在示例中，我们将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。

详细的公式写法可从图中清楚的看出，在此不再赘述

五、用来排位的函数RANK、PERCENTRANK

1、一个数值在一组数值中的排位的函数RANK

数值的排位是与数据清单中其他数值的相对大小，当然如果数据清单已经排过序了，则数值的排位就是它当前的位置。

数据清单的排序可以使用Excel提供的排序功能完成。

语法形式为RANK（number,ref,order）其中Number为需要找到排位的数字；Ref为包含一组数字的数组或引用。

Order为一数字用来指明排位的方式。

如果order为0或省略，则Excel将ref当作按降序排列的数据清单