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

[µo°Ý] ¨ç¼Æ¤½¦¡°£¿ùªº°ÝÃD¡C

[µo°Ý] ¨ç¼Æ¤½¦¡°£¿ùªº°ÝÃD¡C

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

¦WºÙ©w¸q¡J
R_R=$R7¡F T_3=T$3¡F T_5=T$5¡F X=$R7+T$3¡F Y=T$5-$R7¡F Z =MOD(T$5+Y,$R$6)

A_1
=OR($R7="",T$5="",$R7>=T$5,$R7+T$3=T$5-$R7,T$5=$R7+T$3)
A_2
=OR($R7="",T$5="",$R7>=T$5,$R7+T$3=T$5-$R7,T$5<>$R7+T$3)
B_1
=MOD(T$3,Y)=0
C_1
T$5=$R7+T$3
C_2
T$5<>$R7+T$3
G_1
=OR($R7="",T$5="",$R7>=T$5,$R7+T$3=T$5-$R7,MOD(T$3,Y)<>0,T$5=$R7+T$3)
G_2
=OR($R7="",T$5="",$R7>=T$5,$R7+T$3=T$5-$R7,MOD(T$3,Y)<>0,T$5<>$R7+T$3)
R_Y
=OR($R$5=OFFSET($I$6,Y,1,,7))
S_1
=OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,X,1,,7))
S_2
=OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,Z,1,,7))=OFFSET($I$6,Y,1,,7))

»¡©ú¡J
Sheet1!T7
=IF(G_1,"",IF(S_1*S_2,Y,""))
·íR7=ªÅ¥Õ©ÎT5=ªÅ¥Õ©ÎR7¤j©óµ¥©óT5©ÎR7+T3=T5-R7©ÎT3°£¥HYªº¾l¼Æ¤£µ¥©ó0©ÎT5µ¥©óR7+T3®É¡AT7Åã¥ÜªÅ¥Õ¡F
¤S·íT5´Á¼Æªº$R$5­È(EX:K97=11)¡A¨ä¦bR7´Á¼Æªº¹ïÀ³­È(EX:K94=26)¦³Åã¥Ü¦bR7+T3´Á¼Æ(EX:N103=26)
¥BT5´Á¼Æªº$R$5­È(EX:K97=11)¡A¨ä¦bT5+Y´Á¼Æªº¹ïÀ³­È(EX:K100=04)¦³Åã¥Ü¦bT5-R7´Á¼Æ(EX:K9=04)®É~
«hT7Åã¥ÜY(T5-R7)­È(EX:T94=3)¡A§_«hÅã¥ÜªÅ¥Õ¡C

Sheet2!T7
=IF(G_2,"",IF(S_1*R_Y,Y,""))
·íR7=ªÅ¥Õ©ÎT5=ªÅ¥Õ©ÎR7¤j©óµ¥©óT5©ÎR7+T3=T5-R7©ÎT3°£¥HYªº¾l¼Æ¤£µ¥©ó0©ÎT5¤£µ¥©óR7+T3®É¡AT7Åã¥ÜªÅ¥Õ¡F
¤S·íT5´Á¼Æªº$R$5­È(EX:K97=11)¡A¨ä¦bR7´Á¼Æªº¹ïÀ³­È(EX:K88=23)¦³Åã¥Ü¦bR7+T3´Á¼Æ(EX:N97=23)
¥BT5´Á¼Æªº$R$5­È¦³Åã¥Ü¦bT5-R7´Á¼Æ(EX:L15=11)®É~
«hT7Åã¥ÜY(T5-R7)­È(EX:T88=9)¡A§_«hÅã¥ÜªÅ¥Õ¡C


¤W­z2­Ó¤½¦¡¸g´ú¸Õ«á¡J
¨ä¤¤ªºG_1©MG_2¤§·íR7¤j©óµ¥©óT5«hÅã¥ÜªÅ¥Õ¡A·|¦]MOD(T$3,Y)<>0¦Ó¥¢®Ä(EX:T97=#DIV/0!)¡A
©Ò¥H¡A¥u¦n±N¨ä©î¸Ñ¬°~
Sheet1-1!T7
=IF(A_1,"",IF(B_1*S_1*S_2,Y,""))
Sheet2-1!T7
=IF(A_2,"",IF(B_1*S_1*R_Y,Y,""))

»Ý¨D¡J
1.½Ð±ÐG_1©MG_2§ó²z·Qªº¼gªk¡C
2.½Ð±N¤G¦¡¤½¦¡¦X¨Ö¬°¤@¦¡(½Ð¾¨¶q²¤Æ)

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

G_1¡G=OR($R7="",T$5="",$R7>=T$5,$R7+T$3=T$5-$R7,ISERR(0/(MOD(T$3,Y)=0)))
G_2¡G=1+(T$5<>$R7+T$3)

¢â¢¶¤½¦¡¡G=IF(G_1,"",IF(S_1*IF(G_2=2,S_2,R_Y),Y,""))

TOP

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

­ã¤j:
´ú¸ÕOK¤F!ÁÂÁ±z

¥t¥~~
½Ð±Ð±z:
G_2¡G
=1+(T$5<>$R7+T$3)
³o­Ó1¬O¥Nªí¤°»ò·N¸q?
·q½Ð«ü¾É~·P®¦!

TOP

¦^´_ 2# ­ã´£³¡ªL
­ã¤j:
©êºp!¦A½Ð±Ð±z:
¢â¢¶¤½¦¡¡G=
IF(G_1,"",IF(S_1*IF(G_2=2,S_2,R_Y),Y,""))
³o­Ó2¤S¬O¥Nªí¤°»ò·N¸q?
·q½Ð«ü¾É~·P®¦

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-11-15 18:49 ½s¿è

¦^´_ 3# Airman


¡ã¡ã=1+(T$5<>$R7+T$3)
¡@¡@³o­Ó1¬O¥Nªí¤°»ò·N¸q?

¡@
¨S·N¸q¡A¥u¬O±Nª¬ªp§PÂ_¥H¢°¤Î¢±ªí¥Ü¡]G_2¤£¬O¢°¡A´N¬O¢±¡^
=N(T$5<>$R7+T$3)¡@«h±Nª¬ªp§PÂ_¥H¢¯¤Î¢°ªí¥Ü

©Îª½±µ¡G
=T$5<>$R7+T$3¡@«h±Nª¬ªp§PÂ_¥HFalse¤ÎTrueªí¥Ü

TOP

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

­ã¤j:
ÁÂÁ±zªº¸Ñ»¡~©ú¥Õ¤F

­ì¥»¬O¤p§Ì¦³·Q¨ì³o­Ó»¡ªk¡A¦ý·Q»¡¨S¦³ If¡F
²{¦b¸g±z¸Ñ»¡½T©w«á¡A¤]ÁA¸ÑG2=2ªº¥Î·N¡C
¦A¦¸ÁÂÁ±zªº­@¤ß~·P®¦

TOP

·PÁ¤À¨É,¥¿¦n¦³»Ý¨D

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD