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

本讲重点掌握内容2.1 2.1 运用公式运用公式2.2 2.2 使用函数使用函数2.3 2.3 数据管理数据管理2.4 2.4 数据分析工具的应用数据分析工具的应用第第2 2讲讲 运用运用ExcelExcel分析与处理财务数据基础知识分析与处理财务数据基础知识2.1 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.1 输入公式输入公式 P17P17在单元格中输入公式 运算符类型在编辑栏中输入公式 输入公式的方法 2.1.2 2.1.2 运算符及其优先级运算符及其优先级P18P18类别运算符运算符运算功能运算功能优先先级引用引用:区域运算符区域运算符，用于引用，用于引用单元格区域。例如：元格区域。例如：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逻辑=、=、等于、小于、大于、等于、小于、大于、小于等于、大于等于、不等于小于等于、大于等于、不等于112.1.3 2.1.3 编辑公式编辑公式1修改公式单击公式编辑栏单击公式编辑栏,按按【F2】功能键功能键 双击单元格双击单元格2复制或移动公式利用工具栏上的复制、剪切、粘贴按钮利用工具栏上的复制、剪切、粘贴按钮利用利用【Ctrl+C】、【Ctrl+X】、【Ctrl+V】组合键操作组合键操作利用鼠标拖动填充柄复制利用鼠标拖动填充柄复制复制公式的过程中需要注意公式中对单元格具有不同的引用方式 2.1.4 2.1.4 引用单元格引用单元格P4P41 1相对引用相对引用2 2绝对引用绝对引用3 3混合引用混合引用4 4三维引用三维引用5 5不同引用方式之间的切换不同引用方式之间的切换按按【F4F4】键键 2.1.5 2.1.5 使用数组公式使用数组公式1输入数组公式按【Ctrl+Shift+Enter】组合键 2编辑数组公式3删除数组公式4数组公式的应用【例2-1】【例2-2】【例2-3】数组就是单元的数组就是单元的集合或是一组处集合或是一组处理的值集合理的值集合 2.1.6 2.1.6 使用名称建立公式使用名称建立公式1为单元格或单元格区域命名的方法u利用名称框定义名称u利用菜单命令定义名称u【插入】【名称】【定义】2单元格名称的命名规定名称不能包含任何空格可以使用任何字符和数字的组合，但是名称的首字符必须是字母、下划线或汉字名称不能与单元格引用相同名称长度限制在255个字符以内名称不区分大小写3使用名称建立公式u直接输入名称公式u利用粘贴名称的方法输入公式u【插入】【名称】【粘贴】4为已有的公式套用名称【插入】菜单的【名称】子菜单中执行【应用】2.1.7 2.1.7 特殊的计算公式特殊的计算公式1日期的运算直接输入计算公式通过引用单元格中的日期数据进行计算2数值转换当输入的数值类型与运算符所需要的类型不同时，Excel 2003可以自动对其进行转换 2.1.8 2.1.8 显示与隐藏公式显示与隐藏公式1使用菜单命令【工具】【选项】【视图】2利用快捷键切换按【Ctrl+】组合键 2.1.9 2.1.9 公式审核及出错检查公式审核及出错检查1追踪引用单元格【工具】【公式审核】【追踪引用单元格】【工具】【公式审核】【取消所有追踪箭头】2追踪从属单元格【工具】【公式审核】【追踪从属单元格】【工具】【公式审核】【取消所有追踪箭头】3公式审核工具栏【工具】【公式审核】【显示“公式审核”工具栏】4 4常见的公式错误信息常见的公式错误信息错误信息信息错误原因原因#DIV/0!公式的除数公式的除数为零零#N/A内部函数或自定内部函数或自定义工作表函数中缺少一个工作表函数中缺少一个或多个参数或多个参数#NAME?不能不能识别的名字。的名字。#NULL!指定的两个区域不相交。指定的两个区域不相交。#NUM!在需要数字参数的函数中使用了不能接受在需要数字参数的函数中使用了不能接受的函数的函数#REF!公式中引用了无效的公式中引用了无效的单元格。元格。#VAULE!参数或操作数的参数或操作数的类型有型有错误。#!公式公式产生的生的结果太果太长，单元格容元格容纳不下。不下。2.2 2.2 使用函数使用函数P26P262.2.1 函数的语法格式2.2.2 函数的调用方法2.2.3 使用Excel的帮助功能理解函数2.2.4 常用函数介绍2.2.1 2.2.1 函数的语法格式函数的语法格式函数名（参数1，参数2，参数n）。u在使用函数时，应注意以下几个问题：（1 1）函数名与其后的括号）函数名与其后的括号“（”之间不能有空格。之间不能有空格。（2 2）当有多个参数时，参数之间要用逗号）当有多个参数时，参数之间要用逗号“，”分隔。分隔。（3 3）参数部分总长度不能超过）参数部分总长度不能超过10241024个字符。个字符。（4 4）参数可以是数值、文本、逻辑值、单元格地址或单元）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。格区域地址，也可以是各种表达式或函数。（5 5）函数中的逗号）函数中的逗号“，”、引号、引号“”等都是等都是半角字符半角字符，而非全角的中文字符。而非全角的中文字符。(6)(6)没有参数的函数，括号不能省略没有参数的函数，括号不能省略 如如PI()PI()，RAND(RAND(),NOW(),NOW()2.2.2 2.2.2 函数的调用方法函数的调用方法1单一函数的调用方法直接输入函数利用菜单命令或工具按钮调用函数2嵌套函数的输入方法2.2.3 2.2.3 使用使用ExcelExcel的帮助功能理解函数的帮助功能理解函数1利用“帮助”任务窗格【帮助】【Microsoft Excel 帮助】或按【F1】键在【Excel 帮助】任务窗格中的“搜索”框中输入函数名称进行搜索2利用函数对话框上的帮助提示【有关该函数的帮助】按钮 2.2.4 2.2.4 常用函数介绍常用函数介绍1求和函数（1 1）无条件求和）无条件求和SUMSUM函数函数该函数的功能是计算所选取的单元格区域中所有该函数的功能是计算所选取的单元格区域中所有数值数值的和。的和。（2 2）条件求和）条件求和SUMIFSUMIF函数函数SUMIFSUMIF函数的功能是根据指定条件对若干单元格求和。函数的功能是根据指定条件对若干单元格求和。SUMIF(range,criteria,sum_range)SUMIF(range,criteria,sum_range)Range Range 为用于条件判断的单元格区域。为用于条件判断的单元格区域。CriteriaCriteria 为确定哪些单元格将被相加求和的为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。条件，其形式可以为数字、表达式或文本。Sum_rangeSum_range 是需要求和的实际单元格。是需要求和的实际单元格。说明说明只有在区域中相应的单元格符合条件的情况下，只有在区域中相应的单元格符合条件的情况下，sum_range sum_range 中的单元格才求和。中的单元格才求和。如果忽略了如果忽略了 sum_rangesum_range，则对区域中的单元格求和。，则对区域中的单元格求和。P25P25（3）SUMPRODUCT函数功能是在给定的几组数组中，将数组间对应的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。元素相乘，并返回乘积之和。语法：语法：SUMPRODUCT(array1,array2,array3,SUMPRODUCT(array1,array2,array3,.).)Array1,array2,array3,.Array1,array2,array3,.为为 2 2 到到 30 30 个数组，其相应元素需要进行相乘并求和。个数组，其相应元素需要进行相乘并求和。说明说明：数组参数必须具有相同的维数，否则，：数组参数必须具有相同的维数，否则，函数函数 SUMPRODUCT SUMPRODUCT 将返回错误值将返回错误值#VALUE!#VALUE!。函数函数 SUMPRODUCT SUMPRODUCT 将非数值型的数组元素作为将非数值型的数组元素作为 0 0 处理。处理。2.2.4 2.2.4 常用函数介绍常用函数介绍2.2.4 2.2.4 常用函数介绍常用函数介绍2平均值函数 AVERGAE函数功能是计算给定参数的算术平均值。3最小值与最大值函数MIN/MAX函数MIN函数的功能是给定参数表中的最小值MAX函数的功能是给定参数表中的最大值。2.2.4 2.2.4 常用函数介绍常用函数介绍4数学函数（1）ABS函数：返回数字的绝对值。（2）SQRT函数：计算一个正数的正平方根。（3）ROUND函数：返回某个数字按指定位数舍入后的数字。2.2.4 2.2.4 常用函数介绍常用函数介绍5条件函数IF函数P 30功能功能：它根据参数条件的真假，返回不同的结果。：它根据参数条件的真假，返回不同的结果。语法：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(F260,“合格合格”,“不合格不合格)IFIF函数可以嵌套使用函数可以嵌套使用,最多为最多为7 7层层,如：如：=IF(F2=90,IF(F2=90,优优,IF(F2=80,IF(F2=80,良良,IF(F2=60,IF(F2=60,中中,差差)P236计数函数COUNT函数：计算给定区域内数值型参数的数目 COUNTIF函数：计算给定区域内满足特定条件的单元格的数目。2.2.4 常用函数介绍2.2.4 2.2.4 常用函数介绍常用函数介绍7逻辑函数AND函数：表示逻辑与OR函数：表示逻辑或NOT函数：功能是对参数的逻辑值求反这三个函数一般与IF函数结合使用【例2-6】2.2.4 2.2.4 常用函数介绍常用函数介绍8查找函数（1 1）LOOKUPLOOKUP函数：返回向量（单行区域或单列区域）函数：返回向量（单行区域或单列区域）或数组中的数值。或数组中的数值。（2 2）VLOOKUPVLOOKUP函数：在表格或数值数组的函数：在表格或数值数组的首列首列查找查找指定的数值，并由此返回表格或数组指定的数值，并由此返回表格或数组当前行中指定当前行中指定列处列处的数值。的数值。（3 3）HLOOKUPHLOOKUP函数：从表格或数值数组的函数：从表格或数值数组的首行首行查找查找指定的数值，并由此返回表格或数组指定的数值，并由此返回表格或数组当前列中指定当前列中指定行处行处的数值。的数值。（4 4）MATCHMATCH函数：返回在指定方式下与指定数值匹函数：返回在指定方式下与指定数值匹配的数组中元素的相应配的数组中元素的相应位置位置。LOOKUPLOOKUP函数函数语法：语法：lookup(lookup_value,lookup_vector,result_vectorlookup(lookup_value,lookup_vector,result_vector)功能：从单行或单列区域或者从一个数组返回值功能：从单行或单列区域或者从一个数组返回值 ，有，有两种形式。两种形式。向量向量LOOKUP LOOKUP 的向量形式在单行区域或单列区域的向量形式在单行区域或单列区域（称为（称为“向量向量”）中查找值，然后返回第二个单行区）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。域或单列区域中相同位置的值。当要查询的值列表较当要查询的值列表较大或者值可能会随时间而改变时，使用该向量形式。大或者值可能会随时间而改变时，使用该向量形式。数组数组LOOKUP LOOKUP 的数组形式在数组的第一行或第一列的数组形式在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值。列中相同位置的值。当要查询的值列表较小或者值在当要查询的值列表较小或者值在一段时间内保持不变时，使用该数组形式。一段时间内保持不变时，使用该数组形式。2.2.4.82.2.4.8查找函数查找函数2.2.4.8查找函数u Lookup_valueLookup_value：LOOKUP LOOKUP 在第一个向量中在第一个向量中搜索的值。搜索的值。Lookup_value Lookup_value 可以是数字、文本、可以是数字、文本、逻辑值、名称或对值的引用。逻辑值、名称或对值的引用。uLookup_vectorLookup_vector：只包含一行或一列的区域。只包含一行或一列的区域。lookup_vector lookup_vector 中的值可以是文本、数字或逻中的值可以是文本、数字或逻辑值。辑值。注意：注意：lookup_vector lookup_vector 中的值必须以升序顺序中的值必须以升序顺序放置：放置：.,-2,-1,0,1,2,.,-2,-1,0,1,2,.；A-ZA-Z；FALSE,TRUEFALSE,TRUE。否则，。否则，LOOKUP LOOKUP 可能无法提供正可能无法提供正确的值。大写文本和小写文本是等同的。确的值。大写文本和小写文本是等同的。2.2.4.8查找函数VLOOKUPVLOOKUP函数函数功能：在表格或数组的首列查找值，并由此返回表格功能：在表格或数组的首列查找值，并由此返回表格或数组当前行中或数组当前行中其他列其他列的值。的值。语法：语法：VLOOKUP(lookup_value,table_array,col_index_num,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)range_lookup)说明：说明：Range_lookupRange_lookup为逻辑值，指定希望为逻辑值，指定希望 VLOOKUP VLOOKUP 查找精确的匹配值还是近似匹配值：查找精确的匹配值还是近似匹配值：如果为如果为 TRUE TRUE 或省略，则返回精确匹配值或近似匹配或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回值。也就是说，如果找不到精确匹配值，则返回小于小于 lookup_value lookup_value 的最大数值。的最大数值。table_array table_array 第一列中的值必须以第一列中的值必须以升序排序升序排序；否则；否则 VLOOKUP VLOOKUP 可能无法返回正确的值。可能无法返回正确的值。HLOOKUP函数功能：功能：在表格或数值数组在表格或数值数组的首行查找指定的数的首行查找指定的数值，并由此返回表格或数组当前值，并由此返回表格或数组当前列列中中指定行处指定行处的数值。当比较值位于的数值。当比较值位于数据表的首行数据表的首行，并且要，并且要查找下面给定查找下面给定行中行中的数据时，请使用函数的数据时，请使用函数 HLOOKUPHLOOKUP。当比较值位于要查找的数据。当比较值位于要查找的数据左边的左边的一列一列时，请使用函数时，请使用函数 VLOOKUPVLOOKUP。2.2.4.8查找函数MATCH函数功能：返回在指定方式下与指定数值匹配的数组功能：返回在指定方式下与指定数值匹配的数组中元中元素的素的相应位置。相应位置。如果需要找出匹配元素的如果需要找出匹配元素的位置位置而不是而不是匹配元素本身，则应该使用匹配元素本身，则应该使用 MATCH MATCH 函数而不是函数而不是 LOOKUP LOOKUP 函数。函数。语法：语法：MATCH(lookup_value,lookup_array,match_type)MATCH(lookup_value,lookup_array,match_type)说明：说明：Lookup_valueLookup_value 为需要在数据表中查找的数为需要在数据表中查找的数值。值。Lookup_arrayLookup_array 可能包含所要查找的数值的连续单可能包含所要查找的数值的连续单元格区域。元格区域。Match_typeMatch_type 为数字为数字 -1-1、0 0 或或 1 1。Match-type Match-type 指指明明 Microsoft Excel Microsoft Excel 如何在如何在 lookup_array lookup_array 中查找中查找 lookup_valuelookup_value。2.2.4.8查找函数2.2.4 常用函数介绍9引用函数（1）INDEX函数：返回表格或区域中的数值或对数值的引用。（2）ADDRESS函数：按照给定的行号和列标，建立文本类型的单元格地址。（3）INDIRECT函数：返回由文字串指定的引用。Index函数函数Index函数功能：返回表或区域中的值或对值的引用功能：返回表或区域中的值或对值的引用 语法：语法：INDEX(array,row_num,column_num)INDEX(array,row_num,column_num)说明：说明：ArrayArray 是一个单元格区域或数组常量。是一个单元格区域或数组常量。如果数组中只包含一行或一列，则可以不使用相应的如果数组中只包含一行或一列，则可以不使用相应的 row_num row_num 或或 column_num column_num 参数。参数。如果数组中包含多个行和列，但只使用了如果数组中包含多个行和列，但只使用了 row_num row_num 或或 column_numcolumn_num，INDEX INDEX 将返回数组中整行或整列的数组。将返回数组中整行或整列的数组。Row_numRow_num 用于选择要从中返回值的数组中的行。用于选择要从中返回值的数组中的行。如果省略如果省略 row_numrow_num，则需要使用，则需要使用 column_numcolumn_num。Column_numColumn_num 用于选择要从中返回值的数组中的列。用于选择要从中返回值的数组中的列。如果省略如果省略 column_numcolumn_num，则需要使用，则需要使用 row_numrow_num。2.3 2.3 数据管理数据管理2.3.1 建立和使用数据清单2.3.2 数据排序2.3.3 数据筛选2.3.4 数据的分类汇总2.3.5 数据透视表与数据透视图2.3.6 数据的合并计算 2.3.1 2.3.1 建立和使用数据清单建立和使用数据清单1 1建立数据清单应注意的事项建立数据清单应注意的事项u 应避免在一个工作表上建立多个数据清单。u 在工作表的数据清单与其他数据间至少留出一个空白列和空白行 u 避免在数据清单中放置空白行、列。u 避免将关键字数据放到数据清单的左右两侧 2建立数据清单的方法【数据】【记录单】【新建】3使用记录单修改或删除数据4使用记录单查找数据2.3.2 2.3.2 数据排序数据排序1数据排序的规则2 数据排序的步骤【数据】【排序】选择关键字 2.3.2 2.3.2 数据排序数据排序3自定义排序【工具】【选项】【自定义序列】【添加】【数据】【排序】选择关键字【选项】在【排序选项】对话框中选择建立的自定义序列 2.3.3 2.3.3 数据筛选数据筛选1自动筛选【数据】【筛选】【自动筛选】若要恢复所有的记录，单击标题右边的下拉列表中的“全部”项再次执行【数据】【筛选】【自动筛选】命令，可取消【自动筛选】状态2自定义筛选方式在标题列的下拉列表中选择“自定义”命令 2.3.3 2.3.3 数据筛选数据筛选3一般条件下的高级筛选建立一个条件区域【数据】【筛选】【高级筛选】【数据】【筛选】【全部显示】可显示全部原始数据2.3.3 2.3.3 数据筛选数据筛选3计算条件下的高级筛选在数据清单以外的任一空单元格内输入计算公式建立一个条件区域【数据】【筛选】【高级筛选】2.3.4 数据的分类汇总1自动分类汇总【数据】菜单中选择【排序】【数据】菜单，选择【分类汇总】选择【分类字段】和【汇总方式】2撤消分类汇总【数据】【分类汇总】【全部删除】2.3.5 数据透视表与数据透视图数据透视表与数据透视图1建立数据透视表P26【数据】【数据透视表和数据透视图】2使用数据透视表 在下拉列表中选择要查看的项目2.3.5 2.3.5 数据透视表与数据透视图数据透视表与数据透视图3建立数据透视图在数据透视表中选取任意一个单元格，单击鼠标右键，在系统弹出的快捷菜单中选择【数据透视图】命令，则系统自动创建一张数据透视图 单击“数据”右边的倒三角形，在下拉列表中选择要查看的项目2.3.5 2.3.5 数据透视表与数据透视图数据透视表与数据透视图4.更新数据透视表当数据清单中的数据发生变化时，单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【刷新数据】命令；也可在数据透视表的【数据】菜单中选择【刷新数据】命令。5编辑数据透视表例如，要修改数据透视表的布局，具体步骤是：（1）选取数据透视表中的任意单元格。（2）单击鼠标右键，在弹出的快捷菜单中选择【数据透视表向导】命令，打开【数据透视表和数据透视图向导布局】对话框，再进行相应的修改即可。2.3.5 2.3.5 数据透视表与数据透视图数据透视表与数据透视图6删除数据透视表删除数据透视表所在的工作表在快捷菜单中选择【删除】命令仅删除数据透视表单击“数据透视表”工具栏中的“数据透视表”下拉式箭头，从弹出的菜单中单击【选定】子菜单的【整张表格】命令。再单击【编辑】【删除】命令2.3.6 数据的合并计算数据的合并计算【例2-11】【数据】菜单中选择【合并计算】2.4 数据分析工具的应用2.4.1 模拟运算表2.4.2 单变量求解2.4.3 规划求解2.4.4 方案分析2.4.5 数据分析工具库2.4.1 模拟运算表1单变量模拟运算表在【数据】菜单中单击【模拟运算表】命令【例2-12】2双变量模拟运算表【例2-13】2.4.2 单变量求解在【工具】菜单中单击【单变量求解】命令【例2-14】2.4.3 规划求解【工具】【加载宏】【规划求解】【工具】【规划求解】【例2-15】利用规划求解工具求解方程组 【例2-16】2.4.4 方案分析【例2-17】1建立方案设计方案分析表格式，并对有关单元格进行命名【工具】【方案】【方案管理器】【添加】对【添加方案】对话框进行设置 2显示方案【工具】【方案】【方案管理器】【显示】2.4.4 方案分析3修改、删除或增加方案在【方案管理器】对话框中选取需要修改的方案，单击【编辑】按钮在【编辑方案】对话框中单击【确定】按钮在【方案变量值】对话框中进行相应的修改在【方案管理器】对话框中选取需要删除的方案，单击【删除】按钮，可删除选中的方案。2.4.4 方案分析4 建立方案摘要报告在【方案管理器】对话框中单击【摘要】按钮在【方案摘要】对话框中进行相应的设置2.4.5 数据分析工具库Excel还提供了一组称为“分析工具库”的数据分析工具，其中包括方差分析、相关系数分析、协方差分析、描述统计分析、指数平滑分析、F-检验、傅里叶分析、直方图分析、移动平均分析、随机数发生器、排位与百分比排位、回归分析、抽样分析、t-检验、z-检验等。利用这些数据分析工具，可为解决实际财务管理工作中的很多问题提供极好的帮助。