Excel在财务与管理中的高效运用.ppt

韩小良,Excel在财务与管理中的高效运用,韩小良 资深实战型 Excel 培训讲师和应用解决方案专家,韩小良,韩小良 老师 简介,韩小良，资深实战型Excel培训讲师、应用解决方案专家和管理系统软件开发设计师，对Excel及Excel VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用。韩老师具有丰富的实际管理经验和极强的Excel应用开发能力。从事Excel研究及应用多年，已经为数百家中外企业进行了Excel培训和应用方案解决咨询服务，出版了近30余部关于Excel应用方面的著作，并陆续开发出了财务、销售、人力资源、工资等管理、客户管理、进销存等基于Excel的管理软件，成功应用于数十家企业。 主要服务客户包括：中国银行、中国民生银行、浦发银行南京分行、上海农业商业银行、深圳联合证券、百度总部、 Google(中国)总部、上海医药集团、上海万家基金、上海电信、上海太平洋资产管理公司、索尼爱立信、中储股份、北京国华能源投资、浙江中烟集团、天津索恩照明、天津Vestas风力技术、卓伦机械(天津)、北京奥瑞金种业、苏州瀚得汽车产品、苏州珈农生化(丁家宜)、苏州赛发过滤科技、强生医疗器械（苏州）、上海海斯特叉车、苏州GIF研发中心、无锡华润上华科技、中化(宁波)集团、伟福科技工业(武汉)、贵州银燕集团、江苏交通科学研究院、南瑞集团、南京劲草时装、南京华德火花塞、上海麦考林国际邮购、常州瓦卢瑞克钢管、广东欧普照明、广东移动、上海晶澳太阳能光伏、上海博莱科信谊药业、上海SKF、嘉兴阿克苏诺贝尔涂料、扬州亚普汽车部件、江苏德邦化工集团、南京朗诗集团、金螳螂、郑州煤机股份、上海三菱电梯、豪森药业、辉瑞制药、扬州太平洋重工、南益地产集团、圣戈班、等等数百家中外企业。,韩小良,韩小良老师的主要著作,1.用图表说话：Excel实用精美图表大制作，电子工业出版社 2.Excel数据透视表从入门到精通，中国铁道出版社 3.Excel高效办公应用大全：方法、技巧及实际案例，中国铁道出版社 4.Excel 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在销售与财务管理中的应用，电子工业出版社 14. Excel企业管理应用案例精萃，电子工业出版社 15.Excel VBA应用开发(技巧精华版)，电子工业出版社,韩小良,韩小良老师的主要著作(续),16.Excel +SQL Server数据库管理技术详解，机械工业出版社 17.Excel在纳税管理与筹划中的应用，科学出版社 18.Excel会计应用范例精解，电子工业出版社 19.Excel VBA +Access财务系统开发从基础到实践，电子工业出版社 20.Excel在投资理财中的应用，电子工业出版社 21.Excel VBA工资管理应用案例详解，中国铁道出版社 22.Excel在财务管理与分析中的应用，中国水利水电道出版社 23.运用Excel VBA创建高效财务管理模型，中国铁道出版社 24.Excel VBA行政与人力资源管理应用案例详解，中国铁道出版社 25.Excel VBA财务管理应用案例详解，中国铁道出版社 26.Excel 2003/2007数据透视表 从入门到精通 ，中国铁道出版社 27.Excel 高效管理表单设计从入门到精通，中国铁道出版社 28.Excel 人力资源高效管理从入门到精通，中国铁道出版社 29.Excel 数据分析之道：让分析报告更有说服力，中国铁道出版社,韩小良,韩小良老师的经典课程,Excel高效财务管理：方法、技巧和实用模型 Excel高效人力资源管理：方法、技巧和实用模型 Excel高效管理应用：方法、技巧和实用模型 让你的分析报告更有说服力Excel高效数据处理与分析 Excel及Excel VBA高级金融建模 Excel VBA快速入门与实战演练 Excel与数据库整合应用：方法、技巧和实际案例,韩小良,课程目录,实用高效的数据整理技巧 常用函数的高效运用 快速汇总多个工作簿和工作表数据 数据透视表工具的灵活应用 其他应用,韩小良,实用高效的数据整理技巧,快速修改非法日期 快速转换文本型数字 快速删除数据中的特殊字符 快速分列数据 快速填充数据,韩小良,快速修改非法日期,日期是特殊的数字，是从1开始的序列号。日期永远是大于零的正整数。 正确输入日期 正确输入日期：2010-11-17，或者 2010/11/17 错误的输入日期方式：2010.11.17，或者 20101118 很多情况下，从系统导入的日期数据是文本，并不是真正的日期，必须进行修改（案例01）,韩小良,快速转换文本型数字,在有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，也无法利用数据透视表等工具进行分析，需要转换为纯数字。 方法1：利用智能标记 方法2：利用VALUE函数 方法3：利用公式（两个负号或者乘以1或除以1） 方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1） 方法5：利用分列工具 案例02 将文本型数字转化为纯数字,韩小良,快速删除数据中的特殊字符,数据中的很多“垃圾”字符会影响到数据的处理和分析，应当删除。 比如： 删除数据中的空格 删除字符中的看不见、打印不出的特殊字符（案例03）,韩小良,快速分列数据,有些情况下，从数据库导入的数据是一列数据，需要根据实际情况进行分列。 方法 方法1：使用“分列”工具 方法2：实用文本函数 案例04 数据分列,韩小良,快速填充数据,有些时候，从系统导入的数据表格有大量的空白单元格，需要进行填充。 根据具体情况： 填充为上一行的数据 填充为数字0 案例05 快速填充数据,韩小良,常用函数的高效运用,一个表格中，处理分析最多的是数据求和汇总和数据查询，因此必须熟练掌握相关求和汇总函数和查找函数的使用。 高效求和汇总 数据的灵活查询,韩小良,高效求和汇总,低效率的加减公式，既不科学，也容易出错。在很多情况下，可以使用有关的求和函数来解决。 常用的求和函数 SUM函数：无条件求和。但SUM函数还有另类用法，比如快速计算表格中的小计总和 ； 快速对多个工作表求和 SUMIF 函数：单条件求和 =SUMIF(条件判断区域，条件值，实际求和区域) SUMPRODUCT 函数：计算乘积和，可以用于多条件求和与多条件计数 = SUMPRODUCT(数组1，数组2，数组3，) SUBTOTAL 函数：对数据进行分类汇总，主要用于筛选和列表 = SUBTOTAL(函数类型，计算区域1，计算区域2，) 案例06 高效求和汇总,韩小良,数据的灵活查询,查找函数有很多，其中使用最多的是VLOOKUP函数、INDEX函数、MATCH函数、INDIRECT函数。 VLOOKUP函数：根据首列数据进行查询（案例07） =VLOOKUP(查找依据，查找区域，指定列位置，逻辑值) INDEX函数：根据行索引和列索引进行查询（案例08） = INDEX(查找区域，指定行位置，指定列位置) MATCH函数：对数据进行定位（案例08） = MATCH(查找依据，指定行位置，指定列位置),韩小良,快速汇总多个工作簿和工作表数据,快速汇总多个结构完全相同的工作表 快速汇总结构不同的多个工作表（特殊情况） 快速汇总结构不同的多个工作表（一般情况） 快速汇总多个有关联的工作表,韩小良,快速汇总多个结构完全相同的工作表,当多个工作表的结构相同时，可以使用合并计算工具快速进行合并计算，同时还可以创建分类汇总和分级显示。 案例研究: 案例09 快速合并多个结构相同的分报表(当前工作簿) 案例10 快速合并多个结构相同的分报表(不同工作簿),韩小良,快速汇总结构不同的多个工作表（特殊情况）,利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相关的统计分析。 注意：要汇总的多个工作表数据行数和列数可以不同，但从数据区域的第2列开始必须是数字。 案例11 快速汇总多个工作表数据(特殊情况),韩小良,快速汇总结构不同的多个工作表（一般情况）,一般情况下，要汇总的各个工作表数据可能含有多列文本，此时就不能采用普通的多重合并计算数据区域的数据透视表来进行汇不总了。 最好的解决方法：使用导入外部数据+SQL语句。 案例12 快速汇总多个工作表数据(一般情况),韩小良,快速汇总多个有关联的工作表,有些情况下，几个工作表中分别保存不同的数据，但他们通过一个关键列数据（比如工号）联系起来。 要将这样几个相关联的工作表数据进行汇总，可使用Microsoft Query。 案例13 快速汇总多个有关联的工作表数据,韩小良,数据透视表的灵活应用,数据透视表的基本概念 数据透视表的类型 准备工作 创建数据透视表的基本方法和注意事项 设置数据透视的格式 利用数据透视表快速编制各类统计分析报表 综合应用1：对比分析两个表格数据 综合应用2：进销存数据查询,韩小良,数据透视表的基本概念,什么是数据透视表？ 数据透视表能做什么？ 怎样灵活使用数据透视表？,韩小良,数据透视表的类型,普通区域数据透视表 合并区域数据透视表 外部数据源的数据透视表 OLAP数据透视表,韩小良,准备工作,制作数据透视表的数据区域必须是数据清单 数据区域第一行为列标题 列标题不能重名 数据区域中不能有空行和空列 数据区域中不能有合并单元格 每列数据为同一种类型的数据,整理数据区域 修改非法日期 把文本型数字转换为纯数字 删除数据区域内的所有空行和空列 取消合并单元格并进行填充 去掉字符串前后的空格 删除数字中的特殊字符 删除不必要的小计行和总计行 将二维表格整理为数据清单 等等,韩小良,创建数据透视表的基本方法和注意事项,利用数据透视表向导（案例14） 按照向导步骤进行操作 缺点：无法实现数据源的动态更新，除非使用动态数据区域名称 解决方法：（1）利用导入数据方法；（2）利用OFFSET函数定义动态名称 利用导入数据方法 按照导入数据向导进行操作 优点： 可以实现数据源的动态更新； 可以在不打开其他工作簿的情况下创建数据透视表 可以以数据库的数据创建数据透视表 缺点：删除数据项目后，会遗留“假”项目名称 利用数据库查询方法 按照数据库查询向导进行操作 优点： 不用导入数据，就可以制作需要的报表 可以有选择性地对某些符合条件的数据制作报表 可以在不打开其他工作簿的情况下创建数据透视表 可以以数据库的数据创建数据透视表,韩小良,设置数据透视的的格式,在数据透视表上对数据透视表进行重新布局 修改字段名称 设置字段的数字格式 取消数据透视表的行或列汇总 取消字段的分类汇总 让数据透视表数据按照某一字段进行排序 合并数据标志 设置错误值的显示方式 更新数据透视表的数据,韩小良,利用数据透视表快速编制各类统计分析报表,布局透视表 设置字段的分类汇总方式（案例14） 设置字段的显示方式（案例14） 自定义数据透视表（案例14） 组合字段（案例15） 制作明细表 快速汇总大量工作表数据,韩小良,综合应用1：对比分析两个表格数据,两个工作表有什么不同？如何快速把两个工作表的差异数据找出来？ 案例16 对比分析两个表格数据,韩小良,综合应用2：进销存数据查询,如何快速了解各个物料、商品等的入库、出库以及库存情况？ 利用数据透视表，可以非常容易地制作出进销存数据查询表格。 案例17 进销存数据查询,韩小良,其他应用,图表制作的注意事项和典型图表演示 宏和VBA简介 Excel报表和图表与Word/PPT的链接,韩小良,图表制作的注意事项和典型图表演示,用图表准确表达出你的观点 绘制图表基本方法及注意事项 图表的修饰与美化 用组合图表表