ML089

=SUMPRODUCT(--TEXT(ROUNDUP(FREQUENCY(TEXT(C3,"0!:00")*24*60+ROW(INDIRECT("1:"&INT((B5+TEXT(C5,"0!:00") - (B3+TEXT(C3,"0!:00")))*24*60))),"8:00"*24*60+(ROW(1:9)-1)*12*60)/60,0)*18,"[>100]!1!0!0;0"))

ROW(1:9) ±±¨î°±¨®³Ìªø¤Ñ¼Æ4.5¤Ñ¡A­Y³Ì¦h30¤Ñ®É´N§ï¬°ROW(1:60)

ML089

¦^´_ 3# lesaux001

=SUMPRODUCT(--TEXT(ROUNDUP(FREQUENCY(TEXT(C3,"0!:00")*24*60+ROW(INDIRECT("1:"&INT((B5+TEXT(C5,"0!:00") - (B3+TEXT(C3,"0!:00")))*24*60))),"8:00"*24*60+(ROW(1:9)-1)*12*60)/60,0)*18,"[>100]!1!0!0;0"))

ROW(1:9) ±±¨î°±¨®³Ìªø¤Ñ¼Æ4.5¤Ñ¡A­Y³Ì¦h30¤Ñ®É´N§ï¬°ROW(1:60)

¤½¦¡¤¤ ROUNDUP(..../60, 0) ´N¬O±N¤£¨¬¤@¤p®É¼W¬°¤@¤p®É¡C

ML089

¦^´_ 6# lesaux001

°±¨®¶O¥Î­pºâ.rar (10.11 KB)

¤U¸ü¦¸¼Æ: 19

2017-4-1 06:04

¤U¸üÀɮ׬ݬÝ

¥i¯à¬O¯uªºÀɮ׮榡»P§Aµ¹ªº½d¨ÒÀɮ׮榡¤£¦P§a


½Ð¥Î«ö ¦^ÂСA¤~¯à§Ö³t³qª¾§Ú

ML089

¦^´_ 9# lesaux001

§ÚµLªk§PÂ_§Aªº°ÝÃD¬O¤°»ò?

§A¥ý¿é¤J¤é´Á©Î®É¶¡´ú¸Õ¡A¤½¦¡ªº¥¿½T©Ê¦p¦ó¡C

ML089

¦^´_ 12# lesaux001

°±¨®¶O¥Î­pºâ2003.rar (10.14 KB)

¤U¸ü¦¸¼Æ: 19

2017-4-2 23:46

¦s¬°2003ª©¥»¡A§A¦b¶}¬Ý¬Ý¡C

§A±H¨ÓªºÀɮ׶}°_¨Ó¡A¶}±Ò½s¿è´NOK¡A¨S¦³²§¼Ë¡C

ML089

¦^´_ 14# lesaux001

³o§Ú´N¤£À´¤F? ¬O§AEXCEL³]©w¤W¦³¤£¦P¶Ü?

¿ï¶µ - ¤½¦¡ ¤¤¬Ý¬Ý¦³¦ó¤£¦P


   

¤U¸ü (79.57 KB)

2017-4-3 08:41

ML089

¦^´_ 18# GBKEE

D4¤½¦¡§Ú¦Û¤v­«·s¤U¸ü«á­«¶}¤]¨S¦³°ÝÃD¡A«ç»ò«e­±·|¦³ªÅ®æ? µM¹D§Ú­è¦w¸Ëªº EXCEL 2016 ¤ô¤g¤£ªA¶Ü?
½Ð°Ý GBKEE¶W¯Åª©¥D D4¤½¦¡´ú¸ÕOK¡A¥i§_´ú¸ÕOK¦A¤W¶Ç¡C

ML089

¦^´_ 22# lesaux001

¤é´Á®æ¦¡¤£¬O°ÝÃD
®É¶¡­ì¥ý¤£¬OEXCEL®æ¦¡¡A¬O¥H0000¨Óªí¥Ü¡A­Y­×§ï«á¤½¦¡­I¶·°t¦X­×§ï¡C

ML089

¦^´_ 25# lesaux001

F4 =SUM(F5:F10)
F5 =--TEXT(ROUNDUP((MIN((B$5+TEXT(C$5,"0!:00")), B$3+"8:00"+(ROW(A3)-1)*0.5) - MAX((B$3+TEXT(C$3,"0!:00")), B$3-"4:00"+(ROW(A3)-1)*0.5))*24,0)*18,"[>100]!1!0!0;!0;0")
¤U©Ô¦ÜF10

ML089

¦^´_ 27# lesaux001

°±¨®¶O¥Î­pºâ2017040401.rar (10.89 KB)

¤U¸ü¦¸¼Æ: 16

2017-4-4 08:22

D4
D5
F4
³o¨Ç­È¤@¼Ë¶Ü?