STEP财务人员EXCEL培训

内部培训教材Excel常用函数财务人员培训之———2—培训内容简介常用函数基本功能讲解第二章基本概念的理解第一章第三章函数综合应用示例—3—三个基本概念相对引用绝对引用混合引用基本概念第一章—4—基本概念相对引用：相对引用实际上是引用单元格的相对位置，即当把公式复制到其他单元格时，行或者列都会发生改变。如当前单元格显示为A1，向下复制会自动变成A2，向右复制向B1。绝对引用：绝对引用实际上是引用单元格的绝对位置，即当复制公式时，行和列均不会发生改变。如当前单元格显示为$A$1,向下或向右复制单元格显示的内容不变，始终还是$A$1。混合引用：混合引用实际上是行或者列当中有一个是绝对引用，另一个是相对引用。如单元格显示为$A1（对列进行绝对引用）,即复制公式到任何其他的单元格改变的是行而列号始终保持不变；或A$1（对行进行绝对引用）,即复制公式到任何其他的单元格改变的是列而行号始终保持不变。小窍门：相对引用与绝对引用的快速切换键F4第一章—5—函数基本功能讲解包含四方面内容:函数主要功能函数使用格式涉及参数说明基本用法示例第二章功能讲解—6—第二章IF函数主要功能:根据对指定条件的逻辑判断的真假结果,返回相对应的内容使用格式:=IF(Logical,Value_if_true,Value_if_false)参数说明:Logical代表逻辑判断表达式;Value_if_true表示当判断条件为逻辑”真(True)”时显示的内容,如果忽略返回”True”;Value_if_false表示当判断条件为逻辑”假(False)”时的显示内容,如果忽略返回”False”.应用示例:一般性示例参考IF一般性应用表重点讲涉及需同时满足多个条件的判断:如公司2013年对工龄超过5年同时属制造部门的员工进行加薪，具体示例见Excel常用函数实例操作-IF示例:功能讲解公式=IF(AND(B2=制造部门,E25),Y,)—7—第二章IF函数同时满足多个条件的判断思考：如果公司2013年对工龄超过5年同时属制造部门或研发部门的员工进行加薪，那公式怎么写？功能讲解—8—第二章Vlookup函数主要功能:在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处的数值使用格式:Vlookup(lookup_value,table_array,col_index_num,range_lookup)参数说明:Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值……）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。功能讲解—9—第二章Vlookup函数出现#N/A的处理应用示例:一般性示例略，具体解决如果找不到目标值则返回错误值#N/A的情况。此时会涉及到另外一个函数ISNA，该函数用来判断是否存在#N/A的错误值，如果存在则返回一个自己想要设定的值。如查找公司员工所属部门，如职工姓名在查找范围内不存在的情况下的处理。功能讲解公式=IF(ISNA(VLOOKUP(I1,A:B,2,0)),无此员工,VLOOKUP(I1,A:B,2,0))—10—第二章SUM函数主要功能:计算所有参数数值的和使用格式:=SUM（Number1,Number2……）参数说明:Number1、Number2……代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等应用示例:一般性示例略,重点解决多个相同格式的工作表对同一单元格进行求和的问题，如对2013年度各月发生的现金流量表进行按月汇总。思考:如果SUM内的表格之间的位置发生变化会出现什么结果?功能讲解公式=SUM('201101:201112'!C7)—11—第二章SUMIF函数主要功能:计算符合指定条件的单元格区域内的数值和使用格式:=SUMIF（Range,Criteria,Sum_Range）参数说明:Range代表条件判断的单元格区域，即在哪个范围内来寻找需要满足的条件；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用示例:由于此函数应用较为广泛,故重点列举此函数的五种常用用法来举例。功能讲解—12—第二章SUMIF函数示例之一对满足条件列的求和：如对表名为Sumif对列求和中的数据按供应商名称进行数量和金额汇总，则sumif函数中的Range=B列，Criteria即为需要进行汇总的各个供应商名称=H1，H2……..，而Sum_Range即为需要进行汇总的E列和F列功能讲解公式=SUMIF(B:B,H2,E:E)—13—第二章SUMIF函数示例之二对满足条件行的求和：如对表名为Sumif对行求和中的数据分别按2012年度和2013年度进行汇总，则sumif函数中的Range=第二行(即$B$2:$AK$2)，Criteria即为需要进行汇总的各个年份=AN2或AO2，而Sum_Range即为需要进行汇总的行,如第3行,第4行等功能讲解公式=SUMIF($B$2:$AK$2,AN$2,$B3:$AK3)—14—第二章SUMIF函数示例之三对满足模糊条件的值进行汇总：如在表名为Sumif模糊条件汇总的表中，对料号包含F38物料进行汇总。则sumif函数中的Range=A列，Criteria为“F38*”，而Sum_Range为需要进行汇总的列,如要汇总数量则为C列，要汇总金额则为D列。功能讲解公式=SUMIF(A:A,F38*,D:D)如果不直接输入F38*而用单元格的数值来替换怎么弄？—15—第二章SUMIF函数示例之四对满足日期条件的值进行汇总：如在表名为Sumif对日期条件汇总的表中，对日期大于2011年5月5日的质量成本进行汇总。则sumif函数中的Range=A列，Criteria为“”&date(2011,5,5)，而而Sum_Range为需要进行汇总的C列。思考:如果要汇总大于2011.5.1日且小于2011.7.1日的值怎么写?功能讲解公式=SUMIF(A:A,&DATE(2011,5,5),C:C)—16—第二章SUMIF函数示例之五对满足多个条件的值同时进行汇总：如在产成品差异分摊结转表中，其他存货金额包含了在制品、半制品、产成品和在途商品，现对这四个会计科目同时进行汇总。用科目代码为满足条件，则sumif函数中的Range=B列，Criteria为四个会计科目的代码用数组表示{“12410101”,“12420101”,“12430101”,“12900102”}，而Sum_Range为需要进行汇总的D列。此处由于是要多对个会计科目同时进行求和，所以在sumif函数结束后再需用sum函数对其进行汇总。功能讲解公式=SUM(SUMIF('科目余额表-201213'!B:B,{12410101,12420101,12430101,12900102},'科目余额表-201213'!G:G))—17—第二章SUMIF函数示例之五功能讲解思考：模糊条件汇总与多个条件值同时进行汇总的关系—18—第二章Column函数主要功能:返回给定引用的列标使用格式:=COLUMN(reference)参数说明:Reference为需要得到其列标的单元格或单元格区域。如果省略reference，则假定函数COLUMN是对所在单元格的引用。如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则COLUMN函数将reference中的列标以水平数组的形式返回应用示例:公式“=COLUMN(A3)”返回1，公式=COLUMN(B3:C5)返回2功能讲解—19—第二章Row函数主要功能:返回给定引用的行号使用格式:=ROW(reference)参数说明:Reference为需要得到其行号的单元格或单元格区域应用示例:公式“=ROW(A6)”返回6，如果在C5单元格中输入公式“=ROW()”，其计算结果为5功能讲解思考：Column和Row返回单元格的行和列号在实际上有什么用处？—20—第二章Trim函数主要功能:除了单词之间的单个空格外，清除文本中的所有的空格使用格式:=TRIM(text)参数说明:Text是需要清除其中空格的文本应用示例:如果A1=FirstQuarterEarnings，则公式“=TRIM(A1)”返回“FirstQuarterEarnings”，A1单元格内容中首未空格被剔除。功能讲解特别说明：此函数主要用在用Vlookup检索某个文本值是否一致时，由于要检索的单元格文本含有人眼所无法直接判断出的字符，导致Vlookup检索失败的情况。—21—第二章Clean函数主要功能:删除文本中不能打印的字符，主要是单元格在自动换行中留有空行的情况使用格式:=CLEAN(text)参数说明:Text为要从中删除不能打印字符的任何字符串应用示例:如Clean剔除自动换行空格表中A1=B2=Clean(A1)后剔除了自动换行时所留下的空行,则显示为功能讲解—22—第二章Indirect函数主要功能:返回由文字串指定的引用,此函数立即对引用进行计算并显示其内容。使用格式:=INDIRECT(ref_text，a1)参数说明:Ref_text是对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文字串单元格的引用;A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略，ref_text被解释为A1-样式的引用。如果a1为FALSE，ref_text被解释为R1C1-样式的引用。应用示例:具体示例参见科目余额表-格式整理模板表。功能讲解—23—第二章Indirect函数功能讲解公式=INDIRECT(SAP科目余额表!F&ROW()*2+1)C列单元格公式—24—第二章Indirect函数功能讲解公式=VALUE(TRIM(INDIRECT(SAP科目余额表!J&ROW()*2+1)))D列单元格公式—25—第三章综合示例根据上述所讲到的函数,建立一张Excel查询表,使其能根据所选择的月份来查询到利润表的预算金额和实际发生金额,并计算其差额和达标率.—26—第三章综合示例—27—第三章综合示例根据上述所讲到的函数,建立一张Excel查询表,使其能根据所选择的月份来查询到利润表的预算金额和实际发生金额,并计算其差额和达标率.想一想:是否还有其他的函数或方法能达到想要的结果?—28—经验总结表格和数据应尽可能的规范，比如相同行或列数据格式要保持一致,整个表格中尽量不要有合并单元格。善于发现数据的规律性，学会挖掘各关联数据间的逻辑关系。Excel公式和程序能够大量处理的数据，但前提必须是这些数据或数据之间具有某种规律性。了解函数的基本用法是基础，灵活运用各函数创建彼此间的逻辑关系是核心。要做到这一点需要多想、多练才能熟能生巧。补拙精于诚思勤能课件制作