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

重庆工商大学经济管理实验教学中心赵青华第一部分EXCEL基础知识•启动：开始→程序→MicrosoftExcel;双击Excel快捷图标；双击.xls文件.•关闭:按关闭按钮;选择“文件”中“退出”；或ALT+F4.•创建工作簿:单击“新建”按钮;使用“Ctrl+N”•打开工作簿:文件→打开或直接双击图标.•保存工作簿:文件→保存或另存为;“Ctrl+S”.•插入工作表:插入→工作表或表标签处单击右健插入•在工作簿中切换工作表:“Ctrl+PgUp”“Ctrl+PgDn”亦可使用标签进行滚动切换.工作表的编辑•输入数据类型：标签、数值、公式•数据输入：输入数值、输入文本（数据作为文本输入前加’或=“数字”，亦可先将表格设置为文本形式）、输入日期与时间（格式分别为年／月／日或月／日，时：分；输入当天的日期ctrl+;,输入当前时间为ctrl+shift+:）.输入公式为”＝＋公式“•相同数据输入：shift+ctrl+enter•等比或等差输入：编辑→填充→序列•采用公式输入数据：如单元格之间存在制约关系“＝if(b2=“甲”,10%,5%)数据输入•输入公式：凡以等号开头被认为是公式“＝ＳＱＲＴ（Ａ２）”。如公式有误，则显示错误信息。•运算符及优先级：（）→-％→^→*和/→+和-→&→=、、、=、＝、•公式显示：工具→选项→视图→公式选框•引用：单击单元格→输入“=”单击第一个引用单元格→输入符号→单击第二个引用单元格……→回车•引用分为相对引用和绝对引用、混合引用和三维引用。绝对引用符号$,三维引用格式为“表格＋单元格地址”输入函数•EXCEL有工作表、财务、日期、数学、三角函数、数据库管理、统计、文本及信息类函数等•函数格式：＝函数名（参数1，参数2…）逗号、引号等全部用半角•常用函数介绍：SUM(),SUMIF(),AVERAGE(),MAX(),MIN(),COUNT(),COUNTIF().•单一函数输入方法：手动输入、粘贴函数•嵌套函数输入方法：工作表格的编排•插入单元格：单元格→插入;删除单元格：编辑→删除;清除单元格：编辑→清除或Delete•工作表的格式化：改变单元格的行高和列宽、取消与恢复网格线（工具→选项→视图→网格）、边框与底纹、字体及字号（格式→单元格→边框）、对齐方式（一般、特殊格式→单元格→对齐）、自动套用格式（格式→自动套用格式）、为单元格区域命名（利用名字框、利用名称框插入→名称→定义)•数据的复制与移动•数据的删除与替换:编辑→替换或Ctrl+H•合并单元格与数据保护打印管理•设置打印区域:文件→打印区域→设置打印区域;若取消设置,相反操作即可.•分页设置:水平分页和垂直分页:选定要插入分页符的行或列→插入→分页符•分页调整:视图→分页预览.再用鼠标拖动分页符至需要的位置.返回时,视图→普通•页面设置:文件→页面设置→页面\页边距\页眉页角及工作表•打印预览及打印输出•EXCEL的帮助使用:按?或F1第二部分EXCEL财务管理运用基础•公式及函数的高级运用:•数组公式及其运用•相同数据输入：shift+ctrl+enter•等比或等差输入：编辑→填充→序列•采用公式输入数据：如单元格之间存在制约关系“＝if(b2=“甲”,10%,5%)•输入公式：凡以等号开头被认为是公式“＝SQRT（A2）”。如公式有误，则显示错误信息。数据的获取与预处理•数据库中数据的组织方式：数据在不同软件之间的转移，以免重新录入．•常见的关系型数据库◆１９７０年以后，关系型数据库出现，改变独立文件存储方式．◆关系型数据库反映不同实体的表的集合，表表之间存在关系．◆连接数据库与应用程序之间的应用软件称为数据库管理系统（ＤＢＭＳ）．◆数据管理软件与电子表格各有所长，转换很方便表•表是数据库管理系统中最基本的单位．•若干个字段记录同一对象的情况，构成记录．◆主要关键字：在表中使一记录区别于表中其他记录，象征一条记录的字段，不会在表中重复◆外部关键字：指在表中某个在其他表中作关键字的字段，可以用于与其他表建立关联．◆表的作用是按顺序记录日常发生的活动所产生的数据．视图及查询•是ＶＦＰ在表的基础上建立的一种建立的一种数据进行反映的对象．•设计视图的规定表之间的联系．•查询是通过用户的设计而而产生的数据实体，依赖于其他表或视图．•用户可以从Excel中访问的数据库有SQLSeverOLAPservice、Ａccess、dBASE、FOXPRO、Excel、ORACLE、Paradox、SQLServer等．数据库向EXCEL的数据传递•MicrosoftQuery的安装.“数据→获取外部数据→新建数据库查询”访问Office支持的数据库管理系统,并将其引入到EXCEL的工作表.如是第一次查询则提示放入安装盘.•从VFP获取数据:◆数据→获取外部数据→新建数据库查询◆从“选择数据源”中选“VisualFoxproDatabase”◆通过“浏览”选择要访问的文件◆通过查询向导引入表或视图并规定引入的具体数据项◆设定引入的数据存放的具体位置•数据清单：包含数据的单元格区域数据类型的转换(一)•基本类型：数值型、字符型、逻辑型和日期型．•使用公式将原数据进行计算◆下面四种绝对地址的引用方式哪两种对？A.=C6/E2B.=$C$6/E2C.=$C6/E2D.=C$6/E2•使用函数转变为原数据．◆Excel默认的日期系统是1900年。如年份输入在00-29之间，则默认为2000-2029年；如在30-99则默认为1930-1999年；DATEVALUE（）可将字符型的日期转变为日期型的数据。◆字符型数据转换为和数据型数字的转换TEXT()或&;而VALUE()作用与之相反.◆字符型数据的转换:TRIM去掉字符串尾部的空;LTRIM去掉字符串开头空格;LEN返回字符串的长度;LEFT从字符串左端开始截取指定长度的字符串;FIND返回字符串1在字符串2中自开始倍数之后出现的位置;MID从字符串的开始位数截取指定长度的字符串;RIGHT从字符串的右端截取指定长度的子字符串.数据类型的转换(二)◆数值型数据的转换:INT取整;ROUND保留小数位;MOD取余;ABS取绝对值.IF条件函数.数据类型的转换(三)•使用选择性粘贴◆进行数据的转置:行列转换:选择→复制→单元格→编辑→选择性粘贴→转置→确定◆放弃数据源,只保留转换的结果:使用选择性粘贴,只存留结果,不保留公式;反之,如直接选择复制再粘贴则保留了计算公式.模板的使用•模板是一种模式化的操作界面,是种有某种规定格式的工作薄文件,有单独的文件格式.包括样式\背景\数据的有效性及数据保护方面的设计.•格式是对单元格或其区域进行设计的功能,可通过”格式”来完成各项设计工作.•样式是事先设计好的格式设定•工具菜单中的选项:可以让用户自行定义进入EXCEL后的工作表界面.•数据的有效性:避免数据输入不当•数据的保护:通过工具菜单中”保护”功能来完成.数据检索•数据检索是一种简单的数据分析.包括筛选、简单汇总和分析统计等。可用菜单或函数。•数据清单：包含相关数据的一系列工作表数据行。清单结构/清单格式•排序：排序操作要用户指定一个或多个属性作为排序的关键字•分类汇总：根据用户指定的关键字对数据清单进行分类按照类别对用户指定的数据字段进行运算。汇总之前需要先进行排序。•高级筛选：显示符合条件的记录，并可复制到工作表的其他位置。数据库函数•数据库函数是针对一个数据清单，根据用户规定条件对数据清单中指定字段进行检索和计算的函数。•函数的基本语法：database指数据清单所在的单元格区域；field指函数将要筛选和计算的对象；criteria则条件区域所处的单元格地址。•函数的使用方法：DISCUNT:查找清单中符合条件的记录，并计算指定字段包含数字的单元格数目；DCOUNTA查找数据清单中符合条件的，并计算这些记录中指定字段中非空单元格数目。•DMAX可以查找清单中符合条件的记录中指定字段的最大值，并计算指定字段包含数字的单元格数目；DMIN则相反；DSUM可以计算符合条件的记录中指定字段的合计数；DAVERAGE计算符合条件的记录中指定字段的均值；DGET返回条件符合且惟一存在的记录中指定字段的值；DPRODUCT可以计算符合条件的记录中指定字段的乘积。函数的使用方法数据的合并计算•工作表、工作薄之间的链接：指一单元的计算公式中包含了其他工作表或工作薄的单元格地址，是最为简洁的数据合并方式。•合并计算：多个单元格区域的数字可能通过“合并计算”功能进行组合。可按位置或标志进行合并计算。“数据→合并计算→求和→引用位置→添加→确定”•合并工作薄：针对具有共享属性的工作薄文件而言，它实现了通过一个副本文件对正本文件的替换修正，并且对发生修正的单元格进行追踪记录。数据透视表•用于快速汇总大量数据的交互式表格。用户可按对源数据的不同汇总要求来构造透视表。通过显示不同的页来筛选数据或显示所关心区域的明细数据。•数据透视表的基本编制过程：ComputerAidedFinance•在合并表中先选中单元格区域→数据→合并计算→在函数中选择求和(平均\乘积\计数\标准差…)•模拟运算表:将工作表中的一个单元格区域的数据进行模拟计算,测试使用一个或两个变量对运算结果的影响.有单变量模拟和多变模拟.单变量模拟运算表:数据→模拟运算表→输入引用列(行)单元格单变量求解:求解只有一个变量的方程的根.工具→单变量求解.数据的合并计算•用于求解运筹学\线性规划\线性或非线性方程组.•求解优化问题:线性规划加载宏.完全安装.否则,放入光盘,单击工具,选择加载宏对话框的规划求解即可.求解方程组:设计工作表→设置变动单元格,以存放解→选定单元格输入求和公式→任取一方程的和作目标函数,另外的作约束条件→在规划求解对话框中设置目标单元格输入约束条件→求解规划求解•建立方案•显示方案•修改和增删方案•建立方案报告数据分析工具库:分析工具库包括方差分析\相关系数分析\协方差分析\描述统计分析\指数平滑分析\F检验\傅里叶分析\T检验\Z检验等.方案分析•建立自定义函数（以经济订货批量为例）:◆工具→宏→VisualBasic编辑器→插入→模块◆在模块1窗口中，单击插入→过程→名称→输入经济订货批量→类型→函数→确定使用自定义函数：同EXCEL函数一样。只是在函数分类中选择用户定义→经济订货批量含有宏的工作薄再次打开时，要注意选择启用宏。宏与VBA初步应用•单利终值与现值◆=1000*(1+5%*5)◆=1000/(1+5%*5)•复利终值与现值◆=(1+B2:K2/100)^A3:A12OR=FV(5%,5,0,-100)◆=1/(1+B2:K2/100)^A3:A12OR=PV(5%,5,0,-100)年金终值与现值◆=FV(RATE,NPER,PMT,PV,TYPE)◆=PV(RATE,NPER,PMT,FV,TYPE)终值\现值\年金终\现值•先付年金终值:◆=FV(RATE,NPER,PMT,PV,TYPE)(1+RATE)•先付年金现值:◆=PV(RATE,NPER,PMT,FV,TYPE)(1+RATE)递延年金现值:◆=A·(PVIFAi,n)·(PVIFi,m)递延年金终值:◆=FV(RATE,NPER,PMT,PV,TYPE)先付(递延)年金现/终值•名义利率和有效利率关系–有效年利率的计算◆=EFFECT(NOMINAL_RATE,NPERY)前者为名义利率，后者为复利期数。–名义年利率的计算◆＝NOMINAL(EFFECT_RATE,NPERY)(1)1mim名义利率和有效利率•贷款利率计算◆＝RATE（NPER，PMT，PV，FV，TYPE，GUESS）•贷款偿还期的计算◆=NPER（RATE，PMT，PV，FV，TYPE）等额分期付款方式贷款年偿还总额◆=PMT（RATE，NPER，PV，FV，TYPE）等额分期付款方式贷款年偿还额本金◆=PPMT（RAT