某公司人员管理知识分析及财务培训(30页PPT).pptx

《某公司人员管理知识分析及财务培训(30页PPT).pptx》由会员分享，可在线阅读，更多相关《某公司人员管理知识分析及财务培训(30页PPT).pptx（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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代表逻辑判断表达式;Value_i

3、f_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列的值）；Range_lookup为一逻辑值，如果为TR

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

6、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年度各月发生的现金流量表进行按月汇总。思考:如果SUMSUM内的表格之间的位置发生变化会出现什么结果?公式=SUM

7、(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应用示例:由于此函数应用较为广泛,故重点列举此函数的五种常用用法来举例。12 第二章 SUMI

8、F函数示例之一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对满足条件行的求和：如对表名为SumifSumif对行求和中的数据分别按20122012年度和20132013年度进行汇总，则sumifsumif

9、函数中的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:$AK3)SUMIF($B$2:$AK$2,AN$2,$B3:$AK3)14 第二章 SUMIF函数示例之三l对满足模糊条件的值进行汇总：如在表名为SumifSumif模糊条件汇总的表中，对料号包含F38F38物料进行汇总。则sumifsumif函数中的Range=ARange

10、=A列，CriteriaCriteria为“F38*F38*”，而Sum_RangeSum_Range为需要进行汇总的列,如要汇总数量则为C C列，要汇总金额则为D D列。公式=SUMIF(A:A,F38*,D:D)SUMIF(A:A,F38*,D:D)如果不如果不直接输直接输入入F38*F38*而用单而用单元格的元格的数值来数值来替换怎替换怎么弄？么弄？15 第二章 SUMIF函数示例之四l对满足日期条件的值进行汇总：如在表名为SumifSumif对日期条件汇总的表中，对日期大于20112011年5 5月5 5日的质量成本进行汇总。则sumifsumif函数中的Range=ARange=A列

11、，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,5),CSUMIF(A:A,&DATE(2011,5,5),C:C):C)16 第二章 SUMIF函数示例之五l对满足多个条件的值同时进行汇总：如在产成品差异分摊结转表中，其他存货金额包含了在制品、半制品、产成品和在途商品，现对这四个会计科目同时进行汇总。用科目代码为满足

