国庆收藏!36个Excel函数,一学就会

① SUMIFS｜多条件求和

简单描述：像个听话的仓库管理员，你喊一声「华东区、A 产品的货加一下」，它只搬符合条件的。做月报、对账，它是出场率最高的一个。

=SUMIFS(C:C, A:A, "华东", B:B, "A产品")

② COUNTIFS｜多条件数个数

简单描述：SUMIFS 的兄弟，一个管加、一个管数。老板问「销售部今天几个人迟到」，别翻考勤表，它一秒数完。

=COUNTIFS(B:B, "销售部", C:C, "迟到")

③ SUMPRODUCT｜乘积求和

简单描述：两列先相乘再合计。算总金额时，不用先建一列「单价×数量」，它一步到位，还能算加权平均。

=SUMPRODUCT(A2:A10, B2:B10)

④ SUBTOTAL｜只算看得见的

简单描述：筛选完再求和，SUM 会把藏起来的行也算进去，结果虚高；SUBTOTAL 只算屏幕上看得见的，是筛选状态下的正确答案。

=SUBTOTAL(109, B2:B100)

① IF｜基本判断

简单描述：给表格立规矩——业绩满一万写「达标」，不满写「未达标」。所有自动化判断，都从它开始。

=IF(B2>=10000, "达标", "未达标")

② IFS｜多档评级

简单描述：好几个 IF 套在一起嫌乱，就用 IFS 平铺。从左往右看，先满足先停，写奖金档位、评级最清楚。

=IFS(A2>90, "优", A2>80, "良", TRUE, "差")

③ IFERROR｜错误值清洗

简单描述：除不出来、查不到的时候，Excel 会甩你一脸 #DIV/0!。套上它，出错就显示你指定的 0 或空白，报表立刻体面。

=IFERROR(A2/B2, 0)

④ SWITCH｜按值对号

简单描述：点名式替换——见到 1 喊周一，见到 2 喊周二，都不认识就喊「未知」。比 IF 一层层套省事。

=SWITCH(A2, 1, "周一", 2, "周二", "未知")

① VLOOKUP｜基础匹配

简单描述：按姓名找工资。注意第 4 参数要写 0（精确匹配），不写容易返回莫名其妙的答案，这是新手第一大坑。

=VLOOKUP(E2, A:C, 3, 0)

② XLOOKUP｜终极查找

简单描述：不用再数「找的在第几列」，选查找列、选返回列就行，还能顺手定一句「查无此人」。有新版本就用它。

=XLOOKUP(E2, A:A, B:B, "查无此人")

③ INDEX＋MATCH｜交叉查询

简单描述：MATCH 负责找到它在第几行，INDEX 负责把那行的货取出来。俩人搭伙，反向查找、双向交叉查都拿手。

=INDEX(C:C, MATCH(E2, A:A, 0))

④ LOOKUP｜查最后一次

简单描述：想找某个客户「最后一次」的成交价？普通查找只会给第一个，这条经典写法专治最后一次出现。

=LOOKUP(1, 0/(A:A=E2), B:B)

① TRIM｜去空格

简单描述：从网页、系统导出的名字后面总藏着看不见的空格，VLOOKUP 就此失灵。TRIM 清掉首尾空格，是数据清洗第一步。

=TRIM(A2)

② TEXTJOIN｜强力拼接

简单描述：把一列名字连成一句话，中间用顿号隔开，空格子自动跳过。做名单汇总、关键词合集必备。

=TEXTJOIN("、", TRUE, A2:A20)

③ FIND｜智能定位

简单描述：告诉你某个字符排在第几位。想提邮箱里 @ 前面的用户名？先让 FIND 找到 @ 的位置，再交给 MID 去取。

=FIND("@", A2)

④ MID｜中段截取

简单描述：从第几位开始、取几位。身份证第 7 位往后 8 位就是生日，一条公式提出来。

=MID(A2, 7, 8)

① WORKDAY｜项目截止日

简单描述：项目今天开始、工期 10 个工作日，几号交货？它自动跳过周末，还能把节假日表放进去一起排。

=WORKDAY(TODAY(), 10)

② NETWORKDAYS｜净工作日

简单描述：两个日期之间实际上几天班？算工资、算出勤、算工期，它自动扣掉周六日。

=NETWORKDAYS(A2, B2)

③ DATEDIF｜整年整月

简单描述：算年龄、算工龄最准的办法。有意思的是，它在函数列表里搜不到，得手动敲全名，是个「隐藏函数」，但一直好用。

=DATEDIF(A2, TODAY(), "Y")

④ EOMONTH｜账期计算

简单描述：下个月最后一天是几号？财务结账、合同到期日，全靠它一把算出，不用再翻日历。

=EOMONTH(A2, 1)

① UNIQUE｜智能去重

简单描述：以前去重要「复制—粘贴—删除重复项」三步，现在一条公式一步到位，源数据变了名单还自动更新。

=UNIQUE(A2:A100)

② FILTER｜万能筛选

简单描述：把研发部的整行记录都筛到新区域。跟手动筛选的区别是：它是个活公式，源数据一动，结果跟着动。做动态看板的核心。

=FILTER(A2:C20, B2:B20="研发部")

③ SORT｜自动排序

简单描述：让数据永远保持从大到小。套在 FILTER 外面，就是一张自动刷新的销售排行榜。

=SORT(A2:B20, 2, -1)

④ VSTACK｜堆叠合并

简单描述：一月、二月的表各在各的工作表里？竖着摞成一张总表，复制粘贴党可以退休了。

=VSTACK(一月!A2:C10, 二月!A2:C10)

💡 本章 4 个都是新函数，需要 Excel 2021 或 Microsoft 365 才有；老版本会显示 #NAME?，先升级再谈感情。

① INDIRECT｜动态引用

简单描述：单元格里写着「Sheet2」，它就真去 Sheet2 拿 B2 的数。把文字变成真地址，做二级下拉菜单的核心。

=INDIRECT(A2&"!B2")

② OFFSET｜偏移引用

简单描述：圈一个会自动长高的区域——数据加一行，它就跟着大一号。动态图表、动态名称的首选方案。

=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

③ TRANSPOSE｜行列转置

简单描述：横排变竖排、竖排变横排。打印要竖版、入库要长表，一条公式搞定，不用粘贴选择性转置了。

=TRANSPOSE(A1:E1)

④ HYPERLINK｜超链接导航

简单描述：做个目录页，点一下就跳到对应工作表或文件。表多了以后，它是导航神器。

=HYPERLINK("#Sheet2!A1", "查看详情")

① TEXT｜格式化

简单描述：Excel 里的整容医生——只改显示的样貌，不改数字本身。把日期变成「星期三」，或把编号补成 001 样式。

=TEXT(A2, "aaaa")

② SUBSTITUTE｜定点替换

简单描述：按「内容」替换，不像 REPLACE 按「位置」换。把地址里的空格全删掉、把旧年份批量换新，都找它。

=SUBSTITUTE(A2, " ", "")

③ MOD｜循环与余数

简单描述：取余数。行号除以 2 轮流得 0 和 1，配合条件格式就是漂亮的斑马纹表格；判奇偶、算工时零头也用它。

=MOD(ROW(), 2)

④ ROUND｜精确舍入

简单描述：和「设置单元格格式」不一样——那个只是看着像两位小数，ROUND 是真的把数值四舍五入，防汇总时差一分钱。

=ROUND(A2*B2, 2)

① LARGE／SMALL｜极值分析

简单描述：比 MAX 灵活，想要第几大就写几。要「前 3 名销售额求和」，在外面套个 SUM 就行。

=LARGE(B:B, 3)

② LEN｜长度校验

简单描述：数一数有几个字。手机号该 11 位、身份证该 18 位，错一位马上现形，数据校验第一步。

=LEN(A2)

③ ROW／COLUMN｜自动编号

简单描述：用行号减 1 造序号，之后不管怎么删行、排序，序号永远是 1、2、3 顺下去，不用手改。

=ROW()-1

④ COUNTIF｜存在性校验

简单描述：核对两个表，不用知道有几个，只要知道有没有——数出来大于 0 就是「有」，否则「缺」，两表核对最经典的用法。

=IF(COUNTIF(A:A, B2)>0, "有", "缺")

「收藏不是学会，敲一遍才是。」