数据有效性自动添加一段字符串_用Excel进行数据分析(一)

文章总体图

一、数据分析中常用的Excel操作1. 基本操作1-1. 显示全部列名以及信息
1、自动换行

自动居中

2、设置列宽

设置列宽

1-2. 隐藏、取消隐藏1-2-1. 隐藏列

隐藏列

1-2-1. 取消隐藏列
取消隐藏列：

取消隐藏列

• “开始”选项卡
• 格式
• 取消隐藏

1-3. 删除重复值
删除重复值：根据唯一标识删除重复值

删除重复值

• “数据”选项卡
• 删除重复项
• 取消所有列名的全选
• 选择唯一标识数据的列名

1-4. 查看列值总数
点击列名，选中列，便可以看到列总数

城市列数

1-5. 定位列缺失的地方

定位数值缺失地方

步骤

• 选中列名
• ”开始选项卡“
• 查找和选择
• ”空“值按钮

1-6. 补齐缺失的数据
”CTRL+ENTER“键1-7. 复制-粘贴列

复制-粘贴列

1-8. 分列

分列功能

分列步骤：

• ”数据“选项卡
• 分列
• 选择”分隔符号“按钮
• 选择”逗号“选项按钮

1-9. 检查数据有效性
1、判断数据是否有效

检查最低薪水数据的有效性

检查有效性步骤：

• 选中列名
• “数据”选项卡
• “筛选功能
• 下拉滑动条查看所有的数据是否合理

2、查看无效数据产生的原因
在”文本筛选“中选中不正确的列值，查看原因

查看数据无效原因

1-10. 字符替换

替换单位

字符替换的步骤：

• 开始选项卡
• 查找和调换
• 填写替换前后的字符

1-11. 字符串形式数字转为数字类型
对列操作中，将字符串形式数字转为数字类型，步骤如下：

• 列复制、粘贴，形成新列；隐藏原有的列
• 对新列采用依据引号来分列操作

1-12. 数据透视表
原理：

• 数据分组
• 应用函数
• 组合结果

2. 函数2-1. 均值函数

均值函数

下拉

均值函数结果

2-2. 查找函数

1、find函数
1）用途
查找一个字符串在另一个字符串出现的起始位置
2）函数用法

find

3）函数使用示例

find函数使用示例

2、截断函数
1）函数用法
find函数常常配合使用的函数：left、right、mid

left

2）函数使用示例

截断函数使用示例

3、find函数和截断函数结合
1）find函数、left函数求得最新薪水

最低薪水

2）find函数、mid函数求得最高薪水

最高薪水

3. 日期数据3-1. 日期规范化
需要实现的效果

效果变化

分列

格式统一化

单元格格式

3-2. 每月发文涨粉量
创建透视表：行标签为日期，数值为“涨粉数”
单击“日期"列，设置分组。分组依据为”月份“

分组-按月汇总

最终效果：

按月汇总结果

3-3. 每周发文涨粉量

按周汇总数据

最终效果：

按周汇总结果

3-4. 每月最大涨粉量
操作：值字段设置

每月最大值的值字段设置

达到的效果

每月最大值效果

4. vlookup
Vlookup可实现两个功能：多表关联查询和数据分组
参数：

vlookup

4-1. 多表关联查询
已知成绩信息表，查询三好学生表中学生的班级

查询班级

tips:vlookup只能获取到一项数据。如果有错误的数据，可以设置辅助列精确查找。4-2. 数据分组

分组

tips:阈值为范围的最小值二、数据分析
本次数据分析以“数据分析师薪水表”作为分析数据。
数据分析的步骤

• 明确问题
• 理解数据
• 数据清洗
  • 选择子集
  • 列名重命名
  • 删除重复值
  • 缺失值处理
  • 一致化处理
  • 数据排序
  • 异常值处理
• 数据分析 OR 模型构建
• 可视化处理

1. 明确问题

• 哪些些城市找到数据分析师的机会比较大
• 数据分析师的薪水如何
• 根据工作经验，薪水是怎样变化的

2. 理解数据2-1. 数据集介绍
数据集是“数据分析师职位薪资表”，总数据量为5033项。2-2. 列名

• 城市
  职位所在的城市；北京、上海、深圳、杭州、成都等
• 公司全名
  公司全称，例如：1号店的全称是纽海信息技术(上海)有限公司
• 公司ID
  公司名可能重复，但是公司ID唯一标识一家公司，不可能重复。
• 公司简称
  例如：1号店
• 公司大小
  公司的人数
• 公司所在商区
  ['五里桥', '打浦桥', '制造局路']
• 职位所属
  产品、需求、项目、技术等
• 教育要求
  大专、本科、硕士
• 公司所属领域
  移动互联网、电子商务、O2O、金融
• 职位ID
  唯一标识职位，在数据集中作为数据项的唯一标识
• 职位福利
• 职位名称
• 数据分析师、数据运营、分析师、大数据开发工程师等
• 薪水
  7k~15k、7k以上等
• 工作年限要求
  应届生、1年以下、1 ~ 3年、3 ~ 5年、5 ~ 10 年、10年以上、不限

3. 数据清洗3-1. 选择子集
在工作区显示需要的列，不需要的列最好不要删除，而是采用“隐藏”的方式。
在“招聘网站数据”Excel表中，隐藏对本次分析无用的列。
1） 公司ID用于标识公司，在分析薪酬的任务中，不需要公司ID这一列的参与。

隐藏公司ID

2） “公司全名”和“公司简称”二者作用相同，不需要“公司全名”。

隐藏公司全名

3-2. 列名重命名
将列名修改为符合规范的列名。3-3. 删除重复值
根据“job ID”删除重复值。

删除重复值

3-4. 缺失值处理3-4-1. 判断列缺失数量
"城市"列的缺失数量=“职位ID”列总数-“城市”列总数，其中，“职位ID”为对职位的唯一标识项。
“职位ID”列数和”城市“列数分别为5033、5031，则”城市列“缺失数据为2个。

查看列项

城市列数

3-4-2. 定位值缺失位置

定位数值缺失位置

3-4-3. 对缺失值的处理方式

处理方式有四种：

• 人工补齐
• 删除缺失数据
• 用均值代替
• 用计算的统计值值代替

由于数据集中只有2个缺失项，采用”人工补齐“的方式。
在缺失数据的位置填入”上海“，按住”Enter+CTRL“即可实现自动补齐。3-5. 一致化处理
含义：数据是否有统一的标准或命名
数据不一致化的情况。3-5-1. 公司所属领域
第一个领域为”移动互联网“，第二个框住的领域有两个，且用逗号隔开。这就是数据不一致的情况。对于后续的数据分析有一定的影响。

数据不一致

处理步骤

1、复制、粘贴列

复制，粘贴列

2、隐藏原先的”公司所在领域“列

3、”公司所在领域“列根据”，“进行分列

分列功能

经过上述三步，对”公司所属领域“ 列数据的分列后的效果如下所示：

分列后的数据

3-5-2. 薪水
目的：由薪水列计算出最低薪水、最高薪水、均值薪水

薪水操作实现的效果

3-5-2-1. 获取最低薪水
使用find函数+left函数，获取最低薪水

最低薪水

最低薪水计算公式也可以是

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

3-5-2-2. 获取最高薪水
使用find函数+mid函数，获取最高薪水值

最高薪水计算

3-5-2-3. 自动填充数据
鼠标位置移动到单元的右下角，”双击“操作，即可自动填充单元列。3-5-2-4. 薪水有效性检查
1、最低薪水
1）检查最低薪水是否存在无效数据

检查最低薪水数据的有效性

2）分析无效原因

查看数据无效原因

得到下图

最低薪水无效原因

由上图可知，无效原因是：k的大写形式。
3）对策：将"k"的大写形式替换为小写形式

替换字符

4）二次查看最低薪水是否都有效

二次查看最低薪水有效性

由上图知：不存在未定义的数据。因此，最低薪水都是有效的

2、最高薪水
1）检查最高薪水是否都有效

查看最高薪水有效性

2）分析无效原因

最高薪水无效性原因

由上图可知，无效原因是很多HR没有填写最高薪水，而是用”以上“代替。
3）对策：使用最低薪水的值填充最高薪水

最高薪水无效性对策

4）二次检查最高薪水是否有效

二次查看薪水是否有效

