第二章 运用Excel分析与处理财务数据基础知识.ppt

《第二章 运用Excel分析与处理财务数据基础知识.ppt》由会员分享，可在线阅读，更多相关《第二章 运用Excel分析与处理财务数据基础知识.ppt（107页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第二章运用Excel分析与处理财务数据基础知识2.1 运用公式2.1.1 2.1.1 输入公式输入公式2.1.2 2.1.2 运算符及其优先级运算符及其优先级2.1.3 2.1.3 编辑公式编辑公式2.1.4 2.1.4 引用单元格引用单元格2.1.5 2.1.5 使用数组公式使用数组公式2.1.6 2.1.6 使用名称建立公式使用名称建立公式2.1.7 2.1.7 特殊的计算公式特殊的计算公式2.1.8 2.1.8 显示与隐藏公式显示与隐藏公式2.1.9 2.1.9 公式审核及出错检查公式审核及出错检查2.1.4 引用单元格1相对引用 =a22绝对引用 =$a$23混合引用 =$a2 or

2、a$24三维引用 5不同引用方式之间的切换按【按【F4F4】键】键 2.1.5 使用数组公式1输入数组公式按【按【Ctrl+Shift+EnterCtrl+Shift+Enter】组合键】组合键 2编辑数组公式3删除数组公式4数组公式的应用【例例2-12-1】【例例2-22-2】【例例2-32-3】数组公式的应用【例2-1】数组公式的应用【例2-2】数组公式的应用【例2-3】2.1.6 使用名称建立公式1 1为单元格或单元格区域命名的方法为单元格或单元格区域命名的方法 利用名称框定义名称利用名称框定义名称 利用菜单命令定义名称利用菜单命令定义名称【插入】【插入】【名称】【名称】【定义】【定义】

3、2 2单元格名称的命名规定单元格名称的命名规定3 3使用名称建立公式使用名称建立公式 直接输入名称公式直接输入名称公式 利用粘贴名称的方法输入公式利用粘贴名称的方法输入公式【插入】【插入】【名称】【名称】【粘贴】【粘贴】4 4为已有的公式套用名称为已有的公式套用名称【插入】菜单的【名称】子菜单中执行【应用】【插入】菜单的【名称】子菜单中执行【应用】（1）第一个字符必须是字母、下划线、汉字（2）不能与单元格引用相同Eg：P1 Q2 Z5等等（3）最长255字符 (4)不区分大小写为单元格或单元格区域命名使用名称建立公式2.1.7 特殊的计算公式1日期的运算直接输入计算公式直接输入计算公式通过引用

4、单元格中的日期数据进行计算通过引用单元格中的日期数据进行计算2数值转换当输入的数值类型与运算符所需要的类型不同时，当输入的数值类型与运算符所需要的类型不同时，Excel 2003Excel 2003可以自动对其进行转换可以自动对其进行转换 2.1.8 显示与隐藏公式1使用菜单命令【工具】【工具】【选项】【选项】【视图】【视图】2利用快捷键切换按【按【Ctrl+Ctrl+】组合键】组合键 2.1.9 公式审核及出错检查1 1追踪引用单元格追踪引用单元格【工具】【工具】【公式审核】【公式审核】【追踪引用单元格】【追踪引用单元格】【工具】【工具】【公式审核】【公式审核】【取消所有追踪箭头】【取消所有

5、追踪箭头】2 2追踪从属单元格追踪从属单元格【工具】【工具】【公式审核】【公式审核】【追踪从属单元格】【追踪从属单元格】【工具】【工具】【公式审核】【公式审核】【取消所有追踪箭头】【取消所有追踪箭头】3 3公式审核工具栏公式审核工具栏【工具】【工具】【公式审核】【公式审核】【显示【显示“公式审核公式审核”工具栏】工具栏】4常见的公式错误信息错误错误信息信息错误错误原因原因#DIV/0!#DIV/0!公式的除数公式的除数为为零零#N/A#N/A内部函数或自定内部函数或自定义义工作表函数中缺少一个或多个工作表函数中缺少一个或多个参数参数#NAME?#NAME?不能不能识别识别的名字。的名字。#NU

6、LL!#NULL!指定的两个区域不相交。指定的两个区域不相交。#NUM!#NUM!在需要数字参数的函数中使用了不能接受的函数在需要数字参数的函数中使用了不能接受的函数#REF!#REF!公式中引用了无效的公式中引用了无效的单单元格。元格。#VAULE!#VAULE!参数或操作数的参数或操作数的类类型有型有错误错误。#!#!公式公式产产生的生的结结果太果太长长，单单元格容元格容纳纳不下。不下。2.2 使用函数2.2.1 函数的语法格式2.2.2 函数的调用方法2.2.3 使用Excel的帮助功能理解函数2.2.4 常用函数介绍2.2.1 函数的语法格式函数名（参数函数名（参数1 1，参数，参数2

7、 2，参数，参数n n）。）。在使用函数时，应注意以下几个问题：在使用函数时，应注意以下几个问题：（1 1）函数名与其后的括号）函数名与其后的括号“（”之间不能有空格。之间不能有空格。（2 2）当有多个参数时，参数之间要用逗号）当有多个参数时，参数之间要用逗号“，”分隔。分隔。（3 3）参数部分总长度不能超过）参数部分总长度不能超过10241024个字符。个字符。（4 4）参数可以是数值、文本、逻辑值、单元格地址或单元）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。格区域地址，也可以是各种表达式或函数。（5 5）函数中的逗号）函数中的逗号“，”、引号、引号

8、“”“”等都是半角字符，而非全等都是半角字符，而非全角的中文字符。角的中文字符。2.2.2 函数的调用方法1单一函数的调用方法直接输入函数直接输入函数利用菜单命令或工具按钮调用函数利用菜单命令或工具按钮调用函数2嵌套函数的输入方法2.2.3 使用Excel的帮助功能理解函数1利用“帮助”任务窗格【帮助】【帮助】【Microsoft Excel Microsoft Excel 帮助】或按【帮助】或按【F1F1】键】键在【在【Excel Excel 帮助】任务窗格中的帮助】任务窗格中的“搜索搜索”框中输入函框中输入函数名称进行搜索数名称进行搜索2利用函数对话框上的帮助提示【有关该函数的帮助】按钮【

9、有关该函数的帮助】按钮 2.2.4 常用函数介绍1求和函数（1）无条件求和SUM函数该函数的功能是计算所选取的单元格区域中所有该函数的功能是计算所选取的单元格区域中所有数值的和。数值的和。2.2.4 常用函数介绍（2）条件求和SUMIF函数SUMIFSUMIF函数的功能是根据指定条件对若干单元格函数的功能是根据指定条件对若干单元格求和。求和。【例例2-42-4】（3）SUMPRODUCT函数功能是在给定的几组数组中，将数组间对应的元功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。素相乘，并返回乘积之和。=sumproduct(array1,array2,)=sumproduc

10、t(array1,array2,)Eg.=sumproduct(1 2 3 4),(5 6 7 8)Eg.=sumproduct(1 2 3 4),(5 6 7 8)=1*5+2*6+3*7+4*8 =1*5+2*6+3*7+4*8 =5+12+21+32 =5+12+21+32 =70 =702.2.4 常用函数介绍2平均值函数 AVERGAE函数功能是计算给定参数的算术平均值。功能是计算给定参数的算术平均值。3最小值与最大值函数MIN/MAX函数MINMIN函数的功能是给定参数表中的最小值函数的功能是给定参数表中的最小值MAXMAX函数的功能是给定参数表中的最大值。函数的功能是给定参数表中

