数据分析必备——Excel基础知识

数据说·梦想季

海量碎片化信息使人们内心越来越焦虑，人们渴望内心的安宁，渴望缓解精神压力，解除审美疲劳。数据之路，与你同行!——数据说·梦想季

导读：大数据时代，数据分析已经是每个行业成果的必经之路了，更是职场核心竞争力之一，它可以帮助我们找出真实世界的规律，辅助我们进行决策和验证。通过数据表象，看到背后的本质，发现问题，给出解决方案。

例如，分析全国销售的业绩，面对一堆杂乱无章的数据源表时，要让销售人员看到自己的业绩情况，发现不足，及时改进。我们就需要展示每个销售人员的业绩，不仅能和历史业绩对比，还能和其它销售业绩进行横向、纵向的比较。

这时，我们若熟练掌握Excel使用以及函数，几分钟就能完成，而别人也许倒腾1天的工作量哦。

可见，Excel的使用对于数据分析的重要性不言而喻！（本文只是对Excel简单的梳理，对于具体的知识点及函数应用，本文就不一一介绍了，感兴趣的同学请参考《Excel数据分析》、《中文版Excel2019宝典》等专业书籍，若不想买书，可以添加木兮，领取Excel学习资料！）

那么，接着上一篇文章《数据分析必备——统计学入门基础知识》，咱们今天来简单的梳理一下数据分析必备——Excel中常用的统计函数。本文所有公式需均结合实例，讲为辅，练为主，基础扎实伙伴可以直接跳过，其他伙伴可以当做回顾和复习。

01
计算公式

在Excel我们经常会遇到计算，计算公式其实很简单，‘=’后面加对应的函数，并取函数的参数就可以了，以下图的数据为例，我们来计算每件商品的销售利润，我们知道，销售利润=（售出单价-成本价）*销售数量，那我们在Excel中怎么列计算公式呢？其实很简单，把对应的值进行转换就行了，操作看下图： 

销售利润=(D2-B2)*C2。有5个常用的运算符：加（+），减（-），乘（*），除（/），幂（^），运算符是需要前后有单元格引用的，单个单元格不生效。

02
描述性指标

我们在对一组数据进行观察的时候大多用几个指标来展示整体数据情况。

集中程度的指标有最大值、最小值、平均值、中位数、众数等，在Excel中也存在对应的函数。

l 最大值：取一组数据的最大值，公式为= MAX（number1，number2）

l 最小值：取一组数据的最小值，公式为= MIN（number1，number2）

l 中位数：取一组数据的中位数，公式为= MEDIAN（number1，number2）

l 平均值：取一组数据的算术平均值，公式为= AVERAGE（number1，number2）

l 众数：取一组数据的众数，公式为= MODE（number1，number2）

03
箱线图

离散程度的指标有极差、四分位间距、方差与标准差、变异系数，这些数据还可以用一个更直观的箱线图表示出来，以销售数量为例展示:

再来解释一下箱线图的指标的含义，看下图

绘图方法：

1、 选中需要绘图的数组，这里选中的是C2:C10；

2、 点击【插入】，选择图形类型；

3、 找到箱线图，绘制完成，其他图也是如此。

04
IF函数

IF函数是常见的比较函数，通常会用在值域转换上，来看IF函数的公式解析。

例：在销售数量中大于20的评判为“优“，小于20的评判为”-“，公式为=IF(C2>20,"优","-")

上图是二分类的判断，当条件有多个的时候就需要做嵌套了，嵌套的if详细解析：

IF（条件1，真值，IF（条件2，真值，IF(…IF(条件n，真值，假值))））

例：销售数量大于30的为“优”，20-30的为“良”，10-20的为“中”，小于10 的为“-”，公式为=IF(C2>30,"优",IF(C2>20,"良",IF(C2>10,"中","-")))

05
VLOOUP 函数

VLOOKUP是Excel常用的主要函数之一，我们做统计分析的时候数据往往分布在不同的表中，我们要把这些字段联合在一起就需要用VLOOKUP函数。 

例：在文具店的2张销售数据表中，需要按照各个商品进行整合。

1、 分别打开表1和表2；

2、在表2的成本价这列的单元格中输入公式；=VLOOKUP(@A:A,[表1.xlsx]Sheet3!$A:$B,2,0)，第1个参数直接选中A列需要查找的值，第2个参数选中表1中我们需要查找范围数据区域A、B2列，第3个参数输入我们要查找列数，这里是第2列，第4个参数，输入0（0为精确匹配，1位模糊匹配）。参数意思是VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配)。

3、 填充至整个表格。

VLOOKUP函数在WPS和Excel里都是可以使用的，学完别忘了要多上手练习！

06
数据透视表

数据透视表，可以说是EXCEL的核心武器了。

理论上讲，数据透视表是excel提供的一种交互式的强大数据分析和汇总工具。可以总结为：数据透视表是可以把一个明细表进行分类汇总，而且可以随意改变汇总模式的一种工具。

明细表?分类汇总？工具？云云.......总之，数据透视表的用途如果细数会有很多，但最基本、最常用的是“分类汇总”，所以当我们需要对一个流水账式的明细表进行分类汇总时，就需要使用数据透视表。听起来还是有点抽象，我们还是看实际的数据操作比较直观。

下面我们结合一组数据来一探究竟：

源数据是2020年3月-9月的销售数据，每一行代表一笔交易，数据涉及7个关键字段“订单序号”、“日期”、“省份”、“城市”、“销售额“、"销售数量"、“客单价”。如果我们想知道每个月，每个省份销售额是多少，该怎么办呢？

我们先选中所有列，在插入模块选中“数据透视表”。

接着就是选择数据透视表存放的区域，在向导的第一步一般是默认选项，不需要设置，直接点确定即可。默认是新工作表，大家在实际操作中也可选择现有工作表的区域。

会自动新建一个工作表，且在工作表中会有一个数据透视表空白区域，其他的什么都没有，需要我们安排数据具体的位置。大家注意右侧的“数据透视表字段”区域，这里是透视表的核心控件。但是我们需要“计算每个月，每个省份的销售额”，那就是按照“月”和“省份”来进行分组了。

以哪个字段分组，就将哪个字段拖到行或者列。

添加数据透视表项目

·把日期拖动到行标签；

·把省份放在列标签；

像下面这样：

左侧数据透视表结构区域随着我们的拖动发生了变化，刚才我们把日期拖动到行，把省份移动到列，果然，数据透视表布局和我们操作一样的：

数据透视表分组逻辑为判断是否唯一，如果唯一则单独分为一行（或一列），想要把行标签的日期格式变成月的维度。

需要我们选择上面的菜单，选择“组选择”：

点击“组选择”之后会出现如下选项卡：

起始时间默认是源数据中最早和最晚时间，这里不用更改，我们想以月的维度创建分组，所以选择“月”。

这透视表分组，行是月份，列是省份。

我们要计算涉及到的核心字段是销售额，在已经分好组的情况下，只需要把销售额字段拖到拖动到数值区域：

一个数据透视表的雏形已展现在我们的面前：

大家注意，我们刚才把销售额拖动到数值区域，一般情况下，默认是“计数项”，数据透视表现在显示的每个值，指的是订单数量，如果要计算销售额，要再点击“销售额”字段。

进入“值字段设置”。

这里的“计算类型”选择“求和”，我们就得到各月各省的销售额总和，“平均值”就是各月各省销售额平均值，最大值、最小值依然。（我们最常用的也就是这几个）

最后：各省、各月销售额一目了然。

但是，在制作数据透视表，需要注意以下几点：

1、空字段名包括空列，无法制作数据透视表；

2 、相同的字段名，会自动添加序号，以示区别 ；

3 、字段所在行有合并单元格，等同于空字段，也无法创建数据透视表； 

4 、如果有空行，会当成空值处理。

本次的Excel基础知识就介绍到这里，虽然没有梳理的太深，但已经涉及了大部分数据工作中常用的操作和公式。其实，Excel最重要就是数据透视表的使用，但是现在wps的便捷，基础的数据透视的使用还是很简单的，如果你对数据分析感兴趣，欢迎加入关注我们，一起学习！

精进不休

尺有所短，寸有所长，持之以恒，坚持不懈！就数据分析而言，Excel重要性不言而喻！如果你想看到问题本质，学数据分析入门，Excel也可以说是最简单轻松的途径了。

图片来源于网络

若对社群的好的建议和想法，欢迎在下方留言

加入社群

即可获得数据分析资料一份

我们将尽其所能打造数据分析交流的理想之地