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

课程介绍  Introduction

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

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

相关信息  Information

课程时长：6学时/1天

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

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

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

课程大纲  Outline

起点-从零乱到有序：表格规范设计（1.5h）

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

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

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

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

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

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

处理空白单元格、空白行

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

效率倍增——快捷技巧大幅提升工作效率

填充：一键将数据或公式批量填充到所选单元格

定位：空值、公式、常量等特殊单元格无所遁形

粘贴：选择性粘贴还可以这样用，探究不为人知的用法

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

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

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

输入前温馨提示

制作下拉型输入框

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

个性化定制错误警告

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

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

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

突出显示规则

最前/最后规则

图标集规则

使用公式定义条件规则

重复数据一键自动标识

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

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

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

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

形成汇总行

快速调整行列顺序

结构化引用、自建名称

自动隔行变色及冻结首行

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

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

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

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

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

修改前：

修改后：

中场-学函数掌技巧：函数组合嵌套（2.5h）

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

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

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

引用：掌握绝对引用和相对引用的原理和切换方式

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

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

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

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

查找函数：VLOOKUP+CHOOSE的精确\区间\多条件\模糊查询、IFERROR屏蔽错误值#N/A

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

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

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

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

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

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

如何根据销售员的职务和销售额，计算出奖金吗？

如何查询出两张表格的差异吗？

终点-从静态到动态：透视交叉分析（2h）

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

参数表：系统配置参数

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

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

数据整理——明细表的排序和筛选

按数值、颜色快速排序

日期、文本、数字筛选

筛选前N条数据

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

了解透视表相关术语

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

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

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

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

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

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

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

为什么日期数据不能组合

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

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

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

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

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

图表基础——生动呈现的图表绘制技巧

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

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

修改图标颜色、风格和样式

格式化坐标轴、设置图表选项

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

基于双层图片叠加图，激活学员的创造思维，制作出温度计图
 

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

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

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

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

动态高点图：自动用图标标识折线的最高点

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

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

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

课程超值福利包

20份报表模板：观摩和学习优秀报表的设计

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

常用公式教程

图表的10个提示

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

学习资源支持

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

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

课程现场  The Scene

学员反馈  Feedback

甘彬老师的其它课程