原文标题:《看到他这么用 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 函数除了以上作用，它的用途远远不止这些~ 郑重声明：此文内容为本网站转载企业宣传资讯，目的在于传播更多信息，与本站立场无关。仅供读者参考，并请自行核实相关内容。 |
