Excel进阶-数据统计与分析

Excel进阶-数据统计与分析

我要报名

课程介绍

对于 Excel 这个工具，我们已经知道它很强大，并且已经掌握了较好的运用技能。但是如何使用它帮助我们做更多的工作、提高工作效率？本课程面向具备一定 Excel 基础的人员，想要解决更高级的难题如：如何让报表自动化、避免每个月都要重新制作？如何制作灵活多变的动态图表？怎么对几十个、几百个格式相同的 Excel 文件进行汇总？……学会了马上就能用到工作中，让我们做数据分析、报表的时间大大缩短！

课程收益

了解数据分析步骤，及在采集数据、加工整合环节中所需要的相关技术；

掌握数据统计中常用的函数如 COUNTIFS/SUMIFS/IF/VLOOKUP/INDEX/OFFSET 等用法；

掌握数据分析利器——数据透视表的更多应用技巧；

掌握常见的图表制作技巧，以及利用函数+控件制作动态报表的技巧；

掌握 SQL 的基本查询语法，以及利用 SQL 实现多表合并查询技术；

了解 VBA 开发环境、基本对象模型，掌握用 VBA 宏批量合并数据的方法。

培训对象

从事人力资源、财务管理、数据分析、信息技术及管理人员等

本课程不是 Excel 基础课，要求学员具备一定的运用技能，例如：日常的表格格式设置、打印设置等操作技巧，以及常用的快捷键；会对数据作筛选与排序处理；基本的统计函数如SUM/AVERAGE/COUNT/MIN/MAX；基本的文本处理函数如 LEN/TRIM/LEFT/RIGHT；基本日期计算函数如 TODAY/NOW/YEAR/MONTH/DAY；会制作柱形图、折线图、饼图、条形图等，并会设置图表常见的参数；会使用数据透视表进行简单的报表制作

课程大纲

一、数据分析概述

1 数据分析基本思路

2 明确目的：分析的问题是什么？想验证什么观点？

3 采集数据：数据从哪里来？如何采集？

4 加工整合：数据不规范，如何转化、提取、筛选？

5 分析呈现：使用什么分析方法、分析工具？怎么呈现？

6 报告撰写：得出了哪些结论？怎么组织完整的报告内容？

二、采集数据：规范的表格更利于分析

1 Excel 中的两种表格

2 规范的数据库表格设计要求

3 制作数据采集模板

➢ 限制输入数值/日期的类型与范围

➢ 限制所输入文本的字数

➢ 制作下拉框及二级动态下拉框

➢ 对数据模板添加保护措施

4 获取外部数据

➢ 导入文本中的数据

➢ 导入外部 Excel 文

三、加工整合：表格与数据格式处理

1 批量取消合并单元格并填充内容

2 修改错误的文本格式的数值

3 非法格式的日期快速修正

4 删除数据中的重复内容、只保留唯一记录

5 将一列内容拆分为多列、将多列内容合并到一列

6 神奇的快速填充，一键完成分列与合并

7 数据按条件筛选与随机抽样

8 按照学历/职务/地区等指定顺序进行排序

四、分析工具：常用的函数

1 相对引用、混合引用与绝对引用的区别

2 根据特定条件来计数或求和 COUNTIFS/SUMIFS：销售订单数与业绩统计

3 逻辑判断函数 IF：判断考评标准、计算工龄工资

4 查找引用函数：VLOOKUP/INDEX/MATCH

5 日期函数：DATE/EOMONTH/DATEDIF/WEEKDAY

6 文本处理函数：MID/FIND

7 极值与排名计算：LARGE/SMALL/RANK

五、分析工具：数据透视表

1 透视表创建与应用：按部门、地区、人员等多维度分析

2 设置值汇总依据和百分比显示方式

3 在透视表中筛选及排序

4 组合分析

➢ 对日期生成年度/季度/月度报表

➢ 对数值按不同区间分组统计

➢ 对文本字段组合分类

5 添加分类汇总并居中显示带标签的单元格

