EXCEL在财务工作中的应用.ppt

1、EXCEL在财务工作中的应用EXCEL在财务工作中的应用概述在财务工作中的应用概述EXCEL基本技术基本技术EXCEL应用案例应用案例提纲提纲EXCELEXCEL在财务工作中的应用概述在财务工作中的应用概述p对对EXCEL软件的再认识软件的再认识 EXCEL不是一个简单的表格处理软件，而不是一个简单的表格处理软件，而是一个数据管理平台是一个数据管理平台 主要表现在：主要表现在：强大的数据计算能力强大的数据计算能力 基本的逻辑判断能力基本的逻辑判断能力 灵活多样的报表格式定义灵活多样的报表格式定义 强大的数据管理能力强大的数据管理能力EXCELEXCEL在财务工作中的应用概述在财务工作中的应用概

2、述p 在财务工作中引入在财务工作中引入EXCEL的动因分析的动因分析 弥补软件功能缺陷弥补软件功能缺陷 完成数据共享与交换完成数据共享与交换 满足个性化信息需求满足个性化信息需求 EXCELEXCEL在财务工作中的应用概述在财务工作中的应用概述p EXCEL在财务工作中应用的主要内容在财务工作中应用的主要内容 在会计处理方面：账簿数据整理、数据检索与查询、统计报表在会计处理方面：账簿数据整理、数据检索与查询、统计报表编制编制 在财务管理方面：财务报表分析、财务决策模型的构建在财务管理方面：财务报表分析、财务决策模型的构建 在审计方面：数据导出、数据正确性验证、审计工作底稿的编在审计方面：数据导

3、出、数据正确性验证、审计工作底稿的编制制 EXCELEXCEL应用的基本思路应用的基本思路选择实现选择实现工具工具分析数据分析数据来源来源确定求解确定求解算法算法约束条件约束条件判定判定确定求解确定求解问题问题资料收集资料收集获取数据获取数据算法定义算法定义程序设计程序设计表格设计表格设计EXCEL基本技术基本技术利用利用ExcelExcel获取会计数据获取会计数据 p 直接读取会计软件产生的数据交换文件直接读取会计软件产生的数据交换文件 绝大多数数据库都符合绝大多数数据库都符合ODBC（开放式数据库互（开放式数据库互连）标准，即数据库可以将数据库中的数据按照一个连）标准，即数据库可以将数据库

4、中的数据按照一个标准的格式转出，也可以按照标准的格式转入。会计标准的格式转出，也可以按照标准的格式转入。会计软件都离不开数据库的支持，每个成熟的商品化会计软件都离不开数据库的支持，每个成熟的商品化会计软件都提供了与其他软件相连的数据接口，各种数据软件都提供了与其他软件相连的数据接口，各种数据库都可以转出和读取文本格式的数据库都可以转出和读取文本格式的数据 EXCEL基本技术基本技术利用利用ExcelExcel获取会计数据（续）获取会计数据（续）p利用利用 Excel 软件提供的软件提供的“获取外部数据获取外部数据”获取数据获取数据 可以通过使用可以通过使用Query来检索诸如来检索诸如 Mic

5、rosoft Access 或或 Microsoft SQL Server 等关系数据库中的数据。除等关系数据库中的数据。除外部数据库外，还可以检索外部数据库外，还可以检索 Microsoft Excel 数据清数据清单或文本文件中的数据，并且可以保持数据库中的数单或文本文件中的数据，并且可以保持数据库中的数据与据与 Excel 中的数据同步。中的数据同步。EXCEL基本技术基本技术ExcelExcel中的常用函数中的常用函数 p 财务函数财务函数p 日期与时间函数日期与时间函数p 统计函数统计函数p 查找与应用函数查找与应用函数p 文本函数文本函数EXCEL基本技术基本技术ExcelExce

6、l中的数据管理功能中的数据管理功能 p 排序排序p 筛选筛选p 分类汇总分类汇总p 数据透视表数据透视表EXCEL基本技术基本技术ExcelExcel中的高级应用中的高级应用 p VBA程序程序p 宏宏p 数据安全性数据安全性EXCEL基本技术基本技术数组公式及其应用一、数组公式涵义一、数组公式涵义 数组公式就是可以同时进行多重计算并返回一种或多数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。种结果的公式。数组参数：数组公式中使用的两组或多组数据，它数组参数：数组公式中使用的两组或多组数据，它可以是一个数据区域也可以是数组常量。可以是一个数据区域也可以是数组常量。注意：公式中的每个

