Excel魔法学校之神奇函数

[培训目的]

此次为期 1天的EXCEL课程，旨在帮助参与者:

通过各个案例的融合讲解，全面理解EXCEL的公式和函数的基础知识和工作原理；
通过企业中各种疑难案例的讲解，介绍EXCEL各种类别函数的经典魔法，并通过进阶的练习从而熟练掌握EXCEL函数的应用。

[课程大纲]

1. 公式与函数基础（30分钟）

熟悉公式输入环境
疑难：为什么公式的计算结果不会随单元格值的变化而变化？
疑难：为什么单元格只显示公式文本而不显示计算结果？
疑难：单元格左上角的绿色小三角到底是什么含义？――错误检查
公式与函数基础概念？
疑难：为什么IF函数只用两个参数也能计算？――可选参数用法
疑难：为什么数字与空单元格相加会出错？――空文本与空单元格的区别
魔法：如何快速规范以小数点间隔的伪日期？
魔法：如何实现输入数字1、2后自动将其显示为“男”、“女”
公式常用操作技巧
魔法：如何按照合并单元格求对应金额小计？――相对、绝对、混合引用
魔法：如何让合并单元格中的每个单元格都有内容？
魔法：如何复制公式且保持相对引用地址不变？
在公式中使用名称
疑难：为什么要使用名称定义？――名称的好处
魔法：如何在不同工作表中定义不同数据的相同名称？
魔法：如何让图片随选择的生肖而自动变化？
数组公式
疑难：什么是数组公式？――数组的作用
魔法：如何根据一组商品的单价与数量直接求总金额？
魔法：如何判断多项运算是否需要使用数组公式？――数组公式的意义
2. 逻辑判断（60分钟）

逻辑关系――与、或、非
魔法：如何判断串连和并联电路的连通状态？――IF、AND、OR函数的应用
魔法：如何判断同时满足购买经济适用房的三个条件？
魔法：如何根据职工性别和职务判断退休年龄？
多层判断
魔法：如何根据成绩判断优良中差等级？――多层IF函数嵌套应用
魔法：如何根据多选题答案给出评分？――多个条件判断的分支与嵌套
常用条件设置
魔法：如何将公式返回的错误值转为其他值？――IS类函数的解决之道
魔法：如何判断单元格是否漏填信息？――判断真空单元格
魔法：如何根据身份证号码判断性别？――MOD函数应用
3. 数据汇总（60分钟）

汇总求和
魔法：如何进行单条件汇总？――SUMIF函数的应用
魔法：如何汇总月薪在5000至8000的工资总额？――SUMIFS函数的应用
魔法：如何进行多条件汇总？――SUMPRODUCT函数的神奇应用
魔法：如何汇总固定间隔n行的数据？――MOD函数构建n行循环
数据计数
魔法：如何统计产品达标的数量？――COUNTIF函数的应用
魔法：如何统计35岁以上有房有车的职工人数？――COUNTIFS函数的应用
魔法：如何统计不重复数据的个数？
极值均值
魔法：如何设置提成资金的上下限？――MAX、MIN、MEDIAN函数的应用
魔法：如何剔除一个最大值和一个最小值后求平均值？
魔法：如何计算各条生产线最高工时的平均值？
排名排序
魔法：如何实现多列成绩统一排名？――RANK函数的应用
魔法：如何进行跨工作表数据总排名？――RANK函数的多表三维引用
魔法：如何让并列排名统计只占一个名次？――中国式排名的3种算法
4. 文本处理（60分钟）

截取与连接
魔法：如何解读身份证中的有用信息？――LEFT、RIGHT、MID函数应用
魔法：如何提取混合字符串中的连续汉字？
魔法：如何将字符串按特定分隔符分列？――通用公式法
查找与替换
魔法：如何判断字符串中是否包含某个字符？――FIND函数的应用
魔法：如何提取包含特定字符的所有记录？
魔法：如何计算包含特定字符的个数？――SUBSTITUTE函数的应用
数字变幻
魔法：如何加密手机号码？――替换指定位置和长度的字符
魔法：如何将阿拉伯数字转为人民币大写金额？
魔法：如何根据图书目录的大纲级别制作分级编码？
5. 日期与时间处理（60分钟）


日期推算
魔法：如何推算某个日期所在的月、季首和月、季末日期？――DATE函数
魔法：如何计算两个日期相隔的年、月、天数？――DATEDIF函数的应用
魔法：如何判断某个日期在上旬、中旬还是在下旬？
时间推算
魔法：如何获取时间的时、分、秒要素？
魔法：如何将十进制数时间转换为标准时间格式？
魔法：如何统计设备开启后在某个月度中的运行时长？
星期与工作日
魔法：如何判断某个日期是否为周末？――MOD函数与WEEKDAY函数
魔法：如何计算离职员工最后一个月的实际工作天数？――NETWORKDAYS函数的应用
魔法：如何推算某个日期处于当年中的第几周？
6. 查找与引用（90分钟）

查找定位
魔法：如何根据物料号在库存表中查询库存数？――VLOOKUP的精确查找
魔法：如何编制适合中国的9级累进所得税公式？――VLOOKUP的近似查找
魔法：如何根据输入工号自动显示员工基本信息？――VLOOKUP+MATCH
魔法：如何根据品名和规格双列条件查询物品单价？――INDEX+MATCH
魔法：如何将满足条件的多条记录放在一个单元格？
魔法：如何将一个单元格内多个“字段”与内容进行分离――VLOOKUP应用
超级链接
魔法：如何跳转到最后一行数据下方的空单元格？
魔法：如何制作工作表目录超链接？
数据整合
魔法：如何根据去年和今年人员表分析出离职、在职、入职人员清单？
魔法：如何快速将一张表的数据按关联字段链接过来？――VLOOKUP+COLUMN函数的应用
魔法：如何快速整合多张表的数据到一张表？――VLOOKUP+MATCH应用
魔法：如何剔除重复值并按数据出现频率降序排序？
7. 其他计算应用（60分钟）

获取信息
魔法：如何获取文件所在的路径？――CELL函数的应用
魔法：如何检测错误值类型并返回错误分析信息？
数值舍入
魔法：如何对数值进行取整？――4种方法
魔法：如何根据货物数量计算所需纸箱数？――向上舍入方法
魔法：如何按照四舍六入五单双规则修约数值？
财务金融
魔法：如何制作按揭贷款计算器？
魔法：如何制作养老金投资计算器？
魔法：如何用直线法计算固定资产折旧？
魔法：如何用双倍余额递减法计算固定资产折旧？