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

[µo°Ý] ¨ç¼Æ_¦h­Ó©wÂI­È¡A¨ä¹ïÀ³­È³£¬Û¦P¡C

[µo°Ý] ¨ç¼Æ_¦h­Ó©wÂI­È¡A¨ä¹ïÀ³­È³£¬Û¦P¡C

¥»©«³Ì«á¥Ñ Airman ©ó 2015-11-17 07:29 ½s¿è

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

¥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 X=$R7+T$3¡F Y=T$5-$R7¡F Z =MOD(T$5+Y,$R$6)
L_1=OR(T$5="",$R7="",$R7>=T$5,X=Y)

T5©MY¤G­Ó´Á¼ÆªºR5¤§¹ïÀ³­È³£¬Û¦P
Sheet1!T7
=IF(L_1,"",IF((SUMIF(OFFSET($I$6,T$5,1,,7),$R$5,OFFSET($I$6,$R7,1,,7))=SUMIF(OFFSET($I$6,Y,1,,7),$R$5,OFFSET($I$6,X,1,,7))),Y,""))
§Y·íIÄæT5´Á¼ÆªºR5­È¡A¨ä¦bR7´Á¼Æªº¹ïÀ³­È=IÄæY´Á¼ÆªºR5­È¡A¨ä¦bX´Á¼Æªº¹ïÀ³­È®É~
«hT7Åã¥ÜY­È¡A§_«hÅã¥ÜªÅ¥Õ

T5©MY©MZ¤T­Ó´Á¼ÆªºR5¤§¹ïÀ³­È³£¬Û¦P
Sheet2!T7
=IF(L_1,"",IF((SUMIF(OFFSET($I$6,T$5,1,,7),$R$5,OFFSET($I$6,$R7,1,,7))=SUMIF(OFFSET($I$6,Y,1,,7),$R$5,OFFSET($I$6,X,1,,7)))*(SUMIF(OFFSET($I$6,T$5,1,,7),$R$5,OFFSET($I$6,$R7,1,,7))=SUMIF(OFFSET($I$6,Z,1,,7),$R$5,OFFSET($I$6,MOD(X*2,$R$6),1,,7))),Y,"")
§Y·íIÄæT5´Á¼ÆªºR5­È¡A¨ä¦bR7´Á¼Æªº¹ïÀ³­È=IÄæY´Á¼ÆªºR5­È¡A¨ä¦bX´Á¼Æªº¹ïÀ³­È=IÄæZ´Á¼ÆªºR5­È¡A¨ä¦bX*2´Á¼Æªº¹ïÀ³­È~
«hT7Åã¥ÜY­È¡A§_«hÅã¥ÜªÅ¥Õ

»Ý¨D¡J½Ð±Ð§ó²¤Æªº¨ç¼Æ¤½¦¡¡C

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

¦³¤@­Ó·Qªk~¤£ª¾¥i¦æ§_?
°²³]:¦UT5´Á¼Æ¬°A¡F¦UT5´Á¼Æ¹ïÀ³´Á¼Æ¬°B¡F¹ïÀ³­È¬°C~
©Ò¥H~"T5" MATCH "A" + "C"=OFFSET  "B"
¦³ÂIÃþ¦üVLOOKUP()ªº¥Îªk
³o¼Ë´N¤£¥Î2­ÓT5¤@¦¡¡F3­ÓT5¦A¤@¦¡¡A4­ÓT5¤S¤@¦¡...¡C

¦pªG³o­Ó·Qªk¤£¥i¦æ~½Ð¦p´£°Ý¤º®e:¶È²¤Æ¤½¦¡§Y¥i¡CÁÂÁÂ!

TOP

°²³]¹ïÀ³´Á¼Æ¦³4­Ó~R7,X,Y,Z~
T7
=IF(L1,"",IF(AND(SUMIF(OFFSET($J$6,CHOOSE({1,2,3},X,Y,Z),,,7),OFFSET($I$6,$R7,MATCH($R$5,OFFSET($J$6,T$5,,,7),)))),Y,""))

¥»¨Ó¬O·Q¥H¤W­z³o­Ó"­ã´£³¡ªL "¤j¤j½ç±Ðªº¤½¦¡¨Ó¶i¶¥­×¥¿¡A¦ýT5Åܦ¨¦³¦h­Ó´Á¼Æ~
¤£ª¾¹D«ç»ò­×¥¿?¤]¤£ª¾¹D³o­Ó·Qªk¦æ¤£¦æªº³q?

TOP

=IF(L_1,"",IF(MODE(SUMIF(OFFSET($I$6,IF({1;0},T$5,Y),1,,7),$R$5,OFFSET($I$6,IF({1;0},$R7,X),1,,7)),0,0),Y,""))

=IF(L_1,"",IF(MODE(SUMIF(OFFSET($I$6,CHOOSE({1;2;3},T$5,Y,Z),1,,7),$R$5,OFFSET($I$6,CHOOSE({1;2;3},$R7,X,MOD(X*2,$R$6)),1,,7)),0,0,0),Y,""))

¸Õ¬Ý¬Ý, ¤]¤£ª¾¹ï¤£¹ï¡H

¥t¤@ÃD¦]¬°¨â¦¸ªºÄæ¦ì¤£¤@¼Ë¡A¨S±o²¤Æ¡A¥u¯à¦p­ì¦¡¤@¤@­é¨ú¡ã¡ã

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2015-11-17 23:26 ½s¿è

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

­ã¤j:
´ú¸ÕOK¤F!¦³§ó¤F¸Ñ¶Q¤½¦¡ªºÅܤƩM¹B¥Î~ÁÂÁ±zªº­@¤ß«ü¾É~·P®¦:lol
¥tÃD~¦pªG¬O¦]¡y¹ïÀ³­È¦AÅã¥Ü¡z®Éªº"Äæ¦ì¤£¦P"¦ÓµLªk²¤Æ~
¬O§_¥i¥H´N¨ä»Ý¨D¡y¹ïÀ³­È¦AÅã¥Ü¡z§ï¬°¡y¹ïÀ³­È¦AÅã¥Ü©ó"¦PÄæ¦ì"¡z~½Ð±z¦A¦¸½ç±Ð§@¸Ñ?
¦pªG¥i¥Hªº¸Ü~©ú¤Ñ¦A»¡(²{¤w¤Ó±ß)~·P®¦::L

TOP

¦^´_ 4# ­ã´£³¡ªL
­ã¤j:
¥t¤@ÃD(http://forum.twbts.com/thread-15609-1-1.html)ªº»Ý¨D§ï¦¨:¡y¹ïÀ³­È¦AÅã¥Ü©ó"¦PÄæ¦ì"¡z~½Ð¸Ô¨£"·sªþ¥ó"¤º»¡©ú¡CÁÂÁÂ!

¸ÑÃD¤½¦¡¤£¤@©w­n¦p¦¹ÃD¤½¦¡«¬ºA~¤@¤@­é¨ú¥ç¥i¡C
·q½ÐÄ~Äò½ç±Ð¬°Ã«!·P®¦:lol

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD