公众号文章|职场Excel实用函数第一期

职场Excel实用函数

职场里绝大多数Excel加班，都不是“不会做”，而是把同一件事，每做一次就从零再做一遍。手工筛选、手工复制粘贴、手工核对，看着快，其实每次都在重付一遍学费。

这一期只讲一件事：把这些重复动作交给公式。选5个最常被用错、也最容易见效的函数，讲清它们解决什么问题、怎么写、以及最容易踩的坑。

【本文解决的是“方法困惑”：知道该用哪个函数，比记住它的语法更重要。】

01 先分清你缺的是“找”还是“算”

打开一张表之前，先判断你正在做的动作属于哪一类。职场表格里的重复劳动，基本逃不出三种：

· 找——把另一张表里对应的值搬过来，比如用工号查姓名、用订单号查金额。

· 算——满足某几个条件才统计，比如“华东区、7月、A产品”的销售额合计。

· 判——按数值给出结论、把脏数据洗干净，比如按分数定等级、清掉单元格里的多余空格。

这三类动作，对应三组主力函数：查找函数、条件统计函数、逻辑与文本函数。下面5个公式，就是从这三组里挑出来的高频款。

一个前提：下面的写法以 Excel 2019 及以上、Microsoft 365 和较新版本 WPS 为准。如果你的版本里某个函数不存在，文中都给了兼容写法。

注释：公式真正读的，是“一片区域”，不是某一个格子。

02 SUMIFS：一次把所有条件都算完

最典型的手工活：明细表几万行，要按“片区+月份+产品”汇总。多数人的做法是先筛选、再选中可见单元格求和，做一次换一次条件。

条件求和函数一次能吃掉全部条件：

=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, …)

把它落到具体场景，大致长这样：

=SUMIFS(明细!$E:$E, 明细!$C:$C, $I2, 明细!$B:$B, ">="&$J$1, 明细!$B:$B, "<="&$K$1)

意思是：在明细的E列（金额）里求和，同时要求C列等于当前行指定的片区、B列落在J1到K1这一段日期区间内。

想统计的个数而不是金额，把SUMIFS换成COUNTIFS，参数结构完全一样。

这里有三个坑，几乎每个人都踩过一次：

· 求和区域和每个条件区域的行数必须一致。写成 E2:E1000 配 C:C，结果会错，还不会报错。

· 条件里的比较符要整体加引号，再和单元格用 & 连接。">=10000"、">="&$J$1 都是这个逻辑，直接写 >=10000 会被当成文本。

· 区域别写死。今天引用 A2:A500，下个月数据到第800行，新数据就全在范围外。稳妥做法是把明细转成“表格”（选中区域按 Ctrl+T），公式引用表格列名，新增行会自动纳入；或者一开始就用整列引用，代价是大文件计算变慢。

最后提醒一句：SUMIFS 默认对文本是“完全等于”，想模糊匹配要主动加通配符，比如 "*"&A2&"*"，表示包含A2这段文字。

03 XLOOKUP：把“查表”这件事一次做对

找值的场景太常见了：一张明细表，一张汇总表，要把工号对应的姓名、部门、金额搬到汇总表上。

新一代的查找函数是 XLOOKUP：

=XLOOKUP(查找值, 查找数组, 返回数组, 找不到时返回什么)

例如：=XLOOKUP($A2, 明细!$B:$B, 明细!$F:$F, "未匹配")

它比 VLOOKUP 好用的地方有四条：默认就是精确匹配，不用记得补第4个参数；查找列不必在最左边，往左查也行；找不到时可以自定义提示，而不是甩一个 #N/A；返回数组可以是整行，一次带出多列。

但 XLOOKUP 是较新版本才有的函数（Excel 2021 之后和 Microsoft 365）。在更老的版本里，用 INDEX + MATCH 替代，效果等价：

=INDEX(明细!$F:$F, MATCH($A2, 明细!$B:$B, 0))

如果你习惯 VLOOKUP，有三个硬约束必须记住：查找值必须落在查找区域的第一列；第几列是写死的数字，左边插一列就全错；第4个参数一定要写 0 或 FALSE，否则默认近似匹配，会给你一个“看起来对、其实错”的结果。

“近似匹配悄悄返回错误值”，是职场表格里最难发现的一类错误——它不报错，只是默默错了。

04 IF 与 IFS：把“分级判断”写成规则

“90分以上优秀、75分以上合格、其余待改进”，这种分级判断几乎每个考核表都要做。

最基础的写法是 IF 嵌套：

=IF(AND(C2>=90, D2<=3), "优秀", IF(C2>=75, "合格", "待改进"))

