EXCEL在财务中的应用

陈海烽Excel在财务中的高级应用《Excel在财务中的应用》主讲内容：一Excel在财务中的应用基础；二公式的编辑；三函数的运用；五应用EXCEL完成财务会计工作；六财务函数的应用；七应用财务函数完成财务管理工作；四数组公式和函数的嵌套；《Excel在财务中的应用》1、Excel在财务中的应用基础；1.1、代码化（数据标准化）1.2、日期、分数的输入1.3、引用的应用1.4、数据清单1.5、工作表的保护及安全打开《Excel在财务中的应用》1.1、代码化（数据标准化）以电子计算机为计算工具处理财务数据的基本概念。代码化——对系统原始数据按一定的规律进行编码的处理。数据处理代码化，缩短了数据项的长度，减少数据占用的存储空间，提高会计数据处理的速度和精度。方便计算机进行排序、分类、汇总等操作。输入方式：’+代码；如：现金科目代码输入方式：’1001《Excel在财务中的应用》1.2、日期、分数的输入日期在EXCEL中其实质为数字1900-1-1代表1分数与日期输入5/8在EXCEL中被识别为5月8日分数5/8的输入方式：0+空格+5/8《Excel在财务中的应用》1.3、引用的使用；引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。引用不同工作簿中的单元格称为链接。相对引用绝对引用（混合引用）链接名称《Excel在财务中的应用》1.3、引用的使用；相对引用--在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。随公式位置的改变，其引用的单元格也会相应发生变化。绝对引用--复制公式时Excel不调整引用，如$C$1。包括绝对引用单元格的公式，无论将其复制到什么位置，总是引用特定的单元格。链接--引用不同工作簿中的单元格。每次按F4键时，Excel会在以下组合间切换：绝对列与绝对行（例如，$A$1）；相对列与绝对行(A$1)；绝对列与相对行($A1)以及相对列与相对行(A1)，当切换到用户所需的引用时，按回车键确认即可。《Excel在财务中的应用》1.4动态引用OFFSET函数可以对动态数据进行查询，它以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数，其语法格式如下：OFFSET(reference,rows,cols,height,width)可以看出，该函数最多包含五个参数，后两个参数为可选项。其中：Reference作为偏移量参照系的引用区域，它必须为对单元格或相连单元格区域的引用；否则，函数OFFSET返回错误值#VALUE!。Rows为相对于偏移量参照系左上角单元格，上（下）偏移的行数。Cols为相对于偏移量参照系左上角单元格，左（右）偏移的列数。Heigh为高度，即所要返回的引用区域的行数。Width为宽度，即所要返回的引用区域的列数。《Excel在财务中的应用》1.4、数据清单在Excel中，数据库是作为一个数据清单来看待。我们可以理解数据清单就是数据库。在一个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段。Excel提供了一整套功能强大的命令集，使得管理数据清单（数据库）变得非常容易。我们可以完成下列工作：排序——在数据清单中，针对某些列的数据，我们可以用数据菜单中的排序命令来重新组织行的顺序。可以选择数据和选择排序次序，或建立和使用一个自定义排序次序。筛选——可以利用“数据”菜单中的“筛选”命令来对清单中的指定数据进行查找和其它工作。一个经筛选的清单仅显示那些包含了某一特定值或符合一组条件的行，暂时隐藏其它行。数据记录单——一个数据记录单提供了一个简单的方法，让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。自动分类汇总——利用“数据”菜单的“分类汇总”命令，在清单中插入分类汇总行，汇总你所选的任意数据。当你插入了分类汇总后，MicrosoftExcel自动为你在清单底部插入一个“总计”行。《Excel在财务中的应用》Excel数据库表格遵循的准则建立和使用Excel数据库表格时，用户必须遵循以下的基本准则：（1）一个数据库最好单独占据一个工作表，避免将多个数据库放到一个工作表上。（2）数据记录紧接在字段名下面，不要使用空白行将字段名和第一条记录数据分开。（3）避免在数据库中间放置空白行或空白列，任意两行的内容不能完全相同。（4）避免将关键数据放到数据库左右两侧，防止数据筛选时这些数据被隐藏。（5）字段名的字体、对齐方式、格式、边框等样式，应当与其他数据的格式相区别。（6）条件区域不要放在数据库的数据区域下方。因为用记录单添加数据时，Excel会在原数据库的下边添加数据记录，如果数据库的下边非空，就不能利用记录单添加数据。《Excel在财务中的应用》2、公式的编辑；公式的组成：运算单元、运算符、函数及参数、引用、常数、文本、时间等公式中元素的结构或次序决定了最终的计算结果。Excel中的公式遵循一个特定的语法或次序：最前面是等号（=），后面是参与计算的元素（运算数），这些参与计算的元素又是通过运算符隔开的。每个运算数可以是不改变的数值（常量数值）、单元格或引用单元格区域、标志、名称、或工作表函数。《Excel在财务中的应用》2.1公式中的运算符（1）算术运算符：完成基本的数学运算。（2）比较操作符：比较运算符用于比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是TRUE就是FALSE。（3）文本运算符：使用和号（&）可以将文本连接起来。（4）引用操作符：引用运算符的作用是确定在公式中需要参与运算的数据在工作表中所处的位置，可以使用三个运算符：冒号、逗号和空格。《Excel在财务中的应用》2.1公式中的运算符①：（冒号）：区域运算符，对两个单元格之间，包括两个单元格在内的所有单元格参与运算。②，（逗号）：联合运算符，可将多个引用合并为一个引用。③空格：交叉运算符，它是将同时属于两个引用的单元格区域进行引用，即两个单元格引用相重叠的区域。《Excel在财务中的应用》2.2运算符及其优先级类别运算符运算功能优先级引用:区域运算符，用于引用单元格区域。1引用,联合运算符，用于将多个引用合并。2引用空格交叉运算符，用于引用两个单元格区域的重叠部分。3算术()括号4算术-负号5算术%百分号6算术^乘方7算术*和/乘法和除法8算术+和－加法和减法9文本&文本连接10逻辑=、、=、=、等于、小于、大于、小于等于、大于等于、不等于11《Excel在财务中的应用》2.3公式审核及出错检查1．追踪引用单元格【工具】【公式审核】【追踪引用单元格】【工具】【公式审核】【取消所有追踪箭头】2．追踪从属单元格【工具】【公式审核】【追踪从属单元格】【工具】【公式审核】【取消所有追踪箭头】3．公式审核工具栏【工具】【公式审核】【显示“公式审核”工具栏】《Excel在财务中的应用》2.4常见的公式错误信息错误信息错误原因#DIV/0!公式的除数为零#N/A内部函数或自定义工作表函数中缺少一个或多个参数#NAME?不能识别的名字。#NULL!指定的两个区域不相交。#NUM!在需要数字参数的函数中使用了不能接受的函数#REF!公式中引用了无效的单元格。#VAULE!参数或操作数的类型有错误。####!公式产生的结果太长，单元格容纳不下。《Excel在财务中的应用》3、函数的运用3.1函数的语法格式3.2常用函数1．数学函数2．文本函数3．子字符串函数4．逻辑函数5．查找和引用函数6．财务函数《Excel在财务中的应用》3.1函数的语法格式函数名（参数1，参数2，…，参数n）。在使用函数时，应注意以下几个问题：（1）函数名与其后的括号“（”之间不能有空格。（2）当有多个参数时，参数之间要用逗号“，”分隔。（3）参数部分总长度不能超过1024个字符。（4）参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。（5）函数中的逗号“，”、引号“”等都是半角字符，而非全角的中文字符。《Excel在财务中的应用》3.2常用函数介绍1．求和函数（1）无条件求和SUM函数该函数的功能是计算所选取的单元格区域中所有数值的和。（2）条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和。（3）SUMPRODUCT函数功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。（3）DSUM函数功能是对数据库表格进行多条件汇总。《Excel在财务中的应用》《Excel在财务中的应用》3.2常用函数介绍2．平均值函数——AVERGAE函数功能是计算给定参数的算术平均值。3．最小值与最大值函数——MIN/MAX函数MIN函数的功能是给定参数表中的最小值MAX函数的功能是给定参数表中的最大值。《Excel在财务中的应用》3.2常用函数介绍4．数学函数（1）ABS函数：返回数字的绝对值。（2）SQRT函数：计算一个正数的正平方根。（3）ROUND函数：返回某个数字按指定位数舍入后的数字。《Excel在财务中的应用》3.2常用函数介绍5．条件函数IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。6．计数函数COUNT函数：计算给定区域内数值型参数的数目COUNTIF函数：计算给定区域内满足特定条件的单元格的数目。《Excel在财务中的应用》3.2常用函数介绍7．逻辑函数AND函数：表示逻辑与OR函数：表示逻辑或NOT函数：功能是对参数的逻辑值求反这三个函数一般与IF函数结合使用《Excel在财务中的应用》3.2常用函数介绍8．查找函数（1）LOOKUP函数：返回向量（单行区域或单列区域）或数组中的数值。LOOKUP(lookup_value,lookup_vector,result_vector)（2）VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)（3）HLOOKUP函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)《Excel在财务中的应用》3.2常用函数介绍9．引用函数INDEX函数：返回表格或区域中的数值或对数值的引用。INDEX(array,row_num,column_num)《Excel在财务中的应用》4、数组公式和数据透析；数组公式数据透析；《Excel在财务中的应用》4.1数组公式1.数组公式的输入数组公式与一般公式不同之处在于它被括在大括号（{}）中，其输入步骤如下：（1）选中一个单元格或者单元格区域。说明：如果数组公式只是返回一个结果，需要选择保存用来保存结果的那一个单元格；如果数组公式返回多个结果，则需要选中需要保留数组公式计算结果的单元格区域。（2）按照前面介绍的公式输入规则，输入公式的内容。（3）公式输完后，按【Ctrl+Shift+Enter】组合键结束操作。《Excel在财务中的应用》4.2数据透析数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在Excel中的实现工具是数据透视表。数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，Excel中还可以根据数据透视表制作不同格式的数据透