专栏名称: 爱数据原统计网

中国统计网（www.itongji.cn），国内最大的数据分析门户网站。提供数据分析行业资讯，统计百科知识、数据分析、商业智能(BI)、数据挖掘技术，Excel、SPSS、SAS、R等数据分析软件等在线学习平台。

实操：Excel分析5000+数据，哪个城市薪水高！

爱数据原统计网 · 公众号 · BI · 2019-09-25 21:15

正文

- 点击上方 “中国统计网” 订阅我吧!-

数据分析的步骤：

1.提出问题

2.理解数据

3.数据清洗

4.构建模型

5.数据可视化

提出问题：

a.去哪个城市工作？

b.发展前途如何?

c.薪水高吗？

Tip: excel单元格中数据左对齐表示字符串类数据，右对齐表示数值类数据。

理解数据

文件中包含了14个字段，

城市、公司全名、公司ID、公司简称、公司大小、公司所在商区、职位所属、教育要求、公司所属领域、职位ID、职位福利、职位名称、薪水、工作年限要求。

从数据中我们可以粗略的了解到这是一份描述了不同地区不同公司的招聘信息，其中包含了公司的基本信息和招聘的要求以及岗位的待遇福利。

数据清洗

了解Excel的一些基本操作

1. 如何隐藏行？ (在我们获取到的原始数据中，可能会存在一些无意义的字段或者与某些字段具有相同意义的字段，此时我们可以将这些字段（一列）隐藏起来，可以更加方便我们对剩余数据进行有效的分析和处理。)

方法：单击需要隐藏的行的最上面的字母单元格（A B C …），右击选择隐藏即可。显示隐藏列的方法：全选所有数据（Ctrl+A），选择Excel选项卡中的开始，点击单元格功能块中的格式按钮，选择隐藏和取消隐藏按钮，选择取消隐藏列。

2. 如何删除重复值？

在数据中，有一列名为职位ID的一列，我们对此列进行删除重复值处理。

选择数据选项卡、数据工具功能区中的删除重复值、取消全选、勾选职位ID、点击确定

结果提示为： 发现了1843个重复值，已将其删除； 保留了5031个唯一值。

3. 如何判断是否存在缺失值？

点击职位ID单元格上的J，然后会在右下角看到计数情况

再点击城市单元格上的A，查看计数情况

此时我们可以看出，城市列中的数据有两个缺省值。

在进行判断之前，确保你进行对比的列中 至少有一列是完整的、不存在缺省值，以此位参照进行比对。

4. 查找补全缺失值

选中要查找的列

点击确认，然后会发现此列的空白单元格会被选中

此时填入你想要填上的值，然后按下Ctrl+Enter组合键，会发现其余空白单元格都会填入这个值。

5. 如何将多值数据进行拆分?

当我们的数据中存在如下图所示的时候，我们可能有将其拆分的需求。

此列中的值由一个或多个分类标签组成，我们如何将其进行拆分然后统一化格式？

至于选择哪一列作为实际使用的数据，根据自己的需求而定。

6. 使用函数进行计算

当我们通过分列方法对薪水进行拆分后得到以下结果（k字符我通过替换功能替换掉了）

然后进行求平均值计算

然后双击，Excel会自动将此单元格的操作应用至整列中，生成的结果如下。

7. 给列增加筛选功能

我们可以通过对字段进行排序，来查看数据的排列情况。

当我们对平均薪水进行排序降序排列时，得到以下结果。

我们可以看到高薪水的工作的招聘要求以及福利待遇

8. 形成数据透视表

对字段进行排序

可以看出数据分析师的职位最多

构建模型

首先我们为Excel添加数据分析功能

然后我们会发现一个多出来的数据分析功能

我们先简单的应用一下

得出的结果如下

从结果中我们可以获取我们想要的信息，还有其他更多的分析功能需要我们去主动学习。

VLOOKUP

下面我们来认识一下VLOOKUP函数

这个函数非常重要！！！

百度百科解释：

VLOOKUP函数是Excel中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，在工作中都有广泛应用，例如可以用来核对数据，多个表格之间快速导入数据等函数功能。功能是按列查找，最终返回该列所需查询列序所对应的值；与之对应的HLOOKUP是按行查找的。

反正看完这个解释，我是不明白这个函数到底能干啥，还是通过一些实际的案例来学习这个函数吧。

首先打开学生信息.xlsx和三好学生.xlsx文件

接着我们提出一个需求，如何通过学生信息表中的信息来补全三好学生表中的班级信息，要保证辅助列对应的字段是相等的。

经过这一系列操作后，我们大概能猜到VLOOKUP这个函数能完成什么样的功能。

VLOOKUP函数能够 通过某个数据区域的数据与我们要查找的字段的值进行匹配 ，然后在我们指定的某列中进行值的选择，我姑且认为这是一个将某字段标签化的功能函数。

在上述的例子中，我们利用VLOOKUP函数将各个姓名对应的班级找了出来，接下来再通过一个例子来认识一下VLOOKUP函数其他作用。

打开分组.xlsx文件

此时的需求是通过右边给出的月消费分组的数据情况，我们需要通过这个来对左边的月消费水平进行消费分组。

仔细的同学应该可以看出，上面两个案例最大的不同应该是在第四个值（Range_lookup）的选择上，当这个值为1或者True时，是进行的精确匹配，只有当两个值完全相等时，才会配对；当这个值为0或者FALSE时，是进行的模糊匹配，匹配规则是比较两个值的近似度，选择最相似的，但是在数值比较中会保证结果值一定小于目标值。

在本例子中，B5的值为789，在E列中位于500-1000之间，分别与500相差289，与1000相差211，虽然与1000之间的差值更小，但是结果任然是中消费，说明 函数判定789与500更近似，因为要保证结果值（500）一定要小于目标值（1000）。

引用

相对引用：A1

绝对引用：$A$1

混合引用：$A1 / A$1

在使用VLOOKUP函数时，我们进行了一次按F4的操作，这个操作有什么用处呢？

当我们在使用函数时选中一个单元格，假设选择A1单元格，此时我们按下F4，会发现选中的A1单元格会变成$A$1，这个代表着绝对引用这个单元格，通俗的来说就是锁死这个单元格，让它不会随着我们其他的额外操作而导致位置的改变。

可能有的小伙伴会疑惑，为什么选中的单元格会发生改变呢？

不知道大家有没有注意过，当我们通过双击右下角来将函数表达式应用到整列中时，其中部分值会发生改变。

第一个参数会随着位置的变化跟着变化，因为我们是将函数作用与整个列，所以B2中的B列不会改变，但是