11、的最大值。2.2.4 常用函数介绍4数学函数（1）ABS函数：返回数字的绝对值。（2）SQRT函数：计算一个正数的正平方根。（3）ROUND函数：返回某个数字按指定位数舍入后的数字。2.2.4 常用函数介绍5条件函数IFIF函数也称条件函数，它根据参数条件的真假，函数也称条件函数，它根据参数条件的真假，返回不同的结果。返回不同的结果。【例例2-52-5】6计数函数COUNTCOUNT函数：计算给定区域内数值型参数的数目函数：计算给定区域内数值型参数的数目 COUNTIFCOUNTIF函数：计算给定区域内满足特定条件的函数：计算给定区域内满足特定条件的单元格的数目。单元格的数目。IF函数的应用【

12、例2-5】2.2.4 常用函数介绍7逻辑函数ANDAND函数：表示逻辑与函数：表示逻辑与OROR函数：表示逻辑或函数：表示逻辑或NOTNOT函数：功能是对参数的逻辑值求反函数：功能是对参数的逻辑值求反这三个函数一般与IF函数结合使用【例2-6】IF及AND函数的应用【例2-6】2.2.4 常用函数介绍8 8查找函数查找函数 （1 1）LOOKUPLOOKUP函数：返回向量（单行区域或单列区域）函数：返回向量（单行区域或单列区域）或数组中的数值。或数组中的数值。（2 2）VLOOKUPVLOOKUP函数：在表格或数值数组的首列查找函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当

13、前行中指定指定的数值，并由此返回表格或数组当前行中指定列处的数值。列处的数值。（3 3）HLOOKUPHLOOKUP函数：从表格或数值数组的首行查找函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定指定的数值，并由此返回表格或数组当前列中指定行处的数值。行处的数值。（4 4）MATCHMATCH函数：返回在指定方式下与指定数值匹函数：返回在指定方式下与指定数值匹配的数组中元素的相应位置。配的数组中元素的相应位置。VLOOKUP函数的功能2.2.4 常用函数介绍9引用函数（1 1）INDEXINDEX函数：返回表格或区域中的数值或对函数：返回表格或区域中的数值或对数值的

14、引用。数值的引用。（2 2）ADDRESSADDRESS函数：按照给定的行号和列标，建函数：按照给定的行号和列标，建立文本类型的单元格地址。立文本类型的单元格地址。（3 3）INDIRECTINDIRECT函数：返回由文字串指定的引用。函数：返回由文字串指定的引用。2.2.4 常用函数介绍10矩阵函数（1）TRANSPOSE函数：求矩阵的转置矩阵。（2）MINVERSE函数：返回矩阵的逆矩阵。（3）MMULT函数：返回两数组的矩阵乘积。【例2-7】TRANSPOSE函数的应用【例2-7】2.3 创建图表2.3.1 图表类型2.3.2 图表的建立2.3.3 图表的编辑2.3.4 动态图表的建立2

15、.3.1 图表类型Excel 2003提供了14种标准图表类型，如柱形图、条形图、折线图、饼图、XY散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图、棱锥图，每种图表类型又都有几种不同的子类型。此外，Excel还提供了约20种自定义图表类型，用户可根据不同的需要选用适当的图表类型。2.3.2 图表的建立单击工具栏上的【图表向导】按钮单击工具栏上的【图表向导】按钮或单击【插入】菜单，执行【图表】命令或单击【插入】菜单，执行【图表】命令【例例2-8】根据下列数据建立柱形销售图表根据下列数据建立柱形销售图表根据下列数据建立柱形销售图表根据下列数据建立柱形销售图表 柱形销售图表柱

16、形销售图表2.3.3 图表的编辑1修改坐标轴格式2修改标题和图例的字体和字号3改变图表的大小4移动或复制图表5添加数据标志6改变图表颜色、图案、边框2.3.4 动态图表的建立【例2-9】根据给出的数据建立各部门的动态图表2.3.4 动态图表的建立使用的函数INDIRECT:INDIRECT:返回参数所确定的单元格内的数据返回参数所确定的单元格内的数据ADDRESS:ADDRESS:返回由行号和列标所确定的单元格返回由行号和列标所确定的单元格 CELL(row):CELL(row):返回当前光标所在的行号返回当前光标所在的行号 COLUMN:COLUMN:返回参数所在的列标返回参数所在的列标使用

