数据分析很简单快速掌握Excel函数数据透视表和动态图表市公开课一等奖百校联赛特等奖课件

数据分析很简单

—快速掌握Excel函数、

数据透视表和动态图表

主讲韩小良第1页关于我们E讯东方简介E讯东方，实名:上海倍讯企业管理咨询有限企业。是专门为中外企业提供特色高品质Excel培训和咨询等相关服务一家专业机构。我们培训特色是：结合企业实际情况设计针对性案例来解决问题，并注重培训后续服务，使培训真正能达到“今天学习，明天应用”效果；真正实现参加者、组织者、投资者三方满意。我们优势：专业资深教授讲师，保证培训效果实用进阶系统课程，保证课程质量免费多样课后服务，保证客户满意我们信念：专业实效创新责任我们宗旨：了解客户，服务客户，发展客户服务领域:公开课和企业内训涵盖如下方面：财税管理,员工信息管理,招聘管理，薪酬管理,考勤管理,销售管理,市场分析,生产管理,仓储物流管理,以及采购管理等；针对企业实际需求提供解决方案式咨询辅导包括：人力资源管理体系建立和金融行业模型建立，企业财务模版，财税软件等。E讯东方，是企业管理向科学化，规范化和高效化发展路上，必不可少忠实伙伴！第2页课程体系第3页关于课程课程宗旨：帮您树立一个正确高效使用Excel基本理念和思绪为您提供一套高效实用Excel技能和方法课程特点：系统性：囊括利用Excel进行高效日常管理各方面内容。全方面性：全方面介绍了Excel工具在日常管理中经典应用，兼顾Excel使用方法技巧介绍和应用思绪启发，重视点面结合。实用性：重视Excel在管理中实际应用，培训内容紧密结合实际案例。精选数十个源自企业管理过程中经典案例，极具代表性和实用价值，反应了当代企业中亟需处理问题，含有非常强针对性。示范性：强调动手实践，使每个学员有充分动手机会，及时处理学习中问题。在进行实例分析时一步一步地递进，清楚易懂，既方便学员了解管理决议分析过程，又使学员真正掌握Excel强大功效。细致性：讲课认真、细致，辅导主动、耐心，使每个学员都能够掌握所讲内容。无忧性：完善课后服务，随时与老师联络，及时帮助企业学员处理实际工作中碰到问题和难题。第4页经典课程第5页韩小良老师介绍韩小良：资深实战型Excel培训讲师；Excel应用处理方案教授和管理系统软件开发设计师；陆续开发出了财务、销售、人力资源、工资等管理、客户管理、进销存等基于Excel管理软件。Excel应用方面著作第一人，出版了近40部关于Excel企业管理高效应用方面实用书籍。从事Excel研究及应用多年，对Excel及ExcelVBA在企业管理中应用有着较深研究和独特认识，对Excel及VBA在管理中高级应用培训有着丰富实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中应用。韩老师含有丰富实际管理经验和极强Excel应用开发能力；已经为数千家大中型企业进行了Excel培训和应用方案处理咨询服务，在业界享受很高评价，深得客户认可。第6页我们客户服务过客户包含但不限于：

金融证券：中国银行、浦发银行、广发银行、民生银行、上海农商银行、联合证券、万家基金、国金证券、上海太平资产管理企业、齐鲁证券房地产：雅居乐地产、南益地产集团、北京城建、朗诗集团、新兴集团医药医疗：上海医药集团、强生医疗器械（苏州）、上海博莱科信谊药业、苏州辉瑞动保、豪森药业、山东绿叶制药、四川迈克生物能源电力：中国石化、胜利油田、北京国华能源投资、南方电网通信：广东移动、上海电信、湖北联通、泰州移动、齐齐哈尔移动、上海青浦电信网络媒体：汤森路透、baidu总部、Google（中国）总部、西安华商报社、北京东方口岸、麦考林国际邮购生产制造企业：中粮集团、统一集团、光明乳业、东风雪铁龙、扬州亚普汽车部件、摩根新材料、海斯特叉车、普茨迈斯特机械、南京华德火花塞、广东欧普照明、苏州珈农生化（丁家宜）、上海三菱电梯、浙江中烟集团、安徽中烟、南京爱立信熊猫、上海施耐德电气、伟福科技工业（武汉）、无锡华润上华科技、嘉兴阿克苏诺贝尔涂料、常州瓦卢瑞克钢管、贵州银燕集团、上海晶澳太阳能光伏、江苏德邦化工集团、北京奥瑞金种业、苏州瀚德汽车产品、苏州赛发过滤科技、苏州GIF研发中心、江苏交通科学研究院、南瑞集团、南京劲草时装、上海SKF、日本佳能、浙江巨人控股、无锡工装自控、上海柴油机股份、徐州圣戈班、无锡日新电机、郑州煤机股份、苏州道森矿采机械、上海圣戈班、扬州太平洋重工、苏州金螳螂、中材科技、中国电子科技集团、中储股份、中化（宁波）集团、浙江盾安热工科技，厦门林德叉车、中铝国际工程、北京万源瀚德、南通恒辉手套、江阴吉利贝克尼、蒙牛集团、新兴集团其它：ACCA、欧力士租赁、天目湖旅游企业，上海哈特佛德咨询第7页课程目录第1部分公式函数基础知识第2部分日期数据分析第3部分处理文本数据第4部分数据逻辑判断第5部分数据分类汇总计算第6部分数据查找与引用第7部分快速掌握数据透视表技术第8部分快速掌握动态图表技术第8页第1部分公式函数基础知识相对引用和绝对引用主要性创建复杂公式实用方法保护隐藏工作表中公式函数基本语法和注意事项快速准确输入嵌套函数创建高效数据分析公式基本标准第9页相对引用和绝对引用主要性相对引用：复制公式时，单元格引用发生相对位移绝对引用：复制公式时，单元格引用不发生位移合理设置相对引用和绝对引用，能够快速复制公式案例练习案例01相对引用和绝对引用第10页创建复杂公式实用方法当计算公式要用到很多函数，需要创建复杂嵌套函数公式时，能够采取分解综正当基本步骤Step1：先分解公式，确保每步都准确无误Step2：再综合成一个公式Step3：最终完善公式案例练习案例02分解综正当创建复杂公式第11页保护隐藏工作表中公式工作表中计算公式必须保护起来，以免不小心破坏公式。那些没有公式单元格，能够不保护，随时进行编辑。基本方法Step1：先解除整个工作表或者指定区域锁定Step2：选择要保护公式单元格，锁定或隐藏Step3：保护工作表案例练习案例03保护隐藏工作表中公式第12页函数基本语法和注意事项函数语法函数名(参数1，参数2，参数3，……)函数能够有参数，也能够没参数参数能够是数值、引用、名称、表示式、另外一个函数全部标点符号都必须是半角字符学习函数三要素函数是干什么函数怎么用函数用在什么地方第13页快速准确输入嵌套函数嵌套函数：一个函数里嵌套一个相同函数或者不一样函数快速准确输入嵌套函数基本方法方法1：分解综正当方法2：使用函数参数对话框和名称框案例练习案例04快速准确输入嵌套函数第14页创建高效数据分析公式基本标准尽可能使用函数公式尽可能简单必要时使用辅助计算区域尽可能不要使用数组公式第15页第2部分日期数据分析日期函数三弟兄高效协议管理计算年纪和工龄计算固定资产折旧综合练习测验第16页日期函数三弟兄today：计算当日日期使用方法：=today()edate：计算到期日使用方法：=edate(开始日期，总月数)datedif：计算两个日期之间期限使用方法：=datedif(开始日期，截止日期，格式代码)第17页高效协议管理怎样快速计算协议到期日？EDATE函数EOMONTH函数怎样对协议进行动态跟踪，提前提醒？条件格式案例练习案例05高效协议管理第18页计算年纪和工龄计算年纪和工龄DATEDIF函数案例练习案例06计算年纪和工龄第19页计算固定资产折旧固定资产折旧计算动态、任意选择月份考虑会计制度要求使用函数EOMONTHDATEDIFIF案例练习案例07计算固定资产折旧第20页第3部分处理文本数据文本函数六弟兄快速将数字和日期格式快速分列规则汉字和数字从身份证号码自动提取信息使用FIND函数进行含糊匹配查找综合练习测验

