EXCEL数据分析之数据分析的步骤(三)
数据清洗
7、异常值处理
通过if函数和count函数,对职位进行判断,来处理异常值
a)if函数,是根据一种特定的判断条件进行判断,从而返回TURE和FALSE逻辑类型值的函数;
公式为:=IF(条件判断, 结果为真返回值, 结果为假返回值)
第一参数是条件判断,比如说A1="20"或21>37这种,结果返回TRUE或FALSE。如果判断返回的是TRUE,那么IF函数返回第二参数的值,否则则返回第三参数的值。
例如:=IF(1>2,"判断真","判断假")
其中判断1大于2结果是假,也就是FALSE,所以单元格返回第三参数文本判断假。
注:在if函数中,当条件为0以外的实数时,表示条件为真,返回第二参数值;当条件为0时,表示条件为假,返回第三参数值。
b)count函数,用于Excel中对给定数据集或者单元格中数据的个数进行计数;
公式为:=COUNT(单元格引用或区域)
count函数只能对数字数据进行统计,对于空单元格、逻辑值或者文本数据将被忽略,因此可以利用该函数来判断特定单元格区域中,是否包含空单元格。
例如,计算如下图所示的数字出现的个数,得到的计数结果为9;
通过count函数计算单元格区域出现数值的个数
通过上述两个函数的组合判断,来得到相应的判断值,从而对异常值进行筛选:在线excel
1)先通过find函数,在目标列中,找到目标数据的值,比如数据分析,分析师,数据运营等;
公式为FIND({"数据运营","数据分析","分析师"},目标列);
Tip:用大括号{}包括起来的一组数据叫做数组数据,而其中的每一个数据叫做这个数组数据的元素。例如:数组{甲、乙、丙},其中甲、乙、丙都是这个数组的一个元素。
(此处关于数组数据的信息了解的不是特别清楚,后续需专项了解数组数据的运算和性质)
2)通过count函数,对find函数得到的结果进行计算,若find函数找到对应的目标值,则count函数得到除0以外的实数;若find函数未找到对应的目标值,则count函数得到0;
公式为COUNT(FIND("数据运营","数据分析","分析师",目标列));
3)通过if函数,对count函数得到的值进行判断,若count函数得到非0的实数,则if函数得到是,若count函数得到0,则if函数得到否;
公式为IF(COUNT(FIND("数据运营","数据分析","分析师",目标列)),"是","否")
如下图所示:
通过多个函数组合,来判断目标列的职位名称,是否为数据分析的岗位
数据分析或构建模型
a)数据透视表
数据透视表是数据分析的重要功能。数据透视表的原理就是将数据按照某种特定的规则进行分组、对每组的数据进行函数运算,再将上一步的计算结果重新组合汇总。
数据透视表的原理
上图可以简单概括为,基于数据分组,利用应用函数设定统计目标,得到组合结果,即基于A,统计B,得到数据透视表C。
例如通过招聘网站数据文档,基于职位名称,统计职位名称出现的次数,得到一张新的数据透视表;
选择目标列职位名称→选择插入选项卡→选择数据透视表功能→选择新工作表,在新工作表中进行统计→基于A,将职位名称拖入到行中→统计B,将职位名称的计数项拖入值中,即可得到统计结果,如下图:
基于职位名称,统计职位名称出现的次数
通过选择职位名称的筛选器,对数据进行排序,将排序依据设置为通过计数项排序,即可对统计的数量进行排序,来了解那个职位名称出现的次数最多,如下图:
职位名称出现次数进行降序排列
切换数据透视表中,数据统计的显示方式,可以通过在数据统计列当中的任意单元格,右键选择值显示方式,选择列汇总的百分比,即可将数据统计由统计数量切换为各统计数量所占的百分比例,如下图:
切换值显示方式
b)Excel加载项当中的数据分析工具
Mac电脑版本的office,添加Excel加载项→选择工具菜单→选择Excel…加载项→勾选分析工具库→点击确定即可安装Excel的数据分析工具,如下图:在线excel
Excel分析工具库加载项
使用上述分析工具对一列数据做描述统计分析,选择数据选项卡→选择数据分析功能→在弹窗中选择描述统计→在描述统计弹窗中,选择输入的区域及对应参数设置→点击确定输出对数据列的描述性统计,如下图:
描述统计分析工具得到对应数据列的描述统计分析结果
c)多表数据关联工具VLOOKUP函数
当同时分析多张表的时候,表和表之间需要进行数据关联时,就需要引入多表关联查询工具VLOOKUP函数,它可以具体条件找到目标表格当中的数据。
操作步骤,在需要关联的单元格中插入VLOOKUP函数,如下图:
VLOOKUP函数公式生成弹窗
VLOOKUP函数可以概括为这样几个参数:
找什么:指基于A表格的目标字段,去B表格中查找目标字段的属性值;
在哪找:查找目标字段属性值的目标表格B;注:在目标表格B当中,设置查找范围时,查找的值必须位于第一列,否则就会报错,例如:在A表中的目标字段是索隆,那么在B表中设定查找范围时,索隆所在的列必须位于第一列;
第几列:目标表格B中,目标字段属性值所在的列;注:这里的所在列,是相对于选定查找范围的第X列,而不是相对整张表格的;
准确找/近似找:在线excel准确找即为0,是从第一行开始,向后逐个查找,一找到第一个匹配的属性值之后,就停止查询,返回找到的第一个值;近似找即为1,是找到所有匹配的属性值之后,返回最后一个值。
Tip1:通过上述方式,VLOOKUP函数,不论是准确找还是近似找,都只能返回一个值。如果有重复值的话,这个时候就需要新建一个辅助列,将目标属性与另外一个属性进行拼接,公式为:=单元格A&单元格B,来实现重复值的过滤和精确查找。
d)通过VLOOKUP函数对数据进行分组
通过VLOOKUP函数来实现对数据进行分组,例如下图,如何将月消费水平划分为高、中、低三个区间:
通过VLOOKUP将不同月份的月消费水平进行高、中、低分组
1)先建立一个数据表格,进行分组定义,分别定义高、中、低的分组范围,并将每一组范围的最小值作为阈值,如下图:
定义分组范围
2)在消费分组列的单元格中,输入VLOOKUP函数,并设置相应的参数:
VLOOKUP函数参数设置
找什么:这里的找什么,可以理解为需要被分组的数据,即上表中B列当中的数据;
在哪找:寻找的范围即为分组定义范围的表格,分别有阈值、分组属性以及范围;分组定义范围的表格需要被绝对引用,也就是说消费分组列的数据在进行函数运算时,所引用的表格,不随着运算表格的位置移动而移动。
第几列:这里的第几列,则为定义范围表格中,作为分组属性的列,例如高、中、低的属性;
准确找/近似找:当使用VLOOKUP函数对属于进行分组的时候,需要使用模糊的方式进行查找,所以输入1。
3)通过VLOOKUP函数运算以及应用于同列,得到如下图所示的分组结果:
对月消费水品进行分组
Tip:数据引用的三种方式
Excel当中,数据的引用分为三种方式:
相对引用:A1
相对引用
相对引用中,当把公式复制到其他单元格中时,每一个单元格运算时所引用的行号与列号,都会随着单元格位置的改变而改变;
绝对引用:$A$1
绝对引用
绝对引用中,当把公式复制到其他单元格中时,绝对引用的单元格的位置,不会随着单元格运算位置的改变而改变;
混合引用:$A1,A$1
混合引用中,当把公式复制到其他单元格中时,混合引用的单元格的位置,会根据$"所在的位置,单独约束行的位置,或者是列的位置,来实现复制公式的单元格,在单一维度上,不会随着单元格运算位置的改变而改变。
最后,教大家一个简单拖拽创建表单和生成报表的方法。使用简道云表单在线创建“云表格”,通过简单拖拽,即可制作出一个个柱形图、折线图、饼图等图表,1分钟实现数据可视化分析!非常方便~