包含起止时间的随机时间函数公式的完善

tgydslr

事情起源于一个论坛求助，http://www.excelpx.com/thread-323564-1-4.html
构建一个时间差为两分钟的随机函数，比如11:03-11:05,12:59-13:01……
参与了回帖，

1. =TEXT((TEXT(RANDBETWEEN(0,23),"00")&TEXT(RANDBETWEEN(0,59),"00"))*10001+2,"00!:00!-00!:00")
   

复制代码

1. =TEXT((TEXT(RAND()*23,"00")&TEXT(RAND()*59,"00"))*10001+2,"00!:00!-00!:00")

复制代码

1. =TEXT(SUBSTITUTE(TEXT(RAND(),"hh:mm"),":","")*10001-20000,"00!:00-00!:00")

复制代码

，公式有了，但是容易出现23:59-23:61或者13:98-14:00一样的错误或者这样的错误


其实就是说没有解决随机中的进位问题（60进制）
而后顺版主的解决方案也不是很符合题意

1. =TEXT(SUM((RANDBETWEEN(1,24)/1%+(RANDBETWEEN(1,57)+{0,2}))*{10000,1}),"00!:00-00!:00")

复制代码

从公式上进位不存在了，但是前面的时间倒是只能能够到57分了，无法写出15:58-16:00的随机时间。
今天闲来没事，倒想起来这个问题的一个解决办法
（1）注意两个时间相差两分钟，所以考虑用tex构建成A-A+2（A为时间）型（比如13:58-14:00）数组。但考虑随机函数的特点，两部分只能有一个随机函数生成，还涉及进位问题。
（2）为了达到上面的目标，观察到1分钟换乘数字就是1/1440=0.0006944……，第一个有效数字在小数点后四位，所以考虑用五位小数构建第一个时间，后面的时间加上2/1440(保留5位）即可，为此，找到这样一个类型，

1. RANDBETWEEN(0,99999)*(10^5+1)+INT(200000/1440)

复制代码

，这样可以生成12345,12483（换乘5位数字的话，2分钟就是138）,型的数字，这样其前四位除以100000，后四位除以100000，得出两个时间，两个时间差正好为2分钟（换成5为整数的话，差约为138）。然后就可以把两个时间连起来，然后用text整合了。但是当前一位数字随机成99999的话后面的加14（99999&99999+138就该考虑进位了）就不是5位了，为此
(3)为了解决上面的问题，对上述公式微调一下

1. =RANDBETWEEN(0,99999)*(10^6+1)+INT(20000/1440)

复制代码

这样的话整个数就成11位了，后面的进位也不涉及前面五位数，并且后面6位在前面进位后除以100000，就是1.ABCDE与0.ABCDE所表示的时间是一样的（只是日期部分不一样），但这不影响结果，这样就解决了进位问题。
（4）构造公式如下

1. =TEXT(SUM(TEXT(MID(TEXT(RANDBETWEEN(0,99999)*(10^6+1)+INT(2*10^5/1440),REPT(0,11)),{1,7},5)/10^5,"hhmm")*{10000,1}),"00!:00-00!:00")

复制代码

这样的话就成真正的随机时间了（前面的包含0-59分，后面的进位也解决了）
献丑了！
附上附件

tgydslr

让大家见笑了

蓝粆

学习是必须的，灌水也是可以的

蓝粆

嘿嘿。。。=TEXT(RAND()*23,"00")  这个可以解释下么？能返回0-23的~

tgydslr

蓝粆 发表于 2014-5-2 23:02
嘿嘿。。。=TEXT(RAND()*23,"00")  这个可以解释下么？能返回0-23的~

嗯，时间的小时

蓝粆

tgydslr 发表于 2014-5-2 23:10
嗯，时间的小时

{:031:}  只知道是小时 不知道是怎么回事 啊

tgydslr

时间包含几点几分

FnG

灌水：
貌似rept("0",11)中的双引号可去掉，还有text里 - 前的叹号可去掉。

hongqiqqq

感谢分享，受教了。

tgydslr

FnG 发表于 2014-5-3 00:37
灌水：
貌似rept("0",11)中的双引号可去掉，还有text里 - 前的叹号可去掉。

感谢提醒，连字符前的叹可以去掉，已纠正