第21页文本函数六弟兄len：计算字符串长度（字符个数）使用方法：＝len(字符串)left：从字符串左边第一个开始，向右取指定个数字符使用方法：＝left(字符串,字符个数)right：从字符串右边第一个开始，向左取指定个数字符使用方法：＝right(字符串,字符个数)mid：从字符串中某个字符开始，向右取指定个数字符使用方法：＝mid(字符串,起始位置,字符个数)text：把数字转换成指定格式文本使用方法：＝text(字符串,格式代码)find：查找指定字符串在另外一个字符串中位置使用方法：＝find(查找字符串,字符串,查找起始位置)第22页快速将数字和日期格式转换数字格式，能够使用text函数案例练习案例08快速将数字和日期格式第23页快速分列规则汉字和数字下面问题怎样快速处理：怎样将规则汉字和数字分开？怎样将汉字姓名和英文姓名分开？使用函数len、lenb、left、mid、right案例练习案例09快速分列规则汉字和数字第24页从身份证号码自动提取信息怎样输入身份证号码，自动提取个人信息，比如出生日期、性别、户口所在地？案例练习案例10从身份证号码提取信息第25页使用FIND函数进行含糊匹配查找FIND函数在处理文本数据时非常有用，能够进行含糊查找，也能够进行数据分列。案例练习案例11复杂情况下数据分列第26页第4部分数据逻辑判断逻辑函数四弟兄快速准确输入嵌套IF函数处理公式错误值，让表格更清楚综合练习：处理重复考勤刷卡数据问题研究：嵌套IF无法处理了怎么办？第27页逻辑函数四弟兄if：基本逻辑判断使用方法：=if(条件表示式,条件成立时结果,条件不成立时结果)and：把几个条件组合成一个与条件使用方法：=and(条件1,条件2,条件3,……)or：把几个条件组合成一个与条件使用方法：=or(条件1,条件2,条件3,……)iserror：判断一个值是否为错误值使用方法：=iserror(值)第28页快速准确输入嵌套IF函数快速准确输入嵌套if基本步骤先捋清楚逻辑流程采取正确方法输入嵌套if案例练习案例12快速准确输入嵌套IF函数第29页处理公式错误值，让表格更清楚处理错误值，目标是让表格清楚美观，又不影响汇报重复使用。使用函数联合使用if函数和iserror函数仅仅使用iferror函数案例练习案例13处理公式错误值第30页综合练习：考勤刷卡/指纹数据处理怎样对从刷卡机导出考勤刷卡数据进行处理，把重复刷卡数据删除，保留唯一数据？怎样从导出指纹数据进行统计分析，计算每个人迟到、早退、全勤次数？案例练习案例14考勤刷卡-指纹数据处理第31页问题研究：嵌套IF无法处理了怎么办？

当IF嵌套超出7层时，必须采取其它方法来处理数据判断问题了，比如：使用条件表示式使用VLOOKUP使用SUMIF使用SUMIFS使用SUMPRODUCT等等案例练习案例15使用其它工具处理嵌套if无法处理问题第32页第5部分数据分类汇总计算分类汇总函数三弟兄两种主要分类汇总问题SUMIF函数两种最实用计算方法SUMIFS函数适用条件与练习神奇SUMPRODUCT函数三弟兄优缺点比较综合练习1：管理费用动态管控模版综合练习2：员工信息多维度分析与流动性动态分析模版综合练习测验第33页分类汇总函数三弟兄SUMIF：单条件求和使用方法：=SUMIF(判断区域,条件值,求和区域)SUMIFS：多条件求和使用方法：=SUMIFS(求和区域,判断区域1,条件值1,判断区域2,条件值2,……)SUMPRODUCT：单条件求和、多条件求和、单条件计数、多条件计数使用方法：=SUMPRODUCT(数组1,数组2,数组3,……)第34页两种主要分类汇总问题求和单条件求和多条件求和计数单条件计数多条件计数第35页SUMIF函数两种最实用计算方法SUMIF函数两种使用方法：准确匹配汇总含糊匹配汇总案例练习案例16SUMIF函数应用第36页SUMIFS函数适用条件与练习SUMIFS函数适用条件：与条件必须已经有这么条件列案例练习案例17SUMIFS函数应用第37页神奇SUMPRODUCT函数SUMPRODUCT函数是一个多功效函数，能够进行各种条件下求和与计数计算，也能够进行愈加复杂汇总、数据查找、排名等。需要使用其它技能条件表示式TEXT函数案例练习案例18SUMPRODUCT函数第38页三弟兄优缺点比较请您总结一下，sumif、sumifs、sumproduct三个函数各有什么优缺点第39页综合练习1：管理费用动态管控模版练习：将从NC导出管理费用科目余额表进行汇总。案例19管理费用动态管控模版

第40页综合练习2：员工信息多维度分析

与流动性动态分析模版练习：对员工属性进行分析，并建立员工流动性分析模版案例20员工信息综合分析

