excel实战小测第四

【项目背景】 本项目为某招聘网站部分招聘信息，要求对“数据分析师”岗位进行招聘需求分析，通过对城市、行业、学历要求、薪资待遇等不同方向进行相关性分析，加深对数据分析行业的了解。 结合企业真实招聘信息，可以帮助有意转向数据分析岗位的学生调整学习的方向，同时也为简历准备上做出针对性参考。

【数据操作】

step1、数据清洗

1）重复数据

一般情况下，一个数据有且仅有一个唯一ID，因此针对数据“职位ID”列删除重复项。

选定想要删除的列【职位ID】，菜单栏选择【数据】--【删除重复项】，弹框选项中选择【扩展选定区域】--【删除重复项】，选择指定列【岗位ID】--【删除重复项】。

2）缺失数据（无需操作）

清理重复值之后，还需要考虑对空值的处理，如果某一字段数据缺失>50%，则可以考虑删除，如果只是个别的缺失值，可以考虑删除，也可以进行填充（如分类变量数据可以使用人工手动补全，连续变量数据可以使用平均值进行替代）。

数据表中，选择想要查看的列，页面左下角查看【计数】，判断数据是否确实，其中可以看到【公司福利】、【工作地点】等数据都有缺失，但是不影响实际分析，因此这里无需操作修改。

Excel数据处理（缺失值/重复值/异常值/拆分） - 知乎 (zhihu.com)

3）一致化处理

1.薪资待遇

数据中，【薪资】多表示一个范围，不能直接用于数据分析，根据取薪资下限和薪资上限（拆分）（若为单边范围的薪资数据，如“6K以上”,则做上下限薪资相同处理），然后取薪资平均值用于后续数据分析。

【方式一：分列取平均值】

选中【薪资】列，菜单栏选择【数据】-【分列】，选择文件类型为【分隔符号】。

选择使用符号【-】进行切分，

数据填充

数据分割之后，可以发现数据中【15k以上】此类数据，没有分割，可以对【空白】数据进行【筛选】，对空白数据进行填充【=N148】（以实际内容为准），同时下拉，填充所有空白数据。

填充完毕之后，可以看到数据中有文本信息【k】，无法直接进行数学计算，使用【查找替换】将其删除。快捷键【Ctrl+F】,选择【替换】，查找内容为【k】,替换值为空即可。

同时还要考虑带有文本的数据，如【15k以上】，上步骤中已经把【k】去除，还需要同样方式把【以上】【以下】等内容进行替换删除。

数据全部替换之后，计算平均薪资。右侧添加一列【平均薪资】。使用公式或者函数计算品均值。

【方式二：使用函数取平均值】

* len：用于计算文本字符串的字符个数；len(text)

* Left：返回从文本字符串的左侧开始到指定个数的字符，可用于字符串截取前几个字符；left(hello,2)，字符数量默认为1。

* right：返回文本字符串右侧开始到指定个数的字符，可用于字符串截取后几个字段；

* mid：返回文本字符串中指定位置开始的指定数目的字符。可用于中间截取。

* find：用于查找指定字符在字符串中的第一次出现的位置；find(“l”,”hello”)

根据薪资计算【薪资下限】，可以发现在第一个【k】之前的内容为下限，因此可以使用【find】函数，找出【k】所在的位置，使用【left】函数进行左侧字符串截取。

=LEFT(text,[num_chars])

=LEFT(P2,FIND("k",P2)-1)

结果中错误处，可以看到这里是【K】，使用替换功能，将其替换成【k】即可。

计算【薪资上限】，可以发现上限数值，在【-】和【k】之间,可以使用【mid】函数截取两者之间的数值。

=MID(text,start_num,num_chars)

=MID(P2,FIND("-",P2)+1,LEN(P2)-FIND("-",P2)-1)

通过筛选可以看到，【薪资上限】中也有不能现实的数值，查看发现，对于特殊数值，无法使用函数，这里直接等于【薪资下限】即可，下拉修改所有内容。

计算【平均薪资】，使用函数【average】。注意，抽取出来的文本型数字不能参与数值计算，需要将文本型数字转化成数值型数字。

=AVERAGE(Q2*1,R2*1)

4）异常值处理

【岗位名称】中可以发现有很多不用的名称，包括数据分析师、产品、测试等，因此需要对数据进行异常值处理。

本次我们重点分析【数据分析】，因此对数据进行筛选，使用关键字【数据分析】、【分析师】、【数据运营】对岗位进行判断。可以看出关键词【数据分析】可以甄别出大部分数据分析岗位，满足我们的分析需求。

* find：用于查找指定字符在字符串中的第一次出现的位置；find(“l”,”hello”)

* count：计算包含数字的单元格以及参数列表中数字的个数。

* if：判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另外一个值。

对结果进行筛选，数据为“1”的则为我们要用到的【数据分析】岗位信息。

(1) 分析城市对于岗位数量的需求，结果（格式为：城市 岗位数）为 。

(2) 分析行业对于岗位的需求，结果（格式：企业领域 岗位数 示例：移动互联网,电子商务 115，这里企业领域不做切分操作）为 。

(3) 分析公司规模对于岗位的需求，结果（格式：公司规模 岗位数）为 。

(4) 分析城市对薪资（取平均值）的影响，结果（格式：城市 平均薪资，注意单位为k，下同）为 。

(5) 分析公司规模对于薪资（取平均值）的影响，结果（格式： 公司规模 平均薪资）为 。

(6) 分析学历对于薪资（取平均值）的影响，结果（格式：学历要求 平均薪资）为 。

(7) 分析工作经验对于薪资、岗位的影响，结果（格式：工作年限 平均薪资 岗位数）为 。

(8) 试分析给出数据分析岗位需求量较大的公司top6，结果（格式：公司简称 岗位数）为 。

(9) 现有一名本科、工作两年的择业人员，想要在北京转数据分析岗位，对岗位需求量分析给出建议投递简历的公司top5（岗位需求量降序），结果（格式：公司简称 岗位数）为 。

第二题 csv

网站用户注册分析

(15/15分)

数据集：

数据集.csv

考核条件如下：

(1) 读取数据，查看数据信息简要，"用户名"非空数据个数结果为

(3/3)分

(2) 进行数据缺失值查看，“注册日期”缺失值个数结果为

(3/3)分

(3) 编写程序/root/python/demo3.py,要求程序运行结果为每月用户注册数,结果为 。（结果格式：注册日期 注册数量 ，参考步骤说明）

(3/3)分

(4) 编写程序/root/python/demo4.py,要求以月作为行索引，年份作为列索引，对比不同年份中每月的注册用户数,结果为 。（参考步骤说明）

(6/6)分

数据透视表

数据透视表，一篇就够了 - 知乎 (zhihu.com)

计算类别数量（例题）