Excel在财务管理中的高级运用-8项内容－张敦力2014－3－15课件

12023/3/91Excel在财务管理中的高级运用电邮：电话：1580719210822023/3/92内容提要：一、时间价值系数表的编制二、常用财务指标计算器的设计三、贷款偿付计划/租金计划的编制四、数据库的构建和运用五、数据的整理和分析六、数据透视表的编制七、数据关系的图形描述八、好好学习、天天向上32023/3/93（一）复利终值系数（PVIF）（二）复利现值系数（FVIF）（三）普通年金终值系数（PVIFA）（四）普通年金现值系数（FVIFA）一、时间价值系数表的编制52023/3/951）输入基本数据2）输入基本计算公式（难点）3）拖拽1.混合引用法62023/3/961）输入基本数据2）在A2中输入基本计算公式3）选中单元格区域A2：K124）执行运算2.模拟运算表/数据表法72023/3/971.通过混合引用拖拽生成2.通过模拟运算表/数据表编制（二）复利现值系数（PVIF）92023/3/992）输入基本计算公式（如下图A2）102023/3/910Excel2003：数据→模拟运算表Excel2010：数据→数据工具→假设分析→模拟运算表112023/3/911在对话框“输入引用行的单元格”和“输入引用列的单元格”右侧的空格中分别选定所引用的单元格D1和F15）确定132023/3/913142023/3/914（三）普通年金终值系数（PVIFA）152023/3/915（四）普通年金现值系数（FVIFA）172023/3/917如下页图，在B10中输入下列公式即可：=LOOKUP（0,B9:G9,B3:G3）+（-LOOKUP（LOOKUP（0,B9:G9,B3:G3）,B3:G3,B9:G9））/LOOKUP（LOOKUP（0,B9:G9,B3:G3）+1,B3:G3,B4:G4）（一）投资回收期182023/3/918（四）

债券定价（五）债券溢折价摊销192023/3/919（二）终值现金流量类型计算方法一次性收付款项表达式法、FV函数法→计算器多次收付款项普通年金表达式法、FV函数法→计算器预付年金表达式法、FV函数法→计算器递延年金表达式法、FV函数法→计算器无规律表达式法、FV函数法→列表212023/3/9211.单变量求解法2.Irr函数法3.Rate函数法4.Mirr函数法5.Xirr函数法（四）内含报酬率（到期收益率/实际利率）（五）有效年利率与名义年利率筹资1000万元，3年期，到期还本*22年利率付息方式每次利息年利息A银行7.08%月5.9（1000×7.08%/12）70.8（5.9×12）B银行7.24%季度18.1（1000×7.24%/4）72.4（18.1×4）C银行7.29%年72.9（1000×7.29%）72.9*23名义年利率付息方式有效年利率A银行7.08%月7.31%B银行7.24%季度7.44%C银行7.29%年7.29%252023/3/925利用给定的名义年利率和每年的复利期数，计算有效的年利率。EFFECT(nominal_rate,npery)1.有效利率262023/3/926#NAME？Excel2003:工具→加载宏→分析工具库Excel2010:文件→选项→加载项→转到→加载宏→分析工具库可能的问题292023/3/929（一）等额本金还款法本金在整个还款期内按借款期限平均偿还，利息按期初待偿本金和利率计算（二）等额本息还款法每次等额还本付息三、贷款偿付计划/租金计划的编制302023/3/930企业借入5年期的长期借款1000万元，固定年利率6%，本金在每年末按借款期限平均偿还，利息按年初待偿本金和利率计算。问：每次还款总额为多少？其中有多少是当期承担的利息？有多少用于偿还本金？简例1（等额本金还款法）：312023/3/931企业借入5年期的长期借款1000万元，固定年利率6%，每年末等额还本付息。问：每次还款总额为多少？其中有多少是当期承担的利息？有多少用于偿还本金？简例2（等额本息还款法）：322023/3/932（一）二/三维数据库（二）应收账款备查账（三）薪酬管理（人事档案）（四）存货管理（进销存）（五）试题库与自测四、数据库的构建和运用332023/3/9331.一般表格：考勤表/通讯录/销售统计表2.财务报表3.预算报表（一）二/三维数据库为提供以下信息如何设计报表？每个省的销售额及销售数量每个市的销售额及销售数量每个人的销售额及销售数量不同日期的销售额及销售数量每个省的每个市的销售额及销售数量每个省的每个市的每个人的销售额及销售数量每个省的每个市的每个人的相应日期的销售额及销售数量352023/3/935（二）应收账款备查账362023/3/9361.结构2.根据身份证号自动生成“出生日期”、“年龄”和“性别”3.薪酬查询（三）薪酬管理（人事档案）372023/3/937382023/3/938392023/3/939（四）存货管理（进销存）402023/3/940（一）计数（二）合并计算（三）排序、排位与百分比排位（四）求最值、平均值、方差和标准差五、数据的整理和分析（1/2）412023/3/941（五）回归分析和方差分析（六）列出符合条件的数据（七）分别计算和展示每一部门的业绩五、数据的整理和分析（2/2）422023/3/942符合特定条件的单元格有多少个符合特定条件的数据有多少个

