Excel函数和数组公式的高级应用.docx

1、Excel函数和数组公式的高级应用Excel函数和数组公式的高级应用课程大纲课程介绍 Introduction Excel的函数功能让很多人又爱又恨！爱的是函数够能帮我们解决很多工作中的数据统计难题、构建随时更新的数据报表。恨得是每次看到别人做的复杂函数都感觉无从学起，不能很好的据为己用。本次课程将通过大量商业应用实例，帮助信息工作者解决如何使用Excel函数，如何用好Excel函数的问题。学会了灵活应用函数，您会发现，很多重复的、烦闷的数据处理过程，会变得非常美妙，只需点点或拖拖鼠标，然后靠在椅背上啜口咖啡，本来会忙得晕天黑地的任务已让系统帮您处理好了。学员收益 Benefits以浅显易懂的

2、方式，快速掌握Excel函数中看似复杂的概念和算法不仅掌握Excel的灵魂函数的使用方法，更有心法，让你知其然且知所以然学习数据和参数表的科学管理方法，避免从数据源头就产生问题学习变化思维，多层函数嵌套太复杂，变思路用辅助列化繁为简通过训练，探究高级数组公式的抽象思维和想象力通过课程，您收获到的不只是Excel实用技巧，更是一种全新的高效率和规范的数据管理方式获得由甘彬老师开发的一款让Excel操作变得简单和轻松的插件“Enjoyoffice Tools”（简称ET）插件，大幅提升效率的同时，同等课程时间可以学习到更多的知识相关信息 Information课程时长：12学时/2天适合对象：掌握

3、了函数引用、IF、VLOOKUP等基础函数使用方法的Excel用户（高级培训）授课方式：理念讲授 + 分组讨论 + 案例分析 + 操作示范 + 学员上台演练 + 一对一辅导推荐人数：由于课程难度较大，建议30人以内中级水平学员参加课程大纲 Outline一、函数通用技巧（0.5h）ET插件快到不敢想象加载Enjoyoffice Tools插件一键选定行/列数据：从此再也不用滚了一键转换整行/列数据格式：日期数字文本想变就变一键搞定所有合并单元格的拆分及填充：这也太神奇了吧一键完成工作表/簿的数据合并与拆分：跟打开复制粘贴N次的日子Say Goodbye一键公式粘贴为数值：再也不用先复制再选择性

4、粘贴了一键生成组合函数：根据日期返回季度、从身份证提取性别和出生日期并计算年龄、在相对引用、绝对引用和混合引用间灵活切换通过案例抽丝拨茧，掌握不同引用方式的使用场合利用快捷键轻松在不同引用方式之间来回切换这个公式哪里错了？利用错误检查器分析出错的原因利用快捷键单步检查公式的运算结果二、文本函数（1h）LEFT+RIGHT+MID+FIND：文本拆分知识点：学会寻找拆分数据的规则、构建辅助列减少函数的复杂度MID+TEXT函数：根据身份证号码提取出生日期知识点：掌握TEXT的格式的格式代码、学习“-”运算符的用法文本合并PHONETIC：连接文本很方便利用PHONETIC快速将多行文本合并为单行

5、三、日期函数（0.5h）YEAR+MONTH+CEILING函数：根据日期返回年、季度、月知识点：季度函数计算规则DATEDIF+TODAY+IF+MONTH函数：计算年龄及判断是否本月生日知识点：系统内置函数的使用、动态返回当天日期计算两个时段的分钟数知识点：日期和时间计算时间差的方法四、统计函数（1.5h） 根据当天/月销售额统计累计销售额知识点：SUM求和函数与混合引用相结合多条件计数COUNTIFS和多条件求和SUMIFS知识点：精确统计、模糊统计、区间统计为相同类别的不同明细数据编号知识点：&连接符的功能介绍、COUNTIFS函数与混合引用相结合五、查询函数（3h）VLOOKUP函数

6、多条件查询：以多个关键字定位结果知识点：利用&符号制作辅助列、VLOOKUP函数使用方法和优缺点介绍VLOOKUP+MATCH函数一对多查询：返回相同查询关键字的多个结果知识点：COUNTIFS函数统计记录数、IFERROR函数隐藏错误值INDEX+MATCH函数查询单个项目：根据身份证号码查询姓名知识点：INDEX函数突破VLOOKUP函数不能返回左侧数据的局限INDEX+MATCH函数查询多个项目：根据身份证号码查询员工所有相关信息知识点：函数和混合引用结合使用，一键定位空白单元格，将公式粘贴到其他单元格INDEX+MATCH函数多条件模糊查询：自动匹配数字区间参数表结构设计思维构建、模糊

7、查询-1和1的区别六、引用函数（2h）OFFSET+MATCH行列数据互转引用表1表2知识点：将表1的数据引用到表2中OFFSET+MATCH+COUNTIFS+有效性：制作多级联动下拉菜单通过有效性设置下拉菜单、OFFSET动态区域引用函数七、数组公式高阶函数混合应用（3.5h）认识数组公式知识点：掌握普通公式与数组公式的区别判断“对接人”与7-11月人员是否相同知识点：一个数组公式搞定5个IF函数才能完成的判断VLOOKUP+IF1,0反向查询：根据身份证号码查询姓名和部门知识点：利用IF函数形成虚拟数据源VLOOKUP+IF1,0多条件查询：以多个关键字定位结果知识点：无需创建辅助列，&

8、在数组公式中的应用LOOKUP+FIND多关键字模糊匹配：为书籍自动分类知识点：LOOKUP函数和FIND函数使用方法介绍INDEX+MATCH+TEXT+&多条件模糊匹配：计算奖励金额知识点：满足三个数字条件的模糊匹配、用数组公式构建虚拟表、TEXT函数设置数字格式LOOKUP+FIND查找同类项中的最后一个；IFNA+VLOOKUP+&按条件合并同类项知识点：VLOOKUP函数使用动态数据源、构建辅助列将同类项进行合并LOOKUP+FREQUENCY寻找一组数据中的最接近项FREQUENCY计算频率分布、方差的使用八、资源支持和持续改进学习资源支持根据企业实际使用Office版本，提供培训学员该版本的Office安装包提供课堂同步学习手册、资源素材以及案例学习样本课后持续改进提供课程后期的答疑与新内容推送，帮助学员落实学习成果，持续改进提升