数据分析 - Excel

前言

Excel 已诞生很多年，坊间也不乏各种奇技淫巧，但在个人看来，工具终归只是工具。推崇 Python 之禅的一句：There should be one— and preferably only one —obvious way to do it.

数据分析师要做的事，不是炫技，而是利用工具完成自己的目的：分析数据，得到结论，以改善产品。

因此，在这里，只对各种目的提供一种方法。如果以后还有更好的方法，那肯定会直接使用更好的方法。

另：如果有不会的地方，使用搜索。只要会搜索，没有找不到的东西，也就没有没有学不会的东西。

Excel 版本：2016

数据清洗需要用到的方法

查找

Ctrl + F

通配符：

• • ：替代任意数量的字符
• ? ：替代一个字符

示例

目的：找到书名含有“马克思”的书籍

替换

• Ctrl + H
• 定位：F5 / Ctrl G

示例

目的：找到含有“数据”一词的书名，并标记为红色

删除重复值

”数据“ → ”数据工具“ → ”删除重复项“

示例

目的：删除数据中重复数据

删除之前：

删除时选项：

删除后：

筛选

• “开始” → “排序和筛选” → “筛选”
• 制表：Ctrl + T

示例

目的：筛选出“ A ”列为 2 的数据

筛选前：

筛选选项：

筛选结果：

数值格式转化

每一个数据都有格式，比如文本格式、日期格式、数字格式等等。

Excel 中的数据格式位置：“开始” → “数字“ → 下拉项“常规”

如何更改数据格式：

• 选中数据 → 下拉项“常规”
• 选中数据 → 选中区域鼠标右键 → “设置单元格格式”

数据可视化

Excel 中的数据可视化主要有两种，一是图表，二是条件格式。

图表

图表有很多种，直方图、条形图、折线图、扇形图…

下面是一张图表的要素点。

这里只简单记录每种图的特点以及做法，不说优化之类的。

折线图

折线图多用来表示某种趋势，可以是任意组数据。

示例：

Excel 绘制折线图的方法如下：

选中数据 → “插入” → “图表” → “折线图”。

柱形图

最常见的图表之一，表现类别之间的关系，其特点是学习成本低，受众更容易接受柱形图想要表达的信息。

示例：

Excel 绘制柱形图的方法如下：

选中数据 → “插入” → “图表” → “柱形图”。

直方图

直方图其实是柱形图的延伸，多用来反映数据波动情况，便于快速确定产品需改进地方。

示例：

Excel 绘制直方图的方法如下：

选中数据 → “插入” → “图表” → “直方图“。

散点图

散点图多用来展示两件事之间是否存在某种关系。延伸有单轴散点图。

示例：

Excel 绘制散点图的方法如下：

选中数据 → “插入” → “图表” → “散点图”。

气泡图

气泡图是散点图的变种，其添加一个变量：气泡大小。气泡越大，其值越大，省却再去观察值大小的时间。

示例：

Excel 绘制气泡图的方法如下：

选中数据 → “插入” → “图表” → “气泡图”。

面积图与折线图类似，但不仅仅是趋势的变化，还强调熟练随时间的变化。

示例：

Excel 绘制面积图的方法如下：

选中数据 → “插入” → “图表” → “面积图”。

树形图 / 树状图

树形图 / 树状图常用来表示同一等级中不同种类的占比，典型如软件 WizTree 的效果图。

示例：

Excel 绘制树形图 / 树状图的方法如下：

选中数据 → “插入” → “图表” → “树状图“。

雷达图

多用于表现某件事物的各方特质，游戏中常用来表示某个英雄人物的各种属性值。

示例：

Excel 绘制雷达图的方法如下：

选中数据 → “插入” → “图表” → “雷达图“。

箱型图

箱型图用来研究和观察数据分布，同时也能对比数据分布。

示例：

饼图

不建议使用。常用于表示占比。但，除 PPT 上使用，其余地方使用的话，对解释数据、传递信息没有太大帮助，反而可能会难于解读，甚至可能造成误解。

示例：

Excel 绘制饼图的方法如下：

选中数据 → “插入” → “图表” → “饼图“。

圆环图

饼图的变种，亦不建议使用。

示例：

Excel 绘制圆环图的方法如下：

建立辅助列 → “插入” → “图表” → “推荐的图表” → “所有图表” → “饼图” → 最后一个图型。

相关示意图：

替代方案：如果数据类型不多，可以使用柱形图或其他图；数据类型多，则可以使用树形图。

热力图

热力图是通过颜色饱和度来提供视觉上的暗示，帮助阅读者更快的捕捉到潜在兴趣点。常见的热力图包括地理热力图、网站访问数量热力图等等。

示例：

Excel 的绘制方法如下：

选中数据 → “条件格式” → “色阶”

如果是地理热力图，则使用 Excel 的“三维地图”。

组合图

Excel 的图表功能有个强大的功能，就是“组合图”，可以自定义画出任何组合图，包括水平线和垂直线、双 Y 轴图、双 X 轴图、漏斗图、瀑布图等等。

水平线和垂直线

这两种线主要用来作参考。

示例：

Excel 的绘制方法如下：

建立辅助列 → 选中数据 → ”插入“ → ”图表“ → ”推荐的图表” → “组合图” → 自选
相关示意图：

垂直线的生成方式类似。

双 Y 轴图 / 双 X 轴图

