Excel高效数据管理与分析（2天）

课程介绍  Introduction

工欲善其事必先利其器！想提高Excel的工作效率？请先从巧用电子表格开始！

掌握Excel平台的各类技巧，运用所学的各种方法及技巧在往后的工作中解决实际问题。通过有多年实战经验的讲师的演示讲解及亲自辅导，使您系统掌握Excel操作工具的精髓并能现场解决您日常工作中遇到的疑问或提供相关参考，为您开拓解决问题的思路，避免简单模仿操作过程及零散学习的弊端，令您事半功倍。为Excel日常办公人员度身设计的实用课程，边学边练，即时体验成长与进步。

课程通过4个模块，由浅入深、循序渐进的带领走进神奇的Excel世界!

关信息  Information

课程时长：12学时/2天

适合对象：需要使用Excel 进行数据管理和分析的商业人士（中级培训）

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

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

课程大纲  Outline

零乱到有序：表格规范设计（2h）

制表规范——“源数据表”的十宗罪

讨论：工作表结构设计中的常见问题
结构方面：如果不想通过多个IF函数，仅用一个VLOOKUP函数就可以计算出所有科目的等级，请思考右侧的参数表格结构应该如何优化？

数据方面：你能说出这个表格存在哪些数据不规范的问题吗？
 

正确的工作表标题的输入位置

调整页面边距，设置重复打印标题行

一键秒删手工制作的小计行

处理空白单元格、空白行

2022.1.1，这样的日期格式正确吗？快速完成文本\日期\数字格式的强制转换

利用智能填充功能，快速按规律拆分数据
 

精准定位——使用定位精准选择特殊单元格

定位区域、常量、公式、空值和批注

定位行/列内容差异单元格

定位可见单元格、条件格式和数据验证

测试：你能不用复制粘贴，仅仅一个求和搞定所有的小计吗？

妙用粘贴——灵活使用选择性粘贴功能

一键将表格粘贴为图片

快速粘贴为引用区域

粘贴运算

灵活复制列宽

超级行列转置

跳过空单元格，跨行粘贴

数据审核——规范数据输入

输入前温馨提示

制作下拉型输入框

源数据一更新，下拉输入框自动更新

个性化定制错误警告

数据对碰，圈出无效数据，轻松筛选无效数据

动手实践：如何利用数据有效性检查错误输入值
 

条件格式——让重点数据脱颖而出

突出显示规则

最前/最后规则

图标集规则

使用公式定义条件规则

重复数据一键自动标识

用两种颜色数据条突出正负

峰值可视化：一秒用五星标出图书销售最高值
 

用旋风图反映两组数据的对比
 

表格美颜——形成动态数据源

自动形成汇总行

快速调整行列顺序

结构化引用、自建名称

自动隔行变色及冻结首行

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

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

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

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

表格怎样设计才具美感？专业商务表格赏析

向优秀的表格模版学习表格美颜

新建表格样式

插入动态筛选器——切片器

学函数掌技巧：函数组合嵌套（5h）

函数基础——函数基本知识

设置：显示/隐藏公式编辑栏

参数：了解公式的语法和参数设置

搜索：学会搜索函数和查看帮助

窍门：掌握高手才知道的多层函数嵌套的秘密通道

技巧：还在用“拖”的方法复制公式吗？按“Enter”键公式自动填充到最后一行

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

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

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

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

常用函数——熟练掌握各种类型常用函数

逻辑函数：IF+AND+OR多层嵌套时，逻辑树分析来梳理

统计函数：COUNTIFS、SUMIFS多条件计数和求和、模糊统计

查找函数：VLOOKUP的精确\区间\模糊查询、IFERROR屏蔽错误值#N/A
                 VLOOKUP+CHOOSE+MATCH实现逆向查询、多条件查询、多结果返回

文本函数：LEFT+MID+RIGHT文本截取、TEXT格式转换

