吃透这60个Excel函数,你的价格表、对账单再也不求人

吃透这60个Excel函数，你的价格表、对账单再也不求人

——一张表从乱到准，差的不是耐心，是这几个函数

本文由作者使用 AI 辅助整理，函数用法均依据 Microsoft 官方文档，具体版本以你电脑上的 Excel / WPS 实际为准。

很多人一提到 Excel 就头大，觉得自己"不会用"。其实绝大多数人卡住的地方，根本不是什么高深操作，而是不知道有这个函数、或者知道但想不起来怎么写。

我见过太多做采购、做贸易、做仓库的人，每天对着几千行的价格表、对账单，手动一行行比对、手动算差价、手动找哪家贵了——一上午耗进去，还容易看花眼算错。

这篇文章不跟你讲虚的。我把日常最容易用上的 60 个函数按场景拆开，每个都给你：它是干嘛的 → 什么时候用 → 一句能直接抄的公式。你不用全背，存下来，用的时候翻出来对着抄就行。

先说一句掏心窝的话：函数不是越多越好，真正天天用的是二三十个。后面我标了"⭐高频"，这些是你优先要会的。

一、文本处理（清洗报价单、拆分型号，最常用）

做数据的人 80% 的时间不是在算，是在"洗"。供应商发来的表，型号里混着规格、名称里带空格、规格和单位挤在一个格子里——下面这 15 个专门对付这些。

1. TRIM 文本｜去首尾空格 ⭐高频 =TRIM(A2) 场景：从系统导出的数据，单元格前面总有无形空格，VLOOKUP 怎么都匹配不上，十有八九是它。先 TRIM 一遍再比。

2. CLEAN 文本｜去掉不可见字符 =CLEAN(A2) 场景：从网页或 PDF 复制来的内容，常带换行符、制表符，看着一样实则不同。CLEAN+TRIM 一起用最稳：=TRIM(CLEAN(A2))。

3. LEFT 文本｜从左边取字 ⭐高频 =LEFT(A2,3) 场景：型号"AB-12345"，只想取前缀"AB"做分类。

4. RIGHT 文本｜从右边取字 =RIGHT(A2,4) 场景：取订单号末 4 位、取规格后缀。

5. MID 文本｜从中间取字 ⭐高频 =MID(A2,4,5) 场景：固定格式编码第 4 位起取 5 位，比如从"SKU202401001"里抠出日期段。

6. LEN 文本｜算字符长度 =LEN(A2) 场景：校验长度对不对（身份证 18 位、编号 12 位），配合 IF 找异常。

7. FIND 文本｜找字符位置（区分大小写） =FIND("-",A2) 场景：找分隔符"-"在第几位，再喂给 MID 用。

8. SEARCH 文本｜找字符位置（不区分大小写） =SEARCH("x",A2) 场景：和 FIND 类似，但英文字母不分大小写，中文场景多用它。

9. TEXT 文本｜把数字变成你要的格式 ⭐高频 =TEXT(B2,"0.00") =TEXT(C2,"yyyy-mm-dd") 场景：把 45366 这种日期序列变回"2024-01-01"；把价格统一成两位小数字符串，避免科学计数。

10. CONCAT / CONCATENATE 文本｜拼接 ⭐高频 =CONCAT(A2,"-",B2) 场景：把"品牌+型号+规格"拼成一列唯一键，方便后续匹配。

11. & 文本｜最简单的拼接符号 =A2&"-"&B2 场景：和 CONCAT 一样，写起来更快，老手最爱。

12. SUBSTITUTE 文本｜替换指定内容 =SUBSTITUTE(A2,"旧","新") 场景：把"￥"符号、多余的"元"字批量清掉，再转数值。

13. REPLACE 文本｜按位置替换 =REPLACE(A2,2,3,"***") 场景：隐藏手机号中间几位（脱敏），做对外表很有用。

14. TEXTJOIN 文本｜带分隔符拼一堆 ⭐高频 =TEXTJOIN("、",TRUE,B2:B10) 场景：把一列标签拼成"红色、大号、现货"，做商品描述、做关键词一行搞定。

15. VALUE 文本｜文本转数字 =VALUE(A2) 场景：看着是数字其实是文本（左对齐、绿色小三角），算总和不出来，VALUE 一下就好。

二、查找匹配（对账单、找差价，核心中的核心）⭐⭐

这一部分是你每天最该熟的。两张表对不上，全靠它们。

16. VLOOKUP 查找｜竖向查 ⭐高频（必会） =VLOOKUP(找谁, 去哪查, 第几列, 0) =VLOOKUP(A2, 价格表!A:D, 4, 0) 场景：用"型号"去价格表里查"单价"。最后一个参数务必写 0（精确匹配），写 1 会糊弄你。

17. HLOOKUP 查找｜横向查 =HLOOKUP(表头, 去哪查, 第几行, 0) 场景：表头在首行、数据横向排时反向查。

18. XLOOKUP 查找｜VLOOKUP 的现代替代 ⭐高频（强烈推荐） =XLOOKUP(A2, 价格表!A:A, 价格表!D:D) 场景：不用数第几列、不会出错、查不到能直接给默认值。新版本 Excel / WPS 都有，能用的话优先它。

19. INDEX 查找｜按坐标取数 ⭐高频 =INDEX(返回列, 行号) =INDEX(D:D, 5) 场景：配合 MATCH 组成"无敌查找组合"。

20. MATCH 查找｜找位置 ⭐高频 =MATCH(A2, 价格表!A:A, 0) 场景：返回"型号"在价格表第几行，再喂给 INDEX。

21. INDEX+MATCH 组合｜双向查找 ⭐高频 =INDEX(价格表!D:D, MATCH(A2, 价格表!A:A, 0)) 场景：比 VLOOKUP 灵活——可以往左查、不用固定列、插列不崩。熟练后基本替代 VLOOKUP。

22. OFFSET 查找｜动态偏移 =OFFSET(起点, 行偏移, 列偏移) 场景：做动态区间、滚动汇总时用。

23. INDIRECT 查找｜用文本当引用 =INDIRECT("价格表!A"&B2) 场景：跨多个同名结构表切换查，进阶玩法。

24. CHOOSE 查找｜手动指定返回 =CHOOSE(2, "甲","乙","丙") 场景：把数字 1/2/3 映射成档位名，做等级显示。

25. LOOKUP 查找｜旧式查（近似） =LOOKUP(A2, 区间表) 场景：做"数量段→折扣率"这种阶梯匹配，比 IF 嵌套清爽。

三、逻辑判断（该涨该降、该标红，靠它自动判定）

26. IF 逻辑｜如果…就… ⭐高频（必会） =IF(B2>C2, "涨", "降") 场景：比上期价格，自动标涨/降。

27. IFS 逻辑｜多条件依次判断 ⭐高频 =IFS(B2>C2,"涨", B2<C2,"降", TRUE,"平") 场景：代替层层嵌套 IF，写起来清爽，新版才有的函数。

28. IFERROR 逻辑｜出错就给默认值 ⭐高频 =IFERROR(VLOOKUP(...), "未找到") 场景：查不到就显示"未找到"而不是满屏 #N/A，对外表必备。

29. AND 逻辑｜同时满足 =AND(B2>0, C2<100) 场景：多个条件一起卡，配合 IF 用。

30. OR 逻辑｜满足其一 =OR(B2="现货", C2="预售") 场景：状态是现货或预售都算有货。

31. NOT 逻辑｜取反 =NOT(ISERROR(A2)) 场景：反向判断。

32. ISERROR / ISNA 逻辑｜判断是不是错误值 =ISNA(VLOOKUP(...)) 场景：专门抓 #N/A，做"缺失清单"用。

33. SWITCH 逻辑｜多分支选择 =SWITCH(等级, "A","优", "B","良", "其他") 场景：比 IFS 更适合"一个值对应一个结果"的场景。

四、条件统计（按品类、按供应商算总量、算家数）⭐⭐

34. SUMIF 统计｜单条件求和 ⭐高频 =SUMIF(品类列, "办公用品", 金额列) 场景：只把"办公用品"的金额加起来。

35. SUMIFS 统计｜多条件求和 ⭐高频 =SUMIFS(金额列, 品类列,"办公", 供应商列,"甲") 场景：办公品类 + 甲供应商的金额合计，对账利器。

36. COUNTIF 统计｜单条件计数 ⭐高频 =COUNTIF(供应商列, "甲") 场景：甲家一共多少行。

37. COUNTIFS 统计｜多条件计数 ⭐高频 =COUNTIFS(品类列,"办公", 金额列,">100") 场景：办公品类里金额大于 100 的有几家。

38. AVERAGEIF 统计｜单条件平均 =AVERAGEIF(品类列, "办公", 单价列) 场景：办公品类平均单价，看是不是被高价品拉偏。

39. AVERAGEIFS 统计｜多条件平均 =AVERAGEIFS(单价列, 品类列,"办公", 供应商列,"甲") 场景：甲家办公品类的均价。

40. SUBTOTAL 统计｜会避开隐藏行的汇总 ⭐高频 =SUBTOTAL(9, 金额列) 场景：你筛选了"只看涨价的"，它只算筛出来的，做临时汇总最准。

41. COUNT 统计｜数数字单元格 =COUNT(B2:B100) 场景：看有多少行真有数字（漏填一眼看穿）。

42. COUNTA 统计｜数非空单元格 =COUNTA(A2:A100) 场景：看有多少行填了内容。

五、数学与四舍五入（报价、算税、算件数）

43. SUM 数学｜求和 ⭐高频（入门第一个） =SUM(B2:B100) 场景：总价、总数量。

44. ROUND 数学｜四舍五入 ⭐高频 =ROUND(B2, 2) 场景：价格保留两位，别让 3.145 变 3.14 惹麻烦。

45. ROUNDUP 数学｜向上取整 =ROUNDUP(B2, 0) 场景：运费、件数宁多勿少时。

46. ROUNDDOWN 数学｜向下取整 =ROUNDDOWN(B2, 0) 场景：折扣后价格向下取，给客户让利。

47. INT 数学｜取整数部分 =INT(B2) 场景：去掉小数，看整数件数。

48. MOD 数学｜取余数 ⭐高频 =MOD(行号, 2) 场景：做隔行底色（配合条件格式）、判断奇偶。

49. ABS 数学｜取绝对值 =ABS(B2-C2) 场景：算价差绝对值，不管涨还是降。

50. CEILING 数学｜向上取到某倍数 =CEILING(B2, 0.5) 场景：价格按 0.5 一档向上取整。

51. FLOOR 数学｜向下取到某倍数 =FLOOR(B2, 10) 场景：报价按 10 元一档向下取整。

52. PRODUCT 数学｜连乘 =PRODUCT(B2:C2) 场景：单价×数量之外，算多因子乘积。

53. SQRT 数学｜开平方 =SQRT(B2) 场景：算标准差、几何均值偶尔用到。

六、日期与时间（账期、交期、逾期）

54. TODAY 日期｜今天 =TODAY() 场景：动态算"距今天多少天"。

55. NOW 日期｜现在 =NOW() 场景：带时间戳的录入。

56. DATEDIF 日期｜两日期差 ⭐高频 =DATEDIF(下单日, 今天, "d") 场景：算账期过了多少天、交期延误几天。

57. NETWORKDAYS 日期｜工作日天数 =NETWORKDAYS(开始, 结束) 场景：扣掉周末算实际处理天数。

58. EOMONTH 日期｜月末日期 =EOMONTH(今天, 0) 场景：算本月最后一天，做月结对账基准。

59. WEEKDAY 日期｜星期几 =WEEKDAY(今天, 2) 场景：判断是不是周末、排值班。

60. TEXT+日期｜格式化显示 =TEXT(TODAY(),"yyyy年mm月dd日") 场景：对外表里把日期写得人话一点。

七、动态数组（新版本神器，一次出一片）

如果你的 Excel / WPS 是新版，下面这 5 个能让你从"写公式拖拽"升级到"写一次自动溢出"。

61. UNIQUE 动态｜去重 =UNIQUE(供应商列) 场景：一键列出所有不重复的供应商名。

62. FILTER 动态｜按条件筛 ⭐高频 =FILTER(全表, 品类列="办公") 场景：自动抽出办公品类所有行，做子表不用复制粘贴。

63. SORT 动态｜排序 =SORT(价格列, 1, 1) 场景：价格自动从小到大排好。

64. SORTBY 动态｜按别的列排 =SORTBY(商品列, 价格列, 1) 场景：按价格给商品名排序。

65. SEQUENCE 动态｜生成序列 =SEQUENCE(10,1,1,1) 场景：快速造 1~10 的序号列。

八、你真的要先会哪 20 个？

函数列了 60 多个，别焦虑。我帮你砍到每天高频、先练会这 20 个就够打天下：

TRIM、LEFT/RIGHT/MID、TEXT、CONCAT(&)、VALUE、VLOOKUP、XLOOKUP、INDEX+MATCH、IF、IFS、IFERROR、SUMIF/SUMIFS、COUNTIF/COUNTIFS、ROUND、ABS、DATEDIF、SUBTOTAL、UNIQUE、FILTER、TEXTJOIN。

把这 20 个的写法抄进你的"常用公式本"（哪怕就记在手机备忘录），遇到对应场景直接套，半个月就能形成肌肉记忆。

九、三个最容易踩的坑

1.  VLOOKUP 最后一个参数忘了写 0：默认是模糊匹配，会给你一个"看起来对但其实错"的数。永远写 0。

2.  文本型数字 vs 数值型数字：一个左对齐一个右对齐，VLOOKUP 死活匹配不上。先用 VALUE 或乘 1 转一下。

3.  跨表引用丢了路径：公式里 价格表!A:D 的"价格表"是工作表名，改名就全红。改名前先想清楚，或改用 XLOOKUP 配合定义名称。

十、最后说一句

Excel 不是学出来的，是用出来的。你不用把这 60 个全背下来，把这篇收藏了，下次对账单卡住、价格表算不动，翻出来对着抄就行。

真正拉开差距的，从来不是谁函数记得多，而是谁愿意先把重复劳动交给公式。你每天省下来的那两小时，拿去谈客户、看行情，比手动对账值钱多了。

你平时最常被哪张表卡住？是价格比对、还是库存盘点？评论区说说，下次我挑一个写成一步步的实操。