《EXCEL在财务工作中的应用课件.ppt》由会员分享，可在线阅读，更多相关《EXCEL在财务工作中的应用课件.ppt（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Financial Management experimentEXCEL在财务工作中的应用在财务工作中的应用EXCEL在财务工作中的应用概述在财务工作中的应用概述EXCEL基本技术基本技术EXCEL应用案例应用案例提纲提纲EXCELEXCEL在财务工作中的应用概述在财务工作中的应用概述p对对EXCEL软件的再认识软件的再认识EXCEL不是一个简单的表格处理软件，不是一个简单的表格处理软件，而是一个数据管理平台而是一个数据管理平台主要表现在：主要表现在：强大的数据计算能力强大的数据计算能力基本的逻辑判断能力基本的逻辑判断能力灵活多样的报表格式定义灵活多样的报表格式定义强大的数据管理能力强大的数据
2、管理能力EXCELEXCEL在财务工作中的应用概述在财务工作中的应用概述p在财务工作中引入在财务工作中引入EXCEL的动因分析的动因分析弥补软件功能缺陷弥补软件功能缺陷完成数据共享与交换完成数据共享与交换满足个性化信息需求满足个性化信息需求EXCELEXCEL在财务工作中的应用概述在财务工作中的应用概述pEXCEL在财务工作中应用的主要内容在财务工作中应用的主要内容在会计处理方面：账簿数据整理、数据检索与查询、统计报在会计处理方面：账簿数据整理、数据检索与查询、统计报表编制表编制在财务管理方面：财务报表分析、财务决策模型的构建在财务管理方面：财务报表分析、财务决策模型的构建在审计方面：数据导出
3、、数据正确性验证、审计工作底稿的在审计方面：数据导出、数据正确性验证、审计工作底稿的编制编制EXCELEXCEL应用的基本思路应用的基本思路选择实现选择实现工具工具分析数据分析数据来源来源确定求解确定求解算法算法报告报告报告报告生成生成生成生成制制制制定定定定解解解解决决决决方方方方案案案案约束条件约束条件判定判定确定求解确定求解问题问题资料收集资料收集需需需需求求求求分分分分析析析析获取数据获取数据算法定义算法定义程序设计程序设计表格设计表格设计方方方方案案案案实实实实现现现现EXCEL基本技术基本技术利用利用ExcelExcel获取会计数据获取会计数据 p直接读取会计软件产生的数据交换文件
4、直接读取会计软件产生的数据交换文件绝大多数数据库都符合绝大多数数据库都符合ODBC（开放式数据库互（开放式数据库互连）标准，即数据库可以将数据库中的数据按照一个连）标准，即数据库可以将数据库中的数据按照一个标准的格式转出，也可以按照标准的格式转入。会计标准的格式转出，也可以按照标准的格式转入。会计软件都离不开数据库的支持，每个成熟的商品化会计软件都离不开数据库的支持，每个成熟的商品化会计软件都提供了与其他软件相连的数据接口，各种数据软件都提供了与其他软件相连的数据接口，各种数据库都可以转出和读取文本格式的数据库都可以转出和读取文本格式的数据EXCEL基本技术基本技术利用利用ExcelExcel
5、获取会计数据（续）获取会计数据（续）p利用利用Excel软件提供的软件提供的“获取外部数据获取外部数据”获取数据获取数据可以通过使用可以通过使用Query来检索诸如来检索诸如MicrosoftAccess或或MicrosoftSQLServer等关系数据库中等关系数据库中的数据。除外部数据库外，还可以检索的数据。除外部数据库外，还可以检索MicrosoftExcel数据清单或文本文件中的数据，并且可以保持数据清单或文本文件中的数据，并且可以保持数据库中的数据与数据库中的数据与Excel中的数据同步。中的数据同步。EXCEL基本技术基本技术ExcelExcel中的常用函数中的常用函数 p财务函数
6、财务函数p日期与时间函数日期与时间函数p统计函数统计函数p查找与应用函数查找与应用函数p文本函数文本函数EXCEL基本技术基本技术ExcelExcel中的数据管理功能中的数据管理功能 p排序排序p筛选筛选p分类汇总分类汇总p数据透视表数据透视表EXCEL基本技术基本技术ExcelExcel中的高级应用中的高级应用 pVBA程序程序p宏宏p数据安全性数据安全性EXCEL基本技术基本技术数组公式及其应用一、数组公式涵义一、数组公式涵义数组公式就是可以同时进行多重计算并返回一种或数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。多种结果的公式。数组参数：数组公式中使用的两组或多组数据，它数
7、组参数：数组公式中使用的两组或多组数据，它可以是一个数据区域也可以是数组常量。可以是一个数据区域也可以是数组常量。注意：公式中的每个数组参数必须有相同的行与列。二、数组公式的输入、编辑与删除二、数组公式的输入、编辑与删除（一）数组公式的输入（一）数组公式的输入1、输入步骤：、输入步骤：（1）选定单元格或单元区域；）选定单元格或单元区域；（2）输入数组公式；）输入数组公式；（3）同时按下）同时按下“Ctrl+Shift+Enter”组合键。组合键。2、输入数组常量的方法、输入数组常量的方法数组常量：直接键入的数值数组。数组常量：直接键入的数值数组。（1）选取单元区域）选取单元区域（2）在公式编辑
8、栏中输入数组公式）在公式编辑栏中输入数组公式（3）同时按下）同时按下“Ctrl+Shift+Enter”组合键组合键注意：直接在公式输入数值，必须用注意：直接在公式输入数值，必须用“”括住；括住；不同列的数值用逗号隔开；不同列的数值用逗号隔开；不同行的数值用分号隔开。不同行的数值用分号隔开。3、输入数组公式的方法、输入数组公式的方法（1）选取单元区域）选取单元区域（2）在公式编辑栏中输入数组公式）在公式编辑栏中输入数组公式（3）同时按下）同时按下“Ctrl+Shift+Enter”组合键组合键（二）编辑数组公式编辑步骤：编辑步骤：1、在数组区域中单击任一单元格；、在数组区域中单击任一单元格；2
9、、单击公式编辑栏，当编辑栏被激活时，、单击公式编辑栏，当编辑栏被激活时，“”在在数组公式中消失；数组公式中消失；3、编辑数组公式内容；、编辑数组公式内容；4、修改完后，按、修改完后，按“Ctrl+Shift+Enter”组合键组合键（三）删除数组公式步骤：1、选定存放数组公式的所有单元格；2、按下Delete键二、数组公式的应用（一）计算两个数据区域的乘积（二）计算多个数据区域的和（三）同时对多个数据区域进行相同的计算一、AND函数、OR函数、NOT函数（一）（一）AND函数函数1、表示逻辑与，当所有条件都满足时，、表示逻辑与，当所有条件都满足时，AND函数返回函数返回TRUE，否则返回否则返
10、回FALSE。2、格式：、格式：=AND（条件条件1，条件，条件2，条件条件n）（二）（二）OR函数函数1、表示逻辑或，只要有一个条件都满足时，该函数、表示逻辑或，只要有一个条件都满足时，该函数返回返回TRUE，当所有条件都不满足时才返回，当所有条件都不满足时才返回FALSE。2、格式：、格式：=OR（条件（条件1，条件，条件2，条件条件n）（三（三）NOT函数函数1、只有一个逻辑参数，可以计算出、只有一个逻辑参数，可以计算出TRUE或或FALSE的的逻辑值或逻辑表达式。逻辑值或逻辑表达式。2、格式：、格式：=NOT（条件条件）注意：以上三个函数一般与注意：以上三个函数一般与IF函数结合使用函
11、数结合使用IFIF函数函数格式：格式：IF（logical-test,value-if-true,value-if-false）其中：其中：logical-test：为条件；为条件；value-if-true：条件为真时执行该参数；条件为真时执行该参数；value-if-false：条件为假时执行该参数。条件为假时执行该参数。如：如：=IF（TRUE，”开始开始”,“结束结束”）=IF（A160，”及格及格”,“不及格不及格”）案例案例某企业根据各销售部门的销售额及销售费用确定奖某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于金提成比例及提取额，若销售额大于3000
12、00元且销售费元且销售费用占销售额的比例不超过用占销售额的比例不超过1%，则奖金提取比例为，则奖金提取比例为15%，否则为否则为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、若对连续的
13、一行或一列数据进行求和时，可用工具栏中的若对连续的一行或一列数据进行求和时，可用工具栏中的“”按纽实现。按纽实现。SUMSUM的特殊用法的特殊用法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
14、函数函数1、目的：在给定的几组数组中，将数组间对应的元素相乘并返回乘积之和。2、格式：=SUMPRODUCT（array1，array2，array3，）3、注意：数组必须具有相同的维数。假设假设:A2=3,B2=2,C2=5,D2=4SUMPRODUCT(A2:B2,C2:D2)=23SUMPRODUCT(A2:B2)=5SUMPRODUCTSUMPRODUCT应用于多条件统计方面应用于多条件统计方面公式公式1=sumproduct（A1:A710)*(A1:A710)+(A1:A710，用数组公式，返回一组逻辑值；，用数组公式，返回一组逻辑值；（A1:A710)*(A1:A710)*(A1
15、:A710）,(A1:A710）,0+(A1:A720)）0+：表示：表示“强制将逻辑值进行转换强制将逻辑值进行转换”（四）（四）DSUM函数函数1、目的：返回数据清单或数据库的列中满足指定条件的数字之和。、目的：返回数据清单或数据库的列中满足指定条件的数字之和。2、格式：、格式：=DSUM(database,field,criteria)3、参数说明：、参数说明：Database数据清单或数据库。数据清单或数据库。Field指定函数所使用的数据列。数据清单中的数据列必须在第一行具指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。有标志项。Field可以是文本，即两端带引号的标
16、志项，也可以是代表数可以是文本，即两端带引号的标志项，也可以是代表数据清单中数据列位置的数字：据清单中数据列位置的数字：1表示第一列。表示第一列。Criteria为一组包含给定条件的单元格区域。可以为参数为一组包含给定条件的单元格区域。可以为参数criteria指定指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。元格。求和总结求和总结1.连续区域求和：自动求和连续区域求和：自动求和2.不连续区域求和：不连续区域求和：SUM3.基于单条件对数字求和：基于单条件对数字求和：SUMIF4.基于多个条件对数字求和：基
17、于多个条件对数字求和：SUM+IF（须用数组公（须用数组公式确定）、式确定）、SUMPRODUCT5、基于存储在其他单元区域中的条件，对数字求和：、基于存储在其他单元区域中的条件，对数字求和：DSUM（一）（一）LOOKUP、HLOOKUP、VLOOKUP1.LOOKUP返回返回向量向量（单行区域或单列区域）或（单行区域或单列区域）或数组数组中的数值。中的数值。向量形式：向量形式：是在单行区域或单列区域（向量）中查找是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置数值，然后返回第二个单行区域或单列区域中相同位置的数值；的数值；=LOOKUP(lookup_v
18、alue,lookup_vector,result_vector）Lookup_vector的数值必须按升序排序的数值必须按升序排序三、查找与引用函数三、查找与引用函数数组形式：数组形式：在数组的第一行或第一列中查找指定数值，然后返回最在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。后一行或最后一列中相同位置处的数值。=LOOKUP(lookup_value,array)注意：注意：1.array的数值必须按升序排序的数值必须按升序排序2.如果数组区域中的列数多于行数，如果数组区域中的列数多于行数，LOOKUP在第一行查找在第一行查找lookup_value
19、。3.如果数组为正方形，或者区域中的行数多于如果数组为正方形，或者区域中的行数多于列数）则列数）则在第一列查找在第一列查找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：第二参数中待返回的匹配值的序列号；第二参数中待返回的匹配值的序列号；
21、RANGE-LOOKUP：逻辑值，指明该函数返回时是精确匹配逻辑值，指明该函数返回时是精确匹配（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：查找大于或等
23、于 lookup_value 的最小数值。Lookup_array 必须按降序排列。如果省略 match_type，则假设为 1。（二（二）INDEX函数函数1、目的：返回表格或区域中的数值或对数值的、目的：返回表格或区域中的数值或对数值的引用。引用。2、格式一（返回值为数值或数组）、格式一（返回值为数值或数组）=INDEX（array，row-num,column-num）array：单元区域或数组常量；单元区域或数组常量；row-num：数组中某行的行号，函数从该行返回数数组中某行的行号，函数从该行返回数值；若省略，则必须有值；若省略，则必须有column-numcolumn-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_
26、num分别为可选项。例如，对于单行的引用，可以使用函数分别为可选项。例如，对于单行的引用，可以使用函数INDEX(reference,column_num)。2、Row_num引用中某行的行序号，函数从该行返回一个引用。引用中某行的行序号，函数从该行返回一个引用。3、Column_num引用中某列的列序号，函数从该列返回一个引用引用中某列的列序号，函数从该列返回一个引用4、Area_num选择引用中的一个区域，并返回该区域中选择引用中的一个区域，并返回该区域中row_num和和column_num的交叉区域。选中或输入的第一个区域序号为的交叉区域。选中或输入的第一个区域序号为1，第二个，第二个
27、为为2，以此类推。如果省略，以此类推。如果省略area_num，函数函数INDEX使用区域使用区域1。如果引用描述单元格为如果引用描述单元格为(A1:B4,D1:E4,G1:H4)，则则area_num1为区域为区域A1:B4、area_num2为区域为区域D1:E4、而而area_num3为区域为区域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)等于
28、 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为需要计算其中满足条件的单元格数目的单元为需要计算其中满足条件的单元格数目的单元格区域。格区域。Criteria为确定哪些单元格将
29、被计算在内的条件，其形为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。式可以为数字、表达式或文本。计数函数总结计数函数总结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,start_num)3 3、FINDBFINDB功能：用于查找其他文本字符
31、串功能：用于查找其他文本字符串(within_text)内内的文本字符串的文本字符串(find_text)，并基于每个字符所使用的，并基于每个字符所使用的字节数从字节数从within_text的首字符开始返回的首字符开始返回find_text的起始位置编号。的起始位置编号。此函数用于双字节字符此函数用于双字节字符4 4、LEFTLEFT功能：基于所指定的字符数返回文本字符串中功能：基于所指定的字符数返回文本字符串中的第一个或前几个字符。的第一个或前几个字符。格式：格式：=LEFT(text,num_chars)如果如果num_chars大于文本长度，则大于文本长度，则LEFT返回返回所有文本。所有文本。RIGHT函数则返回最后一个或多个字符函数则返回最后一个或多个字符5 5、LENLEN功能：返回文本字符串中的字符数功能：返回文本字符串中的字符数格式：格式：=LEN(text)6 6、MIDMID功能：功能：返回文本字符串中从指定位置开始的特定数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。目的字符，该数目由用户指定。格式：格式：=MID(text,start_num,num_chars)带星号“*”的函数要安装了“分析工具箱”之后才能使用。