12、条件，则sumifsumif函数中的Range=BRange=B列，CriteriaCriteria为四个会计科目的代码用数组表示“12410101”,“12420101”,“12430101”,“12900102”“12410101”,“12420101”,“12430101”,“12900102”，而Sum_RangeSum_Range为需要进行汇总的D D列。此处由于是要多对个会计科目同时进行求和，所以在sumifsumif函数结束后再需用sumsum函数对其进行汇总。公式=SUM(SUMIF(SUM(SUMIF(科目余额表科目余额表-201213!B:B,12410101,124201

13、01,12430101,12900102,-201213!B:B,12410101,12420101,12430101,12900102,科目余额表科目余额表-201213!G:G)201213!G:G)17 第二章 SUMIF函数示例之五 思考：模糊条件汇总与多个条件值同时进行汇总的关系思考：模糊条件汇总与多个条件值同时进行汇总的关系 18 第二章 Column函数l主要功能:返回给定引用的列标l使用格式:=COLUMN(reference)l参数说明:Reference为需要得到其列标的单元格或单元格区域。如果省略reference，则假定函数COLUMN是对所在单元格的引用。如果refe

14、rence为一个单元格区域，并且函数COLUMN作为水平数组输入，则COLUMN函数将reference中的列标以水平数组的形式返回l应用示例:公式“=COLUMN(A3)”返回1，公式=COLUMN(B3:C5)返回2 19 第二章 Row函数l主要功能:返回给定引用的行号l使用格式:=ROW(reference)l参数说明:Reference为需要得到其行号的单元格或单元格区域l应用示例:公式“=ROW(A6)”返回6，如果在C5单元格中输入公式“=ROW()”，其计算结果为5 思考：Column和Row返回单元格的行和列号在实际上有什么用处？20 第二章 Trim函数l主要功能:除了单词

15、之间的单个空格外，清除文本中的所有的空格l使用格式:=TRIM(text)l参数说明:Text是需要清除其中空格的文本l应用示例:如果A1=FirstQuarterEarnings ，则公式“=TRIM(A1)”返回“FirstQuarterEarnings”，A1A1单元格内容中首未空格被剔除。特别说明：此函数主要用在用特别说明：此函数主要用在用VlookupVlookup检索某个文本值是否一致时，由于要检索某个文本值是否一致时，由于要检索的单元格文本含有人眼所无法直接判断出的字符，导致检索的单元格文本含有人眼所无法直接判断出的字符，导致VlookupVlookup检索失检索失败的情况。败的

16、情况。21 第二章 Clean函数l主要功能:删除文本中不能打印的字符，主要是单元格在自动换行中留有空行的情况l使用格式:=CLEAN(text)l参数说明:Text为要从中删除不能打印字符的任何字符串l应用示例:如Clean剔除自动换行空格表中A1=1=B2=Clean(A1)B2=Clean(A1)后剔除了自动换行时所留下的空行,则显示为 22 第二章 Indirect函数l主要功能:返回由文字串指定的引用,此函数立即对引用进行计算并显示其内容。l使用格式:=INDIRECT(ref_text INDIRECT(ref_text，a1)a1)l参数说明:Ref_textRef_text是对

17、单元格的引用，此单元格可以包含A1A1样式的引用、R1C1R1C1样式的引用、定义为引用的名称或对文字串单元格的引用;A1;A1为一逻辑值，指明包含在单元格ref_textref_text中的引用的类型。如果a1a1为TRUETRUE或省略，ref_textref_text被解释为A1-A1-样式的引用。如果a1a1为FALSEFALSE，ref_textref_text被解释为R1C1-R1C1-样式的引用。l应用示例:具体示例参见科目余额表-格式整理模板表。23 第二章 Indirect函数公式=INDIRECT(SAPINDIRECT(SAP科目余额表科目余额表!F&ROW()*2+1)

18、!F&ROW()*2+1)C列单元格公式列单元格公式 24 第二章 Indirect函数公式=VALUE(TRIM(INDIRECT(SAPVALUE(TRIM(INDIRECT(SAP科目余额表科目余额表!J&ROW()*2+1)!J&ROW()*2+1)D列单元格公式列单元格公式 25 第三章 根据上述所讲到的函数根据上述所讲到的函数,建立一张建立一张Excel查询表查询表,使其能根使其能根据所选择的月份来查询到利润表的预算金额和实际发生金额据所选择的月份来查询到利润表的预算金额和实际发生金额,并计算其差额和达标率并计算其差额和达标率.26 第三章 27 第三章 根据上述所讲到的函数根据上

19、述所讲到的函数,建立一张建立一张Excel查询表查询表,使其能根使其能根据所选择的月份来查询到利润表的预算金额和实际发生金据所选择的月份来查询到利润表的预算金额和实际发生金额额,并计算其差额和达标率并计算其差额和达标率.想一想:是否还有其他的函数或方法能达到想要的结果?28 l表格和数据应尽可能的规范，比如相同行或列数据格式要保持一致表格和数据应尽可能的规范，比如相同行或列数据格式要保持一致,整整个表格中尽量不要有合并单元格。个表格中尽量不要有合并单元格。l善于发现数据的规律性，学会挖掘各关联数据间的逻辑关系。善于发现数据的规律性，学会挖掘各关联数据间的逻辑关系。Excel公式和程序能够大量处

20、理的数据，但前提必须是这些数据或数据之间公式和程序能够大量处理的数据，但前提必须是这些数据或数据之间具有某种规律性。具有某种规律性。l了解函数的基本用法是基础，灵活运用各函数创建彼此间的逻了解函数的基本用法是基础，灵活运用各函数创建彼此间的逻辑关系是核心。要做到这一点需要多想、多练才能熟能生巧。辑关系是核心。要做到这一点需要多想、多练才能熟能生巧。补拙拙精于精于诚思思勤勤能能课件制作谢谢观看Office资源宝库1、有时候读书是一种巧妙地避开思考的方法。11月-2211月-22Monday,November 7,20222、阅读一切好书如同和过去最杰出的人谈话。11:58:3111:58:311

21、1:5811/7/2022 11:58:31 AM3、越是没有本领的就越加自命不凡。11月-2211:58:3111:58Nov-2207-Nov-224、越是无能的人，越喜欢挑剔别人的错儿。11:58:3111:58:3111:58Monday,November 7,20225、知人者智，自知者明。胜人者有力，自胜者强。11月-2211月-2211:58:3111:58:31November 7,20226、意志坚强的人能把世界放在手中像泥块一样任意揉捏。07 十一月 202211:58:31 上午11:58:3111月-227、最具挑战性的挑战莫过于提升自我。十一月 2211:58 上午1

22、1月-2211:58November 7,20228、业余生活要有意义，不要越轨。2022/11/7 11:58:3111:58:3107 November 20229、一个人即使已登上顶峰，也仍要自强不息。11:58:32 上午11:58 上午11:58:3211月-2210、你要做多大的事情，就该承受多大的压力。11/7/2022 11:58:32 AM11:58:3207-11月-2211、自己要先看得起自己，别人才会看得起你。11/7/2022 11:58 AM11/7/2022 11:58 AM11月-2211月-2212、这一秒不放弃，下一秒就会有希望。07-Nov-2207 November 202211月-2213、无论才能知识多么卓著，如果缺乏热情，则无异纸上画饼充饥，无补于事。Monday,November 7,202207-Nov-2211月-2214、我只是自己不放过自己而已，现在我不会再逼自己眷恋了。11月-2211:58:3207 November 202211:58谢谢大家谢谢大家