做表这些年被同事问得最多的一句话就是有没有Excel常用函数大全。我每次都想说网上那种把四百多个函数按字母排一遍的清单收藏了也不会打开第二次。真正在工位上救命的从来不是我知道有多少个函数而是这张表摆在我面前我该敲哪几个。所以这篇不打算做字典我按查找引用、条件统计、文本清洗、日期计算四条主线把日常八成工作量会用到的Excel常用函数串起来讲每个函数都配上我自己踩过的坑和能直接抄的公式。不管你是刚接手台账的行政、天天对账的财务还是被周报月报折磨的运营只要你会选中区域、会敲等号就能跟下来。看完至少能拿到两样东西一眼看懂别人表里那坨长公式在干什么以及自己动手写一个不再报错的嵌套公式。1. 先想清楚一件事函数不是背出来的是按场景拎出来的1.1 把几百个函数砍成四类工作量就少一大半Excel内置函数按版本不同大概有四五百个真正常用的不到三十个。我按手上的活儿把它们分成四类查找引用解决这张表要的数据在另一张表里条件统计解决按部门、按月份、按品类加总文本清洗解决系统导出的数据脏得没法直接用日期时间解决按月按周按工作日算。剩下的几百个函数绝大多数人一辈子碰不到三次。这个分类不是学术划分是我接活时的判断顺序。拿到一张表先看它要干什么再去对应类别里挑函数比在函数列表里按字母翻要快得多。举个例子同事发来一句帮我把每个人的部门填上这属于查找引用脑子里立刻跳出VLOOKUP或者INDEXMATCH如果他说帮我把每个部门的金额加起来那是条件统计直接上SUMIFS。分类对了函数自然就锁定了剩下只是参数怎么填的问题。1.2 我的练习顺序先单条件再嵌套最后才碰数组很多人卡住不是因为函数难是跳级了。我的建议是三步走。第一步只练单条件单结果的写法一个IF、一个VLOOKUP精确匹配练到闭着眼睛能写对四个参数第二步练两个函数嵌套比如IF里套VLOOKUP、SUMIFS里带通配符第三步才去碰数组和动态数组。跳步的典型症状是公式一长就不知道错在哪因为每一层的返回值你都没单独验证过。我自己的笨办法是每写一层嵌套就在旁边开一列把这一层的结果打出来看确认无误再往里套套完把那列辅助列删掉。慢一点但不容易塌。新人最容易犯的错是把五层IF一口气写完然后回车报错然后盯着屏幕找半小时——其实只要拆开看问题往往出在第二层的逗号打成了分号。1.3 参数记不住记住要什么、去哪找、返回第几个函数的参数看着吓人其实绝大多数都能翻译成一句人话。VLOOKUP翻译过来是我要查谁、去哪个区域找、找到后返回这个区域第几列、要不要精确匹配SUMIFS翻译过来是对哪一列求和、在哪个区域里比对第一个条件、条件是什么、第二个条件区域、第二个条件是什么。你把这个句子念顺了参数顺序自然就记住了。我教新人时从不让他们背参数只让他们先把这句话说完整。说不完整说明你自己都没想清楚这一步要干嘛这时候敲公式纯属碰运气。等这句话能脱口而出写公式就变成了填空出错率能降一大半。2. 查找引用VLOOKUP没死但你得知道它什么时候会骗你2.1 四个参数逐个拆开看VLOOKUP的完整写法是VLOOKUP(查找值, 查找区域, 返回列序号, 匹配方式)。第一参数是你要查的那个值可以写死也可以引用单元格第二参数是查找区域这里有个硬规矩查找值必须落在区域的第一列这是VLOOKUP的死穴它只会从左往右看第三参数是找到之后返回区域里第几列从区域第一列算作1开始数第四参数填0或FALSE是精确匹配填1或TRUE是近似匹配。VLOOKUP(E2,$A$2:$C$500,3,0)这段的意思是拿E2这个值去A2:C500这个区域的第一列A列里找找到后返回同一行第3列C列的内容要求完全一致才算找到。第四参数省略不写会默认近似匹配这是新手最容易中招的地方。注意第四参数不写等于埋雷。数值型数据在近似匹配下会返回一个不大于查找值的最大值看着有结果其实全是错的而且不报错最难查。2.2 VLOOKUP最常见的五个翻车现场第一个查找值格式不一致。A表里工号是文本格式的10086B表里是数字10086肉眼一模一样VLOOKUP死活返回#N/A。判断方法很简单选中单元格看编辑栏里有没有前置的单引号或者用ISTEXT(A2)判断。解决办法是在查找值后面加个转成文本或者用--转成数字两边统一即可。第二个查找区域没锁。写公式时没加美元符号往下一拖区域跟着跑前面几行结果是对的后面全错。养成习惯第二个参数按一下F4。第三个返回列序号是硬编码。写死了3后来有人在B列插了一列序号全歪。稳妥的做法是用MATCH动态定位或者干脆换成INDEXMATCH。第四个重复值只返回第一个。VLOOKUP遇到多条匹配记录时永远只认从上往下第一条它不会告诉你下面还有。如果业务上不该出现重复就得先用COUNTIFS查出来。第五个查找值超过255个字符。老版本有这个限制超长文本会直接报错这种场景建议改用SUMPRODUCT或者INDEXMATCH。2.3 INDEXMATCH与XLOOKUP什么时候该换枪INDEX(返回区域, MATCH(查找值, 查找区域, 0))这一套是VLOOKUP的升级替代。优势有三个不受必须从左往右的限制可以反向查插入删除列不会破坏公式因为不用数第几列大数据量下计算速度快一些因为MATCH只扫一列INDEX取值是直接定位。缺点是可读性差一点新人看着晕。INDEX($C$2:$C$500,MATCH(E2,$A$2:$A$500,0))新版Excel里的XLOOKUP把这套逻辑简化了写法是XLOOKUP(查找值, 查找数组, 返回数组, [找不到时返回什么], [匹配模式], [搜索方向])。它自带错误兜底第四个参数直接填未找到就不会再看到#N/A默认精确匹配不用操心第四参数还能从后往前搜。我现在的习惯是自己用的表优先XLOOKUP要发给可能用旧版Excel的同事就退回INDEXMATCH。方案反向查找插入列是否受影响错误兜底兼容性VLOOKUP不支持受影响需外套IFERROR全版本INDEXMATCH支持不受影响需外套IFERROR全版本XLOOKUP支持不受影响内置第四参数较新版本3. 条件统计SUMIFS和COUNTIFS这一套能顶半个数据透视表3.1 SUMIF与SUMIFS的参数差异别搞混SUMIF(条件区域, 条件, [求和区域])只有三个参数条件区域在前求和区域在后。SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)恰好相反求和区域打头。这个顺序差异坑过无数人我也是错了好几次才记住。SUMIFS($D$2:$D$5000,$A$2:$A$5000,$G2,$B$2:$B$5000,H$1)这段是典型的二维汇总D列求和A列必须等于G2比如部门B列必须等于H1比如月份。写这种公式有个硬性要求所有区域的行数必须完全一致否则结果会错位而且不一定报错。我通常的做法是选中第一段区域后用F4锁定然后复制这段文本改列号保证每段都是同样的起止行。3.2 通配符和区间条件写法有讲究条件参数不只是等于某个值。文本可以带通配符*代表任意多个字符?代表单个字符~是转义。比如统计所有以A开头的产品条件写成A*统计包含退货两个字的订单数写成*退货*。数值区间要用引号把比较符和数字包在一起并且用拼接单元格引用COUNTIFS($C$2:$C$5000,2024-01-01,$C$2:$C$5000,2024-03-31)想用单元格里的日期做边界得写成$J$1不能直接写$J$1后者会被当成文本比较结果永远是0。提示SUMIFS的条件里写大于0这类区间时注意空单元格也会被算进去因为空单元格在某些比较下等价于0。做金额汇总时建议加一个金额的条件排除空值。3.3 SUMPRODUCT多条件去重计数的救命稻草有些统计需求COUNTIFS天生干不了比如统计有多少个不同的客户下过单。传统做法是用数据透视表但透视表不能实时跟着公式走。这时候用SUMPRODUCTSUMPRODUCT(($A$2:$A$1000)/COUNTIF($A$2:$A$1000,$A$2:$A$1000))原理是把每个客户出现的次数取倒数重复的客户加起来刚好等于1不重复的也算1最后求和就是唯一值个数。第一次看不懂没关系把它当模板存着改一下区域就能用。SUMPRODUCT还能做多条件计数SUMPRODUCT((A:A华东)*(B:B100))注意这里所有区域必须等长且不能写整列引用全列引用在SUMPRODUCT里会拖垮性能。4. 文本清洗系统导出的脏数据八成靠这六个函数救回来4.1 脏数据通常脏在哪三个地方从业务系统导出的表格脏法基本就那几种。第一种是空格问题名字前后带空格、名字中间有两个空格导致VLOOKUP查不到、去重去不掉。第二种是不可打印字符看着干干净净实际上末尾藏着换行符或者制表符是系统拼接时带出来的。第三种是一格里塞了多个信息比如张三/技术部/主管全在一个单元格里想按部门统计就没法做。这三种脏法各有对症的函数。空格用TRIM不可打印字符用CLEAN一格多信息用分列或者MIDFIND。4.2 TRIM、CLEAN、SUBSTITUTE的配合套路TRIM(文本)去掉首尾所有空格并把中间连续的多个空格压成一个。这是最常用的清洗函数副作用是它只处理普通的半角空格遇到全角空格中文输入法打出来的那种无能为力。TRIM(SUBSTITUTE(A2,CHAR(160), ))CHAR(160)是网页复制内容里常见的非断行空格TRIM动不了它得先用SUBSTITUTE替换成普通空格再套TRIM。这个组合是我处理从网页或者邮件里复制过来的表格时的标准动作。CLEAN(文本)专门清理ASCII码0到31的不可打印字符从系统导出的CSV里特别常见。SUBSTITUTE(文本, 旧文本, 新文本, [替换第几个])是精确替换第四个参数可以指定只替换第几次出现比如只把第一个-换成空写成SUBSTITUTE(A2,-,,1)。注意清洗完别忘了复制—选择性粘贴为值覆盖回去。如果直接删掉原列依赖原列的所有公式会一起断掉这个操作我建议先复制一列备份。4.3 拆分姓名、编码、地址MID加FIND的组合拳MID(文本, 起始位置, 截取长度)从中间截取LEFT从左截取RIGHT从右截取这三个负责取。FIND(找什么, 在哪找, [从第几位开始])负责定位LEN负责数长度。真正干活的是它们的组合。比如张三/技术部/主管这种结构想取部门MID(A2,FIND(/,A2)1,FIND(/,A2,FIND(/,A2)1)-FIND(/,A2)-1)逻辑是这样的第一个FIND找到第一个/在第几位加1就是部门名的起始位置第二个FIND从第一个/后面一位开始找找到第二个/的位置两者相减再减1就是部门名的长度。这公式我第一次写的时候试了七八遍现在基本能一次写对靠的就是拿笔在纸上把位置数字标出来。取姓名的部分更简单LEFT(A2,FIND(/,A2)-1)。取最后的职务可以用RIGHT(A2,LEN(A2)-FIND(/,A2,FIND(/,A2)1))。新版Excel还有TEXTBEFORE和TEXTAFTER按分隔符直接取前后段写法短很多但老版本不认。要发给多人协作的文件还是老实写MIDFIND。4.4 TEXT函数格式化输出的隐藏彩蛋TEXT(值, 格式代码)表面上是把数字转成文本实际上是整个Excel里最被低估的函数。它的格式代码和单元格自定义格式是同一套语法。TEXT(A2,yyyy年m月) 日期转2024年3月 TEXT(A2,#,##0.00) 千分位保留两位 TEXT(A2,0%) 小数转百分比 TEXT(A2,aaaa) 日期转星期一 TEXT(A2,[1000]超标;合格) 条件格式一个典型用法是把日期拼进文本里本月合计TEXT(SUM(D:D),#,##0元)。如果不用TEXT直接拼SUM的结果会变成一长串小数看着像乱码。另一个用法是把日期统一成yyyy-mm-dd喂给后面的文本比较避免2024/3/1和2024-03-01被当成两个不同的值。提示TEXT转出来的是文本不能直接参与数值运算。用完之后如果还要算得用--或者VALUE()转回数字。5. 日期计算Excel里日期是数字想通这一点就不难了5.1 日期的本质与三种坑Excel里的日期本质是一个序列号1900年1月1日记作1每过一天加1。所以2024-03-01在底层其实是45352这样的整数。想通这一点很多操作就顺了两个日期相减得到的是天数差日期加7就是往后推一周时间本质是小数部分0.5代表中午12点。三种常见的坑。第一种是看着像日期其实是文本特征是左对齐数字默认右对齐参与运算会报#VALUE!用ISNUMBER(A2)一测就知道文本日期可以用--A2或者DATEVALUE转换。第二种是日期和时间混在一个格里筛选某一天的时候只匹配到0点那一条解决办法是加辅助列用INT(A2)只取日期部分。第三种是跨年计算没考虑闰年用天数除以365算年龄会有一两天的误差得用专门函数。5.2 DATEDIF藏在Excel里但极好用的函数DATEDIF是个隐藏函数输入的时候没有参数提示但它算年龄、算工龄、算账龄最好用。写法是DATEDIF(起始日期, 结束日期, 单位)单位里Y是整年数M是整月数D是天数YM是去掉整年后的剩余月数MD是去掉整年整月后的剩余天数。DATEDIF(B2,TODAY(),Y) 周岁年龄 DATEDIF(B2,TODAY(),Y)年DATEDIF(B2,TODAY(),YM)个月 精确到月的年龄第二个公式是我给人事做员工年龄分布时用的输出35年4个月这种格式比单纯一个数字直观。注意DATEDIF要求起始日期不能大于结束日期否则报#NUM!套个IFERROR兜住。5.3 EDATE、EOMONTH、WORKDAY、NETWORKDAYS跟月和工作日打交道EDATE(起始日期, 月数)返回几个月后的同一天做合同到期提醒、账期计算特别顺手。EOMONTH(起始日期, 月数)返回那个月的最后一天配合EOMONTH(B2,-1)1就能得到当月的第一天这个组合我几乎每个月都要用一次。WORKDAY(起始日期, 工作日数, [节假日])往后推N个工作日跳过周末NETWORKDAYS(起始, 结束, [节假日])算两个日期之间的工作日天数。这两个函数的第三个参数可以引用一个节假日区域注意要绝对引用不然一拖就漂。WORKDAY(B2,15,$J$2:$J$20) 合同签订后15个工作日 NETWORKDAYS(B2,C2,$J$2:$J$20) 项目周期内的有效工作日注意WORKDAY和NETWORKDAYS默认只跳过周六周日。如果公司是大小周或者单休这两个函数就不准了得改用NETWORKDAYS.INTL它有个参数可以自定义哪几天休息比如周末只休周日的写法是11。6. 完整实战一张月度经营看板公式从零搭到能用6.1 先把数据源捏成一维表公式写得再漂亮源数据不规范也是白搭。我当时接手的原始表是这样的每个月的销售额单独一个sheet表头合并单元格部门名竖着排在左边月份横着排在顶上典型的二维交叉表。这种表看着舒服但一个公式都写不了因为没有任何一列是统一的类型字段。处理办法是先做一维化新建一个sheet叫明细只留六列——日期、部门、产品、数量、单价、金额每一行代表一条独立记录。原来的二维表用复制—选择性粘贴—转置配合手动补录第一张表花了两个小时后面几个月就快了。这里插一句如果源表行数很多可以用Power Query逆透视比手工快得多值得花半小时学一下。一维表还有几个要点表头只占一行不要合并单元格不要有小计行混在中间日期列统一日期格式。做到这三点后面所有公式都能安心写。6.2 第一层基础汇总公式看板的左上角放部门列表顶上放月份交叉格子里填汇总SUMIFS(明细!$F:$F,明细!$B:$B,$A3,明细!$A:$A,DATE(2024,COLUMN()-1,1),明细!$A:$A,EOMONTH(DATE(2024,COLUMN()-1,1),0))这段用COLUMN()把横向月份换算成月份数字再用DATE拼出月初和月末避免了12个公式写12遍。缺点是整列引用的性能问题数据量超过五万行时会明显卡顿正规做法是把$F:$F换成$F$2:$F$100000这种固定区间或者把明细区域转成表格用结构化引用。6.3 第二层环比、占比、排名汇总出来之后业务方最关心的三个衍生指标是同比环比、占比和排名。环比IFERROR((C3-B3)/B3,)。IFERROR这一层不能省因为上个月可能是0或者空直接除会得到#DIV/0!屏幕上红一片。我这个表里还加了个判断如果上期是0返回新增而不是空字符串。占比IFERROR(C3/SUM($C$3:$C$12),)。注意分母要绝对引用横向拖拽的时候才不会被改变。排名RANK.EQ(C3,$C$3:$C$12,0)。第三参数填0是降序不填默认也是降序但显式写出来更清楚。并列名次的情况RANK.EQ会返回相同的排名下一个名次会跳过比如两个并列第2下一个就是第4这符合大多数业务口径。6.4 第三层把错都兜住让看板不会突然爆红一张要给领导看的表最忌讳满屏#N/A和#DIV/0!。我的做法是每个可能出错的公式外面都套一层IFERRORIFERROR(VLOOKUP(...),) IFERROR(SUMIFS(...),0)文本类结果返回空字符串数值类结果返回0。这个习惯一开始觉得麻烦后来救过我好几次——有次源表某个月的数据没维护整个看板几十个格子全红了我重做了一遍加上兜底之后再遇到缺数据也只是空着不会显得像坏了。7. 报错与性能公式跑不动、结果不对先看这几条7.1 八种报错码速查表报错含义常见原因排查动作#N/A找不到查找值不存在、格式不一致、多了空格先单独查一遍查找值用TRIM和ISNUMBER判断类型#VALUE!类型不对文本参与数学运算、区域大小不匹配检查参与运算的单元格是否含文本或空格#REF!引用失效删掉了被公式引用的行或列用公式—追踪引用单元格定位#DIV/0!除零分母为0或空套IFERROR或先判断分母#NAME?名字不认函数名拼错、引用了不存在的名称、版本不支持检查拼写确认函数在当前版本可用#NUM!数值越界日期相减为负、对数取0检查参数范围#NULL!区域不相交用了空格做区域交叉运算检查公式里的空格#SPILL!溢出受阻动态数组结果被其他内容挡住了清空结果区域下方或右侧的单元格这张表我打印出来贴过工位后来发现自己最常遇到的就两种#N/A和#VALUE!一个查内容一个查类型掐住这两条能解决七成问题。7.2 全列引用和易失函数表格变卡的隐形元凶SUMIFS(A:A, B:B, 华东)这种整列引用写起来爽但只要公式数量上百每次重算都要扫一遍百万行表格就开始转圈。判断方法很简单按F9手动重算看状态栏显示计算中的时间超过两三秒就该改了。改法是把整列换成实际数据范围或者用Excel的表格功能CtrlT公式自动变成SUMIFS(表1[金额],表1[区域],华东)范围会跟着数据增减自动调整还不用改。另一类是易失函数TODAY、NOW、OFFSET、INDIRECT、RAND、RANDBETWEEN。这类函数的特点是不管你改没改东西任何一次计算都会重新求值用多了表格就像卡了缓。TODAY在几个格子里用没问题但如果几百行数据里都塞了OFFSET那就是在自我折磨。替代方案是用INDEX代替OFFSET用表格结构化引用代替INDIRECT。7.3 动态数组和溢出新版Excel的甜与坑新版Excel的公式一次返回多个结果会自动溢出比如SORT(FILTER(明细!A2:F1000,明细!B2:B1000华东))一条公式就能拉出一个筛选排序后的表不用写辅助列。这个功能一旦用上就回不去了。但它有个坑溢出区域是活的如果你在它下面又写了别的内容就会报#SPILL!。我曾经在一个已经放了溢出公式的表格里加了合计行整块数据瞬间变红。解决办法是给溢出公式留足空间或者用符号强制只返回单个值。另一个提醒是动态数组公式发给旧版本Excel的同事对方会看到花括号包裹的数组公式或者干脆报错。团队协作用之前先问一句版本能省很多沟通成本。8. 几个我踩过的坑和固定习惯有几个习惯是踩完坑之后固定下来的分享出来。第一任何从别的表引数据的公式先查重复。用COUNTIFS(查找列, 查找值)1在旁边加一列确认全是FALSE再用VLOOKUP能提前发现一对多的隐患。第二源数据和公式分表放。我见过太多把原始数据和计算过程混在一个sheet里的表改一个公式把原始数据覆盖了找都找不回来。第三复杂的嵌套先拆再合辅助列不丢人丢人的是写了个自己都看不懂的公式第二天不敢动。最后再补一句关于函数学习节奏的体会。我一开始也是照着大全的清单背函数背了忘忘了背效率很低。真正让水平跳一级的是某次被一张乱七八糟的源表逼着连续写了三十多个公式写完回头再看那些清单发现大半都用上了而且知道什么场景用哪个。所以别急着收藏清单先找一张自己工作上真实的表从把这一列的数据对上开始一个函数一个函数地试试通了再换下一个。这个过程的收益比看十篇大全都实在。