数据分析基础——EXCEL快速上手秘籍

作者 | 周志鹏

来源 | 数据不吹牛

EXCEL从入门到熟练？缺乏体系和数据源？练好这篇就够了！

这篇文章是本系列的第一篇，选择性汇总了EXCEL的常用且重点的模块和公式， 用作内部员工EXCEL基础操作培训，以帮助表格基础薄弱的同事快速熟悉常用操作，提升工作效率。 现将内容分享，作为数据分析基础的第一篇。

所有公式均结合实例（本节课以小例子为主），讲为辅，练为主，实例数据附在文章最后，也可在公众号导航栏“实战数据”获取。

陈独秀（基础扎实）童鞋可以直接跳过，其他同鞋可以当做回顾和复习。这，将是后面数据分析的公式(EXCEL）基础（下一篇将会是实战篇）。

文章略长，大家可以先马后看，当然更重要的是实践。

P1 基础操作模块：

1.1、数据透视表：

开篇神器必谈透视表，它可以说是EXCEL的核武器了，杀伤力爆表。不过有一点和核武器不同，它不仅灰常重要，还经常在实战中使用。

百度定义是这样的：数据透视表（Pivot Table）是一种交互式的表，可以进行某些计算，如求和与计数等。

Emmm，各位看完之后有没有一个特别清晰的概念呢。反正我是没有的。

我觉得数据透视表就是一个 快速分组，并基于分组个性化计算的神器。

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

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

在学会透视表之前我会靠着缜密的思维和坚韧的毅力人肉计算。掌握透视表之后我发现当初缜密的思维和坚韧的毅力都是傻逼的表现。

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

接着就是选择数据透视表存放的区域，默认是新工作表，大家在实践中也可选择现有工作表的区域。

始的透视表什么都没有，大家注意右侧的“数据透视表字段”区域，这里是控制透视表的核心地带。

我们的问题是“计算每个月，每个省份的销售额”，那就是按照“月”和“省份”来进行分组了。

以哪个字段分组，就将哪个字段拖到行或者列 ，像下面这样：

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

等等！我们是想知道每个月的销售情况，为什么这里出现的是“年月日 时分秒”的格式？

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

我们选择行标签的单元格，右键选择“创建组”：

点击创建组之后会出现如下选项卡：

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

这透视表分组，如你所愿了，行是月份，列是省份。

分组完了，下面就是 个性化计算 ，我们要计算涉及到的核心字段是销售额，在已经分好组的情况下，只需要把销售额字段拖到值的位置：

数据透视表随之改变，大功告成。。。了吗？

别急，大功只差一步，大家注意，我们刚才把销售额拖动到值的位置，默认是“计数项”，也就是说，数据透视表现在显示的每个值，指的是订单数量，如果要计算销售额，要再点击“销售额”字段，

进入“值字段设置”，

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

最后才算大功告成：

各省、各月销售额，一目了然。

1.2、分列：

很多时候，我们拿到的源数据某一列是按一定规律混杂的，而我们需要把它分成多个列，从而有侧重的分析。

假如我们从数据库中导出的数据是这样的：

省-市混在一起，正常分析我们当然需要将省和市拎出来单独分析，很简单，选中源数据所在的列，点击“数据”选项卡，选择“分列”

这时候会蹦出分列的逻辑

第一种是按照分隔符号分列，

规性的符号有Tab键、分号、逗号，这里我们分列的依据是中文波折号，所以勾选其他，手动输入“——”，需要注意，上面源数据里，中文波折号是两个短线构成，而这里手动输入最多只能输入一条短线：

输入之后我们会看到源数据列已经被分成3列（系统默认按照单个短线划分，省—空行—市），正常情况我们是想分成两列的，只需要勾选“连续分隔符号视为单个处理”即可。

有一种分列逻辑是按照固定长度，适用于规律非常明确的源数据，只需要自己移动分割线的位置，就能实现源数据的自定义分列：

结果就是把省的名称和”省“字分成单独两列。

1.3、删除重复项：

顾名思义，就是删掉重复的项，这个项指的是行。

选中数据，点击“数据”选项卡下的“删除重复项”

弹出删除界面：

默认是全选，但一定要慎重，假如我们单勾选A，就是只判断A列中的值是否重复，若重复则删去（单选B则删B），这里我们选单选A尝试，

结果反馈：

删除后的数据：

源数据中，第6行杭州的钢铁侠和北京的钢铁侠都被删除了，毕竟钢铁侠只有一个。

但是！钢铁侠只有一个，并不妨碍我前室友曾自诩“穷版钢铁侠”啊，同理，杭州的钢铁侠可能和北京的钢铁侠并不是一个人。

因此，需要同时判断姓名和城市，如果都重复才会删除，只有一个重复则保留。要实现这个逻辑，只需要按照默认勾选，同时选A和B就可以了，结果如下：

OKAY~That is it!

P2 函数模块：

二、字符串相关：

2.1 LEN、LENB

LEN（字符串）和LENB（字符串）是俩兄弟，他们都是用来衡量目标字符串长度的，但度量维度有所不同。

简单来说，英文和数字的话，用LEN(TEXT)和LENB(TEXT)得到的数字是一样的，而汉字，LEN(TEXT)中，一个汉字是1个长度，LENB(TEXT)则是2个。

2.2 TRIM和SUBSTITUDE

上面两个函数专用于清除空格，只是他们清除的空格位置不同。

TRIM(单元格)清除的是目标单元格前后的空格，

而SUBSTITUDE(单元格）清除了目标所有空格，包括字符串中间的空格。

2.3 CONCATENATE 和 &

常用于连接多个单元格内容：

现在有这几个单元格

我们想把A8,A9,A10单元格中的内容连接起来，很简单，用CONCATENATE 或者 & （他们实现的是一样的链接效果）：

2.4 LEFT,RIGHT

他们语法逻辑是一样的，拿LEFT来说，他有两个参数

LEFT(TEXT,NUM)，第一个参数输入你要提取内容所在的位置（单元格），第二个参数是一个数字，也就是你想要从左边开始，提取多少位，LEFT（text,3），就是从左边起，提取3个字符，RIGHT(TEXT,3)是从右边起，提取3个，下面是一个简单的例子：

我们想要分别提取A13单元格，左边3个字符，右边5个字符：

2.5 FIND,SEARCH,MID

上面LEFT和RIGHT是很粗暴的提取方法，而MID就显得更加温婉和灵活了，

有一串这样的文本，而我们只想要提取其中的数字部分，该怎么做呢？

很简单，输入“=MID(TEXT,4,4)”即可，MID有3个参数，第一个参数依然是目标单元格，第二个参数规定了从第几个字符开始提取，第三个参数是说提取几位。上面的公式是说我们从第4个字符开始，提取其后的4位，结果如下：

咳，提取问题加大难度：

假如我们想要提取上面“省-市”单元格中的城市部分 ，怎么办呢？（比如武汉市、杭州市..）

首先你想到了MID函数，很棒！但是MID需要指定从第几个字符开始，这里“黑龙江省”和其他省长度不一样，不能够硬性指定从第几个字符开始，也不能强制性指定截取多少位，如果有个灵活查找固定字符出现位置的函数，我们MID就可以用了。这个时候，FIND和SEARCH函数闪亮登场！

FIND（要查找什么，TEXT，从第几个开始）和SEARCH函数都有3个参数，第一个参数是我们想要查找的内容，这里也就是“省”，第二个参数是在哪里查找，即目标单元格，我们以A28为例，第三个参数是从第几个开始查找，可以根据需要设置，此处我们设置为1。对应函数和结果如下：

到这一步，MID函数第一个参数（提取谁）有了，第二个参数（从哪里开始截取）也有了，还差一个截取长度设定。仔细观察目标函数，发现规律了吗？没错，我们可以再次利用FIND或SEARCH函数，找到“市”出现的位置， 用市出现的位置减去省出现的位置 ，不就是我们要截取的长度了？

注意，MID（TEXT,从哪里开始，截取几个），我们刚才拿到“省”的位置，还需要加1，才是正确的开始位置。到此，3个宝石在手（参数），MID函数打了个响指，喏，就是这样：

FIND和SEARCH函数语法相近，需要注意的是FIND区分大小写，SEARCH不区分 ，举个简单的栗子：

用FIND查找“D”，会严格找到大写的“D"，而SEARCH不区分大小写，遇到小写的"d"就停止了搜索。

现在，你掌握了LEFT,RIGHT,MID,FIND,SEARCH，结合源数据打个响指试试呗：）。

三、日期函数：

3.1 时间函数：YEAR,MONTH,DAY,WEEKDAY,HOUR,MINUTE,SECOND

上面7个公式，其实本质都是一样的，那就是获取目标日期的对应模块。

比如year(时间)得到的就是年份，month(时间)会返回月份，minute(时间)得到具体的分钟数，second（时间）亦然。至于weekday嘛有点特殊，他有两个参数：

第一个参数和前面介绍的函数一样，就是目标时间，后面的参数选项比较多了，大家可以尝试一下：

咳，同志们，不要被参数所迷惑，我们使用weekday是想知道目标时间是星期几，这个星期几我们习惯是从星期一开始算的，默认选择2就OK。

下面是一个小例子：

上述这些GUYS在实践中常用于构建辅助列。

3.2 DAYS

DAYS（结束日期，开始日期），输入结束日期和开始日期，DAYS函数会计算返回两个日期的相差天数：

四、逻辑与条件判断：

4.1 AND,OR

AND（参数1，参数2，..)，AND参数个数不限，每个参数是一个判断，比如(A1>0)，每个判断回返回一个TRUE（A1确实大于0）或者FALSE（A1小于等于0），如果每一个参数返回的都是TRUE，AND会返回一个TRUE，如果有一个返回FALSE，AND则返回FALSE。

OR用法和AND一样，不同的是，只有当所有的参数返回FALSE，OR函数才会最终返回FALSE，否则会返回TRUE。

概括来说，

AND是（参数）全为真（TRUE）时才为真（TRUE)

