数据清洗小技巧——Excel篇

目录

 

介绍

1.剔除重复值和缺失值

2.文本分列

3.从文本中提取数字(比如100分提取100)

4.删除单元格多余空格

5.以文本形式存储的数字的转换

6.批量替换单元格内容

7.英文文本大小写转换

8.替换单元格中的特殊字符

9.字符串的连接

10.vlookup函数

11.替换vlookup函数返回的#N/A值为空或其他

---

 

介绍

本篇介绍了数据清洗中常用的11个Excel小技巧，包括详细的操作步骤和案例展示，希望能够对大家有帮助。

后附完整思维导图

1.剔除重复值和缺失值

（1）删除重复值

数据-删除重复值——想要以哪个列的重复值为基准删除数据就要选择那个列

（2）缺失值剔除

A.方法1：选中列，开始——查找——定位条件——空值——右键删除——整行

有时这个方法会不起作用，就可以使用方法二

B.方法二：选中——开始——替换——查找内容输入空格，替换值随便输入一个例如a，全部替换，然后再查找所输入的值a，替换值不输入，全部替换——最后再利用查找——定位条件——空值——右键删除——整行

2.文本分列

把一列内容切开放到两列中

（1）方法1

分隔符号——张三(男)数据类型

插入空白列——选中所需列——数据——分列——分割符号——其他(输入要切割的符号比如左括号)——数据插入位置选择原列——右括号的删除可以用替换或者继续重复分隔符号_其他(输入右括号)的步骤

（2）方法2

固定宽度——202141020111学号类型分割

插入空白列——选中所需列——数据——分列——固定宽度——进行切分——数据位置选择插入的空白列

注意：在这要给大家提醒一下一个必须要注意的点，如图所示，列数据格式要选择文本，否则专业和年级会显示为：2，1，而不是01，01

3.从文本中提取数字(比如100分提取100)

（1）方法1——文本中只有一个数字时

在新建列写上一个想要提取的数字——数据——快速填充(快捷键Ctrl E)

（2）方法2——遇到文本中有好几处数字时

先将文本先用固定宽度的方法切开，然后再进行数字提取

（3）作用：可以为数据分析提供更多变量

4.删除单元格多余空格

（1）为什么要进行此操作

就比如同一个店名如果前面带了空格的话在用数据透视表做统计时他是不不会把这两个店当做同一个店的

（2）方法——利用函数trim

新建列——输入trim(选中所要修改单元格)——双击填充柄快速填充

5.以文本形式存储的数字的转换

（1）为什么要进行转换

因为如果数字是文本形式的话，做公式计算的时候会出现问题

（2）方法1

选中所需转换列——右下角黄色的小三角——转换为数字

（3）方法2

新建列——输入函数value(选中单元格)——双击快速填充

6.批量替换单元格内容

（1）应用场景1——替换数据只有一个时

比如两个数据记录的是同一物品，但是因为数据录入的时候不完全相等，然后会导致数据统计出现问题

方法1

选中数据范围——替换——全部替换

（2）应用场景2——替换数据多种多样时

做数据分析时比如某些店铺类别只有一家，需要把这些只有一家或者是数量较少的这些小类别合为一个大类

方法2

全选数据——数据——筛选——把需要保留的那些类别取消勾选——应用筛选——将第一个单元格改为其他————双击快速填充

7.英文文本大小写转换

（1）首字母大写proper

输入函数proper(选中单元格)——双击填充柄快速填充

（2）全部小写lower

输入函数lower(选中单元格)——双击填充柄快速填充

（3）全部大写upper

输入函数upper(选中单元格)——双击填充柄快速填充

8.替换单元格中的特殊字符

（1）substitute函数

也可以用来替换文本

substitute(选中单元格，要替换的内容，新的内容)

文本拆分textsplit(选中单元格，分割标准)

9.字符串的连接

（1）字符少——&&

（2）字符多——concat函数

A.直接连接

concat(选中单元格范围)

B.想要在中间加上字符

concat(范围1，"字1"，范围2，"字2")

公式：

10.vlookup函数

连接两个表

VLOOKUP(用来连接的数据，数据范围，要返回的数据在区域中的列号，返回近似匹配或精确匹配-指示1True或0False)

数据范围中用来连接的数据必须处于首列

11.替换vlookup函数返回的#N/A值为空或其他

iferror/ifna(vlookup函数写上，"")

思维导图：