公式速查表与写前自查清单

全书速查表：按用途找函数

这本书从数学统计讲到财务信息，函数散落在四个方向。把用过的函数按用途重新排一遍，用的时候先在这里定位，再去对应篇章看细节。

版本一栏标 2019 和 2021 的函数发文件前要确认对方环境，标全版本和 2007 的可以放心用。这张表不需要背，收藏起来随时查就行。

关于这张表还有一个使用建议：把自己工作中真正用到的十几条单独挑出来，抄在便签或者另存一份小抄，天天看的那十几个才是最该熟的。表里列了三十多条，不是要你全掌握，而是让你在遇到问题时知道有这么个东西存在，回头能找到。工具的价值在于被想起，不在于被记住。

按场景选函数

实际工作里的顺序往往是反的：先有具体问题，再找该用谁。下表把高频问题和函数直接对应，省掉中间的思考成本。

写公式前的自查清单

公式写错，八成不是语法不熟，而是动手前漏了某一步检查。养成下面这个六步习惯，能省掉大量回头排查的时间。

第一步查数据类型。数字被存成文本，求和结果是零；编号被当成数字，长串会变科学计数法。动手前先在数据旁边写一条 =ISNUMBER(A2) 和 =ISTEXT(A2) 看看这列到底是哪种。这一步花十秒钟，能提前发现后面所有报错的根源。

第二步查空白和空格。看着空的单元格可能藏着一个空格，COUNTA 会把它算进去。用 =LEN(TRIM(A2)) 判断，结果是零才是真空。查不出问题的公式，往往问题就在这类看不见的字符上。

第三步查区域范围。写区域别图省事用整列，A:A 看着简洁，实际要扫过一百万行。写 $A$2:$A$5000 这样的实际范围，又快又稳，还能避免以后数据下面多出一行脏值被误算进去。

第四步查引用方式。要向下填充的公式，行号不能锁；要固定引用的参数表，行列都要加美元符号。填错引用是公式错位的最常见原因，填充前在头两行对比一下结果，确认位置对得上。

第五步查容错。任何可能查不到、可能为空的地方，外面套一层判断或者 IFERROR。这不是为了掩盖错误，而是为了让报错时表格看起来是有意设计过的，而不是一片狼藉。

第六步把结果和手工样本对一遍。挑三五行用计算器或肉眼算一下，跟公式结果比对。三行都对得上再往下填充，否则填充几千行之后发现问题，返工的成本高得多。

报错类型速查

公式报错时先看错误码，大部分能直接定位原因。

排查顺序建议从 #NAME? 和 #REF! 这两个最基础的问题开始，它们是语法层面的错误，修正后其他错误往往会一起消失。剩下的类型错误和查找错误属于逻辑层面，需要结合具体数据判断。

让公式好维护的三个习惯

写完能跑只是及格，能交接、能改、半年后自己还看得懂才是及格线以上。三个习惯投入很小，回报很大。

习惯一是给关键区域起名字。把参数表、税率、基础数据这些会被反复引用的区域，在名称框里起个中文名，比如“税率表”“客户主数据”。公式里写 =VLOOKUP(A2,客户主数据,3,0) 而不是 =VLOOKUP(A2,Sheet3!$A$2:$D$5000,3,0)。好处是区域变了只改名称定义，所有公式自动跟随；更重要的是一眼能看出这个公式在拿什么数据。

习惯二是把参数集中放在一处。税率、考核线、预算上限这类会变的数字，不要散落在各个公式里硬写。集中放在表头或专用参数区，公式引用单元格。明年税率从百分之十三变成百分之十一，你只需要改一个格子，而不是全表搜索。

习惯三是给复杂公式加注释。Excel 没有行内注释，但可以用名称管理器给公式片段起名，或者干脆在旁边的空列写一句说明。更长久的做法是把公式里的中间步骤拆成辅助列，每一步占一列，逻辑一目了然。辅助列多一点不影响速度，反而让调试轻松得多。

一条公式的诞生过程

前面讲了表格和清单，这里用一个完整的例子把整条思路串一遍，看看一个公式从需求到落地要经过哪些步骤。

需求是这样的：有一张员工表，A 列工号，B 列姓名，C 列部门，D 列入职日期，E 列月薪。要做一张部门汇总，算出每个部门的平均月薪和平均司龄。听上去是两件事，拆开来做就清楚了。

先解决部门列表。部门会增减，不能手工列。用 =UNIQUE(C2:C500) 一条公式拿到当前所有部门，新增部门时自动出现。这一步就是前面讲过的动态数组思路，如果版本不支持，退回到用数据透视表取唯一值。

再算平均月薪。写法是 =IFERROR(AVERAGEIF($C$2:$C$500,G2,$E$2:$E$500),"暂无数据")，G2 是部门名。用 IFERROR 是为了防止某个部门没有记录时报除零错误。注意这里用的是 AVERAGEIF 而不是 AVERAGE 加 IF，前者一条公式解决，后者要配数组，可读性差。

接着算平均司龄。这个麻烦一点，要先算出每个人的司龄再平均。辅助列写 =DATEDIF(D2,TODAY(),"Y") 得到年数，填充整列。然后在汇总表里写 =IFERROR(AVERAGEIF($C$2:$C$500,G2,$F$2:$F$500),0)，F 列就是刚算出来的司龄。辅助列看着多余，但它让后面的公式简单直白，比嵌套三层要值。

最后检查一遍。按自查清单的顺序：数字列确认是数值格式不是文本；日期列确认是真日期不是字符串；区域范围写的是实际范围不是整列；引用方式确认 G2 在填充时要跟着走所以不加美元符号，而区域要锁住所以加了。全部确认后向下填充，一张部门汇总表就成了，源数据一变结果自动更新。

这个例子里的每个函数都在前面讲过，把它们串起来之后你会发现，做表的核心不是函数多，而是思路清楚：先把问题拆成独立的小问题，每个小问题找最简单的函数解决，最后拼起来。函数是工具，拆分才是本事。

三个最常被问到的困惑

第一个困惑是函数名记得住但参数顺序总记错。这不是记忆力问题，是学习方式问题。参数顺序靠背没有意义，靠理解才牢。拿 SUMIFS 举例，它的顺序是求和区域在前、条件区域在后，这个顺序确实和直觉相反。但只要你理解成先说清楚要加什么，再说明在哪些条件下加，顺序自然就记住了。每学一个函数，先弄懂每个参数在业务上代表什么，比死记位置有效得多。

第二个困惑是网上的公式复制过来不能用。绝大多数情况是三个原因之一：单元格区域没改，还是原文的 A2:B10；分隔符号地区差异，中文版用逗号而部分版本用分号；函数在当前版本里不存在。前两个手动改一下就好，第三个需要换写法。复制公式时养成习惯，先看清它引用了哪些区域，把区域换成自己的，再检查函数是否可用，最后抽样验证结果。这三步做完再往下填充，能避开绝大多数问题。

第三个困惑是公式对了但结果是错的。这种情况最危险，因为不报错所以容易被忽略。常见原因有：区域范围多算了表头或合计行，导致平均值被拉低；文本型数字参与计算被当成零，导致求和偏小；日期格式不统一，DATEDIF 算出奇怪的结果。排查这类问题只能靠抽样，挑几行手工算一遍对照。养成每个新公式都手工验三行的习惯，是避免带错数据汇报的最后一道防线。

学完这本书之后

这本书覆盖了四类高频函数，但 Excel 的函数有四百多个，谁也学不完。学不完不要紧，重要的是建立起一套属于自己的查用方法：遇到问题先想这属于哪一类任务，再从速查表里找对应的函数，找到之后去具体篇章看语法和坑，最后用自查清单验证一遍。这个方法本身比记住任何一个函数都值钱。

下一步的提升方向有两个。一是往数据处理的深水区走，学 Power Query 做清洗合并，学数据透视表做交互分析，这两样能解决公式解决不了的大数据量问题。二是往自动化的方向走，学用名称管理器封装逻辑，学用 LET 和 LAMBDA 把重复计算收拢，让复杂的表变得可维护。两条路没有先后，看你手上的表更需要哪一种。

最后一个提醒：工具会更新，函数会增加，但做表的基本功是不变的——想清楚要算什么，先把数据结构理顺，再动手写公式。结构清楚，公式就简单；结构混乱，再多的函数也救不回来。这本书教的是函数，真正希望你带走的是这个顺序。

常见问题

这些函数里哪几个最该先学

如果只学五个，推荐 SUMIFS、COUNTIFS、IFERROR、INDEX 加 MATCH、TEXTJOIN。这五个覆盖了统计、容错、查找、文本四类最高频需求，且全部兼容老版本。学会这五个，日常八成的表格任务就不用现搜了。剩下的按需补，用到了再学印象更深。

速查表需要背下来吗

不需要，也不该背。函数太多，硬记容易混淆参数顺序。正确做法是记住“这件事该用哪一类函数”，具体语法查表。用多了自然会记下来，那才是真的记住，比死记硬背牢靠。真正的门槛从来不是记不住语法，而是不知道该用什么。

为什么我写的 SUMIFS 结果是零

按自查清单顺序查一遍。最常见原因有三个：条件区域的写法和数据不一致，比如条件是数字但数据是文本；区域范围大小不匹配，求和区域和条件区域行数不一样；条件里的比较符号写成全角，或者没加引号。用 =""&条件单元格 手动拼接条件字符串能解决大部分类型问题。

公式里的冒号和逗号地区差异

中文版 Excel 用逗号分隔参数，部分地区的版本用分号，公式从网上复制过来时可能报错。这不是函数本身的问题，是区域设置不同。解决办法是改系统区域设置，或者按报错位置手动把分号换成逗号。跨语言版本协作时这一点要留意，公式复制后最好在本地实测一遍。

老版本怎么实现这些技巧

正文里每处用到的老版本写法，在对应篇章都有替代方案。核心思路是：新函数大多能被基础函数组合替代，差别主要在写起来麻烦一点、自动化程度低一点。插件和新函数让工作更快，但基础的 SUM、IF、VLOOKUP 组合永远可用，任何时候都不会让你无路可走。