EXCEL“加”在财务中的应用培训课件

第二章

公式与函数应用第二章

公式与函数应用1通过公式或函数定义多个单元格之间的数据关系在单元格里输入公式或函数后后，会立即在单元格里显示出计算结果EXCEL2007提供大量的内部函数，可以进行数学、文本、逻辑等计算通过公式或函数定义多个单元格之间的数据关系2公式设置涉及到计算的数据对象和运算符数据对象可以是常量数值、单元格引用或区域引用等运算符用于连接要运算的数据对象，主要包括：算术运算符比较运算符文本运算符引用运算符2.1运算符公式设置涉及到计算的数据对象和运算符2.1运算符3完成基本的数学运算2.1.1算术运算符算术运算符含义示例+加法运算E1+E2-减法运算或负数D4-D5*乘法运算F7*1.5/除法运算D7/3%百分比D12%^乘幂运算2^3完成基本的数学运算2.1.1算术运算符算术运算符含义示例+加4比较运算符用以对两个值进行比较，结果为逻辑值，符合比较条件，结果为TRUE，否则为FALSE2.1.2比较运算符比较运算符含义示例=等于A3=A4>大于A3>A4<小于A3<A4>=大于或等于A3>=A4<=小于或等于A3<=A4<>不等于A3<>A4如果A3单元格内为3，A4单元格内为4，则A3>A4为FALSE；A3<A4，为TRUE比较运算符用以对两个值进行比较，结果为逻辑值，符合比较条件，5&（和）将两个或多个文本值串联起来一长生一个连续的文本值2.1.3文本运算符&（和）2.1.3文本运算符6用于对于目标单元格或目标区域进行引用单个单元格使用列标和行号的方式引用2.1.4引用运算符引用运算符含义示例:区域运算符A3:B7,联合运算符，将多个引用合并为一个引用A3:B7,C3:D4

（空格）交叉运算符，产生对两个引用共有的单元格的引用A3:B7A3:D4注：运算符的输入必须使用英文输入法，否则非法用于对于目标单元格或目标区域进行引用2.1.4引用运算符引用72.2公式的使用2.2.1公式的基本操作公式的基本操作主要包括公式的输入、修改、显示、复制和删除等(1)输入公式选择要输入公式的单元格，在编辑栏中输入“=”，然后输入公式，输入完毕后按回车键即在单元格内显示公式运算结果2.2公式的使用2.2.1公式的基本操作8(2)修改公式选择要修改公式的单元格，编辑栏中会显示单元格对应的公式，直接在编辑栏中进行修改，回车完成(3)显示公式为整体显示所有单元格公式便于公式审核“公式”选项卡-“公式审核”组-“显示公式”(2)修改公式9(4)复制公式复制公式可以快速的将单元格公式复制到其他单元格中复制公式往往与公式的相对引用结合使用B6=B3+B4+B5，如果复制B6单元格到C6单元格，C6单元格的公式为=C3+C4+C5(4)复制公式10(5)删除公式使用公式计算出结果后，可以删除单元格公式，至保留计算结果选中区域，点击鼠标右键，选择“复制”再点击鼠标右键，选择“选择性粘贴”，选中“数值”(5)删除公式112.2.1相对引用相对引用包含目标单元格和其公式中所包含的单元格的相对位置关系复制公式时，目标单元格位置发生变化，公式中涉及的单元格的引用也随之发生变化，保持目标单元格和公式中单元格之间相对位置的关系2.2单元格引用2.2.1相对引用2.2单元格引用12例如：B6=B3+B4+B5，如果复制B6单元格到C6单元格，C6单元格的公式为=C3+C4+C5目标单元格由B6移动到C6，相右移动了1列，则公式中涉及的单元格都向右移动1列，保持目标单元格和公式中各单元格的相对位置关系E5=B5+C5+D5，如果复制E5单元格到E6单元格，E6单元格的公式为=B6+C6+D6例如：132.2.2绝对引用采取绝对引用的单元格，进行单元格复制时，无论目标单元格如何变动都不发生变化在列标和行号前加上$，如$C$12.2.2绝对引用142.2.3混合引用混合引用是指在一个单元格的引用中，既有绝对引用，又有相对引用，即一个单元格的行使用绝对引用，列使用相对引用，或行使用相对引用，列使用绝对引用引用方式为：列绝对引用，行相对引用例如：$A1列相对引用，行绝对引用例如：B$12.2.3混合引用15将具有特定功能的一组公式组合在一起形成函数函数的基本格式包括函数名和参数如：SUM(B2:B7)2.3函数的使用将具有特定功能的一组公式组合在一起形成函数2.3函数的使用162.3.1使用函数的基本操作2.3.1使用函数的基本操作17（1）求和函数SUM计算选择的单元格区域内所有数值的和例如：SUM(B2:B5)（2）平均数函数AVERAGE计算选择的单元格区域中数据的算数平均数例如：AVERAGE(B2:B5)2.3.2常用函数（1）求和函数SUM2.3.2常用函数18（3）条件函数IFIF（logical_test,value_if_true,value_if_false)例如：IF（A3>A4，1，0)表示：

如果A3>A4为TURE,目标单元格的值为1，否则，目标单元格的值为0（4）最大值函数MAX获取选定单元格区域中的最大值（5）最小值函数MIN获取选定单元格区域中的最小值（3）条件函数IF19（7）四舍五入函数ROUND返回某个数字按指定位数取整后的数字例如：ROUND(3.4446，3)=3.445（8）计数函数COUNT返回包含数字的单元格的个数以及返回参数列表中的数字个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。（7）四舍五入函数ROUND20（9）排名函数RANK返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值RANK(number,ref,order)Number

为需要找到排位的数字。Ref

为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order

为一数字，指明排位的方式。如果order为0（零）或省略，按照降序如果order不为零，按照升序（9）排名函数RANK21B2=RANK(A2,A2:A6,1)EXCEL“加”在财务中的应用培训课件22常用公式综合操作示例常用公式综合操作示例23EXCEL2007中包含53个与财务管理和投资决策有关的函数，分成四类投资计算函数证券计算函数折旧计算函数偿还率计算函数2.3.3财务函数及其应用EXCEL2007中包含53个与财务管理和投资决策有关的函24（1）投资现值函数PV语法：PV(rate,nper,pmt,[fv],[type])Rate

为各期利率Nper

为总投资期Pmt

为各期年金Fv

为未来值Type

为数字0或1，用以指定各期的付款时间是在期初还是期末，Type值支付时间0或省略期末1期初（1）投资现值函数PV25假设对一项投资进行评估，该投资需要一次性投入200万，以后分3年，每年年末收回100万，第三年末投资收回值50万，该项投资是否可行？PV(10%,3,100,50,,)=-286.25如果无收回值，PV(10%,3,100,,,)=-248.69假设对一项投资进行评估，该投资需要一次性投入200万，以后分26有收回值，PV(10%,3,100,50,,)=-286.25如果无收回值，PV(10%,3,100,,,)=-248.69结论：根据投资的年金计算的投资三年收回价值相当于当前价值（投入）286.25，现在只需投入200，所以该投资是可行的。PV(10%,1,,100,,)或PV(10%,1,100,,,)PV(10%,2,,100,,)PV(10%,3,,100,,)PV(10%,3,,50,,)SUM(A10:D10)PV(10%,1,,100,,)PV(10%,2,,1027（2）投资净现值NPV通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。NPV(rate,value1,value2,...)Rate

为某一期间的贴现率，是一固定值。Value1,value2,...

代表支出及收入的1到254个参数。Value1,value2,...在时间上必须具有相等间隔，并且都发生在期末NPV使用Value1,Value2,…的顺序来解释现金流的顺序。所以务必保证支出和收入的数额按正确的顺序输入（2）投资净现值NPV28EXCEL“加”在财务中的应用培训课件29（3）投资未来值函数FVFV(rate,nper,pmt,[pv],[type])Rate

为各期利率。Nper

为总投资期，即该项投资的付款期总数。Pmt

为各期所应支付的金额，其数值在整个年金期间保持不变。Pv

为现值Type

数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。Type值为0，

期末；为1：期初（3）投资未来值函数FV30零存整取：每年年末存1万，存10年，每年利率为5%，10年期满存款余额为多少？FV(5%,10,-1)=12.58投资入股某公司第一年年初投入10万，按每年收益率6%，第5年末收回投资，应收回多少?FV(6%,5,0,-10)=13.38零存整取：每年年末存1万，存10年，每年利率为5%，10年期31（4）求贷款分期偿还额函数PMTPMT(rate,nper,pv,fv,type)Rate

贷款利率Nper

该项贷款的付款总数Pv

现值Fv

为未来值Type

数字0或1，用以指定各期的付款时间是在期初还是期末。Type值为0或省略，

期末；为1，期初（4）求贷款分期偿还额函数PMT32EXCEL“加”在财务中的应用培训课件33（5）有价证券贴现率函数DISCDISC(settlement,maturity,pr,redemption,basis)Settlement

为证券的结算日。结算日是在发行日之后，证券卖给购买者的日期Maturity

为有价证券的到期日。到期日是有价证券有效期截止时的日期Pr

为贴现价格Redemption

清偿价值Basis

为日计数基准类型（5）有价证券贴现率函数DISC34例：例：35例:2007年1月1日，购买债券100万，2009年1月1日到期，收回110万，投资年回报率是多少？DISC(“07/01/01”,“09/01/01”,100，110，2)=

0.0448=4.48%如果投资100万购买债券，期望回报率为6%，那到期收回价值应该为多少？FV(6%,2,0,-100)=112.36如果，债券到期按面值100万收回，期望回报率为6%，那当前购买价格应该为多少？PV(6%,2,0,100)=-89例:2007年1月1日，购买债券100万，2009年1月1日36（6）投资的盈利函数RATERATE(nper,pmt,pv,fv,type,guess)Nper

为总投资期，即该项投资的付款期总数。Pmt

为各期所应支付的金额，其数值在整个年金期间保持不变。。Pv

为现值Fv

为未来值Type

数字0或1，用以指定各期的付款时间是在期初还是期末。Type值为0或省略：期末；为1：期初

Guess

为预期利率。（6）投资的盈利函数RATE37例：一项投资，第一年投入80万，以后5年年末每年获取收益20万。投资回报率为多少？RATE(5,20,-80)=8%例：一项投资，第一年投入80万，以后5年年末每年获取收益2038（7）直线折旧法函数SLNSLN(cost,salvage,life)Cost

为资产原值Salvage

为资产在折旧期末的价值（有时也称为资产残值）Life

为折旧期限（有时也称作资产的使用寿命）（7）直线折旧法函数SLN39（8）年数总和折旧法函数SYD（7）固定余额递减法函数DB（9）双倍余额递减法函数DDB（10）变数余额递减法函数VDB（8）年数总和折旧法函数SYD40第二章

公式与函数应用第二章

公式与函数应用41通过公式或函数定义多个单元格之间的数据关系在单元格里输入公式或函数后后，会立即在单元格里显示出计算结果EXCEL2007提供大量的内部函数，可以进行数学、文本、逻辑等计算通过公式或函数定义多个单元格之间的数据关系42公式设置涉及到计算的数据对象和运算符数据对象可以是常量数值、单元格引用或区域引用等运算符用于连接要运算的数据对象，主要包括：算术运算符比较运算符文本运算符引用运算符2.1运算符公式设置涉及到计算的数据对象和运算符2.1运算符43完成基本的数学运算2.1.1算术运算符算术运算符含义示例+加法运算E1+E2-减法运算或负数D4-D5*乘法运算F7*1.5/除法运算D7/3%百分比D12%^乘幂运算2^3完成基本的数学运算2.1.1算术运算符算术运算符含义示例+加44比较运算符用以对两个值进行比较，结果为逻辑值，符合比较条件，结果为TRUE，否则为FALSE2.1.2比较运算符比较运算符含义示例=等于A3=A4>大于A3>A4<小于A3<A4>=大于或等于A3>=A4<=小于或等于A3<=A4<>不等于A3<>A4如果A3单元格内为3，A4单元格内为4，则A3>A4为FALSE；A3<A4，为TRUE比较运算符用以对两个值进行比较，结果为逻辑值，符合比较条件，45&（和）将两个或多个文本值串联起来一长生一个连续的文本值2.1.3文本运算符&（和）2.1.3文本运算符46用于对于目标单元格或目标区域进行引用单个单元格使用列标和行号的方式引用2.1.4引用运算符引用运算符含义示例:区域运算符A3:B7,联合运算符，将多个引用合并为一个引用A3:B7,C3:D4

（空格）交叉运算符，产生对两个引用共有的单元格的引用A3:B7A3:D4注：运算符的输入必须使用英文输入法，否则非法用于对于目标单元格或目标区域进行引用2.1.4引用运算符引用472.2公式的使用2.2.1公式的基本操作公式的基本操作主要包括公式的输入、修改、显示、复制和删除等(1)输入公式选择要输入公式的单元格，在编辑栏中输入“=”，然后输入公式，输入完毕后按回车键即在单元格内显示公式运算结果2.2公式的使用2.2.1公式的基本操作48(2)修改公式选择要修改公式的单元格，编辑栏中会显示单元格对应的公式，直接在编辑栏中进行修改，回车完成(3)显示公式为整体显示所有单元格公式便于公式审核“公式”选项卡-“公式审核”组-“显示公式”(2)修改公式49(4)复制公式复制公式可以快速的将单元格公式复制到其他单元格中复制公式往往与公式的相对引用结合使用B6=B3+B4+B5，如果复制B6单元格到C6单元格，C6单元格的公式为=C3+C4+C5(4)复制公式50(5)删除公式使用公式计算出结果后，可以删除单元格公式，至保留计算结果选中区域，点击鼠标右键，选择“复制”再点击鼠标右键，选择“选择性粘贴”，选中“数值”(5)删除公式512.2.1相对引用相对引用包含目标单元格和其公式中所包含的单元格的相对位置关系复制公式时，目标单元格位置发生变化，公式中涉及的单元格的引用也随之发生变化，保持目标单元格和公式中单元格之间相对位置的关系2.2单元格引用2.2.1相对引用2.2单元格引用52例如：B6=B3+B4+B5，如果复制B6单元格到C6单元格，C6单元格的公式为=C3+C4+C5目标单元格由B6移动到C6，相右移动了1列，则公式中涉及的单元格都向右移动1列，保持目标单元格和公式中各单元格的相对位置关系E5=B5+C5+D5，如果复制E5单元格到E6单元格，E6单元格的公式为=B6+C6+D6例如：532.2.2绝对引用采取绝对引用的单元格，进行单元格复制时，无论目标单元格如何变动都不发生变化在列标和行号前加上$，如$C$12.2.2绝对引用542.2.3混合引用混合引用是指在一个单元格的引用中，既有绝对引用，又有相对引用，即一个单元格的行使用绝对引用，列使用相对引用，或行使用相对引用，列使用绝对引用引用方式为：列绝对引用，行相对引用例如：$A1列相对引用，行绝对引用例如：B$12.2.3混合引用55将具有特定功能的一组公式组合在一起形成函数函数的基本格式包括函数名和参数如：SUM(B2:B7)2.3函数的使用将具有特定功能的一组公式组合在一起形成函数2.3函数的使用562.3.1使用函数的基本操作2.3.1使用函数的基本操作57（1）求和函数SUM计算选择的单元格区域内所有数值的和例如：SUM(B2:B5)（2）平均数函数AVERAGE计算选择的单元格区域中数据的算数平均数例如：AVERAGE(B2:B5)2.3.2常用函数（1）求和函数SUM2.3.2常用函数58（3）条件函数IFIF（logical_test,value_if_true,value_if_false)例如：IF（A3>A4，1，0)表示：

如果A3>A4为TURE,目标单元格的值为1，否则，目标单元格的值为0（4）最大值函数MAX获取选定单元格区域中的最大值（5）最小值函数MIN获取选定单元格区域中的最小值（3）条件函数IF59（7）四舍五入函数ROUND返回某个数字按指定位数取整后的数字例如：ROUND(3.4446，3)=3.445（8）计数函数COUNT返回包含数字的单元格的个数以及返回参数列表中的数字个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。（7）四舍五入函数ROUND60（9）排名函数RANK返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值RANK(number,ref,order)Number

为需要找到排位的数字。Ref

为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order

为一数字，指明排位的方式。如果order为0（零）或省略，按照降序如果order不为零，按照升序（9）排名函数RANK61B2=RANK(A2,A2:A6,1)EXCEL“加”在财务中的应用培训课件62常用公式综合操作示例常用公式综合操作示例63EXCEL2007中包含53个与财务管理和投资决策有关的函数，分成四类投资计算函数证券计算函数折旧计算函数偿还率计算函数2.3.3财务函数及其应用EXCEL2007中包含53个与财务管理和投资决策有关的函64（1）投资现值函数PV语法：PV(rate,nper,pmt,[fv],[type])Rate

为各期利率Nper

为总投资期Pmt

为各期年金Fv

为未来值Type

为数字0或1，用以指定各期的付款时间是在期初还是期末，Type值支付时间0或省略期末1期初（1）投资现值函数PV65假设对一项投资进行评估，该投资需要一次性投入200万，以后分3年，每年年末收回100万，第三年末投资收回值50万，该项投资是否可行？PV(10%,3,100,50,,)=-286.25如果无收回值，PV(10%,3,100,,,)=-248.69假设对一项投资进行评估，该投资需要一次性投入200万，以后分66有收回值，PV(10%,3,100,50,,)=-286.25如果无收回值，PV(10%,3,100,,,)=-248.69结论：根据投资的年金计算的投资三年收回价值相当于当前价值（投入）286.25，现在只需投入200，所以该投资是可行的。PV(10%,1,,100,,)或PV(10%,1,100,,,)PV(10%,2,,100,,)PV(10%,3,,100,,)PV(10%,3,,50,,)SUM(A10:D10)PV(10%,1,,100,,)PV(10%,2,,1067（2）投资净现值NPV通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。NPV(rate,value1,value2,...)Rate

为某一期间的贴现率，是一固定值。Value1,value2,...

代表支出及收入的1到254个参数。Value1,value2,...在时间上必须具有相等间隔，并且都发生在期末NPV使用Value1,Value2,…的顺序来解释现金流的顺序。所以务必保证支出和收入的数额按正确的顺序输入（2）投资净现值NPV68EXCEL“加”在财务中的应用培训课件69（3）投资未来值函数FVFV(rate,nper,pmt,[pv],[type])Rate

为各期利率。Nper

为总投资期，即该项投资的付款期总数。Pmt

为各期所应支付的金额，其数值在整个年金期间保持不变。Pv

为现值Type

数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。Type值为0，

期末；为1：期初（3）投资未来值函数FV70零存整取：每年年末存1万，存10年，每年利率为5%，10年期满存款余额为多少？FV(5%,10,-1)=12.58投资入股某公司第一年年初投入10万，按每年收益率6%，第5年末收回投资，应收回多少?FV(6%,5,0,-10)=13.38零存整取：每年年末存1万，存10年，每年利率为5%，10年期71（4）求贷款分期偿还额函数PMTPMT(rate,nper,pv,fv,type)Rate

贷款利率Nper

该项贷款的付款总数Pv

现值Fv

为未来值Type

数字0或1，用以指定各期的付款时间是在期初还是期末。Type值为0或省略，

期末