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

[µo°Ý] ¶}©l®É¶¡»Pµ²§ô®É¶¡ºâªk

[µo°Ý] ¶}©l®É¶¡»Pµ²§ô®É¶¡ºâªk

¦U¦ì¥ý¶i¦n
¤p§Ì¦³¤@°ÝÃD·Q½Ð±Ð
Sum  offset  lookup ¥h°µ¤F¤@±iªíÅý¨ä¤u®É¯à¦Û°Ê²Î­pªí³æ
¦ý³Â·Ðªº¬O¦]¬°¬O24¤p®É­pºâ¦³¨Çºâ¤£¥X¨Ó
¶W¹L00:00¤p§Ì¦³¥[1 ¦ýºâ¥X¨Óªº­ÈÁÙ¬O¤£¹ï
Àµ½Ð¸Ñ´b!!
·PÁÂ!!
¤½¦¡¦p¤U
H2==IF(G2>F2,($G2-$F2)*60*24,(($G2+1)-($F2))*60*24)+SUM(OFFSET($C$2,,,LOOKUP(VALUE($F2),$B$2:$B$11,{1,2,3,4,5,6,7,8,9,10})))-IF(G2>F2,SUM(OFFSET($C$2,,,LOOKUP(VALUE($G2),$B$2:$B$11,{1,2,3,4,5,6,7,8,9,10}))),SUM(OFFSET($C$2,,,LOOKUP(VALUE($G2+1),$B$2:$B$11,{1,2,3,4,5,6,7,8,9,10}))))

¶}§¹¤uªí³æ.zip (9.95 KB)

¤W¶ÇªþÀÉ»PÃD¤£¦P???
¬O§_­«¶Ç???

TOP

¦^´_ 2# ­ã´£³¡ªL

¤j¤j±z¦n!!
¶}§¹¤uªí³æ.zip (7.73 KB)
­«¶Ç¤Fªþ¥ó
¤º®e»P­ì´£°Ýªº¹Ï¤ù¤@¼Ë¡A¦A³Â·Ð½T»{¤@¤U
·PÁ±zªºÀ°¦£!!

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2019-7-1 17:19 ½s¿è

¦^´_ 3# fantersy


¥ð®§®É¶¡½Õ¾ã¤@¤U:   
Xl0000582.rar (5.16 KB)

©Î³o­Ó¤è¦¡:
Xl0000582-1.rar (5.8 KB)

TOP

¦^´_ 4# ­ã´£³¡ªL


    ÁÂÁ ­ã´£³¡ªL ¤j¤j
³o¤½¦¡ ¤ñ§Ú¤§«eÁÙ²`¿D
¦³ÂI¬Ý¤£¤ÓÀ´  
»Ý­nÂI®É¶¡¤F¸Ñ

TOP

¦^´_ 4# ­ã´£³¡ªL

­ã´£³¡ªL¤j ±z¦n
¤p§Ì¹Á¸Õ¤F¥h¥Î ±z«Øijªº¤½¦¡¥h­×§ï

¨Ã¹Á¸Õ°µ¤FÀx¦s®æ¸ò¦ì¸mªº­×¥¿¡A¦ý³£±o¤£¨ì¤@­Óµª®×
ªþ¥ó~½Ð°Ñ¦Ò
¶}§¹¤u®É¶¡A.zip (41.65 KB)
·Q½Ð°Ý!!
§Ú¤w¸g§â½d³òªºROWªº½d³ò¥[¤j¤F¬°¦óÁÙ·|¿ù»~!?
SMALL ¤w¸g§ì¨ì¤F¬Û¹ïªº¦ì¸m
¦ýLookup «o§ì¤£¨ì
¯uªº¤£¤ÓÀ´³o¤½¦¡ªº·N«ä

³Â·Ð±z¸Ñ´b!!ÁÂÁÂ!!

TOP

¦^´_ 6# fantersy

¶}©l®É¶¡        µ²§ô®É¶¡
00:00        00:00
00:10        01:10
03:00        03:10
05:20        06:00
06:00        08:00
10:00        10:10
12:10        13:10
15:00        15:10
17:20        18:00
18:00        20:00
22:00        22:10

²Ä¤@¦æ¥²¶·¬O00:00, §_«h 00:01 ·|LOOKUP¬°#N/A
¦@¦³11¦æ*2­Ó¼Æ¦r, ©Ò¥H SMALL¥²¶·¬OROW(1:22),  
MOD«h¬° ROW(2:23), ¤]¥i¥Î  MOD(ROW(1:22)+1,2), ¥u­n§Î¦¨{0;1;0;1~~~}°}¦C§Y¥i

=============================

TOP

¦^´_ 7# ­ã´£³¡ªL


   ¦nªº!!«D±`·PÁ ­ã´£³¡ªL¤jªº¦^ÂÐ !!

TOP

¦^´_ 3# fantersy

¨Ì·Ó3¼Ó¸ê®Æ®æ¦¡

H2¤½¦¡
=MOD(G2-F2,1)*1440-SUM(
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)>TRANSPOSE(INT(A$2:A$11*1440)))*
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)<=TRANSPOSE(INT(B$2:B$11*1440)))*TRANSPOSE(C$2:C$11>0))
°}¦C¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 9# ML089

¦] °_©l®É¶¡+1°_¸õ¡A©Ò¥H 8:00-8:00  3:00-3:00 ³oºØ¬°0ªº®É¶¡·|¤£­p¤J¦©°£¡A«e¤½¦¡¥i­×²

H2 °}¦C¤½¦¡
=MOD(G2-F2,1)*1440-SUM(
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)>TRANSPOSE(INT(A$2:A$11*1440)))*
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)<=TRANSPOSE(INT(B$2:B$11*1440))))

¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD