EXCEL在财务管理中的应用

EXCEL在财务管理中的应用为什么EXCEL对财务人员如此重要•财务人员每天面对大量数据和信息，大部分的数据都将通过EXCEL处理•决策和分析需要用数据（事实）说话•EXCEL是帮助财务人员进行数据处理的强大通用工具•用好EXCEL意味着：•大大减少工作量和劳动强度•节省时间去处理更为重要的任务•及时快速的决策支持•在下属和上司面前树立自己的技术权威致力于财务管理实践，打造卓越财务经理人！提升Excel操作水平的快捷方法•掌握财务工作中常用的快捷键•掌握财务工作中常用的菜单项•熟悉财务工作中常用的函数和公式•双手操作，键盘鼠标同上阵•模块一：常用函数的运用•模块二：透视表的运用•模块三：图表的制作•模块四：EXCEL与PPT的结合运用课程安排模块一：实用函数•财务部常用函数介绍•实用案例：VLOOKUP的巧用•实用案例：工资个人所得税计算的技巧•实用案例：补充不完整的数据表•实用案例：三招搞定excel单﹨多条件求和•Year():取出单元格中日期字符的年份•Month()：取出单元格中日期字符的月份•Now():取出当天日期，也可以用函数TODAY()代替•想想看，制作应收账款余额表时,日期函数有什么用？Now()\Date()?常用函数之日期篇致力于财务管理实践，打造卓越财务经理人！•Sum()：数值求和•Sum函数的快捷键是”ALT”+”+”•Sumproduct():利用数组进行数值求和•Sumif()：有条件的数值求和•Count():数目求和•Countif():有条件的数目求和常用函数之统计篇致力于财务管理实践，打造卓越财务经理人！Vlookup()：在表格或数值数组的首列查找指定的数值（可返回一列，也可利用数组返回多列，并由此返回表格或数组当前行中指定列处的数值。用于条件定位，是财务工作中使用频率极高的一个函数。如果使用数组的话可以一次找出多列结果.常用函数搜索篇致力于财务管理实践，打造卓越财务经理人！巧用VLOOKUP查找，替换数据•编制的基础是从财务软件中取得的应收帐款科目的明细记录清单。要得到所需要的输出格式，我们还需要增加以下四个字段的信息：帐龄，客户名称，销售经理和销售渠道巧用VLOOKUP查找，替换数据•VLOOKUP的原理就是根据两张表（源表，目标表）共有的索引列来把源表的指定列引用到目标表格上。接下来我们使用VLOOKUP函数将供应商名称从订单信息表中查找到并填写到当前表格上去。你需要根据哪个索引列查找？确定进行要在源表上进行查找的数据区矩形区域指定引用列在在矩形区域中所处的列数，以索引列为第1列默认为0或FALSE，表示绝对匹配巧用VLOOKUP查找，替换数据•索引列的唯一性：索引列的重复将导致系统只引用首先找到的（通常从上往下的第一个）值，从而丢失数据•绝对引用：引用源表的索引区域时一定记得加$，否则可能无法查到数据•索引列：字符前后无空格，其数据类型两张表必须保持一致，否则查找不到，可以运用TRIM（）函数解决VLOOKUP使用时容易犯的三大错误巧用VLOOKUP查找，替换数据•连续输入三次VLOOKUP函数可以完成以上三列，有没有一次搞定的好方法呢？•如果需要引用的不是三列，而是十三列，三十列，那么即便一次次的输入VLOOKUP函数也会让人抓狂，财务人员需要更强大的VLOOKUP函数使用方法VLOOKUP的进阶使用：一次搞定N列！相对引用和绝对引用相对引用、绝对引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。具体情况举例说明：1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1当将公式复制到C2单元格时变为：=A2+B2当将公式复制到D1单元格时变为：=B1+C12、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1当将公式复制到C2单元格时仍为：=$A$1+$B$1当将公式复制到D1单元格时仍为：=$A$1+$B$1在Excel表中创建公式时，若使用单元格引用，就意味着这个公式同工作薄中的其他单元格连接起来了，当引用单元格中的值发生变化时，公式的值也相应地发生变化。If()：可以使用函数IF对数值和公式进行条件检测And()：所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE。Or()：在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE，否则为FALSE＊判断型函数一般不会单独使用，而是与其它类型的函数构成复合函数出现。看上去很简单的函数，用得好就能化腐朽为神奇，节约你大量宝贵的工作时间。常用函数之判断篇致力于财务管理实践，打造卓越财务经理人！Find()：查找其他文本字符串(within_text)内的文本字符串(find_text)并返回find_text的起始位置编号。Left():从一个文本字符串的左边第一个字符开始，截取指定数目的字符。Right():从一个文本字符串的右边第一个字符开始，截取指定数目的字符Mid():从一个文本字符串的指定位置开始，截取指定数目的字符。Len():统计文本字符串中字符数目。Trim():把单元格字符串中前后的空格去除，常用于精确比较前的准备工作常用函数之字符篇致力于财务管理实践，打造卓越财务经理人！实用复合函数例2－补充不完整的数据表•公式法自动宏致力于财务管理实践，打造卓越财务经理人！其他部门交来的表格留下了大量的空格，相同栏目只是第一行会写名字，如果一个个手工给填上去，几千行数据，得花多少时间啦？Excel能帮我想想办法吗？实用三招搞定EXCEL单/多条件求和汇总•EXCEL中用SUM求和大家都了解。如图所示。但如果我们需要知道区域是华北的A产品销售额是多少？能不能有办法用公式/函数实现呢？致力于财务管理实践，打造卓越财务经理人！第一招：用SUMIF（）单条件求和指定条件所在的区域指定条件，可以是数值或表达式要进行汇总的数据区域致力于财务管理实践，打造卓越财务经理人！第1步第2步第3步第二招：用EXCEL自带的条件求和向导实现多条件求和第二招：用EXCEL自带的条件求和向导实现多条件求和条件求和向导需要在加载宏中加载方可运用致力于财务管理实践，打造卓越财务经理人！第二招：用EXCEL自带的条件求和向导实现多条件求和第三招：用SUM+IF实现多条件求和•求A产品30000和B产品都大于25000的分公司收入合计*代表和，改成+就代表或运用数组和用*/+连接乃是实现多条件求和的关键致力于财务管理实践，打造卓越财务经理人！=SUM(IF($C$2:$C$1430000,IF($D$2:$D$1425000,$F$2:$F$14,0),0))致力于财务管理实践，打造卓越财务经理人！QUESTION：统计出区域在华北并且A产品销售额超过20000元或B产品销售额超过15000元的分公司之销售收入总和。Tips:＊/+，（），数组的灵活组合与运用是实现多条件求和（求个数）的关键。致力于财务管理实践，打造卓越财务经理人！记住这三点，N多条件求和也不怕！+号表示条件OR/*号表示条件AND（）里面的表达式将得到优先运算然后再参与（）外的运算换成Count将可以统计符合条件的公司数量致力于财务管理实践，打造卓越财务经理人！•模块一：常用函数的运用•模块二：透视表的运用•模块三：图表的制作•模块四：EXCEL与PPT的结合运用课程安排模块二：透视表的应用•透视表的基本应用•透视表在财务报表中的综合运用对每个月预算的执行情况，财务部在月结完后都要对外提供管理报表。由于每位部门副总看问题的角度、对报表格式的要求不一致，同样的数据内容（比如应收帐款的分析），财务部总是要做出好几张报表，费时费力。有没有更好的办法来一劳永逸的解决问题呢？销售：按供应商排序、汇总…财务：按帐龄排序、汇总…人力资源：按销售经理排序、汇总………2.1透视表应用案例1.打开课堂用沙盘文件目录下的应收帐款余额表文件，选到应收帐款余额明细表，执行以上操作。2.1透视表应用案例Step1-点击插入按钮Step2-点击数据透视表2.1透视表应用案例Step3-数据选择区域确认，确保你需要的数据内容都被选上！Step4-点击确认，一般情况下我们都将透视表放在新工作表内，因此第2部分的选项不需要改动2.1透视表应用案例透视表进阶按钮区透视表字段选择区透视表字段置放区，透视表结果显示区，将字段从选择区拖拽至置放区，则结果显示区发生变化。显示结果。数据透视表选项-显示-经典数据透视表布局2.1透视表应用案例将应收帐款余额拖至此处将帐龄拖至此处将客户名额拖至此处Step4.按财务的表格要求制作透视表。（试试看将渠道拖至页字段会出现什么情况？）2.1透视表应用案例将应收帐款余额拖至此处将帐龄拖至此处将客户名额拖至此处Step4.按财务的表格要求制作透视表。（试试看将渠道拖至页字段（报表筛选）会出现什么情况？）2.1透视表应用案例Step5.表格的雏形初显，还需要进一步加工1.金额显示为千元级，小数点后两位。2.按总计金额大小降序排列。3.帐龄显示应该从左至右升序排列，方符合财务工作的习惯。求和项:应收帐款余额帐龄客户名称180Days0-30Days120-180Days30-60Days60-90Days90-120Days总计北京鑫德商贸842292.61765840.2211660.81161.62820955.2北汽福田42393754239375东风雪铁龙6287206.86287206.8丰田汽车1168507.81004641268971.8吉利汽车238336.26603841.26842177.4捷德进出口有限公司18009636330179.45512302.613643445上海通用1242334.8209664151690.82030550.63634240.2一汽大众1962120594834.6591039.6533296.83681291总计148067120966416768790.44491689.413420086.6604676142417662.42.1透视表应用案例客户名称0-30Days30-60Days60-90Days90-120Days120-180Days180Days总计捷德进出口有限公司6,330.185,512.301,800.9613,643.45吉利汽车6,603.84238.346,842.18东风雪铁龙6,287.216,287.21北汽福田4,239.384,239.38一汽大众594.83591.04533.301,962.123,681.29上海通用209.662,030.55151.691,242.333,634.24北京鑫德商贸1,765.84211.661.16842.292,820.96丰田汽车100.461,168.511,268.97总计209.664,491.6913,420.096,046.7616,768.791,480.6742,417.66%0%11%32%14%40%3%100%课堂练习：通过改变数据表的结构完成销售和人力资源要求的表格。2.1透视表应用案例数据更新和追加数据按钮万一找不到字段列表了，就点它通过增加公式字段来适用不同的报表需要，而不必改变原始数据表小贴士-记牢快捷键，不变应万变•多记住一些快捷键操作，比单纯记住菜单项上的选择界面要更有效。•Excel2007的界面比之前的版本有了巨大的变动，很多菜单项都让人感觉找不到了，但快捷键没有变。•“以不变应万变”是应付Excel版本不断更新的一个好办法。常用的透视表操作Ø修改字段名称，数据格式，汇总方式，显示方式，是否隐藏等。Ø是否隐藏，是否分类汇总，排序Ø通过公式新增计算字段Ø设置报表格式Ø数据的更新Ø不会编宏也能让EXCEL自动制作透视表致力于财务管理实践，打造卓越财务经理人！多重区域合并的透视表运用•同时对多张相同的表格进行透视表分析•应注意的事项•模块一：常用函数的运用•模块二：