15个Excel公式,搞定90%日常数据处理,第7个我几乎每次都用
在日常数据处理工作中,Excel依然是核心工具。通过掌握特定的公式,可以高效解决数据清洗、提取、匹配、统计及判断等常见问题。以下15个公式按功能场景分类,旨在提供一份实用的操作指南,帮助提升数据处理效率。
01清洗类公式
- TRIM:去除多余空格
语法:=TRIM(A2)
场景:清除系统导出数据中肉眼不可见的空格,解决VLOOKUP无法匹配的问题。 - CLEAN:去除不可见字符
语法:=CLEAN(A2)
场景:清除换行符、制表符等非打印字符。通常与TRIM套用(=TRIM(CLEAN(A2)))作为数据清洗第一步。 - LEN:校验数据长度
语法:=LEN(A2)
场景:校验身份证号(18位)、手机号(11位)等定长数据,配合条件格式快速标记异常行。 - SUBSTITUTE:替换指定内容
语法:=SUBSTITUTE(A2," ","")
场景:处理全角空格或特殊分隔符,解决TRIM无法清除的特定字符问题。
02提取类公式
- LEFT / RIGHT:从两端截取
语法:=LEFT(A2,6) 或 =RIGHT(A2,4)
场景:截取身份证前6位(地区码)或手机号后4位,常用于数据脱敏。 - MID:从中间截取
语法:=MID(A2,7,8)
场景:从身份证号中提取出生日期(第7位开始取8位)。
03匹配类公式
- VLOOKUP:老牌万能匹配
语法:=VLOOKUP(F2,A:D,4,0)
场景:跨表数据查询的首选,适用于旧版Office环境,操作简单通用。 - INDEX+MATCH:灵活高效匹配
语法:=INDEX(D:D,MATCH(F2,A:A,0))
场景:大数据量环境下处理速度更快,支持向左查找,不受列顺序限制。 - XLOOKUP:新版全能匹配
语法:=XLOOKUP(F2,A:A,D:D,"未找到")
场景:支持向左查找、内置容错,无需计数列号,需Excel 2021及以上版本支持。
04统计类公式
- COUNTIF:条件计数
语法:=COUNTIF(A:A,"张三")
场景:统计特定文本出现的次数,用于查重或数据质量排查。 - SUMIFS:多条件求和
语法:=SUMIFS(D:D,B:B,"计算机学院",C:C,"在职")
场景:基于多个条件(如学院与在职状态)汇总数值,如工资总额。 - COUNTIFS:多条件计数
语法:=COUNTIFS(B:B,"计算机学院",C:C,"在职")
场景:与SUMIFS逻辑一致,用于统计满足多重条件的记录数量。
05判断类公式
- IF:条件判断
语法:=IF(D2>5000,"重点关注","正常")
场景:根据数值阈值自动打标签,进行数据分类。 - IFERROR:错误兜底
语法:=IFERROR(VLOOKUP(F2,A:D,4,0),"无记录")
场景:将查找失败产生的错误代码(如#N/A)替换为指定文本,保持报表整洁。
06新函数
- UNIQUE:一键去重
语法:=UNIQUE(A2:A100)
场景:快速提取数据源中的不重复值,生成部门列表或供应商名单,需Excel 2021及以上版本。
这15个公式涵盖了日常数据处理的绝大多数场景。优先掌握VLOOKUP、IF、TRIM、COUNTIF四个基础公式,即可解决约80%的问题;其余公式可根据具体需求按需调用。
当前文章无评论,是时候发表评论了