6Excel在财务与管理中的高效运用PPT课件(35页PPT)

1、Excel在财务与管理中的高效运用韩小良第1页，共35页。韩小良 老师 简介韩小良，资深实战型Excel培训讲师、应用解决方案专家和管理系统软件开发设计师，对Excel及Excel VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用。韩老师具有丰富的实际管理经验和极强的Excel应用开发能力。从事Excel研究及应用多年，已经为数百家中外企业进行了Excel培训和应用方案解决咨询服务，出版了近30余部关于Excel应用方面的著作，并陆续开发出了财务、销售

2、、人力资源、工资等管理、客户管理、进销存等基于Excel的管理软件，成功应用于数十家企业。主要服务客户包括：中国银行、中国民生银行、浦发银行南京分行、上海农业商业银行、深圳联合证券、百度总部、 Google(中国)总部、上海医药集团、上海万家基金、上海电信、上海太平洋资产管理公司、索尼爱立信、中储股份、北京国华能源投资、浙江中烟集团、天津索恩照明、天津Vestas风力技术、卓伦机械(天津)、北京奥瑞金种业、苏州瀚得汽车产品、苏州珈农生化(丁家宜)、苏州赛发过滤科技、强生医疗器械（苏州）、上海海斯特叉车、苏州GIF研发中心、无锡华润上华科技、中化(宁波)集团、伟福科技工业(武汉)、贵州银燕集团、

3、江苏交通科学研究院、南瑞集团、南京劲草时装、南京华德火花塞、上海麦考林国际邮购、常州瓦卢瑞克钢管、广东欧普照明、广东移动、上海晶澳太阳能光伏、上海博莱科信谊药业、上海SKF、嘉兴阿克苏诺贝尔涂料、扬州亚普汽车部件、江苏德邦化工集团、南京朗诗集团、金螳螂、郑州煤机股份、上海三菱电梯、豪森药业、辉瑞制药、扬州太平洋重工、南益地产集团、圣戈班、等等数百家中外企业。韩小良第2页，共35页。韩小良老师的主要著作1.用图表说话：Excel实用精美图表大制作，电子工业出版社2.Excel数据透视表从入门到精通，中国铁道出版社3.Excel高效办公应用大全：方法、技巧及实际案例，中国铁道出版社4.Excel

4、2003/2007函数和公式的使用艺术，中国铁道出版社5.Excel VBA (2003/2007)高效办公实用宝典，中国铁道出版社6.Excel VBA( 2003/2007)活用范例大辞典，中国铁道出版社7.运用Excel VBA进行高效投资决策，中国铁道出版社8.Excel VBA从入门到精通，中国铁道出版社9.Excel VBA实用技巧大全，中国铁道出版社10.Excel VBA与数据库整合应用范例精解，科学出版社11.Excel VBA销售管理系统开发入门与实践，科学出版社12.Excel VBA整合数据库应用从基础到实践，电子工业出版社13.Excel在销售与财务管理中的应用，电子

5、工业出版社14. Excel企业管理应用案例精萃，电子工业出版社15.Excel VBA应用开发(技巧精华版)，电子工业出版社韩小良第3页，共35页。韩小良老师的主要著作(续)16.Excel +SQL Server数据库管理技术详解，机械工业出版社17.Excel在纳税管理与筹划中的应用，科学出版社18.Excel会计应用范例精解，电子工业出版社19.Excel VBA +Access财务系统开发从基础到实践，电子工业出版社20.Excel在投资理财中的应用，电子工业出版社21.Excel VBA工资管理应用案例详解，中国铁道出版社22.Excel在财务管理与分析中的应用，中国水利水电道出版

6、社23.运用Excel VBA创建高效财务管理模型，中国铁道出版社24.Excel VBA行政与人力资源管理应用案例详解，中国铁道出版社25.Excel VBA财务管理应用案例详解，中国铁道出版社26.Excel 2003/2007数据透视表 从入门到精通 ，中国铁道出版社27.Excel 高效管理表单设计从入门到精通，中国铁道出版社28.Excel 人力资源高效管理从入门到精通，中国铁道出版社29.Excel 数据分析之道：让分析报告更有说服力，中国铁道出版社韩小良第4页，共35页。韩小良老师的经典课程Excel高效财务管理：方法、技巧和实用模型Excel高效人力资源管理：方法、技巧和实用模

7、型Excel高效管理应用：方法、技巧和实用模型让你的分析报告更有说服力Excel高效数据处理与分析Excel及Excel VBA高级金融建模Excel VBA快速入门与实战演练Excel与数据库整合应用：方法、技巧和实际案例韩小良第5页，共35页。课程目录实用高效的数据整理技巧常用函数的高效运用快速汇总多个工作簿和工作表数据数据透视表工具的灵活应用其他应用韩小良第6页，共35页。实用高效的数据整理技巧快速修改非法日期快速转换文本型数字快速删除数据中的特殊字符快速分列数据快速填充数据 韩小良第7页，共35页。快速修改非法日期日期是特殊的数字，是从1开始的序列号。日期永远是大于零的正整数。正确输入

8、日期正确输入日期：2010-11-17，或者 2010/11/17错误的输入日期方式：2010.11.17，或者 20101118很多情况下，从系统导入的日期数据是文本，并不是真正的日期，必须进行修改（案例01）韩小良第8页，共35页。快速转换文本型数字在有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，也无法利用数据透视表等工具进行分析，需要转换为纯数字。方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）方法5：利用分列工具案例02 将文本型数字转化为纯数字韩小

9、良第9页，共35页。快速删除数据中的特殊字符数据中的很多“垃圾”字符会影响到数据的处理和分析，应当删除。比如：删除数据中的空格删除字符中的看不见、打印不出的特殊字符（案例03）韩小良第10页，共35页。快速分列数据有些情况下，从数据库导入的数据是一列数据，需要根据实际情况进行分列。方法方法1：使用“分列”工具方法2：实用文本函数案例04 数据分列 分列前的数据分列后的数据韩小良第11页，共35页。快速填充数据有些时候，从系统导入的数据表格有大量的空白单元格，需要进行填充。根据具体情况：填充为上一行的数据填充为数字0案例05 快速填充数据韩小良第12页，共35页。常用函数的高效运用一个表格中，处

10、理分析最多的是数据求和汇总和数据查询，因此必须熟练掌握相关求和汇总函数和查找函数的使用。高效求和汇总数据的灵活查询韩小良第13页，共35页。高效求和汇总低效率的加减公式，既不科学，也容易出错。在很多情况下，可以使用有关的求和函数来解决。常用的求和函数SUM函数：无条件求和。但SUM函数还有另类用法，比如快速计算表格中的小计总和 ； 快速对多个工作表求和SUMIF 函数：单条件求和=SUMIF(条件判断区域，条件值，实际求和区域)SUMPRODUCT 函数：计算乘积和，可以用于多条件求和与多条件计数= SUMPRODUCT(数组1，数组2，数组3，.)SUBTOTAL 函数：对数据进行分类汇总，

11、主要用于筛选和列表 = SUBTOTAL(函数类型，计算区域1，计算区域2，.)案例06 高效求和汇总韩小良第14页，共35页。数据的灵活查询查找函数有很多，其中使用最多的是VLOOKUP函数、INDEX函数、MATCH函数、INDIRECT函数。VLOOKUP函数：根据首列数据进行查询（案例07）=VLOOKUP(查找依据，查找区域，指定列位置，逻辑值)INDEX函数：根据行索引和列索引进行查询（案例08）= INDEX(查找区域，指定行位置，指定列位置)MATCH函数：对数据进行定位（案例08）= MATCH(查找依据，指定行位置，指定列位置) 韩小良第15页，共35页。快速汇总多个工作簿

12、和工作表数据快速汇总多个结构完全相同的工作表 快速汇总结构不同的多个工作表（特殊情况）快速汇总结构不同的多个工作表（一般情况）快速汇总多个有关联的工作表 韩小良第16页，共35页。快速汇总多个结构完全相同的工作表当多个工作表的结构相同时，可以使用合并计算工具快速进行合并计算，同时还可以创建分类汇总和分级显示。案例研究:案例09 快速合并多个结构相同的分报表(当前工作簿)案例10 快速合并多个结构相同的分报表(不同工作簿) 韩小良第17页，共35页。快速汇总结构不同的多个工作表（特殊情况）利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相

13、关的统计分析。注意：要汇总的多个工作表数据行数和列数可以不同，但从数据区域的第2列开始必须是数字。案例11 快速汇总多个工作表数据(特殊情况) 三个分公司数据汇总到一张工作表韩小良第18页，共35页。快速汇总结构不同的多个工作表（一般情况）一般情况下，要汇总的各个工作表数据可能含有多列文本，此时就不能采用普通的多重合并计算数据区域的数据透视表来进行汇不总了。最好的解决方法：使用导入外部数据+SQL语句。案例12 快速汇总多个工作表数据(一般情况) 韩小良第19页，共35页。快速汇总多个有关联的工作表有些情况下，几个工作表中分别保存不同的数据，但他们通过一个关键列数据（比如工号）联系起来。要将这

14、样几个相关联的工作表数据进行汇总，可使用Microsoft Query。案例13 快速汇总多个有关联的工作表数据 韩小良第20页，共35页。数据透视表的灵活应用数据透视表的基本概念数据透视表的类型准备工作创建数据透视表的基本方法和注意事项设置数据透视的格式利用数据透视表快速编制各类统计分析报表综合应用1：对比分析两个表格数据综合应用2：进销存数据查询韩小良第21页，共35页。数据透视表的基本概念什么是数据透视表？数据透视表能做什么？怎样灵活使用数据透视表？韩小良第22页，共35页。数据透视表的类型普通区域数据透视表合并区域数据透视表外部数据源的数据透视表OLAP数据透视表韩小良第23页，共35

15、页。准备工作制作数据透视表的数据区域必须是数据清单数据区域第一行为列标题列标题不能重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等韩小良第24页，共35页。创建数据透视表的基本方法和注意事项利用数据透视表向导（案例14）按照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数据区域名称解决方法：（1）利用导入数据方法；（2）利用OFFSET函数定义动态名称利

16、用导入数据方法按照导入数据向导进行操作优点：可以实现数据源的动态更新；可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点：删除数据项目后，会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点：不用导入数据，就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表韩小良第25页，共35页。设置数据透视的的格式在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总取消字段的分类汇总让数据透视表数据按照某一字段进行排序合并数据标志设置

17、错误值的显示方式更新数据透视表的数据韩小良第26页，共35页。利用数据透视表快速编制各类统计分析报表布局透视表设置字段的分类汇总方式（案例14）设置字段的显示方式（案例14）自定义数据透视表（案例14）组合字段（案例15）制作明细表快速汇总大量工作表数据韩小良第27页，共35页。综合应用1：对比分析两个表格数据两个工作表有什么不同？如何快速把两个工作表的差异数据找出来？案例16 对比分析两个表格数据 韩小良第28页，共35页。综合应用2：进销存数据查询如何快速了解各个物料、商品等的入库、出库以及库存情况？利用数据透视表，可以非常容易地制作出进销存数据查询表格。案例17 进销存数据查询韩小良第2

18、9页，共35页。其他应用图表制作的注意事项和典型图表演示宏和VBA简介Excel报表和图表与Word/PPT的链接 韩小良第30页，共35页。图表制作的注意事项和典型图表演示用图表准确表达出你的观点绘制图表基本方法及注意事项图表的修饰与美化用组合图表表达更加复杂的信息让图表按照您的要求灵活显示案例18 组合图表演示案例案例19 动态图表演示案例韩小良第31页，共35页。宏和VBA简介什么是宏？什么是VBA？如何学习宏和VBA？宏和VBA的应用：提高数据分析效率（案例演示）开发自己的管理系统（案例演示）韩小良第32页，共35页。Excel报表和图表与Word/PPT的链接Excel报表和图表与Word和PPT的链