標題:

Excel hh:mm 分類

發問:

1.datatime 格式是設訂為yyyy/m/d hh:mm 2.B1欄timerange依A1 hh:mm分成三時段: 00:01~08:00分類代號成"N" 08:01~16:00分類代號成"D" 16:01~24:00分類代號成"E" 圖片參考：http://imgcld.yimg.com/8/n/AB00168970/o/101203280088213869414610陳贊民數學補習班.jpg

最佳解答:

=LOOKUP(A2*24,{0,0.01,8.01,16.01;"E","N","D","E"}) 2012-03-28 10:21:16 補充： 沒注意到A欄是含有日期及時間，謝謝K大提醒 =LOOKUP(TEXT(A2,"h:m")*24,{0,0.01,8.01,16.01;"E","N","D","E"}) 2012-03-28 19:26:36 補充： B2=LOOKUP(TEXT(A2,"h:m")*24,{0,0.01,8.01,16.01;"E","N","D","E"}) 或=LOOKUP(TEXT(A1,"h:m")*2400,{0,1,801,1601;"E","N","D","E"})

其他解答:

B2 =LOOKUP(ROUND(MOD(A2,1)*1440,0),ROUND({"0:00","0:01","8:01","16:01"}*1440,0),{"E","N","D","E"}) 2012-03-28 09:06:14 補充： 鬍鬚大的式子尚需去掉日期才是 001式其實就是002式的簡化式 2012-03-28 10:39:08 補充英文檢定： 我用MOD取時間反而有浮點運算精度的問題，還要加ROUND來校正，用鬍鬚大的TEXT(A2,"h:m")反而簡單。 如板大沒再發問就請鬍鬚大上答 2012-03-28 10:46:55 補充： 002式可修改為下 =LOOKUP(TEXT(A2,"h:m")*1,{"0:00","0:01","8:01","16:01"}*1,{"E","N","D","E"}) 參考用

志光補習班29077F888C89A7E7
arrow
arrow
    文章標籤
    第二 影片 精度 時間 就是
    全站熱搜

    harperfrancob 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