做了3年运营才发现,Excel这7个数学函数才是真·摸鱼神器
- 2026-09-24 15:30:47
大家好，我是沈未迟。
还记得刚转运营岗那会儿，每天被各种报表折腾到怀疑人生。工资表的个税算到眼瞎，库存表的尾数对到崩溃，最离谱的是有一次做活动预算表，因为四舍五入的问题，最后合计差了2分钱，被财务退回来三次。
那时候我天真地以为，Excel里的数学函数嘛，不就是SUM、AVERAGE那几个？直到后来跟财务姐姐学了几招，才发现自己以前简直是拿着金饭碗要饭——Excel的数学函数家族里，藏着一堆能让你早下班两小时的神器。
今天这篇，我把自己运营岗这几年用得最多、踩坑最惨的7个数学函数掏出来给你们唠唠。每个函数都配真实工作场景和我当年踩过的坑，看完你会发现：原来以前加的班，全是因为没认识它们。
— — — — — — — — — —
一、ROUND三兄弟：四舍五入的水，比你想的深
语法说明
三兄弟长得很像，参数都是两个：数值和保留位数。
●`ROUND(数值, 小数位数)` —— 标准四舍五入
●`ROUNDUP(数值, 小数位数)` —— 向上舍入（只要后面有数就进一位）
●`ROUNDDOWN(数值, 小数位数)` —— 向下舍入（直接砍掉后面的）
真实工作场景
场景1：算员工提成
运营岗算提成是家常便饭。比如提成比例是3.5%，一单1299元，用`=ROUND(1299*3.5%, 2)`得到45.47元。如果用ROUNDUP就是45.47？不对，1299*0.035=45.465，ROUNDUP到2位就是45.47，ROUNDDOWN就是45.46。
别小看这1分钱，人多了就是大事。
场景2：做活动预算砍零头
老板说预算往多了算，留余量。这时候就用ROUNDUP，比如一项费用是8732元，`=ROUNDUP(8732, -3)`直接得到9000，往千位上进一位。
我当年踩过的坑
说出来都是泪。刚入行做工资表，我直接在Excel里输公式`=A1*0.03`，然后把单元格格式设成保留2位小数。结果月底对账，总金额怎么都对不上，差了几块钱。
我查了一下午才发现——单元格显示2位小数，不代表实际值就是2位！ 看起来是45.47，实际存的可能是45.465，加总的时候是按精确值加的，和你看到的完全是两码事。
后来财务姐姐教我，涉及金额的计算，一定要用ROUND套一层，别偷懒只改显示格式。不然差个几分钱，对账能对到你怀疑人生。
— — — — — — — — — —
二、INT：取整不是你想的那么简单
语法说明
`INT(数值)` —— 向下取整到最接近的整数。
注意关键词：向下。正数还好说，INT(3.9)=3，INT(3.1)=3。但负数就有意思了：INT(-3.1)=-4，因为-4比-3.1更小。
真实工作场景
场景1：计算员工工龄
入职日期到今天多少年了？`=INT((TODAY()-入职日期)/365)`，直接得到整数年数，不满一年的自动忽略，很符合我们算工龄工资的逻辑。
场景2：批量拆分数据
比如有一列订单号，前4位是地区编码，后面是流水号。用INT配合LEFT可以提取，但其实INT更多用在"取整分组"上——比如第1-10行是第一组，11-20是第二组，`=INT((行号-1)/10)+1`直接算出组号。
我当年踩过的坑
有一次算负增长的数据，我用INT对负数取整，结果跟预期差了1。比如增长率是-5.3%，我想取整成-5，结果INT给了我-6。
那时候才明白，INT的"取整"是"往更小的方向取"，不是"去掉小数部分"。如果只是想简单粗暴地去掉小数，应该用我们后面要说的TRUNC。
— — — — — — — — — —
三、MOD：最被低估的函数，没有之一
语法说明
`MOD(被除数, 除数)` —— 返回两数相除的余数。
看起来很简单对不对？小学数学嘛。但这个函数的妙用，多到你不敢信。
真实工作场景
场景1：判断奇偶
`=MOD(行号(), 2)`，结果是1就是奇数行，是0就是偶数行。配合条件格式就是隔行着色，眼睛看表格再也不酸了。
场景2：每N行汇总一次
比如每3行算一次小计，`=IF(MOD(行号(), 3)=0, SUM(上面3行), "")`，直接自动生成小计行。做月度报表、季度汇总的时候巨好用。
场景3：提取身份证校验位
这个就更高级了，18位身份证最后一位是校验码，用MOD配合一堆加权计算可以验证身份证号是否合法。当然这个用得少，但知道了可以装逼。
我当年踩过的坑
第一次用MOD做隔行着色的时候，我写的是`=MOD(ROW(), 2)=1`，结果插入或删除行之后，颜色全乱了。因为行号变了呀！
后来学聪明了，如果是固定数据，用完之后可以把格式粘贴成值；如果是动态数据，就用表格样式（Ctrl+T），自带的隔行样式更稳定。
还有一个坑：MOD的结果符号和除数一致，不是和被除数一致。比如MOD(-7, 2)=1，不是-1。这个细节在处理负数的时候容易翻车。
— — — — — — — — — —
四、ABS：简单但救命的函数
语法说明
`ABS(数值)` —— 返回绝对值。
这可能是Excel里最简单的函数之一了，但你别小看它，关键时刻能救大命。
真实工作场景
场景1：算偏差率
做运营的经常要对比实际值和目标值的差距。比如目标100万，实际完成95万，偏差率是多少？`=ABS(实际-目标)/目标`，用ABS就不用考虑谁减谁了，结果都是正的5%。
场景2：数据核对找差异
两列数据对账，用`=ABS(A1-B1)`一拉，结果大于0的就是对不上的行，配合筛选直接定位问题。
场景3：计算距离/天数差
两个日期之间差多少天？`=ABS(日期1-日期2)`，不用管哪个在前哪个在后。
我当年踩过的坑
说出来有点好笑，我以前居然不知道有ABS这个函数。算偏差率的时候，我是用IF判断的：`=IF(A1>B1, (A1-B1)/B1, (B1-A1)/B1)`，写得又臭又长。
直到有天同事路过我工位，瞟了一眼屏幕，默默给我改成了ABS版，然后飘走了。留下我一个人在原地石化。
所以啊，简单的函数不一定没用，只是你还没用到刀刃上。
— — — — — — — — — —
五、SUMPRODUCT：超级全能王，一个顶十个
语法说明
`SUMPRODUCT(数组1, 数组2, ...)` —— 将多个数组对应元素相乘，然后求和。
听起来平平无奇是不是？但它的真正实力在于——它天生支持数组运算，还能当条件求和、条件计数用。
真实工作场景
场景1：多条件求和
比如想算"华东地区+美妆品类"的销售额总和：
`=SUMPRODUCT((地区列="华东")*(品类列="美妆")*销售额列)`
对，你没看错，直接把条件写进去乘起来就行，比SUMIFS还灵活。
场景2：多条件计数
算"华东地区+美妆品类+金额大于1000"的订单数：
`=SUMPRODUCT((地区列="华东")*(品类列="美妆")*(金额列>1000))`
COUNTIFS能干的它都能干，而且支持数组运算，更灵活。
场景3：加权平均
算加权平均得分：`=SUMPRODUCT(分数列, 权重列)/SUM(权重列)`，一步到位。
我当年踩过的坑
SUMPRODUCT的坑可太多了，说两个最经典的：
坑1：整列引用会卡死
我一开始图省事，直接写`=SUMPRODUCT((A:A="华东")*(B:B="美妆")*C:C)`，结果Excel直接原地转圈，十几秒才算出来。后来才知道，整列引用就是自找麻烦，尽量用具体的区域，比如A2:A1000。
坑2：有空值会出错
如果引用的区域里有空单元格，SUMPRODUCT会把它当0，条件判断的时候没事，但如果直接乘数值列，空单元格不影响。真正的坑是——如果区域里有文字，直接#VALUE!报错。所以用之前最好确认一下数据区域都是数字。
— — — — — — — — — —
六、CEILING/FLOOR：取整到指定倍数的神器
语法说明
●`CEILING(数值, 倍数)` —— 向上取整到指定倍数
●`FLOOR(数值, 倍数)` —— 向下取整到指定倍数
ROUND三兄弟是按小数位数取整，这俩是按"倍数"取整，完全不是一个路数。
真实工作场景
场景1：算运费
快递首重1公斤8块，续重每公斤5块，不足1公斤按1公斤算。包裹重3.2公斤，运费多少？
`=8 + CEILING(3.2-1, 1)*5` = 8 + 3*5 = 23块。CEILING直接帮你把零头往上凑整。
场景2：排班表安排人数
每个班组5个人，来了23个员工，需要几个班组？`=CEILING(23, 5)/5` = 5个班组。
场景3：促销定价凑整
商品原价87块，想往上调到9的倍数（89、99这种），`=CEILING(87, 10)-1`得到89。FLOOR的话就是往下凑，比如往9.9结尾的价格靠。
我当年踩过的坑
第一次用CEILING的时候，我把参数顺序搞反了，写成`=CEILING(5, 3.2)`，结果得到5，还纳闷怎么不对。
记住口诀：第一个是数，第二个是倍数。CEILING(数字, 要凑到几的倍数)。
还有一个坑：CEILING和FLOOR的第二个参数不能是0，不然直接#DIV/0!。虽然正常不会犯这个错，但如果第二个参数是引用的单元格，万一那个单元格是空的或填了0，就翻车了。
— — — — — — — — — —
七、TRUNC：截尾取整，和ROUND到底有啥区别？
语法说明
`TRUNC(数值, 小数位数)` —— 直接截取指定小数位数，后面的通通砍掉，不做四舍五入。
ROUND是四舍五入，TRUNC是直接截尾巴。正数的时候，TRUNC和ROUNDDOWN效果一样；但负数的时候，TRUNC是往0的方向截，ROUNDDOWN是往更小的方向。
真实工作场景
场景1：提取日期中的年月日
日期在Excel里本质是数字，整数部分是日期，小数部分是时间。用`=TRUNC(日期单元格)`就能把时间去掉，只保留日期。
这个比用TEXT转文本好用多了，因为转完还是日期格式，可以继续参与计算。
场景2：去掉金额的分位
有些财务场景不需要分，直接保留到角，`=TRUNC(金额, 1)`，直接砍掉分位，不四舍五入。
场景3：提取数字的整数部分
不管正负，只要整数部分，用TRUNC最安全。TRUNC(-3.9) = -3，而INT(-3.9) = -4，区别就在这。
我当年踩过的坑
当年做数据清洗，有一列混合了日期和时间，我想提取日期，用了INT函数。结果数据里有早上的时间、晚上的时间，都还好。直到有一天出现了一个跨天的计算结果，是个负数日期，INT直接给我多减了一天，找了半天才发现问题。
后来统一换成TRUNC，世界就清净了。只要是截尾，不管正负，用TRUNC就对了。
— — — — — — — — — —
组合实战：1+1>10的神级用法
单个函数已经很能打了，但真正的高手都是组合出招。给你们分享两个我日常用得最多的组合拳。
组合1：MOD + 条件格式 = 隔行着色
这个太经典了，必须放第一个。
操作步骤：
选中你的数据区域
开始 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格
输入公式：`=MOD(ROW(), 2)=0`
设置填充颜色，确定
效果：所有偶数行自动变色，看数据再也不串行。奇数行的话就把0改成1。
进阶版：隔两行着色，改成`=MOD(ROW(), 3)=0`就行，想隔几行改数字。
组合2：SUMPRODUCT + MOD = 奇偶行分别求和
这个是进阶玩法。比如你有一列数据，奇数行是收入、偶数行是支出，想分别算总收入和总支出：
●奇数行求和：`=SUMPRODUCT((MOD(ROW(A1:A100), 2)=1)*A1:A100)`
●偶数行求和：`=SUMPRODUCT((MOD(ROW(A1:A100), 2)=0)*A1:A100)`
我做考勤表的时候经常用这个——姓名行和数据行交替排列，用这个公式直接跳过姓名行求和，不用手动选区域。
组合3：CEILING + 日期计算 = 月末/周末对齐
比如你想知道某个日期所在月的最后一天是几号，可以用：
`=CEILING(A1, 30)`？不对，月份天数不一样。
正确姿势：`=EOMONTH(A1, 0)`，这个是专门的函数。但如果没有这个函数的版本，可以用CEILING配合DATE玩出花来。
说个更实用的：计算某个日期之后的第一个周五：
`=CEILING(A1-5, 7)+5`
原理是把日期往前挪5天（因为周五是一周的第5天，假设周日是第1天），然后向上取整到7的倍数，再加回来。这个技巧在做周报、排期的时候特别好用。
— — — — — — — — — —
速记口诀 + 新手避坑指南
一、速记口诀
ROUND三兄弟：
●ROUND标准四舍五入，ROUNDUP往上顶，ROUNDDOWN往下砍
●正数好区分，负数看方向
INT vs TRUNC：
●INT向下取，TRUNC截尾巴
●正数都一样，负数差得大
MOD小能手：
●余数函数用处大，奇偶判断全靠它
●隔行着色配条件，每N汇总也不怕
ABS小可爱：
●绝对值，最简单，正负差距全变正
●偏差核对离不了，简单函数大用处
SUMPRODUCT全能王：
●乘完再求和，条件直接写里头
●求和计数加乘都能干，就是别整列引用慢
CEILING/FLOOR凑倍数：
●往上凑用CEILING，往下凑用FLOOR
●参数顺序别搞反，数在前、倍在后
二、新手避坑指南（6条血泪教训）
1. 金额计算一定要套ROUND
别只改单元格显示格式，实际存储的值还是精确的，加总会差钱。财务退你报表事小，对账对到深夜事大。
2. 取整先想清楚：往哪个方向取？
●想四舍五入 → ROUND
●想往上凑整（预算、运费） → ROUNDUP / CEILING
●想往下砍零（不满不算） → ROUNDDOWN / FLOOR
●只想去小数（不管正负） → TRUNC
●算工龄、往下取整 → INT
选对函数比写10层IF强。
3. SUMPRODUCT别整列引用
A:A这种写法会让公式慢到怀疑人生，数据量大的话Excel直接假死。老老实实写A2:A1000，差不了几个字。
4. MOD的余数符号看除数，不看被除数
MOD(-7, 2) = 1，不是-1。这个细节90%的人第一次都会搞错，记一下少踩坑。
5. 日期时间处理优先用TRUNC，别用INT
日期跨天、负数日期的时候，INT会多算一天。TRUNC是真·截尾，安全可靠。
6. 函数嵌套先理清逻辑，从里往外写
复杂公式别一口气写完，先写最内层的，测对了再往外套。不然写错了， debugging能把你眼睛看瞎。
— — — — — — — — — —
写在最后
其实Excel的数学函数远不止这些，但说实话，日常工作中90%的场景，今天说的这7个就够用了。
我刚学Excel的时候，总觉得函数越多越厉害，到处搜集什么"100个常用函数"。后来慢慢发现，真正的高手不是会的函数多，而是能用最简单的函数组合出最厉害的效果。
MOD和条件格式一搭，就是隔行着色；SUMPRODUCT和MOD一配，就是奇偶行分别求和。这些东西说破了不值钱，但没人告诉你的话，你可能要走很多弯路才能摸到门道。
这也是我写「效率小本本」的初衷——把我踩过的坑、摸索出来的门道，都摊开给你们看。能让你们少加一点班，多留点时间给自己，这事儿就值。
好了，今天的内容就到这里。觉得有用的话，点个在看，分享给你身边那个还在跟Excel死磕的朋友。
咱们下期见~
「效率小本本」第18篇 · 沈未迟
