看到他这么用MOD函数,我真的服了…… |
您所在的位置:网站首页 › excel根据日期计算周数 › 看到他这么用MOD函数,我真的服了…… |
0
分享至
用微信扫码二维码 分享至好友和朋友圈 点击蓝字【秋叶 Excel】 发送【福利】 免费领 Excel 插件&工具,看精华文章! 本文作者:赵骄阳 本文编辑:竺兰 隔壁小王、小爽和我是好朋友,我们经常一起讨论 Excel 相关的问题。 某一天,小爽指着桌上的苹果,问我和小王: 如果把桌子上的 12 个苹果分给 5 个小朋友,还剩下几个呢? 小王立马说:不就是求余吗!一个 MOD 函数就可以搞定!公式如下。 MOD(12,5) =2 MOD 函数看起来很基础很简单,其实深藏不露! 用 MOD 函数,还可以解决这么多常见问题: ❶ 从日期中提取时间 ❷ 计算跨天工作时长 ❸ 根据身份证号判断性别 ❹ 根据日期判断周 ❺ 根据条件填充颜色 ……是不是超想学? 在正式开学之前,我们先来看看 MOD 函数的语法规则。 毕竟知己知彼百战百胜! MOD 函数是一个求余函数,它可以返回两数相除的余数。 不过它跟数学意义上的余数有所不同,数学意义上的余数是一个非负数,而这个函数求出来的余数可以是负数,并且符号与除数的符号相同。 MOD 函数的语法: =MOD(number,divisor) 也就是:=MOD(被除数,除数) 注:除数不能为 0,至于为什么……问你的小学老师去! 用 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) /阅读下一篇/ 返回网易首页 下载网易新闻客户端 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |