excel在财务管理中的高级运用课件

《excel在财务管理中的高级运用课件》由会员分享，可在线阅读，更多相关《excel在财务管理中的高级运用课件（146页珍藏版）》请在金锄头文库上搜索。

1、重庆工商大学经济管理实验教学中心 赵青华,Computer Aided Finance,财务电算化（一）,第一部分 EXCEL基础知识,启动：开始程序Microsoft Excel;双击Excel快捷图标；双击.xls文件. 关闭:按关闭按钮;选择“文件”中“退出”；或ALT+F4. 创建工作簿:单击“新建”按钮;使用“Ctrl+N” 打开工作簿:文件打开或直接双击图标. 保存工作簿:文件保存或另存为; “Ctrl+S”.,插入工作表:插入工作表或表标签处单击右健插入 在工作簿中切换工作表: “Ctrl+PgUp” “Ctrl+PgDn”亦可使用标签进行滚动切换.,工作表的编辑,输入数据类型：

2、标签、数值、公式 数据输入：输入数值、输入文本（数据作为文本输入前加或= “数字”，亦可先将表格设置为文本形式）、输入日期与时间（格式分别为年月日或月日，时：分；输入当天的日期ctrl+;,输入当前时间为ctrl+shift+:）.输入公式为”公式“ 相同数据输入：shift+ctrl+enter 等比或等差输入：编辑填充序列 采用公式输入数据：如单元格之间存在 制约关系“if(b2=“甲”,10%,5%),数据输入,输入公式：凡以等号开头被认为是公式“（）”。如公式有误，则显示错误信息。 运算符及优先级：（） - *和/ +和-&=、=、 公式显示：工具选项视图公式选框 引用：单击单元格输入

3、“=”单击第一个引用单元格输入符号单击第二个引用单元格 回车,引用分为相对引用和绝对引用、混合引用 和三维引用。绝对引用符号$,三维引用格 式为“表格单元格地址”,输入函数,EXCEL有工作表、财务、日期、数学、三角函数、数据库管理、统计、文本及信息类函数等 函数格式：函数名（参数1，参数2）逗号、引号等全部用半角,常用函数介绍：SUM(),SUMIF(),AVERAGE(),MAX(),MIN(),COUNT(),COUNTIF(). 单一函数输入方法：手动输入、粘贴函数 嵌套函数输入方法：,工作表格的编排,插入单元格：单元格插入;删除单元格：编辑删除;清除单元格：编辑清除或Delete 工

4、作表的格式化：改变单元格的行高和列宽、取消与恢复网格线（工具选项视图网格）、边框与底纹、字体及字号（格式单元格边框）、对齐方式（一般、特殊）、自动套用格式（格式自动套用格式）、为单元格区域命名（利用名字框、利用名称框),数据的复制与移动 数据的删除与替换:编辑替换或Ctrl+H 合并单元格与数据保护,打印管理,设置打印区域:文件打印区域设置打印区域;若取消设置,相反操作即可. 分页设置:水平分页和垂直分页:选定要插入分页符的行或列插入分页符 分页调整:视图分页预览.再用鼠标拖动分页符至需要的位置.返回时,视图普通 页面设置:文件页面设置页面页边距页眉页角及工作表 打印预览及打印输出 EXCEL

5、的帮助使用:按?或F1,第二部分 EXCEL财务管理 运用基础,公式及函数的高级运用: 数组公式及其运用 相同数据输入：shift+ctrl+enter 等比或等差输入：编辑填充序列 采用公式输入数据：如单元格之间存在 制约关系“if(b2=“甲”,10%,5%) 输入公式：凡以等号开头被认为是公式“SQRT（A2）”。如公式有误，则显示错误信息。,数据的获取与预处理,数据库中数据的组织方式：数据在不同软件之间的转移，以免重新录入 常见的关系型数据库 年以后，关系型数据库出现，改变独立文件存储方式 关系型数据库反映不同实体的表的集合，表表之间存在关系 连接数据库与应用程序之间的应用软件称为数据

6、库管理系统（） 数据管理软件与电子表格各有所长，转换很方便,表,表是数据库管理系统中最基本的单位 若干个字段记录同一对象的情况，构成记录 主要关键字：在表中使一记录区别于表中其他记录，象征一条记录的字段，不会在表中重复 外部关键字：指在表中某个在其他表中作关键字 的字段，可以用于与其他表建立关联 表的作用是按顺序记录日常发生的活动所 产生的数据,视图及查询,是在表的基础上建立的一种建立的一种数据进行反映的对象 设计视图的规定表之间的联系 查询是通过用户的设计而而产生的数据实体，依赖于其他表或视图 用户可以从Excel中访问的数据库有SQL Sever OLAP service、ccess 、

7、dBASE 、 FOXPRO 、 Excel 、 ORACLE 、 Paradox 、 SQL Server等,数据库向EXCEL的数据传递,Microsoft Query的安装.“数据获取外部数据新建数据库查询”访问Office支持的数据库管理系统,并将其引入到EXCEL的工作表.如是第一次查询则提示放入安装盘. 从VFP获取数据: 数据获取外部数据新建数据库查询 从“选择数据源”中选 “Visual Foxpro Database” 通过“浏览”选择要访问的文件 通过查询向导引入表或视图并规定引入的具体数据项 设定引入的数据存放的具体位置 数据清单：包含数据的单元格区域,数据类型的转换(一

8、),基本类型：数值型、字符型、逻辑型和日期型 使用公式将原数据进行计算 下面四种绝对地址的引用方式哪两种对？ A.=C6/E2 B.=$C$6/E2 C.=$C6/E2 D.=C$6/E2 使用函数转变为原数据 Excel默认的日期系统是1900年。如年份输入在00-29之 间，则默认为2000-2029年；如在30-99则默认为 1930-1999年；DATEVALUE（）可将字符型的 日期转变为日期型的数据。,字符型数据转换为和数据型数字的转换TEXT( )或RIGHT从字符串的右端截取指定长度的子字符串.,数据类型的转换(二),数值型数据的转换:INT取整;ROUND保留小数位;MOD取

