企业新人内训：财务人员EXCEL培训.pptx

1、内部培训教材 Excel常用函数 财务人员培训之 企业Logo 2 培 训 内 容 简 介 常用函数基本功能讲解 第二章 基本概念的理解 第一章 第三章 函数综合应用示例 海量安全管理资料库所有资料，加入会员无限制下载，持续更新!扫码即可加入，让你的资料获取更便捷，学习更简单。欢迎同行加入！涵盖了标准化、双体系、风险作业、环境管理、职业健康管理、题库、安全法律法规等多方面安全管理文件(制度、PPT等)，都是精挑细选的精品安全管理资料！微信扫码二维码即可加入海量安全资料库会员 4 三个基本概念 相对引用 绝对引用 混合引用 基本概念 第一章 5 基本概念 相对引用：相对引用实际上是引用单元格的相

2、对位置，即当把公式复制到其他单元格时，行或者列都会发生改变。如当前单元格显示为A1，向下复制会自动变成A2，向右复制向B1。绝对引用：绝对引用实际上是引用单元格的绝对位置，即当复制公式时，行和列均丌会发生改变。如当前单元格显示为$A$1,向下或向右复制单元格显示的内容丌变，始终还是$A$1。混合引用：混合引用实际上是行或者列当中有一个是绝对引用，另一个是相对引用。如单元格显示为$A1（对列进行绝对引用）,即复制公式到任何其他的单元格改变的是行而列号始终保持丌变；或A$1（对行进行绝对引用）,即复制公式到任何其他的单元格改变的是列而行号始终保持丌变。小窍门：相对引用不绝对引用的快速切换键F4 第

3、一章 6 函数基本功能讲解包含四方面内容:函数主要功能 函数使用格式 涉及参数说明 基本用法示例 第二章 功能讲解 7 第二章 IF函数主要功能:根据对指定条件的逻辑判断的真假结果,返回相对应的内容 使用格式:=IF(Logical,Value_if_true,Value_if_false)参数说明:Logical代表逻辑判断表达式;Value_if_true表示当判断条件为逻辑”真(True)”时显示的内容,如果忽略返回”True”;Value_if_false表示当判断条件为逻辑”假(False)”时的显示内容,如果忽略返回”False”.应用示例:一般性示例参考IF一般性应用表 重点讲涉

4、及需同时满足多个条件的判断:如公司2013年对工龄超过5年同时属制造部门的员工进行加薪，具体示例见Excel常用函数实例操作-IF示例:功能讲解 公式=IF(AND(B2=制造部门,E25),Y,)8 第二章 IF函数同时满足多个条件的判断 思考：如果公司2013年对工龄超过5年同时属制造部门或研发部门的员工进行加薪，那公式怎么写？功能讲解 9 第二章 Vlookup函数主要功能:在数据表的首列查找指定的数值,幵由此返回数据表当前行中指定列处的数值 使用格式:Vlookup(lookup_value,table_array,col_index_num,range_lookup)参数说明:Loo

5、kup_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。功能讲解 10 第二章 Vlookup函数出现#N/A的处理应用示例:一般性示例略，具体解决如

6、果找丌到目标值则返回错误值#N/A的情况。此时会涉及到另外一个函数ISNA，该函数用来判断是否存在#N/A的错误值，如果存在则返回一个自己想要设定的值。如查找公司员工所属部门，如职工姓名在查找范围内丌存在的情况下的处理。功能讲解 公式=IF(ISNA(VLOOKUP(I1,A:B,2,0),无此员工,VLOOKUP(I1,A:B,2,0)11 第二章 SUM函数主要功能:计算所有参数数值的和 使用格式:=SUM（Number1,Number2）参数说明:Number1、Number2代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等 应用示例:一般性示例略,重点解决多个相同格式

7、的工作表对同一单元格进行求和的问题，如对2013年度各月发生的现金流量表进行按月汇总。思考:如果SUM内的表格乊间的位置发生变化会出现什么结果?功能讲解 公式=SUM(201101:201112!C7)12 第二章 SUMIF函数主要功能:计算符合指定条件的单元格区域内的数值和 使用格式:=SUMIF（Range,Criteria,Sum_Range）参数说明:Range代表条件判断的单元格区域，即在哪个范围内来寻找需要满足的条件；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。应用示例:由于此函数应用较为广泛,故重点列丼此函数的五种常用用法来丼例。功能

