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

韩小良韩小良 数据分析很简单数据分析很简单快速掌握快速掌握ExcelExcel函数、函数、数据透视表和动态图表数据透视表和动态图表主讲主讲 韩小良韩小良第1页韩小良韩小良 关于我们E讯东方简介E讯东方，实名:上海倍讯企业管理咨询有限企业。是专门为中外企业提供特色高品质Excel培训和咨询等相关服务一家专业机构。我们培训特色是：结合企业实际情况设计针对性案例来解决问题，并注重培训后续服务，使培训真正能达到“今天学习，明天应用”效果；真正实现参加者、组织者、投资者三方满意。我们优势：专业资深教授讲师，保证培训效果 实用进阶系统课程，保证课程质量 免费多样课后服务，保证客户满意我们信念：专业 实效 创新 责任我们宗旨：了解客户，服务客户，发展客户服务领域:公开课和企业内训涵盖如下方面：财税管理,员工信息管理,招聘管理，薪酬管理,考勤管理,销售管理,市场分析,生产管理,仓储物流管理,以及采购管理等；针对企业实际需求提供解决方案式咨询辅导包括：人力资源管理体系建立和金融行业模型建立，企业财务模版，财税软件等。E讯东方，是企业管理向科学化，规范化和高效化发展路上，必不可少忠实伙伴！第2页韩小良韩小良 课程体系第3页韩小良韩小良 关于课程课程宗旨：课程宗旨：帮您树立一个正确高效使用Excel基本理念和思绪为您提供一套高效实用Excel技能和方法课程特点：课程特点：系统性：囊括利用Excel进行高效日常管理各方面内容。全方面性：全方面介绍了Excel工具在日常管理中经典应用，兼顾Excel使用方法技巧介绍和应用思绪启发，重视点面结合。实用性：重视Excel在管理中实际应用，培训内容紧密结合实际案例。精选数十个源自企业管理过程中经典案例，极具代表性和实用价值，反应了当代企业中亟需处理问题，含有非常强针对性。示范性：强调动手实践，使每个学员有充分动手机会，及时处理学习中问题。在进行实例分析时一步一步地递进，清楚易懂，既方便学员了解管理决议分析过程，又使学员真正掌握Excel强大功效。细致性：讲课认真、细致，辅导主动、耐心，使每个学员都能够掌握所讲内容。无忧性：完善课后服务，随时与老师联络，及时帮助企业学员处理实际工作中碰到问题和难题。第4页韩小良韩小良 经典课程经典课程第5页韩小良韩小良 韩小良老师介绍韩小良老师介绍韩小良：韩小良：韩小良：韩小良：资深实战型Excel培训讲师；Excel应用处理方案教授和管理系统软件开发设计师；陆续开发出了财务、销售、人力资源、工资等管理、客户管理、进销存等基于Excel管理软件。Excel应用方面著作第一人，出版了近40部关于Excel企业管理高效应用方面实用书籍。从事Excel研究及应用多年，对Excel及Excel VBA在企业管理中应用有着较深研究和独特认识，对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页韩小良韩小良 使用使用FINDFIND函数进行含糊匹配查找函数进行含糊匹配查找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基本步骤先捋清楚逻辑流程采取正确方法输入嵌套if案例练习案例12 快速准确输入嵌套IF函数第29页韩小良韩小良 处理公式错误值，让表格更清楚处理公式错误值，让表格更清楚处理错误值，目标是让表格清楚美观，又不影响汇报重复使用。使用函数联合使用if函数和iserror函数仅仅使用iferror函数案例练习案例13 处理公式错误值第30页韩小良韩小良 综合练习：考勤刷卡综合练习：考勤刷卡/指纹数据处理指纹数据处理怎样对从刷卡机导出考勤刷卡数据进行处理，把重复刷卡数据删除，保留唯一数据？怎样从导出指纹数据进行统计分析，计算每个人迟到、早退、全勤次数？案例练习案例14 考勤刷卡-指纹数据处理第31页韩小良韩小良 问题研究：嵌套问题研究：嵌套IF 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页韩小良韩小良 SUMIFSUMIF函数两种最实用计算方法函数两种最实用计算方法SUMIF函数两种使用方法：准确匹配汇总含糊匹配汇总案例练习案例16 SUMIF函数应用第36页韩小良韩小良 SUMIFSSUMIFS函数适用条件与练习函数适用条件与练习SUMIFS函数适用条件：与条件必须已经有这么条件列案例练习案例17 SUMIFS函数应用第37页韩小良韩小良 神奇神奇SUMPRODUCTSUMPRODUCT函数函数SUMPRODUCT函数是一个多功效函数，能够进行各种条件下求和与计数计算，也能够进行愈加复杂汇总、数据查找、排名等。需要使用其它技能条件表示式TEXT函数案例练习案例18 SUMPRODUCT函数第38页韩小良韩小良 三弟兄优缺点比较三弟兄优缺点比较请您总结一下，sumif、sumifs、sumproduct三个函数各有什么优缺点第39页韩小良韩小良 综合练习综合练习1 1：管理费用动态管控模版：管理费用动态管控模版练习：将从NC导出管理费用科目余额表进行汇总。案例19 管理费用动态管控模版 第40页韩小良韩小良 综合练习综合练习2 2：员工信息多维度分析：员工信息多维度分析与流动性动态分析模版与流动性动态分析模版练习：对员工属性进行分析，并建立员工流动性分析模版案例20 员工信息综合分析 第41页韩小良韩小良 第第6部分部分 数据查找与引用数据查找与引用查找引用函数五弟兄查找引用函数五弟兄VLOOKUP函数函数MATCH函数和函数和INDEX函数函数INDIRECT函数函数OFFSET函数函数滚动灵活查找技术：制作灵活明细查询表滚动灵活查找技术：制作灵活明细查询表滚动实时汇总技术：快速汇总个数不定大量工作表滚动实时汇总技术：快速汇总个数不定大量工作表滚动累计汇总技术：查看任意月份当月数和累计数滚动累计汇总技术：查看任意月份当月数和累计数综合应用：制作员工简历查询表综合应用：制作员工简历查询表综合练习测验综合练习测验 第42页韩小良韩小良 查找引用函数五弟兄查找引用函数五弟兄VLOOKUP：依据指定条件从指定区域指定列取数使用方法：=VLOOKUP(查找依据,查找区域,取数列位置,准确查询or含糊查询)MATCH：查找指定数据在指定区域位置使用方法：=MATCH(查找值,查找区域,查找模式)INDEX：从指定区域指定行和指定列取数使用方法：=INDEX(指定区域,指定行,指定列)INDIRECT：把字符串表示单元格地址转换为真正引用使用方法：=INDIRECT(字符串表示单元格地址)OFFSET：从指定参考单元格开始向下（向上）、向右（向左）偏移，得到一个新单元格或者单元格区域使用方法：=OFFSET(参考单元格,偏移行,偏移列,新区域行数,新区域列数)第43页韩小良韩小良 VLOOKUPVLOOKUP函数函数VLOOKUP函数只能用于满足以下条件场所：查询表是列结构查询条件是单条件查询方向是从左往右查询条件不允许有重复查询条件是不区分大小写VLOOKUP函数两种基本使用方法准确查询含糊查询案例练习案例21 VLOOKUP函数各种应用第44页韩小良韩小良 MATCHMATCH函数和函数和INDEXINDEX函数函数联合使用MATCH函数和INDEX函数，能够处理绝大多数数据查问询题。案例练习案例22 MATCH-INDEX函数应用第45页韩小良韩小良 INDIRECTINDIRECT函数函数把字符串表示单元格地址转换为真正引用比如，单元格C3中保留文本“A2”，而此文本恰好是单元格A2名字，那么公式=INDIRECT(C3)就得到单元格A2数据比如，公式=INDIRECT(“sheet1!C5”)就间接得到工作表Sheet1单元格C5数据。第46页韩小良韩小良 OFFSETOFFSET函数函数从指定参考单元格开始向下（向上）、向右（向左）偏移，得到一个新单元格或者单元格区域。比如，公式=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页韩小良韩小良 建立深层次统计分析报表建立深层次统计分析报表经过设置字段显示方式，能够建立深层次统计分析报表第56页韩小良韩小良 建立多层统计分析报表建立多层统计分析报表经过组合字段，能够建立多层统计分析报表分组对话框03版组合命令07版组合命令第57页韩小良韩小良 建立多指标计分析报表建立多指标计分析报表经过添加新计算字段和计算项，能够建立多指标计分析报表第58页韩小良韩小良 利用数据透视表快速汇总大量工作表利用数据透视表快速汇总大量工作表三大基本方法多重合并计算数据区域数据透视表导入数据+SQL语句Microsoft Query工具案例练习案例28 多重合并数据透视表案例29 导入数据+SQL语句案例30 Microsoft Query工具第59页韩小良韩小良 利用数据透视表快速查对两个表格数据利用数据透视表快速查对两个表格数据怎样快速查对两个表格差异？实用技能：查找函数VLOOKUP、MATCH等数据透视表案例练习案例31 快速查对社保案例32 快速查对发收货第60页韩小良韩小良 第第8部分部分 快速掌握动态图表技术快速掌握动态图表技术动态图表几个形式动态图表几个形式动态图表基本原理动态图表基本原理惯用控件使用方法惯用控件使用方法动态图表基本练习动态图表基本练习综合应用综合应用1：利润表滚动分析图：利润表滚动分析图综合应用综合应用2：灵活同比分析图：灵活同比分析图第61页韩小良韩小良 动态图表几个形式动态图表几个形式动态图表几个形式数据透视图控件控制图表自动调整图表案例解析案例33 动态图表几个形式第62页韩小良韩小良 动态图表基本原理动态图表基本原理动态图表基本原理使用控件控制单元格数据，进而控制图表显示需要掌握基本技能窗体控件（表单控件）使用方法查找函数（尤其是INDEX）熟练使用数据逻辑正确判断和处理第63页韩小良韩小良 惯用控件使用方法惯用控件使用方法惯用控件组合框选项按钮复选框滚动条第64页韩小良韩小良 动态图表基本练习动态图表基本练习基本练习 案例34 动态图表基本练习第65页韩小良韩小良 综合应用综合应用1 1：利润表滚动分析图：利润表滚动分析图案例35 综合应用1：利润表滚动分析图第66页韩小良韩小良 综合应用综合应用2 2：灵活同比分析图：灵活同比分析图案例36 综合应用2：灵活同比分析图第67页韩小良韩小良 课程结束，欢迎交流第68页韩小良韩小良 E讯东方2季度课程预报第69页韩小良韩小良 第70页韩小良韩小良 韩小良 祝您工作生活愉快！1YEAR2YEAR0YEAR3YEAR第71页