ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] «Ø¤J¥|¦ì¼ÆÅܮɶ¡®æ¦¡+¥ð®§®É¶¡¦©°£.

[µo°Ý] «Ø¤J¥|¦ì¼ÆÅܮɶ¡®æ¦¡+¥ð®§®É¶¡¦©°£.

¥»©«³Ì«á¥Ñ for253 ©ó 2018-4-21 14:46 ½s¿è

½Ð±Ð¦p¦ó³]­p¤@­Ó³øªí®æ¦¡¡A¦bÄæ¦ì«Ø¤J¥|¦ì¼Æ¥i¥HÅã¥Ü®É¶¡
¥t¤@­Ó´N¬O¤u§@¤À¦­±ß¯Z¸ó¹j¤Ñ¡]³o­Ó¦³¤½¦¡­pºâ¤F¡^¡A¦p¦ó¦©°£¦­¯Z©M±ß¯Zªº¥ð®§¥´±½®É¶¡
¦³¥i¯à¦b¤@­ÓÄæ¦ì¸Ì§¹¦¨©Ò¦³ªº¥ð®§®É¶¡¦©°£¶Ü¡H
¦]·|§Ú¥u·|¤@¨Ç°ò¥»ªº¡A­n¦©°£®É¶¡³o¦³ÂIÃø
¤£ª¾¦U¦ì°ª¤â¦³¨S¦³¥i¯à¨ó§U§¹¦¨³o­pºâ¤½¦¡


§Ú¦bªþ¥óÀɦ³»¡©ú¡A¦A½Ð¸Ô¬Ý¡AÁÂÁ¡C
¥ð®§®É¶¡¦©°£.rar (8.87 KB)

½Ð±Ð¦p¦ó³]­p¤@­Ó³øªí®æ¦¡¡A¦bÄæ¦ì«Ø¤J¥|¦ì¼Æ¥i¥HÅã¥Ü®É¶¡
¥t¤@­Ó´N¬O¤u§@¤À¦­±ß¯Z¸ó¹j¤Ñ¡]³o­Ó¦³¤½¦¡­pºâ ...
for253 µoªí©ó 2018-4-21 14:45

¥ð®§®É¶¡¦©°£(NEW).rar (8.33 KB)
­èªºÀɮפº®e©Ç©Çªº,§Ú­«·s¤W¶Ç,¥H¦¹«Ê¬°¥D

TOP

=SUMPRODUCT(FREQUENCY(ROW(INDIRECT(B7*1440+1&":"&B8*1440)),MID(G4:G8,{1,7},5)*1440)*MOD(ROW(1:11),2))+SUMIF(F4:F8,"<>",H4:H8)
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

=SUMPRODUCT(FREQUENCY(ROW(INDIRECT(B7*1440+1&":"&B8*1440)),MID(G4:G8,{1,7},5)*1440)*MOD(ROW(1:11),2) ...
hcm19522 µoªí©ó 2018-4-22 12:09


±z¦n¡A§Ú¦³±N¤½¦¡®M¤J¡A¦ý­pºâ·|¤Ö¦©°£10¤ÀÄÁ¡A¥t¥~¦pªG¬O¸ó¤À­¶­n¦p¦ó­pºâ¡H§Ú¥Î°_¨Ó¦n¹³©Ç©Çªº¡C
¥i¥HÀ°¦£¦A¬Ý¤U­±ªº®æ¦¡¶Ü¡HÁÂÁ¡C
¥ð®§®É¶¡¦©°£V02.rar (14.37 KB)

TOP

¦^´_ 4# for253


   «á­±¥h±¼ SUMPRODUCT(FREQUENCY(ROW(INDIRECT(B7*1440+1&":"&B8*1440)),MID(G4:G8,{1,7},5)*1440)*MOD(ROW(1:11),2))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 5# hcm19522

·PÁ¤j¤jªº¤½¦¡¡A­è§R°£«á¡A¤é¯Zªº­pºâ´N¥¿±`¤FOK¤F
¦ý©]¯Zªº¤W¯Z®É¬q20:00~24:00©Î¬O20:00~01:40§Úª½±µ®M¥Î¡Aµ²ªG®É¶¡¦©°£¥ð®§¤U¨Ó¬O¿ù»~
¬O¦]¬°¸ó¹j¤éªºÃö«Y¶Ü?§Ú¬Ý¤½¦¡¤]¦³¦b1440+1¤F? ¨D¸Ñ@@. ÁÂÁÂ.

TOP

¦^´_ 6# for253
©]¯Z­pºâ.rar (13.69 KB)

:L ¦U¦ì¤j¤j¡A§Úªº©]¯Z®É¬qªº¤u§@®É¶¡¦©°£ÁÙ¨S¦³¸Ñ¨M¡A¥Ø«e¥u¯à¤â°ÊKEY IN  
¦³¤j¤j¥i¥H¦AÀ°§Ú¸Ñ¨M¶Ü¡AÁÂÁ¡C

TOP

¦^´_ 7# for253

©]¶¡
D14 =SUMPRODUCT(FREQUENCY(MOD(ROW(INDIRECT(B13*1440+1&":"&(B14+(B13>B14))*1440)),1440),ROUND(MID(G$4:G$8&J$4:J$8,{1,7,12,18},5)*1440,)) *MOD(ROW($1:$21),2))

¤é¶¡
¤]¥i¥HD14½Æ»s¦ÜD8
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# for253

­ì©l®É¶¡®æ¦¡
07 50
20 00
D3 =SUMPRODUCT(FREQUENCY(MOD(ROW(INDIRECT((B2&":"&C2)*1440+1&":"&((--(B3&":"&C3)+(--(B2&":"&C2)>--(B3&":"&C3)))*1440))),1440),ROUND(MID(G$4:G$8&J$4:J$8,{1,7,12,18},5)*1440,)) *MOD(ROW($1:$21),2))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# for253
D14 =HOUR((B14-B13+IF(B13>B14,1))-(TEXT(INT(K9/60),"00") & ":" & TEXT(MOD(K9,60),"00")))*60+MINUTE((B14-B13+IF(B13>B14,1))-(TEXT(INT(K9/60),"00") & ":" & TEXT(MOD(K9,60),"00")))¡AÀx¦s®æ¥Î³q¥Î¡A±o¨ìµª®×¬O465

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD