《EXCEL在财务工作中的应用.ppt》由会员分享，可在线阅读，更多相关《EXCEL在财务工作中的应用.ppt（56页珍藏版）》请在三一办公上搜索。
1、EXCEL在财务工作中的应用,EXCEL在财务工作中的应用概述EXCEL基本技术EXCEL应用案例,提纲,EXCEL在财务工作中的应用概述,对EXCEL软件的再认识 EXCEL不是一个简单的表格处理软件，而是一个数据管理平台 主要表现在：强大的数据计算能力 基本的逻辑判断能力 灵活多样的报表格式定义 强大的数据管理能力,EXCEL在财务工作中的应用概述,在财务工作中引入EXCEL的动因分析 弥补软件功能缺陷 完成数据共享与交换 满足个性化信息需求,EXCEL在财务工作中的应用概述,EXCEL在财务工作中应用的主要内容 在会计处理方面：账簿数据整理、数据检索与查询、统计报表编制 在财务管理方面：
2、财务报表分析、财务决策模型的构建 在审计方面：数据导出、数据正确性验证、审计工作底稿的编制,EXCEL应用的基本思路,选择实现工具,分析数据来源,确定求解算法,报告生成,制定解决方案,约束条件判定,确定求解问题,资料收集,需求分析,获取数据,算法定义,程序设计表格设计,方案实现,EXCEL基本技术,利用Excel获取会计数据,直接读取会计软件产生的数据交换文件 绝大多数数据库都符合ODBC（开放式数据库互连）标准，即数据库可以将数据库中的数据按照一个标准的格式转出，也可以按照标准的格式转入。会计软件都离不开数据库的支持，每个成熟的商品化会计软件都提供了与其他软件相连的数据接口，各种数据库都可以
3、转出和读取文本格式的数据,EXCEL基本技术,利用Excel获取会计数据（续）,利用 Excel 软件提供的“获取外部数据”获取数据 可以通过使用Query来检索诸如 Microsoft Access 或 Microsoft SQL Server 等关系数据库中的数据。除外部数据库外，还可以检索 Microsoft Excel 数据清单或文本文件中的数据，并且可以保持数据库中的数据与 Excel 中的数据同步。,EXCEL基本技术,Excel中的常用函数,财务函数 日期与时间函数 统计函数 查找与应用函数 文本函数,EXCEL基本技术,Excel中的数据管理功能,排序 筛选 分类汇总 数据透视
4、表,EXCEL基本技术,Excel中的高级应用,VBA程序 宏 数据安全性,EXCEL基本技术,数组公式及其应用,一、数组公式涵义 数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。数组参数：数组公式中使用的两组或多组数据，它可以是一个数据区域也可以是数组常量。注意：公式中的每个数组参数必须有相同的行与列。,二、数组公式的输入、编辑与删除（一）数组公式的输入 1、输入步骤：（1）选定单元格或单元区域；（2）输入数组公式；（3）同时按下“Ctrl+Shift+Enter”组合键。,2、输入数组常量的方法 数组常量：直接键入的数值数组。（1）选取单元区域（2）在公式编辑栏中输入数组公式（
5、3）同时按下“Ctrl+Shift+Enter”组合键注意：直接在公式输入数值，必须用“”括住；不同列的数值用逗号隔开；不同行的数值用分号隔开。,3、输入数组公式的方法（1）选取单元区域（2）在公式编辑栏中输入数组公式（3）同时按下“Ctrl+Shift+Enter”组合键,（二）编辑数组公式,编辑步骤：1、在数组区域中单击任一单元格；2、单击公式编辑栏，当编辑栏被激活时，“”在数组公式中消失；3、编辑数组公式内容；4、修改完后，按“Ctrl+Shift+Enter”组合键,（三）删除数组公式,步骤：1、选定存放数组公式的所有单元格；2、按下Delete键,二、数组公式的应用,（一）计算两个数
6、据区域的乘积（二）计算多个数据区域的和（三）同时对多个数据区域进行相同的计算,一、AND函数、OR函数、NOT函数,（一）AND函数1、表示逻辑与，当所有条件都满足时，AND函数返回TRUE，否则返回FALSE。2、格式：=AND（条件1，条件2，条件n）,（二）OR函数1、表示逻辑或，只要有一个条件都满足时，该函数返回TRUE，当所有条件都不满足时才返回FALSE。2、格式：=OR（条件1，条件2，条件n）,（三）NOT函数1、只有一个逻辑参数，可以计算出TRUE或FALSE的逻辑值或逻辑表达式。2、格式：=NOT（条件）注意：以上三个函数一般与IF函数结合使用,IF函数,格式：IF（log
7、ical-test,value-if-true,value-if-false）其中：logical-test：为条件；value-if-true：条件为真时执行该参数；value-if-false：条件为假时执行该参数。如：=IF（TRUE，”开始”,“结束”）=IF（A160，”及格”,“不及格”）,案例 某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于300000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%。,IF(AND(C5300000,D5/C5300000)*(D5/C521%),15%,10%),IF(OR(C530000
8、0,D5/C5300000)+(D5/C521%),15%,10%),*=AND+=OR 仅限于非数组公式中。,SUM、SUMIF、SUMPRODUCT、DSUM（一）无条件求和SUM函数1、目的：对指定的若干数值或单元格求和。2、格式：=SUM（参数1，参数2，参数N）3、若对连续的一行或一列数据进行求和时，可用工具栏中的“”按纽实现。,SUM的特殊用法,1、SUM（A2：A4*B2：B4）=SUMPRODUCT（A2：A4，B2：B4）2、SUM+IF：用于多条件求和,（二）条件求和SUMIF函数1、目的：根据指定条件对若干单元格求和。常用于分类汇总计算。2、格式：=SUMIF（range
9、，criteria,sum_range）range：用于条件判断的单元区域；criteria：确定哪些单元格将被相加求和的条件；sum-range：需要求和的实际单元格。,（三）SUMPRODUCT 函数1、目的：在给定的几组数组中，将数组间对应的元素相乘并返回乘积之和。2、格式：=SUMPRODUCT（array1，array2，array3，）3、注意：数组必须具有相同的维数。,假设:A2=3,B2=2,C2=5,D2=4 SUMPRODUCT(A2:B2,C2:D2)=23 SUMPRODUCT(A2:B2)=5,SUMPRODUCT应用于多条件统计方面,公式1=sumproduct（A
10、1:A710)*(A1:A710)+(A1:A720)注意：1.“*”，一方面表示“且”关系，另一方面也起一个乘的作用，即将逻辑值运算成数值；or：+2.逻辑值的运算 如：FLASE+TRUE=1，FLASE*TRUE=0 即在数值运算中FLASE相当于0，TRUE相当于1,解释：A1:A710，用数组公式，返回一组逻辑值；（A1:A710)*(A1:A710)*(A1:A720)=sumproduct（0，1，1，1，1，0，0）,当表示成：SUMPRODUCT（A1：A710）,(A1:A720)）,此时，结果为0。,改为：SUMPRODUCT（0+（A1：A710）,0+(A1:A720
11、)）0+：表示“强制将逻辑值进行转换”,（四）DSUM 函数1、目的：返回数据清单或数据库的列中满足指定条件的数字之和。2、格式：=DSUM(database,field,criteria)3、参数说明：Database 数据清单或数据库。Field 指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文本，即两端带引号的标志项，也可以是代表数据清单中数据列位置的数字：1 表示第一列。Criteria 为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。,返回,求和总结1.连续区域
12、求和：自动求和2.不连续区域求和：SUM3.基于单条件对数字求和：SUMIF4.基于多个条件对数字求和：SUM+IF（须用数组公式确定）、SUMPRODUCT5、基于存储在其他单元区域中的条件，对数字求和：DSUM,（一）LOOKUP、HLOOKUP、VLOOKUP1.LOOKUP 返回向量（单行区域或单列区域）或数组中的数值。向量形式：是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；=LOOKUP(lookup_value,lookup_vector,result_vector）Lookup_vector 的数值必须按升序排序,三、查找与引用函数,
13、数组形式：在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。=LOOKUP(lookup_value,array)注意：1.array的数值必须按升序排序 2.如果数组区域中的列数多于行数，LOOKUP 在第一行查找 lookup_value。3.如果数组为正方形，或者区域中的行数多于列数）则 在第一列查找 lookup_value。,数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。最好使用
14、 HLOOKUP 或 VLOOKUP 来替代函数 LOOKUP 的数组形式,2、VLOOKUP,功能：用于搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行列号，再进一步返回选定单元格的值。格式：VLOOKUP（LOOKUP-VALUE，TABLE-ARRAY，COL-INDEX-NUM，RANGE-LOOKUP）其中：LOOKUP-VALUE：需要在数据表第一列中查找的数值；TABLE-ARRAY：需要在其中查找数据的数据表；COL-INDEX-NUM：第二参数中待返回的匹配值的序列号；RANGE-LOOKUP：逻辑值，指明该函数返回时是精确匹配（FALSE）还是近似匹配（TRUE或
15、省略）。,注意：range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列；为 FALSE，table_array 不必进行排序。,HLOOKUP：与VLOOKUP含义相同，只是LOOKUP-VALUE是在第一行。,（二）MATCH函数1、目的：返回在指定方式下与指定数值匹配 的数组中元素的相应位置。2、格式：=MATCH（lookup-value，lookup-array，match-type）Lookup_value 为需要在数据表中查找的数值。Lookup_array 可能包含所要查找的数值的连续单元格区域。Match_type 为数字-1、0 或
16、 1。,其中：1：查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列0：查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。-1：查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列。如果省略 match_type，则假设为 1。,（二）INDEX函数1、目的：返回表格或区域中的数值或对数值的 引用。2、格式一（返回值为数值或数组）=INDEX（array，row-num,column-num）array：单元区域或数组常量；row-num：数组中某行的行号，函数从
17、该行返回数 值；若省略，则必须有column-num column-num：数组中某列的列号，函数从该列返回 数值；若省略，则必须有row-num,注意：1、若同时使用row-num和column-num，该函数将返回row-num和column-num交叉 处单元格的数值；2、若将row-num或column-num设置为0，则 该函数返回整个行或列的数组数值。但这 时，须按数组公式的形式输入。如：INDEX(1,2;3,4,2,2)等于 4如果作为数组公式输入，则：INDEX(1,2;3,4,0,2)等于 2;4,格式二：（返回值为引用）=INDEX(reference,row_num,c
18、olumn_num,area_num)其中：1、Reference 对一个或多个单元格区域的引用；如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,column_num)。2、Row_num 引用中某行的行序号，函数从该行返回一个引用。,3、Column_num引用中某列的列序号，函数从该列返回一个引用4、Area_num 选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。选
19、中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。如果引用描述单元格为(A1:B4,D1:E4,G1:H4)，则 area_num 1 为区域 A1:B4、area_num 2 为区域 D1:E4、而 area_num 3 为区域 G1:H4。,例:INDEX(Fruit,2,3)等于引用 C3，内容为 38INDEX(A1:C6,A8:C11),2,2,2)等于引用 B9，内容为$3.55SUM(INDEX(Stock,0,3,1)等于 SUM(C1:C11)等于 216SUM(B2:INDEX(Fruit,5,2)等于 SU
20、M(B2:B6)等于 2.42,INDEX+MATCH,INDEX(D2:D7,MATCH(1,(A10=B2:B7)*(B10=C2:C7),0),1=TRUE*TRUE，即两个条件同时满足,公式必须是数组确定，即Ctrl+Shift+Enter,COUNTIF函数,功能：计算区域中满足给定条件的单元格的个数格式：COUNTIF(range,criteria)其中：Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。,计数函数总结,1、根据条件计数：COUNTIF、DCOUNT、DCOUNTA2、区
21、域中空白单元格的个数：COUNTBLANK3、列表中含数字或文本的单元格个数：COUNTA4、统计列表中包含数字的单元格的个数：COUNT,文本函数,1、EXACT 功能：测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。格式：=EXACT(text1,text2),2、FIND,功能：用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。格式：=FIND(find_text,within_
22、text,start_num),3、FINDB,功能：用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并基于每个字符所使用的字节数从 within_text 的首字符开始返回 find_text 的起始位置编号。此函数用于双字节字符,4、LEFT,功能：基于所指定的字符数返回文本字符串中的第一个或前几个字符。格式：=LEFT(text,num_chars)如果 num_chars 大于文本长度，则 LEFT 返回所有文本。,RIGHT函数则返回最后一个或多个字符,5、LEN,功能：返回文本字符串中的字符数 格式：=LEN(text),6、MID,功能：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。格式：=MID(text,start_num,num_chars),带星号“*”的函数要安装了“分析工具箱”之后才能使用。,