OR是（参数）全为假（FALSE）时才为假(FALSE）。

他们通常结合IF条件判断函数使用。

4.2 IF

IF(判断条件，如果为真执行的操作，如果为假执行的操作），

一个简单例子：IF("数学>90“，”优秀“，”不够优秀“)，第一个参数会判断数学是否大于90分，如果大于就返回TRUE，程序会自动执行第二个参数里面的指令，这里是显示”优秀“，否则则执行第三个参数（FALSE）时的指令。

简单嵌套一下：IF("数学">90,"优秀",IF("数学">80,"良好",IF(”数学">60，“及格”,“不及格"）

别晕，一层一层看，显示判断数学是否大于90分，大于就是优秀，否则再判断是否大于80（小于90的情况下），是则返回“良好",不然继续判断是否大于60，大于60是及格，小于就是不及格。

需要注意的是，IF函数可以不断嵌套。

IF大哥出镜率很高，我们再来引入一个情景集合AND函数巩固一下，我们这里有ABCDE五位男嘉宾，有颜值和身材两个打分维度，1的话代表公认具备，0的话代表不具备，

打分后的结果是这个样子：

我们需要判断每个男嘉宾属于什么类型，如果颜值和身材并存（都是1），自然是男神了，如果颜值1身材0，暂且归为”靠脸吃饭“，如果只有身材没有颜，就是”肌肉男“，最后，如果什么都没有，别灰心，至少还是个好人。

要完成上述打分，IF结合AND可以很轻松的搞定：

五、匹配：

匹配函数很多种，只有VLOOKUP最受宠。

VLOOKUP(匹配的参数，想要在哪个区域匹配，返回匹配区域的第多少列，是否精确查找）

函数构成很难懂，绝知此事要躬行：

现在有两个区域，区域1一个是包含产品ID，销量，销售额

区域2一个是供应商表，有ID，最早生成时间，供应商三个字段，还缺少销量，销售额两个字段：

我们发现两个区域的表有一个交集，他们有共同的产品ID，因此，我们可以通过ID作为纽带，将区域1里面的销量、销售额数据匹配到区域2中。

先做销量，我们在J2单元格输入如下公式：

展开解释，首先我们想要根据G2单元格的ID——SW0001进行匹配，第一个参数就是G2，

第二步，是想根据ID匹配获取表1区域的销量字段，所以在第二个参数位置输入A:C（选择A到C列所有数据），选定待匹配的数据列；

第三步，就是输入我们想要返回的列数（这里是销量），从匹配列（ID）数起，ID本身是第一列，销量是第二列，因此我们再第三个参数输入2；

最后，就是选择匹配方式，精确匹配还是近似匹配，绝大部分情况下我们默认精确匹配，因此输入FALSE或者0。

这样，根据ID我们就匹配到了对应ID的销量，销售额公式只需要改变返回的列数即可：

至此，表2的区域获取了销量、销售额相关数据：

注：这里两张表放在一起是为了方便演示，实际中表格一般是独立的，函数都支持跨表格选择对应参数。

六、计算统计相关：

6.1 COUNT/COUNTIF

COUNT(区域)函数，是统计目标区域有多少个数值类型的单元格，拿下面数据为例：

=COUNT(A:C)，就是统计A、B、C列所有单元格，有多少个数值类型的, 结果显而易见是20（销量和销售额都是数值类型）。

COUNT函数还有一群表兄弟:COUNTA(区域)是统计所有非空单元格个数，COUNTBLANK(区域)统计空白单元格个数，他们不太常用，就不展开赘述。

下面重点讲一下COUNTIF()函数。

他可以统计区域内，符合我们设置条件的单元格个数。

COUNTIF(区域，条件)由2个参数构成，第一个是要统计的区域，第二个是条件设置，比如我们想要统计ID为“SW0001”的产品出现了多少次，输入

即可，还有一个小技巧

上面数据中，"SW0001"在F2单元格，在COUNTIF函数第二个参数直接输入他所在的位置F2，等同于输入了“=SW0001"。

6.2 SUM和SUMIF

SUM函数很好懂，常用于对某一区域求和，SUM（区域）就是对该区域内所有数值求和。

SUMIF用法稍微复杂点，SUMIF(匹配列，条件，求和列）

直接上例子

左边是之前的数据，产品ID存在重复，我们想计算出每个ID的销量之和（补全右边销量区域），以F2为例，直接输入SUMIF(A:A,F2,B:B)，

第一个参数是被匹配区域的匹配列，简单来说，你想通过F列的ID，来匹配A列的ID（再获取A附近的销量列），那么A:A就是被匹配区域的匹配列；

第二个参数是条件，以F2为例，参数输入F2，等同于"=SW0001"，当A列产品ID等于"SW0001“时，条件生效。

第三个参数规定了求和列，是对销量进行汇总，自然就是B:B。

6.3 MAX/MIN/AVERAGE/MEDIAN/STD

最后这几个函数用法都很简单，只需要选定区域，就能计算对应的结果：

MAX 最大值，MIN最小值

AVERAGE 平均值，MEDIAN则是中位数

STD（2016版是STDEV.P）计算的是样本总体标准差。

以上，虽没有做到面面俱到，但已经涉及了大部分工作中常用的操作和公式。整理不易，觉得有用的点个好看哈~

不知道会不会有人耐着性子看到这个地方，能一口气看下来的毅力绝对能成大事！

且受我一拜！

文中数据下载链接： pan.baidu.com/s/1y5WsDd

密码：9vdq

◆  ◆  ◆   ◆  ◆

长按二维码关注我们

数据森麟公众号的交流群已经建立，许多小伙伴已经加入其中，感谢大家的支持。大家可以在群里 交流关于数据分析&数据挖掘的相关内容， 还没有加入的小伙伴可以扫描下方管理员二维码，进群前一定要关注公众号奥，关注后让管理员帮忙拉进群，期待大家的加入。

管理员二维码：