在Excel中清洗数据的好用方法

0.1 1. 项目概览

• 项目背景：某公司开展员工满意度调查，回收问卷后得到原始数据，需要对原始数据进行清洗.
• 数据规模：标注数据量1000条数据+6个维度、数据来源企业脱敏数据。
• 项目周期：独立完成，耗时4h。

0.1.1 2. 技术路径

• 数据处理：经过分析,该原始数据存在以下问题：
  1. 部分问卷的 “年龄” 字段为空值；
  2. “入职日期” 字段格式不一致（如 “2023-01”“2023 年 1 月”“2023/1”）；
  3. 存在重复提交的问卷（同一员工提交了 2 次）；
• 处理方法：
  1. 插入数据透视表,以"部门"为行,以"年龄为值"统计每个部门平均年龄; 用Excel的筛选功能,筛选出"年龄"为空的行,再筛选出“部门”为“销售部”的行,统一填入销售部的平均年龄。如此可以快速填充缺失值。
  2. 通过 “拆分格式→文本转换→汇总统一→转日期类型” 的流程，将混乱的多格式日期，逐步规范为统一的日期值： 拆分格式：为每种日期格式建列，明确处理对象； 文本转换：用 IF+MID 把不同格式转成统一文本（如 YYYY-MM-DD 文本）； 汇总统一：用 IF 合并结果，确保每行一个统一文本； 转日期：用 DATE+MID 将文本转为真正的日期类型（可参与日期计算）。 （注：步骤 2 中 IF 返回的 0 需注意过滤，避免影响后续转换；函数操作在 Excel、Google Sheets 等工具中通用，语法略有差异时需调整。）
  3. 用Excel的“删除重复项”功能，将重复提交的问卷删除。

0.1.2 以下是统一多格式日期数据的逐点解析：

1️⃣. 对每一种日期格式添加一个列 目的：先梳理数据中存在的日期格式（如 YYYY-MM-DD、MM/DD/YYYY、DD.MM.YYYY 等），为每种格式单独建列。 作用：方便后续针对不同格式的字符串，分别提取年、月、日信息。

2️⃣. 使用 IF 结合 MID，转化为目标格式的常规文本（false 忽略会返回 0） 逻辑：

MID(text, start_num, num_chars)：从字符串中按位置截取字符（比如从 MM/DD/YYYY 中截取月份 MID(A1, 1, 2)）。
IF(condition, value_if_true, value_if_false)：判断日期格式，针对性截取年、月、日，拼接成 统一文本格式（如 YYYY-MM-DD 的文本，而非日期类型）。

注意：若 IF 的条件不满足（value_if_false），默认返回 0，需后续处理无效值。

3️⃣. 添加一个汇总列，用 IF 进行汇总 目的：将步骤 2 中不同格式列的结果，统一合并到一列。 逻辑：用 IF 判断哪一列有有效文本（非空、非 0），将其值提取到汇总列。例如：

=IF(格式列1<>"", 格式列1, IF(格式列2<>"", 格式列2, ...))

确保每行只有 一个统一的文本日期（如 2023-01-01 形式的文本）。

4️⃣. 再添加一个日期列，用 DATE() 结合 MID() 将常规文本转化为日期 逻辑：

DATE(year, month, day)：将数字型的年、月、日转化为 日期类型（如 DATE(2023, 1, 1) 返回 2023/1/1 日期）。

先用 MID 从汇总文本中提取年、月、日的数字（如从 2023-01-01 中提取 MID(汇总列, 1, 4) 作为年），再传给 DATE 函数，最终生成标准日期。