Excel在财务管理中的高级运用项内容张敦力.pptx

《Excel在财务管理中的高级运用项内容张敦力.pptx》由会员分享，可在线阅读，更多相关《Excel在财务管理中的高级运用项内容张敦力.pptx（107页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、12023/2/211内容提要：一、时间价值系数表的编制一、时间价值系数表的编制二、常用财务指标计算器的设计二、常用财务指标计算器的设计三、贷款偿付计划三、贷款偿付计划/租金计划的编制租金计划的编制四、数据库的构建和运用四、数据库的构建和运用五、数据的五、数据的整理和分析整理和分析六、数据透视表的编制六、数据透视表的编制七、数据关系的图形描述七、数据关系的图形描述八、八、好好学习、天天向上好好学习、天天向上第1页/共107页22023/2/212（一）复利终值系数（一）复利终值系数（PVIFPVIF）（二）复利现值系数（二）复利现值系数（FVIFFVIF）（三）普通年金终值系数（三）普通年金终

2、值系数（PVIFAPVIFA）（四）普通年金现值系数（四）普通年金现值系数（FVIFAFVIFA）一、时间价值系数表的编制一、时间价值系数表的编制第2页/共107页32023/2/213（一）复利终值系数（一）复利终值系数（FVIF）第3页/共107页42023/2/2141 1）输入基本数据）输入基本数据2 2）输入基本计算公式（难点）输入基本计算公式（难点）3 3）拖拽）拖拽1.1.混合引用法混合引用法第4页/共107页52023/2/2151 1）输入基本数据）输入基本数据2 2）在）在A2A2中输入基本计算公式中输入基本计算公式3 3）选中单元格区域）选中单元格区域A2A2：K12K1

3、24 4）执行运算）执行运算2.2.模拟运算表模拟运算表/数据表法数据表法第5页/共107页62023/2/2161.1.通过混合引用拖拽生成通过混合引用拖拽生成2.2.通过模拟运算表通过模拟运算表/数据表编制数据表编制（二）复利现值系数（二）复利现值系数（PVIF）第6页/共107页72023/2/2171）输入基本数据（如下图）输入基本数据（如下图）第7页/共107页82023/2/2182）输入基本计算公式（如下图）输入基本计算公式（如下图A2）第8页/共107页92023/2/219Excel 2003：数据数据模拟运算表模拟运算表Excel2010：数据数据数据工具数据工具假设分析假

4、设分析模拟运算表模拟运算表第9页/共107页102023/2/2110在对话框在对话框“输入引用行的单元格输入引用行的单元格”和和“输入引用列的单元格输入引用列的单元格”右侧的空格中分别选定右侧的空格中分别选定所引用的单元格所引用的单元格D1D1和和F1 F1 5 5）确定）确定第10页/共107页112023/2/2111第11页/共107页122023/2/2112第12页/共107页132023/2/2113（三）普通年金终值系数（三）普通年金终值系数（PVIFA）第13页/共107页142023/2/2114（四）普通年金现值系数（四）普通年金现值系数（FVIFA）第14页/共107页

5、152023/2/2115（一）投资回收期一）投资回收期（二）终值（二）终值（三）现值或净现值（三）现值或净现值二、常用财务指标计算器的设计二、常用财务指标计算器的设计（四）内含报酬率（四）内含报酬率（到期收益率（到期收益率/实际利率）实际利率）（五）有效利率与名义利率（五）有效利率与名义利率第15页/共107页162023/2/2116如下页图，在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）（

6、一）投资回收期（一）投资回收期第16页/共107页172023/2/2117（四）（四）债券定价债券定价（五）债券溢折价摊销（五）债券溢折价摊销第17页/共107页182023/2/2118（二）终值（二）终值现金流量类型现金流量类型计算方法计算方法一次性收付款项一次性收付款项表达式法、表达式法、FVFV函数法函数法计算器计算器多次多次收付收付款项款项普通年金普通年金表达式法、表达式法、FVFV函数法函数法计算器计算器预付年金预付年金表达式法、表达式法、FVFV函数法函数法计算器计算器递延年金递延年金表达式法、表达式法、FVFV函数法函数法计算器计算器无规律无规律表达式法、表达式法、FVFV函

7、数法函数法列表列表第18页/共107页192023/2/21191.1.表达式法表达式法列表求解法列表求解法函数法函数法函数法函数法容易出错容易出错函数法函数法（三）现值或净现值（三）现值或净现值第19页/共107页202023/2/21201.1.单变量求解法单变量求解法函数法函数法函数法函数法函数法函数法函数法函数法（四）内含报酬率（四）内含报酬率（到期收益率（到期收益率/实际利率）实际利率）第20页/共107页（五）有效年利率与名义年利率筹资筹资1000万元，万元，3年期，到期还本年期，到期还本*21年利年利率率付息付息方式方式每次利息每次利息年利息年利息A A银行银行7.08%7.08