1.单条件计数2.多条件计数（一）计数432023/3/943（1）空白单元格的个数：countblank（2）非空白单元格的个数：counta（3）包含数字的单元格个数：count（4）特定内容的单元格个数：Countif

frequency1.单条件计数442023/3/944例：第一行为表头，A列是“姓名”，B列是“性别”，D列是“年龄”，F列是“工作部门”，H列是“基本工资”。现在要统计：“基本工资”为2000~3000元、年龄为20~30岁的男性的人数。统计结果存放在本工作表的其他列。2.多条件计数452023/3/945（1）COUNTIFS（Excel2010）=COUNTIFS(B2:B301，"男"，D2:D301，">=20"，D2:D301，"<30"，H2:H301，">=2000"，H2:H301，"<3000")2.多条件计数462023/3/946（2）＝SUM（IF（（B2:B301="男"）*（D2:D301>=20）*（D2:D301<30）*（H2:H301>=2000）*（H2:H301<3000）,1,0））输入完公式后，按Ctrl+Shift+Enter键2.多条件计数472023/3/947将多个相似格式的工作表或数据区域，按指定的方式进行自动匹配计算。（二）合并计算482023/3/9492023/3/9491.主要目的2.专用术语3.合并方式4.注意事项（二）合并计算502023/3/9501.单表数据汇总（透视表）2.多表数据汇总（布局同否？）3.明细与汇总并存（结果显示在新表中）4.多表数据合并

多张两列的表合并成一张多列的新表（二）合并计算——主要目的512023/3/9511.源数据：源工作表/源区域→单个、多个工作表或多重工作簿2.目标数据：目标工作表/目标区域→不一定与源数据在同一张工作表/工作簿中（二）合并计算——专用术语522023/3/9523.标志：列标签/行标签4.标志位置：首行/最左列5.创建指向源数据的链接（二）合并计算——专用术语532023/3/953检查源数据，根据数据结构与布局来确定合适的方式1.按公式进行合并计算2.按位置进行合并计算3.按分类进行合并计算4.从多个合并区域创建数据透视表（二）合并计算——合并方式542023/3/954对于所有类型或排列的数据，尤其对布局不同的数据，推荐使用公式中的三维引用在合并计算工作表上，复制或输入待合并计算数据的标志单击用来存放合并计算数据的单元格键入合并计算公式，通过三维引用，使公式中的引用指向每张工作表中包含待合并数据的源单元格1.按公式进行合并计算552023/3/955源数据按相同顺序排列并使用相同标签，合并各区域中相同单元格里的数据2.按位置进行合并计算562023/3/956设置要合并的数据为每个区域命名（也可不命名，在引用时用窗口选定区域）在“数据”菜单中，单击“合并计算”在“函数”框中，单击需要用来对数据进行合并的汇总函数2.按位置进行合并计算572023/3/957单击“引用位置”框，再单击要进行合并的第一个区域的工作表标签，键入区域的给定名称，再单击“添加”。对每个区域重复这一步骤如果要在源区域的数据更改的任何时候都自动更新合并表，并且确认以后在合并中不需要包括不同的或附加的区域，请选中“创建连至源数据的链接”复选框2.按位置进行合并计算582023/3/958单击“引用位置”框，再单击要进行合并的第一个区域的工作表标签，键入区域的给定名称，再单击“添加”。对每个区域重复这一步骤如果要在源区域的数据更改的任何时候都自动更新合并表，并且确认以后在合并中不需要包括不同的或附加的区域，请选中“创建连至源数据的链接”复选框2.按位置进行合并计算592023/3/959根据位置进行合并时，请将“标签位置”下的复选框设为空，源数据中的行或列标志不被复制到合并中，如果需要合并数据的标志，请从源区域之一进行复制或手工输入→这是与按分类进行合并的区别2.按位置进行合并计算602023/3/960源数据未按顺序排列但使用相同标签，计划合并来自包含匹配标志的行或列中的数据在“标签位置”下，选中指示标志在源区域中位置的复选框：首行、最左列或两者都选。3.按分类进行合并计算612023/3/9611.确保每个数据区域为列表（包含相关数据的一系列行）格式：第一行中的每一列都具有标志，同一列中包含相似的数据，并且在列表中没有空行或空列。2.根据位置进行合并时，请确保每个区域具有相同的布局，请将“标签位置”下的复选框设为空（二）合并计算——注意事项622023/3/9623.根据分类进行合并时，确保要合并的行或列的标志具有相同的拼写（大小写、缩写、空格）。任一与其他源数据区域中的标志不匹配的标志都会导致合并中出现单独的行或列（二）合并计算——注意事项632023/3/9631.排序2.排位3.百分比排位（三）排序、排位与百分比排位642023/3/964（1）关键词：主要、次要

（是否包括标题）（2）依据：数值、颜色、图标（3）次序：升、降1.排序：条件652023/3/965rank函数（Rank.EQ）如果多个值具有相同的排位，则返回该组数值的最高排位，对重复数的排位相同，但重复数的存在将影响后续数值的排位2.排位：列出位次662023/3/966RANK.AVG:如果有一个以上的值排位相同，则返回平均排位如有4个数并列第5，RANK.EQ为4个5，RANK.AVG4个为6.5（5+6+7+8）/42.排位：列出位次672023/3/9671.排名前20%奖励20002.排在31%～50%的奖励10003.排在51～80%奖励500→大于该数据的数据个数/（大于该数据的数据个数+小于该数据的数据个数）思考：如何按名次确定奖金？682023/3/968（1）借助percentrank函数＝1－PERCENTRANK（array，x，significance）

（2）排位与百分比排位文件→选项→加载项→转到→分析工具库→数据分析3.百分比排位692023/3/969（1）点：原数据的序数（2）排序后的数据系列：名称随数据的标志而变化（3）排位：重复数据占用同一位置（4）百分比：按照降序排列3.百分比排位702023/3/9701.最值2.平均值3.方差和标准差4.求协方差和相关系数（四）求最值、平均值、方差和标准差712023/3/971（1）最大值：max函数（2）最小值：min函数（3）第几大：large函数（4）第几小：small函数2.平均值3.方差4.标准差1.求最值（可能不只1个）722023/3/972average函数DAVERAGE2.求平均值732023/3/973（1）总体方差：VarP函数（2）样本方差：Var函数（3）总体标准差：StDevp函数（4）样本标准差：StDev函数3.求方差和标准差742023/3/974（1）协方差：covar函数

=covar（数据系列1，数据系列2）（2）相关系数：CORREL函数4.求协方差和相关系数752023/3/975Excel2003:工具→数据分析→分析工具Excel2010:数据→数据分析→分析工具（五）回归分析和方差分析762023/3/976查询、列示、打印符合一个（或多个）条件的数据项1.中南地区的销售情况2.中南地区男、女销售代表的销售情况（六）列出符合条件的数据772023/3/9771.自动筛选2.高级筛选3.数据透视表（六）列出符合条件的数据782023/3/9781.各地区的销售总额2.男、女销售代表的销售总额3.中南地区男、女销售代表的销售总额（七）分别计算和展示每一部门的业绩792023/3/9791.分类汇总（必须先按汇总的标志排序）2.分级显示3.数据透视表（七）分别计算和展示每一部门的业绩802023/3/980（一）数据透视表的性质（二）数据透视表的构成要素（三）数据透视表的编制（四）多重合并计算数据区域数六、数据透视表812023/3/9811.一种交互式报表2.把繁杂的数据简洁化3.最常用、功能最全的Excel数据分析工具之一（一）数据透视表的性质（1/3）822023/3/982可以快速分类汇总、比较大量的数据可以随时选择其中页、行和列中的不同元素，以达到快速查看源数据的不同统计结果可以随意显示和打印出你所感兴趣区域的明细数据。（一）数据透视表的性质（2/3）832023/3/983一张“数据透视表”仅靠鼠标移动字段位置，即可变换出各种类型的报表。（一）数据透视表的性质（3/3）842023/3/9841.页字段/报表筛选→页2.行字段/标签→行3.列字段/标签→列4.数据项/数值→值（二）数据透视表的构成要素可以自定义这些字段设置以便对其重命名，并将其格式化为需要的格式852023/3/9851.明确目的2.设定结构3.鼠标移至数据源中（定位于数据区域内任一单元格）（三）数据透视表的编制（1/3）862023/3/9864.插入数据透视表5.设计数据透视表6.美化版面（三）数据透视表的编制（2/3）872023/3/9875.设计数据透视表（1）根据目的和结构拖拽页字段、行字段、列字段和数据项（2）调整页字段、行字段、列字段、数据项（3）重新设定汇总项882023/3/9882007和2010中数据透视表向导被隐藏在后台通过快捷键调出使用（Alt+D+P）直接自定义在快速访问工具栏中（四）多重合并计算数据区域数892023/3/9892007：Office按钮——Excel选项——自定义——不在功能区中的命令——数据透视表和数据透视图向导——添加——确定2010：文件—选项—快速访问工具栏——不在功能区中的命令——数据透视表和数据透视图向导——添加——确定（四）多重合并计算数据区域数902023/3/9901.页：多重合并计算区域数据透视表中的新字段，分析时可以通过拖拽实现数据分析的目的页字段数目的最大值为4（四）多重合并计算数据区域数912023/3/9912.行：始终由数据源中的第一列组成3.列：是一项高级字段，为数据源中的第一列之外其他列的合并打包（四）多重合并计算数据区域数922023/3/9924.值：数据源中除首字段外的所有“项”。“值”字段默认数据源的文本项为零值（四）多重合并计算数据区域数932023/3/993（一）资金习性示意图（二）投资组合机会集曲线（三）利润构成分析饼形图（四）产品销售对比分析柱形图七、数据关系的图形描述942023/3/994（五）动态图表（六）主次坐标轴图表（七）甘特图七、数据关系的图形描述952023/3/995（一）资金习性示意图962023/3/996（二）投资组合机会集曲线972023/3/9