17、动态图表按【按【F9F9】键（即对工作表数据重新计算）】键（即对工作表数据重新计算）2.3.4 动态图表的建立2.3.4 动态图表的建立2.4 数据管理2.4.1 建立和使用数据清单2.4.2 数据排序2.4.3 数据筛选2.4.4 数据的分类汇总2.4.5 数据透视表与数据透视图2.4.6 数据的合并计算 2.4.1 建立和使用数据清单1建立数据清单应注意的事项2建立数据清单的方法【数据】【数据】【记录单】【记录单】【新建】【新建】【例例210210】3使用记录单修改或删除数据4使用记录单查找数据建立数据清单【例210】使用记录单查找数据2.4.2 数据排序1数据排序的规则2 数据排序的步骤

18、【数据】【数据】【排序】【排序】选择关键字选择关键字 2.4.2 数据排序3自定义排序【工具】【工具】【选项】【选项】【自定义序列】【自定义序列】【添加】【添加】【数据】【数据】【排序】【排序】选择关键字选择关键字 【选项】【选项】在【排序选项】对话框中选择建立的在【排序选项】对话框中选择建立的自定义序列自定义序列 自定义排序结果2.4.3 数据筛选1自动筛选【数据】【数据】【筛选】【筛选】【自动筛选】【自动筛选】自动筛选结果若要恢复所有的记录，单击标题右边的下拉列表若要恢复所有的记录，单击标题右边的下拉列表中的中的“全部全部”项项再次执行【数据】再次执行【数据】【筛选】【筛选】【自动筛选】命

19、【自动筛选】命令，可取消【自动筛选】状态令，可取消【自动筛选】状态2.4.3 数据筛选2自定义筛选方式在标题列的下拉列表中选择在标题列的下拉列表中选择“自定义自定义”命令命令 自定义筛选结果2.4.3 数据筛选3一般条件下的高级筛选建立一个条件区域建立一个条件区域 【数据】【数据】【筛选】【筛选】【高级筛选】【高级筛选】一般条件下的高级筛选【数据】【数据】【筛选】【筛选】【全部显示】可显示全部【全部显示】可显示全部原始数据原始数据 2.4.3 数据筛选4计算条件下的高级筛选在数据清单以外的任一空单元格内输入计算公式在数据清单以外的任一空单元格内输入计算公式建立一个条件区域建立一个条件区域 【数

20、据】【数据】【筛选】【筛选】【高级筛选】【高级筛选】2.4.4 数据的分类汇总1自动分类汇总【数据】菜单中选择【排序】【数据】菜单中选择【排序】2.4.4 数据的分类汇总【数据】菜单，选择【分类汇总】【数据】菜单，选择【分类汇总】选择【分类字段】和【汇总方式】选择【分类字段】和【汇总方式】按地区分类汇总的结果 2 2撤消分类汇总撤消分类汇总【数据】【数据】【分类汇总】【分类汇总】【全部删除】【全部删除】2.4.5 数据透视表与数据透视图1建立数据透视表【数据】【数据】【数据透视表和数据透视图】【数据透视表和数据透视图】建立数据透视表建立数据透视表各销售人员销售商品的数据透视表 2.4.5 数据

