第2讲运用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类别运算符运算功能优先级引用:区域运算符，用于引用单元格区域。例如：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:B2B2:C3的结果是B2。3算术()括号4算术-负号5算术%百分号6算术^乘方7算术*和/乘法和除法8算术+和－加法和减法9文本&文本连接10逻辑=、、=、=、等于、小于、大于、小于等于、大于等于、不等于112.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．常见的公式错误信息错误信息错误原因#DIV/0!公式的除数为零#N/A内部函数或自定义工作表函数中缺少一个或多个参数#NAME?不能识别的名字。#NULL!指定的两个区域不相交。#NUM!在需要数字参数的函数中使用了不能接受的函数#REF!公式中引用了无效的单元格。#VAULE!参数或操作数的类型有错误。####!公式产生的结果太长，单元格容纳不下。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函数•功能：返回表或区域中的值或对值的