看到他这么用MOD函数,我真的服了…… (⊙o⊙)

作者:赵骄阳

编辑:脸脸、尽头、久久酱

隔壁小王、小爽和我是好朋友,我们经常一起讨论 Excel 相关的问题。
某一天,小爽指着桌上的苹果,问我和小王:
如果把桌子上的 12 个苹果分给 5 个小朋友,还剩下几个呢?
小王立马说:不就是求余吗!一个 MOD 函数就可以搞定!公式如下。
MOD(12,5) =2
MOD 函数看起来很基础很简单,其实深藏不露!
用 MOD 函数,还可以解决这么多常见问题:
❶ 从日期中提取时间
❷ 计算跨天工作时长
❸ 根据身份证号判断性别
❹ 根据日期判断周
❺ 根据条件填充颜色
……
是不是超想学?
在正式开学之前,我们先来看看 MOD 函数的语法规则。
毕竟知己知彼百战百胜!
MOD 函数是一个求余函数,它可以返回两数相除的余数。
不过它跟数学意义上的余数有所不同,数学意义上的余数是一个非负数,而这个函数求出来的余数可以是负数,并且符号与除数的符号相同。
MOD 函数的语法:
=MOD(number,divisor)
也就是:=MOD(被除数,除数)
注:除数不能为 0,至于为什么……问你的小学老师去!

现在关注【秋叶 Excel】

回复关键词【模板】

即可免费领取 100 实用 Excel 图表模板

👇👇👇

秋叶Excel

和秋叶一起学Excel,免费获取Excel模板大全、插件合集!还有Excel真人视频、图文教程,助你轻松掌握Excel函数、图表、透视表……一起成为办公效率达人!
663篇原创内容
公众号
用 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 时,能计算判断日期的星期。
当然 MOD 函数除了以上作用,它的用途远远不止这些~
想真正学好 Excel,掌握高效学习方法,还需要系统学习,提升自己哟!
🎁
看完文章,同学们是不是感觉还没有学够?
没关系!为感谢同学们对秋叶 Excel 的陪伴和支持,我们专门为大家准备了 《3天Excel集训体验营》读者粉丝仅需1元秒杀~
扫码下方二维码报名后添加班班即可参与~
报名后你将获得3天Excel社群模式学习,各类福利资料干货分享发放,助力大家提升办公效率哦~

《秋叶Excel 3天集训营》

课程原价99 

当下限时1 元秒杀!!

仅需 3天,实操练习 名师带学

提升你的Excel能力,助你早下班!

报名即送【35个常用函数说明】,赶紧扫码预约吧!👇👇👇

🎁
在公众号【秋叶 Excel】
回复关键词【礼包
还能免费领办公神器
Office 模板和免商字体!
(0)

相关推荐

  • 利用mod函数根据年份计算生肖

    小伙伴们,大家好.今天利用mod函数做几个实例,有一个典型的案例就是根据年份计算生肖.在说实例之前,先来对昨天的文章补充说明一下.我认为昨天对mod的循环效果没有展示明白. 先来看几张图片的.下图A列 ...

  • 求余函数mod的基础用法

    同学们,大家好.今天继续学习基础函数的用法.今天要讲的是求余函数mod,它也是一个数学函数,返回两数相除的余数,结果的符号与除数相同. 先来看下它的语法结构: MOD(number, divisor) ...

  • mod函数中负数参数返回结果的规律总结

    小伙伴们,大家好.我们之前在mod函数中用的参数都是正数,今天来看看如果参数中有负数会返回什么样的结果.先来看个简单的例子,如下图所示,AB两列分别是被除数和除数,C列是返回的余数,D列是公式. 为了 ...

  • 4.10 mod函数参数为负数的用法

    4.10 mod函数参数为负数的用法

  • MOD函数,结合IF、SUM、条件格式等,解决3类针对性问题

    大家好,在数据统计汇总时,需要进行隔行或隔列的求和,报表美化时需要各行或各列的颜色填充:条件判定时,需要对奇偶性.余数大小等进行判定,这些操作都涉及到一个小小的函数,就是MOD函数,它常常和IF函数. ...

  • Excel常用函数之MOD函数

    在Excel中,MOD函数是用于返回两数相除的余数,什么是余数这个概念我想大家都知道,反正我永远记住了小学老师的那句话:被除数=商乘除数加余数.Excel利用MOD函数很容易可以得到余数,MOD函数的 ...

  • 关于求和的7个函数,真的都不赖,哪个才是你的菜?

    求和,再普通不过的话题了,但是在实际数据除了和分析中,还是会遇到各种各样的问题,如果你能掌握下文所述的方法,那对于求和将会迎刃而解. 一.普通求和--Sum 功能:计算指定区域中数值的和. 语法结构: ...

  • INT+MOD 函数 在excel中加班小时换算成加班天数

    取整:int(单元格):将单元格内容变为整数,不四舍五入,如果单元格内容为负数,则为向下取整求余数:mod(单元格,被除数)循环等差数列:如果想要1-5循环,即为:mod(单元格,5),此时单元格可以 ...

  • INT函数和MOD函数,有整有余?

    行云里讲堂(ID:xingyunli2022) 践行终身学习,专注个人提升. 整理编辑:枏北 函数MOD可以用函数INT函数表示:MOD(n,d)= n - d*INT(n/d). 一 INT函数 该 ...

  • 我真的服了!高人指点我如何背单词,4分30秒开始值得反复听8遍

    我真的服了!高人指点我如何背单词,4分30秒开始值得反复听8遍