第41页第6部分数据查找与引用查找引用函数五弟兄VLOOKUP函数MATCH函数和INDEX函数INDIRECT函数OFFSET函数滚动灵活查找技术：制作灵活明细查询表滚动实时汇总技术：快速汇总个数不定大量工作表滚动累计汇总技术：查看任意月份当月数和累计数综合应用：制作员工简历查询表综合练习测验

第42页查找引用函数五弟兄VLOOKUP：依据指定条件从指定区域指定列取数使用方法：=VLOOKUP(查找依据,查找区域,取数列位置,准确查询or含糊查询)MATCH：查找指定数据在指定区域位置使用方法：=MATCH(查找值,查找区域,查找模式)INDEX：从指定区域指定行和指定列取数使用方法：=INDEX(指定区域,指定行,指定列)INDIRECT：把字符串表示单元格地址转换为真正引用使用方法：=INDIRECT(字符串表示单元格地址)OFFSET：从指定参考单元格开始向下（向上）、向右（向左）偏移，得到一个新单元格或者单元格区域使用方法：=OFFSET(参考单元格,偏移行,偏移列,新区域行数,新区域列数)第43页VLOOKUP函数VLOOKUP函数只能用于满足以下条件场所：查询表是列结构查询条件是单条件查询方向是从左往右查询条件不允许有重复查询条件是不区分大小写VLOOKUP函数两种基本使用方法准确查询含糊查询案例练习案例21VLOOKUP函数各种应用第44页MATCH函数和INDEX函数联合使用MATCH函数和INDEX函数，能够处理绝大多数数据查问询题。案例练习案例22MATCH-INDEX函数应用第45页INDIRECT函数把字符串表示单元格地址转换为真正引用比如，单元格C3中保留文本“A2”，而此文本恰好是单元格A2名字，那么公式=INDIRECT(C3)就得到单元格A2数据比如，公式=INDIRECT(“sheet1!C5”)就间接得到工作表Sheet1单元格C5数据。

第46页OFFSET函数从指定参考单元格开始向下（向上）、向右（向左）偏移，得到一个新单元格或者单元格区域。比如，公式=OFFSET(A1,3,4)就得到单元格E4引用，也就是引用该单元格数据比如，公式=OFFSET(A1,3,4,2,6)就得到单元格区域E4:J5引用问题研究：怎样总是计算最近三个月平均值？怎样计算截止到指定月份累计数？第47页滚动灵活查找技术

制作灵活明细查询表VLOOKUP函数和MATCH函数最大缺点是无法查询重复数据，所以在制作明细表时就无能为力了。利用滚动查找技能，能够快速制作任何条件下明细表。关键技能：INDIRECT函数MATCH函数INDEX函数IF函数条件表示式条件格式案例练习案例23数据滚动查找技术第48页滚动实时汇总技术

快速汇总个数不定大量工作表当要汇总工作表数目不定时，怎样把这些工作表数据汇总到一个工作表上？利用滚动汇总技能，能够快速制作大量不定工作表数据汇总和分析。关键技能INDIRECT函数案例练习案例24快速汇总个数不定工作表

第49页滚动累计汇总技术

查看任意月份当月数和累计数怎样从一个预算表和实际执行表中，把指定月份当月数查询出来，并计算截止到该月份累计值？关键函数MatchOffsetSumif案例练习案例25任意查询当月数和累计数第50页综合应用：制作员工简历查询表怎样依据员工简历信息，制作动态简历查询表，既能够显示建立信息数据，又能显示图片？案例练习案例26制作动态员工档案卡第51页第7部分快速掌握数据透视表技术创建数据透视表基本方法和注意事项数据透视表美化建立多维度统计分析报表建立深层次统计分析报表建立多层统计分析报表建立多指标计分析报表利用数据透视表快速汇总大量工作表利用数据透视表快速查对两个表格数据第52页创建数据透视表基本方法和注意事项怎样创建数据透视表首先标准化、规范化表格数据使用正确方法制作数据透视表（要考虑数据源改变，方法是否正确）错误做法选择整列区域选择整个工作表区域

案例练习案例27数据透视表基本练习第53页数据透视表美化制作基本数据透视表是极难看，需要进行美化。主要技能使用“数据透视表选项”对话框使用“字段设置”对话框使用快捷菜单命令第54页建立多维度统计分析报表经过设置字段分类汇总方式，能够建立多维度统计分析报表第55页建立深层次统计分析报表经过设置字段显示方式，能够建立深层次统计