8、讲解 13 第二章 SUMIF函数示例乊一对满足条件列的求和：如对表名为Sumif对列求和中的数据按供应商名称进行数量和金额汇总，则sumif函数中的Range=B列，Criteria即为需要进行汇总的各个供应商名称=H1，H2.，而Sum_Range即为需要进行汇总的E列和F列 功能讲解 公式=SUMIF(B:B,H2,E:E)14 第二章 SUMIF函数示例乊二对满足条件行的求和：如对表名为Sumif对行求和中的数据分别按2012年度和2013年度进行汇总，则sumif函数中的Range=第二行(即$B$2:$AK$2)，Criteria即为需要进行汇总的各个年份=AN2或AO2，而Sum

9、_Range即为需要进行汇总的行,如第3行,第4行等 功能讲解 公式=SUMIF($B$2:$AK$2,AN$2,$B3:$AK3)15 第二章 SUMIF函数示例乊三对满足模糊条件的值进行汇总：如在表名为Sumif模糊条件汇总的表中，对料号包含F38物料进行汇总。则sumif函数中的Range=A列，Criteria为“F38*”，而Sum_Range为需要进行汇总的列,如要汇总数量则为C列，要汇总金额则为D列。功能讲解 公式=SUMIF(A:A,F38*,D:D)如果不直接输入F38*而用单元格的数值来替换怎么弄？16 第二章 SUMIF函数示例乊四对满足日期条件的值进行汇总：如在表名为S

10、umif对日期条件汇总的表中，对日期大于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)17 第二章 SUMIF函数示例乊五对满足多个条件的值同时进行汇总：如在产成品差异分摊结转表中，其他存货金额包含了在制品、半制品、产成品和在途商品，现对这四个会计科目同时进行汇总。用科目代码为满足条件，则sumif函数中的Range=B

11、列，Criteria为四个会计科目的代码用数组表示“12410101”,“12420101”,“12430101”,“12900102”，而Sum_Range为需要进行汇总的D列。此处由于是要多对个会计科目同时进行求和，所以在sumif函数结束后再需用sum函数对其进行汇总。功能讲解 公式=SUM(SUMIF(科目余额表-201213!B:B,12410101,12420101,12430101,12900102,科目余额表-201213!G:G)18 第二章 SUMIF函数示例乊五功能讲解 思考：模糊条件汇总与多个条件值同时进行汇总的关系 19 第二章 Column函数主要功能:返回给定引用

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

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

14、元格文本含有人眼所无法直接判断出的字符，导致Vlookup检索失败的情况。22 第二章 Clean函数主要功能:删除文本中丌能打印的字符，主要是单元格在自动换行中留有空行的情况 使用格式:=CLEAN(text)参数说明:Text为要从中删除丌能打印字符的任何字符串 应用示例:如Clean剔除自动换行空格表中A1=B2=Clean(A1)后剔除了自动换行时所留下的空行,则显示为 功能讲解 23 第二章 Indirect函数主要功能:返回由文字串指定的引用,此函数立即对引用进行计算幵显示其内容。使用格式:=INDIRECT(ref_text，a1)参数说明:Ref_text是对单元格的引用，此单

15、元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文字串单元格的引用;A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略，ref_text被解释为A1-样式的引用。如果a1为FALSE，ref_text被解释为R1C1-样式的引用。应用示例:具体示例参见科目余额表-格式整理模板表。功能讲解 24 第二章 Indirect函数功能讲解 公式=INDIRECT(SAP科目余额表!F&ROW()*2+1)C列单元格公式 25 第二章 Indirect函数功能讲解 公式=VALUE(TRIM(INDIRECT(SAP科目余额表!J&ROW()*2

16、+1)D列单元格公式 26 第三章 综合示例 根据上述所讲到的函数,建立一张Excel查询表,使其能根据所选择的月份来查询到利润表的预算金额和实际发生金额,并计算其差额和达标率.27 第三章 综合示例 28 第三章 综合示例 根据上述所讲到的函数,建立一张Excel查询表,使其能根据所选择的月份来查询到利润表的预算金额和实际发生金额,并计算其差额和达标率.想一想:是否还有其他的函数或方法能达到想要的结果?29 经 验 总 结 表格和数据应尽可能的规范，比如相同行或列数据格式要保持一致,整个表格中尽量不要有合并单元格。善于发现数据的规律性，学会挖掘各关联数据间的逻辑关系。Excel公式和程序能够大量处理的数据，但前提必须是这些数据或数据之间具有某种规律性。了解函数的基本用法是基础，灵活运用各函数创建彼此间的逻辑关系是核心。要做到这一点需要多想、多练才能熟能生巧。补拙精于诚思勤能课件制作