8、%月月5.95.9（10007.08%/110007.08%/12 2）70.870.8（5.9125.912）B B银行银行7.24%7.24%季度季度18.118.1（10007.24%/410007.24%/4）72.472.4（18.1418.14）C C银行银行7.29%7.29%年年72.972.9（10007.29%10007.29%）72.972.9第21页/共107页*22名义年利率名义年利率付息方式付息方式有效年利率有效年利率A A银行银行7.08%7.08%月月7.31%7.31%B B银行银行7.24%7.24%季度季度7.44%7.44%C C银行银行7.29%7.2

9、9%年年7.29%7.29%第22页/共107页232023/2/2123Nominal_rate Nominal_rate：名义利率：名义利率Npery Npery：每年的复利期数：每年的复利期数Effect_rate Effect_rate：有效利率：有效利率第23页/共107页242023/2/2124利用给定的名义年利率和每年的复利利用给定的名义年利率和每年的复利期数，计算有效的年利率。期数，计算有效的年利率。EFFECT(nominal_rate,npery)1.有效利率有效利率第24页/共107页252023/2/2125#NAME#NAME？Excel2003:Excel2003

10、:工具工具加载宏加载宏分析工具库分析工具库Excel2010:Excel2010:文件文件选项选项加载项加载项转到转到加载宏加载宏分析工具库分析工具库可能的问题可能的问题第25页/共107页262023/2/2126Npery Npery 将被截尾取整。将被截尾取整。如果任一参数为非数值型，函数如果任一参数为非数值型，函数 EFFECT EFFECT 返回错误值返回错误值#VALUE!#VALUE!。如果如果 nominal_rate 0 nominal_rate 0 或或 npery 1npery=20，D2:D301，=2000，H2:H301，=20）*（D2:D301=2000）*（H

11、2:H3013000）,1,0）输入完公式后，按Ctrl+Shift+Enter键2.多条件计数多条件计数第45页/共107页462023/2/2146将多个相似格式的工作表或数据区域，按指定的方式进行自动匹配计算。（二）合并（二）合并计计算算第46页/共107页472023/2/21第47页/共107页482023/2/21481.主要目的2.专用术语3.合并方式4.注意事项（二）合并（二）合并计计算算第48页/共107页492023/2/21491.单表数据汇总（透视表）2.多表数据汇总（布局同否？）3.明细与汇总并存（结果显示在新表中）4.多表数据合并多张两列的表合并成一张多列的新表（二

12、）合并（二）合并计计算算主要目的主要目的第49页/共107页502023/2/21501.源数据：源工作表/源区域 单个、多个工作表或多重工作簿2.目标数据：目标工作表/目标区域不一定与源数据在同一张工作表/工作簿中（二）合并（二）合并计计算算专用术语专用术语第50页/共107页512023/2/21513.标志：列标签/行标签4.标志位置：首行/最左列5.创建指向源数据的链接（二）合并（二）合并计计算算专用术语专用术语第51页/共107页522023/2/2152检查源数据，根据数据结构与布局来确定合适的方式1.按公式进行合并计算2.按位置进行合并计算3.按分类进行合并计算4.从多个合并区域

13、创建数据透视表（二）合并（二）合并计计算算合并方式合并方式第52页/共107页532023/2/2153对于所有类型或排列的数据，尤其对布局不同的数据，推荐使用公式中的三维引用在合并计算工作表上，复制或输入待合并计算数据的标志单击用来存放合并计算数据的单元格键入合并计算公式，通过三维引用，使公式中的引用指向每张工作表中包含待合并数据的源单元格1.按公式按公式进行合并行合并计算算第53页/共107页542023/2/2154源数据按相同顺序排列并使用相同标签，合并各区域中相同单元格里的数据2.按位置按位置进行合并行合并计算算第54页/共107页552023/2/2155设置要合并的数据为每个区域

14、命名（也可不命名，在引用时用窗口选定区域）在“数据”菜单中，单击“合并计算”在“函数”框中，单击需要用来对数据进行合并的汇总函数2.按位置按位置进行合并行合并计算算第55页/共107页562023/2/2156单击“引用位置”框，再单击要进行合并的第一个区域的工作表标签，键入区域的给定名称，再单击“添加”。对每个区域重复这一步骤如果要在源区域的数据更改的任何时候都自动更新合并表，并且确认以后在合并中不需要包括不同的或附加的区域，请选中“创建连至源数据的链接”复选框2.按位置按位置进行合并行合并计算算第56页/共107页572023/2/2157单击“引用位置”框，再单击要进行合并的第一个区域的

