171、运用EXCEL进行数据分析

Excel是进行数据分析用得最多、最基本的工具，下面内容是运用Excel2013对一份数据分析师招聘网站的数据进行分析。
Excel数据分析的步骤：
1、提出问题（明确数据分析目的）
2、理解数据（理解数据列名的意义）
3、数据清洗（统一格式内容）
4、构建模型（思考用什么样的表现形式把数据呈现出来）
5、数据可视化（把数据转化成图）
一、提出问题
首先要明确这次数据分析的目的是什么？也就是为了解决什么问题？
提出问题：
1、在哪些城市找到数据分师工作的机会比较大？
2、数据分师的薪水如何？
3、根据工作经验的不同，薪酬是怎样变化的？
二、理解数据

接下来要理解表格中的各个字段（列名）表示什么意思： 1.列名.png

城市：用于比较不同城市对数据分析师的需求如何
职位所属：分析以后的工作岗位
职位ID：表示职位的唯一表示，也就是每一行数据的唯一标识------用于去掉重复ID
薪水：比较不同城市、和所属领域的薪水区别
工作年限：从时间轴上对比薪资涨幅
三、数据清洗
数据清洗即数据预处理，目的是去掉无效、重复数据，以取得符合我们要求的数据。

数据清洗的基本步骤： 2.数据清洗步骤.jpg

1.选择子集

只选择对数据分析有意义的字段，无意义的字段选择隐藏，即隐藏不需要分析的列（尽量不删，保证数据的完整性）。这里隐藏公司ID和公司全名，保留职位ID和公司简称。 3.隐藏列.png

2.列名重命名
将不合适的列名更改为我们容易理解的形式。
3.删除重复值

对重复数据进行删除，这里我们对【职场ID】列进行删除重复值处理： 4.删除重复项.png

4.缺失值处理

先检查数据是否存在缺失值，先查看完整数据列的计数： 5.选择数据完整列.png 再选择其他列查看是否缺失数据： 6.查看缺失情况.png

可以看出【城市】这一列缺失2条数据。
一般对缺失值的处理有4种方法，根据情况灵活使用：
①　通过人工手动补全（缺失值较少，并且可以根据其他信息确定该值）
②　删除缺失的数据（无法判断该位置填写何值，或者删除的数据对分析无大的影响
③　用平均值代替缺失值
④　用统计模型计算出的值去代替缺失值

这里对【城市】这一列的处理方法： 7.定位条件1.png 8.定位条件2.png 由于缺失数据较少，这里选用人工手动补全，使用Ctrl+Eneter快捷键，在不连续的单元格中同时输入同一个数据或公式时很好用： 9.自动填充.png

5.一致化处理
（1）对数据进行统一的命名和处理。比如数据中的公司的所属领域是“企业服务，数据服务”，对该列数据进行拆分。

步骤：选中要进行拆分的列-选项卡-数据---分列--分隔符号---下一步---勾选 其他，并入输入“，”且 勾选 连续分隔符视为单个处理（注意，将输入法 切到 中文状态 ，因为 中文的逗号和英文的不是一种字符）---下一步--完成，就会看到 在右边生了一列出来，如图： 10.分列1.png 10.分列2.png 10.分列3.png 10.分列4.png

注意事项：
①　将数据先复制到最后一列（分列功能会覆盖右边单元格），隐藏原始列----进行分列；
②　对拆分出来的一页 添加列名：公司所属领域2，原来列的列名改为公司所属领域1 ，否则数据透视时会出现警告：”数据表字段名无效。
（2）我们将薪水处理成【最低薪水】、【最高薪水】、【平均薪水】，用于存放清洗后的薪水数据。这里有两个方法可以实现：

一种是使用上面提到的分列功能，将【-】作为分隔符号，然后用【查找替换】功能替换掉【k】，再使用函数AVERAGE求出平均薪水。 11.第一种方法.png

第二种是利用函数实现：

FIND函数的意思是查找一个字符串在另一个字符串中出现的起始位置，用FIND 函数查找分隔符【k】或者【-】。 12.find函数.jpg

FIND函数用来对原始数据中某个字符串进行定位，以确定其位置。FIND函数进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。

LEFT函数（从左开始截取字符串），MID函数（从中间截取字符串），Len函数（计算字符串长度），函数具体用法可以在百度上查。 13.left_right_mid函数.jpg 利用LEFT函数截取最低薪水： 14.使用left函数截取最低薪水.jpg 同理，使用MID函数截取最高薪水： 15.截取最高薪水1.jpg 15.截取最高薪水2.jpg 15.截取最高薪水3.jpg

在做的时候会最高薪水这一列出现错误值，使用筛选的功能查找错误值：