AND 表示“同时满足”，OR 表示“满足任意一个”。条件一多，嵌套就会变成括号地狱——三层以上基本只有写它的人看得懂，换个人接手就是灾难。

新版 Excel 提供了 IFS，从上往下判断，第一个成立的直接返回：

=IFS(C2>=90, "优秀", C2>=75, "合格", TRUE, "待改进")

两个细节：

· 顺序决定结果。IFS 不是“最匹配”，而是“第一个成立”。所以条件要从严到宽排，先写 90 再写 75。

· 记得兜底。所有条件都不成立时，IFS 会返回 #N/A。末尾加一对 TRUE, "其他"，相当于给 IF 写了 else。

如果老版本没有 IFS，就继续用 IF 嵌套，但把层级控制在三层以内；再多就该考虑换一种思路——用近似匹配的查找函数按区间取等级，比堆 IF 更好维护。

05 TEXT 与 TEXTJOIN：格式和拼接，别再靠手工改

两个高频小需求：把一串小数显示成两位；把多行内容合并成一句话。

先说格式。数字、日期在 Excel 里本质是“值”，显示成什么样由格式决定。公式里要控制显示，用 TEXT：

=TEXT(E2, "0.00")　　保留两位小数

=TEXT(E2, "yyyy-mm-dd")　　转成标准日期写法

再说拼接。想把一列里的多个姓名合成“张三、李四、王五”，用 TEXTJOIN：

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

第一个参数是分隔符，第二个参数 TRUE 表示忽略空单元格——这一步很关键，用 & 手工拼接时，空行会留下一串多余的顿号。

原始数据里常有一个“入职日期”，想算工龄，用 DATEDIF：

=DATEDIF(入职日期, TODAY(), "Y")

这个函数在函数列表里找不到，属于隐藏函数，但可以直接用，第三个参数 "Y" 表示整年。

一个必须记住的坑：TEXT 的结果是文本，不是数字。左边对齐、不能参与求和、排序会按字符顺序走。凡是需要继续计算的地方，不要用 TEXT 转格式，改单元格格式就行。

注释：省下来的不是时间，是每次重做时那份不确定。

06 IFERROR：让表格“干净”，但别把问题藏起来

查找类公式最怕 #N/A。把错误显示换掉，用 IFERROR 包一层：

=IFERROR(VLOOKUP($A2, 明细!$B:$F, 4, 0), "")

找不到就留空，表格立刻干净。但这里有一个很容易被忽略的副作用：IFERROR 会把所有错误一并吞掉，包括你写错区域、列号超界这类真正的错误。排查时先去掉 IFERROR，看清原始报错，再决定要不要包。

另一样让表格“不干净”的，是看不见的空格。从系统导出的数据，经常带着首尾空格、不可见字符、英文全角混排。三个清洗函数够用了：

· TRIM　清掉首尾和多余的空格（只处理半角空格）。

· CLEAN　清掉不可打印的控制字符。

· SUBSTITUTE　定点替换，比如把全角空格换成空，或把“万”字去掉。

如果是“一列里塞了姓名和部门”这种结构问题，别用公式硬拆，用“数据—分列”更快；去重就用“删除重复值”。这两个操作会直接改原数据，做之前先复制一份备份，是职场里最便宜的一次保险。

07 一张表带走：什么需求配什么函数

把上面的内容压缩成一张“公式地图”。以后遇到同类问题，先在这里认领，再动手写：

· 要按多个条件求和　→　SUMIFS　　坑：区域等长、比较符加引号

· 要按多个条件数个数　→　COUNTIFS　　坑：同 SUMIFS，区域必须对齐

· 要把另一张表的值搬过来　→　XLOOKUP（老版本用 INDEX+MATCH）　　坑：找不到时的提示要自定义

· 要按区间给等级、给结论　→　IFS 或 IF 嵌套　　坑：顺序从严到宽、末尾要兜底

· 要控制数字和日期的显示　→　TEXT　　坑：结果是文本，不能再算

· 要把多行合成一句　→　TEXTJOIN　　坑：第二个参数记得写 TRUE

· 要让错误显示变干净　→　IFERROR　　坑：排查时先去掉它看原错误

· 要清掉空格和脏字符　→　TRIM / CLEAN / SUBSTITUTE　　坑：TRIM 管不了全角空格

如果只从这一期带走三句话，那就是：先判断动作属于“找、算、判”哪一类；条件写全，别靠人脑补；区域别写死，给数据留出增长的空间。

函数的价值不在多，而在稳定——同一套口径，这个月和下个月算出来的结果必须能对上。这也是下一期要接着讲的事：数据透视表和图表，怎么把已经算好的结果，变成别人一眼能看懂的东西。

你可以在评论区说说，自己最常被哪一类表格卡住。下一期会按留言最多的场景来写。