Exce图表函数及透视表高阶应用（1天）

课程介绍  Introduction

Excel作为帮助办公人员进行数据处理的强大通用工具，可以大大减少工作量，提升工作效率。在应用Excel进行分析工作的时候，您是否存在以下问题：

基础的Excel应用已经不能满足自己的工作需求，还有哪些拓展功能等待自己去挖掘运用？

想将众多数据转换成有意义的动态汇总数据报告，使公司的人资状况、财务状况、经营销售状况一目了然地呈现在老板的眼前，Excel能帮助我做到吗？

面对大段文字不知道如何抽丝剥茧，提炼出关键词，并转化成适合的图表类型

课程定位于对Excel应用有一定基础的学员，传授学员数据管理的工作思路，配合丰富的实操案例，在基础技能的层面上继续实现功能拓展，带您进入一个高端信息处理平台，帮助学员巩固和进阶Excel的运用，不但让学员在课堂上学习和理解，更能将带到实际工作中去，突破更高层次的技能突破。

相关信息  Information

课程时长：6学时/1天

适合对象：掌握了函数引用、基础函数、基础图表等常用工具使用方法的用户（高阶培训）

授课方式：理念讲授 + 分组讨论 + 案例分析 + 操作示范 + 学员上台演练 + 一对一辅导

学员人数：为了达到更好的学习效果，课程人数建议50人以内

课程大纲  Outline

计算力：高阶函数组合应用（2.5h）

公式引用——复杂公式必备知识

切换引用：利用快捷键切换绝引用、相对引用和混合引用

动手实践：怎样娴熟的切换三种不同引用方式

公式审核：名称的定义、公式中应用名称、名称的管理

表格样式——形成动态数据源

自动形成汇总行

快速调整行列顺序

结构化引用、自建名称

自动隔行变色及冻结首行

一键快速选择整行、整列及整表

公式输入完成后自动填充到整列

明细表一更新，图表、透视表联动更新

表格不仅内容重要，颜值也很重要

多结果查询——INDEX+MATCH模糊\多结果\照片查询

INDEX+MATCH函数多条件模糊查询：自动匹配数字区间
 

INDEX+MATCH函数查询多个项目：根据员工编号查询员工所有相关信息，并动态显示照片

多功能查询——LOOKUP逆向\多条件\最新数据\包含部分关键词查询

结合SEARCH根据包含的部分关键词，将费用划分到不同栏位
 

条件统计——SUMPRODUCT 15位以上多条件计数和求和

SUMPRODUCT多条件计数和多条件求和

SUMPRODUCT实现无重复计数

设计力：可视图表深度解析（1.5h）

图表选择依据

选择图表类型的误区

确定你所要表达的主题

确定对比关系

选择图表形式

透析五大对比关系：成分\项类\时间序列\频率分布\相关性对比关系

不同图表类型使用注意事项

图表设计思维

面对大段文字，如何提炼出关键词，并为其选择适合的图表

3个凸显重点的方法，重点数据立刻变得一目了然、抓人眼球

掌握图表遵循的设计原则

商务图表案例

均线图：自动让前3的数据序列变颜色
知识点：利用AVERAGE计算均值；利用LARGE计算第几个最大值

对比图：计划（目标\预算）与实际对比，反映实际数据的不足或盈余
知识点：调整分类间距；设置次要坐标轴

子母饼图：展示多个类别数据以及其中某个指定类别的子分类占比情况
知识点：添加数据标签、调整第一绘图区及第二绘图图的内容和图形大小

长标签图：解决标签过长无法正常显示的问题，让标签内置在图表中
知识点：逆序坐标轴、调整重叠比例、添加数据标签
 

重要节点图：在折线图中自动标识重要节点
知识点：利用IF+VLOOKUP函数判断出重要节点位置

四象限图：将事物的两个重要指标作为分析的依据，进行分类关联分析
知识点：在散点图中利用水平和垂直分割线将图表区域分为四个象限

分析利器：多表的交叉透视分析（2h）

多表合并——利用Power Query实现多表合并，源表更新汇总表自动更新

追加查询：多张工作表的纵向合并

实战案例：同一文件夹中结构相同的多个工作簿，动态合并到一张总表中

交叉分析——创建单表数据透视表

查看明细数据、修改汇总方式和显示方式

在数据透视表中应用排序和筛选

明细表增加数据透视表自动更新

将数据按月、季度和月份分类汇总

将年龄按区间分类汇总

将全年销售记录按区域自动拆分

使用组合和计算项

多表透视——将数据加载到Power Pivot实现多表透视

显示Power Pivot选项卡

将数据加载到Power Pivot

建立多表关联

利用Related函数引用其它工作表的数据

实现多表数据透视

全局筛选——添加和修改切片器和日程表

添加切片器，实现多透视表的动态筛选

设置切片器名称及样式

修改切片器大小和行列布局

隐藏或显示切片器

让切片器不显示已删除项目

将切片器内项目自定义排序

日程表格式自定义设置

双日程表联动筛选透视表

数据看板——生成动态仪表板

修改数据透视表样式

数据透视表刷新后保持调整好的列宽

以表格形式显示，重复所有项目标签

数据透视表报告的整理和美化

课程超值福利包

4个图表模板：只需一键，即可生成精美图表

1个模糊匹配工具：可查找两张表中都包含部分关键字的数据

1个动态地图：可根据数据，动态让中国地图不同省份的数据填充不同的颜色

学习资源支持

提供课堂同步学习手册、资源素材以及案例学习样本

课程现场  The Scene

学员反馈  Feedback

甘彬老师的其它课程