《Excel财务与管理高效应用：方法、技巧和实用模型》.ppt

韩小良,Excel财务与管理高效应用方法、技巧和实用模型,韩小良资深实战型 Excel 培训讲师和应用解决方案专家,韩小良,韩小良 老师 简介,韩小良，资深实战型Excel培训讲师、应用解决方案专家和管理系统软件开发设计师，对Excel及Excel VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用。韩老师具有丰富的实际管理经验和极强的Excel应用开发能力。从事Excel研究及应用多年，已经为数百家中外企业进行了Excel培训和应用方案解决咨询服务，出版了近30余部关于Excel应用方面的著作，并陆续开发出了财务、销售、人力资源、工资等管理、客户管理、进销存等基于Excel的管理软件，应用于数十家企业，取得了明显的经济效益。主要服务客户包括：中国银行、中国民生银行、浦发银行南京分行、上海农业商业银行、深圳联合证券、百度总部、Google(中国)总部、上海医药集团(三期)、上海万家基金、上海电信、上海太平洋资产管理公司、索尼爱立信、中储股份、北京国华能源投资、浙江中烟集团、天津索恩照明、天津Vestas风力技术、卓伦机械(天津)、北京奥瑞金种业、苏州瀚得汽车产品、苏州珈农生化(丁家宜)、苏州赛发过滤科技、上海海斯特叉车、苏州GIF研发中心、无锡华润上华科技、中化(宁波)集团、伟福科技工业(武汉)、贵州银燕集团、江苏交通科学研究院、南京南瑞继保电器、南京劲草时装、南京华德火花塞、上海麦考林国际邮购(三期)、常州瓦卢瑞克钢管、广东欧普照明、广东移动(二期)、上海晶澳太阳能光伏、上海博莱科信谊药业、上海SKF、嘉兴阿克苏诺贝尔(三期)、扬州亚普汽车部件、江苏德邦化工集团、南京朗诗集团、等等数百家中外企业。,韩小良老师的主要著作,用图表说话：Excel实用精美图表大制作，电子工业出版社Excel数据透视表从入门到精通，中国铁道出版社Excel高效办公应用大全：方法、技巧及实际案例，中国铁道出版社Excel 2003/2007函数和公式的使用艺术，中国铁道出版社Excel VBA(2003/2007)高效办公实用宝典，中国铁道出版社Excel VBA(2003/2007)活用范例大辞典，中国铁道出版社运用Excel VBA进行高效投资决策，中国铁道出版社Excel VBA从入门到精通，中国铁道出版社Excel VBA实用技巧大全，中国铁道出版社Excel VBA与数据库整合应用范例精解，科学出版社Excel VBA销售管理系统开发入门与实践，科学出版社Excel VBA整合数据库应用从基础到实践，电子工业出版社Excel在销售与财务管理中的应用，电子工业出版社 Excel企业管理应用案例精萃，电子工业出版社Excel VBA应用开发（技巧精华版），电子工业出版社Excel+SQL Server数据库管理技术详解，机械工业出版社Excel在纳税管理与筹划中的应用，科学出版社Excel会计应用范例精解，电子工业出版社Excel VBA+Access财务系统开发从基础到实践，电子工业出版社Excel在投资理财中的应用，电子工业出版社Excel VBA工资管理应用案例详解，中国铁道出版社Excel在财务管理与分析中的应用，中国水利水电道出版社运用Excel VBA创建高效财务管理模型，中国铁道出版社Excel VBA行政与人力资源管理应用案例详解，中国铁道出版社Excel VBA财务管理应用案例详解，中国铁道出版社Excel 20032007数据透视表从入门到精通，中国铁道出版社Excel 高效管理表单设计从入门到精通，中国铁道出版社Excel 高效人力资源管理从入门到精通，中国铁道出版社数据分析之道：Excel 高效数据处理分析，中国铁道出版社,韩小良老师的经典课程,Excel高效财务管理：方法、技巧和实用模型Excel高效销售管理：方法、技巧和实用模型Excel高效人力资源管理：方法、技巧和实用模型让你的分析报告更有说服力Excel高效数据统计与分析Excel及Excel VBA高级金融建模巧用Excel函数、数据透视表处理人事、财务、销售的繁杂问题Excel VBA快速入门与实战演练,韩小良,使用Excel的基本素养,充分认识Excel是一个科学管理工具Excel是一个科学管理工具管理要用数据说话，而不是拍脑袋做决策一个有说服力报告的重点内容是数据分析表格和图表养成使用Excel的好习惯合理设计Excel表单，为日常数据管理和处理分析打好基础原始数据表格与数据处理分析表格分开保存采用合理的方法获取外部数据等等自定义Excel操作界面添加常用的操作按钮（格式按钮、增大/缩小字号按钮、选择性粘贴按钮、粘贴数值按钮、等等）熟练使用常用的快捷键等等让你的Excel发挥最大的效能安装完全版（Excel 2003）加载分析工具库,韩小良,如何高效使用Excel，实现科学管理？,掌握常用函数和工具,设计规范表格,快速制作汇总统计分析报表和图表,熟练使用数据透视表,用图表说话,财务数据日常管理,韩小良,本课程目标,树立一种理念，把Excel真正用好用活：Excel是管理工具要科学设计表格掌握一套技能，提高管理工作效率：掌握Excel的常用操作技巧掌握规范整理表格的常用方法和技巧掌握常用数据分析工具的使用方法掌握常用函数及其在管理中使用方法掌握利用数据透视表处理分析数据的方法、技巧和各种应用了解动态交互图表的制作原理和方法,韩小良,课程目录,Excel表格数据的整理与规范公式和函数的基本使用方法和技巧数据日常管理实用技能与技巧数据透视表工具的灵活应用用图表说话 制作精美实用图表实际问题研究与答疑,韩小良,Excel表格数据的整理与规范,不论是别人提交给您的表格，还是从数据库导出的数据，在很多情况下都需要进行整理，以便进行进一步的统计分析。例如：高效核对数据数据分列修改非法日期将文本型数字转化为纯数字不规范表格的重新整理复制可见单元格数据数据整理综合练习,韩小良,高效核对数据,别人提供给您的表格中可能有很多错误的数据，如何快速将这些错误数据找出来？方法：方法1：使用数据有效性和公式审核工具方法2：使用条件格式案例01 圈释无效数据 数据有效性小知识介绍,韩小良,数据分列,有些情况下，从数据库导入的数据是一列数据，需要根据实际情况进行分列。案例02 数据分列,韩小良,修改非法日期,日期小知识：日期是特殊的数字。日期永远是大于零的正整数。输入日期正确输入日期：2009-9-4，或者 2009/9/4（如何快速输入当前日期？）错误的输入日期方式：，或者 20090904如何把非法的日期修改为合法日期？案例03 修改非法日期 问题研究：如何利用日期数据的特性和条件格式，设计日程安排表？,韩小良,将文本型数字转化为纯数字,有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，需要转换为纯数字。方法有很多：方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）方法5：利用分列工具案例04 将文本型数字转化为纯数字 问题研究：如何把数字转换为文本型数字？,韩小良,不规范表格的重新整理,对于不规范的表格，例如多表头，有合并单元格，等等，需要进行相应的整理。方法：根据情况，可以使用复制/粘贴，函数、VBA等方法。案例05 取消合并单元格并快速填充数据,韩小良,复制可见单元格数据,如何在分类汇总后，仅仅将汇总数据复制到另外一个工作表，而不复制明细数据？如何在将某些行或列手工隐藏后，仅仅把没有隐藏的数据复制到另外一张工作表？,韩小良,数据整理综合练习,将下面的表格数据整理为便于进行数据分析的表格案例07 数据整理综合练习,韩小良,公式和函数的基本使用方法和技巧,公式及单元格引用保护工作表的计算公式和特殊数据快速输入嵌套函数（IF函数应用）单条件求和（SUMIF函数）数据查询（VLOOKUP函数）处理计算误差多条件求和（SUMPRODUCT函数）,韩小良,公式及单元格引用,公式：在单元格中先输入等号、再输入相关表达式的就是公式。公式种类：普通公式；数组公式单元格引用：在公式中，既可以使用具体的数值，也可以使用某个或某些单元格的数据，后者就称之为单元格的引用。引用方式：根据引用单元格的位置是否变化，引用分为绝对引用和相对引用。引用方式快速转换：按“F4”键合理使用引用方式，可以快速准确批量输入公式。,韩小良,保护工作表的计算公式和特殊数据,当工作表中很多单元格有计算公式，但另外一些单元格是随时可以改变的数据时，就需要把这些计算公式单独保护起来，以免不小心破坏公式。基本方法：取消整个工作表的锁定选择计算公式单元格以及特殊数据单元格，并锁定这些单元格保护工作表操作案例：案例10 SUMIF函数应用之2,韩小良,快速输入嵌套函数(IF函数应用),在实际工作中，经常碰到要使用多个函数来解决某些复杂的计算问题，也就是要输入嵌套函数。输入嵌套函数的快速准确方法是联合使用名称框和函数参数对话框案例08 快速准确输入嵌套函数,韩小良,单条件求和(SUMIF函数),单条件求和，可使用SUMIF函数。案例09 SUMIF函数应用之1案例10 SUMIF函数应用之2,韩小良,数据查询（VLOOKUP函数）,查找函数有很多，其中使用最多的是VLOOKUP函数。例如，假设某个工作表保存产品的信息，就可以使用VLOOKUP函数快速输入某个产品的相关信息数据。案例11 VLOOKUP函数应用,韩小良,处理计算误差,Excel的计算误差是很烦人的，在计算数据时要特别注意这个问题。一般使用ROUND函数进行四舍五入。案例12 处理计算误差,韩小良,多条件求和（SUMPRODUCT函数）,多条件求和：满足多个条件的求和汇总。方法：在公式中使用条件表达式使用SUMPRODUCT函数案例14 多条件求和,韩小良,数据日常管理实用技能与技巧,排序和筛选的灵活使用利用条件格式标识特殊数据自定义数字格式创建多级别的分类汇总创建工作表的分级显示快速汇总多个结构完全相同的工作表,韩小良,排序和筛选的灵活使用,排序：默认排序；自定义排序；按笔划排序；区分大小写排序利用排序快速插入空行筛选：自定义筛选高级筛选（筛选不重复记录）如何对多行标题（有合并单元格）建立自动筛选？列表列表具有全部的筛选功能可以自动扩展或缩小筛选区域可以添加汇总行,韩小良,利用条件格式标识特殊数据,利用条件格式可以动态标识特殊数据，从而更加容易对数据进行管理，做到心中有数。例如：合同提前提醒；应收账款提前提醒；最低库存预警案例15 设计提前提醒模块应收账款提前提醒最低库存预警,韩小良,自定义数字格式,当数字很大时，表格既不便于查看数据，打印出的表格也很不美观。可以通过设置数字的自定义格式来美化表格。例如：将数字缩小1千倍显示将数字缩小1万倍显示将数字缩小百万倍显示将特殊的数字显示为指定的颜色案例16 自定义数字格式,韩小良,创建多级别的分类汇总,利用分类汇总工具，可以创建多级别的分类汇总，从而更加方便地查看和分析数据。案例17 创建多级别的分类汇总,韩小良,创建工作表的分级显示,建立分级显示有两种方法：自动建立分级显示：需要先手工插入汇总行和列，然后再建立分级显示。手动建立分级显示：需要先选择要分级显示的行或列进行组合，然后再建立分级显示。案例18 自动建立分级显示 案例19 手动建立分级显示,韩小良,快速汇总多个结构完全相同的工作表,当多个工作表的结构相同时，可以使用“合并计算”工具快速进行合并计算，同时还可以创建分类汇总和分级显示。案例20 快速合并多个结构相同的分报表,韩小良,数据透视表的灵活应用,准备工作创建数据透视表设置数据透视的格式设置字段汇总方式组合字段设置字段显示方式自定义数据透视表利用数据透视表分析数据快速汇总多个工作表数据(特殊情况)快速汇总多个工作表数据(一般情况)对比分析两个表格数据数据透视表综合练习,韩小良,准备工作,制作数据透视表的数据区域必须是数据清单数据区域第一行为列标题列标题不能重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据,整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等,韩小良,创建数据透视表,利用数据透视表向导（案例21）按照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数据区域名称利用导入数据方法按照导入数据向导进行操作优点：可以实现数据源的动态更新；可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点：删除数据项目后，会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点：不用导入数据，就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表,韩小良,设置数据透视的的格式,在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总取消字段的分类汇总让数据透视表数据按照某一字段进行排序合并数据标志设置错误值的显示方式更新数据透视表的数据,韩小良,设置字段汇总方式,在数据透视表中，可以对某个字段进行多种统计分析，即通过设置字段汇总方式来创建表达多种信息的综合统计报表。,韩小良,组合字段,对字段可以进行自动组合或者手动组合，从而可以创建年报、季报、月报和多种类别组织的统计报表，对企业的财务、销售、人力资源等进行多方位的分析。案例22 组合数据透视表字段,韩小良,设置字段显示方式,可以设置字段的各种显示方式，以便进行各种统计分析，例如：占比、份额、贡献率分析同比分析环比分析等等,韩小良,自定义数据透视表,可以对数据透视表进行自定义，添加源数据中不存在的字段和项目，从而可以使得报表能够表达更多的信息。例如添加自定义计算字段添加自定义计算项目,韩小良,利用数据透视表分析数据,各个产品、各个地区、各个部门、各个销售人员等等的销售状况如何？如何对销售业绩进行排序？如何只显示销售业绩最好的前5个数据？如何快速编制某个地区、某个城市、某种商品等的销售明细表？,韩小良,快速汇总多个工作表数据(特殊情况),利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相关的统计分析。注意：要汇总的多个工作表数据行数和列数可以不同，但数据区域的第2列开始必须是数字。案例23 快速汇总多个工作表数据（特殊情况）,韩小良,快速汇总多个工作表数据(一般情况),如果要汇总的几个工作表数据有多列文本，就不能使用多重合并计算数据区域的数据透视表了。最好的方法是使用导入数据+SQL方法案例24 快速汇总多个工作表数据（一般情况）,韩小良,对比分析两个表格数据,两个工作表有什么不同？如何快速把两个工作表的差异数据找出来？案例25 对比分析两个表格数据,韩小良,数据透视表综合练习,数据透视表综合练习：销售统计分析,韩小良,用图表说话 制作精美实用图表,图表类型的选用原则和建议 美化您的图表，使图表更加引人注目使用自选图形突出图表信息的重点使用组合图表表达更加复杂的信息制作动态交互图表,韩小良,图表类型的选用原则和建议,图表类型柱形图、条形图、折线图、饼图、XY 散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图和棱锥图 选用原则和建议不同类型的图表有不同的适用场合。首先要突出重点，充分反映数据信息。其次是美化图表。关于组合图表几种图表类型的组合。在某些情况下，单一的图表类型无法满足要求，需要绘制组合图表。,韩小良,美化您的图表，使图表更加引人注目,图表的结构与元素图表区、绘图区、数据系列、分类轴、数值轴、图表标题、图例、数据标志、坐标轴标签设置图表各元素的格式改变图表大小和位置 改变某个系列的图表类型，或者将某个系列绘制在次轴上。添加系列和删除系列小窍门：利用图表工具栏选择图表元素,韩小良,使用自选图形突出图表信息的重点,利用自选图形对图表进行修饰，可以突出图表的重点信息，使阅读者一目了然地了解经营存在的问题案例26 利用自选图形美化图表,韩小良,使用组合图表表达更加复杂的信息,很多问题不是一种图表类型所能表示清楚的，需要使用多类型的组合图表，或者对图表进行变形处理。案例演示：案例27 差异对比分析图,韩小良,制作动态交互图表,制作动态交互图表，可以更加更加灵活地、更加所心所欲在图表上显示需要重点了解的信息。基本方法：使用窗体控件，比如选项按钮、复选框、组合框、滚动条等定义动态名称，或者设计辅助绘图数据区域需要熟练使用有关的查找函数，并了解窗体控件的功能和属性案例演示：案例28 动态交互图表,韩小良,本次课程到此结束谢谢您欢迎随时联系研究解决您的实际问题,