Excel数据分析基础技能

基于Excel的数据清洗基础

• 1.排序

  • 排序和自定义排序
    
    
    自定义排序
    
    
    
    老板给了你一个排序表？让你找到这些房源的相关信息？
    还是自定义排序，但是我们需要先定义排序
    文件-选项-高级-常规-编辑自定义列表
    首先在EXCEL上面输入你要自定义的东西
    
    
    
    
    如上图所示，点击“向上的箭头”然后“导入”
    
    如下图所示，到时候就可以找到自定义序列
    
    以上可知，可以作为一个快速的筛选。对于排序的主要内容

• 2.查找

  • 定位、查找和替换
    CTRL+G：定位条件（快捷方式）
    先把这一列给选中，假如找到一个“空值”
    
    
    
    接下来就是把空置如何一次性赋予一个值
    
    直接在第一个“空值”里面输入“未知”。然后按住“CTRL+ENTER”
  • 单元格匹配的勾选代表是否完全匹配查找内容
    
    
    
    
    如下图所示：CTRL+A：全选
    
    想把处女座换成“别的字”，输入“别的字”，然后CTRL+ENTER
    可以根据格式去选择，如下图所示，根据颜色去选择。
    
    
    
    

• 3.筛选

  • 找出静安寺附近的房源（就是一个范围内的数据）
  • 需要左上角的经度纬度和右下角的经度纬度信息
    
    
    筛选带有合并单元格的数据
    正确步骤：
    将合并的格式刷格式到其他列-取消合并居中-F5或者CTRL+G定位空格-让空值等于上一个单元格-CTRL+ENTER全部输入-格式刷回来-再筛选
    
    
    高级筛选
    
    列表区域就全部筛选
    条件区域如下图所示：
    

常用函数汇总及应用场景

数据统计基础——数据透视

下面的图显示，是不能做数据分析的。必须拆分单元格

下面图的显示，才能做透视分析

常用函数/快捷键

快捷键
1.改变数字格式：ctrl+shift+1/2/3/4/5/…
2.快速插入/删除：ctrl shift + / ctrl -
3.快速自动调整列宽：鼠标移动到列首右侧，双击

4.绝对引号，相对引号：A1,&A1,A&1,&A&1，快捷键F4
选中下图的内容，按F4

NETWORKDAYS

WORKDAY

COUNTIF()，可以计算出有多少个双子座。

平均年龄是25岁，如下图所示------------------------这个好像是分割线？

如下图所示，1代表的就是正确返回1的值，0代表的就是错误。，可以编的，如果需要汉字，可以加上双引号。

单元格合并

使用&

单元格字符换提取

EXCEL中的字符串拆分比较麻烦，需要使用公式。
如下所示，我要将计算结果中LID单元格中的PAC值（行政区划代码 ）取出，这个时候我就要对A列进行字符串的截取。

首先我们切到公式选项卡，对B2单元格插入函数，选择MID函数


Text选择要截取数据的单元格
Start_num从这里要应用一下FIND函数，“PAC”——要从这里开始截取；A2——所在单元格；1——我也不太清楚咋回事，但是必须得填；+3——跳过“PAC”三个字符
Num_chars字符长度，在这里行政区划代码是6位，所以选择6
最后输入的公式如下所示，即可得出计算结果。

单元格匹配VLOOKUP

表格如下，有行政区划代码PAC，我想直接看出县名，sheet1中有县名NAME和行政区划 代码PAC的对应关系，那么，我如何把县名自动填充过来呢？


这个时候就需要VLOOKUP函数了，公式如下：
=VLOOKUP(E2,Sheet1!A2:B67,2,FALSE)


Lookup_value，在这里是行政区划代码，要根据这个来选择县名

Table_array，选择sheet1中的县名和行政区划代码对应区域

Col_index_num，sheet1中县名在第2列，我们要填充的就是这个第二列的值，所以在这里选2

Range_lookup在这里是FALSE，完全匹配选择

公式输入好了，下拉自动填充，可以看到，前几个没问题，后面为啥就不行了呢？

原因就是，公式下拉填充的过程中，Table_array范围也会跟随发生变化，这样就超过了sheet1中数据所在的范围，导致填充出错，这个时候就需要让这个变量为常数。

这个时候，我们需要在指定单元格位置时加入“$”符号，公式如下：=VLOOKUP(E2,Sheet1!A$2:B$67,2,FALSE)，如此即可完成填充。