运用EXCEL分析与处理财务数据基础知识

本讲重点掌握内容2.1运用公式2.2使用函数2.3数据管理2.4数据分析工具的应用,第2讲运用Excel分析与处理财务数据基础知识,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输入公式P17,在单元格中输入公式运算符类型在编辑栏中输入公式输入公式的方法,2.1.2运算符及其优先级P18,2.1.3编辑公式,1修改公式单击公式编辑栏,按【F2】功能键双击单元格2复制或移动公式利用工具栏上的复制、剪切、粘贴按钮利用【Ctrl+C】、【Ctrl+X】、【Ctrl+V】组合键操作利用鼠标拖动填充柄复制复制公式的过程中需要注意公式中对单元格具有不同的引用方式,2.1.4引用单元格P4,1相对引用2绝对引用3混合引用4三维引用5不同引用方式之间的切换按【F4】键,2.1.5使用数组公式,1输入数组公式按【Ctrl+Shift+Enter】组合键2编辑数组公式3删除数组公式4数组公式的应用【例2-1】【例2-2】【例2-3】,数组就是单元的集合或是一组处理的值集合,2.1.6使用名称建立公式,1为单元格或单元格区域命名的方法利用名称框定义名称利用菜单命令定义名称【插入】【名称】【定义】2单元格名称的命名规定名称不能包含任何空格可以使用任何字符和数字的组合，但是名称的首字符必须是字母、下划线或汉字名称不能与单元格引用相同名称长度限制在255个字符以内名称不区分大小写,3使用名称建立公式直接输入名称公式利用粘贴名称的方法输入公式【插入】【名称】【粘贴】4为已有的公式套用名称【插入】菜单的【名称】子菜单中执行【应用】,2.1.7特殊的计算公式,1日期的运算直接输入计算公式通过引用单元格中的日期数据进行计算2数值转换当输入的数值类型与运算符所需要的类型不同时，Excel2003可以自动对其进行转换,2.1.8显示与隐藏公式,1使用菜单命令【工具】【选项】【视图】2利用快捷键切换按【Ctrl+】组合键,2.1.9公式审核及出错检查,1追踪引用单元格【工具】【公式审核】【追踪引用单元格】【工具】【公式审核】【取消所有追踪箭头】2追踪从属单元格【工具】【公式审核】【追踪从属单元格】【工具】【公式审核】【取消所有追踪箭头】3公式审核工具栏【工具】【公式审核】【显示“公式审核”工具栏】,4常见的公式错误信息,2.2使用函数P26,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）函数中的逗号“，”、引号“”等都是半角字符，而非全角的中文字符。(6)没有参数的函数，括号不能省略如PI()，RAND(),NOW(),2.2.2函数的调用方法,1单一函数的调用方法直接输入函数利用菜单命令或工具按钮调用函数2嵌套函数的输入方法,2.2.3使用Excel的帮助功能理解函数,1利用“帮助”任务窗格【帮助】【MicrosoftExcel帮助】或按【F1】键在【Excel帮助】任务窗格中的“搜索”框中输入函数名称进行搜索2利用函数对话框上的帮助提示【有关该函数的帮助】按钮,2.2.4常用函数介绍,1求和函数（1）无条件求和SUM函数该函数的功能是计算所选取的单元格区域中所有数值的和。（2）条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和。SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。Sum_range是需要求和的实际单元格。说明只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。如果忽略了sum_range，则对区域中的单元格求和。P25,（3）SUMPRODUCT函数功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法：SUMPRODUCT(array1,array2,array3,.)Array1,array2,array3,.为2到30个数组，其相应元素需要进行相乘并求和。说明：数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。函数SUMPRODUCT将非数值型的数组元素作为0处理。,2.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函数P30功能：它根据参数条件的真假，返回不同的结果。语法：IF（logical-test，value-if-true，value-if-false）。参数：logical-test是结果为true（真）或false（假）的数值或表达式；value_if_true是logical_test为true时函数的返回值，如果logical_test为ture并且省略了value_if_true，则返回true。而且value_if_true可以是一个表达式；value_if_false是logical_test为false时函数的返回值。说明：如果logical_test为false并且省略value_if_false，则返回false。Value_if_false也可以是一个表达式。如：=IF(F260,“合格”,“不合格)IF函数可以嵌套使用,最多为7层,如：=IF(F2=90,优,IF(F2=80,良,IF(F2=60,中,差)P23,6计数函数COUNT函数：计算给定区域内数值型参数的数目COUNTIF函数：计算给定区域内满足特定条件的单元格的数目。,2.2.4常用函数介绍,2.2.4常用函数介绍,7逻辑函数AND函数：表示逻辑与OR函数：表示逻辑或NOT函数：功能是对参数的逻辑值求反这三个函数一般与IF函数结合使用【例2-6】,2.2.4常用函数介绍,8查找函数（1）LOOKUP函数：返回向量（单行区域或单列区域）或数组中的数值。（2）VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。（3）HLOOKUP函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。（4）MATCH函数：返回在指定方式下与指定数值匹配的数组中元素的相应位置。,LOOKUP函数语法：lookup(lookup_value,lookup_vector,result_vector)功能：从单行或单列区域或者从一个数组返回值，有两种形式。向量LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要查询的值列表较大或者值可能会随时间而改变时，使用该向量形式。数组LOOKUP的数组形式在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值。当要查询的值列表较小或者值在一段时间内保持不变时，使用该数组形式。,2.2.4.8查找函数,2.2.4.8查找函数,Lookup_value：LOOKUP在第一个向量中搜索的值。Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。Lookup_vector：只包含一行或一列的区域。lookup_vector中的值可以是文本、数字或逻辑值。注意：lookup_vector中的值必须以升序顺序放置：.,-2,-1,0,1,2,.；A-Z；FALSE,TRUE。否则，LOOKUP可能无法提供正确的值。大写文本和小写文本是等同的。,2.2.4.8查找函数,VLOOKUP函数功能：在表格或数组的首列查找值，并由此返回表格或数组当前行中其他列的值。语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)说明：Range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：如果为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。,HLOOKUP函数功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。当比较值位于要查找的数据左边的一列时，请使用函数VLOOKUP。,2.2.4.8查找函数,MATCH函数功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。语法：MATCH(lookup_value,lookup_array,match_type)说明：Lookup_value为需要在数据表中查找的数值。Lookup_array可能包含所要查找的数值的连续单元格区域。Match_type为数字-1、0或1。Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value。,2.2.4.8查找函数,2.2.4常用函数介绍,9引用函数（1）INDEX函数：返回表格或区域中的数值或对数值的引用。（2）ADDRESS函数：按照给定的行号和列标，建立文本类型的单元格地址。（3）INDIRECT函数：返回由文字串指定的引用。,Index函数,Index函数功能：返回表或区域中的值或对值的引用语法：INDEX(array,row_num,column_num)说明：Array是一个单元格区域或数组常量。如果数组中只包含一行或一列，则可以不使用相应的row_num或column_num参数。如果数组中包含多个行和列，但只使用了row_num或column_num，INDEX将返回数组中整行或整列的数组。Row_num用于选择要从中返回值的数组中的行。如果省略row_num，则需要使用column_num。Column_num用于选择要从中返回值的数组中的列。如果省略column_num，则需要使用row_num。,2.3数据管理,2.3.1建立和使用数据清单2.3.2数据排序2.3.3数据筛选2.3.4数据的分类汇总2.3.5数据透视表与数据透视图2.3.6数据的合并计算,2.3.1建立和使用数据清单,1建立数据清单应注意的事项应避免在一个工作表上建立多个数据清单。在工作表的数据清单与其他数据间至少留出一个空白列和空白行避免在数据清单中放置空白行、列。避免将关键字数据放到数据清单的左右两侧2建立数据清单的方法【数据】【记录单】【新建】3使用记录单修改或删除数据4使用记录单查找数据,2.3.2数据排序,1数据排序的规则2数据排序的步骤【数据】【排序】选择关键字,2.3.2数据排序,3自定义排序【工具】【选项】【自定义序列】【添加】【数据】【排序】选择关键字【选项】在【排序选项】对话框中选择建立的自定义序列,2.3.3数据筛选,1自动筛选【数据】【筛选】【自动筛选】若要恢复所有的记录，单击标题右边的下拉列表中的“全部”项再次执行【数据】【筛选】【自动筛选】命令，可取消【自动筛选】状态2自定义筛选方式在标题列的下拉列表中选择“自定义”命令,2.3.3数据筛选,3一般条件下的高级筛选建立一个条件区域【数据】【筛选】【高级筛选】【数据】【筛选】【全部显示】可显示全部原始数据,2.3.3数据筛选,3计算条件下的高级筛选在数据清单以外的任一空单元格内输入计算公式建立一个条件区域【数据】【筛选】【高级筛选】,2.3.4数据的分类汇总,1自动分类汇总【数据】菜单中选择【排序】【数据】菜单，选择【分类汇总】选择【分类字段】和【汇总方式】2撤消分类汇总【数据】【分类汇总】【全部删除】,2.3.5数据透视表与数据透视图,1建立数据透视表P26【数据】【数据透视表和数据透视图】2使用数据透视表在下拉列表中选择要查看的项目,2.3.5数据透视表与数据透视图,3建立数据透视图在数据透视表中选取任意一个单元格，单击鼠标右键，在系统弹出的快捷菜单中选择【数据透视图】命令，则系统自动创建一张数据透视图单击“数据”右边的倒三角形，在下拉列表中选择要查看的项目,2.3.5数据透视表与数据透视图,4.更新数据透视表当数据清单中的数据发生变化时，单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【刷新数据】命令；也可在数据透视表的【数据】菜单中选择【刷新数据】命令。5编辑数据透视表例如，要修改数据透视表的布局，具体步骤是：（1）选取数据透视表中的任意单元格。（2）单击鼠标右键，在弹出的快捷菜单中选择【数据透视表向导】命令，打开【数据透视表和数据透视图向导布局】对话框，再进行相应的修改即可。,2.3.5数据透视表与数据透视图,6删除数据透视表删除数据透视表所在的工作表在快捷菜单中选择【删除】命令仅删除数据透视表单击“数据透视表”工具栏中的“数据透视表”下拉式箭头，从弹出的菜单中单击【选定】子菜单的【整张表格】命令。再单击【编辑】【删除】命令,2.3.6数据的合并计算,【例2-11】【数据】菜单中选择【合并计算】,2.4数据分析工具的应用,2.4.1模拟运算表2.4.2单变量求解2.4