21、透视表与数据透视图2使用数据透视表 在下拉列表中选择要查看的项目在下拉列表中选择要查看的项目2.4.5 数据透视表与数据透视图3建立数据透视图在数据透视表中选取任意一个单元格，单击鼠标在数据透视表中选取任意一个单元格，单击鼠标右键，在系统弹出的快捷菜单中选择【数据透视右键，在系统弹出的快捷菜单中选择【数据透视图】命令，则系统自动创建一张数据透视图图】命令，则系统自动创建一张数据透视图 建立数据透视图单击单击“数据数据”右边的倒三角形，在下拉列表中选择右边的倒三角形，在下拉列表中选择要查看的项目要查看的项目完成的数据透视图2.4.5 数据透视表与数据透视图4.4.更新数据透视表更新数据透视表 当

22、数据清单中的数据发生变化时，单击数据透视表中的当数据清单中的数据发生变化时，单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【刷新任一单元格，单击鼠标右键，在快捷菜单中选择【刷新数据】命令；也可在数据透视表的【数据】菜单中选择数据】命令；也可在数据透视表的【数据】菜单中选择【刷新数据】命令。【刷新数据】命令。5 5编辑数据透视表编辑数据透视表 例如，要修改数据透视表的布局，具体步骤是：例如，要修改数据透视表的布局，具体步骤是：（1 1）选取数据透视表中的任意单元格。）选取数据透视表中的任意单元格。（2 2）单击鼠标右键，在弹出的快捷菜单中选择【数据透）单击鼠标右键，在弹出的快捷菜单

23、中选择【数据透视表向导】命令，打开【数据透视表和数据透视图向导视表向导】命令，打开【数据透视表和数据透视图向导布局】对话框，再进行相应的修改即可。布局】对话框，再进行相应的修改即可。2.4.5 数据透视表与数据透视图6删除数据透视表删除数据透视表所在的工作表删除数据透视表所在的工作表在快捷菜单中选择【删除】命令在快捷菜单中选择【删除】命令仅删除数据透视表仅删除数据透视表单击单击“数据透视表数据透视表”工具栏中的工具栏中的“数据透视表数据透视表”下拉式箭下拉式箭头，从弹出的菜单中单击【选定】子菜单的【整张表头，从弹出的菜单中单击【选定】子菜单的【整张表格】命令。再单击【编辑】格】命令。再单击【编

24、辑】【删除】命令【删除】命令2.4.6 数据的合并计算【例2-11】2.4.6 数据的合并计算【数据】菜单中选择【合并计算】2.5 数据分析工具的应用2.5.1 模拟运算表2.5.2 单变量求解2.5.3 规划求解2.5.4 方案分析2.5.5 数据分析工具库2.5.1 模拟运算表1单变量模拟运算表在【数据】菜单中单击【模拟运算表】命令在【数据】菜单中单击【模拟运算表】命令 【例例2-122-12】完成的单变量模拟运算表2.5.1 模拟运算表2双变量模拟运算表【例例2-132-13】完成的双变量模拟运算表2.5.2 单变量求解在【工具】菜单中单击【单变量求解】命令【例2-14】单变量求解结果2

25、.5.3 规划求解【例2-15】2.5.3 规划求解【工具】【加载宏】【规划求解】【工具】【规划求解】添加约束条件设置完成的【规划求解参数】对话框 规划求解选项 规划求解的结果 附加1MIN Z=-3XMIN Z=-3X1 1+X+X2 2+X+X3 3X X1 1-2X-2X2 2+X+X3 31111-4X-4X1 1+X+X2 2+2X+2X3 333-2X-2X1 1+X+X3=3=1 1X X1 1,X,X2 2,X,X3 300生产生产A A获利获利2 2元元生产生产B B获利获利3 3元元产品产品A A产品产品B B最大提供量最大提供量设备设备1 12 28 8台时台时材料材料A

26、 A4 40 01616公斤公斤材料材料B B0 04 41212公斤公斤附加2某人现有资金某人现有资金10W10W在今后五年内考虑如下项目投资：在今后五年内考虑如下项目投资：项目项目A A：从第一年到第四年年初需要投资，并于：从第一年到第四年年初需要投资，并于 次次年末收回本利年末收回本利115%115%项目项目B B：第三年初需投资，第五年末收回本例：第三年初需投资，第五年末收回本例125%125%，规定最大投资额不超过，规定最大投资额不超过4W4W项目项目C C：第二年初需要投资，到第五年末收回本利：第二年初需要投资，到第五年末收回本利140%140%，规定最大投资额不超过，规定最大投资

