历史版本2 :字段清洗 返回文档
编辑时间: 内容长度:图片数:目录数: 修改原因:

目录:

1. 概述编辑

1.1 应用场景

数据清洗，就是按照一定的规则清理脏数据，以确保后续分析结果的准确性。

例如，一些订单信息表中，对于销售额、利润等字段会包含货币符号「¥」，想要将字符清除掉进行一些其他的运算。

4.png

1.2 实现思路

  • 先用 LEFT 函数截取第一位返回值，与（ 符号进行比较。

  • 如果字符中的第一个字符为（，那么就需要使用 SUBSTITUTE 函数替换字段中的 （、）、￥内容，并使用 CONCATENATE 函数拼接「-」负号字符；

  • 如果字符中的第一个字符不是（，那么就使用 SUBSTITUTE 函数替换字段中的 ￥内容；

  • 最后使用 TONUMBER 函数将结果转换成数值类型。

2. 示例编辑

示例数据：订单利润数据.xlsx

2.1 创建分析表

下载示例数据并上传至数跨境，将数据保存在项目中，点击「创建分析表」，如下图所示：

11.png

2.2 处理数据

1）点击「+>新增字段>公式」，如下图所示：

12.png

2）新增字段命名为「数据清洗」，输入公式：TONUMBER(if(left(利润,1)="（",CONCATENATE("-",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(利润,"（",""),"）",""),"￥","")),SUBSTITUTE(利润,"￥","")))，点击「确定」，如下图所示：

13.png

公式说明：

公式说明
SUBSTITUTE(利润,"（","")将「利润」字段中的（ 去掉
SUBSTITUTE(SUBSTITUTE(利润,"（",""),"）","")将「利润」字段中的 ）去掉
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(利润,"（",""),"）",""),"￥","")将「利润」字段中的 ￥ 去掉
SUBSTITUTE(利润,"￥","")将「利润」字段中的 ￥ 去掉
if(left(利润,1)="（",CONCATENATE("-",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(利润,"（",""),"）",""),"￥","")),SUBSTITUTE(利润,"￥",""))

如果「利润」字段的第一个字符为（ ，那么就将（ ）以及￥去掉，并和 - 拼接；

如果「利润」字段的第一个字符不是（，那么就直接去掉 ￥

TONUMBER( )将结果从文本类型转换成数值类型

2.3 实现效果

点击「确定」后可以看到处理好的数据，如下图所示：

4.png

3. 更多内容编辑

上述是对数字进行清洗，还有一些其他的场景。比如：

需求清洗前的数据清洗后的数据如何实现？
首字母转大写，其他小写

BEIJING TECHNOLOGY CO.,LTD

beijing technology co.,ltd

Beijing Technology Co.,Ltd英文大小写转化
去除本文中的（）、【】、[]

[1] xxxxxx

【1】xxxxxx

xxxxxx

以【1】为例：

  • 可以使用字段拆列根据】先拆分成两列，再删除不需要的那一列；

  • 可以组合使用 RIGHT 和 LENB 函数，比如：RIGHT(字段,LENB(字段)-3)，获取】右边的字符。

统一日期格式YYYY-MM-DD2019/11/25
2019年11月25日
20191125		2019-11-25常用日期公式