15、工作表标签，键入区域的给定名称，再单击“添加”。对每个区域重复这一步骤如果要在源区域的数据更改的任何时候都自动更新合并表，并且确认以后在合并中不需要包括不同的或附加的区域，请选中“创建连至源数据的链接”复选框2.按位置按位置进行合并行合并计算算第57页/共107页582023/2/2158根据位置进行合并时，请将“标签位置”下的复选框设为空，源数据中的行或列标志不被复制到合并中，如果需要合并数据的标志，请从源区域之一进行复制或手工输入这是与按分类进行合并的区别2.按位置按位置进行合并行合并计算算第58页/共107页592023/2/2159源数据未按顺序排列但使用相同标签，计划合并来自包含匹配

16、标志的行或列中的数据在“标签位置”下，选中指示标志在源区域中位置的复选框：首行、最左列或两者都选。3.按分按分类进行合并行合并计算算第59页/共107页602023/2/21601.确保每个数据区域为列表（包含相关数据的一系列行）格式：第一行中的每一列都具有标志，同一列中包含相似的数据，并且在列表中没有空行或空列。2.根据位置进行合并时，请确保每个区域具有相同的布局，请将“标签位置”下的复选框设为空（二）合并（二）合并计计算算注意事项注意事项第60页/共107页612023/2/21613.根据分类进行合并时，确保要合并的行或列的标志具有相同的拼写（大小写、缩写、空格）。任一与其他源数据区域中

17、的标志不匹配的标志都会导致合并中出现单独的行或列（二）合并（二）合并计计算算注意事项注意事项第61页/共107页622023/2/21621.排序2.排位3.百分比排位（三）（三）排序、排位与百分比排位排序、排位与百分比排位第62页/共107页632023/2/2163（1）关键词：主要、次要（是否包括标题）（2）依据：数值、颜色、图标（3）次序：升、降1.排序：排序：条件条件第63页/共107页642023/2/2164rank函数（）如果多个值具有相同的排位，则返回该组数值的最高排位，对重复数的排位相同，但重复数的存在将影响后续数值的排位2.排排位位：列出位次：列出位次第64页/共107页

18、652023/2/2165RANK.AVG:如果有一个以上的值排位相同，则返回平均排位如有4个数并列第5，为4个5，个为（5+6+7+8）/42.排排位位：列出位次：列出位次第65页/共107页662023/2/21661.排名前20%奖励20002.排在31%50%的奖励10003.排在5180%奖励500大于该数据的数据个数/（大于该数据的数据个数+小于该数据的数据个数）思考：如何按名次确定奖金？思考：如何按名次确定奖金？第66页/共107页672023/2/2167（1）借助percentrank函数1 PERCENTRANK（array，x，significance）（2）排位与百分比

19、排位文件选项加载项转到分析工具库数据分析3.百分比百分比排排位位第67页/共107页682023/2/2168（1）点：原数据的序数（2）排序后的数据系列：名称随数据的标志而变化（3）排位：重复数据占用同一位置（4）百分比：按照降序排列3.百分比百分比排排位位第68页/共107页692023/2/21691.最值最值2.平均值平均值3.方差方差和和标标准差准差4.求求协方差和相关系数协方差和相关系数（四）（四）求最值、平均值、方差和标求最值、平均值、方差和标准差准差第69页/共107页702023/2/2170（1）最最大大值值：max函数（2）最最小小值值：min函数（3）第几大：large

20、函数（4）第几小：small函数2.平均值平均值3.方差方差4.标标准差准差1.求最值求最值（可能不只（可能不只1个）个）第70页/共107页712023/2/2171average函数DAVERAGE2.求平均值求平均值第71页/共107页722023/2/2172（1）总体方差：VarP函数（2）样本方差：Var函数（3）总体标准差：StDevp函数（4）样本标准差：StDev函数3.求方差和标求方差和标准差准差第72页/共107页732023/2/2173（1）协方差：covar函数=covar（数据系列1，数据系列2）（2）相关系数：CORREL函数4.求求协方差和相关系数协方差和相关

21、系数第73页/共107页742023/2/2174Excel2003:Excel2003:工具工具数据分析数据分析分析工具分析工具Excel2010:Excel2010:数据数据数据分析数据分析分析工具分析工具（五）回归分析和方差分析（五）回归分析和方差分析第74页/共107页752023/2/2175查询、列示、打印符合一个（或多个）条件的数据项1.中南地区的销售情况2.中南地区男、女销售代表的销售情况（六）（六）列出符合条件的数据列出符合条件的数据第75页/共107页762023/2/21761.自动筛选2.高级筛选3.数据透视表（六）（六）列出符合条件的数据列出符合条件的数据第76页/共

22、107页772023/2/21771.各地区的销售总额2.男、女销售代表的销售总额3.中南地区男、女销售代表的销售总额（七）（七）分别计算和展示每一部门的业绩分别计算和展示每一部门的业绩第77页/共107页782023/2/21781.分类汇总（必须先按汇总的标志排序）2.分级显示3.数据透视表（七）（七）分别计算和展示每一部门的业绩分别计算和展示每一部门的业绩第78页/共107页792023/2/2179（一）数据透视表的性质（一）数据透视表的性质（二）数据透视表的构成要素（二）数据透视表的构成要素（三）数据透视表的编制（三）数据透视表的编制（四）多重合并计算数据区域数（四）多重合并计算数据

23、区域数六、数据透视表六、数据透视表第79页/共107页802023/2/21801.1.一种交互式报表一种交互式报表2.2.把繁杂的数据简洁化把繁杂的数据简洁化3.3.最常用、功能最全的最常用、功能最全的ExcelExcel数据分析工具之一数据分析工具之一（一）数据透视表的性质（一）数据透视表的性质（1/3）第80页/共107页812023/2/2181可以快速分类汇总、比较大量的数据可以快速分类汇总、比较大量的数据可以随时选择其中页、行和列中的不同元可以随时选择其中页、行和列中的不同元素，以达到快速查看源数据的不同统计结素，以达到快速查看源数据的不同统计结果果可以随意显示和打印出你所感兴趣区

24、域的可以随意显示和打印出你所感兴趣区域的明细数据。明细数据。（一）数据透视表的性质（一）数据透视表的性质（2/3）第81页/共107页822023/2/2182一张一张“数据透视表数据透视表”仅靠鼠标移动字段位置，即可变换出各种类型的报表。仅靠鼠标移动字段位置，即可变换出各种类型的报表。（一）数据透视表的性质（一）数据透视表的性质（3/3）第82页/共107页832023/2/21831.1.页字段页字段/报表筛选报表筛选页页2.2.行字段行字段/标签标签行行3.3.列字段列字段/标签标签列列4.4.数据项数据项/数值数值值值（二）数据透视表的构成要素（二）数据透视表的构成要素可可以以自自定定

25、义这些些字字段段设置置以以便便对其其重重命命名名，并并将将其其格格式式化化为需要的格式需要的格式第83页/共107页842023/2/21841 1.明确目的明确目的2.2.设定结构设定结构3.3.鼠标移至数据源中（定位于数据区鼠标移至数据源中（定位于数据区域内任一单元格）域内任一单元格）（三）数据透视表的编制（三）数据透视表的编制（1/3）第84页/共107页852023/2/21854.4.插入数据透视表插入数据透视表5.5.设计数据透视表设计数据透视表6.6.美化版面美化版面（三）数据透视表的编制（三）数据透视表的编制（2/3）第85页/共107页862023/2/21865.5.设计数

26、据透视表设计数据透视表（1 1）根据目的和结构拖拽页字段、行字段、根据目的和结构拖拽页字段、行字段、列字段和数据项列字段和数据项（2 2）调整页字段、行字段、列字段、数据调整页字段、行字段、列字段、数据项项（3 3）重新设定汇总项重新设定汇总项第86页/共107页872023/2/218720072007和和20102010中数据透视表向导被隐藏中数据透视表向导被隐藏在后台在后台通过快捷键调出使用（通过快捷键调出使用（Alt+D+PAlt+D+P）直接自定义在快速访问工具栏中直接自定义在快速访问工具栏中（四）多重合并计算数据区域数（四）多重合并计算数据区域数第87页/共107页882023/2

27、/218820072007：OfficeOffice按钮按钮ExcelExcel选项选项自自定义定义不在功能区中的命令不在功能区中的命令数据透数据透视表和数据透视图向导视表和数据透视图向导添加添加确定确定20102010：文件文件选项选项快速访问工具栏快速访问工具栏不在功能区中的命令不在功能区中的命令数据透视表和数数据透视表和数据透视图向导据透视图向导添加添加确定确定（四）多重合并计算数据区域数（四）多重合并计算数据区域数第88页/共107页892023/2/21891.1.页：页：多重合并计算区域数据透视表中的新字段，分析时可以通过拖拽实现数据分析的目的页字段数目的最大值为4（四）多重合并计

28、算数据区域数（四）多重合并计算数据区域数第89页/共107页902023/2/21902.2.行行：始终由数据源中的第一列组成3.3.列列：是一项高级字段，为数据源中的第一列之外其他列的合并打包（四）多重合并计算数据区域数（四）多重合并计算数据区域数第90页/共107页912023/2/21914.4.值：值：数据源中除首字段外的所有“项”。“值”字段默认数据源的文本项为零值（四）多重合并计算数据区域数（四）多重合并计算数据区域数第91页/共107页922023/2/2192（一）资金习性示意图（一）资金习性示意图（二）投资组合机会集曲线（二）投资组合机会集曲线（三）利润构成分析饼形图（三）利

29、润构成分析饼形图（四）产品销售对比分析柱形图（四）产品销售对比分析柱形图七、数据关系的图形描述七、数据关系的图形描述第92页/共107页932023/2/2193（五）动态图表（五）动态图表（六）主次坐标轴图表（六）主次坐标轴图表（七）甘特图（七）甘特图七、数据关系的图形描述七、数据关系的图形描述第93页/共107页942023/2/2194（一）资金习性示意图（一）资金习性示意图第94页/共107页952023/2/2195（二）投资组合机会集曲线（二）投资组合机会集曲线第95页/共107页962023/2/2196（三）利润构成分析饼形图（三）利润构成分析饼形图第96页/共107页9720

30、23/2/2197（四）产品销售对比分析柱形图第97页/共107页982023/2/2198（五）动态图表视图工具栏窗体复选框 第98页/共107页992023/2/2199（六）主次坐标轴图表当二维图表中不同的数据系列的数值范围变化很大，或在您具有混合数据类型（例如价格和成交量）时，可在次值 （y y）坐标轴上绘制一个或多个数据系列。次坐标轴的刻度反映了相应数据系列的值。第99页/共107页1002023/2/21100单击需要沿次数值轴绘制的数据系列。单击需要沿次数值轴绘制的数据系列。在在“格式格式”菜单上，单击菜单上，单击“数据系列数据系列”，再单击，再单击“坐标轴坐标轴”选项卡。选项卡

31、。单击单击“次坐标轴次坐标轴”。第100页/共107页1012023/2/21101（七）甘特图第101页/共107页1022023/2/21102选择绘制甘特图需要的数据选择绘制甘特图需要的数据（任务、开始时间任务、开始时间）；在在“插入插入”选项卡上的选项卡上的“图表图表”组中单击组中单击“条条形图形图”，单击，单击“堆积条形图堆积条形图”；在图表中，对着在图表中，对着“开始时间开始时间”数据系列，单击数据系列，单击右键，点击右键，点击“选择数据选择数据”，添加，添加“所需时间所需时间”数据；数据；主要制作流程（主要制作流程（1/3）第102页/共107页1032023/2/21103在图

32、表中，对着在图表中，对着“开始时间开始时间”数据系列，单击数据系列，单击右键，点击右键，点击“设置数列系列格式设置数列系列格式”，在，在“系列系列选项选项”组中单击组中单击“填充填充”，选中，选中“无填充无填充”后，后，单击单击“关闭关闭”。主要制作流程（主要制作流程（1/3）第103页/共107页1042023/2/21104选择纵坐标轴选择纵坐标轴（数值轴数值轴），在，在“设置坐标轴格设置坐标轴格式式”选项卡的选项卡的“坐标轴选项坐标轴选项”组中，选中组中，选中“逆逆序类别序类别”复选框复选框；在图表上，单击图例，然后按在图表上，单击图例，然后按DeleteDelete；进一步美化。进一步

33、美化。主要制作流程（主要制作流程（3/3）第104页/共107页1052023/2/211051.1.自我帮助、永不落伍自我帮助、永不落伍Excel Excel 之家：之家：ExcelExcel精英培训论坛精英培训论坛：办公软件学习网办公软件学习网：ExcelExcel金融计算专业教程金融计算专业教程王晓明王晓明清华大学出版社，清华大学出版社，20042004ExcelExcel实战技巧精粹实战技巧精粹excelhomeexcelhome人民邮电人民邮电出版社，出版社，20072007Excel 2010Excel 2010应用大全应用大全人民邮电出版社人民邮电出版社，201120112.2.相互学习、共同进步相互学习、共同进步 八、好好学习、天天向上八、好好学习、天天向上第105页/共107页1062023/2/21第106页/共107页感谢您的观看！第107页/共107页