日期函数：DATEDIF计算日期间隔、EDATE几个月之前或之后的日期、TODAY当天日期

问题分析：函数出错的原因分析和排错方法

功能结合：与条件格式和有效性结合，限定输入并突出重点

颠覆认知——你知道函数在不同场景中的用法吗？

你知道函数的参数使用名称引用，数据能自动扩展的功能吗？IF函数在多条件判断的情况下，如何轻松嵌套并做到完全不会出错吗？

在不改变列的顺序的情况下，实现VLOOKUP函数的逆向查询吗？

如何根据一个查询条件带出多个相关结果，并把公式一键填充到所有的空白单元格吗？

返回相同查询条件的多个结果

查询出两张表格的差异

如何实现合同到期提醒

能说话会表达：图表可视化分析（2h）

图表基础——认识图表中的各个元素

坐标轴：最大值、最小值、间隔单位、显示单位、排序、显示次坐标

系列：填充色（用互补色突出显示负值）、间距（柱形/条形图）、线型、数据标记（折线图/散点图）、分析线（趋势线、误差线、连接线）

图例：位置、显示项目

网格线：线型、颜色

数据标签：字体、位置、定制数据标签

图表标题：链接单元格，让标题自动更新

添加或删除图表元素

决定保留的元素、数据范围和序列

重调系列的顺序、图表的美化技巧

编辑数据源和更改图表类型

制图原则——为数据选择适合的图表类型

数据变成图表的过程

选择图表类型的误区

确定你所要表达的主题

成分\项类\时间序列\频率分布\相关性对比关系

图表选择矩阵

饼图\条形图\柱形图\折线图\散点图，五大图表类型的使用场景和注意事项

高级图表——生动刻画趋势差异关系

图表易容术：怎样用图片来呈现图表的数据

用两种颜色的图片，展示数据的百分比
 

高级图表——生动刻画趋势差异关系

复合条饼图：增强占比值较小数据的可读性

单层圆环图：可以在圆环的中心显示总体情况

平滑折线图：显示更好的显示数据变化趋势

逆序条形图：更直观的呈现数据的排名

多图表组合：柱形+折线的组合图表，体现目标或平均线

次要坐标轴：将数据差异较大的值通过主、次坐标分别展示

从静态到动态：筛选排序和数据透视（3h）

三表思维——用数据库的标准化思维来规范表格结构

参数表：系统配置参数

明细表：利用数据有效性、函数与参数表关联

汇总表：数据透视表多维度分析

数据排序——归纳常用但易被忽视的排序功能

多关键字排序

按颜色排序

导入自定义排序列表

自动筛选——剖析典型筛选数据的应用方法

文本筛选：包含某些关键词

日期筛选：按年份、季度、月份筛选

数字筛选：大于、小于、10个最大值

复制筛选后的数据区域

高级筛选——筛选多组无规律数据

设置多个筛选条件

筛选结果的存放位置不同

可筛选不重复记录

不包含单元格引用的筛选条件

包含单元格引用的筛选条件

结合公式的条件筛选

多条件筛选分为“条件与”、“条件或”

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

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

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

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

什么是数据透视表

了解数据透视表的使用情境

了解透视表相关术语

掌握数据透视表的组成结构

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

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

对数字、日期、文本进行分组

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

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

问题解决——数据透视表常见问题分析

为什么会多出一行空白数据

为什么日期数据不能组合

案例解析——数据透视表案例分析

筛选各地区各产品的销量前10名

计算各地区、各产品占销售总额的百分比

计算每年每个月的销量、累计销量及年度占比

当期的数据较去年同期变动率（YOY）

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

修改数据透视表样式

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

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

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

课程超值福利包

20份精美专业的报表模板

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

常用公式教程

图表的10个提示

数据透视表基础和进阶教程

学习资源支持

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

提供表格、函数、图表和透视表功能场景卡，方便学员参考使用

课程现场  The Scene

学员反馈  Feedback

甘彬老师的其它课程