Excel逻辑函数（精选7篇）
Excel逻辑函数 篇1
用来判断真假值,或者进行复合检验的Excel函数,我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。
一、AND、OR、NOT函数
这三个函数都用来返回参数逻辑值。详细介绍见下:
1、AND函数
所有参数的逻辑值为真时返回TRUE;只要一个参数的逻辑值为假即返回FALSE。简言之,就是当AND的参数全部满足某一条件时,返回结果为TRUE,否则为FALSE。
语法为AND(logical1,logical2,…),其中Logicall,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
2、OR函数
OR函数指在其参数组中,任何一个参数逻辑值为TRUE,即返回TRUE。它与AND函数的区别在于,AND函数要求所有函数逻辑值均为真,结果方为真。而OR函数仅需其中任何一个为真即可为真。比如,上面的示例2,如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE
3、NOT函数
NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时,可以使用NOT函数。简言之,就是当参数值为TRUE时,NOT函数返回的结果恰与之相反,结果为FALSE.
比如NOT(2+2=4),由于2+2的结果的确为4,该参数结果为TRUE,由于是NOT函数,因此返回函数结果与之相反,为FALSE。
二、TRUE、FALSE函数
TRUE、FALSE函数用来返回参数的逻辑值,由于可以直接在单元格或公式中键入值TRUE或者FALSE。因此这两个函数通常可以不使用。
三、IF函数
1、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工作表函数。
2、IF函数应用
(1)输出带有公式的空白表单
以图4～5所示的人事状况分析表为例,由于各部门关于人员的组成情况的数据尚未填写,在总计栏(以单元格G5为例)公式为:
我们看到计算为0的结果。如果这样的表格打印出来就页面的美观来看显示是不令人满意的。是否有办法去掉总计栏中的0呢?你可能会说,不写公式不就行了。当然这是一个办法,但是,如果我们利用了IF函数的话,也可以在写公式的情况下,同样不显示这些0。如何实现呢?只需将总计栏中的公式(仅以单元格G5为例)改写成:
通俗的解释就是:如果SUM(C5:F5)不等于零,则在单元格中显示SUM(C5:F5)的结果,否则显示字符串。
几点说明:
①SUM(C5:F5)不等于零的正规写法是SUM(C5:F5)<>0,在EXCEL中可以省略<>0;
②””表示字符串的内容为空,因此执行的结果是在单元格中不显示任何字符。
(2)不同的条件返回不同的结果
如果对上述例子有了很好的理解后,我们就很容易将IF函数应用到更广泛的领域。比如,在成绩表中根据不同的成绩区分合格与不合格。现在我们就以某班级的英语成绩为例具体说明用法。
某班级的成绩如图6所示,为了做出最终的综合评定,我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的,否则记作不合格。
根据这一规则,我们在综合评定中写公式(以单元格B12为例):
语法解释为,如果单元格B11的值大于60,则执行第二个参数即在单元格B12中显示合格字样,否则执行第三个参数即在单元格B12中显示不合格字样。
在综合评定栏中可以看到由于C列的同学各科平均分为54分,综合评定为不合格。其余均为合格。
(3)多层嵌套函数的应用
在上述的例子中,我们只是将成绩简单区分为合格与不合格,在实际应用中,成绩通常是有多个等级的,比如优、良、中、及格、不及格等。有办法一次性区分吗?可以使用多层嵌套的办法来实现。仍以上例为例,我们设定综合评定的规则为当各科平均分超过90时,评定为优秀。如图7所示。
说明:为了解释起来比较方便,我们在这里仅做两重嵌套的示例,您可以按照实际情况进行更多重的嵌套,但请注意Excel的IF函数最多允许七重嵌套。
根据这一规则,我们在综合评定中写公式(以单元格F12为例):
语法解释为,如果单元格F11的值大于60,则执行第二个参数,在这里为嵌套函数,继续判断单元格F11的值是否大于90 (为了让大家体会一下AND函数的应用,写成AND(F11>90),实际上可以仅写F11>90),如果满足在单元格F12中显示优秀字样,不满足显示合格字样,如果F11的值以上条件都不满足,则执行第三个参数即在单元格F12中显示不合格字样。
在综合评定栏中可以看到由于F列的同学各科平均分为92分,综合评定为优秀。
3、根据条件计算值
在了解了IF函数的使用方法后,我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。例如,如果要计算单元格区域中某个文本串或数字出现的次数,则可使用COUNTIF工作表函数。如果要根据单元格区域中的某一文本串或数字求和,则可使用SUMIF工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的应用。
COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。
语法形式为COUNTIF(Range,Criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。例如,条件可以表示为32、"32"、">32"、"apples"。
(1)成绩表
这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是:每位学生取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示。
根据这一规则,我们在优秀门数中写公式(以单元格B13为例):
语法解释为,计算B4到B10这个范围,即jarry的各科成绩中有多少个数值大于90的单元格。
在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。
(2)销售业绩表
销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如,可能希望计算销售人员的订单数,然后汇总每个销售人员的销售额,并且根据总发货量决定每次销售应获得的奖金。
原始数据表如图9所示(原始数据是以流水单形式列出的,即按订单号排列)
按销售人员汇总表如图10所示。
如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售人员汇总的表单样式,如图所示。然后分别计算订单数、订单总额、销售奖金。
①订单数—用COUNTIF计算销售人员的订单数。以销售人员ANNIE的订单数公式为例。公式:
语法解释为计算单元格A17(即销售人员ANNIE)在"销售人员”清单的范围内(即图9所示的原始数据表)出现的次数。
这个出现的次数即可认为是该销售人员ANNIE的订单数。
②订单总额—用SUMIF汇总每个销售人员的销售额。以销售人员ANNIE的订单总额公式为例。公式:
此公式在"销售人员"清单中检查单元格A17中的文本(即销售人员ANNIE),然后计算"订单金额"列()中相应量的和。
这个相应量的和就是销售人员ANNIE的订单总额。
③销售奖金—用IF根据订单总额决定每次销售应获得的奖金。
假定公司的销售奖金规则为当订单总额超过5万元时,奖励幅度为百分之十五,否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为:
如果订单总额小于50000则奖金为10%;如果订单总额大于等于50000,则奖金为15%。
至此,我们已完全了解了EXCEL函数的逻辑函数,相信大家在实际工作中会想出更多更有用的运用。
Excel逻辑函数 篇2
用途:将数字转换为欧元形式，将数字由欧元形式转换为欧盟成员国货币形式，或利用欧元作为中间货币将数字由某一欧盟成员国货币转化为另一欧盟成员国货币的形式(三角转换关系)，
语法:EUROCONVERT(number，source，target，full_precision，triangulation_precision)
参数:Number为要转换的货币值，或对包含该值的单元格的引用。Source是由三个字母组成的字符串，或对包含字符串的单元格的引用，该字符串对应于源货币的ISO代码。EUROCONVERT函数中可以使用下列货币代码:
国家 / 地区
基本货币单位
ISO 代码
比利时
法郎
BEF
卢森堡
法郎
LUF
德国
德国马克
DEM
西班牙
西班牙比塞塔
ESP
法国
法郎
FRF
爱尔兰
爱尔兰磅
IEP
意大利
里拉
ITL
荷兰
荷兰盾
NLG
奥地利
奥地利先令
ATS
葡萄牙
埃斯库多
PTE
芬兰
芬兰马克
FIM
希腊
德拉克马
GRD
欧盟成员国
欧元
EUR
2.SQL.REQUEST
用途:与外部数据源连接，从工作表运行查询，然后 SQL.REQUEST 将查询结果以数组的形式返回，而无需进行宏编程，
语法:SQL.REQUEST(connection_string，output_ref，driver_prompt，query_text，col_names_logical)
参数:Connection_string提供信息，如数据源名称、用户ID和密码等。Output_ref对用于存放完整的连接字符串的单元格的引用。Driver_prompt指定驱动程序对话框何时显示以及何种选项可用。Column_names_logical指示是否将列名作为结果的第一行返回。
如果要将列名作为结果的第一行返回，请将该参数设置为TRUE。如果不需要将列名返回，则设置为FALSE。如果省略column_names_logical，则SQL.REQUEST函数不返回列名。
在Excel中绘制数学函数曲线 篇3
在下面的步骤1至步骤4中，我们将通过抛物线函数f（x）=x2介绍绘图的基本原理。步骤5至步骤8，我们会介绍一些其他图形的处理方法 。
操作步骤
1、定义值范围
我们将x轴的值范围定义为-3到3，首先在“A3”和“A4”单元格输入值“-3”和“-2.5”。选择这两个单元格，拖动选择框右下角到单元格“A15”，Excel将自动按照“A3”和“A4”单元格值的大小差距，自动在其他单元格输入等距的数值。
2、输入公式
抛物线函数f（x）=x2，在Excel中，我们可以在f（x）所对应的单元格“B3”中以公式“=A3*A3”来进行计算。我们可以在单元格“B3”中输入该公式，回车即可执行计算。
3、计算y轴坐标
接下来选择单元格“B3”，拖动选择框右下角到单元格“B15”，Excel将自动为每一个单元格输入公式，并自动调整公式的参数，计算出每一个x轴数值对应的y轴坐标。
4、画出抛物线
选择单元格区域“B3”到“B15”并选择合适的图表类型，例如选择“插入”选项卡“图表”中的“散点图”，我们马上就可以看到根据数据画出的抛物线。接下来，我们还可以根据自己的需要，调整图形以及增减图形中的元素。例如如果希望看到抛物线的数值，可以通过左上角的“添加图表元素”选择“数据标签”，并根据自己的喜好选择数值显示的方式。
5、输入有极点的有理函数
在Excel中输入有极点的有理函数，例如直接输入公式“（1/A3^2）”以计算1/x2。有理函数的极点在Excel中将始终以错误信息“#DIV/0！”显示。不过，我们可以通过IF函数来解决类似的问题。首先，选择单元格“B3”并单击上方的公式图标“f（x）”，选择公式类型为“IF”并确认。在对话框中的第一个输入框中键入“A3=0”，然后在第二个输入框中键入““””，最后在第三个输入框中键入“（1/A3^2）”，按回车键确认。选择单元格“B3”，拖动选择框右下角到单元格“B15”。现在，我们将看到“B9”的单元格是空着的，也就是有理函数的极点。
6、选择和编辑图形类型
在通过数据生成图形之后，我们可以根据需要调整图形。以有极点的有理函数图形为例，我们可以通过“更改图表类型”选择其他类型的图表，例如我们可以选择“散点图”中的“带平滑线和数据标点的散点图”。在创建图表之后，在x轴的极点位置上双击，我们还可以通过选项“无线条”清除不必要的线，如有理函数极点部分的线条。
7、绘制几何图形
在Excel中通过数据直接绘制类似于等腰三角形之类的几何图形有一定的困难，但是通过在x轴和y轴上定义坐标，我们可以绘制几何图形。例如在“A”列中依次输入数据“0”、“30”、“15”、“0”，在“B”列中依次输入数据“5”、“5”、“20”、“5”，通过这两组数据并依上面介绍的方法画图，即可画出一个等腰三角形。
8、计算和绘制正弦曲线
Excel逻辑函数 篇4
Excel是微软办公系列软件中的一个重要组成部分, 利用它可以进行数据处理、统计分析, 并对数据进行辅助决策, 在管理、统计财经、金融等众多领域得到了越来越广泛的应用。作为Excel软件的重要组成部分, 函数在软件的实践应用中起到越来越大的作用, Excel中的函数共分为12大类, 分别为日期与时间函数、逻辑函数、数据库函数、统计函数、工程函数、外部函数、数学与三角函数、查找与引用函数、文本函数、财务函数、信息函数以及用户自定义函数, 这些函数的运用, 使得Excel的功能异常强大, 给人们的数据处理带来了极大的便利。本文以逻辑函数为例, 浅析其在实践中的应用。
1 逻辑函数简介
在Excel中, 逻辑函数用来判断真假值, 或者进行复合检验。逻辑值是可以进行直接运算的文本, 一般有TRUE和FALSE两种, 分别代表逻辑真和逻辑假, 有时也用1和0来表示。在Excel中, 逻辑函数有6种, 分别为AND、OR、IF、NOT、FALSE、TRUE函数。这些函数在数据处理中各自扮演着重要的角色。
AND函数的语法为AND (数值1, 数值2, 数值3, ……) , 其中数值1~3均为逻辑值, 当所选区域有文本或空格时, 做忽略处理。全部选区中的逻辑值都为真时, 最终结果才为真。
OR函数的语法类似于AND函数, 所不同的是, 在全部选区中, 只要有一个逻辑值为真, OR函数的最终结果即为真。
IF函数是Excel逻辑函数中最常用的函数, 其功能和语法也最为强大。IF函数的语法为IF (数值所满足的条件, 条件为真时的返回值, 条件为假时的返回值) , 一般情况下, 真假返回值分别设置为TURE和FALSE逻辑值。IF函数可以自定义一个条件, 并对此条件进行判断, 在实际运用中, 灵活使用IF函数可以有效地提高工作效率。
NOT函数、FALSE函数和TRUE函数在实践中使用频率不是很高, NOT函数主要是对所判断条件的真假进行取反, FALSE函数和TRUE函数用来返回逻辑值FALSE和TRUE, 由于逻辑值可以在Excel中直接输入, 所以这两个函数在实践中并不常用。
2 逻辑函数的应用
2.1 IF、AND及OR函数的应用
教师在考试结束后, 必然要对大量的学生成绩进行统计分析, 以了解各个分数段的学生人数, 以便进行后续的分析与总结。由于学生人数及成绩的数量巨大, 如果以人工方式统计, 必然耗时耗力, Excel的逻辑函数恰好可以解决这个问题。我们以图1-图3所示的学生成绩表为例。
在成绩出来后, 教师往往会先查看本班各个分数段的学生, 在实践中可以先通过IF函数, 对学生成绩进行条件判断, 由于IF函数可以套用, 所以可以直接用一个函数, 将从不及格到优秀5个级别, 通过函数一次性区别出来, 函数表示为=IF (C3<60, "不及格", IF (C3<70, "合格", IF (C3<80, "中等", IF (C3<90, "良好", IF (C3<=100, "优秀") ) ) ) ) , 需要注意的是在公式中, 所有字符均为英文字符, 其中的文本需要使用引号, 具体转换方法见图2, 经过公式的复制后, 学生的成绩等次便一目了然了, 具体结果见图3。
学生成绩的等次有了后, 总分的计算和分析即是成绩统计的重点之一, 在实践中, 有时需要查看是否有低于某一标准的总分, 以便发出学业预警通知, 对于大量的成绩来说, 如果靠人工查找, 往往费时费力, 我们可以通过IF函数将总分转换为逻辑值, 再通过AND函数对逻辑值进行计算, 这样可以轻易得知是否有低于标准的总分, 如果没有, 则很轻易地完成了筛选与判断, 如果有, 我们则可以分段使用AND函数, 确定低分的范围, 为人工查找减轻负担。比如我们规定总分为350以下的学生, 必须向其发放学业预警通知, 在面对大量数据时, 首先用IF函数进行逻辑转换, 将大于350的分数转换为TRUE, 小于350的分数转换为FALSE, 随后, 通过AND函数, 对所有逻辑结果进行计算, 公式为=AND (I3:I34) 。如果结果返回如图4所示, 则说明所有学生的成绩均在350以上, 此时, 可以轻松判断出无需向任何学生发出学业预警通知。反之, 如果AND函数的计算值为FALSE, 我们可以继续使用AND函数分段进行计算, 使用公式=AND (I3:I11) 后, 每隔一段对公式进行复制, 缩小范围, 以便进行人工搜索, 本文截取部分结果如图5所示, 对于大批量的数据, 笔者认为可以对分段进行灵活调整。
除了AND函数外, OR函数同样适用于对IF函数逻辑返回值的计算, 比如想知道在这个班级中, 是否有总分高于400分的优秀学生, 我们可以先使用IF函数, 对学生成绩进行逻辑判断, 然后使用OR函数计算, 即可知道优秀学生的存在情况, 如图6所示。
2.2 NOT、TURE及FALSE函数的应用
NOT函数用于求反, 在实践中, 可以单独使用, 也可与其它函数配合使用。如图7所示, 在班级成绩的比较过程中, 我们可以使用IF函数与NOT函数配合, 将各科成绩中, 平均分较高的班级名称计算出来如图8所示。D2单元格中的公式为=IF (NOT (B2>C2) , "二班", "一班") , NOT函数在公式中, 起到了取反的作用。另外, NOT函数也可以单独运用, 比如我们要将一班平均分高的值返回为FALSE逻辑值, 将二班平均分高的值返回为TRUE逻辑值, 以便后续进一步操作与分析, 我们即可使用NOT函数进行计算, E2单元格的计算公式为=NOT (D2="一班") , 结果如图9所示。
TRUE函数和FALSE函数用于返回逻辑值, 其参数一般为空。利用公式的复制, 可以方便地快速返回逻辑值。如图10、11所示, 在C2中的公式为=TRUE () , 在E2中的公式为=FALSE () , 复制后即可快速得到真假值。
3 结语
Excel是办公套装软件中不可缺少的一个组成部分, 而逻辑函数亦是Excel中的重要组成部分。逻辑函数的运用有效地提高了工作效率, 除了在学生成绩的统计与分析中能极大地对教师起到帮助外, 逻辑函数在各行各业的数据处理中都有着不可替代的作用, 灵活运用好逻辑函数, 并能熟练地与其它函数套用, 必将在实践应用中起到事半功倍的功效。
摘要：逻辑函数是Excel函数的重要组成部分, 逻辑函数的使用可以进行真假值的判断及进行复合检验, 有效地使用逻辑函数可以极大地提高工作效率, 给人们的数据处理带来极大便利。通过分析在实践中经常会遇到的案例, 对逻辑函数的使用进行了分析, 提出了逻辑函数灵活运用的方法。
关键词：Excel,逻辑函数,实践应用
参考文献
[1]赵延惠.用Excel统计班级成绩[J].思茅师范高等专科学校学报, 2006 (3) .
[2]赵磊.Excel中进行数据分析的几个常用函数[J].中小企业管理与科技, 2010 (6) .
[3]张山风.Excel中各分数段人数的统计方法[J].办公自动化, 2008 (8) .
Excel逻辑函数 篇5
Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式，以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。
参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。
按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们;而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。
什么是公式？
函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式，
以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。
如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。
Excel的函数功能 篇6
Excel中使用的函数其实是工作表函数 (Worksheet Functions) , 通常简称为Excel函数。它是使用一些内部预先定义的顺序, 并按照特定的数值和参数结构来进行运算的。我们可以很直接的套用这些函数来对一定范围内的数据进行运算。例如用来进行时间的计算, 查找和定位数据, 判断条件是否存在, 快速实现多表数据标示, 单条件求和, 现金流的收益计算, 将文本表达式生成数据等等。比如, 财会人员利用随机函数计算年终个人所得税。
函数是否可以允许多重计算呢?如一个函数的计算中加入另外函数的参数, 这个我们确定是可以的, 这里就引用到了嵌套函数。嵌套函数的含义, 就是指函数的参数如果在特定的公式中, 这些参数就可以是另外的函数, 把这些函数作为参数来使用, 参与到整个公式的运算中。
当我们在Excel中要使用函数公式的时候, 可以调用Excel中的公式【选项卡】
在【公式】选项卡的【函数库】选项中, Excel内置了多种常用函数类型, 在函数库中可以看到函数的简介和参数说明。
单击【公式】选项卡的【插入函数】按钮之后, 就可以在向导中选择或搜索所需要的函数公式。
2. 函数的输入和使用
2.1 鼠标点击, 选中需要使用函数的空白单元格
2.2 单击【公式】选项卡插入函数按钮, 打开【函数库】, 使用函数库插入已知类别的函数
2.3 如果【函数库】中没有所需函数公式, 则选中【其他函数】下拉按钮, 根据需要插入函数
2.4 选择了需要使用的具体之后, 单击确定按钮, Excel会自动插入函数并显示【函数参数】面板
2.5 在函数参数面板中, 包含了函数名称, 参数和函数简介, 函数运算结果等。参数面板的最右侧将实时显示函数的运算结果
3. 函数的分类
Excel中的函数从提供方式上分为4大类:内置函数, 拓展函数, 自定义函数, 宏表函数。其中内置函数根据应用范围可以分为12小类。
3.1 文本函数
在E中的数据大致包括文本、数据、逻辑值、错误值等等。其中的文本数据主要指普通的字符集合。比如人员姓名、部门名称、单位名称、英语单词等等。在一个单元格中输入了这些常见字符后, 就被Excel识别为文本。除了输入的字符是文本之外, 在Excel中使用文本函数运算后的结果也是文本类型。这里需要注意的是, 文本数据当中的“空文本”, 空文本一般用“”来显示, 它是字符长度为0的文本类型, 通常是用它表示公式计算为空。在Excel的默认状态下, 输入单元格的日期和数字都自动显示为右对齐的模式, 出现错误的数值和逻辑值自动显示为居中的模式, 文本数据自动显示为左对齐的模式。
3.2 信息函数
信息函数是Excel中具有特别功能的函数, 可以为使用者反馈出软件当前的状态, 对错误值进行判断。
3.3 逻辑函数
逻辑函数可以对一个或多个公式的逻辑关系运算后做出判断, 然后得出一个逻辑结果。常见的3种逻辑关系分别是:“是”“与”“非”, 它们分别对应的函数是AND, OR, NOT。
3.4 查找和引用函数
日常的工作中经常遇到需要提取某一部分数据, 然后对其进行处理的情况。如从电话本中提取出人名, 从商品编号中查找相同类型的数值从而判断商品的种类, 从身份证号中提取出生日期等等。这里提供几个常见的查找和引用函数:
LEFT (text, num-chars) --从一组数据的最左端开始提取规定数量的数据
MID (text, Start-num, num-chars) --从一组数据的中间任何位置提取规定数量的数据
RIGHT (text, num-chars) --从一组数据的最右端开始提取规定数量的数据
但在进行数据提取并理的时候, 提取的开始位置, 数据的数量经常是不能确定的, 这时就需要用一定的条件来进行定位。这里用到了FIND和SEARCH函数。
FIND函数和S函数都是用来定位数据在指定位置的起始, 然后以数字表示的函数。
3.5 日期和时间函数
在Excel中日期和时间函数是一种主要的数据类型。它们也是经常在工作中接触到的一类数据, 所以Excel软件也特别提供了很多种类的日期和时间函数, 用来处理这一类的数据。Excel的系统把日期作为一种特殊的数值表现形式来看待, 也就是说, 日期是实质为数值的一组序列数。这类函数在Excel中种类非常多, 同时一些数值运算使用的函数也可以被用来处理日期和时间, 比如MOD函数, INT函数, ROUND函数等等。
3.6 统计函数
E中使用频率最高的一类工作表函数可以说就是统计函数, 大部分的工作表数据都需要统计函数。这类别的函数常用到的有:COUNT函数, COUNTA函数, COUNTBLANK函数。
3.7 数字和三角函数
数学计算类函数可以帮助用户在软件中利用数学函数的基本规律, 完成一些熟悉的数学公式计算过程, 比如求和函数, 余数函数, 三角函数, 随机函数等。在对常用的数学函数进行了掌握后, 用户在构造数组序列, 变换单元格引用位置, 日期和时间函数的应用以及提取文本函数的方面都将十分便捷。
3.8 数据库函数
Excel包含了一些工作表函数, 用于对存储在列表或数据库中的数据进行分析, 这些函数统称为数据库函数。每个函数均有三个参数:database, field, criteria。这些参数指向函数所使用的工作表区域。
3.9 财务函数
财务函数在E中的类型非常丰富, 主要的种类有投资评价计算, 折旧计算, 债务相关计算等。复杂的财务计算过程通过这些函数计算后变的简单, 为财务工作提供了很大的便利条件。
3.1 0 工程函数
工程函数是E中一类专为工程师准备的函数。它们是工程专业领域用来分析计算使用的函数。比较常用的有:Bessel函数, BIN2OCT函数, BIN2DEC函数, OCT2BIN函数, OCT2DEC, HEX2BIN, HEX2DEC
3.1 1 多维数据集函数
多维数据集函数可以被用来取代并辅助单元格公式, 是一类非常适应的函数。多维数据函数在内存当中构造出虚拟的对多个单元格的引用, 使得各个区域能够参与独立运算并同时返回运算结果, 提高了公式编辑和公式运算的效率。
3.1 2 兼容性函数
在E中有一类非常特殊的函数, 甚至很难在E的函数列表中找到它们的身影, 但兼容性函数是一类比较实用的函数, 常用的有:GET.DOCUMENT, GET.WORKBOOK, GET.CELL, EVALUATE等。
4. 绘制三角函数曲线
以上对Excel函数的相关知识做了简要介绍, 这里用一种常用的函数来进行示例说明。
三角函数是我们非常熟悉的函数, Excel提供的数学和三角函数已基本囊括了常用的各种数学公式与三角函数, 用Excel画三角函数图, 非常方便。
比如画正弦 (Y=sinX) 函数的图形, X的取值范围是-360°到+360°之间, 详细具体步骤为:
4.1 在第1列单元格第1行输入X, X下方的第一列单元格使用填充柄 (即光标变成细的黑色十字) 输入从-360到360的数值。
4.2 单击选中第2列单元格, 鼠标右键单击后在“设置单元格格式”选项里把第2列单元格的分类属性定义为“数值”。在第2列单元格第1行中输入函数“A1*PI () /180) ”, 使用填充柄向下填充一直计算出所有Y在-360°到+360°时其对应的sinX取值。
4.3 同时选择第1列和第2列的所有数据, 点击插入菜单下的【图表】, 这时出现图表向导, 在向导中选择【XY散点图】的第2个图形【平滑线散点图】, 选择完按【下一步】, 此时可以进行图表有关选项的设置, 我们可以根据自己的需要去设置, 也可以保留默认的设置。这样一条标准的三角函数曲线图就画出来了。 (图1)
5. 结语
作为生活在信息时代的人, 我们需要频繁的进行数据处理, Excel就是为用户数据处理定制的一个专门工具, 它的操作方法非常人性化, 所以在各个领域被广泛应用。Excel拥有强大的计算, 分析功能, 对于用户来说, 确实是一个非常高效的软件。
参考文献
[1]罗刚君, 章兰新Excel函数图表与透视表从入门到精通北京:中国铁道出版社, 2012.
[2]魏雪萍, 汪震Excel应用大全北京:人民邮电出版社, .2011
Excel财务函数应用解析 篇7
在介绍具体的财务函数之前,我们首先来了解一下财务函数中常见的参数:
未来值(fv)--在所有付款发生后的投资或贷款的价值。
期间数(nper)--为总投资(或贷款)期,即该项投资(或贷款)的付款期总数。
付款(pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常pmt包括本金和利息,但不包括其他费用及税款。
现值(pv)--在投资期初的投资或贷款的价值。例如,贷款的现值为所借入的本金数额。
利率(rate)--投资或贷款的利率或贴现率。
类型(type)--付款期间内进行支付的间隔,如在月初或月末,用0或1表示。
日计数基准类型(basis)--为日计数基准类型。Basis为0或省略代表US(NASD)30/360,为1代表实际天数/实际天数,为2代表实际天数/360,为3代表实际天数/365,为4代表欧洲30/360。
接下来,我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型财务函数,更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用,返回错误值#NAME?,请安装并加载“分析工具库”加载宏。操作方法为:
在“工具”菜单上,单击“加载宏”;
在“可用加载宏”列表中,选中“分析工具库”框,再单击“确定”。
一、投资计算函数
投资计算函数可分为与未来值fv有关,与付款pmt有关,与现值pv有关,与复利计算有关及与期间数有关几类函数。
1、与未来值fv有关的函数--FV、FVSCHEDULE
2、与付款pmt有关的函数--IPMT、ISPMT、PMT、PPMT
3、与现值pv有关的函数--NPV、PV、XNPV
4、与复利计算有关的函数--EFFECT、NOMINAL
5、与期间数有关的函数—NPER
在投资计算函数中,本文将重点介绍FV、NPV、PMT、PV函数。
(1)求某项投资的未来值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)
(2)求投资的净现值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)
(3)求贷款分期偿还额PMT
PMT函数基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款,要求在一定周期内支付完时,每次需要支付的偿还额,也就是我们平时所说的“分期付款”。比如借购房贷款或其它贷款时,可以计算每期的偿还额。
其语法形式为:PMT(rate,nper,pv,fv,type)其中,rate为各期利率,是一固定值,nper为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为现值,或一系列未来付款当前值的累积和,也称为本金,fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv,则假设其值为零(例如,一笔贷款的未来值即为零),type为0或1,用以指定各期的付款时间是在期初还是期末。如果省略type,则假设其值为零。
例如,需要10个月付清的年利率为8%的￥10,000贷款的月支额为:
PMT(8%/12,10,10000)计算结果为:-￥1,037.03。
(4)求某项投资的现值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)。因此,这不是一项合算的投资。
二、折旧计算函数
折旧计算函数主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。这些函数都是用来计算资产折旧的,只是采用了不同的计算方法。这里,对于具体的计算公式不再赘述,具体选用哪种折旧方法,则须视各单位情况而定。
三、偿还率计算函数
偿还率计算函数主要用以计算内部收益率,包括IRR、MIRR、RATE和XIRR几个函数。
1、返回内部收益率的函数--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。分别求出投资两年、四年以及五年后的内部收益率。
在工作表的B1:B6输入数据"函数.xls"所示,计算此项投资四年后的内部收益率IRR(B1:B5)为-3.27%;计算此项投资五年后的内部收益率IRR(B1:B6)为8.35%;计算两年后的内部收益率时必须在函数中包含guess,即IRR(B1:B3,-10%)为-48.96%。
2、用RATE函数计算某项投资的实际赢利
在经济生活中,经常要评估当前某项投资的运作情况,或某个新企业的现状。例如某承包人建议你贷给他30000元,用作公共工程建设资金,并同意每年付给你9000元,共付五年,以此作为这笔贷款的最低回报。那么你如何去决策这笔投资?如何知道这项投资的回报率呢?对于这种周期性偿付或是一次偿付完的投资,用RATE函数可以很快地计算出实际的赢利。其语法形式为RATE(nper,pmt,pv,fv,type,guess)。
具体操作步骤如下:
(1)选取存放数据的单元格,并按上述相似的方法把此单元格指定为"百分数"的格式。
(2)插入函数RATE,打开"粘贴函数"对话框。
(3)在“粘贴函数”对话框中,在"Nper"中输入偿还周期5(年),在"Pmt"中输入7000(每年的回报额),在"Pv"中输入-30000(投资金额)。即公式为=RATE(5,9000,-30000)
(4)确定后计算结果为15.24%。这就是本项投资的每年实际赢利,你可以根据这个值判断这个赢利是否满意,或是决定投资其它项目,或是重新谈判每年的回报。
四、债券及其他金融函数
债券及其他金融函数又可分为计算本金、利息的函数,与利息支付时间有关的函数、与利率收益率有关的函数、与修正期限有关的函数、与有价证券有关的函数以及与证券价格表示有关的函数。
1、计算本金、利息的函数--CUMPRINC、ACCRINT、AC-CRINTM、CUMIPMT、COUPNUM
2、与利息支付时间有关的函数--COUPDAYBS、COUP-DAYS、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。
(1)求定期付息有价证券的应计利息的函数ACCRINT
ACCRINT函数可以返回定期付息有价证券的应计利息。
其语法形式为ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
其中issue为有价证券的发行日,first_interest为有价证券的起息日,settlement为有价证券的成交日,即在发行日之后,有价证券卖给购买者的日期,rate为有价证券的年息票利率,par为有价证券的票面价值,如果省略par,函数ACCRINT就会自动将par设置为￥1000,frequency为年付息次数,basis为日计数基准类型。
例如,某国库券的交易情况为:发行日为2010年3月1日;起息日为2010年8月31日;成交日为2010年5月1日,息票利率为10.0%;票面价值为￥1,000;按半年期付息;日计数基准为30/360,那么应计利息为:
(2)求本金数额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。
那么该笔贷款在第下半年偿还的全部本金之中(第7期到第12期)为:=CUMPRINC(A2/12,A3*12,A4,7,12,0)计算结果为:-436.568194。
该笔贷款在第一个月偿还的本金为:=CUMPRINC(A2/12,A3*12,A4,1,1,0)计算结果为:-68.27827118。
(3)求有价证券的贴现率DISC
DISC函数返回有价证券的贴现率。
其语法形式为DISC(settlement,maturity,pr,redemption,basis)其中settlement为有价证券的成交日,即在发行日之后,有价证券卖给购买者的日期,maturity为有价证券的到日期,到期日是有价证券有效期截止时的日期,pr为面值为"￥100"的有价证券的价格,redemption为面值为"￥100"的有价证券的清偿价格,basis为日计数基准类型。
例如:某债券的交易情况如下:成交日为2010年3月18日,到期日为2010年8月7日,价格为￥48.834,清偿价格为￥52,日计数基准为实际天数/360。那么该债券的贴现率为:DISC("2010/3/18","2010/8/7",48.834,52,2)计算结果为:0.154355363。
参考文献
[1]周丽媛.《Excel在财务管理中的应用》,东北财经大学出版社,2007.1.
[2]俞静,秦晓红.《Excel财务管理典型实例》,科学出版社,2008.3.