6 创建数据透视图：与上一年度业绩同比分析

六、分析呈现：图表制作技巧

1 双坐标轴图表：数据差异较大的系列绘制在一张图表中

2 表达实际业绩与计划业绩比较、相同指标的对比

3 在柱形图上绘制平均线并标注最高与最低点

4 漏斗图显示逐步筛选过程

5 制作销售业绩逐月跟踪图

6 条件格式：制作条形图、提示数据异常、合同到期自动变色提醒

七、进阶技巧：一劳永逸的报表技术

1 一劳永逸”报表要解决的两个问题

2 动态报表技术

➢ 使用数据验证+函数制作：不同分公司产品销量对比

➢ 使用函数+控件制作：按部门查看费用支出明细、滚动显示最新 N 个月数据

➢ 制作交互式动态透视表：按年/月/地区/产品等多维度自由组合条件查看

3 数据源增加了如何追加到报表中

➢ 在函数报表中简单易行的解决方案

➢ 使用 OFFSET 函数定义动态数据源区域

➢ 使用表格让数据源有增加时报表更方便的刷新

4 数组公式的概念及基本应用

➢ 数组及维度的概念

➢ 数组公式的输入方法与运算原理

➢ 应用案例：求绩效总分数、实现 VLOOKUP 向左/多关键字查找

➢ 将不断变化的名单自动提取为不重复的名单

5 利用 SQL 查询实现多表合并

➢ 对同一工作簿中格式相同的多张表格合并查询

➢ 关联查询同一工作簿中多张表的不同字段

➢ 合并汇总不同工作簿中的数据

八、进阶技巧：带你进入神秘的 VBA 殿堂

1 VBA 开发基础

➢ 熟悉 VBA 开发环境

➢ 执行 VBA 程序的几种方法

➢ 条件语句与循环语句

➢ 过程与函数：开发一个自定义函数并在工作表中使用

2 Excel VBA 常用对象的运用

➢ Workbook/Worksheet/Range 对象

➢ Sheets 与 Selection

➢ 实例运用：读取或修改单元格中值

➢ 实例运用：根据指定名单批量创建工作表

3 VBA 开发实战案例解析

➢ 合并同一个工作薄中格式相同的多张工作表内容

➢ 读取一个目录下所有 Excel 文件中的内容

➢ 批量打印姓名牌工具

➢ 某公司工资报表生成系统

王老师

专业背景

聚焦于 Excel/PPT 高效办公应用，微软认证 Office 专家，中关村人才市场特聘讲师，北京培训行业联合会理事，国家职业资格认证企业培训师，2013 年荣获搜狐“最佳 Excel 实战讲师”，2016 年荣获网易“年度风云百强讲师

授课风格

实战案例：使用企事业单位工作场景中的真实案例，学员学会了立马能用到工作中；

启发引导：德宝老师的课程道、术结合。对于道，不是生硬的灌输道理，而是通过案例对比让学员自己感悟到；对于术，德宝老师会亲自示范操作，并加以悉心指导；

严谨治学：认真对待每一堂课、每一位学员、每一个问题。授课稳重中也不乏幽默，但对于只追求课堂热闹气氛而不讲究实效的客户，请勿邀请德宝老师授课。

服务企业

神华集团、中海油、中石化、四川省电力科学院、新浪网、搜房网、凤凰网、北京易宝支付，国电信、大唐电信、北京电信、北京联通、上海电信，建设银行山东分行、中国农业银行、成都银行、包商银行，中国科技大学、南开大学、国家开放大学、北京科技大学，首都机场、南方航空、中国速递物流（EMS）、沈阳三星电子、特步集团、天津富力城、中粮集团、蒙牛集团、杭州丘比、茅台集团白金酒，东风汽车集团、沃尔沃（上海）、中国汽车研究中心、南京汽车、奇瑞捷豹路虎...........

我要报名

在线报名：Excel进阶-数据统计与分析(苏州)

其他城市职业技能培训