7、数组参数必须有相同的行与列。二、数组公式的输入、编辑与删除二、数组公式的输入、编辑与删除（一）数组公式的输入（一）数组公式的输入 1、输入步骤：、输入步骤：（1）选定单元格或单元区域；）选定单元格或单元区域；（2）输入数组公式；）输入数组公式；（3）同时按下）同时按下“Ctrl+Shift+Enter”组合键。组合键。2、输入数组常量的方法、输入数组常量的方法 数组常量：直接键入的数值数组。数组常量：直接键入的数值数组。（1）选取单元区域）选取单元区域（2）在公式编辑栏中输入数组公式）在公式编辑栏中输入数组公式（3）同时按下）同时按下“Ctrl+Shift+Enter”组合键组合键注意：直接在

8、公式输入数值，必须用注意：直接在公式输入数值，必须用“”括住；括住；不同列的数值用逗号隔开；不同列的数值用逗号隔开；不同行的数值用分号隔开。不同行的数值用分号隔开。3、输入数组公式的方法、输入数组公式的方法（1）选取单元区域）选取单元区域（2）在公式编辑栏中输入数组公式）在公式编辑栏中输入数组公式（3）同时按下）同时按下“Ctrl+Shift+Enter”组合键组合键（二）编辑数组公式编辑步骤：编辑步骤：1、在数组区域中单击任一单元格；、在数组区域中单击任一单元格；2、单击公式编辑栏，当编辑栏被激活时，、单击公式编辑栏，当编辑栏被激活时，“”在数在数组公式中消失；组公式中消失；3、编辑数组公式

9、内容；、编辑数组公式内容；4、修改完后，按、修改完后，按“Ctrl+Shift+Enter”组合键组合键（三）删除数组公式步骤：1、选定存放数组公式的所有单元格；2、按下Delete键二、数组公式的应用（一）计算两个数据区域的乘积（二）计算多个数据区域的和（三）同时对多个数据区域进行相同的计算一、AND函数、OR函数、NOT函数（一）（一）AND函数函数1、表示逻辑与，当所有条件都满足时，、表示逻辑与，当所有条件都满足时，AND函数返回函数返回TRUE，否则返回否则返回FALSE。2、格式：、格式：=AND（条件条件1，条件，条件2，条件条件n）（二）（二）OR函数函数1、表示逻辑或，只要有一

10、个条件都满足时，该函数、表示逻辑或，只要有一个条件都满足时，该函数返回返回TRUE，当所有条件都不满足时才返回，当所有条件都不满足时才返回FALSE。2、格式：、格式：=OR（条件（条件1，条件，条件2，条件条件n）（三（三）NOT函数函数1、只有一个逻辑参数，可以计算出、只有一个逻辑参数，可以计算出TRUE或或FALSE的的逻辑值或逻辑表达式。逻辑值或逻辑表达式。2、格式：、格式：=NOT（条件条件）注意：以上三个函数一般与注意：以上三个函数一般与IF函数结合使用函数结合使用IFIF函数函数格式：格式：IF（logical-test,value-if-true,value-if-false）

11、其中：其中：logical-test：为条件；为条件；value-if-true：条件为真时执行该参数；条件为真时执行该参数；value-if-false：条件为假时执行该参数。条件为假时执行该参数。如：如：=IF（TRUE，”开始开始”,“结束结束”）=IF（A160，”及格及格”,“不及格不及格”）案例案例 某企业根据各销售部门的销售额及销售费用确定奖某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于金提成比例及提取额，若销售额大于300000元且销售费元且销售费用占销售额的比例不超过用占销售额的比例不超过1%，则奖金提取比例为，则奖金提取比例为15%，否则为否则

12、为10%。IF(AND(C5300000,D5/C5300000)*(D5/C52300000,D5/C5300000)+(D5/C521%),15%,10%)*=AND +=OR 仅限于非数组公式中。仅限于非数组公式中。SUM、SUMIF、SUMPRODUCT、DSUM（一）无条件求和一）无条件求和SUM函数函数1、目的：对指定的若干数值或单元格求和。、目的：对指定的若干数值或单元格求和。2、格式：、格式：=SUM（参数参数1，参数，参数2，参数参数N）3、若对连续的一行或一列数据进行求和时，可用工具栏中的若对连续的一行或一列数据进行求和时，可用工具栏中的“”按纽实现。按纽实现。SUMSUM

