Excel 数据清洗与转换 #

┌─────────────────────────────────────────────────────────────┐
│                    数据清洗流程                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. 识别问题：空值、重复、格式不一致                         │
│  2. 清理数据：删除、填充、修正                               │
│  3. 转换格式：文本、数字、日期                               │
│  4. 验证数据：检查、确认                                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

检查空值：
=ISBLANK(A1)
=COUNTBLANK(A:A)

填充空值：
=IF(A1="", "默认值", A1)
=IFERROR(A1, "默认值")

删除空行：
筛选 → 空白 → 删除

检查重复：
=COUNTIF(A:A, A1)>1

标记重复：
=IF(COUNTIF($A$1:A1, A1)>1, "重复", "")

删除重复：
数据 → 删除重复值

提取唯一值（Excel 365）：
=UNIQUE(A:A)

大小写统一：
=UPPER(A1)    转大写
=LOWER(A1)    转小写
=PROPER(A1)   首字母大写

空格处理：
=TRIM(A1)     清除多余空格

不可见字符：
=CLEAN(A1)    清除不可打印字符

从身份证提取出生日期：
=TEXT(MID(A1, 7, 8), "0000-00-00")

从邮箱提取用户名：
=LEFT(A1, FIND("@", A1)-1)

从电话提取区号：
=LEFT(A1, FIND("-", A1)-1)

从地址提取省市：
=LEFT(A1, FIND("省", A1))

按分隔符拆分：

原始数据：张三,男,25岁

姓名：=LEFT(A1, FIND(",", A1)-1)  → "张三"
性别：=MID(A1, FIND(",", A1)+1, FIND(",", A1, FIND(",", A1)+1)-FIND(",", A1)-1)
年龄：=RIGHT(A1, LEN(A1)-FIND(",", A1, FIND(",", A1)+1))

合并多个单元格：
=A1 & B1 & C1
=CONCAT(A1, B1, C1)
=TEXTJOIN("-", TRUE, A1:C1)

=VALUE(A1)
=A1*1
=A1+0
=--A1

批量转换：
数据 → 分列 → 完成

=A1&""
=TEXT(A1, "0")
=TEXT(A1, "00000")  补零

文本转日期：
=DATEVALUE(A1)
=TEXT(A1, "yyyy-mm-dd")

数字转日期：
=TEXT(A1, "yyyy-mm-dd")

日期格式化：
=TEXT(A1, "yyyy年mm月dd日")

是否为数字：=ISNUMBER(A1)
是否为文本：=ISTEXT(A1)
是否为日期：=ISNUMBER(A1)  （日期存储为数字）
是否为空：=ISBLANK(A1)

是否在范围内：
=AND(A1>=最小值, A1<=最大值)

是否为有效日期：
=AND(ISNUMBER(A1), A1>=DATE(1900,1,1), A1<=DATE(2100,12,31))

是否为有效手机号：
=AND(LEN(A1)=11, LEFT(A1,1)="1", ISNUMBER(VALUE(A1)))

是否为邮箱格式：
=ISNUMBER(FIND("@", A1))*ISNUMBER(FIND(".", A1, FIND("@", A1)))

是否为身份证格式：
=AND(LEN(A1)=18, ISNUMBER(VALUE(LEFT(A1,17))))

原始数据：
    A              B          C
1   姓名           电话       邮箱
2   "  张三  "     13812345678  ZHANG@EXAMPLE.COM
3   李四           139-1234-5678  li_si@test.com

清洗后：
    A              B          C
1   姓名           电话       邮箱
2   张三           13812345678  zhang@example.com
3   李四           13912345678  li_si@test.com

清洗公式：
姓名：=PROPER(TRIM(A2))
电话：=SUBSTITUTE(B2, "-", "")
邮箱：=LOWER(TRIM(C2))

原始数据：
    A
1   北京市朝阳区xxx路xxx号
2   上海浦东新区xxx路xxx号
3   广州天河区xxx路xxx号

提取省份：
=IF(LEFT(A1, 2)="北京", "北京市",
   IF(LEFT(A1, 2)="上海", "上海市",
      IF(LEFT(A1, 2)="广州", "广东省", "")))

提取城市：
=IF(LEFT(A1, 2)="北京", "北京",
   IF(LEFT(A1, 2)="上海", "上海",
      IF(LEFT(A1, 2)="广州", "广州", "")))

原始数据：
    A
1   张三|男|25|北京
2   李四|女|30|上海

分列：
    A          B          C          D
1   姓名       性别       年龄       城市
2   张三       男         25         北京
3   李四       女         30         上海

公式：
姓名：=LEFT(A1, FIND("|", A1)-1)
性别：=MID(A1, FIND("|", A1)+1, FIND("|", A1, FIND("|", A1)+1)-FIND("|", A1)-1)
年龄：=MID(A1, FIND("|", A1, FIND("|", A1)+1)+1, 
            FIND("|", A1, FIND("|", A1, FIND("|", A1)+1)+1)-
            FIND("|", A1, FIND("|", A1)+1)-1)
城市：=RIGHT(A1, LEN(A1)-FIND("|", A1, FIND("|", A1, FIND("|", A1)+1)+1))

原始数据：
    A
1   13812345678
2   13987654321

格式化为：
    A
1   138-1234-5678
2   139-8765-4321

公式：
=LEFT(A1, 3) & "-" & MID(A1, 4, 4) & "-" & RIGHT(A1, 4)

原始数据：
    A          B
1   产品       销量
2   产品A      100
3   产品B      200
4   产品A      150

合并相同产品：
    A          B
1   产品       总销量
2   产品A      250
3   产品B      200

公式（Excel 365）：
=UNIQUE(A2:A4)  产品列表
=SUMIF(A:A, D2, B:B)  各产品总销量