Excel函数和数组公式的高级应用.docx
《Excel函数和数组公式的高级应用.docx》由会员分享，可在线阅读，更多相关《Excel函数和数组公式的高级应用.docx（12页珍藏版）》请在冰豆网上搜索。
Excel函数和数组公式的高级应用
Excel函数和数组公式的高级应用课程大纲
课程介绍Introduction
Excel的函数功能让很多人又爱又恨！
爱的是函数够能帮我们解决很多工作中的数据统计难题、构建随时更新的数据报表。
恨得是每次看到别人做的复杂函数都感觉无从学起，不能很好的据为己用。
本次课程将通过大量商业应用实例，帮助信息工作者解决如何使用Excel函数，如何用好Excel函数的问题。
学会了灵活应用函数，您会发现，很多重复的、烦闷的数据处理过程，会变得非常美妙，只需点点或拖拖鼠标，然后靠在椅背上啜口咖啡，本来会忙得晕天黑地的任务已让系统帮您处理好了。
学员收益Benefits
▪以浅显易懂的方式，快速掌握Excel函数中看似复杂的概念和算法
▪不仅掌握Excel的灵魂——函数的使用方法，更有心法，让你知其然且知所以然
▪学习数据和参数表的科学管理方法，避免从数据源头就产生问题
▪学习变化思维，多层函数嵌套太复杂，变思路用辅助列化繁为简
▪通过训练，探究高级数组公式的抽象思维和想象力
▪通过课程，您收获到的不只是Excel实用技巧，更是一种全新的高效率和规范的数据管理方式
▪获得由甘彬老师开发的一款让Excel操作变得简单和轻松的插件“EnjoyofficeTools”（简称ET）插件，大幅提升效率的同时，同等课程时间可以学习到更多的知识
相关信息Information
▪课程时长：
12学时/2天
▪适合对象：
掌握了函数引用、IF、VLOOKUP等基础函数使用方法的Excel用户（高级培训）
▪授课方式：
理念讲授+分组讨论+案例分析+操作示范+学员上台演练+一对一辅导
▪推荐人数：
由于课程难度较大，建议30人以内中级水平学员参加
课程大纲Outline
一、函数通用技巧（0.5h）
▪ET插件——快到不敢想象
▪加载EnjoyofficeTools插件
▪一键选定行/列数据：
从此再也不用滚了
▪一键转换整行/列数据格式：
日期数字文本想变就变
▪一键搞定所有合并单元格的拆分及填充：
这也太神奇了吧
▪一键完成工作表/簿的数据合并与拆分：
跟打开复制粘贴N次的日子SayGoodbye
▪一键公式粘贴为数值：
再也不用先复制再选择性粘贴了
▪一键生成组合函数：
根据日期返回季度、从身份证提取性别和出生日期并计算年龄、……
▪在相对引用、绝对引用和混合引用间灵活切换
▪通过案例抽丝拨茧，掌握不同引用方式的使用场合
▪利用快捷键轻松在不同引用方式之间来回切换
▪这个公式哪里错了？
▪利用错误检查器分析出错的原因
▪利用快捷键单步检查公式的运算结果
二、文本函数（1h）
▪LEFT+RIGHT+MID+FIND：
文本拆分
▪知识点：
学会寻找拆分数据的规则、构建辅助列减少函数的复杂度
▪MID+TEXT函数：
根据身份证号码提取出生日期
▪知识点：
掌握TEXT的格式的格式代码、学习“-”运算符的用法
▪文本合并PHONETIC：
连接文本很方便
▪利用PHONETIC快速将多行文本合并为单行
三、日期函数（0.5h）
▪YEAR+MONTH+CEILING函数：
根据日期返回年、季度、月
▪知识点：
季度函数计算规则
▪DATEDIF+TODAY+IF+MONTH函数：
计算年龄及判断是否本月生日
▪知识点：
系统内置函数的使用、动态返回当天日期
▪计算两个时段的分钟数
▪知识点：
日期和时间计算时间差的方法
四、统计函数（1.5h）
▪根据当天/月销售额统计累计销售额
▪知识点：
SUM求和函数与混合引用相结合
▪多条件计数COUNTIFS和多条件求和SUMIFS
▪知识点：
精确统计、模糊统计、区间统计
▪为相同类别的不同明细数据编号
▪知识点：
&连接符的功能介绍、COUNTIFS函数与混合引用相结合
五、查询函数（3h）
▪VLOOKUP函数多条件查询：
以多个关键字定位结果
▪知识点：
利用&符号制作辅助列、VLOOKUP函数使用方法和优缺点介绍
▪VLOOKUP+MATCH函数一对多查询：
返回相同查询关键字的多个结果
▪知识点：
COUNTIFS函数统计记录数、IFERROR函数隐藏错误值
▪INDEX+MATCH函数查询单个项目：
根据身份证号码查询姓名
▪知识点：
INDEX函数突破VLOOKUP函数不能返回左侧数据的局限
▪INDEX+MATCH函数查询多个项目：
根据身份证号码查询员工所有相关信息
▪知识点：
函数和混合引用结合使用，一键定位空白单元格，将公式粘贴到其他单元格
▪INDEX+MATCH函数多条件模糊查询：
自动匹配数字区间
▪参数表结构设计思维构建、模糊查询-1和1的区别
六、引用函数（2h）
▪OFFSET+MATCH行列数据互转引用
表1
表2
▪知识点：
将表1的数据引用到表2中
▪OFFSET+MATCH+COUNTIFS+有效性：
制作多级联动下拉菜单
▪通过有效性设置下拉菜单、OFFSET动态区域引用函数
七、数组公式——高阶函数混合应用（3.5h）
▪认识数组公式
▪知识点：
掌握普通公式与数组公式的区别
▪判断“对接人”与7-11月人员是否相同
▪知识点：
一个数组公式搞定5个IF函数才能完成的判断
▪VLOOKUP+IF{1,0}反向查询：
根据身份证号码查询姓名和部门
▪知识点：
利用IF函数形成虚拟数据源
▪VLOOKUP+IF{1,0}多条件查询：
以多个关键字定位结果
▪知识点：
无需创建辅助列，&在数组公式中的应用
▪LOOKUP+FIND多关键字模糊匹配：
为书籍自动分类
▪知识点：
LOOKUP函数和FIND函数使用方法介绍
▪INDEX+MATCH+TEXT+&多条件模糊匹配：
计算奖励金额
▪知识点：
满足三个数字条件的模糊匹配、用数组公式构建虚拟表、TEXT函数设置数字格式
▪LOOKUP+FIND查找同类项中的最后一个；IFNA+VLOOKUP+&按条件合并同类项
▪知识点：
VLOOKUP函数使用动态数据源、构建辅助列将同类项进行合并
▪LOOKUP+FREQUENCY寻找一组数据中的最接近项
▪FREQUENCY计算频率分布、方差的使用
八、资源支持和持续改进
▪学习资源支持
▪根据企业实际使用Office版本，提供培训学员该版本的Office安装包
▪提供课堂同步学习手册、资源素材以及案例学习样本
▪课后持续改进
▪提供课程后期的答疑与新内容推送，帮助学员落实学习成果，持续改进提升
升级会员 