查看: 360|回复: 1

Excel常用公式-数据清洗类(转载)

[复制链接]
论坛徽章:
1
python徽章
日期:2019-02-28 15:00:58
发表于 2019-2-16 22:30 | 显示全部楼层 |阅读模式

数据库 SQL Excel ETL 数据仓库 字符

数据清洗是为了解决数据质量问题,“脏数据”一般有以下三种类型:

1、残缺数据

这一类数据主要是一些应该有的信息缺失,如供应商的名称、分公司的名称、客户的区域信息缺失、业务系统中主表与明细表不能匹配等。对于这一类数据过滤出来,按缺失的内容分别写入不同Excel文件向客户提交,要求在规定的时间内补全。补全后才写入数据仓库。

2、错误数据

这一类错误产生的原因是业务系统不够健全,在接收输入后没有进行判断直接写入后台数据库造成的,比如数值数据输成全角数字字符、字符串数据后面有一个回车操作、日期格式不正确、日期越界等。这一类数据也要分类,对于类似于全角字符、数据前后有不可见字符的问题,只能通过写SQL语句的方式找出来,然后要求客户在业务系统修正之后抽取。日期格式不正确的或者是日期越界的这一类错误会导致ETL运行失败,这一类错误需要去业务系统数据库用SQL的方式挑出来,交给业务主管部门要求限期修正,修正之后再抽取。

3、重复数据

对于这一类数据——特别是维表中会出现这种情况——将重复数据记录的所有字段导出来,让客户确认并整理。

数据清洗是一个反复的过程,不可能在几天内完成,只有不断的发现问题,解决问题。对于是否过滤,是否修正一般要求客户确认,对于过滤掉的数据,写入Excel文件或者将过滤数据写入数据表,在ETL开发的初期可以每天向业务单位发送过滤数据的邮件,促使他们尽快地修正错误,同时也可以做为将来验证数据的依据。数据清洗需要注意的是不要将有用的数据过滤掉,对于每个过滤规则认真进行验证,并要用户确认。

1、Trim
功能:去除单元格两端的空格。

语法:=TRIM(text)

text指要移除空格的文本或者单元格名称

说明:此方法只能删除字符串首尾的空格,而不能删除字符串中间的空格!因为英文单词之间的空格是必须的,Excel不会去除这种空格!

2、Clean
有时文字值包含起始空格、 尾随或多个嵌入的空格字符 (Unicode 字符集值 32 和 160) 或非打印字符 (Unicode 字符集值 0 到 31、 127、 129、 141、 143、 144 和 157)。这些字符进行排序、 筛选或搜索时,有时会导致意外的结果。

功能:删除文本中所有不能打印的字符。

语法:=CLEAN(text)

CLEAN 函数语法具有以下参数:

text,必需。要从中删除非打印字符的任何工作表信息。
3、Concatenate
功能:连接单元格内的内容

语法:= CONCATENATE(text1, [text2], ...)

text1为必需,要联接的第一个项目。项目可以是文本值、数字或单元格引用。
[text2]表示text2为选填的意思,下同。
说明:concatenate能够连接的参数最多只有30个,而&则没有限制。

4、Mid
功能:提取字符串中间的字符串

语法:= MID(text, start_num, num_chars)

text必填。包含要提取字符的文本字符串。
Start_num必填。文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1,以此类推。
Num_chars必填。指定希望 MID 从文本中返回字符的个数。
5、Left
如果继续想从出生年月里提取年份,则需要用到left函数。

功能:提取字符串左边的字符串

语法:= LEFT(text, [num_chars])

Text,必需。 包含要提取的字符的文本字符串。
num_chars,可选。 指定要由 LEFT 提取的字符的数量。
Num_chars 必须大于或等于零。
如果 num_chars 大于文本长度,则 LEFT 返回全部文本。
如果省略 num_chars,则假定其值为 1。
6、right
与left类似,如果想从出生年月里提取月日,则需要用到right函数。

功能:提取字符串右边的字符串

语法:=RIGHT(text,[num_chars])

text 必需。包含要提取字符的文本字符串。
num_chars可选。指定希望RIGHT提取的字符数。
7、repalce
功能:替换字符串中的连续几个字符或者某个字符

语法:= REPLACE(old_text, start_num, num_chars, new_text)

Old_text必填。要替换其部分字符的文本。
Start_num必填。old_text 中要替换为 new_text 的字符位置。
Num_chars必填。old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。
New_text必填。将替换 old_text 中字符的文本。
8、substitute
也可以利用substitute实现。

功能:替换字符串中的连续几个字符或者某个字符

语法:=SUBSTITUTE(text, old_text, new_text, [instance_num])

text必填。需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
Old_text必填。需要替换的文本。
New_text必填。用于替换 old_text 的文本。
Instance_num虽然日程表需要数据点之间常量的步骤,预测.指定要用 new_text 替换 old_text 的事件。如果指定了 instance_num,则只有满足要求的 old_text 被替换。否则,文本中出现的所有 old_text 都会更改为 new_text。


回复

使用道具 举报

论坛徽章:
29
Tensorflow徽章
日期:2017-08-03 18:16:28OpenAI课程徽章
日期:2018-11-09 11:07:08知识图谱徽章
日期:2018-11-22 15:35:26人工智能徽章
日期:2018-12-13 15:50:34python徽章
日期:2018-12-27 15:34:57Git徽章
日期:2019-01-03 15:42:50股票徽章
日期:2019-01-10 14:04:57股票徽章
日期:2019-01-10 14:08:37股票徽章
日期:2019-01-24 16:03:16Git徽章
日期:2019-04-28 13:58:08股票徽章
日期:2019-05-09 14:30:32python徽章
日期:2019-05-16 15:53:00
发表于 2019-2-17 16:57 | 显示全部楼层
为了作业而回帖:非常感谢,谢谢分享,有学到东西!
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册 新浪微博登陆

本版积分规则

 

GMT+8, 2019-9-16 03:55 , Processed in 0.089692 second(s), 31 queries .