9、余;ABS取绝对值.IF条件函数.,数据类型的转换(三),使用选择性粘贴 进行数据的转置:行列转换:选择复制单元格编辑选择性粘贴转置确定,放弃数据源,只保留转换的结果:使用选择性粘贴,只存留结果,不保留公式;反之,如直接选择复制再粘贴则保留了计算公式.,模板的使用,模板是一种模式化的操作界面,是种有某种规定格式的工作薄文件,有单独的文件格式.包括样式背景数据的有效性及数据保护方面的设计. 格式是对单元格或其区域进行设计的功能,可通过”格式”来完成各项设计工作. 样式是事先设计好的格式设定 工具菜单中的选项:可以让用户自行定义进入EXCEL后的工作表界面. 数据的有效性:避免数据输入不当 数据的

10、保护:通过工具菜单中”保护”功能来完成.,数据检索,数据检索是一种简单的数据分析.包括筛选、简单汇总和分析统计等。可用菜单或函数。 数据清单：包含相关数据的一系列工作表数据行。清单结构/清单格式 排序：排序操作要用户指定一个或多个属性作为排序的关键字 分类汇总：根据用户指定的关键字对数据清单进行分类,按照类别对用户指定的数据字段进行运算。汇总之前需要先进行排序。 高级筛选：显示符合条件的记录，并可复制到工作表的其他位置。,数据库函数,数据库函数是针对一个数据清单，根据用户规定条件对数据清单中指定字段进行检索和计算的函数。 函数的基本语法：database指数据清单所在的单元格区域； field

11、指函数将要筛选和计算的对象； criteria则条件区域所处的单元格地址。,函数的使用方法：DISCUNT:查找清单中符合条件的记录，并计算指定字段包含数字的单元格数目；DCOUNTA查找数据清单中符合条件的，并计算这些记录中指定字段中非空单元格数目。,DMAX可以查找清单中符合条件的记录中指定字段的最大值，并计算指定字段包含数字的单元格数目；DMIN则相反；DSUM可以计算符合条件的记录中指定字段的合计数；DAVERAGE计算符合条件的记录中指定字段的均值；DGET返回条件符合且惟一存在的记录中指定字段的值；DPRODUCT可以计算符合条件的记录中指定字段的乘积。,函数的使用方法,数据的合并

12、计算,工作表、工作薄之间的链接：指一单元的计算公式中包含了其他工作表或工作薄的单元格地址，是最为简洁的数据合并方式。 合并计算：多个单元格区域的数字可能通过“合并计算”功能进行组合。可按位置或标志进行合并计算。“数据合并计算求和引用位置添加确定”,合并工作薄：针对具有共享属性的工作薄文件而言，它实现了通过一个副本文件对正本文件的替换修正，并且对发生修正的单元格进行追踪记录。,数据透视表,用于快速汇总大量数据的交互式表格。用户可按对源数据的不同汇总要求来构造透视表。通过显示不同的页来筛选数据或显示所关心区域的明细数据。 数据透视表的基本编制过程：,Computer Aided Finance,重

13、庆工商大学经管实验教学中心,财务电算化（二）,在合并表中先选中单元格区域数据合并计算在函数中选择求和(平均乘积计数标准差) 模拟运算表:将工作表中的一个单元格区域的数据进行模拟计算,测试使用一个或两个变量对运算结果的影响.有单变量模拟和多变模拟.,单变量模拟运算表:数据模拟运算表输入引用列(行)单元格 单变量求解:求解只有一个变量的方程的根.工具单变量求解.,数据的合并计算,用于求解运筹学线性规划线性或非线性方程组. 求解优化问题:线性规划加载宏.完全安装.否则,放入光盘,单击工具,选择加载宏对话框的规划求解即可.,求解方程组:设计工作表设置变动单元格,以存放解选定单元格输入求和公式任取一方程

14、的和作目标函数,另外的作约束条件在规划求解对话框中设置目标单元格输入约束条件求解,规划求解,建立方案 显示方案 修改和增删方案 建立方案报告,数据分析工具库:分析工具库包括方差分析相关系数分析协方差分析描述统计分析指数平滑分析F检验傅里叶分析T检验Z检验等.,方案分析,建立自定义函数（以经济订货批量为例）: 工具宏Visual Basic编辑器插入模块 在模块1窗口中，单击插入过程名称输入经济订货批量类型函数确定,使用自定义函数：同EXCEL函数一样。只是在函数分类中选择用户定义经济订货批量 含有宏的工作薄再次打开时，要注意选择启用宏。,宏与VBA初步应用,单利终值与现值 =1000*(1+5

15、%*5) =1000/(1+5%*5) 复利终值与现值 =(1+B2:K2/100)A3:A12 OR =FV(5%,5,0,-100) =1/(1+B2:K2/100)A3:A12 OR =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),名义利率和有效利率,贷款利率计算 RATE（NPER，PMT，PV，FV，TYPE，GUESS） 贷款偿还期的计算 =NPER（RATE，PMT，PV，FV，TYPE）,等额分期付款方式贷款年偿还总额 =PMT（RATE，NPER，PV，FV，TYPE） 等额分期付款