热点 来源:IT之家 2023-05-15 12:52 阅读量:10756
原文标题:《看到他这么用 MOD 函数,我真的服了……》
隔壁小王、小爽和我是好朋友,我们经常一起讨论 Excel 相关的问题。
某一天,小爽指着桌上的苹果,问我和小王:
如果把桌子上的 12 个苹果分给 5 个小朋友,还剩下几个呢?
小王立马说:不就是求余吗!一个 MOD 函数就可以搞定!公式如下。
MOD=2
MOD 函数看起来很基础很简单,其实深藏不露!
用 MOD 函数,还可以解决这么多常见问题:
? 从日期中提取时间
? 计算跨天工作时长
? 根据身份证号判断性别
? 根据日期判断周
? 根据条件填充颜色
……
是不是超想学?
在正式开学之前,我们先来看看 MOD 函数的语法规则。
毕竟知己知彼百战百胜!
MOD 函数是一个求余函数,它可以返回两数相除的余数。
不过它跟数学意义上的余数有所不同,数学意义上的余数是一个非负数,而这个函数求出来的余数可以是负数,并且符号与除数的符号相同。
MOD 函数的语法:
=MOD
也就是:=MOD
注:除数不能为 0,至于为什么…… 问你的小学老师去!
1、用 MOD 函数计算时间▌从日期中提取时间
下图中,我们需要提取 B 列日期中的时间。
在 C3 单元格输入公式:
=MOD
然后下拉填充,就搞定啦!
在 Excel 中日期的本质是数值。日期为整数部分,时间为小数部分。
比如在单元格输入「2020/4/20 12:00」,把单元格格式改为常规,就能得到数字 43941.5。
43941 是日期部分,0.5 是时间部分。
当我们用 MOD 函数求余数:
=MOD
得到结果为 0.5,把该单元格设置为时间格式,就得到 12:00,也就是时间部分。
敲重点:
公式:=MOD
用 MOD 函数第二参数为 1 时,求得的日期时间的余数,结果就是日期中的时间。
▌计算跨天时长
如下图,根据上下班时间,计算工作了多少小时。
在 D3 单元格输入公式:
=MOD*24
当天的 23 点~24 点+ 次日 0 点~9 点(9 小时)=10 小时。所以结果为 10 小时。
我们来看看用 MOD 函数是怎么来的。
前面我们得知:MOD 函数可以得到日期时间中的时间,那它同样可以得到时间间隔中的时间。
公式为:
=MOD
用 MOD 函数公式:
=MOD*24
=MOD 求的是两者之间相差的时间,是一个小数。
因为 1 天 = 24 小时,所以我们需要在后面乘以 24,将其转换为小时。
2、用 MOD 函数判断性别
如下图,怎么根据身份证号得知性别。
在 C3 单元格输入公式:
=IF,2),"男","女")
然后下拉填充。
身份证第 17 位数字为奇数表示为男,偶数表示为女。
小解析:
先用 MID 函数提取表示性别的第 17 位数字;
再用 MOD 函数判断数字的奇偶:
数字除以 2 得到余数是 0,则为偶数;如果余数是 1,则为奇数;
最后用 IF 函数判断若为奇数,则为男,否则为女。
3、用 MOD 函数判断周末
判断下图中 B 列的日期是否是周末,若是周六日则返回「是」,否则返回空字符。
在 C3 单元格输入公式:
=IFMOD2,"是",""
然后下拉填充。
首先我们列出一组连续日期,如下图 B 列;
然后用 MOD 函数,日期除以 7 得到余数是一组 0 到 6 循环的整数,如下图 C 列。
把 B 列日期格式自定义设置为「aaa」,显示日期星期几,如 D 列。
可以看出,数字 0-6 依次对应周六、周日、周一、周二、周三、周四、周五;
=IFMOD2,"是",""
用 IF 函数判断循环数值是否小于 2,若小于 2则是周末,否则返回空字符。
4、用 MOD 函数填充颜色
这个案例有点难,会涉及到数组公式,大家作为一个了解即可。
如下图,当我们美化表格时,可能需要把相同部门隔行用颜色填充:
如果一个个手动去设置颜色,只有几个部门倒还好。
如果部门多、表格多,这样操作很低效。
其实不用这么繁琐,请看下面操作!
操作重点是条件格式公式:
=MOD),2)
N
小解析:
? 判断部门所在行的下一行与上行是否相同,如果不同就累计 1 次;N 函数将逻辑值 True 或 False 转化成数值 1 或 0;
? 再用 SUM 函数累加求和;
? 最后用 Mod 函数对累计的和判断奇偶,若是奇数则填充颜色。
最后我转过头问小王他们:你们现在还认为 MOD 函数很简单嘛?
小王惊奇地说道:没想到小小的 MOD,看起来很简单,实际上真的不简单啊!
5、总结
?当 MOD 函数的除数是 1 时,可以在日期和时间同时存在的时候提取时间,可以计算跨日时间差;
?当 MOD 函数的除数是 2 时,可以判断数字奇偶;
?当 MOD 函数的除数是 7 时,能计算判断日期的星期。
当然 MOD 函数除了以上作用,它的用途远远不止这些~
郑重声明:此文内容为本网站转载企业宣传资讯,目的在于传播更多信息,与本站立场无关。仅供读者参考,并请自行核实相关内容。