数据分析很简单—快速掌握Excel函数数据透视表和动态图表.pptx
课程体系第1页/共70页关于课程课程宗旨：u帮您树立一种正确高效使用Excel的基本理念和思路u为您提供一套高效实用的Excel技能和方法课程特点：u系统性：囊括利用Excel进行高效日常管理的各方面内容。u全面性：全面介绍了Excel工具的在日常管理中的典型应用，兼顾Excel使用方法技巧介绍和应用思路启发，注重点面结合。u实用性：注重Excel在管理中的实际应用，培训内容紧密结合实际案例。精选数十个源自企业管理过程中的典型案例，极具代表性和实用价值，反映了现代企业中亟需解决的问题，具有非常强的针对性。u示范性：强调动手实践，使每个学员有充分的动手机会，及时解决学习中的问题。在进行实例分析时一步一步地递进，清晰易懂，既方便学员了解管理决策分析的过程，又使学员真正掌握Excel的强大功能。u细致性：讲课认真、细致，辅导积极、耐心，使每个学员都能够掌握所讲的内容。u无忧性：完善的课后服务，随时与老师联系，及时帮助企业学员解决实际工作中遇到的问题和难题。第2页/共70页 经典课程第3页/共70页韩小良老师介绍韩小良老师介绍韩小良：韩小良：资深实战型Excel培训讲师；Excel应用解决方案专家和管理系统软件开发设计师；陆续开发出了财务、销售、人力资源、工资等管理、客户管理、进销存等基于Excel的管理软件。Excel应用方面的著作第一人，出版了近40部关于Excel企业管理高效应用方面的实用书籍。从事Excel研究及应用多年，对Excel及Excel VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用。韩老师具有丰富的实际管理经验和极强的Excel应用开发能力；已经为数千家大中型企业进行了Excel培训和应用方案解决咨询服务，在业界享有很高的评价，深得客户认可。第4页/共70页我们的客户服务过的客户包括但不限于：金融证券：中国银行、浦发银行、广发银行、民生银行、上海农商银行、联合证券、万家基金、国金证券、上海太平资产管理公司、齐鲁证券房地产：雅居乐地产、南益地产集团、北京城建、朗诗集团、新兴集团医药医疗：上海医药集团、强生医疗器械（苏州）、上海博莱科信谊药业、苏州辉瑞动保、豪森药业、山东绿叶制药、四川迈克生物能源电力：中国石化、胜利油田、北京国华能源投资、南方电网通信：广东移动、上海电信、湖北联通、泰州移动、齐齐哈尔移动、上海青浦电信网络媒体：汤森路透、百度总部、Google（中国）总部、西安华商报社、北京东方口岸、麦考林国际邮购生产制造企业：中粮集团、统一集团、光明乳业、东风雪铁龙、扬州亚普汽车部件、摩根新材料、海斯特叉车、普茨迈斯特机械、南京华德火花塞、广东欧普照明、苏州珈农生化（丁家宜）、上海三菱电梯、浙江中烟集团、安徽中烟、南京爱立信熊猫、上海施耐德电气、伟福科技工业（武汉）、无锡华润上华科技、嘉兴阿克苏诺贝尔涂料、常州瓦卢瑞克钢管、贵州银燕集团、上海晶澳太阳能光伏、江苏德邦化工集团、北京奥瑞金种业、苏州瀚德汽车产品、苏州赛发过滤科技、苏州GIF研发中心、江苏交通科学研究院、南瑞集团、南京劲草时装、上海SKF、日本佳能、浙江巨人控股、无锡工装自控、上海柴油机股份、徐州圣戈班、无锡日新电机、郑州煤机股份、苏州道森矿采机械、上海圣戈班、扬州太平洋重工、苏州金螳螂、中材科技、中国电子科技集团、中储股份、中化（宁波）集团、浙江盾安热工科技，厦门林德叉车、中铝国际工程、北京万源瀚德、南通恒辉手套、江阴吉利贝克尼、蒙牛集团、新兴集团其他：ACCA、欧力士租赁、天目湖旅游公司，上海哈特佛德咨询第5页/共70页课程目录课程目录第1部分 公式函数的基础知识第2部分 日期数据分析第3部分 处理文本数据第4部分 数据逻辑判断第5部分 数据分类汇总计算第6部分 数据查找与引用第7部分 快速掌握数据透视表技术第8部分 快速掌握动态图表技术第6页/共70页第1部分 公式函数的基础知识相对引用和绝对引用的重要性创建复杂公式的实用方法保护隐藏工作表中的公式函数的基本语法和注意事项快速准确输入嵌套函数创建高效数据分析公式的基本原则第7页/共70页相对引用和绝对引用的重要性相对引用：复制公式时，单元格引用发生相对位移绝对引用：复制公式时，单元格引用不发生位移合理设置相对引用和绝对引用，可以快速复制公式案例练习案例01 相对引用和绝对引用第8页/共70页创建复杂公式的实用方法当计算公式要用到很多函数，需要创建复杂嵌套函数公式时，可以采用分解综合法基本步骤Step1：先分解公式，保证每步都准确无误Step2：再综合成一个公式Step3：最后完善公式案例练习案例02 分解综合法创建复杂公式第9页/共70页保护隐藏工作表中的公式工作表中的计算公式必须保护起来，以免不小心破坏公式。那些没有公式的单元格，可以不保护，随时进行编辑。基本方法Step1：先解除整个工作表或者指定区域的锁定Step2：选择要保护的公式单元格，锁定或隐藏Step3：保护工作表案例练习案例03 保护隐藏工作表中的公式第10页/共70页函数的基本语法和注意事项函数语法函数名(参数1，参数2，参数3，)函数可以有参数，也可以没参数参数可以是数值、引用、名称、表达式、另外一个函数所有的标点符号都必须是半角字符学习函数的三要素函数是干什么的函数怎么用函数用在什么地方第11页/共70页快速准确输入嵌套函数嵌套函数：一个函数里嵌套一个相同的函数或者不同的函数快速准确输入嵌套函数的基本方法方法1：分解综合法方法2：使用函数参数对话框和名称框案例练习案例04 快速准确输入嵌套函数第12页/共70页创建高效数据分析公式的基本原则尽量使用函数公式尽可能的简单必要时使用辅助计算区域尽量不要使用数组公式第13页/共70页第2部分 日期数据分析日期函数三兄弟高效合同管理计算年龄和工龄计算固定资产折旧综合练习测验第14页/共70页日期函数三兄弟today：计算当天日期用法：=today()edate：计算到期日用法：=edate(开始日期，总月数)datedif：计算两个日期之间的期限用法：=datedif(开始日期，截止日期，格式代码)第15页/共70页高效合同管理如何快速计算合同到期日？EDATE函数EOMONTH函数如何对合同进行动态跟踪，提前提醒？条件格式案例练习案例05 高效合同管理第16页/共70页计算年龄和工龄计算年龄和工龄DATEDIF函数案例练习案例06 计算年龄和工龄第17页/共70页计算固定资产折旧固定资产折旧计算动态、任意选择月份考虑会计制度规定使用函数EOMONTHDATEDIFIF案例练习案例07 计算固定资产折旧第18页/共70页第3部分 处理文本数据文本函数六兄弟快速将数字和日期格式快速分列规则的汉字和数字从身份证号码自动提取信息使用FIND函数进行模糊匹配查找综合练习测验 第19页/共70页文本函数六兄弟len：计算字符串长度（字符个数）用法：len(字符串)left：从字符串左边第一个开始，向右取指定个数的字符用法：left(字符串,字符个数)right：从字符串右边第一个开始，向左取指定个数的字符用法：right(字符串,字符个数)mid：从字符串中的某个字符开始，向右取指定个数的字符用法：mid(字符串,起始位置,字符个数)text：把数字转换成指定格式的文本用法：text(字符串,格式代码)find：查找指定字符串在另外一个字符串中的位置用法：find(查找的字符串,字符串,查找起始位置)第20页/共70页快速将数字和日期格式转换数字格式，可以使用text函数案例练习案例08 快速将数字和日期格式第21页/共70页快速分列规则的汉字和数字下面的问题如何快速解决：如何将规则的汉字和数字分开？如何将中文姓名和英文姓名分开？使用函数len、lenb、left、mid、right案例练习案例09 快速分列规则的汉字和数字第22页/共70页从身份证号码自动提取信息如何输入身份证号码，自动提取个人信息，比如出生日期、性别、户口所在地？案例练习案例10 从身份证号码提取信息第23页/共70页使用FINDFIND函数进行模糊匹配查找FIND函数在处理文本数据时非常有用，可以进行模糊查找，也可以进行数据分列。案例练习案例11 复杂情况下的数据分列第24页/共70页第4部分 数据逻辑判断逻辑函数四兄弟快速准确输入嵌套IF函数处理公式的错误值，让表格更清晰综合练习：处理重复的考勤刷卡数据问题研究：嵌套IF无法解决了怎么办？第25页/共70页逻辑函数四兄弟if：基本逻辑判断用法：=if(条件表达式,条件成立时的结果,条件不成立时的结果)and：把几个条件组合成一个与条件用法：=and(条件1,条件2,条件3,)or：把几个条件组合成一个与条件用法：=or(条件1,条件2,条件3,)iserror：判断一个值是否为错误值用法：=iserror(值)第26页/共70页快速准确输入嵌套IF IF函数快速准确输入嵌套if的基本步骤先捋清楚逻辑流程采用正确的方法输入嵌套if案例练习案例12 快速准确输入嵌套IF函数第27页/共70页处理公式的错误值，让表格更清晰处理错误值，目的是让表格清晰美观，又不影响报告的重复使用。使用函数联合使用if函数和iserror函数仅仅使用iferror函数案例练习案例13 处理公式的错误值第28页/共70页综合练习：考勤刷卡/指纹数据处理如何对从刷卡机导出的考勤刷卡数据进行处理，把重复的刷卡数据删除，保留唯一的数据？如何从导出的指纹数据进行统计分析，计算每个人迟到、早退、全勤的次数？案例练习案例14 考勤刷卡-指纹数据处理第29页/共70页问题研究：嵌套IF IF无法解决了怎么办？当IF嵌套超过7层时，必须采用其他的方法来解决数据的判断问题了，比如：使用条件表达式使用VLOOKUP使用SUMIF使用SUMIFS使用SUMPRODUCT等等案例练习案例15 使用其他工具解决嵌套if无法解决的问题第30页/共70页第5部分 数据分类汇总计算分类汇总函数三兄弟两种重要的分类汇总问题SUMIF函数的两种最实用计算方法SUMIFS函数的适用条件与练习神奇的SUMPRODUCT函数三兄弟优缺点的比较综合练习1：管理费用动态管控模版综合练习2：员工信息多维度分析与流动性动态分析模版综合练习测验第31页/共70页分类汇总函数三兄弟SUMIF：单条件求和用法：=SUMIF(判断区域,条件值,求和区域)SUMIFS：多条件求和用法：=SUMIFS(求和区域,判断区域1,条件值1,判断区域2,条件值2,)SUMPRODUCT：单条件求和、多条件求和、单条件计数、多条件计数用法：=SUMPRODUCT(数组1,数组2,数组3,)第32页/共70页两种重要的分类汇总问题求和单条件求和多条件求和计数单条件计数多条件计数第33页/共70页SUMIFSUMIF函数的两种最实用计算方法SUMIF函数的两种用法：精确匹配汇总模糊匹配汇总案例练习案例16 SUMIF函数应用第34页/共70页SUMIFSSUMIFS函数的适用条件与练习SUMIFS函数的适用条件：与条件必须已经有这样的条件列案例练习案例17 SUMIFS函数应用第35页/共70页神奇的SUMPRODUCTSUMPRODUCT函数SUMPRODUCT函数是一个多功能函数，可以进行各种条件下的求和与计数计算，也可以进行更加复杂的汇总、数据查找、排名等。需要使用的其他技能条件表达式TEXT函数案例练习案例18 SUMPRODUCT函数第36页/共70页三兄弟优缺点的比较请您总结一下，sumif、sumifs、sumproduct三个函数各有什么优缺点第37页/共70页综合练习1 1：管理费用动态管控模版练习：将从NC导出的管理费用科目余额表进行汇总。案例19 管理费用动态管控模版 第38页/共70页综合练习2 2：员工信息多维度分析与流动性动态分析模版练习：对员工属性进行分析，并建立员工流动性分析模版案例20 员工信息综合分析 第39页/共70页第6部分 数据查找与引用查找引用函数五兄弟VLOOKUP函数MATCH函数和INDEX函数INDIRECT函数OFFSET函数滚动灵活查找技术：制作灵活的明细查询表滚动实时汇总技术：快速汇总个数不定的大量工作表滚动累计汇总技术：查看任意月份的当月数和累计数综合应用：制作员工简历查询表综合练习测验 第40页/共70页查找引用函数五兄弟VLOOKUP：根据指定条件从指定区域的指定列取数用法：=VLOOKUP(查找依据,查找区域,取数的列位置,精确查询or模糊查询)MATCH：查找指定数据在指定区域的位置用法：=MATCH(查找值,查找区域,查找模式)INDEX：从指定区域的指定行和指定列取数用法：=INDEX(指定区域,指定行,指定列)INDIRECT：把字符串表示的单元格地址转换为真正的引用用法：=INDIRECT(字符串表示的单元格地址)OFFSET：从指定的参照单元格开始向下（向上）、向右（向左）偏移，得到一个新的单元格或者单元格区域用法：=OFFSET(参照单元格,偏移行,偏移列,新区域行数,新区域列数)第41页/共70页VLOOKUPVLOOKUP函数VLOOKUP函数只能用于满足下列条件的场合：查询表是列结构查询条件是单条件查询方向是从左往右查询条件不允许有重复查询条件是不区分大小写VLOOKUP函数的两种基本用法精确查询模糊查询案例练习案例21 VLOOKUP函数各种应用第42页/共70页MATCHMATCH函数和INDEXINDEX函数联合使用MATCH函数和INDEX函数，可以解决绝大多数的数据查询问题。案例练习案例22 MATCH-INDEX 函数应用第43页/共70页INDIRECTINDIRECT函数把字符串表示的单元格地址转换为真正的引用比如，单元格C3中保存文本“A2”，而此文本恰好是单元格A2的名字，那么公式=INDIRECT(C3)就得到单元格A2的数据比如，公式=INDIRECT(“sheet1!C5”)就间接得到工作表Sheet1单元格C5的数据。第44页/共70页OFFSETOFFSET函数从指定的参照单元格开始向下（向上）、向右（向左）偏移，得到一个新的单元格或者单元格区域。比如，公式=OFFSET(A1,3,4)就得到单元格E4的引用，也就是引用该单元格的数据比如，公式=OFFSET(A1,3,4,2,6)就得到单元格区域E4:J5的引用问题研究：如何总是计算最近三个月的平均值？如何计算截止到指定月份的累计数？第45页/共70页滚动灵活查找技术制作灵活的明细查询表VLOOKUP函数和MATCH函数的最大缺点是无法查询重复数据，因此在制作明细表时就无能为力了。利用滚动查找技能，可以快速制作任何条件下的明细表。核心技能：INDIRECT函数MATCH函数INDEX函数IF函数条件表达式条件格式案例练习案例23 数据滚动查找技术第46页/共70页滚动实时汇总技术快速汇总个数不定的大量工作表当要汇总的工作表数目不定时，如何把这些工作表数据汇总到一个工作表上？利用滚动汇总技能，可以快速制作大量不定工作表数据的汇总和分析。核心技能INDIRECT函数案例练习案例24 快速汇总个数不定工作表 第47页/共70页滚动累计汇总技术查看任意月份的当月数和累计数如何从一个预算表和实际执行表中，把指定月份的当月数查询出来，并计算截止到该月份的累计值？核心函数MatchOffsetSumif案例练习案例25 任意查询当月数和累计数第48页/共70页综合应用：制作员工简历查询表如何根据员工的简历信息，制作动态的简历查询表，既可以显示建立信息数据，又能显示图片？案例练习案例26 制作动态的员工档案卡第49页/共70页第7部分 快速掌握数据透视表技术创建数据透视表的基本方法和注意事项数据透视表的美化建立多维度统计分析报表建立深层次统计分析报表建立多层统计分析报表建立多指标计分析报表利用数据透视表快速汇总大量工作表利用数据透视表快速核对两个表格数据第50页/共70页创建数据透视表的基本方法和注意事项如何创建数据透视表首先标准化、规范化表格数据使用正确的方法制作数据透视表（要考虑数据源的变化，方法是否正确）错误的做法选择整列区域选择整个工作表区域案例练习案例27 数据透视表基本练习第51页/共70页数据透视表的美化制作的基本数据透视表是很难看的，需要进行美化。主要技能使用“数据透视表选项”对话框使用“字段设置”对话框使用快捷菜单命令第52页/共70页建立多维度统计分析报表通过设置字段分类汇总方式，可以建立多维度统计分析报表第53页/共70页建立深层次统计分析报表通过设置字段显示方式，可以建立深层次统计分析报表第54页/共70页建立多层统计分析报表通过组合字段，可以建立多层统计分析报表分组对话框03版的组合命令07版的组合命令第55页/共70页建立多指标计分析报表通过添加新的计算字段和计算项，可以建立多指标计分析报表第56页/共70页利用数据透视表快速汇总大量工作表三大基本方法多重合并计算数据区域数据透视表导入数据+SQL语句Microsoft Query工具案例练习案例28 多重合并数据透视表案例29 导入数据+SQL语句案例30 Microsoft Query工具第57页/共70页利用数据透视表快速核对两个表格数据如何快速核对两个表格的差异？实用技能：查找函数VLOOKUP、MATCH等数据透视表案例练习案例31 快速核对社保案例32 快速核对发收货第58页/共70页第8部分 快速掌握动态图表技术动态图表的几种形式动态图表的基本原理常用控件的使用方法动态图表基本练习综合应用1：利润表滚动分析图综合应用2：灵活的同比分析图第59页/共70页动态图表的几种形式动态图表的几种形式数据透视图控件控制的图表自动调整的图表案例解析案例33 动态图表的几种形式第60页/共70页动态图表的基本原理动态图表的基本原理使用控件控制单元格数据，进而控制图表显示需要掌握的基本技能窗体控件（表单控件）的使用方法查找函数（尤其是INDEX）的熟练使用数据逻辑的正确判断和处理第61页/共70页常用控件的使用方法常用的控件组合框选项按钮复选框滚动条第62页/共70页动态图表基本练习基本练习 案例34 动态图表基本练习第63页/共70页综合应用1 1：利润表滚动分析图案例35 综合应用1：利润表滚动分析图第64页/共70页综合应用2 2：灵活的同比分析图案例36 综合应用2：灵活的同比分析图第65页/共70页课程结束，欢迎交流第66页/共70页E讯东方2季度课程预报第67页/共70页第68页/共70页韩小良 祝您工作生活愉快！1YEAR2YEAR0YEAR3YEAR第69页/共70页感谢您的观看。第70页/共70页
