[µo°Ý] ¨ç¼Æ_¦h­Ó«ü©w´Á¼Æ¦³¤@­Ó(§t)¥H¤W¬Û¦PÄæ¦ìªº¥æ¶°­È¡C

Airman

°Ñ¦Òªþ¥ó¡Jhttp://www.FunP.Net/941833

¥H¤£¦A¼W¥[¦WºÙ©w¸q¬°­ì«h¡CÁÂÁÂ!

»¡©ú¡J
¦WºÙ©w¸q¡J
R_R=$R7¡F T_3=T$3¡F T_5=T$5¡F  L_1==OR(T$5="",$R7="",$R7>=T$5,$R7-T$3*2<7)

Sheet1!T7
=IF(L_1,"",IF(MODE(SUMIF(OFFSET($I$6,CHOOSE({1;2;3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5,OFFSET($I$6,CHOOSE({1;2;3},$R7,$R7-T$3,$R7-T$3*2),1,,7)),0,0,0),$R7-T$3*2,""))

»Ý¨D¡JSheet2!T7
=T5©MT5-T3©MT5-T3*2¤T­Ó´Á¼Æ³£¦³R5­È¡F¥t¦b$R7,$R7-T$3,$R7-T$3*2¤T­Ó´Á¼Æ¦³¤@­Ó(§t)¥H¤W¬Û¦PÄæ¦ìªº¥æ¶°­È¡C
PS:¤G­Ó¬q¸¨µL¶·¦³¹ïÀ³Ãö«Y¡C

½Ð°Ý¡J
Sheet1!T7¤½¦¡ªºÂŦâ¬q¸¨­n¦p¦ó­×§ï¤~¯à²Å¦XSheet2!T7ªº»Ý¨D?

¸Ô²Ó»¡©ú¤Î¹Ï¥Ü¦pªþ¥ó¡C

¥H¤W  Àµ½Ð¦U¦ì¥ý¶i¡B«e½ú¤£§[½ç±Ð!  ÁÂÁÂ!

Airman

©êºp~
T7¤½¦¡ªºÂŦâ¬q¸¨ªºÃC¦âº|¼Ð¤F~
Sheet1!T7
=IF(L_1,"",IF(MODE(SUMIF(OFFSET($I$6,CHOOSE({1;2;3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5,OFFSET($I$6,CHOOSE({1;2;3},$R7,$R7-T$3,$R7-T$3*2),1,,7)),0,0,0),$R7-T$3*2,""))

Airman

¹ï©ó¬Û¦PÄæ¦ìªº¥æ¶°­È¤§²[¸q¦A§@¸É¥R»¡©ú:
¦]T5,T5-T3,T5-T3*2¤T­Ó´Á¼Æ©MR7,R7-T3,$R7-T3*2¤T­Ó´Á¼Æªº¤G­Ó¬q¸¨µL¶·¦³¹ïÀ³Ãö«Y~
©Ò¥H¥u°w¹ïR7,R7-T3,R7-T3*2¤T­Ó´Á¼Æ¦Ó¨¥:
72 { 07,09,12,20,21,45,39 }
81 { 01,07,13,20,42,45,12 }
90 { 09,12,17,20,39,45,13 }
¤T­Ó´Á¼Æªº¬Û¦PÄæ¦ì³£¦³20,45 ¡C

­ã´£³¡ªL

¦^´_ 3# Airman

¤T¦C¦PÄæ, ¥ô¤@¬Û¦P¡G
=MODE(N(OFFSET($I$6,CHOOSE({1,2,3},$R96,$R96-T$3,$R96-T$3*2),ROW($1:$7)))+ROW($1:$7)*100,0,0,0)

Airman

¦^´_ 3# Airman
­ã¤j:
´ú¸ÕOK¤F!ÁÂÁ±zªº­@¤ß«ü¾É

¥i§_½Ð±z¦A½ç±Ð:
ÀË´úT$5©MT$5-T$3©MT$5-T3$*2¤T­Ó´Á¼Æ³£¦³$R$5­È¡F¥B¸Ó¤T´Áªº$R$5­È³£¬O¬Û¦PÄæ¦ì¡C·P®¦

­ã´£³¡ªL

¦^´_ 5# Airman


=AND(SUMIF(OFFSET($I$6,T$5,1,,7),$R$5,OFFSET($I$6,IF({1,0},T$5-T$3,T$5-T$3*2),1,,7))=$R$5)
=AND(N(OFFSET($I$6,IF({1,0},T$5-T$3,T$5-T$3*2),MATCH($R$5,OFFSET($I$6,T$5,1,,7),)))=$R$5)
=MODE($R$5,0,0,0,N(OFFSET($I$6,IF({1,0},T$5-T$3,T$5-T$3*2),MATCH($R$5,OFFSET($I$6,T$5,1,,7),))))