课程大纲：

Excel高级应用 培训课程

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

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

课程时长：12学时/2天
适合对象：掌握了函数引用、IF、VLOOKUP等基础函数使用方法的Excel用户（高级培训）
授课方式：理念讲授 + 分组讨论 + 案例分析 + 操作示范 + 学员上台演练 + 一对一辅导
推荐人数：由于课程难度较大，建议50人以内中级水平学员参加

课程大纲  Outline
一、培训前预备知识（线上）
Excel操作界面介绍
自定义功能区选项卡
自定义快速访问工具栏
.xls和.xlsx文件格式的区别
工作簿视图：普通、分页预览、页面布局
调整页面显示比例
显示/隐藏编辑栏、网格线、标题
多工作簿并排查看
工作表与工作簿管理
工作表与工作簿的概念
添加、删除、移动、复制、隐藏、取消隐藏工作表
快速切换和选择工作表、选择行/列/区域
插入、删除工作表行/列/单元格
调整行高、列宽，设置为最适合的行高和列宽
显示/隐藏行或列、调整行/列顺序
数据的输入与整理
设置工作表的字体
切换到下一单元格和前一单元格
快速输入序号列
自定义序号格式
编辑自定义列表
一键输入当天日期和当前时间
快速生成选择列表
输入长数字变成科学计数法，怎么办？
排序和筛选
函数基础——函数基本知识
设置：显示/隐藏公式编辑栏
参数：了解公式的语法和参数设置
搜索：学会搜索函数和查看帮助
窍门：用窍门巧学函数，会简单英语就可以了
技巧：还在用“拖”的方法复制公式吗？按“Enter”键公式自动填充到最后一行
常用函数——熟练掌握各种类型常用函数
基础函数：SUM、MAX、MIN、AVERAGE、COUNT、COUNTA
逻辑函数：IF多层嵌套时，逻辑树分析来梳理
查找函数：VLOOKUP的*模糊
问题分析：函数出错的原因分析和排错方法
功能结合：与条件格式和有效性结合，限定输入并突出重点

二、加载Enjoyoffice Tools插件和效率提升工具箱
ET插件——快到不敢想象
一键选定行/列数据：从此再也不用滚了
一键转换整行/列数据格式：日期数字文本想变就变
一键搞定所有合并单元格的拆分及填充：这也太神奇了吧
一键完成工作表/簿的数据合并与拆分：跟打开复制粘贴N次的日子Say Goodbye
一键公式粘贴为数值：再也不用先复制再选择性粘贴了
一键生成组合函数：根据日期返回季度、从身份证提取性别和出生日期并计算年龄、……

三、函数通用技巧（0.5h）
在相对引用、*引用和混合引用间灵活切换
通过案例抽丝拨茧，掌握不同引用方式的使用场合
利用快捷键轻松在不同引用方式之间来回切换
这个公式哪里错了？
利用错误检查器分析出错的原因
利用快捷键单步检查公式的运算结果

四、文本函数（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安装包
提供课堂同步学习手册、资源素材以及案例学习样本
课后持续改进
微信群：提供课程结束后的一个月内微信群答疑，帮助学员落实学习成果
公众号：提供课程后期的答疑与新内容推送，持续改进提升

Excel高级应用 培训课程