看到MOD函数还能这么用，我真的绷不住了……

点击关注 【秋叶 Excel】

发送【7】

免费领 1000+ 篇 Excel 精选教程！

作者：赵骄阳

编辑：竺兰

大家好，这里是秋叶编辑部~

问你一个问题：

如果把 12 个苹果分给 5 个小朋友，还剩下几个呢？

这，就是求余！在 Excel 里，一个 MOD 函数就可以搞定！公式如下：

MOD(12,5) =2

MOD 函数看起来很基础很简单，其实深藏不露！

用它还可以解决这么多常见问题：

❶从日期中提取时间

❷计算跨天工作时长

❸根据身份证号判断性别

❹根据日期判断周

❺根据条件填充颜色

是不是超想学？

在正式学习之前，我们先来看看 MOD 函数的语法规则。

毕竟知己知彼百战百胜！

MOD 函数是一个求余函数，它可以返回两数相除的余数。

不过它跟数学意义上的余数有所不同，数学意义上的余数是一个非负数，而这个函数求出来的余数可以是负数，并且符号与除数的符号相同。

MOD 函数的语法：

=MOD(number,divisor)

也就是：

=MOD(被除数,除数)

注：除数不能为 0，至于为什么……问你的小学老师去！

什么，你想系统提升 Excel 技能？

那么千万别错过秋叶专为 0 基础小白打造的《Excel 3 天集训营》课程！

现在扫码报名，你还能免费领取函数手册+快捷键大全+110 套 Excel 模板！

别再犹豫了，赶紧扫码

抢占免费学习名额

助力自己在职场中脱颖而出吧！

用 MOD 函数计算时间

▌从日期中提取时间

下图中，我们需要提取 B 列日期中的时间。

在 C3 单元格输入公式：

=MOD(B3,1)

然后下拉填充，就搞定啦！

在 Excel 中日期的本质是数值。日期为整数部分，时间为小数部分。

比如在单元格输入「2020/4/20 12:00」，把单元格格式改为常规，就能得到数字 43941.5。

43941 是日期部分，0.5 是时间部分。

当我们用 MOD 函数求余数：

=MOD(43941.5,1)

得到结果为 0.5，把该单元格设置为时间格式，就得到 12:00，也就是时间部分。

敲重点：

公式：=MOD(日期时间,1)

用 MOD 函数第二参数为 1 时，求得的日期时间的余数，结果就是日期中的时间。

▌计算跨天时长

如下图，根据上下班时间，计算工作了多少小时。

在 D3 单元格输入公式：

=MOD(C3-B3,1)*24

当天的 23 点~24 点（1 小时）+ 次日 0 点~9 点（9 小时）=10 小时。所以结果为 10 小时。

我们来看看用 MOD 函数是怎么来的。

前面我们得知：MOD 函数可以得到日期时间中的时间，那它同样可以得到时间间隔中的时间。

公式为：

=MOD(日期时间,1)

用 MOD 函数公式：

=MOD(C3-B3,1)*24

=MOD(C3-B3,1)求的是两者之间相差的时间，是一个小数。

因为 1 天=24 小时，所以我们需要在后面乘以 24，将其转换为小时。

用 MOD 函数判断性别

如下图，怎么根据身份证号得知性别。

在 C3 单元格输入公式：

=IF(MOD(MID(B3,17,1),2),"男","女")

然后下拉填充。

身份证第 17 位数字为奇数表示为男，偶数表示为女。

小解析：

先用 MID 函数提取表示性别的第 17 位数字； 再用 MOD 函数判断数字的奇偶：数字除以 2 得到余数是 0，则为偶数；如果余数是 1，则为奇数； 最后用 IF 函数判断若为奇数，则为男，否则为女。

用 MOD 函数判断周末

判断下图中 B 列的日期是否是周末，若是周六日则返回「是」，否则返回空字符。

在 C3 单元格输入公式：

=IF(MOD(B3,7)<2,"是","")

然后下拉填充。

首先我们列出一组连续日期，如下图 B 列；

然后用 MOD 函数，日期除以 7 得到余数是一组 0 到 6 循环的整数，如下图 C 列。

把 B 列日期格式自定义设置为「aaa」，显示日期星期几，如 D 列。

可以看出，数字 0-6 依次对应周六、周日、周一、周二、周三、周四、周五；

=IF(MOD(B3,7)<2,"是","")

用 IF 函数判断循环数值是否小于 2，若小于 2（0 和 1）则是周末，否则返回空字符。

用 MOD 函数填充颜色

这个案例有点难，会涉及到数组公式，大家作为一个了解即可。

如下图，当我们美化表格时，可能需要把相同部门隔行用颜色填充：

如果一个个手动去设置颜色，只有几个部门倒还好。

如果部门多、表格多，这样操作很低效。

其实不用这么繁琐，请看下面操作！

操作重点是条件格式公式：

=MOD(SUM(N($B$2:$B2<>$B$3:$B3)),2)

N($B$2:$B2<>$B$3:$B3))

小解析：

❶ 判断部门所在行的下一行与上行是否相同，如果不同就累计 1 次；N 函数将逻辑值 True 或 False 转化成数值 1 或 0； ❷ 再用 SUM 函数累加求和； ❸ 最后用 Mod 函数对累计的和判断奇偶，若是奇数则填充颜色。

最后我转过头问小王他们：你们现在还认为 MOD 函数很简单嘛？

小王惊奇地说道：没想到小小的 MOD，看起来很简单，实际上真的不简单啊！

❶当 MOD 函数的除数是 1时，可以在日期和时间同时存在的时候提取时间，可以计算跨日时间差；

❷当 MOD 函数的除数是 2时，可以判断数字奇偶；

❸当 MOD 函数的除数是 7时，能计算判断日期的星期。

你学会了吗？

只要找到正确的方法，即使是零基础的小白，也可以掌握 Excel！

如果你想利用空闲时间提升 Excel 技能，让工作更高效，那么一定要加入秋叶 Excel 3 天集训营~

秋叶金牌讲师 @拉登Dony 手把手教你 Excel，现在加入，还能获得秋叶学员实战用的10 套精选表格模板：

先给大家看看部分截图 ↓

名额有限，学到就是赚到！

秋叶 Excel 3 天集训营

现在仅需 0 元！

长按下图扫码免费报名

▲ 现在报名，即可免费领取超多福利资料

点击关注【秋叶 Excel】

免费获取更多 Excel 干货、模板