先看数据结构,再写公式
函数写不对,多数时候不是语法错,而是数据本身不规整:合并单元格、前后空格、文本型数字、一行两行混排的表头。写公式前先做三个检查:
- 有没有合并单元格:合并会让引用错位,先拆开。
- 有没有多余空格与不可见字符:用 TRIM / CLEAN 或先去重再处理。
- 数字是不是文本格式:左上角有绿三角就是文本,参与计算会出错。
顺序:先清洗、再计算、最后展示。反过来做,公式会写得很复杂,还很容易被一次数据更新全部打回。
六个高频函数:写法与典型用途
| 函数 | 典型用途 | 写法要点 | 常见坑 |
|---|---|---|---|
| XLOOKUP | 按编号查名称、查价格 | =XLOOKUP(查找值,查找列,返回列,"未找到") | 查找列与返回列长度必须一致 |
| SUMIFS | 按部门/月份汇总金额 | =SUMIFS(求和列,条件列1,条件1,条件列2,条件2) | 条件区域与求和区域行数要一致 |
| COUNTIFS | 统计满足多条件的行数 | =COUNTIFS(条件列1,条件1,条件列2,条件2) | 文本条件要用引号,通配符 * 需注意 |
| TEXTJOIN | 把多行备注合并成一句 | =TEXTJOIN(";",TRUE,区域) | 第二参数 TRUE 才会忽略空值 |
| IFERROR | 包住可能出错的公式 | =IFERROR(原公式,"") | 不要用它掩盖真正的数据错误 |
| TEXT | 日期、金额格式化 | =TEXT(日期,"yyyy-mm") | 结果是文本,不能再参与日期计算 |
XLOOKUP 相比 VLOOKUP 的价值有两点:可以向左查、默认精确匹配。如果你的表格还在用 VLOOKUP + 列序号,迁移到 XLOOKUP 能少掉一大类"插了一列就全错"的事故。
数据清洗四步:去重、分列、统一格式、校验
一份从系统导出的表,通常要经过四步才能用:
- 去重:先按唯一键(订单号、手机号)去重,再谈统计。重复数据会让所有汇总虚高。
- 分列:把"北京市朝阳区XX路"拆成省市区,或把"姓名(部门)"拆成两列,后续才能分组。
- 统一格式:日期统一成 yyyy-mm-dd、金额去掉货币符号、手机号统一 11 位,避免"同一个人算成两个"。
- 校验:用 COUNTIFS 检查关键字段是否有空值、重复值、异常值,再交给下游。
这一步用在线 CSV 工具做会更快:去重、排序、合并、拆分都是点选操作,不用记公式;处理完再导回表格软件做公式汇总,两者分工最省时间。
常见报错对照表
| 报错 | 原因 | 处理 |
|---|---|---|
| #N/A | 查找值不存在 | 用 IFERROR 兜底,并核对是否有多余空格 |
| #VALUE! | 文本参与了数值计算 | 先转数字(VALUE 或分列),再计算 |
| #REF! | 引用的单元格被删除 | 改用整列引用或命名区域 |
| #DIV/0! | 分母为 0 或空 | 用 IF 判断分母再除 |
| 结果全是 0 | 数字是文本格式 | 分列成常规格式,或整列 *1 转换 |
把常用公式沉淀成模板
每月都做的表,别每次重写公式:把清洗步骤、公式、格式做成一个模板文件,下次只替换原始数据区域。更进一步,把"原始数据 / 清洗后 / 汇总"分成三个工作表,任何人接手都能看懂流程。