|
Excel数据处理高级功能应用实战 【课程目标】 本课程为工具类培训，面向所有想学数据分析工具的人员。 本课程的主要目的是，帮助学员掌握Excel数据分析工具，学会数据预处理，简化工作量，提升效率，避免重复工作；学会数据可视化，直观呈现各种业务信息和含义。 通过本课程的学习，达到如下目的： 1、 熟悉PowerQuery数据预处理操作，能够处理各种数据源 2、 掌握透视表，以及高级的统计函数，实现数据汇总和计算 3、 掌握Excel可视化功能，及制图技巧，制作美观的图形 4、 理解动态图表原理，掌握动态图表的制作 5、 熟悉Excel高级函数的应用 6、 熟悉模拟运算、规划求解等高级功能 【授课时间】 1-2天时间（每天6个小时） 可以根据具体的需求选择部分课程内容。 【授课对象】 销售部、营业厅、市场营销部、运营分析部、业务支撑部等业务及应用人员。 本课程由浅入深，结合原理主讲软件工具应用，不需要太深的数学知识，但希望掌握数据分析的相关人员。 【学员要求】 1、 每个学员自备一台便携机(必须)。 2、 便携机中事先安装好Excel2016版本以上。 注：讲师可以提供分析数据源。 【授课方式】 数据整理 + 分析数据 + 数据呈现 + 工具实践操作 采用互动式教学，围绕业务问题，展开数据分析过程，全过程演练操作，让学员在分析、分享、讲授、总结、自我实践过程中获得能力提升。 【课程大纲】 第一部分： 数据分析基础1、 数据分析的六步曲 Ø 明确目的、收集数据（需要人员参与） Ø 整理数据、分析数据、数据可视化（可以借助工具） Ø 形成结论（需要人员参与） 2、 Excel的主要功能 Ø 数据读取、保存、共享 Ø 数据预处理（整理、清洗、集成、派生等） Ø 数据计算（汇总、统计、透视表等） Ø 数据可视化（图表） 第二部分： Excel数据处理1、 传统工具最大问题：重复工作 2、 数据预处理的四大任务 Ø 数据集成、数据清洗、样本处理、变量处理 1、 Power Query高级功能 2、 多数据源读取 演练：从文本/Excel/数据库/Web页获取数据源 3、 数据组合/数据集成 Ø 样本追加：横向合并 Ø 字段合并：纵向合并/连接类型 Ø 表连接的类型（左/右/完全外连接、内连接、左反/右反连接） 演练3：多表合并，实现在职、离职、新入职员工名单 4、 数据预处理 Ø 文件夹合并 演练1：文件夹下多文件单工作表合并 演练2：文件夹下多文件多工作表合并 Ø 多工作表合并 演练1：单文件多个规范工作表合并 演练2：单文件多个不规范工作表合并 3、 异常数据处理 Ø 重复值处理 Ø 缺失值处理 Ø 无效值/错误值 Ø 离群值处理 4、 Power Query数据预处理实战 Ø 文本字段拆分列/拆分行（按分隔符、字符数、非数字到数字等） Ø 展开列表为行/分组依据 Ø 去重/排序/跳过行/第一行作标题 Ø 新增列/新增索引/新增条件列 Ø 文本提取/文本移除/文本转数字 演练1：销售人员绩效自动排名 演练2：借贷凭证数据处理 演练3：采购计划处理（多表合并处理） 演练4：文本数字混合计算金额 5、 排序和筛选 Ø 排序依据、顺序 演练：学历如何排序（自定义顺序） Ø 筛选：简单筛选、复合筛选、自定义筛选 第三部分： Excel条件格式1、 条件格式：根据条件来设置单元格的格式 2、 条件规则类型及条件 Ø 根据值条件、值排名设置条件 Ø 根据内容条件 Ø 根据公式自定义条件 3、 设置格式 Ø 字体、填充、边框、对齐等等 Ø 已有样式：数据条、色阶、图标集 演练：单元格自行添加边框 演练：高亮显示选中内容的单元格 演练：自动添加箭头显示同比/环比变化 4、 表格美化技巧 Ø 突出显示 Ø 前后项突出 Ø 数据条 Ø 色阶 Ø 图标集 Ø 迷你图 5、 自定义格式：<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT> 6、 Text函数改变数据格式 第四部分： Excel高级函数1、 Excel常用函数种类 2、 文本函数 a) 查找：Find/Search b) 替换：Substitue/Replace c) 提取：Left/Right/Mid/TextBefore/TextAfter d) 合并：Concat/TextJoin/& e) 分割：TextSplit 演练：身份证号提取籍贯、出生日期、性别 演练：住址省市区县乡镇街道等拆分 3、 逻辑函数 a) 条件判断：IF/IFS b) 逻辑运算：AND/OR/NOT c) 函数计算：Let/Lambda/Map/Reduce/ByCol/ByRow d) 名称管理器 演练：自定义函数及调用 4、 信息函数 Ø ISBLANK/ISERROR/ISNA Ø ISNUMBER/ISTEXT/ISFORMULA/ISREF Ø ISODD/ISEVEN/ISLOGICAL 5、 查找和引用函数 a) 值查找：VLookup/HLookup/XLookup b) 序号查找：Match/XMatch c) 条件筛选：Filter d) 提取数据：Take/Drop/ChooseCols/ChooseRows e) 引用函数：Index/Offset/Indirect 演练：销量数据查找 演练：销量数据筛选 演练：下拉菜单、多级下拉联动菜单 6、 统计函数 a) 计数：Count/CountA/CountBlank/CountIF/CountIFS b) 均值：Average/AverageIF/AverageIFS c) 极值：Max/Min/Large/Small d) 分类统计：GroupBy/PivotBy 演练：销量统计、分类统计 7、 数学和三角函数 a) 汇总：Sum/SubTotal b) 条件汇总：SumIF/SumIFS/SumProduct 8、 日期和时间函数 a) 时间提取：Year/Month/Day/Hour/Minute/Second/Weekday b) 时间间隔：YearFrac/Days/Networkdays/Workday 9、 正则表达式 演练：电话号码提取、费用数字提取 10、 分级显示: 组合/取消组合 第五部分： Excel模拟运算1、 单变量求解 2、 模拟运算表 3、 方案管理器 第六部分： Excel规划求解1、 规划求解工具介绍 a) 求取极值的最优输入 b) 约束条件使用 c) 全局最优及局部最优 2、 如何实现产品最优定价 演练：利润最大化下的产品生产与分配 演戏：成本最小化下的产品组成配比 第七部分： Excel数据分析1、 数据统计常用工具 Ø 透视表 Ø 数据分析 2、 透视表原理与操作技巧 Ø 汇总方式 Ø 值显示方式：无、百分比、差异等 Ø 创建动态透视表 3、 透视表高级功能 a) 三种布局 b) 值显示方式：百分比、排名等 c) 总计/小计的显示 d) 合并类别项 e) 分层分级透视表 演练：销售分析报表 4、 数据分析 Ø 描述统计 Ø 相关分析（原因分析） Ø 方差分析（影响因素分析） Ø 回归分析（多因素关键探索与预测） 演练：影响消费水平的因素分析 演练：影响销量的关键要素分析 第八部分： Excel可视化1、 数据呈现的两种格式：图与表 2、 常用图形类型及选择原则 Ø 对比：柱状图、条形图 Ø 趋势：折线图 Ø 分布：直方图、箱线图 Ø 结构：饼图、瀑布图 3、 常用图表画图技巧 Ø 柱状图的画图原则 Ø 直方图的画图原则 Ø 饼图的画图原则 Ø 折线图的画图原则 Ø …… 演练：基本图形绘制(柱状图、饼图、折线图、直方图、散点图/气泡图/象限图) 演练：复杂图形绘制(双坐标图、漏斗图、雷达图) 4、 图形美化原则 第九部分： Excel动态图表1、 动态图表的实质 2、 动态图表实现方式 a) 筛选器、切片器（单行、复选） b) 动态钻取 c) 函数实现（OFFSET/INDEX/MATCH/VLOOKUP/CHOOSE/IF） d) 控件（单行、复选、下拉框等） e) 定义名称 演练：贷款金额计算及模拟运算 3、 名称管理器 结束：课程总结与问题答疑。
| 
