先看数据结构,再写公式

函数写不对,多数时候不是语法错,而是数据本身不规整:合并单元格、前后空格、文本型数字、一行两行混排的表头。写公式前先做三个检查:

  • 有没有合并单元格:合并会让引用错位,先拆开。
  • 有没有多余空格与不可见字符:用 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 能少掉一大类"插了一列就全错"的事故。

数据清洗四步:去重、分列、统一格式、校验

一份从系统导出的表,通常要经过四步才能用:

  1. 去重:先按唯一键(订单号、手机号)去重,再谈统计。重复数据会让所有汇总虚高。
  2. 分列:把"北京市朝阳区XX路"拆成省市区,或把"姓名(部门)"拆成两列,后续才能分组。
  3. 统一格式:日期统一成 yyyy-mm-dd、金额去掉货币符号、手机号统一 11 位,避免"同一个人算成两个"。
  4. 校验:用 COUNTIFS 检查关键字段是否有空值、重复值、异常值,再交给下游。

这一步用在线 CSV 工具做会更快:去重、排序、合并、拆分都是点选操作,不用记公式;处理完再导回表格软件做公式汇总,两者分工最省时间。

常见报错对照表

报错原因处理
#N/A查找值不存在用 IFERROR 兜底,并核对是否有多余空格
#VALUE!文本参与了数值计算先转数字(VALUE 或分列),再计算
#REF!引用的单元格被删除改用整列引用或命名区域
#DIV/0!分母为 0 或空用 IF 判断分母再除
结果全是 0数字是文本格式分列成常规格式,或整列 *1 转换

把常用公式沉淀成模板

每月都做的表,别每次重写公式:把清洗步骤、公式、格式做成一个模板文件,下次只替换原始数据区域。更进一步,把"原始数据 / 清洗后 / 汇总"分成三个工作表,任何人接手都能看懂流程。