数月前帮客户做了一次Access数据库清洗数据的课程，花了三天把课程中主要的一些要点整理出来，避免自己忘记，也给大家参考一下，请多提建议。

数据清洗的方法以及使用Access及VBA进行数据清洗的流程及自动化数据清洗的设计

1. 需要的技能

1.1. 数据导入 导出

1.1.1. Excel

1.1.2. Access

链接表

多个Access数据库

Sqlserver

Access 2G

1.1.3. Sqlserver

1.1.4. sysbase(可选)

1.1.5. db2(可选)

1.1.6. text(可选)

1.1.7. outlook(可选)

1.2. 查询的使用

1.3. 常用函数

1.4. Sql知识及常用Sql语句

1.5. 宏与VBA的相关知识

1.6. 窗体与ADO 高阶知识

2. 数据来源

2.1. 手工录入的数据收集

2.2. 系统导出的数据

2.3. 采集第三方的数据

2.4. 混合数据

3. 数据清洗定义

3.1. 数据清洗是指发现并纠正数据文件中可识别的错误的最后一道程序

3.1.1. 检查数据一致性(consistency check)

根据每个变量的合理取值范围和相互关系，检查合乎要求?，发现超出正常范围、逻辑上不合理或者相互矛盾的数据

例子

处理方法

使用软件或程序根据定义的取值范围，自动识别

3.1.2. 处理无效值 补全缺失值

原因

由于调查、编码和录入误差

处理方法

估算(estimation)

整例删除(casewise deletion)

变量删除(variable deletion)

成对删除(pairwise deletion)

3.1.3.

3.2. 数据清洗一般是由计算机及软件完成。

3.3. Data cleaning 把“脏”的数据“洗掉”

3.3.1. 按照一定的规则把“脏数据”“洗掉”，就是数据清洗

3.4. 对数据进行重新审查和校验的过程，目的在于删除重复信息、纠正存在的错误，并提供数据一致性。

4. 为什么要数据清洗

4.1. 因为数据仓库中的数据是面向某一主题的数据的集合

4.2. 来自多个业务系统中抽取而来

4.3. 且可能包含历史数据

4.4. 避免不了有错误数据，甚至前后冲突的数据

4.4.1. 脏数据

5. 数据处理流程及原理

5.1. 数据导入及预处理

5.1.1. 处理工具的选择

5.1.2. 相关环境搭建

5.1.3. 导入数据

5.2. 脏数据

5.2.1. 拼写错误

5.2.2. 命名习惯

5.2.3. 不合法值

5.2.4. 空值

5.3. 预定义的数据清理策略 规则

5.3.1. 数理统计技术 数据挖掘技术

5.3.2. 异常检测 重复处理

数据清理是将数据库精简以除去重复记录，并使剩余部分转换成标准可接收格式的过程

数据清理标准模型是将数据输入到数据清理处理器，通过一系列步骤“ 清理”数据，然后以期望的格式输出清理过的数据

数据清理从数据的准确性、完整性、一致性、惟一性、适时性、有效性几个方面来处理数据的丢失值、越界值、不一致代码、重复数据等问题

5.4. 获得满足质量要求的数据并输出

6. 数据清洗的作用

6.1. 数据清洗， 是整个数据分析过程中不可缺少的一个环节，其结果质量直接关系到模型效果和最终结论

6.2. 数据清洗通常会占据分析过程的50%—80%的时间

7. 数据清洗要点

7.1. 顺序很重要

7.1.1. 如先去掉姓名中的空格再去重

7.2. 反复迭代过程

7.2.1. 不是一次性 或短期几天完成的

7.2.2. 发现问题 用户反馈 解决问题

7.2.3. 修正后 会发现新的问题 或触发新的问题

7.2.4. 不要将有用的数据过滤掉，每个过滤规则要认真验证，并要用户确认

7.3. 难以通用统一，但可以分类

7.3.1. 针对具体应用，因而难以归纳统一的方法和步骤

7.3.2. 根据数据不同可以给出相应的数据清理方法

8. 数据错误检测

8.1. 统计分析的方法识别可能的错误值或异常值

8.1.1. 如偏差分析、识别不遵守分布或回归方程的值

8.2. 简单规则库检测判别数值是否合法

8.2.1. 常识性规则

8.2.2. 业务特定规则等

8.3. 或使用不同属性间的约束、外部的数据来检测和清理数据

8.3.1. 多数据源集成的数据可能有语义冲突，可定义完整性约束用于检测不一致性，也可通过分析数据发现联系

8.4. 专业的工具发现规律和联系

8.5. 自己行业或特定业务 自定义的检测规则与流程

9. 数据清洗的方法

9.1. 无效

9.1.1. 错误的数据

全角数字

多了回车换行符 或隐藏符号

日期格式不正确

日期越界

年龄超限

9.1.2. 修改格式、内容错误

时间、日期、数值、全半角等显示格式不一致

同一内容多种叫法

示例

使用相用的工具 或数据库 或通用规则表 或自定义规则表替换

行业标准数据

示例

使用领域特有的知识库清洗

内容中有不该存在的字符

身份证包含中文 空格 符号

姓名中出现非中文（中国名字）

多余的首尾或中间空格

日期出现 \ 或其它符号

处理中间有, ()之类的数据

复杂的使用正则表达式

内容与该字段应有内容格式不符

手机上填了姓名

身份证处填了手机

性别填了姓名

修正不合理值

如年龄大于150岁

收入大于5000W

箱形图

建立各种标准字段的范围值

其它不符合规则的数据

修正不一致值或矛盾值

如A处与B处数据不一致

9.1.3. SQL批量修正

9.2. 重复

9.2.1. 重复的数据

判断记录间的属性值是否相等来检测记录是否相等

9.2.2. 相等的记录合并为一条记录（即合并/清除)。合并/清除是消重的基本方法

使用查询向导-查找重复项查询向导

SQL将重复数据导出来，让用户确认

delete from [表名] where id not in (select min(id) from [重复向导查询结果] group by [带重复记录的字段名称])

9.2.3. 代码删除去重

9.3. 不全

9.3.1. 不完整的数据 或残缺的数据

无供应商名称

客户区域信息缺失

主表与明细表不能匹配

9.3.2. 处理方法

不重要且无法补齐

直接删除

先做备份

重要

重新补全缺失数据

以业务知识或经验推测填充缺失值

以不同指标的计算结果填充缺失值

10. 自动化清洗系统

10.1. 窗体与控件

10.1.1. 创建窗体

10.1.2. 窗体的分类

10.1.3. 窗体主要属性 方法 事件

10.1.4. 控件

10.1.5. 控件的属性 方法 事件

10.1.6. 需要掌握

recordsosurce

docmd.setwarning false

requery

current事件

10.2. 查询或Sql

10.2.1. 建立部门标准表

Delete from 部门标准表

INSERT INTO 部门标准表 ( 部门 ) SELECT 部门 FROM 员工表待清洗 GROUP BY 部门;

10.2.2. 建立职务标准表

Delete from 职务标准表

INSERT INTO 职务标准表 ( 职务 ) SELECT 职务 FROM 员工表待清洗 GROUP BY 职务;

10.2.3. 判断所属地 是否 中山 是否本地居民

Select * from 员工表待清洗 where (所属地='中山' and 是否本地居民=0) or (所属地'中山' and 是否本地居民0)

10.2.4. 判断入职日期 不合理数据

Select * from 员工表待清洗 where 入职日期 < #2000/01/01# or 入职日期 >#2021/05/01#

10.2.5. 身份证 位数不正确的

Select * from 员工表待清洗 where len(身份证号)18

10.2.6. 编号 中含有中文 的

Select * from 员工表待清洗 where gf_IncludeChinese(编号)

10.2.7. 简历内容 去掉html 标签

Me.frm员工表待清洗.Form.RecordSource = "Select * from 员工表待清洗 where gf_IncludeChinese(编号)"

10.2.8. 创建姓名重复查询向导

10.3. 创建窗体界面

10.3.1. 创建部门标准表窗体

生成部门标准表代码

10.3.2. 创建职务标准表窗体

生成职务标准表代码

10.3.3. 创建姓名重复窗体

10.4. 批量清洗

10.4.1. 批量清洗部门

UPDATE 员工表待清洗 INNER JOIN 部门标准表 ON 员工表待清洗.部门 = 部门标准表.部门 SET 员工表待清洗.部门 = [部门标准表].[部门标准];

10.4.2. 批量清洗职务

10.4.3. 批量清洗简历

10.4.4. 批量清洗重复姓名

11. 多数据源对比

12. 修改逻辑错误

13. 常用快捷键

13.1. Ctr+Enter

13.1.1. 设计视图

13.2. Shift+F2

13.2.1. 弹出缩放窗口

13.3. Ctr+;

13.3.1. 当前日期

13.4. ctr+:

13.4.1. 当前时间

笔记本就是Ctr+Shift+;

13.5. Ctr+'

13.5.1. 复制上一条记录相同字段内容

笔记本就是Ctr+Shift+“

13.6. F4 或 Alt+向下箭头

13.6.1. 展开组合框

13.7. Ctr+F Ctr+H

13.7.1. 查找替换

14. Access相关知识

14.1. 字符串 连接 （文本 数字 日期不同)

14.2. 宏 及 VBA +窗体调用多个查询 或sql语句（连查询也不需要） 批量执行

14.2.1. 设置docmd.SetWarning 语句

14.3. 变量 常量 运算符 表达式

14.3.1. 字符常量

双引号括起来

"abcd'”

“AB""C""D”

空值 空字符串 空格

“”

“ ”

null

14.4. sql语句 聚合函数

14.4.1. SQL语法

SELECT FName,出生日期, 录入日期

FROM tbl员工表

WHERE (FName="赵六" or FName="张三") and (出生日期 between #1990/01/01# and #1990/12/31#) and 地址 not like "*北京*"

ORDER BY FName,出生日期 DESC;

Update tbl员工表

set 录入日期 =#2021/04/23# ,地址="北京市"

WHERE (FName="赵六" or FName="张三") and (出生日期 between #1990/01/01# and #1990/12/31#) and 地址 not like "*北京*"

Delete from tbl员工表

WHERE (FName="赵六" or FName="张三") and (出生日期 between #1990/01/01# and #1990/12/31#) and 地址 not like "*北京*"

SELECT Count(员工表.姓名) AS 人数, 员工表.部门, 员工表.性别 FROM 员工表 GROUP BY 员工表.部门, 员工表.性别;

From

单个表

多个表

单个查询

多个查询

表与查询混合

14.5. 查找不匹配项查询向导 查重向导

14.6. 导进来的数据先加自动编码 ID

14.7. 使用数据表窗体，查错的数据与原始数据(Rawdata)进行关联

码字实在不易，花了好几天的时间整理并进行了代码尝试

觉得好请帮忙点个赞及关注我 @小辣椒高效Office