STEP财务人员EXCEL培训

1、Excel常用函数财务人员培训之 2 培 训 内 容 简 介 常用函数基本功能讲解第二章基本概念的理解第一章第三章函数综合应用示例 3 三个基本概念l 相对引用l 绝对引用l 混合引用基本概念第一章 4 基本概念相对引用：相对引用实际上是引用单元格的相对位置，即当把公式复制到其他单元格时，行或者列都会发生改变。如当前单元格显示为A1，向下复制会自动变成A2，向右复制向B1。绝对引用：绝对引用实际上是引用单元格的绝对位置，即当复制公式时，行和列均不会发生改变。如当前单元格显示为$A$1,向下或向右复制单元格显示的内容不变，始终还是$A$1。混合引用：混合引用实际上是行或者列当中有一个是绝对引用，

2、另一个是相对引用。如单元格显示为$A1（对列进行绝对引用）,即复制公式到任何其他的单元格改变的是行而列号始终保持不变；或A$1（对行进行绝对引用）, 即复制公式到任何其他的单元格改变的是列而行号始终保持不变。 小窍门：相对引用与绝对引用的快速切换键F4第一章 5 函数基本功能讲解包含四方面内容:l函数主要功能l函数使用格式l涉及参数说明l基本用法示例第二章功能讲解 6 第二章 IF函数l主要功能: 根据对指定条件的逻辑判断的真假结果,返回相对应的内容l使用格式:=IF(Logical,Value_if_true,Value_if_false)l参数说明:Logical代表逻辑判断表达式;Val

3、ue_if_true表示当判断条件为逻辑” 真(True)”时显示的内容,如果忽略返回”True”;Value_if_false表示当判断条件为逻辑”假(False)”时的显示内容,如果忽略返回”False”.l应用示例: 一般性示例参考IF一般性应用表重点讲涉及需同时满足多个条件的判断:如公司20132013年对工龄超过5 5年同时属制造部门的员工进行加薪，具体示例见ExcelExcel常用函数实例操作-IF-IF示例: :功能讲解公式= IF(AND(B2=制造部门,E25),Y,) 7 第二章 IF函数同时满足多个条件的判断思考：如果公司20132013年对工龄超过5 5年同时属制造部门

4、或研发部门的员工进行加薪，那公式怎么写？ 功能讲解 8 第二章 Vlookup函数l主要功能: 在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处的数值l使用格式: Vlookup(lookup_value,table_array,col_index_num,range_lookup)l参数说明:Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值）；R

5、ange_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。功能讲解 9 第二章 Vlookup函数出现#N/A的处理l应用示例:一般性示例略，具体解决如果找不到目标值则返回错误值#N/A的情况。此时会涉及到另外一个函数ISNA，该函数用来判断是否存在#N/A的错误值，如果存在则返回一个自己想要设定的值。如查找公司员工所属部门，如职工姓名在查找范围内不存在的情况下的处理。 功能讲解公式公式=IF(ISNA(VLOOKUP(I1,A:B

