EXCEL第二章-运用Excel分析与处理财务数据基础知识课件

单击此处编辑母版标题样式,*,*,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2,运用,Excel,分析与处理财务数据基础知识,2.1,运用公式,2.2,使用函数,2.3,创建图表,2.4,数据管理,2.5,数据分析工具的应用,2.6,宏与,VBA,的初步应用,2.1,运用公式,2.1.1,输入公式,2.1.2,运算符及其优先级,2.1.3,编辑公式,2.1.4,引用单元格,2.1.5,使用数组公式,2.1.6,使用名称建立公式,2.1.7,特殊的计算公式,2.1.8,显示与隐藏公式,2.1.9,公式审核及出错检查,2.1.1,输入公式,在单元格中输入公式,在编辑栏中输入公式,2.1.2,运算符及其优先级,类别,运算符,运算功能,优先级,引用,:,区域运算符，用于引用单元格区域。例如：,A1:B2,，引用了,A1,、,A2,、,B1,、,B2,四个单元格；,B:B,引用整个,B,列；,3:3,引用整个第,3,行。,1,引用,联合运算符，用于将多个引用合并。例如：,公式,“,=SUM(A1:B2, B2:C3)”,的功能是将单元格,A1,、,A2,、,B1,、,B2,以及,B2,、,B3,、,C2,、,C3,中的数据加总，其中,B2,单元格被加总两次。,2,引用,空格,交叉运算符，用于引用两个单元格区域的重叠部分。,例如，,A1:B2 B2:C3,的结果是,B2,。,3,算术,( ),括号,4,算术,-,负号,5,算术,%,百分号,6,算术,乘方,7,算术,*,和,/,乘法和除法,8,算术,+,和,加法和减法,9,文本,&,文本连接,10,逻辑,=,、,=,、,等于、小于、大于、,小于等于、大于等于、不等于,11,2.1.3,编辑公式,1,修改公式,单击公式编辑栏,按,【F2】,功能键,2,复制或移动公式,利用工具栏上的复制、剪切、粘贴按钮,利用,【,Ctrl+C,】,、,【,Ctrl+X,】,、,【,Ctrl+V,】,组合键操作,利用鼠标拖动填充柄复制,复制公式的过程中需要注意公式中对单元格具有不同的引用方式,2.1.4,引用单元格,1,相对引用,2,绝对引用,3,混合引用,4,三维引用,5,不同引用方式之间的切换,按,【F4】,键,2.1.5,使用数组公式,1,输入数组公式,按,【,Ctrl+Shift+Enter,】,组合键,2,编辑数组公式,3,删除数组公式,4,数组公式的应用,【,例,2-1】 【,例,2-2】 【,例,2-3】,数组公式的应用,【,例,2-1】,数组公式的应用,【,例,2-2】,数组公式的应用,【,例,2-3】,2.1.6,使用名称建立公式,1,为单元格或单元格区域命名的方法,利用名称框定义名称,利用菜单命令定义名称,【,插入,】 【,名称,】 【,定义,】,2,单元格名称的命名规定,3,使用名称建立公式,直接输入名称公式,利用粘贴名称的方法输入公式,【,插入,】 【,名称,】 【,粘贴,】,4,为已有的公式套用名称,【,插入,】,菜单的,【,名称,】,子菜单中执行,【,应用,】,为单元格或单元格区域命名,使用名称建立公式,2.1.7,特殊的计算公式,1,日期的运算,直接输入计算公式,通过引用单元格中的日期数据进行计算,2,数值转换,当输入的数值类型与运算符所需要的类型不同时，,Excel 2003,可以自动对其进行转换,2.1.8,显示与隐藏公式,1,使用菜单命令,【,工具,】 【,选项,】 【,视图,】,2,利用快捷键切换,按,【Ctrl+】,组合键,2.1.9,公式审核及出错检查,1,追踪引用单元格,【,工具,】 【,公式审核,】 【,追踪引用单元格,】,【,工具,】 【,公式审核,】 【,取消所有追踪箭头,】,2,追踪从属单元格,【,工具,】 【,公式审核,】 【,追踪从属单元格,】,【,工具,】 【,公式审核,】 【,取消所有追踪箭头,】,3,公式审核工具栏,【,工具,】 【,公式审核,】 【,显示“公式审核”工具栏,】,4,常见的公式错误信息,错误信息,错误原因,#DIV/0!,公式的除数为零,#N/A,内部函数或自定义工作表函数中缺少一个或多个参数,#NAME?,不能识别的名字。,#NULL!,指定的两个区域不相交。,#NUM!,在需要数字参数的函数中使用了不能接受的函数,#REF!,公式中引用了无效的单元格。,#VAULE!,参数或操作数的类型有错误。,#!,公式产生的结果太长，单元格容纳不下。,2.2,使用函数,2.2.1,函数的语法格式,2.2.2,函数的调用方法,2.2.3,使用,Excel,的帮助功能理解函数,2.2.4,常用函数介绍,2.2.1,函数的语法格式,函数名（参数,1,，参数,2,，,，参数,n,）。,在使用函数时，应注意以下几个问题：,（,1,）函数名与其后的括号“（”之间不能有空格。,（,2,）当有多个参数时，参数之间要用逗号“，”分隔。,（,3,）参数部分总长度不能超过,1024,个字符。,（,4,）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。,（,5,）函数中的逗号“，”、引号“,”,等都是半角字符，而非全角的中文字符。,2.2.2,函数的调用方法,1,单一函数的调用方法,直接输入函数,利用菜单命令或工具按钮调用函数,2,嵌套函数的输入方法,2.2.3,使用,Excel,的帮助功能理解函数,1,利用“帮助”任务窗格,【,帮助,】 【Microsoft Excel,帮助,】,或按,【F1】,键,在,【Excel,帮助,】,任务窗格中的“搜索”框中输入函数名称进行搜索,2,利用函数对话框上的帮助提示,【,有关该函数的帮助,】,按钮,2.2.4,常用函数介绍,1,求和函数,（,1,）无条件求和,SUM,函数,该函数的功能是计算所选取的单元格区域中所有数值的和。,2.2.4,常用函数介绍,（,2,）条件求和,SUMIF,函数,SUMIF,函数的功能是根据指定条件对若干单元格求和。,【,例,2-4】,（,3,）,SUMPRODUCT,函数,功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。,SUMIF,函数的应用,【,例,2-4】,2.2.4,常用函数介绍,2,平均值函数, AVERGAE,函数,功能是计算给定参数的算术平均值。,3,最小值与最大值函数,MIN/ MAX,函数,MIN,函数的功能是给定参数表中的最小值,MAX,函数的功能是给定参数表中的最大值。,2.2.4,常用函数介绍,4,数学函数,（,1,）,ABS,函数：返回数字的绝对值。,（,2,）,SQRT,函数：计算一个正数的正平方根。,（,3,）,ROUND,函数：返回某个数字按指定位数舍入后的数字。,2.2.4,常用函数介绍,5,条件函数,IF,函数也称条件函数，它根据参数条件的真假，返回不同的结果。,【,例,2-5】,6,计数函数,COUNT,函数：计算给定区域内数值型参数的数目,COUNTIF,函数：计算给定区域内满足特定条件的单元格的数目。,IF,函数的应用,【,例,2-5】,2.2.4,常用函数介绍,7,逻辑函数,AND,函数：表示逻辑与,OR,函数：表示逻辑或,NOT,函数：功能是对参数的逻辑值求反,这三个函数一般与,IF,函数结合使用,【,例,2-6】,IF,及,AND,函数的应用,【,例,2-6】,2.2.4,常用函数介绍,8,查找函数,（,1,）,LOOKUP,函数：返回向量（单行区域或单列区域）或数组中的数值。,（,2,）,VLOOKUP,函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,（,3,）,HLOOKUP,函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。,VLOOKUP,函数的功能,2.2.4,常用函数介绍,9,引用函数,（,1,）,INDEX,函数：返回表格或区域中的数值或对数值的引用。,（,2,）,ADDRESS,函数：按照给定的行号和列标，建立文本类型的单元格地址。,（,3,）,INDIRECT,函数：返回由文字串指定的引用。,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.3.1,图表类型,Excel 2003,提供了,14,种标准图表类型，如柱形图、条形图、折线图、饼图、,XY,散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图、棱锥图，每种图表类型又都有几种不同的子类型。此外，,Excel,还提供了约,20,种自定义图表类型，用户可根据不同的需要选用适当的图表类型。,2.3.2,图表的建立,单击工具栏上的,【,图表向导,】,按钮,或单击,【,插入,】,菜单，执行,【,图表,】,命令,【,例,2-8】,根据下列数据建立柱形销售图表,柱形销售图表,2.3.3,图表的编辑,1,修改坐标轴格式,2,修改标题和图例的字体和字号,3,改变图表的大小,4,移动或复制图表,5,添加数据标志,6,改变图表颜色、图案、边框,2.3.4,动态图表的建立,【,例,2-9】,根据给出的数据建立各部门的动态图表,2.3.4,动态图表的建立,使用的函数,INDIRECT:,返回参数所确定的单元格内的数据,ADDRESS:,返回由行号和列标所确定的单元格,CELL (row) :,返回当前光标所在的行号,COLUMN :,返回参数所在的列标,使用动态图表,按,【F9】,键（即对工作表数据重新计算）,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,建立数据清单的方法,【,数据,】 【,记录单,】 【,新建,】,【,例,210】,3,使用记录单修改或删除数据,4,使用记录单查找数据,建立数据清单,【,例,210】,使用记录单查找数据,2.4.2,数据排序,1,数据排序的规则,2, 数据排序的步骤,【,数据,】 【,排序,】,选择关键字,2.4.2,数据排序,3,自定义排序,【,工具,】 【,选项,】 【,自定义序列,】 【,添加,】,【,数据,】 【,排序,】,选择关键字,【,选项,】,在,【,排序选项,】,对话框中选择建立的自定义序列,自定义排序结果,2.4.3,数据筛选,1,自动筛选,【,数据,】 【,筛选,】 【,自动筛选,】,自动筛选结果,若要恢复所有的记录，单击标题右边的下拉列表中的“全部”项,再次执行,【,数据,】 【,筛选,】 【,自动筛选,】,命令，可取消,【,自动筛选,】,状态,2.4.3,数据筛选,2,自定义筛选方式,在标题列的下拉列表中选择“自定义”命令,自定义筛选结果,2.4.3,数据筛选,3,一般条件下的高级筛选,建立一个条件区域,【,数据,】 【,筛选,】 【,高级筛选,】,一般条件下的高级筛选,【,数据,】 【,筛选,】 【,全部显示,】,可显示全部原始数据,2.4.3,数据筛选,4,计算条件下的高级筛选,在数据清单以外的任一空单元格内输入计算公式,建立一个条件区域,【,数据,】 【,筛选,】 【,高级筛选,】,2.4.4,数据的分类汇总,1,自动分类汇总,【,数据,】,菜单中选择,【,排序,】,2.4.4,数据的分类汇总,【,数据,】,菜单，选择,【,分类汇总,】,选择,【,分类字段,】,和,【,汇总方式,】,按地区分类汇总的结果,2,撤消分类汇总,【,数据,】 【,分类汇总,】 【,全部删除,】,2.4.5,数据透视表与数据透视图,1,建立数据透视表,【,数据,】 【,数据透视表和数据透视图,】,建立数据透视表,建立数据透视表,各销售人员销售商品的数据透视表,2.4.5,数据透视表与数据透视图,2,使用数据透视表,在下拉列表中选择要查看的项目,2.4.5,数据透视表与数据透视图,3,建立数据透视图,在数据透视表中选取任意一个单元格，单击鼠标右键，在系统弹出的快捷菜单中选择,【,数据透视图,】,命令，则系统自动创建一张数据透视图,建立数据透视图,单击“数据”右边的倒三角形，在下拉列表中选择要查看的项目,完成的数据透视图,2.4.5,数据透视表与数据透视图,4.,更新数据透视表,当数据清单中的数据发生变化时，单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择,【,刷新数据,】,命令；也可在数据透视表的,【,数据,】,菜单中选择,【,刷新数据,】,命令。,5,编辑数据透视表,例如，要修改数据透视表的布局，具体步骤是：,（,1,）选取数据透视表中的任意单元格。,（,2,）单击鼠标右键，在弹出的快捷菜单中选择,【,数据透视表向导,】,命令，打开,【,数据透视表和数据透视图向导,布局,】,对话框，再进行相应的修改即可。,2.4.5,数据透视表与数据透视图,6,删除数据透视表,删除数据透视表所在的工作表,在快捷菜单中选择,【,删除,】,命令,仅删除数据透视表,单击“数据透视表”工具栏中的“数据透视表”下拉式箭头，从弹出的菜单中单击,【,选定,】,子菜单的,【,整张表格,】,命令。再单击,【,编辑,】 【,删除,】,命令,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-12】,完成的单变量模拟运算表,2.5.1,模拟运算表,2,双变量模拟运算表,【,例,2-13】,完成的双变量模拟运算表,2.5.2,单变量求解,在,【,工具,】,菜单中单击,【,单变量求解,】,命令,【,例,2-14】,单变量求解结果,2.5.3,规划求解,【,例,2-15】,2.5.3,规划求解,【,工具,】 【,加载宏,】 【,规划求解,】,【,工具,】 【,规划求解,】,添加约束条件,设置完成的,【,规划求解参数,】,对话框,规划求解选项,规划求解的结果,利用规划求解工具求解方程组,【,例,2-16】,方程组的求解结果,2.5.4,方案分析,【,例,2-17】,2.5.4,方案分析,1,建立方案,设计方案分析表格式，并对有关单元格进行命名,2.5.4,方案分析,1,建立方案,【,工具,】 【,方案,】 【,方案管理器,】【,添加,】,对,【,添加方案,】,对话框进行设置,2.5.4,方案分析,设置完成的,【,方案管理器,】,对话框,2.5.4,方案分析,2,显示方案,【,工具,】 【,方案,】 【,方案管理器,】 【,显示,】,2.5.4,方案分析,3,修改、删除或增加方案,在,【,方案管理器,】,对话框中选取需要修改的方案，单击,【,编辑,】,按钮,在,【,编辑方案,】,对话框中单击,【,确定,】,按钮在,【,方案变量值,】,对话框中进行相应的修改,在,【,方案管理器,】,对话框中选取需要删除的方案，单击,【,删除,】,按钮，可删除选中的方案。,2.5.4,方案分析,4, 建立方案摘要报告,在,【,方案管理器,】,对话框中单击,【,摘要,】,按钮,在,【,方案摘要,】,对话框中进行相应的设置,2.5.5,数据分析工具库,Excel,还提供了一组称为“分析工具库”的数据分析工具，其中包括方差分析、相关系数分析、协方差分析、描述统计分析、指数平滑分析、,F-,检验、傅里叶分析、直方图分析、移动平均分析、随机数发生器、排位与百分比排位、回归分析、抽样分析、,t-,检验、,z-,检验等。利用这些数据分析工具，可为解决实际财务管理工作中的很多问题提供极好的帮助。,2.6,宏与,VBA,的初步应用,2.6.1,建立自定义函数,【,例,2-18】,根据如下所示的不允许缺货且陆续均衡供货和消耗情况下的经济订货批量计算公式建立一个自定义函数。,经济订货批量的计算公式为,2.6.1,建立自定义函数,【,工具,】 【,宏,】 【Visual Basic,编辑器,】,单击,Visual Basic,编辑器窗口的,【,插入,】【,模块,】,在模块,1,窗口中单击,【,插入,】 【,过程,】,2.6,宏与,VBA,的初步应用,在,【,添加过程,】,对话框中的,【,名称,】,栏中输入自定义函数的名称，在,【,类型,】,区域选择“函数”,2.6,宏与,VBA,的初步应用,在编辑过程页面 输入自定义函数的过程代码,2.6.2,使用自定义函数,单击工具栏上,【,插入函数,】,按钮，或在,【,插入,】,菜单中单击,【,函数,】,命令,在,【,插入函数,】,对话框的,【,或选择类别,】,栏中选择“用户定义”，在,【,选择函数,】,栏中选择自定义的函数。,2.6.2,使用自定义函数,2.6.2,使用自定义函数,