27、额不超过3W3W项目项目D D：五年内每年初可购买公债，当年末归还，：五年内每年初可购买公债，当年末归还，并加利息并加利息6%6%问，如何投资使得五年后的投资本利总额最大？问，如何投资使得五年后的投资本利总额最大？利用规划求解工具求解方程组【例2-16】方程组的求解结果 2.5.4 方案分析【例2-17】2.5.4 方案分析1建立方案设计方案分析表格式，并对有关单元格进行命名设计方案分析表格式，并对有关单元格进行命名 2.5.4 方案分析1建立方案【工具】【工具】【方案】【方案】【方案管理器】【添加】【方案管理器】【添加】对【添加方案】对话框进行设置对【添加方案】对话框进行设置 2.5.4 方

28、案分析设置完成的【方案管理器】对话框 2.5.4 方案分析2显示方案【工具】【工具】【方案】【方案】【方案管理器】【方案管理器】【显示】【显示】2.5.4 方案分析3修改、删除或增加方案在【方案管理器】对话框中选取需要修改的方案，在【方案管理器】对话框中选取需要修改的方案，单击【编辑】按钮单击【编辑】按钮在【编辑方案】对话框中单击【确定】按钮在【在【编辑方案】对话框中单击【确定】按钮在【方案变量值】对话框中进行相应的修改方案变量值】对话框中进行相应的修改在【方案管理器】对话框中选取需要删除的方案，在【方案管理器】对话框中选取需要删除的方案，单击【删除】按钮，可删除选中的方案。单击【删除】按钮，

29、可删除选中的方案。2.5.4 方案分析4 建立方案摘要报告在【方案管理器】对话框中单击【摘要】按钮在【方案管理器】对话框中单击【摘要】按钮在【方案摘要】对话框中进行相应的设置在【方案摘要】对话框中进行相应的设置2.5.5 数据分析工具库Excel还提供了一组称为“分析工具库”的数据分析工具，其中包括方差分析、相关系数分析、协方差分析、描述统计分析、指数平滑分析、F-检验、傅里叶分析、直方图分析、移动平均分析、随机数发生器、排位与百分比排位、回归分析、抽样分析、t-检验、z-检验等。利用这些数据分析工具，可为解决实际财务管理工作中的很多问题提供极好的帮助。2.6 宏与VBA的初步应用2.6.1

30、建立自定义函数【例2-18】根据如下所示的不允许缺货且陆续均衡供货和消耗情况下的经济订货批量计算公式建立一个自定义函数。经济订货批量的计算公式为2.6.1 建立自定义函数【工具】【工具】【宏】【宏】【Visual BasicVisual Basic编辑器】编辑器】单击单击Visual BasicVisual Basic编辑器窗口的【插入】【模块】编辑器窗口的【插入】【模块】在模块在模块1 1窗口中单击【插入】窗口中单击【插入】【过程】【过程】2.6 宏与VBA的初步应用在【添加过程】对话框中的【名称】栏中输入自定义函数的名称，在【类型】区域选择“函数”2.6 宏与VBA的初步应用在编辑过程页面 输入自定义函数的过程代码 2.6.2 使用自定义函数单击工具栏上【插入函数】按钮，或在【插入】菜单中单击【函数】命令在【插入函数】对话框的【或选择类别】栏中选择“用户定义”，在【选择函数】栏中选择自定义的函数。2.6.2 使用自定义函数2.6.2 使用自定义函数此此课件下件下载可自行可自行编辑修改，修改，仅供参考！供参考！感感谢您的支持，我您的支持，我们努力做得更好！努力做得更好！谢谢!