报错原因是在薪水这一列中数值有问题，其一是k的大小写问题，出现有大写的K，可使用查找替换修改大写为小写。 16.查找错误值1.jpg 16.查找错误值2.jpg 其二是出现薪资范围是多少k以上： 17.薪水范围报错.png

具体操作方法如下：选中最高薪水该列，定位条件为错误。然后delete删除错误值。再在单元格内输入=ctrl+方向键←，让其等于最低薪水；最后Ctrl+enter，批量操作。
在操作的时候会出现数字显示为文本格式，可以选中该列×1，使其转换成数字格式。
当所有操作都完成之后，使用average函数对其求均值。
6.数据排序

我们对【平均薪水】这一列进行降序排序： 18.降序排序.png 18.降序排序2.png 18.降序排序3.png

7.异常值处理

使用数据透视表处理表格： 19.数据透视表1.png 19.数据透视表2.png 19.数据透视表3.png 此时发现【职位名称】中有职位不属于数据分析： 20.职位异常.png

这些异常值需要去掉，应返回原表重新筛查。
步骤：
①　在原表【职位名称】列后插入新的列命名为“数据分析职位名称”；
②　在下方空白单元格插入函数=IF(COUNT(FIND({"数据运营","数据分析","分析师"},L2)),"是","否")；
③　双击单元格右下方进行自动填充；

21.插入函数筛选职位.png
④　使用筛选功能，选择“是”，过滤掉异常值。 22.过滤异常值.png

四、构建模型及数据可视化
1、在哪些城市找到数据分师工作的机会比较大？
以城市为行标签，工作年限要求为列标签，数据透视分析不同城市对不同年限的数据分析师的需求情况。

步骤：全选--选项卡，数据透视图----将数据透视表字段中的【城市】拖入到行，将【工作年限要求】拖入到列，再将【城市】拖入到值中： 23.透视分析数据分析师需求情况1.png 再点选表格中左上角行标签---其他排序选项---降序排序--计数项：城市 23.透视分析数据分析师需求情况2.png 最后将数值按列汇总的百分比显示数据： 23.透视分析数据分析师需求情况3—按列汇总.png 将值按行汇总百分比显示数据： 23.透视分析数据分析师需求情况4—按行汇总.png
23.透视分析数据分析师需求情况5—可视化结果.png

结论：从数据透视表可以看出，在北京数据分析的岗位最多，往后是上海、深圳、杭州、广州；按工作年限要求来看，3-5年的需求量最大，其次是1-3年，这说明数据分析对年轻人需求将更多。
2、数据分师的薪水如何？

首先，安装EXCEL 的分析工具库功能：选项卡，文件--选项---加载项---管理，选择 EXCEL 加载项---转到---勾选 分析工具库---确定。 24.描述统计.png 操作步骤：选项卡》数据 在右边出现有：数据分析 点击---对话框中，勾选 描述统计----确定----输入区域，框选中平均薪水列---分组方式--逐列----勾选 标志位于第一行(表明第一行是列名不包括在计算机里面)-----点选中： 新工作表组---输入： 薪水描述统计(工作表名称)-----勾选 归总统计、平均数置信度95%、第K值大致5、第K值小值5----确定，结果如下图： 24.描述统计2.png 25.描述统计结果.png 以城市为行标签，平均薪水为列标签，数据透视表分析城市与平均薪水的关系： 26.透视分析城市—平均薪水.png 26.透视分析城市—平均薪水2.png 27.透视分析城市—平均薪水结果.png

结论：从上面数据透视结果可以看出，深圳的数据分析师平均薪水最高，其次是北京，上海，杭州。
3、根据工作经验的不同，薪酬是怎样变化的？

以工作年限要求行标签，平均薪水为列标签，数据透视分析工作年限与平均薪水的关系： 28.透视分析工作年限—平均薪水结果1.png 28.透视分析工作年限—平均薪水结果2.png 28.透视分析工作年限—平均薪水结果3.png 29.透视结果.png

结论：从上面数据透视结果可以看出，随着工作经验的增长，数据分析师的薪酬也在不断增加。

综合上面三个数据透视结果，我们可以得到以下分析结论：
1）数据分析这一岗位，有大量的工作机会集中在北上广深以及新一线城市，如果将来去这些城市找工作，可以提高求职成功的条件概率。
2）从待遇上看，数据分析师留在深圳发展是个不错的选择，其次是北京、上海。
3）数据分析是个年轻的职业方向，大量的工作经验需求集中在1-5年。
对于数据分析师来说，5年似乎是个瓶颈期，如果在5年之内没有提升自己的能力，大概以后的竞争压力会比较大。
4）随着经验的提升，数据分析师的薪酬也在不断提高，10年以上工作经验的人，能获得相当丰厚的薪酬。

上述操作练习的数据：<u>https://pan.baidu.com/s/12Y3SCvUoFwGdlMcJjerYgw</u>