13、的特殊用法的特殊用法1、SUM（A2：A4*B2：B4）=SUMPRODUCT（A2：A4，B2：B4）2、SUM+IF：用于多条件求和：用于多条件求和（二）条件求和SUMIF函数1、目的：根据指定条件对若干单元格求和。常用于分类汇总计算。2、格式：=SUMIF（range，criteria,sum_range）range：用于条件判断的单元区域；criteria：确定哪些单元格将被相加求和的条件；sum-range：需要求和的实际单元格。（三（三）SUMPRODUCT 函数函数1、目的：在给定的几组数组中，将数组间对应的元素相乘并返回乘积之和。2、格式：=SUMPRODUCT（array1，

14、array2，array3，）3、注意：数组必须具有相同的维数。假设假设:A2=3,B2=2,C2=5,D2=4 SUMPRODUCT(A2:B2,C2:D2)=23 SUMPRODUCT(A2:B2)=5SUMPRODUCTSUMPRODUCT应用于多条件统计方面应用于多条件统计方面公式公式1=sumproduct（A1:A710)*(A1:A710)+(A1:A710，用数组公式，返回一组逻辑值；，用数组公式，返回一组逻辑值；（A1:A710)*(A1:A710)*(A1:A710）,(A1:A710）,0+(A1:A720)）0+：表示：表示“强制将逻辑值进行转换强制将逻辑值进行转换”（

15、四）（四）DSUM 函数函数1、目的：返回数据清单或数据库的列中满足指定条件的数字之和。、目的：返回数据清单或数据库的列中满足指定条件的数字之和。2、格式：、格式：=DSUM(database,field,criteria)3、参数说明：、参数说明：Database 数据清单或数据库。数据清单或数据库。Field 指定函数所使用的数据列。数据清单中的数据列必须在第一行具指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。有标志项。Field 可以是文本，即两端带引号的标志项，也可以是代表数可以是文本，即两端带引号的标志项，也可以是代表数据清单中数据列位置的数字：据清单中数据列位置

16、的数字：1 表示第一列。表示第一列。Criteria 为一组包含给定条件的单元格区域。可以为参数为一组包含给定条件的单元格区域。可以为参数 criteria 指定指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。元格。求和总结求和总结1.连续区域求和：自动求和连续区域求和：自动求和2.不连续区域求和：不连续区域求和：SUM3.基于单条件对数字求和：基于单条件对数字求和：SUMIF4.基于多个条件对数字求和：基于多个条件对数字求和：SUM+IF（须用数组公（须用数组公式确定）、式确定）、SUMPRODUCT5、基

17、于存储在其他单元区域中的条件，对数字求和、基于存储在其他单元区域中的条件，对数字求和：DSUM（一）（一）LOOKUP、HLOOKUP、VLOOKUP1.LOOKUP 返回返回向量向量（单行区域或单列区域）或（单行区域或单列区域）或数组数组中的数值。中的数值。向量形式：向量形式：是在单行区域或单列区域（向量）中查找是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置数值，然后返回第二个单行区域或单列区域中相同位置的数值；的数值；=LOOKUP(lookup_value,lookup_vector,result_vector）Lookup_vector 的数值必须

18、按升序排序的数值必须按升序排序 三、查找与引用函数三、查找与引用函数数组形式：数组形式：在数组的第一行或第一列中查找指定数值，然后返回最在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。后一行或最后一列中相同位置处的数值。=LOOKUP(lookup_value,array)注意：注意：1.array的数值必须按升序排序的数值必须按升序排序 2.如果数组区域中的列数多于行数，如果数组区域中的列数多于行数，LOOKUP 在第一行查找在第一行查找 lookup_value。3.如果数组为正方形，或者区域中的行数多于如果数组为正方形，或者区域中的行数多于列数）则列数

19、）则 在第一列查找在第一列查找 lookup_value。数组形式与函数数组形式与函数 HLOOKUP 和函数和函数 VLOOKUP 非常相似。不同之处在于函数非常相似。不同之处在于函数 HLOOKUP 在第一行在第一行查找查找 lookup_value，函数，函数 VLOOKUP 在第一列查在第一列查找，而函数找，而函数 LOOKUP 则按照数组的维数查找则按照数组的维数查找。最好使用最好使用 HLOOKUP 或或 VLOOKUP 来替代函来替代函数数 LOOKUP 的数组形式的数组形式 2 2、VLOOKUPVLOOKUP 功能：用于搜索表区域首列满足条件的元素，确定待检索单元格在功能：用

20、于搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行列号，再进一步返回选定单元格的值。区域中的行列号，再进一步返回选定单元格的值。格式：格式：VLOOKUP（LOOKUP-VALUE，TABLE-ARRAY，COL-INDEX-NUM，RANGE-LOOKUP）其中：其中：LOOKUP-VALUE：需要在数据表第一列中查找的数值；需要在数据表第一列中查找的数值；TABLE-ARRAY：需要在其中查找数据的数据表；需要在其中查找数据的数据表；COL-INDEX-NUM：第二参数中待返回的匹配值的序列号；第二参数中待返回的匹配值的序列号；RANGE-LOOKUP：逻辑值，指明该函数返回时是

21、精确匹配逻辑值，指明该函数返回时是精确匹配 （FALSE）还是近似匹配（还是近似匹配（TRUE或省略）或省略）。注意：注意：range_lookup 为为 TRUE，则，则 table_array 的第一列中的数值必的第一列中的数值必须按须按升序升序排列；为排列；为 FALSE，table_array 不必进行排序。不必进行排序。HLOOKUP：与：与VLOOKUP含义相同，含义相同，只是只是LOOKUP-VALUE是在第一行。是在第一行。（二）（二）MATCH函数函数1、目的：返回在指定方式下与指定数值匹配、目的：返回在指定方式下与指定数值匹配 的数组中元素的相应位置。的数组中元素的相应位置

22、。2、格式：、格式：=MATCH（lookup-value，lookup-array，match-type）Lookup_value 为需要在数据表中查找的数值。为需要在数据表中查找的数值。Lookup_array 可能包含所要查找的数值的连续单元格区域。可能包含所要查找的数值的连续单元格区域。Match_type 为数字为数字-1、0 或或 1。其中：1：查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列0：查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。-1：查找大于或等于 lookup_value

23、 的最小数值。Lookup_array 必须按降序排列。如果省略 match_type，则假设为 1。（二（二）INDEX函数函数1、目的：返回表格或区域中的数值或对数值的、目的：返回表格或区域中的数值或对数值的 引用。引用。2、格式一（返回值为数值或数组）、格式一（返回值为数值或数组）=INDEX（array，row-num,column-num）array：单元区域或数组常量；单元区域或数组常量；row-num：数组中某行的行号，函数从该行返回数数组中某行的行号，函数从该行返回数 值；若省略，则必须有值；若省略，则必须有column-num column-num：数组中某列的列号，函数从该

24、列返回数组中某列的列号，函数从该列返回 数值；若省略，则必须有数值；若省略，则必须有row-num注意：注意：1、若同时使用、若同时使用row-num和和column-num，该函数将返回该函数将返回row-num和和column-num交叉交叉 处单元格的数值；处单元格的数值；2、若将、若将row-num或或column-num设置为设置为0，则，则 该函数返回整个行或列的数组数值。但这该函数返回整个行或列的数组数值。但这 时，须按数组公式的形式输入。时，须按数组公式的形式输入。如：如：INDEX(1,2;3,4,2,2)INDEX(1,2;3,4,2,2)等于等于 4如果作为数组公式输入，

25、则：如果作为数组公式输入，则：INDEX(1,2;3,4,0,2)INDEX(1,2;3,4,0,2)等于等于 2;4格式二：（返回值为引用）格式二：（返回值为引用）=INDEX(reference,row_num,column_num,area_num)其中：其中：1、Reference 对一个或多个单元格区域的引用；对一个或多个单元格区域的引用；如果为引用输入一个不连续的选定区域，必须用括号括起来。如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或或 column_n

26、um 分别为可选项。例如，对于单行的引用，可以使用函数分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,column_num)。2、Row_num 引用中某行的行序号，函数从该行返回一个引用。引用中某行的行序号，函数从该行返回一个引用。3、Column_num 引用中某列的列序号，函数从该列返回一个引用引用中某列的列序号，函数从该列返回一个引用4、Area_num 选择引用中的一个区域，并返回该区域中选择引用中的一个区域，并返回该区域中 row_num 和和 column_num 的交叉区域。选中或输入的第一个区域序号为的交叉区域。选中或输入的第一个区域序号为

27、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)等于 216

28、SUM(B2:INDEX(Fruit,5,2)等于 SUM(B2:B6)等于 2.42INDEX+MATCHINDEX+MATCHINDEX(D2:D7,MATCH(1,(A10=B2:B7)*(B10=C2:C7),0)1=TRUE*TRUE ，即两个条件同时满足公式必须是数组确定，即Ctrl+Shift+EnterCOUNTIFCOUNTIF函数函数功能：计算区域中满足给定条件的单元格的个数功能：计算区域中满足给定条件的单元格的个数格式：格式：COUNTIF(range,criteria)其中：其中：Range 为需要计算其中满足条件的单元格数目的单元为需要计算其中满足条件的单元格数目的单

29、元格区域。格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。可以为数字、表达式或文本。计数函数总结计数函数总结1、根据条件计数：、根据条件计数：COUNTIF、DCOUNT、DCOUNTA2、区域中空白单元格的个数：、区域中空白单元格的个数：COUNTBLANK3、列表中含、列表中含数字或文本数字或文本的单元格个数：的单元格个数：COUNTA4、统计列表中包含、统计列表中包含数字数字的单元格的个数：的单元格的个数：COUNT文本函数文本函数1、EXACT 功能：测试两个字符串是否完全相同。如果它功能：测试两个

30、字符串是否完全相同。如果它们完全相同，则返回们完全相同，则返回 TRUE；否则，返回；否则，返回 FALSE。函。函数数 EXACT 能区分大小写，但忽略格式上的差异。能区分大小写，但忽略格式上的差异。格式：格式：=EXACT(text1,text2)2 2、FINDFIND 功能功能：用于查找其他文本字符串：用于查找其他文本字符串(within_text)内的文本内的文本字符串字符串(find_text)，并从，并从 within_text 的首字符开始返回的首字符开始返回 find_text 的起始位置编号。的起始位置编号。格式：格式：=FIND(find_text,within_text

31、,start_num)3 3、FINDBFINDB 功能：用于查找其他文本字符串功能：用于查找其他文本字符串(within_text)内内的文本字符串的文本字符串(find_text)，并基于每个字符所使用的，并基于每个字符所使用的字节数从字节数从 within_text 的首字符开始返回的首字符开始返回 find_text 的的起始位置编号。起始位置编号。此函数用于双字节字符此函数用于双字节字符 4 4、LEFTLEFT 功能：基于所指定的字符数返回文本字符串中的功能：基于所指定的字符数返回文本字符串中的第一个或前几个字符。第一个或前几个字符。格式：格式：=LEFT(text,num_cha

32、rs)如果如果 num_chars 大于文本长度，则大于文本长度，则 LEFT 返回返回所有文本。所有文本。RIGHT函数则返回最后一个或多个字符函数则返回最后一个或多个字符5 5、LENLEN 功能：返回文本字符串中的字符数功能：返回文本字符串中的字符数 格式：格式：=LEN(text)6 6、MIDMID 功能：功能：返回文本字符串中从指定位置开始的特定数目返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。的字符，该数目由用户指定。格式：格式：=MID(text,start_num,num_chars)与与日日期期有有关关的的函函数数函数说明DATE返回具体日期的系列编号D

33、ATEDIF计算两日期之间的日、月或年数DATEVALUE把文本方式的日期转换成系列编号DAY把系列编号转换成日格式DAYS360以360日/年为基础计算两日期间的天数NOW返回当前日期和时间的系列编号TODAY返回今日日期的系列编号MONTH转换系列编号为月份WEEKDAY转换系列编号为星期中的一天YEAR转换系列编号为年号EOMONTH*在确定的月份数之前或之后，返回月份的最后一日的系列编号EDATE*返回日期的系列编号，在起始日期前后标明月份编号NETWORKDAYS*返回两日期间的全部工作日数WORKDAY*在确定工作日数字之前或之后，返回日期的系列编号WEEKNUM*返回当年中星期数YEARFRAC*返回年号部分，表示在start_date和end_date之间的全部天数带星号“*”的函数要安装了“分析工具箱”之后才能使用。