6、,2,0),=IF(ISNA(VLOOKUP(I1,A:B,2,0),无此员工无此员工,VLOOKUP(I1,A:B,2,0),VLOOKUP(I1,A:B,2,0) 10 第二章 SUM函数l主要功能: 计算所有参数数值的和l使用格式:=SUMSUM（Number1,Number2Number1,Number2）l参数说明:Number1Number1、Number2Number2代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等l应用示例:一般性示例略, ,重点解决多个相同格式的工作表对同一单元格进行求和的问题， 如对20132013年度各月发生的现金流量表进行按月汇总。思

7、考: :如果SUMSUM内的表格之间的位置发生变化会出现什么结果? ?功能讲解公式=SUM(201101:201112!C7)UM(201101:201112!C7) 11 第二章 SUMIF函数l主要功能:计算符合指定条件的单元格区域内的数值和l使用格式:= SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）l参数说明:RangeRange代表条件判断的单元格区域，即在哪个范围内来寻找需要满足的条件；CriteriaCriteria为指定条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区域。l

8、应用示例: 由于此函数应用较为广泛,故重点列举此函数的五种常用用法来举例。功能讲解 12 第二章 SUMIF函数示例之一l对满足条件列的求和：如对表名为SumifSumif对列求和中的数据按供应商名称进行数量和金额汇总，则sumifsumif函数中的Range=BRange=B列，CriteriaCriteria即为需要进行汇总的各个供应商名称=H1=H1，H2.H2.，而Sum_RangeSum_Range即为需要进行汇总的E E列和F F列功能讲解公式=SUMIF(B:B,H2,E:E)SUMIF(B:B,H2,E:E) 13 第二章 SUMIF函数示例之二l对满足条件行的求和：如对表名为

9、SumifSumif对行求和中的数据分别按20122012年度和20132013年度进行汇总，则sumifsumif函数中的Range=Range=第二行( (即$B$2:$AK$2)$B$2:$AK$2)，CriteriaCriteria即为需要进行汇总的各个年份=AN2=AN2或AO2AO2，而Sum_RangeSum_Range即为需要进行汇总的行, ,如第3 3行, ,第4 4行等功能讲解公式= SUMIF($B$2:$AK$2,AN$2,$B3:$SUMIF($B$2:$AK$2,AN$2,$B3:$AK3)AK3) 14 第二章 SUMIF函数示例之三l对满足模糊条件的值进行汇总：

10、如在表名为SumifSumif模糊条件汇总的表中，对料号包含F38F38物料进行汇总。则sumifsumif函数中的Range=ARange=A列，CriteriaCriteria为“F38F38* *”，而Sum_RangeSum_Range为需要进行汇总的列, ,如要汇总数量则为C C列，要汇总金额则为D D列。功能讲解公式=SUMIF(A:A,F38SUMIF(A:A,F38* *,D:D),D:D)如果不如果不直接输直接输入入F38F38* *而用单而用单元格的元格的数值来数值来替换怎替换怎么弄？么弄？ 15 第二章 SUMIF函数示例之四l对满足日期条件的值进行汇总：如在表名为Sum

11、ifSumif对日期条件汇总的表中，对日期大于20112011年5 5月5 5日的质量成本进行汇总。则sumifsumif函数中的Range=ARange=A列，CriteriaCriteria为“”&date(2011,5,5)”&date(2011,5,5)，而而Sum_RangeSum_Range为需要进行汇总的C C列。思考: :如果要汇总大于2011.5.12011.5.1日且小于2011.7.12011.7.1日的值怎么写? ?功能讲解公式= SUMIF(A:A,&DATE(2011,5,SUMIF(A:A,&DATE(2011,5,5),C:C)5

12、),C:C) 16 第二章 SUMIF函数示例之五l对满足多个条件的值同时进行汇总：如在产成品差异分摊结转表中，其他存货金额包含了在制品、半制品、产成品和在途商品，现对这四个会计科目同时进行汇总。用科目代码为满足条件，则sumifsumif函数中的Range=BRange=B列，CriteriaCriteria为四个会计科目的代码用数组表示“12410101”,“12420101”,“12430101”,“12900102” “12410101”,“12420101”,“12430101”,“12900102” ，而Sum_RangeSum_Range为需要进行汇总的D D列。此处由于是要多对

13、个会计科目同时进行求和，所以在sumifsumif函数结束后再需用sumsum函数对其进行汇总。功能讲解公式= SUM(SUMIF(SUM(SUMIF(科目余额表科目余额表-201213!B:B,12410101,12420101,12430101,12900102,-201213!B:B,12410101,12420101,12430101,12900102,科目科目余额表余额表-201213!G:G)-201213!G:G) 17 第二章 SUMIF函数示例之五功能讲解 思考：模糊条件汇总与多个条件值同时进行汇总的关思考：模糊条件汇总与多个条件值同时进行汇总的关系系 18 第二章 Colu

14、mn函数l主要功能:返回给定引用的列标l使用格式:= COLUMN(reference)l参数说明:Reference为需要得到其列标的单元格或单元格区域。如果省略reference，则假定函数COLUMN是对所在单元格的引用。如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则COLUMN函数将reference中的列标以水平数组的形式返回l应用示例:公式“=COLUMN(A3)”返回1，公式=COLUMN(B3:C5)返回2功能讲解 19 第二章 Row函数l主要功能:返回给定引用的行号l使用格式:= ROW(reference)l参数说明:Reference为

15、需要得到其行号的单元格或单元格区域l应用示例:公式“=ROW(A6)”返回6，如果在C5单元格中输入公式“=ROW()”，其计算结果为5功能讲解 思考：Column和Row返回单元格的行和列号在实际上有什么用处？ 20 第二章 Trim函数l主要功能:除了单词之间的单个空格外，清除文本中的所有的空格l使用格式:= TRIM(text)l参数说明:Text是需要清除其中空格的文本l应用示例:如果A1= FirstQuarterEarnings ，则公式“=TRIM(A1)”返回“FirstQuarterEarnings”，A1A1单元格内容中首未空格被剔除。功能讲解 特别说明：此函数主要用在用特

16、别说明：此函数主要用在用VlookupVlookup检索某个文本值是否一致检索某个文本值是否一致时，由于要检索的单元格文本含有人眼所无法直接判断出的字符，时，由于要检索的单元格文本含有人眼所无法直接判断出的字符，导致导致VlookupVlookup检索失败的情况。检索失败的情况。 21 第二章 Clean函数l主要功能:删除文本中不能打印的字符，主要是单元格在自动换行中留有空行的情况l使用格式:= CLEAN(text)l参数说明:Text为要从中删除不能打印字符的任何字符串l应用示例:如Clean剔除自动换行空格表中A1=1=B2=Clean(A1)B2=Clean(A1)后剔除了自动换行时

17、所留下的空行, ,则显示为功能讲解 22 第二章 Indirect函数l主要功能:返回由文字串指定的引用, ,此函数立即对引用进行计算并显示其内容。l使用格式:= INDIRECT( INDIRECT(ref_textref_text，a1)a1)l参数说明:Ref_textRef_text是对单元格的引用，此单元格可以包含A1A1样式的引用、R1C1R1C1样式的引用、定义为引用的名称或对文字串单元格的引用;A1;A1为一逻辑值，指明包含在单元格ref_textref_text中的引用的类型。如果a1a1为TRUETRUE或省略，ref_textref_text被解释为A1-A1-样式的引用

18、。如果a1a1为FALSEFALSE，ref_textref_text被解释为R1C1-R1C1-样式的引用。l应用示例: 具体示例参见科目余额表-格式整理模板表。功能讲解 23 第二章 Indirect函数功能讲解公式= INDIRECT(SAPINDIRECT(SAP科目余额表科目余额表!F&ROW()!F&ROW()* *2+1)2+1) C列单元格公式列单元格公式 24 第二章 Indirect函数功能讲解公式= VALUE(TRIM(INDIRECT(SAPVALUE(TRIM(INDIRECT(SAP科目余额科目余额表表!J&ROW()!J&ROW()* *2+1)2+1) D列单元格公式列单元格公式 25 第三章综合示例 根据上述所讲到的函数根据上述所讲到的函数,建立一张建立一张Excel查询表查询表,使使其能根据所选择的月份来查询到