企业新人内训：财务人员EXCEL培训(29P)

1、 弟一早 /r/r 竟_早 A/v -fr 弟二里 畫功能讲解函数综合应用 魁示例 三个基本概念 基本概念 相对引用 绝对引用 混合引用 -5- 相对引用：相对引用实际上是引用单元格的相对位置，即当把公式复制 到其他单元格时Z行或者列都会发生改变。如当前单元格显示为A1 , 向下复制会自动变成A2，向右复制向B1 o 绝对引用：绝对弓I用实际上是引用单元格的绝对位置，即当复制公式 时，行和列均不会发生改变。如当前单元格显示为$A$1,向下或向右 复制单元格显示的内容不变，始终还是$人$1。 混合引用：混合引用实际上是行或者列当中有一个是绝对引用，另一 个是相对引用。如单元格显示为$人1 （对列

2、进行绝对引用），即复 制公式到任何其他的单元格改变的是行而列号始终保持不变；或A$1 （对行进行绝对引用）z即复制公式到任何其他的单元格改变的 是列而 行号始终保持不变。 函数基本功能讲解包含四方面 函数主要功能函数使用格 式涉及参数说明基本用 法示例 -7 - 功能讲解 IF函数 主要功能: 公式二 IF (AND (B2 二 根据对指走条件的逻辑判断的真假结果，返回相对应的内容 I使用格式： =IF(LogicalfValueJf_truef Value Jf_false) 参数说明： Logical代表逻辑判断表达式;Valujif_true表示当判断条件为逻辑真 (True)时显示的内

3、容，如果忽略返回True ;Value_if_false表示当判 断 G2 =IF(ANl qAi B 1 D E F ssn 瑞 Pi tHT 生il 男 10 山东1 玄 1 上海 彳工 Bin 8 | 山东 目 男 1 山东 需同时满足多个条件的案u 第二章 思考:如果公司2013年对工龄超过 力能讲解 1寸满足多个条件的判 i : 5年同时属制造部门或研发部门的 员工进行加薪，那公式怎么写？ 功能讲解 Vlookup 函数 主要功能: 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值 使用格式：Vlookup(lookup_valueftable_arrayfcol

4、jndex_nurrifrangejookup)参数说 明： Lookup_valuG代表需要查找的数tt；Table_array代表需要在其中查找数据的单元格 区域；ColjndexAnum为在table_array区域中待返回的匹配值的列序号(当 ColjndexAnu m为2时，返回tabljarray第2列中的数值f为3时f返回第3列的值 )； Rangejookup为一逻辑值，如果为TRUE或省略f则返冋近似匹配值f也就是说f如果找不 到精确匹配值，则返回/JAlookup.value的最大数值；如果为FALSE 返回精确匹 配值，如 果找不到则返回错误值#11/人。 第二章 功能讲

5、解 Vlookup函数出JK!#N/A的处理 应用示例： 一般性示例略，具体解决如果找不到目标值则返回错误值#|/人的情 况。此时会涉及到另外一个函数ISNA,该函数用来判断是否存 在 公式 =IF(ISNA(VLOOKUP(II, A:B, 2, 0)/ 无此员工z, VLOOKUP(II, A:B, 字体 对齐 数字栏式 #N/A的错误值，如果存在则返回一个自己想要设定的值。如查找 公司员工所属部门，如职 姓名在查找范围内不存在的情况下的处理。 11 TAT =IF (I SNA (VLOOKUP (11, A : B, 2, O), 无此员工笃 VLOOKUP (II, A 2, 0)

6、姓名 部门 务 李鹏 制造部门 硬件组組长 张敏梁 管理部门 机械结构工程师 王科 研发部门 工程师1机器人项目） 丁信忠 制造部门 伺服软件工程师 1 2 3 4 5 AEC D闻男女女男 G_ HIJ 职工姓名：李三- 所属部门：I无此员工1 第二章 公式二SUM ( 201101:201112,! C7) L 二SMC 201101:2011123 7) -1 B L c: 目 合计 未自外部公司 39, 057, 532. 70 1 1,892,492. 66 1 的现金 5473459. 97 214, 459. 97 观金漁入小计 393 604, 992. 67 2, 106,

7、952.63 弔计兌 1 便川格武:二 SUM |Number2 Number.代表需要计算的值,可以是具体的数值.引用的 单元格（区域）逻辑值等 应用示例： 一般性示例略，重点解决多个相同格式的工作表对同一单元格进行求和 的问题，如对2013年度各月发生的现金流量表进行按月汇总。 思考:如果SUM内的表格之间的位置发生变化会岀现什么结果? 13 第二章 功能讲解 SUMIF函数 主要功能： 十算符箜定条件的单元格区域内的数值和 I使用格式：二 SUMIF ( Range, Criteria, Sum_Range ) Range代表条件判断的单元格区域，即在哪个范围内来寻找需要满足 的条 件；

8、Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格 区域。 应用示例：由于此函数应用较为广泛,故重点列举此函数的五种常用用法 来举 例。 第二章 功能讲解 SUMIF函数示例之一 对满足条件列的求和：公式二SUMIF (B:H2, E: I 如对表名为Sum辻对列求和中的数据按供应商名称进行数量和金 额汇总，则sundf函数中的Range二B列f Criteria即为需要进行汇总 的各个供应商名称二Hl f H2而 SumRange即为需要进行汇总 的E列和F列 13 12 J5cl =SUMIF(E:Bj B CD E 供货单位 上海经驰机电设备有限公司 上海

9、飞特亚空气过鴻有限公司 物料长代码物料名称 R13000620 T 01高压绝缘千 R14000139 T03-1变压器柜门过滤网 数量 50.00 100.00 F GH I亠 J 不含稅金额 供货单位 数量 不含稅金额 803.42 上海经驰机电设备有限公司匚 250.00 1 4,017.10 6, 666. 67 上海飞特亚空气过滤有限公司 3, 262. 00 235. 128.76 第二章 上海飞特亚空气过鴻有限公司 上海飞特亚空气过澹有限公司 上海飞特亚空气过鴻有限公司 R14000140 R14000141 R14000092 C03-1S单元柜过滤网 C03-1三单元柜过滤网

10、 C07三单元柜过滤网 魏德米勒电联国际贸易（上海）有限A11007809 上海怡芯高电子有限公司R11014066 预绝缘母插头（魏德米勒 纽扣电池cr!220松下小 300. 00 300. 00 100.00 1, 000. 00 120.00 20,512.82 23, 076. 92 7,521.00 230.00 328. 21 魏後米勒电联国际贸易（上海）有1 上海怡芯高电千有限公司 北京昆仑通态自动化软件科技有t 上海查尔斯电千有限公司 上海信泰印刷有限公司 15, 837. 00 3. 394. 51 360.00 984. 62 50.00 36.324.79 55, 30

11、0. 00 74. 140.12 1, 137.00 17.201.71 # 第二章 15 功能讲解 SUMIF函数示例之二 对满足条件行的求和：公式二 如对表名为Sumif对行求和中的数据分别按2012年度和2013年度SUMIF ($B$2: $AK$2, AN$2, Criteria即为需要进行汇总的各个年份二AN2或A02，而Sum_Range即 为需要进行汇总的行，如第3行，第4行等 AK3J& 二SUMIF$AK$2, AN$2, $B3:$AK3) 2 3 一、主営业务收入 4 具刊外销收人减八主 5 营业务成本 6 其中：外销成本 2012 2013 D 分析 同比増长 201

12、2 2013 G 分析 同比増长 4,463,618. T0 15,010,373.21 236.28% 14,357,928.34 6, 894, 308.03 -53.6055 引 8,950.77 1,48 乙 52N14 364.81% 4,36 久 565. 21 536 畀 94 52 3,75 乙 365 11 JII 23,691.43 215.10% 13,160,240.82 刍 025, 809.98 -54.21% 92 977 再 20.2? 169.07% 4,735,181.52 心0,12。门 -91.34 八 36玄22工的 5 2012 19,321,54

13、人 04 633,515 93 16,912,即 6 gg 5,098,405.41 ALJ AM 同期累计 2013 企 904681.24 2,019,416.66 17,冏 9501.41 匕 387 把 4IL58 A K 同期 2012 19321,54744 4, 683, 515.98* 16, 912, 606.68 5,098,405.41 第二章 17 功能讲解 SUMIF 函数示例之 IF38*，D: D） 如在表名为Sumi f模糊条件汇总的表中，对料号 含F38物料进行汇总。则sumif函数中的Range=A歹！ J , Criteria为F38* f而SumRang

14、e为需要进行汇总 的列，如要汇总数量则为c列f要汇总金额则为D列。 LT物料 物科描述 2 F36000017变频器操作器（白屏）AS.T003 3 F36000022变频器丰持撫作器（LAY6叩专评） IF36000031手特操作器（一体机用）AS.T008英文版 5 F36000035创KW以下变频器应急电源转换器（线长0 - 5M） 期末数蚩 19.00 28.00 2.00 42.00 AS.T019 n 卩能 n nnnd 斤 R 口 R 梓口 梓握作黑（白显） nn 期末金额 U 936.67 3, 009.72 220. 00 1,576. 26 g. 94化环 包含F33剋溜金

15、额 F38 17,416,4 開沉 1 妾-3书夂口直奂岸In直入 而元数替么 第二章 SUMIF函数示例之四 公式二 SUMIF (A:A, &DATE(2011, 5, 5 ),C:C) 对满足日期条件的值进行汇总： 如在表名为Sumif对日期条件汇总的表中，对日期大于2011年5月 5日的质量成本进行汇总。则sumif函数中的Range二A歹！ J r Criteria 为 ” &date (2011, 5, 5) f而而Sum_Range为需要进行汇总的C列。 E2A A 思考:如果要汇总大于2011. 5.1日且小于2011. 7. 1 曰的值怎么写？ 第二章 A = SUMIF (

16、A :A, AA&DATE(2011, 5, 5), C :C) 1 曰期 事由 质車成丰孑匚总. 2011-01-31 2代11K橢总量皮馈电杭器于接融器线接辰 希姆斯控 10. 95 玄 372, 369. 64 1 2011-01-31 制板支架役有【等料） 54. 75 n 2011-01-31 凤扇必42个散礬器少3D个（等料） 32 85 2011-02-28 1 IKVtm量反馈模块连接线接反 1CI. 95 r 7CISw 、:十R口二洋卡立生|炼不同與岸、爲非（职幺S 功能讲解 SUM1F函数示例之五 公式二 SUM(SUMIFC 科目余额表-201213 !B:B, 124

17、10101, 对满足多个条件的值同时进行汇总： 如在产成品差异分摊结转表中，其他存货金额包含了在制品、半制 品、产成品和在途商品，现对这四个会计科目同时进行汇总。用 科目代码 为满足条件，则sumif函数中的Ranged列，Criteria为四 个会计科目 的代码用数组表示 “12410101” ， “12420101” 12430101” , “12900102” f 而 Sum. Range为需要进行汇总的D列。此处由于是要多对个会计科目同时进 行求和，所以在sumif函数结束后再需用sum函数对其进行汇总。 12420101，12430101，12900102 / 科目 余额表-2012

18、13! G:G) 21 第二章 23 功能讲解 A=Sra（SraiFCTO-2012B!B:B（n 2410101 Y 12420101 Y 12430101 Y 12900102稀余额务 201213腮） D E F G iifW- 46,894,835.61 6,014,039.0? 9.85$ 88.63JS 55 48仇 90 90.15X 思考：模糊条件汇总与多个条件值同时进行汇总的关系 L 第二章 返回给定引用的列标 使用格式：二CO LU M N (reference) 参数说明： Reference为需要得到其列标的单元格或单元格区域。如果省略 reference，则假定函数

19、COLUMN是对所在单元格的引用。如果reference 为一个单元格区域f并且函数COLUMN作为水平数组输入f则COLUMN函 preference中的列标以水平数组的形式返 应用示例： 公式二COLUMN(A3)“返回:L.公式二 COLUMN(B3:C5)返回 2 18 -6T- 4早丁盘运翠言値吐関马冬由 吐 uuinco ZW宙苏 :命宙 筋番韓 44 茸 OMOg AYA*AWSDSWD * 9 回密,（9V）MOy =w :恸竺宙凹徊又爭艺审晅号N亩關言9茸底劃產番genua书 氓i：旳細摩（9Duaja|a4）MOil二:爭宙西台马朋宙IE聿応 回虫：那歪王 第二章 Trim

20、函数 除了单词之间的单个空格外V清除文本中的所有的空格 参数说明： .Text是需要清除耳中空格的文本 如果 FirstQuarterEarnings f 则公式=TRIM(Aiy 返 FirstQuarterEarnings11 Al单元格内容中首未空格被剔除。 特别说明：此函数主要用在用Vlookup检索某个文本值是否一致 第二章 =3800000 / Clean函数 主要功能： 删除文本中不能打印的字符，主要是单元格在自动换行中留有 空行的情 使用格式:二 CLEAN(text) Text为要从中删除不能打印字符的任何辜寤 应用示例： 如Clean剔除自动换行空格表中Al= B2=Cle

21、an (Al)后剔除了自动换行时所畐卞8理行，则显示为 -21 - 第二章 功能讲解 Indirect 函数 公式二 INDIRECT (SAP 科目余额表！ F &ROW () *2+1) C4 习恥压配代彗肛科目余额表！F”&ROW () *2+1) A EC 会计科目余额表 公司 总帐科目 短文本 前一期间的余额 借方余额 S200 10010101 现金人民币 10,152.91 154,153.00 S200 10020101 極彳左隸-右済钢彳宁1 2,767,882.10 11,317,269.96 S200 10021101 银行存款-工商银行城镇保险户 298.45 S200

22、 10021102 银行存款-建设银行小城镇保险户 ll.ll S200 10021103 银行弃款-建设银行综合保险户 19.85 第二章 D列单元格公式 会计科目余额表 公司 总帐科目 短艾本 前一期间的余额 借方余额 S200 10010101 现金-人民币 10.152.91 154,153.00 S200 10020101 辗行存款交通银行 2 T6 人 8820 ! 11,317, 269. 96 S200 10021101 規行存款工商银行城慣保险户 298.45 S200 10021102 银行存款-建设银行小城镇保险户 1叽 功能讲解 Indirect 函 公式二 VALUE

23、 (TRIM (INDIRECTSAP 科目 余额表!J&ROW ()*2+1) D4 匸 二 VALUE (TRIM (INDIRECT (“SAP 科目余额表！昭蚀()吃+1) # -25 - 第 根据上述所讲到的函数，建立一张Excel查询表，使 其能根据所选择的 月份来查询到利润表的预算金额和实际发生金额，并计算其差额和达标率. -31- 综合ZF例 利润差异明细比较分析表 输入查询月份 7月 项目 预算值 实际值 差异额 达标率 预算值累计额 当月族算值 实际值累计额 当月实际值 累计值差异 当月差异 累计达标率 当月达标率 营业收入 168. 062,871.20 25, 8 T9, 131.11 160s 293, 243. 47 23. 29U 218. 00 7, T69. 627. T3 2. 587,913. 11 95. 4X 90. 0X 营业成本 143, 084,482 95 23, 159,712.72 136,