运用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 引用单元格引用单元格P4P4 1 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!#DIV/0!公式的除数为零公式的除数为零#N/A#N/A内部函数或自定义工作表函数中缺少一个内部函数或自定义工作表函数中缺少一个或多个参数或多个参数#NAME?#NAME?不能识别的名字。不能识别的名字。#NULL!#NULL!指定的两个区域不相交。指定的两个区域不相交。#NUM!#NUM!在需要数字参数的函数中使用了不能接受在需要数字参数的函数中使用了不能接受的函数的函数#REF!#REF!公式中引用了无效的单元格。公式中引用了无效的单元格。#VAULE!#VAULE!参数或操作数的类型有错误。参数或操作数的类型有错误。#!#!公式产生的结果太长，单元格容纳不下。公式产生的结果太长，单元格容纳不下。2.2 2.2 使用函数使用函数P26P26 2.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,中中,差差)P23 6计数函数 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_vector)lookup(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-检验等。利用这些数据分析工具，可为解决实际财务管理工作中的很多问题提供极好的帮助。