ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
EH云课堂-专业的职场技能充电站 限时送,魔方网表将Excel变在线系统 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
楼主: delete_007

[Excel 函数与公式] [第123期]工作完成时间[已结]

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2018-6-23 11:15 | 显示全部楼层
本帖已被收录到知识树中,索引项:NOW
本帖最后由 micch 于 2018-6-23 11:22 编辑

厉害了,原来可以这样测试,测试老师辛苦,

似乎明白了又似乎不明白,一直把半点当0.5小时看,好像mod(,1)会把循环小数四舍五入,结果就完全不对了,不知道是不是这样??

这下看明白了,原来就是显示位数导致的
这就是浮点误差吗?一直不懂浮点误差什么情况会产生

TA的精华主题

TA的得分主题

发表于 2018-6-23 22:50 | 显示全部楼层
本帖最后由 丢丢表格 于 2018-6-26 08:21 编辑

这个题的思路是:   由于开始时不一定,相对计算的条件较为复杂,
        如: 9:00  以前开始的,它开始计算工作时 就是 9:00
                9:00   至 12:00  的 ,就须按实时计算工作时间
                12:00 至 13:00  ,要以 13:00 开始计算工作时间
                13:00 至  18:00 的 要实时计算工作时间
                18:00 至 次日 9:00 的 这段时间内不能计算工作时间
                上面的条件是不是太乱了,为了简化条件, 我们统一到 9:00 开始计算工作时间
               那么 就要计算 开始时点 到 早上的 9:00 已有多少工作时间
                  公式写法: SUM(TEXT(MOD(A2,1)*24-{9,13;12,18},"[<]!0")*{1;-1})


       以上的工作时间加上B2的工作时间 ,看这个时长 有多少个8 小时,即有多少天 ,每天是8 个小时的工作时间 还须加上 1小时的午休和 下班的 15 小时 (18:00 至 次日的9:00)
         公式: =SUM(TRUNC((SUM(TEXT(MOD(A2,1)*24-{9,13;12,18},"[<]!0")*{1;-1})+B2+{5,0,0})/{8,8,1},{0,0,5})*{1,15,1})/24
         上面计算出来的是 含休息时间的总时长,再加上 开工起的 9点, 再加上开始时的日期,就是结束日期和时点。
  公式
         =SUM(TRUNC((SUM(TEXT(MOD(A2,1)*24-{9,13;12,18},"[<]!0")*{1;-1})+B2+{5,0,9})/{8,8,1},{0,0,5})*{1,15,1})/24+INT(A2)   

整理公式为
=SUM(TRUNC((MEDIAN(MOD(A2,1)*24-{9,10},,3,8)+B2+{5,0,9})/{8,8,1},{0,0,5})*{1,15,1})/24+INT(A2)

        以上是我个人的解题思路,供大家参考,有不当之处请指正。






评分

参与人数 9鲜花 +20 收起 理由
@木头人 + 2 太强大了
0星剑0 + 2
rssoo + 2 太强大了
Excel实战分享 + 2 厉觉明不
sucx007 + 2 太强大了

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-6-24 08:56 | 显示全部楼层
14楼=SMALL(******,B2*60+1)这个破公式,测试半天,才发现B2*60+1这部分有问题,导致算这组数据少一分钟
2018-5-19 1:42        32.3
32.3*60+1,不管F9还是公式求值,都看不出错误,直接输入1939结果是对的,看来是32.3*60+1在作为small第2参数截尾取整时少了1,要结果正确还得加round....
这是F9都看不到的浮点误差么???

评分

参与人数 1鲜花 +2 收起 理由
@木头人 + 2 感谢帮助

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-6-24 11:19 | 显示全部楼层
xzk235 发表于 2018-6-24 08:56
14楼=SMALL(******,B2*60+1)这个破公式,测试半天,才发现B2*60+1这部分有问题,导致算这组数据少一分钟
2 ...

你这种计算方 法 就是时分堆积法,有时长的限制,且计算量非常大 并有浮点计算的风险 。对短工作时长计算尚可。

评分

参与人数 1鲜花 +2 收起 理由
@木头人 + 2 感谢帮助

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-6-25 22:39 | 显示全部楼层
正确的公式竟然不到1/3,这可能跟模拟数据不够完善也有一定影响,毕竟参与的人员不多,建议还给予一定的财富以示鼓励吧。

TA的精华主题

TA的得分主题

发表于 2018-6-28 10:23 | 显示全部楼层
好吧,贴公式的时候忘了把100改成1000了,冤死了T_T

TA的精华主题

TA的得分主题

发表于 2018-6-30 14:11 | 显示全部楼层
xzk235 发表于 2018-6-24 08:56
14楼=SMALL(******,B2*60+1)这个破公式,测试半天,才发现B2*60+1这部分有问题,导致算这组数据少一分钟
2 ...

谢谢老师的指正,我会继续学习。

TA的精华主题

TA的得分主题

发表于 2018-6-30 14:14 | 显示全部楼层
丢丢表格 发表于 2018-6-24 11:19
你这种计算方 法 就是时分堆积法,有时长的限制,且计算量非常大 并有浮点计算的风险 。对短工作时长计算 ...

谢谢老师的点评,上次也是老师帮忙指出错误的公式,我会吸取不足,多多学习老师们的思维方法。

TA的精华主题

TA的得分主题

发表于 2018-7-2 10:52 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-7-3 16:45 | 显示全部楼层
nsxlyj 发表于 2018-7-2 10:52
期待下一次的题目难度小一些

此题的难度应该是不大的。
=INT(A2)+SUM(TRUNC((MEDIAN(MOD(A2,1)*24-{9,10},,3,8)+B2+{5,0,9})/{8,8,1},{0,0,5})*{1,15,1})/24

你看 这个题 尽是计算数字 。也就是计算关系题。



您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-3-21 01:49 , Processed in 1.125730 second(s), 25 queries , Gzip On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表