Excel在财务管理中的高级运用.docx

1、Excel在财务管理中的高级运用目 录Excel在财务管理中的高级运用 1一、Excel真的难学吗？ 1（一）会摁计算器就会操作Excel 1（二）Excel能办计算器办不到的事 2（三）什么情况下用Excel最合适 4二、工作界面熟悉吧？ 4（一）如何安装 4（二）怎样启用 4（三）窗口有哪些基本要素？ 4（四）如何保存与关闭 7三、数据能对不该看的人保密吗？ 8（一）避免接触 8（二）加密文件 8（三）保护工作簿 9（四）保护工作表 10（五）隐藏行或列 10（六）锁定单元格 11（七）隐藏公式 12（八）隐藏单元格 13四、输入大量数据累不累？ 13（一）尽可能导入与复制 13（二）迫不

2、得已敲键盘 15（三）直接计算得结果 16（四）自动填充有规律 18*（五）“引用”需谨慎 19（六）合并/拆分得新列 22（七）三维操作效率高 24（八）阿拉伯数字自动转换为人民币大写金额 25五、输入的数据错没错？ 26（一）类型正确是基础 26（二）冻结窗口好对照 28（三）确保“数据有效性” 28（四）借助条件格式来提醒 29六、这些财务指标算怎样算？ 32（一）投资回收期 32（二）现值或净现值 32（三）内含报酬率（到期收益率/实际利率） 33（四）债券定价和溢折价摊销 34七、贷款偿付计划怎么编？ 34（一）每次等额还本付息额 34（二）每次等额还本，按期付息 35八、规划求解能

3、够做什么？ 36（一）通过规划求解合理配置有限资源 36（二）如何通过规划求解分配辅助生产成本 39九、如何敏锐分析各因素变动的影响？ 40（一）单变量敏感分析 40（二）双变量敏感分析 40（三）方案管理 40十、如何合理构建数据库？ 46（一）应收账款备查账 46（二）存货管理（进销存） 46（三）人事档案（薪金管理） 46（四）试题库与自测 46十一、如何高效整理和分析数据？ 46（一）列出符合条件的数据 47（二）如何分别计算和展示每一部门的业绩 47（三）排序、排位与百分比排位 48（四）计数 49（五）求最值、平均值、方差和标准差 52（六）回归分析 52十二、如何绘制图形直观揭示

4、数据关系？ 53（一）散点图的运用：资金习性预测法 53（二）散点图的运用：投资组合机会集曲线 53（三）饼图的运用：利润构成分析 53（四）柱形图的运用： 53（五）条形图的运用： 54（六）图形与ppt的结合 54十三、怎样用好数据透视表和数据透视图？ 54（一）数据透视表 54（二）数据透视图 54十四、Excel的能耐其实也有限 55十五、好好学习、天天向上 58（一）自我帮助、永不落伍 58（二）相互学习、共同进步 59Excel在财务管理中的高级运用判断高低的标准水准标志1. 入门2. 初级引用3. 中级宏变量4. 高级意识一、Excel真的难学吗？（一）会摁计算器就会操作Exce

5、l计算：12345（1）计算器（2）Excel（二）Excel能办计算器办不到的事1.操作简便计算“12345”（1）计算器5个数字、4个加数、1个等号10（2）Excel5个数字、1个求和按钮62.过程直观计算器仅显示结果，一般难以检验原始数据是否正确如计算“12345”153.功能强大现有当日发行的企业债券，面值100万元，五年期，票面年利率4.16%，利息按年支付，到期偿还面值。发行费用和所得税忽略不计。问：1.如果不购买该债券，投资于甲项目的年报酬率为5.38%，则该债券的价格高于多少时，企业将不愿购买债券而投资于甲项目？2.如果按110万元购入该债券，则在持有到期时获得的年均投资报酬

6、率为多少？思考：如果每半年付息一次，价格？（三）什么情况下用Excel最合适1以图表精确表示数据关系的250%以上的内容属于数值处理的3部分数值、部分文本的4内容全为文本的二、工作界面熟悉吧？（一）如何安装（二）怎样启用（三）窗口有哪些基本要素？1.Excel20032.Excel20071. 系统控制图标（Alt+space）2. 标题栏（显示当前文件名）3. 最小化按钮、最大化按钮和关闭按钮4. 菜单栏5. 工具栏/工具组6. 格式栏7. 功能快捷按钮8. 窗体工作表sheet（二维表格）将多张表格叠在一起工作簿book（三维表格）9. 行（row）10. 列（column）11. 单元格

7、12. 名称框（地址栏）13. 编辑栏14. 记录单（Alt + D + O）15. 状态栏（任务栏窗口）16. 滚动条（块）（四）如何保存与关闭三、数据能对不该看的人保密吗？（一）避免接触1.物理隔离2.及时关闭（二）加密文件1.何时加密2.如何加密（1）给文件加保护口令Excel2003：文件另存为右上方的工具常规选项打开权限密码Excel2007：文件另存为右上方的工具常规选项（2）修改权限口令Excel2003：文件另存为右上方的工具常规选项修改权限密码Excel2007：文件另存为右上方的工具常规选项（3）只读方式保存和备份文件的生成以只读方式保存工作簿就可以实现以下目的：当多数人同

8、时使用某一工作簿时，如果有人需要改变内容，那么其他用户应该以只读方式打开该工作簿；当工作簿需要定期维护，而不是需每天做日常性的修改时，将工作簿设置成只读方式，可以防止无意中修改工作簿。可在“保存选项“对话框中选定生成备份文件，那么用户每次存储该工作簿时，Excel将创建一个备份文件。备份文件和源文件在同一目录下，且文件名一样，扩展名为 .xlk。这样当由于操作失误造成源文件毁坏时，就可以利用备份文件来恢复。（三）保护工作簿Excel2003：工具保护保护工作簿结构/窗口Excel2007：审阅保护工作簿若需要口令则在对话框的“密码（可选）”输入框中键入口令，并在“确认密码”对话框中再输入一遍刚

9、才键入的口令，然后单击确定按钮。口令最多可包含255个字符，并且可有特殊字符，区分大小写。（四）保护工作表防止用户对工作表内容的修改Excel2003：工具保护保护工作表保护工作表及锁定的单元格内容“密码（可选）”/“确认密码”/允许此工作表的所有用户进行Excel2007：审阅保护工作表口令最多可包含255个字符，并且可有特殊字符，区分大小写。（五）隐藏行或列隐藏工作表或工作表中的行或列，可在一定程度上也可以起到保护工作表的目的。如果工作簿结构受到保护，将无法隐藏工作表或工作表中的行或列，也无法取消对它们的隐藏。要获得最高级的安全性，首先隐藏工作表，然后保护工作簿的结构；当然，要取消对工作表

10、的隐藏之前应先解除对工作簿的保护。方法1：Excel2003：窗口隐藏Excel2007：视图隐藏方法2：鼠标拖拽（六）锁定单元格工作表级的保护是对工作表中所有单元格或全部对象、方案的保护，但有时需要对工作表中的个别单元格进行保护。如工作表中往往有许多公式单元格，用来进行一些计算统计工作，如果操作者直接在这些单元格中键入数据，将会丢失这些精心设计的公式，使计算统计工作无法进行。所以，很有必要对这些单元格进行保护。注意：只有在保护工作表的情况下，锁定单元格才会生效。即工作表保护是较为高层的保护机制，而单元格保护从属于工作表保护。Excel2003：选定要保护的单元格格式单元格格式保护锁定/隐藏如

11、果选择“锁定”选项，则工作表受保护后不能更改这些单元格；选择“隐藏”选项，则工作表受保护后隐藏公式。Excel2007：选定要保护的单元格开始格式锁定单元格/设置单元格格式保护锁定/隐藏（七）隐藏公式注意：只有在保护工作表的情况下，隐藏公式才会生效。即工作表保护是较为高层的保护机制，而单元格保护从属于工作表保护。Excel2003：选定要保护的单元格格式单元格格式保护隐藏如果选择“锁定”选项，则工作表受保护后不能更改这些单元格；选择“隐藏”选项，则工作表受保护后隐藏公式。Excel2007：选定要保护的单元格开始格式锁定单元格/设置单元格格式保护隐藏（八）隐藏单元格Ctrl+1设置单元格格式数

12、字自定义类型输入“;”确定四、输入大量数据累不累？（一）尽可能导入与复制1.导入（1）Excel2007数据自Access/自网站/自文本/自其他来源/query（2）Excel2003数据导入外部数据2.复制与移动（1）单元格的复制与移动（2）块的复制与移动一整行（列）连续多行（列）包含多个连续单元格的区域由多个不连续单元格组成的区域（3）工作表的复制注意:如果单元格中含有引用,容易出错3.转置行列互换（1）Excel2003编辑复制选择性粘贴转置（2）Excel2007开始复制选择性粘贴转置*4.同加/减/乘/除（以常数a）（1）Excel2003输入a编辑复制选中区域选择性粘贴加/减/乘

13、/除（2）Excel2007输入a开始复制选中区域选择性粘贴加/减/乘/除（二）迫不得已敲键盘1.单个单元格内容的输入2.一整块相同内容的输入一整行（列）连续多行（列）内容的输入包含多个连续单元格的区域内容的输入由多个不连续单元格组成的区域内容的输入Ctl（三）直接计算得结果1.通过表达式计算=1000*（15%）31157.625=100*（15%）31）/5%）315.252.通过工作簿函数计算=power（1+5%,3）=fv（5%,3,-100,0,0）（四）自动填充有规律1.相邻单元格数值相同的填充在起始单元格中输入数字，鼠标向右（向下）拖拽但“文本+阿拉伯数字”、“星期一”作为起始

14、的例外，应改为Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽2.相邻单元格连续编号的填充如1、2、20输入起始数1Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽3.延用前续相关数据间的分布规律如：1%、2%、3%10%输入前两个数Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽4.自定义填充序列*（五）“引用”需谨慎1.何为“引用”指明数据的位置一个公式可以引用工作表上不同单元格的数据，多个公式也可以引用同一单元格数据；还可以引用同一工作簿中不同工作表的数据，或是不同工作簿中工作表的数据，乃至其他应用程序的数据2.何时“引用”A产品B产品C产品销量100320760单价15207收入

15、？A产品销量100单价152025收入？1%2%3%1233.“引用”的类型（1）相对引用（2）绝对引用（3）混合引用F4（4）三维引用引用本工作簿中多个工作表的同一单元格（或单元格区域）某商场的“销售额”工作簿中包含12个月份的“销售额”工作表，现在要汇总全年的销售额，就需要使用三维引用。假定要在汇总表的B2单元格中记入销售额全年总计，首先要将该单元格击活，然后输入“=SUM(Sheetl：Sheetl2!A2)”。式中：Sheetl：Sheetl2是1-12月的工作表标签，!号将工作表和单元格隔开，A2是销售额所在的单元格。输入完毕回车确认，即将计算结果记入B2单元格内。（5）外部引用引用

16、其他工作簿的数据叫做外部引用操作方法和三维引用基本相同，只是在输入公式时需依次输入其他工作簿的名称、工作表的名称、引用的单元格或单元格区域。上例，如果是五个单位的全年销售额，分别存在工作簿Bookl至Book5的工作表Sheetl的A2单元格中，可在当前工作表的A1单元格中输入“=SUM(BooklSheetl：Book5Sheetl!A2)”，回车确认，即将五个单位的全年销售额汇总到一起。（6）远程引用引用其他应用程序的数据叫做远程引用（六）合并/拆分得新列1.列合并单位姓名职务/职称中南财经政法大学张敦力教授美国微软公司Bill Gates董事会主席单位/姓名/职务/职称中南财经政法大学/

17、张敦力/教授美国微软公司/Bill Gates/董事会主席2.分列将简单的单元格内容（如名和姓）拆分到不同的列中全名名姓Syed AbbasSyedAbbasMolly DempseyMollyDempseyLola JacobsenLolaJacobsenDiane MargheimDianeMargheim方法1:使用“文本分列向导”拆分姓名根据您的数据，您可以基于分隔符（如空格或逗号）或基于数据中的特定分栏符位置拆分单元格内容。选择要转换的数据区域“数据”“分列”。方法2:使用函数在各列之间拆分文本文本函数适用于操作数据中的字符串，例如，将一个单元格中的名、中间名和姓分布到三个不同的列中

18、。函数语法LEFTLEFT（text, num_chars）MIDMID（text,start_num,num_chars） RIGHTRIGHT（text, num_chars）SEARCHSEARCH（find_text,within_text,start_num）LENLEN（text）（七）三维操作效率高Excel在一个工作簿里可以存放多张表格，并且允许同时对多张表格进行操作，这种操作不是一张接一张的表格操作，而是一次操作对多张表格同时起作用1. 一个工作簿里存放多张表格2. 同时对多张表格进行操作3. 一次操作对多张表格同时起作用（八）阿拉伯数字自动转换为人民币大写金额例：将B2中以

19、阿拉伯数字3701.08表示的金额，在B3中写成人民币大写金额（即“叁仟柒佰零壹元零捌分”）。将下式拷贝到B3中，B2中的阿拉伯数字将自动转换为人民币大写金额。=IF（INT（B2*10）-INT（B2）*10）=0,TEXT（INT（B2）,DBNum2G/通用格式）&元&IF（INT（B2*100）-INT（B2）*10）*10）=0,整,零&TEXT（INT（B2*100）-INT（B2*10）*10,DBNum2G/通用格式）&分）,TEXT（INT（B2）,DBNum2G/通用格式）&元&IF（INT（B2*100）-INT（B2）*10）*10）=0,TEXT（INT（B2*10）

20、-INT（B2）*10）,DBNum2G/通用格式）&角整,TEXT（INT（B2*10）-INT（B2）*10）,DBNum2G/通用格式）&角&TEXT（INT（B2*100）-INT（B2*10）*10,DBNum2G/通用格式）&分）五、输入的数据错没错？（一）类型正确是基础1.数值（1）货币（2）日期（3）百分数（4）小数方法1：选中单元格ctrl+1数字数值方法2：选中单元格击右键设置单元格格式方法3：Excel2003:格式单元格数字数值Excel2007:开始格式设置单元格格式数字数值以上只是简单的显示，而未四舍五入。如：1/3（5）分数整数（0）+空格+分数（6）科学记数法输

21、入超过11位数字时，会自动转为科学计数的方式2.文本（二）冻结窗口好对照Excel2003：窗口冻结窗格Excel2007：视图冻结窗格（三）确保“数据有效性”1.提示“输入信息”（1）数据有效性的提示（2）插入批注来提醒2.科学设置有效性条件数据类型：整数、小数、日期、时间、序列（性别、职称）、自定义数值范围文本长度3.出错警告不含糊4.圈释无效数据Excel2003：工具公式审核公式审核工具栏圈释无效数据先在某个单元格输入了一个小于等于0的数据,然后通过数据有效性设置单元格的值为大于0,当单击公式审核中的圈释无效数据工具时,该单元格就会有一个红圈标记。Excel2007：数据“数据有效性”

22、圈释无效数据（四）借助条件格式来提醒突出显示提示作用1.重复的数据2.成绩大于85分选中Excel2003：格式条件格式条件1（1）单元格数值/公式添加条件1（2）单元格数值/公式格式单元格格式图案/字体/边框Excel2007：开始条件格式新建规则/管理规则只为包含以下内容的单元格设置格式/使用公式确定要设置格式的单元格格式颜色设置单元格格式数字/图案/字体/边框确定3.销售前3名先选中E2:E12单元格，打开所示的对话框，输入公式“=E2LARGE（$E$2:$E$12,4）”4.位数不对的身份证号码（不变色）：“=OR（LEN（A8）=15,LEN（A8）=18）5.让符合特殊条件的日期