由上图可知，最高薪水都有效3-5-2-5. 平均薪水计算
计算平均薪水时，需要用到的数据是最高薪水、最低薪水。但是最高薪水、最低薪水数据类型都是字符串类型，不能进行使用。

步骤：

• 将字符串形式的数字转为数字
• 计算最高薪水和最低薪水的均值作为平均薪水

3-5-2-6. 将字符串数字转为数字
1、复制-粘贴最低薪水列

• 右击”最高薪水“列，选择”复制“
• 复制”最低薪水“列
• ”粘贴最新薪水“列，粘贴选择”选择性粘贴“，粘贴类型为”数字类型“
• 隐藏初始的“最低薪水”列

复制-粘贴 最低薪水列

2、将粘贴后的新”最低薪水“列由”字符串“类型转为”数据“类型。
通过”分列“选项去除”文本“前面的引号

将字符串类型转为数字类型

3、处理后得到的“最低薪水”列

最低薪水列最终效果

判断处理成功的两个标志：

• 数据格式为“数值”类型
• 单元格左上角无绿色三角形

4、采用同1,2,3一样的步骤，处理最高薪水列3-5-2-7. 计算平均薪水

均值薪水计算

此时，便可以得到平均薪水的值。
考虑到平均薪水可能存在小数，需要调整平均薪水的数值格式。3-6. 数据排序
目的：对数据表按照“平均薪水”列的值按序排列
步骤：
1）在排序过程中，为了防止首行参与到排序中，对首行进行“隐藏”操作。
2）对表格按照“平均薪水”的数值大小进行排序

按“平均薪水”的序列对表格排序

3）取消首行的隐藏，对首行进行“冻结窗口”操作。可以实现方便查看数据。

薪水排序效果

3-7. 异常值处理
目的：采用数据透视表的技术功能选取属于数据分析师岗位的职位信息3-7-1. 先期设置
1、取消“自动筛选”

取消自动筛选

2、设置数据区域为表
1）判断行数
“职位ID”的数据数量，即为行数
2）设置表

设置表数据来源

3-7-2. 数据透视
1、创建数据透视表

创建数据透视表

2、数据透视表示例

• 行——分组依据
  值相同，分为一组；值不同，分为不同组
• 求和——应用
  默认：对每一行的数据统计出现的次数

数据透视表示例1

上图中，行标签是：职位名称；数值：对职位名称分组后的数据出现次数。
3、查看是否存在异常值
1）数据排序

查找异常值——排序

2）查看异常值

异常值

排序后的数据，出现次数最多的师数据分析师、数据产品经理。
大数据开发工程师和需求分析师是错误的数据，因此，需要将这些异常数据给处理掉
3）处理异常数据

• 判断职位名称是否是数据分析师职位：数据运营、数据分析、分析师等。

find函数查找是否包含指定数字

• 判断属于数据分析师，设置为是；不属于数据分析师，设置为否

if函数判断是否是数据分析师岗位

• 筛选出是数据分析师岗位的数据

筛选职位

4、将筛选后的数据复制到新建的Excel文件中4. 数据分析4-1. 分析 城市-工作年限-工作数量关系
1、设置行标签、列标签、数值

城市-年限数据透视表

行标签：城市——分组依据是城市名称
列标签：工作年限
数值：城市——统计按照城市分组后的岗位数量
2、对透视表继续排序

按数量排序

降序排序：按照技术项-城市进行排序
3、分析结果

排序后的结果

由上图分析结果知：
对数据分析师需求量前五的城市是：北京、上海、深圳、广州、杭州。其中，北京需求量最大
数据分析师工作年限1 ~ 3年、3 ~ 5年需求量处于Top2。其中，1~3年工作经验的数据分析师需求量最大。
4、换种数据表示方式
按列汇总的百分比

值显示方式

显示结果

值显示方式：列汇总的百分比

4-2. 分析城市-薪资关系
1、加载分析工具库

加载分析工具库

以平均薪水为例，查看统计数据。

平均薪水统计数据

2、制作数据透视表

薪水-城市

由图可知，薪水最高的城市Top5是深圳、背景、上海、杭州、苏州。4-3. 分析工作年限-薪资变化情况

工作经验-薪资

随着年限的增加，数据分析师的薪资逐渐增加。