不建议使用。双 Y 轴图，即有两条 Y 轴，一个主轴，一个次轴。

示例：

替代方案：

• 直接添加标签
  

• 竖直分割
  

• 用颜色将数据和坐标轴联系起来

双 X 轴图使用频率过低，因此没学习。

漏斗图

漏斗图常用来看转化率、流量分布等，适合用于单向流程分析。

示例：

Excel 的绘制方法如下：

在 Excel 中绘制漏斗图，实质上用的是堆积条形图。

建立辅助列 → “插入” → “图表” → “更多图表” → “堆积条线图” → 双击纵坐标轴 → “逆序类别” → 选中辅助列图 → “填充 → 无填充” → “边框 → 无线条”。

备注：Excel 2019已有该功能。

瀑布图

瀑布图能生动看到数据的变动情况。

示例：

Excel 的绘制方法如下：

建立辅助列：

“选中数据” → “插入” → “图表” → “推荐的图标” → “柱形图” → “堆积柱形图”

而后选中图表，双击下方柱形图，“填充”选择“无填充”，“边框”选择“无线条”，再双击单个数据标签，删除。重复该操作，最终得到结果如下：

其他

Excel 中还有一个迷你图，主要包括折线图、柱形图、盈亏图，可以快速得知数据的走向趋势。

具体位置：“插入” → “迷你图“。

也可以直接选中数据，而后右下角会有个小图框，点击小图框可以快速制图。

在线资源：

图表用法 - AntV

Material Design最新数…-UI中国

条件格式

条件格式是 Excel 的功能之一，可以非常直观显示重用数据，其包含的功能包括：

• 突出显示单元格规则
• 项目选取规则
• 数据条
• 色阶
• 图标集

具体跳过。

数据透视表

数据透视表，其实不需要知道它是什么，只要知道它能做做哪些事以及如何去做就可以了。

能做哪些事：对数据进行快速统计计算，还能帮助用户处理、分析数据，从不同的维度对数据进行分析。

怎么做：第一表格要符合三表结构，第二表格要符合“天下第一表”格式。

怎么创建：

选中数据 → ”插入“ → ”数据透视表“。

切片器

切片器是另一个工具，可以快速选择要查看的数据。

函数

Excel 的一大特色就是各种函数，要理解一个函数，其实最简单的方法就是用自己的话把其作用和功能描述出来，其他的函数，如果不会，可以查看相关文档，函数其实很容易理解。

而常用的函数有以下：

时间函数

Year：

作用：返回某个日期值的年份

用法：Year(目标日期值)

示例：=YEAR(NOW())

Month：

作用：返回某个日期的月份

用法：Month(目标日期值)

示例：=Month(NOW())

Day：

作用：返回某个日期的天

用法：Day(目标日期值)

示例：Day(now())

Weekday：

作用：返回周几

用法：WeekDay(目标日期值)

示例：=WEEKDAY(NOW())

Weeknum：

作用：返回第几周

用法：Weeknum(目标日期值)

示例：=WEEKNUM(NOW())

Ctrl + ; ：

作用：返回当前日期

Ctrl + Shift + ; ：

作用：返回当前时间

Now：返回当前日期和时间

用法：Now()

文本函数：

Left：

作用：返回从左起第一个数字到规定数量字符

用法：left(目标文本,保留字符数量)

示例：

Right：

作用：返回从右起第几个数字

用法：Right(目标文本,保留字符数量)

示例：

Mid：

作用：指定起始位置并保留指定字符数量

用法：Mid(目标文本,起始位置,保留字符数量)

示例：

Find：

作用：查找字符（串）在另一字符串中第一次出现的位置

用法：Find(“查找内容”,“查找位置“,[开始查找的位置])

示例：

concatenate：

作用：合并

用法：Concatenate(字符串1,字符串2,…)

示例：

len：

作用：返回字符串长度

用法：Len(字符串)

示例：

逻辑函数

IF：

作用：逻辑检测函数

用法：IF(条件,如果条件为真返回值,如果条件为假返回值)

示例：

AND：

作用：逻辑判断，如果所有条件为真则返回布尔值 TRUE，否则返回 FALSE

用法：AND(条件一，条件二)

示例：

NOT：

作用：逻辑求反

用法：NOT(条件)

示例：

OR：

作用：逻辑判断，任一条件为真则返回 TRUE，否则返回 FALSE

用法：OR(条件一,条件二,…)

示例：

统计函数

AVERAGE：返回平均值

SUM：返回总和

COUNT：统计数字出现的次数

查找与引用函数

VLOOKUP：

作用：在指定位置查找值

用法： VLOOKUP(想要查找的内容, 查找范围, 查找第几列的值，是否精确查找)

示例：

HLOOPUP 的用法与此类似。

其他

常用快捷键

一般而言，自己可以统计或注意平时比较常用的操作有哪些，而后再去背记相关快捷键。

• 绝对引用：F4
• 复制：Ctrl + C
• 粘贴：Ctrl + V（不建议使用，建议使用选择性粘贴）
• 快速填充：Ctrl + E

三表结构

三表即“数据源表、过程表、呈现表”。

数据源表：保存数据源的表，不可污染、不可改变数值。

过程表：数据处理过程的表。

呈现表：数据处理的结果展示表。

参考：为什么每个excel里有3个表格？ - 知乎

制表时需要注意的事项

同类同列

错误示例：

正确示例：