23、突出显示希望符合特殊条件的日期所在的单元格突出显示，如星期六或星期天。这时我们可以先选中日期所在的单元格，如A2:A12，然后打开单元格，输入公式“=OR（WEEKDAY（A2,2）=6,WEEKDAY（A2,2）=7）”，然后设置符合条件的单元格填充色为阴影即可6.让工作表间隔固定行显示阴影让工作表间隔固定行显示阴影的公式：“=MOD（ROW（）,2）=0”间隔两行显示阴影则用公式：“=MOD（ROW（）,3）=0”Excel2003：选定内容格式条件格式条件1（1）公式单元格数值/公式格式单元格格式图案/字体/边框Excel2007：选定内容开始条件格式新建规则/管理规则使用公式确定要设置

24、格式的单元格格式颜色设置单元格格式数字/图案/字体/边框确定=MOD（ROW（）,2）=0六、这些财务指标怎样算？（一）投资回收期=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)（二）现值或净现值1.Pv函数法2.NPV函数法容易出错3.XNPV函数法4.列表求解法（三）内含报酬率（到期收益率/实际利率）1.Rate函数法2.Irr函数法3.Mirr函数法4.单变量求解法解一元（n次）方程Excel2007：数据假设分析单变

25、量求解Excel2003：工具单变量求解（四）债券定价和溢折价摊销1.债券定价2.溢折价摊销现有当日按1018万元发行的企业债券，面值1000万元，三年期，票面年利率4%，利息按年支付，到期偿还面值。发行费用和所得税忽略不计。问：1.实际年利率为多少？2.采用实际年利率法摊销溢折价。七、贷款偿付计划怎么编？（一）每次等额还本付息额1.每次偿付的总金额Pmt（r，n，pv，fv，t）2.利息3.本金（二）每次等额还本，按期付息1.每次偿付的总金额2.利息3.本金八、规划求解能够做什么？（一）通过规划求解合理配置有限资源（1）Excel2003：工具规划求解（2）Excel2007：数据规划求解生

26、产甲、乙、丙产品分别耗用a、b、c三种材料，有关资料如下：甲乙丙材料总量a3249b73515c54312产品价格8710如果企业要求总收入最高，如何合理安排生产？产量分别为x、y、z目标函数：Max8x+7y+10z约束条件：3x+2y+4z97x+3y+5z155x+4y+3z12x、y、z0（1）整理约束条件的表达式左边：含未知数的表达式右边：已知数（2）在Excel 的单元格中输入方程组BCDEFGH2产量C2?D2?E2?3xyz材料量4a32493x+2y+4zH4*5b735157x+3y+5zH5*6c543125x+4y+3zH6*78单价87109收入C9*（3）分别在C9

27、、H4、H5和H6中输入 “=8*C2+7*D2+10*E2”、“=3*C2+2*D2+4*E2”、“=7*C2+3*D2+5*E2”和“=5*C2+4*D2+3*E2”（4）规划求解数据规划求解规划求解参数设置目标单元格可变单元格约束添加求解（二）如何通过规划求解分配辅助生产成本方程组求解代数分配法3x+2y+4z=97x+3y+5z=155x+4y+3z=12无目标单元格约束条件为“”九、如何敏锐分析各因素变动的影响？（一）单变量敏感分析（1） Excel2003：数据模拟运算表（2）Excel2007：数据假设分析数据表（二）双变量敏感分析（三）方案管理由于市场的不断变化，企业生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。Excel提供了称为方案管理的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案（即多个假设条件）进行分析。（1）Excel2003：工具方案方案管理器（2）Excel2007：数据假设分析方案管理器企业生产甲、乙、丙三种产品，2007年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。根据市场情况推测，2008年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如下：2007产品销售情况品名销