EXCEL函数教程

EXCEL函数教程 Excel函数教程 一、Excel函数应用之函数简介 (陆元婕 2001年05月23日 10:12) 编者语:Excel是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理 表格 关于规范使用各类表格的通知入职表格免费下载关于主播时间做一个表格详细英语字母大小写表格下载简历表格模板下载 和进行图形分析，其更强大的功能体现在对数据的自动处理和计算，然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人却难以进一步深入。 Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头，几乎没有什么软件能够与它匹敌。在您学会了Excel的基本操作后，是不是觉得自己一直局限在Excel的操作界面中，而对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上呢,难道Excel只能做这些简单的工作吗,其实不然，函数作为Excel处理数据的一个最重要手段，功能是十分强大的，在生活和工作实践中可以有多种应用，您甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。 一、什么是函数 Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM 函数对单元格或单元格区域进行加法运算。 函数是否可以是多重的呢,也就是说一个函数是否可以是另一个函数的参数呢,当产生有效的值。参数也可以然可以，这就是嵌套函数的含义。所谓嵌套函数，就是指在某些情况下，您可能需要是常量、公式或其它函数。 将某函数作为另一函数的参数使用。例如图1中所示的公式使用了嵌套的 AVERAGE 参数不仅仅是常量、公式或函数，并将结果与 50 相比较。这个公式的含义是:如果单元格F2到F5的平均值大函数，还可以是数组、单元于50，则求F2到F5的和，否则显示数值0。 格引用等: --用于建立可产生多 1.数组 个结果或可对存放在行和列 中的一组参数进行运算的单 图1 嵌套函数 个公式。在 Microsoft Excel有两类数组:区域数组和常 量数组。区域数组是一个矩 在学习Excel函数之前，我们需要对于函数的结构做以必要的了解。如图2所示，函形的单元格区域，该区域中数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函的单元格共用一个公式;常数以公式的形式出现，请在函数名称前面键入等号(= 公式选项板将提供相关的帮助。 量数组将一组给定的常量用作某个公式中的参数。 2.单元格引用--用于表示单 元格在工作表所处位置的坐 标值。例如，显示在第 B 列 和第 3 其引用形式为"B3"。 3.常量--常量是直接键入到 单元格或公式中的数字或文 图2 函数的结构 本值，或由名称所代表的数字或文本值。例如，日期 10/9/96、数字 210 和文本 "Quarterly Earnings"都是常 量。公式或由公式得出的数 值都不是常量。 公式选项板--帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的"编辑公式"按钮，或是单击"常用"工具栏中的"粘贴函数" 按钮之后，就会在编辑栏下面出现公式选项板。整个过程如图3所示。 图3 公式选项板 二、使用函数的步骤 在Excel中如何使用函数呢, 1.单击需要输入函数的单元格，如图4所示，单击单元格C1，出现编辑栏 图4 单元格编辑 2.单击编辑栏中"编辑公式"按钮 ，将会在编辑栏下面出现一个"公式选项板"，此时"名称"框将变成"函数"按钮，如图3所示。 3.单击"函数"按钮右端的箭头，打开函数列表框，从中选择所需的函数; 图5 函数列表框 4.当选中所需的函数后，Excel 2000将打开"公式选项板"。用户可以在这个选项板中输入函数的参数，当输入完参数后，在"公式选项板"中还将显示函数计算的结果; 5.单击"确定"按钮，即可完成函数的输入; 6.如果列表中没有所需的函数，可以单击"其它函数"选项，打开"粘贴函数"对话框，用户可以从中选择所需的函数，然后单击"确定"按钮返回到"公式选项板"对话框。 在了解了函数的基本知识及使用方法后，请跟随笔者一起寻找Excel提供的各种函数。您可以通过单击插入栏中的"函数"看到所有的函数。 图6 粘贴函数列表 三、函数的种类 Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。 1.数据库函数--当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于 1,000 且小于 2,500 的行或记录的总数。Microsoft Excel 共有 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 来创建一条信息，该信息包含着当前日期并将日期以"dd-mm-yy"的格式表示。 11.用户自定义函数--如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。这些函数，称为用户自定义函数，可以通过使用 Visual Basic for Applications 来创建。 以上对Excel函数及有关知识做了简要的介绍，在以后的文章中笔者将逐一介绍每一类函数的使用方法及应用技巧。但是由于Excel的函数相当多，因此也可能仅介绍几种比较常用的函数使用方法，其他更多的函数您可以从Excel的在线帮助功能中了解更详细的资讯。 二、Excel函数应用之数学和三角函数 学习Excel函数，我们还是从“数学与三角函数”开始。毕竟这是我们非常熟悉的函数，这些正弦函数、余弦函数、取整函数等等从中学开始，就一直陪伴着我们。 首先，让我们一起看看Excel提供了哪些数学和三角函数。笔者在这里以列表的形式列出Excel提供的所有数学和三角函数，详细请看附注的表格。 从表中我们不难发现，Excel提供的数学和三角函数已基本囊括了我们通常所用得到的各种数学公式与三角函数。这些函数的详细用法，笔者不在这里一一赘述，下面从应用的角度为大家演示一下这些函数的使用方法。 一、与求和有关的函数的应用 数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、 SUM函 空格等数据的单元格，语法简单、使用方便。相信这也是大家最先学会使用的Excel函数之一。但是实际上，Excel所提供的求和函数不仅 仅只有SUM一种，还包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2几种函数。 这里笔者将以某单位工资表为例重点介绍SUM(计算一组参数之和)、SUMIF(对满足某一条件的单元格区域求和)的使用。(说明:为力求简单，示例中忽略税金的计算。) 图1 函数求和 SUM 1、行或列求和 以最常见的工资表(如上图)为例，它的特点是需要对行或列内的若干单元格求和。 比如，求该单位2001年5月的实际发放工资总额，就可以在H13中输入公式: =SUM(H3:H12) 2、区域求和 区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式"=SUM()"，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。对于需要减去的单元格，则可以按住Ctrl键逐个选中它们，然后用手工在公式引用的单元格前加上负号。当然你也可以用公式选项板完成上述工作，不过对于SUM函数来说手工还是来的快一些。比如，H13的公式还可以写成: =SUM(D3:D12,F3:F12)-SUM(G3:G12) 3、注意 SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符(逗号)不能多于29个，否则Excel就会提示参数太多。对需要参与求和的某个常数，可用"=SUM(单元格区域，常数)"的形式直接引用，一般不必绝对引用存放该常数的单元格。 SUMIF SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。 仍以上图为例，在工资表中需要分别计算各个科室的工资发放情况。 要计算销售部2001年5月加班费情况。则在F15种输入公式为 =SUMIF($C$3:$C$12,"销售部",$F$3:$F$12) 其中"$C$3:$C$12"为提供逻辑判断依据的单元格区域，"销售部"为判断条件即只统计$C$3:$C$12区域中部门为"销售部"的单元格，$F$3:$F$12为实际求和的单元格区域。 二、与函数图像有关的函数应用 我想大家一定还记得我们在学中学数学时，常常需要画各种函数图像。那个时候是用坐标纸一点点描绘，常常因为计算的疏忽，描不出平滑的函数曲线。现在，我们已经知道Excel几乎囊括了我们需要的各种数学和三角函数，那是否可以利用Excel函数与Excel图表功能描绘函数图像呢,当然可以。 这里，笔者以正弦函数和余弦函数为例说明函数图像的描绘方法。 图2 函数图像绘制 1、 录入数据--如图所示，首先在表中录入数据，自B1至N1的单元格以30 度递增的方式录入从0至360的数字，共13个数字。 2、 求函数值--在第2行和第三行分别输入SIN和COS函数，这里需要注意的是:由于SIN等三角函数在Excel的定义是要弧度值，因此必须先将角度值转为弧度值。具体公式写法为(以D2为例): =SIN(D1*PI()/180) 3、 选择图像类型--首先选中制作函数图像所需要的表中数据，利用Excel工具栏上的图表向导按钮(也可利 用"插入","图表")，在"图表类型"中选择"XY散点图"，再在右侧的"子图表类型"中选择"无数据点平滑线散点图"，单击[下一步]，出现"图表数据源"窗口，不作任何操作，直接单击[下一步]。 4、 图表选项操作--图表选项操作是制作函数曲线图的重要步骤，在"图表选项"窗口中进行(如图3)，依次进行操作的项目有: 标题--为图表取标题，本例中取名为"正弦和余弦函数图像";为横轴和纵轴取标题。 坐标轴--可以不做任何操作; 网格线--可以做出类似坐标纸上网格，也可以取消网格线; 图例--本例选择图例放在图像右边，这个可随具体情况选择; 数据标志--本例未将数据标志在图像上，主要原因是影响美观。如果有特殊要求例外。5、完成图像--操作结束后单击[完成]，一幅图像就插入Excel的工作区了。 6、 编辑图像--图像生成后，字体、图像大小、位置都不一定合适。可选择相应的选项进行修改。所有这些操作可以先用鼠标选中相关部分，再单击右键弹出快捷菜单，通过快捷菜单中的有关项目即可进行操作。 至此，一幅正弦和余弦函数图像制作完成。用同样的方法，还可以制作二次曲线、对数图像等等。 三、常见数学函数使用技巧--四舍五入 在实际工作的数学运算中，特别是财务计算中常常遇到四舍五入的问题。虽然，excel的单元格格式中允许你定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正的四舍五入，只是显示结果似乎四舍五入了。如果采用这种四舍五入方法的话，在财务运算中常常会出现几分钱的误差，而这是财务运算不允许的。那是否有简单可行的方法来进行真正的四舍五入呢,其实，Excel已经提供这方面的函数了，这就是ROUND函数，它可以返回某个数字按指定位数舍入后的数字。 在Excel提供的"数学与三角函数"中提供了一个名为ROUND(number,num_digits)的函数，它的功能就是根据指定的位数，将数字四舍五入。这个函数有两个参数，分别是number和num_digits。其中number就是将要进行四舍五入的数字;num_digits则是希望得到的数字的小数点后的位数。如图3所示: 单元格B2中为初始数据0.123456，B3的初始数据为0.234567，将要对它们进 "=ROUND(B2,2)"，小数点后保留两行四舍五入。在单元格C2中输入 位有效数字，得到0.12、0.23。在单元格D2中输入"=ROUND(B2,4)"，则小数点保留四位有效数字，得到0.1235、0.2346。 图3 对数字进行四舍五入 对于数字进行四舍五入,还可以使用INT(取整函数)，但由于这个函数的定义是返回实数舍入后的整数值。因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。仍然以图3为例，如果采用INT函数，则C2公式应写成:"=INT(B2*100+0.5)/100"。 最后需要说明的是: 附注:Excel的数学和三角函数一览表 三、Excel函数应用之逻辑函数 OR、NOT、FALSE、IF、TRUE函数。 一、AND、OR、NOT函数 这三个函数都用来返回参数逻辑值。详细介绍见下: (一)AND函数 用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、 所有参数的逻辑值为真时返回 TRUE;只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。 语法为AND(logical1,logical2, ...)，其中Logical1, logical2, ... 表示待检测的 1 FALSE。 参数必须是逻辑值，到 30 个条件值，各条件值可能为TRUE，可能为 或者包含逻辑值的数组或引用。举例说明: 1、 在B2单元格中输入数字50，在C2中写公式=AND(B2>30,B2<60)。由于B2等于50的确大于30、小于60。所以两个条 件值(logical)均为真，则返回结果为TRUE。 图1 AND函数示例1 2、 如果 B1-B3 单元格中的值为 TRUE、FALSE、TRUE，显然三个参数并不都为真，所以 在B4单元格中的公式=AND(B1:B3) 等于 FALSE 图2 AND函数示例2 (二)OR函数 OR函数指在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。比如，上面的示例2，如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE 图3 OR函数示例 (三)NOT函数 就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE. 结果与之相反，为FALSE。 NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。简言之，比如NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数 二、TRUE、FALSE函数 TRUE、FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者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、 输出带有公式的空白表单 图5 人事分析表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) ""表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。 图4 2、 不同的条件返回不同的结果 如果对上述例子有了很好的理解后，我们就很容易将IF函数应用到更广泛的 领域。比如，在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。 图6 某班级的成绩如图6所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格。 根据这一规则，我们在综合评定中写公式(以单元格B12为例): =IF(B11>60,"合格","不合格") 语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。 在综合评定栏中可以看到由于C列的同学各科平均分为54分，综合评定为不合格。其余均为合格。 3、 多层嵌套函数的应用 在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗,可以使用多层嵌套的办法来实现。仍以上例为例，我们设定综合评定的规则为当各科平均分超过90时，评定为优秀。如图7所示。 图7 说明:为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意Excel的IF函数最多允许七重嵌套。 根据这一规则，我们在综合评定中写公式(以单元格F12为例): =IF(F11>60,IF(AND(F11>90),"优秀","合格")," 不合格") ，则执行第二个参数，在这里为嵌套 语法解释为，如果单元格F11的值大于60 函数，继续判断单元格F11的值是否大于90(为了让大家体会一下AND函数的应用，写成AND(F11>90)，实际上可以仅写F11>90)，如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。 在综合评定栏中可以看到由于F列的同学各科平均分为92分，综合评定为优秀。 (三)根据条件计算值 在了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。 COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。 语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32"、"apples"。 1、成绩表 这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是:每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示 图8 根据这一规则，我们在优秀门数中写公式(以单元格B13为例): =COUNTIF(B4:B10,">90") 语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值大于90的单元格。 在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。 2、 销售业绩表 SUMIF、COUNTIF非常典型的示例。比如， 销售业绩表可能是综合运用IF、 可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。 原始数据表如图9所示(原始数据是以流水单形式列出的，即按订单号排列) 图9 原始数据表 按销售人员汇总表如图10所示 图10 销售人员汇总表 如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。 (1) 订单数 --用COUNTIF计算销售人员的订单数。 以销售人员ANNIE的订单数公式为例。公式: =COUNTIF($C$2:$C$13,A17) 语法解释为计算单元格A17(即销售人员ANNIE)在"销售人员"清单$C$2:$C$13的范围内(即图9所示的原始数据表)出现的次数。 这个出现的次数即可认为是该销售人员ANNIE的订单数。 (2) 订单总额--用SUMIF汇总每个销售人员的销售额。 以销售人员ANNIE的订单总额公式为例。公式: =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函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用。 四、Excel函数应用之文本/日期/时间函数 所谓文本函数，就是可以在公式中处理文字串的函数。例如，可以改变大小写或确定文字串的长度;可以替换 某些字符或者去除某些字符等。而日期和时间函数则可以在公式中分析和处理日期值和时间值。关于这两类函 数的列表参看附表，这里仅对一些常用的函数做简要介绍。 一、文本函数 (一)大小写转换 LOWER--将一个文字串中的所有大写字母转换为小写字母。 UPPER--将文本转换成大写形式。 PROPER--将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。 这三种函数的基本语法形式均为 函数名(text)。示例说明: 已有字符串为:pLease ComE Here! 可以看到由于输入的不 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 ，这句话大小写乱用了。 通过以上三个函数可以将文本转换显示样式，使得文本变得规范。参见图1 pLease ComE Here!)= please come here! Lower( upper(pLease ComE Here!)= PLEASE COME HERE! proper(pLease ComE Here!)= Please Come Here! 图1 (二)取出字符串中的部分字符 您可以使用Mid、Left、Right等函数从长字符串内获取一部分字符。具体语法格式为 定要由 LEFT 所提取的字符数。 MID函数:MID(text,start_num,num_chars)其中Text是包含要提取字符的文本串。 Start_num是文本中要提取的第一个字符的位置。 RIGHT函数:RIGHT(text,num_chars)其中Text是包含要提取字符的文本串。Num_chars指定希望 RIGHT 提取的字符数。 比如，从字符串"This is an apple."分别取出字符"This"、"apple"、"is"的具体函数写法为。 LEFT("This is an apple",4)=This RIGHT("This is an apple",5)=apple MID("This is an apple",6,2)=is LEFT函数:LEFT(text,num_chars)其中Text是包含要提取字符的文本串。Num_chars指 图2 (三)去除字符串的空白 在字符串形态中，空白也是一个有效的字符，但是如果字符串中出现空白字符时，容易在判断或对比数据是发生错误，在Excel中您可以使用Trim函数清除字符串中的空白。 语法形式为:TRIM(text)其中Text为需要清除其中空格的文本。 需要注意的是，Trim函数不会清除单词之间的单个空格，如果连这部分空格都需清除的话，建议使用替换功能。比如，从字符串"My name is Mary" 中清除空格的函数写法为:TRIM("My name is Mary")=My name is Mary 参见图3 图3 (四)字符串的比较 在数据表中经常会比对不同的字符串，此时您可以使用EXACT函数来比较两个字符串是否相同。该函数测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE;否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。利用函数 EXACT 可以测试输入文档内的文字。语法形式为:EXACT(text1,text2)Text1为待比较的第一个字符串。Text2为待比较的第二个字符串。举例说明:参见图4 EXACT("China","china")=False 图4 二、日期与时间函数 在数据表的处理过程中，日期与时间的函数是相当重要的处理依据。而Excel在这方面也提供了相当丰富的函数供大家使用。 (一)取出当前系统时间/日期信息 用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。 语法形式均为 函数名()。 (二)取得日期/时间的部分字段值 如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、 YEAR函数直接从日期/时间中取出需要的数据。具体示例参看图5。 MONTH、 比如，需要返回2001-5-30 12:30 PM的年份、月份、日数及小时数，可以分别采用相应函数实现。 YEAR(E5)=2001 MONTH(E5)=5 DAY(E5)=30 HOUR(E5)=12 图5 此外还有更多有用的日期/时间函数，可以查阅附表。下面我们将以一个具体的示例来说明Excel的文本函数与日期函数的用途。 三、示例:做一个美观简洁的人事资料分析表 1、 示例说明 在如图6所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及 参加工作时间为手工添入外，其余各项均为用函数计算所得。 图6 在此例中我们将详细说明如何通过函数求出: (1)自动从身份证号码中提取出生年月、性别信息。 (2)自动从参加工作时间中提取工龄信息。 2、身份证号码相关知识 在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。我们知道，当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展(由两位变为四位)和末尾加了效验码，就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下: (1)15位的身份证号码:1~6位为地区代码，7~8位为出生年份(2位)，9~10位为出生月份，11~12位为出生日期，第13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。 (2)18位的身份证号码:1~6位为地区代码，7~10位为出生年份(4位)，11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。18位为效验位。 3、 应用函数 在此例中为了实现数据的自动提取，应用了如下几个Excel函数。 (1)IF函数:根据逻辑表达式测试的结果，返回相应的值。IF函数允许嵌套。 语法形式为:IF(logical_test, value_if_true,value_if_false) (2)CONCATENATE:将若干个文字项合并至一个文字项中。 语法形式为:CONCATENATE(text1,text2„„) (3)MID:从文本字符串中指定的起始位置起，返回指定长度的字符。 语法形式为:MID(text,start_num,num_chars) (4)TODAY:返回计算机系统内部的当前日期。 语法形式为:TODAY() (5)DATEDIF:计算两个日期之间的天数、月数或年数。 语法形式为:DATEDIF(start_date,end_date,unit) (6)VALUE:将代表数字的文字串转换成数字。 语法形式为:VALUE(text) (7)RIGHT:根据所指定的字符数返回文本串中最后一个或多个字符。 语法形式为:RIGHT(text,num_chars) (8)INT:返回实数舍入后的整数值。语法形式为:INT(number) 4、 公式写法及解释(以员工Andy为例说明) 说明:为避免公式中过多的嵌套，这里的身份证号码限定为15位的。如果您看懂了公式的话，可以进行简单的修改即可适用于18位的身份证号码，甚至可适用于15、18两者并存的情况。 (1)根据身份证号码求性别 =IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男") 公式解释:a. RIGHT(E4,3)用于求出身份证号码中代表性别的数字，实际求得的为代表数字的字符串 b. VALUE(RIGHT(E4,3)用于将上一步所得的代表数字的字符串转换为数字 c. VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判断这个身份证号码是奇数还是偶数，当然你也可以用Mod函数来做出判断。 d. =IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")及如果上述公式判断出这个号码是偶数时，显示"女"，否则，这个号码是奇数的话，则返回"男"。 (2)根据身份证号码求出生日期 CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),&q = uot;/",MID(E4,11,2)) 公式解释:a. MID(E4,7,2)为在身份证号码中获取表示年份的数字的字符串 b. MID(E4,9,2) 为在身份证号码中获取表示月份的数字的字符串 c. MID(E4,11,2) 为在身份证号码中获取表示日期的数字的字符串 d. CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2)," /",MID(E4,11,2))目的就是将多个字符串合并在一起显示。 (3)根据参加工作时间求年资(即工龄) =CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"个月") 公式解释: a. TODAY()用于求出系统当前的时间 b. DATEDIF(F4,TODAY(),"y")用于计算当前系统时间与参加工作时间相差的年份 c. DATEDIF(F4,TODAY(),"ym")用于计算当前系统时间与参加工作时间相差的月份，忽略日期中的日和年。 d. =CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"个月")目的就是将多个字符串合并在一起显示。 5. 其他说明 在这张人事资料表中我们还发现，创建日期:31-05-2001时显示在同一个单元格中的。这是如何实现的呢,难道是手工添加的吗,不是，实际上这个日期还是变化的，它显示的是系统当前时间。这里是利用函数 TODAY 和函数 TEXT 一起来创建一条信息，该信息包含着当前日期并将日期以"dd-mm-yyyy" 的格式表示。 具体公式写法为:="创建日期:"&TEXT(TODAY(),"dd-mm-yyyy") 至此，我们对于文本函数、日期与时间函数已经有了大致的了解，同时也设想了一些应用领域。相信随着大家在这方面的不断研究，会有更广泛的应用。 附一:文本函数 附二、日期与时间函数 五、Excel函数应用之查询与引用函数在介绍查询与引用函 数之前，我们先来了解一下有关引用的知识。 1、引用的作用 在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。 2、引用的含义 关于引用需要了解如下几种情况的含义: 外部引用--不同工作簿中的单元格的引用称为外部引用。 远程引用--引用其它程序中的数据称为远程引用。 相对引用--在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。 绝对引用--如果在复制公式时不希望 Excel 调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。 3、引用的表示方法 关于引用有两种表示的方法，即A1 和 R1C1 引用样式。 (1)引用样式一(默认)--A1 A1的引用样式是Excel的默认引用类型。这种类型引用字母标志列(从 A 到 IV ，共 256 列)和数字标志行(从 1 到 65536)。这些字母和数字被称为行和列标题。如果要引用单元格，请顺序输入列字母和行数字。例如，C25 引用了 25 交叉处的单元格。如果要引用单元格区域，请输入区域左上角单列 C 和行 元格的引用、冒号(:)和区域右下角单元格的引用，如A20:C35。 (2)引用样式二--R1C1 在 R1C1 引用样式中，Excel 使用"R"加行数字和"C"加列数字来指示单元格的位置。例如，单元格绝对引用 R1C1 与 A1 引用样式中的绝对引用 $A$1 等价。如果活动单元格是 A1，则单元格相对引用 R[1]C[1] 将引用下面一行和右边一列的单元格，或是 B2。 在了解了引用的概念后，我们来看看Excel提供的查询与引用函数。查询与引用函数可以用来在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用。Excel中一共提供了ADDRESS、AREAS、CHOOSE、COLUMN、COLUMNS、HLOOKUP、HYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、ROW、ROWS、TRANSPOSE、VLOOKUP 16个查询与引用函数。下面，笔者将分组介绍一下这些函数的使用方法及简单应用。 一、ADDRESS、COLUMN、ROW 1、 ADDRESS用于按照给定的行号和列标，建立文本类型的单元格地址。 其语法形式为:ADDRESS(row_num,column_num,abs_num,a1,sheet_text) Row_num指在单元格引用中使用的行号。 Column_num指在单元格引用中使用的列标。 Abs_num 指明返回的引用类型，1代表绝对引用，2代表绝对行号，相对列标，3代表相对行号，绝对列标，4为相对引用。 A1用以指明 A1 或 R1C1 引用样式的逻辑值。如果 A1 为 TRUE 或省略，函数 ADDRESS 返回 A1 样式的引用;如果 A1 为 FALSE，函数 ADDRESS 返回 R1C1 样式的引用。 Sheet_text为一文本，指明作为外部引用的工作表的名称，如果省略 sheet_text，则不使用任何工作表名。 简单说，即ADDRESS(行号，列标，引用类型，引用样式，工作表名称) 比如，ADDRESS(4,5,1,FALSE,"[Book1]Sheet1") 等于 "[Book1]Sheet1!R4C5"参见图1 图1 2、 COLUMN用于返回给定引用的列标。 语法形式为:COLUMN(reference) Reference为需要得到其列标的单元格或单元格区域。如果省略 reference，则假定为是对函数 COLUMN 所在单元格的引用。如果 reference 为一个单元格区域，并且函数 COLUMN 作为水平数组输入，则函数 COLUMN 将 reference 中的列标以水平数组的形式返回。但是Reference 不能引用多个区域。 3、 ROW用于返回给定引用的行号。 语法形式为:ROW(reference) Reference为需要得到其行号的单元格或单元格区域。 如果省略 reference，则假定是对函数 ROW 所在单元格的引用。如果 reference 为一个单元格区域，并且函数 ROW 作为垂直数组输入，则函数 ROW 将 reference 的行号以垂直数组的形式返回。但是Reference 不能对多个区域进行引用。 二、AREAS、COLUMNS、INDEX、ROWS 1、 AREAS用于返回引用中包含的区域个数。其中区域表示连续的单元格组或某个单元格。 其语法形式为AREAS(reference) Reference为对某一单元格或单元格区域的引用，也可以引用多个区域。如果需要将几个引用指定为一个参数，则必须用括号括起来。 OLUMNS用于返回数组或引用的列数。 2、 C 其语法形式为COLUMNS(array) Array为需要得到其列数的数组、数组公式或对单元格区域的引用。 3、 ROWS用于返回引用或数组的行数。 其语法形式为ROWS(array) Array为需要得到其行数的数组、数组公式或对单元格区域的引用。 以上各函数示例见图2 图2 4、 INDEX用于返回表格或区域中的数值或对数值的引用。 函数 INDEX() 有两种形式:数组和引用。数组形式通常返回数值或数值数组;引用形式通常返回引用。 (1)INDEX(array,row_num,column_num) 返回数组中指定单元格或单元格数组的数值。 Array为单元格区域或数组常数。Row_num为数组中某行的行序号，函数从该行返回数值。Column_num为数组中某列的列序号，函数从该列返回数值。需注意的是Row_num 和 column_num 必须指向 array 中的某一单元格，否则，函数 INDEX 返回错误值 #REF!。 (2)INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格或单元格区域的引用。 Reference为对一个或多个单元格区域的引用。 Row_num为引用中某行的行序号，函数从该行返回一个引用。 Column_num为引用中某列的列序号，函数从该列返回一个引用。 需注意的是Row_num、column_num 和 area_num 必须指向 reference 中的单元格;否则，函数 INDEX 返回错误值 #REF!。如果省略 row_num 和 column_num，函数 INDEX 返回由 area_num 所指定的区域。 三、INDIRECT、OFFSET 1、 INDIRECT用于返回由文字串指定的引用。 当需要更改公式中单元格的引用，而不更改公式本身，使用函数 INDIRECT。 其语法形式为:INDIRECT(ref_text,a1) 其中Ref_text为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文字串单元格的引用。如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值 #REF!。 A1为一逻辑值，指明包含在单元格 ref_text 中的引用的类型。如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。 需要注意的是:如果 ref_text 是对另一个工作簿的引用(外部引用)，则那个工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。 2、 OFFSET函数用于以指定的引用为参照系，通过给定偏移量得到新的引用。 返回的引用可以是一个单元格或者单元格区域，并可以指定返回的行数或者列数。 其基本语法形式为:OFFSET(reference, rows, cols, height, width)。 其中，reference变量作为偏移量参照系的引用区域(reference必须为对单元格或相连单元格区域的引用，否则，OFFSET函数返回错误值,VALUE!)。 rows变量表示相对于偏移量参照系的左上角单元格向上(向下)偏移的行数(例如rows使用2作为参数，表示目标引用区域的左上角单元格比reference低2行)，行数可为正数(代表在起始引用单元格的下方)或者负数(代表在起始引用单元格的上方)或者0(代表起始引用单元格)。 cols表示相对于偏移量参照系的左上角单元格向左(向右)偏移的列数(例如cols使用4作为参数，表示目标引用区域的左上角单元格比reference右移4列)，列数可为正数(代表在起始引用单元格的右边)或者负数(代表在起始引用单元格的 左边)。 如果行数或者列数偏移量超出工作表边缘，OFFSET函数将返回错误值,REF～。height变量表示高度，即所要返回的引用区域的行数(height必须为正数)。width变量表示宽度，即所要返回的引用区域的列数(width必须为正数)。如果省略height或者width，则假设其高度或者宽度与reference相同。例如，公式OFFSET(A1,2,3,4,5)表示比单元格A1靠下2行并靠右3列的4行5列的区域(即 )。 D3:H7区域 由此可见，OFFSET函数实际上并不移动任何单元格或者更改选定区域，它只是返回一个引用。 四、HLOOKUP、LOOKUP、MATCH、VLOOKUP 1、 LOOKUP函数与MATCH函数 LOOKUP函数可以返回向量(单行区域或单列区域)或数组中的数值。此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数 HLOOKUP。当比较值位于要进行数据查找的左边一列时，使用函数 VLOOKUP。 如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用函数 MATCH 而不是函数 LOOKUP。MATCH函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置。从以上分析可知，查找函数的功能，一是按搜索条件，返回被搜索区域内数据的一个数据值;二是按搜索条件，返回被搜索区域内某一数据所在的位置值。利用这两大功能，不仅能实现数据的查询，而且也能解决如"定级"之类的实际问题。 2、 LOOKUP用于返回向量(单行区域或单列区域)或数组中的数值。 函数 LOOKUP 有两种语法形式:向量和数组。 (1) 向量形式 函数 LOOKUP 的向量形式是在单行区域或单列区域(向量)中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。 其基本语法形式为LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。 Lookup_vector为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。 需要注意的是Lookup_vector 的数值必须按升序排序:...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE;否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。 Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。 如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。 示例详见图3 图3 (2) 数组形式 函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。通常情况下，最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUP 的数组形式。函数 LOOKUP 的这种形式主要用于与其他电子表格兼容。关于LOOKUP的数组形式的用法在此不再赘述，感兴趣的可以参看Excel的帮助。 3、 HLOOKUP与VLOOKUP HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。 VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。 当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。 语法形式为: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。 Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。 Row_index_num为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。 Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推. Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。 下面详细介绍一下VLOOKUP函数的应用。 简言之，VLOOKUP函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数VLOOKUP的查找可以达到两种目的:一是精确的查找。二是近似的查找。下面分别说明。 (1) 精确查找--根据区域最左列的值，对其它列的数据进行精确的查找 示例:创建工资表与工资条 首先建立员工工资表 图4 然后，根据工资表创建各个员工的工资条，此工资条为应用Vlookup函数建立。以员工Sandy(编号A001)的工资条创建为例说明。 第一步，拷贝标题栏 第二步，在编号处(A21)写入A001 第三步，在姓名(B21)创建公式 =VLOOKUP($A21,$A$3:$H$12,2,FALSE) 语法解释:在$A$3:$H$12范围内(即工资表中)精确找出与A21单元格相符的行，并将该行中第二列的内容计入单元格中。 第四步，以此类推，在随后的单元格中写入相应的公式。 图5 (2) 近似的查找--根据定义区域最左列的值，对其它列数据进行不精确值的查找 示例:按照项目总额不同提取相应比例的奖金 第一步，建立一个项目总额与奖金比例的对照表，如图6所示。项目总额的数字均为大于情况。即项目总额在0~5000元时，奖金比例为1%，以此类推。 图6 第二步 假定某项目的项目总额为13000元，在B11格中输入公式 =VLOOKUP(A11,$A$4:$B$8,2,TRUE) 即可求得具体的奖金比例为5%，如图7。 图7 4、 MATCH函数 MATCH函数有两方面的功能，两种操作都返回一个位置值。 一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。 二是确定一个给定值位于已排序列表中的位置，这不需要准确的匹配. 语法结构为:MATCH(lookup_value,lookup_array,match_type) lookup_value为要搜索的值。 lookup_array:要查找的区域(必须是一行或一列)。 match_type:匹配形式，有0、1和,1三种选择:"0"表示一个准确的搜索。"1"表示搜索小于或等于查换值的最大值，查找区域必须为升序排列。",1"表示搜索大于或等于查找值的最小值，查找区域必须降序排开。以上的搜索，如果没有匹配值，则返回,N/A。 五、HYPERLINK 所谓HYPERLINK，也就是创建快捷方式，以打开文档或网络驱动器，甚至INTERNET地址。通俗地讲，就是在某个单元格中输入此函数之后，可以到您想去的任何位置。在某个Excel文档中，也许您需要引用别的Excel文档或Word文档等等，其步骤和方法是这样的: (1)选中您要输入此函数的单元格，比如B6。 (2)单击常用工具栏中的"粘贴函数"图标，将出现"粘贴函数"对话框，在"函数分类"框中选择"常用"，在"函数名"框中选择HYPERLINK，此时在对话框的底部将出现该函数的简短解释。 (3)单击"确定"后将弹出HYPERLINK函数参数设置对话框。 (4)在"Link_location"中键入要链接的文件或INTERNET地址，比如:"c:\my documents\Excel函数.doc";在"Friendly_name" 中键入"Excel函数"(这里是假设我们要打开的文档位于c:\my documents下的文件"Excel函数.doc")。 (5)单击"确定"回到您正编辑的Excel文档，此时再单击B6单元 格就可立即打开用Word编辑的会议纪要文档。 HYPERLINK函数用于创建各种快捷方式，比如打开文档或网络驱动器，跳转到某个网址等。说得夸大一点，在某个单元格中输入此函数之后，可以跳到我们想去的任何位置。 TRANSPOSE) 六、其他(CHOOSE、 1、CHOOSE函数 函数CHOOSE可以使用 index_num 返回数值参数清单中的数值。使用函数 CHOOSE 可以基于索引号返回多达 29 个待选数值中的任一数值。 语法形式为:CHOOSE(index_num,value1,value2,...) Index_num用以指明待选参数序号的参数值。Index_num 必须为 1 到 29 之间的数字、或者是包含数字 1 到 29 的公式或单元格引用。 Value1,value2,... 为 1 到 29 个数值参数，函数 CHOOSE 基于 index_num，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引用，已定义的名称、公式、函数或文本。 2、TRANSPOSE函数 TRANSPOSE用于返回区域的转置。函数 TRANSPOSE 必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与 array 的列数和行数相同。使用函数 TRANSPOSE 可以改变工作表或宏表中数组的垂直或水平走向。 语法形式为TRANSPOSE(array) Array为需要进行转置的数组或工作表中的单元格区域。所谓数组的转置就是，将数组的第一行作为新数组的第一列，数组的第二行作为新数组的第二列，以此类推。 示例，将原来为横向排列的业绩表转置为纵向排列。 图8 第一步，由于需要转置的为多个单元格形式，因此需要以数组公式的方法输入 公式。故首先选定需转置的范围。此处我们设定转置后存放的范围为A9.B14. 第二步，单击常用工具栏中的"粘贴函数"图标，将出现"粘贴函数"对话框，在"函数分类"框中选择"查找与引用函数"框中选择TRANSPOSE，此时在对话框的底部将出现该函数的简短解释。 单击"确定"后将弹出TRANSPOSE函数参数设置对话框。 图9 第三步，选择数组的范围即A2.F3 第四步，由于此处是以数组公式输入，因此需要按 CRTL+SHIFT+ENTER 组合键来确定为数组公式，此时会在公式中显示"{}"。随即转置成功，如图10所示。 图10 以上我们介绍了Excel的查找与引用函数，此类函数的灵活应用对于减少重复数据的录入是大有裨益的。此处只做了些抛砖引玉的示例，相信大家会在实际运用中想出更具实用性的应用方法。 六、Excel函数应用之统计函数 (周国彬 2001年06月26日 09:47) Excel的统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以用来统计样 本的方差、数据区间的频率分布等。是不是觉得好像是很专业范畴的东西,是的，统计工作表 函数中提供了很多属于统计学范畴的函数，但也有些函数其实在你我的日常生活中是很常用的， 比如求班级平均成绩，排名等。在 的函数不在此赘述，详细的使用方法可以参考Excel帮助及相关的书籍。 在介绍统计函数之前，请大家先看一下附表中的函数名称。是不是发现有 些函数是很类似的，只是在名称中多了一个字母A,比如，AVERAGE与 AVERAGEA;COUNT与COUNTA。基本上，名称中带A的函数在统计时不仅统 中笔者将主要介绍不带A的几种常见函数的用法。 TRIMMEAN 一、用于求平均值的统计函数AVERAGE、 1、求参数的算术平均值函数AVERAGE 语法形式为AVERAGE(number1,number2, ...) 其中Number1, number2, ...为要计算平均值的 1,30 个参数。这些参数 可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻 辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 2、求数据集的内部平均值TRIMMEAN 函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在 分析中剔除一部分数据的计算时，可以使用此函数。比如，我们在计算选手平均分数中常用去计数字，而且文本和逻辑值(如TRUE 和 FALSE)也将计算在内。在下文 掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算。 语法形式为TRIMMEAN(array,percent) 其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例，例如，如果 percent = 0.2，在 20 个数据点的集合中，就要除去 4 个数据点(20 x 0.2)，头部除去 2 个，尾部除去 2 个。函数 TRIMMEAN 将除去的数据点数目向下舍为最接近的 2 的倍数。 3、举例说明:示例中也列举了带A的函数AVERAGEA的求解方法。 求选手Annie的参赛分数。在这里，我们先假定已经将该选手的分数进行了从高到底的排序，在后面的介绍中我们将详细了解排序的方法。 图1 二、用于求单元格个数的统计函数COUNT 语法形式为COUNT(value1,value2, ...) 其中Value1, value2, ...为包含或引用各种类型数据的参数(1,30个)，但只有数字类型的数据才被计数。函数 COUNT 在计数时，将把数字、空值、逻辑值、 日期或以文字代表的数计算进去;但是错误值或其他无法转化成数字的文字则被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字;数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，应当使用函数 COUNTA。 举例说明COUNT函数的用途，示例中也列举了带A的函数COUNTA的用途。仍以上例为例，要计算一共有多少评委参与评分(用函数COUNTA)，以及有几个评委给出了有效分数(用函数COUNT)。 图2 三、求区域中数据的频率分布FREQUENCY 由于函数 FREQUENCY 返回一个数组，必须以数组公式的形式输入。 语法形式为FREQUENCY(data_array,bins_array) 其中Data_array为一数组或对一组数值的引用，用来计算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 返回零数组。Bins_array为一数组或对数组区域的引用，设定对 data_array 进行频率计算的分段点。如果 bins_array 中不包含任何数值，函数 FREQUENCY 返回 data_array 元素的数目。 看起来FREQUENCY的用法蛮复杂的，但其用处很大。比如可以计算不同工资段的人员分布，公司员工的年龄分布，学生成绩的分布情况等。这里以具体示例说明其基本的用法。 以计算某公司的员工年龄分布情况为例说明。在工作表里列出了员工的年龄。这些年龄为 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} 图3 四、一组用于求数据集的满足不同要求的数值的函数 1、求数据集的最大值MAX与最小值MIN 这两个函数MAX、MIN就是用来求解数据集的极值(即最大值、最小值)。函数的用法非常简单。语法形式为 函数(number1,number2,...)，其中Number1,number2,... 为需要找出最大数值的 1 到 30 个数值。如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。因此如果逻辑值和文本不能忽 者MINA 来代替。 略，请使用带A的函数MAXA或 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、 以上函数的示例 以某单位年终奖金分配表为例说明。在示例中，我们将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。 详细的公式写法可从图中清楚的看出，在此不再赘述。 图4 五、用来排位的函数RANK、PERCENTRANK 1、一个数值在一组数值中的排位的函数RANK 数值的排位是与数据清单中其他数值的相对大小，当然如果数据清单已经排过序了，则数值的排位就是它当前的位置。数据清单的排序可以使用Excel提供的排序功能完成。 语法形式为RANK(number,ref,order) 其中Number为需要找到排位的数字;Ref 为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。 如果 order 为 0 或省略，则Excel 将 ref 当作按降序排列的数据清单进行排位。 如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。 需要说明的是，函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。嗯，这就好像并列第几的概念啊。例如，在一列整数里，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7(没有排位为 6 的数值)。 2、求特定数值在一个数据集中的百分比排位的函数PERCENTRANK 此PERCENTRANK函数可用于查看特定数据在数据集中所处的位置。例如，可以使用函数 PERCENTRANK 计算某个特定的能力测试得分在所有的能力测试得分中的位置。 语法形式为PERCENTRANK(array,x,significance) 其中Array为彼此间相对位置确定的数字数组或数字区域。X为数组中需要得到其排位的值。Significance为可选项，表示返回的百分数值的有效位数。如果省略，函数 PERCENTRANK 保留 3 位小数。 3、与排名有关的示例 仍以某单位的年终奖金分配为例说明，这里以员工Annie的排名为例说明公式的写法。 奖金排名的公式写法为: =RANK(C3,$C$3:$C$12) 百分比排名的公式写法为: =PERCENTRANK($C$3:$C$12,C3) 图5 以上我们介绍了Excel统计函数中比较常用的几种函数，更多的涉及专业领域的统计函数可以参看附表以及各种相关的统计学书籍。 附表: 七、Excel函数应用之工程函数 (周国彬 2001年07月04日 10:03) Excel的工程函数与统计函数类似，都是属于比较专业范畴的函数。因此，在文中笔者也仅介绍几种比较常用的工程函数，更多的请参考Excel帮助和专业的书籍。顾名思义，工程工作表函数就是用于工程分析的函数。Excel中一共提供了近40个工程函数。工程工作表函数由"分析工具库"提供。如果您 找不到此类函数的话，可能需要安装"分析工具库"。 一、"分析工具库"的安装 如图所示 图1 "工具"菜单中，单击"加载宏"命令。 (1)在 (2)如果"加载宏"对话框中没有"分析工具库"，请单击"浏览"按钮，定位到"分析工具库"加载宏文件"Analys32.xll"所在的驱动器和文件夹(通常位于"Microsoft 序。 (3) 选中"分析工具库"复选框。 二、工程函数的分类 在Excel帮助系统中将工程函数大体可分为三种类型，即: (1)对复数进行处理的函数 (2)在不同的数字系统(如十进制系统、十六进制系统、八进制系统和二进制系统)间进行数值转换的函数 (3)在不同的度量系统中进行数值转换的函数 在文中为了对函数的解释更清晰，笔者把工程函数分为如下的六种类型，即: (1)贝赛尔(Bessel)函数 (2)在不同的数字系统间进行数值转换的函数 (3)用于筛选数据的函数 (4)度量衡转换函数 (5)与积分运算有关的函数 (6)对复数进行处理的函数 下面逐一的对于这些工程函数进行介绍。 1、贝赛尔(Bessel)函数 贝赛尔(Bessel)函数是特殊函数中应用最广泛的一种函数，在理论物理研究、应用数学、大气科学以及无线电等工程领域都有广泛的应用。在Excel中一共提供了四个函数，即:BESSELI、BESSELJ 、BESSELK、BESSELY。 语法形式为:函数(x,n) 其中，X为参数值，N为函数的阶数。如果 n非整数，则截尾取整。需说明的是，如果 x 为非数值型，则贝赛尔(Bessel)函数返回错误值 #VALUE!。如果 n 为非数值型，则贝赛尔(Bessel)函数返回错误值 #VALUE!。如果 n ,0，则贝赛尔(Bessel)函数返回错误值 #NUM!。 Office\Office\Library\Analysis"文件夹中);如果没有找到该文件，应运行"安装"程 2、在不同的数字系统间进行数值转换的函数 Excel工程函数中提供二进制、八进制、十进制与十六进制之间的数值转换函数。 这类工程函数名称非常容易记忆，只要记住二进制为BIN，八进制为OCT，十进制为DEC，十六进制为HEX。再记住函数名称中间有个数字2就可以容易的记住这些数值转换函数了。比如，如果需要将二进制数转换为十进制，应用的函数为前面BIN，中间加个2，后面为DEC，合起来这个函数就是BIN2DEC。 简单列表为: 图2 此类数值转换函数的语法形式也很容易记忆。 比如，将不同进制的数值转为十进制的语法形式为:函数(number)，其中Number为待转换的某种进制数。 又如，将不同进制转换为其他进制的数值的语法形式为:函数(number,places)其中Number为待转换的数。Places为所要使用的字符数。当需要在返回的数值前置零时 places 尤其有用。 3、用于筛选数据的函数DELTA与GESTEP (1)用以测试两个数值是否相等的函数DELTA DELTA用以测试两个数值是否相等。如果 number1=number2，则返回 1，否则返回 0。可用此函数筛选一组数据，例如，通过对几个 DELTA 函数求和，可以计算相等数据对的数目。该函数也称为 Kronecker Delta 函数。 语法形式为DELTA(number1,number2) 其中Number1为第一个参数，Number2为第二个参数。如果省略，假设 Number2 值为零。如果number1或者number2为非数值型，则函数 DELTA 返回错误值 #VALUE!。 (2)可筛选数据的函数GESTEP 使用GESTEP函数可筛选数据。如果 Number 大于等于 step，返回 1，否则返回 0。例如，通过计算多个函数 GESTEP 的返回值，可以检测出数据集中超过某个临界值的数据个数。 语法形式为:GESTEP(number,step) 其中Number为待测试的数值。Step称阀值。如果省略 step，则函数 GESTEP 假设其为零。需注意的是，如果任一参数非数值，则函数 GESTEP 返回错误值 #VALUE! (3)以考试成绩统计为例说明函数的用法 例:某院校举行数学模拟考试，正在进行成绩排定。提出的评定 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 为求出成 绩超过90分的考生人数有哪些人。 在这里我们采用GEStep函数来完成统计，首先会为每位考生的成绩做标记。超过90分的标记为1，否则为0，然后对所有考生的标记进行汇总，即可求出有多少人超过90分。 图3 以1号Annie的成绩为例，成绩为98分，超90分。具体公式为: =GESTEP(C4,90) 4、度量衡转换函数CONVERT CONVERT函数可以将数字从一个度量系统转换到另一个度量系统中。 语法形式为CONVERT(number,from_unit,to_unit) 其中Number为以 from_units 为单位的需要进行转换的数值。From_unit为数值 number 的单位。To_unit为结果的单位。 函数 CONVERT 5、与积分运算有关的函数ERF与ERFC ERF为返回误差函数在上下限之间的积分。 其语法形式为:ERF(lower_limit,upper_limit) 其中，Lower_limit为ERF函数的积分下限。Upper_limit 为ERF函数的积分上限。如果省略，默认为零。 ERFC为返回从 x 到 ?(无穷)积分的 ERF 函数的余误差函数。其语法形式为: ERFC(x) 其中X为ERF函数积分的下限。 6、与复数运算有关的函数 还记得中学时代学过的复数吗,是不是还记得当时求复数的模等计算的繁复,Excel的工程函数中提供的多种与复数运算有关的函数，你可以用它来验证自己的运算结果的正确性啊。关于有哪些函数与复数运算有关，可以察看所附的表格。这里将以简单的事例说明函数的使用方法。注意到在工程函数中有一些前缀为im的函数了吗,这些就是与复数运算有关的函数。 举例，已知复数5+12i，请用函数求解该复数的共轭复数、实系数、虚系数、模等。 图4 八、Excel函数应用之财务函数 像统计函数、工程函数一样，在Excel中还提供了许多财务函数。财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。这些财务函数大体上可分为四类:投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识，只要填写变量值就可以了。在下文中，凡是投资的金额都以负数形式表示，收益 以正数形式表示。 在介绍具体的财务函数之前，我们首先来了解一下财务函数中常见的参数: 未来值 (fv)--在所有付款发生后的投资或贷款的价值。 期间数 (nper)--为总投资(或贷款)期，即该项投资(或贷款)的付款期总数。 付款 (pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。 现值 (pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率 (rate)--投资或贷款的利率或贴现率。 类型 (type)--付款期间内进行支付的间隔，如在月初或月末，用0或1表示。 日计数基准类型(basis)--为日计数基准类型。Basis为0 或省略代表US (NASD) 30/360 ，为1代表实际天数/实际天数 ，为2代表实际天数/360 ，为3代表实际天数/365 ，为4代表欧洲30/360。 接下来，我们将分别举例说明各种不同的财务函数的应用。在 1、在"工具"菜单上，单击"加载宏"。 2、在"可用加载宏"列表中，选中"分析工具库"框，再单击"确定"。 一、投资计算函数 投资计算函数可分为与未来值fv有关，与付款pmt有关，与现值pv有关，与复利计算有关及与期间数有关几类函数。 1、与未来值fv有关的函数--FV、FVSCHEDULE 2、与付款pmt有关的函数--IPMT、ISPMT、PMT、PPMT NPV、PV、XNPV 3、与现值pv有关的函数-- 4、与复利计算有关的函数--EFFECT、NOMINAL 5、与期间数有关的函数--NPER 在投资计算函数中，笔者将重点介绍FV、NPV、PMT、PV函数。 (一) 求某项投资的未来值FV 在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用Excel函数FV进行计算后，可以帮助我们进行一些有 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。 语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率，是一固定值，nper为总投资(或贷款)期，即该项投资(或贷款)的付款期总数，pv为各期所应付给(或得到)的金额，其数值在整个年金期间(或投资期内)保持不变，通常Pv包括本金和利息，但不包括其它费用及税款，pv为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零，type为数字0或1，用以指定各期的付款时间是在期初还是期末，如果省略t，则假设其值为零。 例如:假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息(月利为2.25%/12)，那么两年以后该账户的存款额会是多少呢, 公式写为:FV(2.25%/12, 24,-2000,0,1) 图1 (二) 求投资的净现值NPV NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出(负值)和收入(正值)的当前值的总和。 语法形式为:NPV(rate,value1,value2, ...) 其中，rate为各期贴现率，是一固定值;value1,value2,...代表1到29笔支出及收入的参数值，value1,value2,...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。需要注意的是:NPV按次序使用value1,value2，来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式，则都会计算在内;如果参数是错误值或不能转化为数值的文字，则被忽略，如果参数是一个数组或引用，只有其中的数值部分计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。 例如，假设开一家电器经销店。初期投资,200,000，而希望未来五年中各年的收入分别为,20,000、,40,000、,50,000、,80,000和,120,000。假定每年的贴现率是8%(相当于通贷膨胀率或竞争投资的利率)，则投资的净现值的公式是: =NPV(A2, A4:A8)+A3 在该例中，一开始投资的,200,000并不包含在v参数中，因为此项付款发生在第一期的期初。假设该电器店的营业到第六年时，要重新装修门面，估计要付出,40,000，则六年后书店投资的净现值为: =NPV(A2, A4:A8, A9)+A3 如果期初投资的付款发生在期末，则 投资的净现值的公式是: =NPV(A2, A3:A8) 图2 (三) 求贷款分期偿还额PMT PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的"分期付款"。比如借购房贷款或其它贷款时，可以计算每期的偿还额。 其语法形式为:PMT(rate,nper,pv,fv,type) 其中，rate为各期利率，是一固定值，nper为总投资(或贷款)期，即该项投资(或贷款)的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零(例如，一笔贷款的未来值即为零)，type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。 例如，需要10个月付清的年利率为8%的,10,000贷款的月支额为: 8%/12,10,10000) 计算结果为:-,1,037.03。 PMT( (四) 求某项投资的现值PV PV函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。 其语法形式为:PV(rate,nper,pmt,fv,type) 其中Rate为各期利率。Nper为总投资(或贷款)期，即该项投资(或贷款)的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零(一笔贷款的未来值即为零)。Type用以指定各期的付款时间是在期初还是期末。 例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报,600。此项年金的购买成本为80,000，假定投资回报率为8%。那么该项年金的现值为: PV(0.08/12, 12*20,600,0) 计算结果为:,-71,732.58。 负值表示这是一笔付款，也就是支出现金流。年金(,-71,732.58)的现值小于实际支付的(,80,000)。因此，这不是一项合算的投资。 图3 二、 折旧计算函数 、SYD、 折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLNVDB。这些函数都是用来计算资产折旧的，只是采用了不同的计算方法。这里，对于具体的计算公式不再赘述，具体选用哪种折旧方法，则须视各单位情况而定。 三、偿还率计算函数 偿还率计算函数主要用以计算内部收益率，包括IRR、MIRR、RATE和XIRR几个函数。 (一) 返回内部收益率的函数--IRR IRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付(负值)和收入(正值)。 其语法形式为IRR(values,guess) 其中values为数组或单元格的引用，包含用来计算内部收益率的数字， values必须包含至少一个正值和一个负值，以计算内部收益率，函数IRR根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略;guess为对函数IRR计算结果的估计值，excel使用迭代法计算函数IRR从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%，如果函数IRR经过20次迭代，仍未找到结果，则返回错误值#NUM～，在大多数情况下，并不需要为函数IRR的计算提供guess值，如果省略guess，假设它为0.1(10%)。如果函数IRR返回错误值#NUM～，或结果没有靠近期望值，可以给guess换一个值再试一下。 例如，如果要开办一家服装商店，预计投资为,110,000，并预期为今后五年的净收益为:,15,000、,21,000、,28,000、,36,000和,45,000。分别求出投资两年、四年以及五年后的内部收益率。 图4 在工作表的B1:B6输入数据"函数.xls"所示，计算此项投资四年后的内部收益率IRR(B1:B5)为-3.27%;计算此项投资五年后的内部收益率IRR (B1:B6)为8.35%;计算两年后的内部收益率时必须在函数中包含guess，即IRR(B1:B3，-10%)为-48.96%。 (二) 用RATE函数计算某项投资的实际赢利 在经济生活中，经常要评估当前某项投资的运作情况，或某个新企业的现状。例如某承包人建议你贷给他30000元，用作公共工程建设资金，并同意每年付给你9000元，共付五年，以此作为这笔贷款的最低回报。那么你如何去决策这笔投资,如何知道这项投资的回报率呢,对于这种周期性偿付或是一次偿付完的投资，用RATE函数可以很快地计算出实际的赢利。其语法形式为 具体操作步骤如下: RATE(nper,pmt,pv,fv,type,guess)。 2、插入函数RATE，打开"粘贴函数"对话框。 3、在"粘贴函数"对话框中，在"Nper"中输入偿还周期5(年),在"Pmt"中输入7000(每年的回报额)，在"Pv"中输入,30000(投资金额)。即公式为=RATE(5,9000,-30000) 4、确定后计算结果为15.24,。这就是本项投资的每年实际赢利，你可以根据这个值判断这个赢利是否满意，或是决定投资其它项目，或是重新谈判每年的回报。 四、债券及其他金融函数 债券及其他金融函数又可分为计算本金、利息的函数，与利息支付时间有关的函数、与 1、选取存放数据的单元格，并按上述相似的方法把此单元格指定为"百分数"的格式。 利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。 1、计算本金、利息的函数--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM 2、与利息支付时间有关的函数--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD 3、 与利率收益率有关的函数--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT 4、与修正期限有关的函数--DURATION、MDURATION 5、与有价证券有关的函数--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED 6、与证券价格表示有关的函数--DOLLARDE、DOLLARFR 在债券及其他金融函数中，笔者将重点介绍函数ACCRINT、CUMPRINC、DISC。 (一)求定期付息有价证券的应计利息的函数ACCRINT ACCRINT函数可以返回定期付息有价证券的应计利息。 其语法形式为ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis) 其中issue为有价证券的发行日，first_interest为有价证券的起息日，settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，rate为有价证券的年息票利率，par为有价证券的票面价值，如果省略par，函数ACCRINT就会自动将par设置为,1000，frequency为年付息次数，basis为日计数基准类型。 例如，某国库券的交易情况为:发行日为2008年3月1日;起息日为2008年8月31日;成交日为2008年5月1日，息票利率为10.0%;票面价值为,1,000;按半年期付息;日计数基准为30/360，那么应计利息为: 图5 (二)求本金数额CUMPRINC CUMPRINC函数用于返回一笔货款在给定的st到en期间累计偿还的本金数额。其语法形式为 CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate为利率，nper为总付款期数，pv为现值，start_period为计算中的首期，付款期数从1开始计数，end_period为计算中的末期，type为付款时间类型。 例如，一笔住房抵押贷款的交易情况如下:年利率为9.00%;期限为30年;现值为,125，000。由上述已知条件可以计算出:r=9.00%/12=0.0075，np=30*12=360。 图6 那么该笔贷款在第下半年偿还的全部本金之中(第7期到第12期)为: =CUMPRINC(A2/12,A3*12,A4,7,12,0)计算结果为:-436.568194。 该笔贷款在第一个月偿还的本金为:=CUMPRINC(A2/12,A3*12,A4,1,1,0)计算结果为:-68.27827118。 (三) 求有价证券的贴现率DISC DISC函数返回有价证券的贴现率。 其语法形式为DISC(settlement,maturity,pr,redemption,basis) 其中settlement为有价证券的成交日，即在发行日之后，有价证券卖给购买者的日期，maturity为有价证券的到日期，到期日是有价证券有效期截止时的日期，pr为面值为",100"的有价证券的价格，redemption为面值为",100"的有价证券的清偿价格，basis为日计数基准类型。 例如:某债券的交易情况如下:成交日为99年3月18日，到期日为99年8月7日，价格为,48.834，清偿价格为,52，日计数基准为实际天数/360。那么该债券的贴现率为: DISC("99/3/18","99/8/7",48.834,52,2) 计算结果为:0.154355363。 九、Excel函数应用之信息函数 常用的函数及其参数的应用做出示例。 一、用于返回有关单元格格式、位置或内容的信息的函数CELL 在Excel函数中有一类函数，它们专门用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等，这一类函数我们称为信息函数。在 CELL函数用于返回某一引用区域的左上角单元格的格式、位置或内容等信息。其语法形式为， CELL(info_type,reference) 其中Info_type为一个文本值，指定所需要的单元格信息的类型。Reference则表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回给最后更改的单元格。 首先看一下，info_type 的可能值及相应的结果。 再看一下当info_type 为"format"，以及引用为用内置数字格式设置的单元格时，函数 CELL 返回文本值的情况。 图1 函数CELL主要用于与其他电子表格程序兼容。在随后的示例中我们来学习一下如何使用CELL函数来获取单元格的格式、位置及内容的信息。 例:想要获知单元格A1到B4区域内比如行号、列宽、单元格内容等信息。 图2 二、用于计算区域内空白单元格的个数COUNTBLANK COUNTBLANK用于计算指定单元格区域中空白单元格的个数。其语法形式为COUNTBLANK(range) 其中Range为需要计算其中空白单元格个数的区域。需要注意的是， 即使单元格中含有返回值为空文本 ("")的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。 30,"",A3)，但 在如图所示的例子中，单元格B3包括公式=IF(A3, 该公式计算返回的值为空文本""，所以该单元格被计算为空单元格。而单元格A3为零值的单元格，不计作空单元格。 试比较图3-A与图3-B的结果的区别，两者的差别在于图3-B中单元格B3的公式为=IF(A3,30,"",A3)，计算后返回的结果为0，因此不计作空单元格。 图3A 图3B 三、返回对应于错误类型的数字的函数ERROR.TYPE ERROR.TYPE返回对应于 Microsoft Excel 中某一错误值的数字，或者，如果没有错误则返回 #N/A。语法形式为ERROR.TYPE(error_val) 其中Error_val为需要得到其标号的一个错误值。尽管 error_val 可以为实际的错误值，但它通常为一个单元格引用，而此单元格中包含需要检测的公式。以下即为error_val的函数返回结果。 图4 还记得逻辑函数IF吗,在函数 IF 中可以使用 ERROR.TYPE 检测错误值，并返回文本字符串(如，消息)来取代错误值。具体参看示例。