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

[µo°Ý] ¨ç¼Æ_«ü©w¹ïÀ³¼Æ¦r¦AÅã¥Ü¥¦¦Cªº¤½¦¡¡C

[µo°Ý] ¨ç¼Æ_«ü©w¹ïÀ³¼Æ¦r¦AÅã¥Ü¥¦¦Cªº¤½¦¡¡C

¥»©«³Ì«á¥Ñ Airman ©ó 2016-1-31 10:20 ½s¿è

TEST.rar (17.29 KB)

½Ð°Ý¡J²Å¦X¦p¤U»¡©ú»Ý¨DªºT7¤½¦¡­n¦p¦ó¼W½s¡HÁÂÁ¡I
¦pªG¨ç¼Æ¤½¦¡µLªk¹F¨ì»Ý¨D¡A¥ç³Ò¾r§iª¾¡C¦A¦¸ÁÂÁ¡I

·íIÄ檺T$5´Á¼Æ¤§J¡JPÀx¦s®æ¼Æ¦r¦³Åã¥Ü¦bIÄ檺$R7´Á¼Æ¤§J¡JPÀx¦s®æ¡A
¥BIÄ檺T$5´Á¼Æ¤§J¡JPÀx¦s®æªº¸Ó¼Æ¦r¦bIÄ檺$R7´Á¼Æ¤§J¡JP¹ïÀ³Àx¦s®æ¼Æ¦r¡A
¥ç¦³¦AÅã¥Ü©óIÄ檺$R7+T$3´Á¼Æ¤§J¡JPÀx¦s®æ®É~
«h$T7Àx¦s®æÅã¥Ü$R7+T$3¤§©M­È¡A§_«hÅã¥ÜªÅ¥Õ¡C
EX1¡JIÄ檺T$5´Á¼Æ¤§M94(=33)¦³Åã¥Ü¦bN7(=33)¡A¥BM94¦b$R7´Á¼Æ¤§M7¹ïÀ³Àx¦s®æ¼Æ¦r(=13)¡A
¥ç¦³¦AÅã¥Ü©óIÄ檺$R7+T$3´Á¼Æ¤§K13(=13)Àx¦s®æ®É~«h$T7Åã¥Ü$R7+T$3¤§©M­È(=13)¡C

EX2¡JIÄ檺T$5´Á¼Æ¤§J94(=23)¦³Åã¥Ü¦bP15(=23)¡A¥BJ94¦b$R15´Á¼Æ¤§J15¹ïÀ³Àx¦s®æ¼Æ¦r(=04)¡A
¥ç¦³¦AÅã¥Ü©óIÄ檺$R15+T$3´Á¼Æ¤§P27(=04)Àx¦s®æ®É~«h$T15Åã¥Ü$R15+T$3¤§©M­È(=21)¡C

EX3¡JIÄ檺T$5´Á¼Æ¤§O94(=38)¦³Åã¥Ü¦bM34(=38)¡A¥BO94¦b$R34´Á¼Æ¤§O34¹ïÀ³Àx¦s®æ¼Æ¦r(=43)¡A
¥ç¦³¦AÅã¥Ü©óIÄ檺$R34+T$3´Á¼Æ¤§N46(=43)Àx¦s®æ®É~«h$T34Åã¥Ü$R34+T$3¤§©M­È(=40)¡C

EX4¡JIÄ檺T$5´Á¼Æ¤§N94(=36)¦³Åã¥Ü¦bN35(=36)¡A¥BN94¦b$R35´Á¼Æ¤§N35¹ïÀ³Àx¦s®æ¼Æ¦r(=36)¡A
¥ç¦³¦AÅã¥Ü©óIÄ檺$R35+T$3´Á¼Æ¤§N47(=36)Àx¦s®æ®É~«h$T35Åã¥Ü$R35+T$3¤§©M­È(=41)¡C
¨ä¾l......¦P²zÃþ±À¡C

¸Ô¦pªþ¥ó¡C

¥»©«³Ì«á¥Ñ Airman ©ó 2016-2-1 16:17 ½s¿è

¦^´_ 1# Airman
¥H¤½¦¡­×§ïªº¤è¦¡§@¸É¥R»¡©ú¡J
¥»¨Óªº¤½¦¡³£¬O¥H$R$5­È¬°·j´M¼Ð·Ç~
EX¡J
=IF(OR($R7="",$R7>=T$5),"",IF((COUNT(RANK($R$5,OFFSET($I$6,CHOOSE({1,2},T$5,$R7),1,,7)))=2)*OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7)),$R7+T$3,""))

¦ý¦]¦³ªº$R$5­È·|100´Á³£µLµª®×~(EX¡J$R$5=21)~
©Ò¥H¤£¦A­­¨î¬°$R$5Àx¦s®æ­È¡A¦Ó±N¨äÂX¤j¬°¤@´Á7­ÓÀx¦s®æ­È¡C

¦ý«e¬q¾Ç²ß¤F¡A«á¬q¦Û¦æ­×§ï¤@ª½¨S¦³¦¨¥\~¦]¦¹´£°Ý¥uPO­×§ï«á¤§«e¬q¤½¦¡~
«á¬q¤½¦¡~   ·q½Ð¦U¦ì°ª¤â¤£§[½ç±Ð¬O©¯!·P®¦

TOP

°}¦C:
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0)),R7+T$3,"")

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2016-2-1 17:42 ½s¿è

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

­ã¤j:
´ú¸ÕOK¤F~·P®¦

¥i§_¥t½Ð±z¦A¶¶¹D½ç±Ð«á¬q¤½¦¡~
OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))
¥Ñ$R$5Àx¦s®æ­ÈÂX¤j¬°¤@´Á7­ÓÀx¦s®æ­Èªº³æ¿W¤½¦¡?(¥H³Æ·í­n³æ¿W¥Î®É¤§»Ý)~
ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2016-2-2 12:08 ½s¿è

¦^´_ 3# ­ã´£³¡ªL
­ã¤j:
¤£¦n·N«ä¡AÁöµMSUMPRODUCT,SUMIF,OFFSETµ¥3­Ó¨ç¼Æªº·N¸q³£¤F¸Ñ¡A¦ý¬°¦ó¯à¥H"/"³sµ²¦Ó¹F¨ì»Ý¨Dµª®×~¦Ü¤µ©|¤£ª¾©Ò¥HµM

ªì¬Ý¶Q¸Ñ®É¡A¤p§Ì»{¬°À³¸Ó¬O«á¬q¤½¦¡±N$R$5¥h±¼«á¡AÂX¤j¬°T$5´Á¼Æªº7­ÓÀx¦s®æ­È¦b$R7´Á¼Æªº¹ïÀ³­È¦³¦AÅã¥Ü©ó$R7+T$3´Á¼Æ¤§J¡JPÀx¦s®æ~
¦ý¤£½×¦³©Î¨S¦³¥[¤W«e¬q¤½¦¡¡A¨äµª®×³£¬O¤@¼Ëªº¥¿¸Ñµª®×~¦]¦¹¥Hµª®×»¡¨Ó¡A¤p§Ì¤S»{¬°¶Q¸Ñµª¤½¦¡À³¸Ó¬O±N¤G¬q¤½¦¡»Ý¨D¦X¨Ö¦¨¤@¾ã¬q~
¤]¦]¦¹¤~¦A´£¥X#4ªº     *OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))
±N$R$5­ÈÂX¤j¬°T$5´Á¼Æªº7­ÓÀx¦s®æ­È¦b$R7´Á¼Æªº¹ïÀ³­È¦³¦AÅã¥Ü©ó$R7+T$3´Á¼Æ¤§J¡JPÀx¦s®æ®É¡AÅã¥Ü$R7+T$3¤§©M­Èªº¤½¦¡»Ý¨D~
·q½Ð±z½ç±Ð¬°Ã«~·P®¦

TOP

¦^´_ 5# Airman


¤½¦¡¤£Ãø¡A­n·|¡e¼h¼h¡f³v¨B¸Ñ¡A¥H¤U¥Î¢Ô¢¸³v¤@À˵ø¡G
¢ã¢¶¡G=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))¡@¡@
¡@¡@¡@{0,0,0,33,0,0,21}
¢ä¢¶¡G=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0¡@¡@
¡@¡@¡@{#NUM!,#NUM!,#NUM!,1,#NUM!,#NUM!,1}
¢å¢¶¡G=OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0¡@¡@
¡@¡@¡@{#NUM!,#NUM!,#NUM!,13,#NUM!,#NUM!,21}
¢æ¢¶¡G=SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0)
¡@¡@¡@{0,0,0,13,0,0,0}

¤½¦¡¤Îµ{¦¡¡A¨ä¹ê­ì²z®t¤£¦h¡A­n±q¿ù»~¤¤¥h­×¥¿¡A¤F¸Ñ¿ù¦b­þ¡H¤~¯à¾Ç¨ì¯u¥¿ªºÂI¡A
¤µ¤Ñ°µ¤£¥X¨Ó¡A©ú¤Ñ¦A¨Ó¡A³o¬O§Ú±j½Õªº¡e¤Tªá¡f¡Dªá®É¶¡¡Dªáºë¯«¡DªáÅé¤O¡A
¢ä¢Ð¢Ï«h¦A¥[¡e¤@ªá¡f¡Ö¡eªá¿ú¡f¡Ö¶R®Ñ¬Ý¡ã¡ã¡ã¨S¦³±¶®|ªº¡I

TOP

¦^´_ 5# Airman


OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))¡@

SUM¡@±N¡@($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7)¡@¥[Á`¡A
­þ¦³¥i¯à»P¡@OFFSET($I$6,$R7+T$3,1,,7)¡@¤ñ¹ï¡H¡H¡H

SUM »P OR ³£¬O±N°}¦C¦¬¦X¬°³æ¤@­È¡A¶·ª`·N¹B¥Î®É¾÷¡I
¦h´¢¼¯´X¦¸§a¡I¡I¡I
¡@
¡@

TOP

¥i¦A¥Î¥H¤U¤½¦¡³v¤@±ÀºV!!!

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))>0))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(COUNTIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*ISNUMBER(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7))))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7))>0))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(MATCH(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7),)>0))),R7+T$3,"")

TOP

¥»©«³Ì«á¥Ñ Airman ©ó 2016-2-2 14:29 ½s¿è

¦^´_ 8# ­ã´£³¡ªL
­ã¤j:
ÁÂÁ±zªº¯Ó®É¶O¯«ªº­@¤ß±Ð¾É~·P®¦
¤p§Ì©|¦b¬ã¨s©M§V¤O¤¤~¦^À³¥ý

¥t¥~~
SUM¡@±N¡@($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7)¡@¥[Á`¡A
­þ¦³¥i¯à»P¡@OFFSET($I$6,$R7+T$3,1,,7)¡@¤ñ¹ï¡H¡H¡H

±z»~·|¤F~
¤p§Ìªº·N«ä¬O­n±N~T$5´Á¼Æ¦³$R$5­È¥B·í»P¸Ó$R$5­È¦PÄæ¦ìªº$R7´Á¼Æ¤§Àx¦s®æ­È¥ç¦³Åã¥Ü¦b$R7+T$3´Á¼Æ¤§J¡JPÀx¦s®æ®É¡A«hÅã¥Ü$R7+T$3¤§©M­È~ªº¤W­z¤½¦¡~
§ï¬°~
·íT$5´Á¼Æªº7­ÓÀx¦s®æ­È¡A¨ä¦b$R7´Á¼Æ¦PÄæ¦ìªºÀx¦s®æ­È¥ç¦³Åã¥Ü¦b$R7+T$3´Á¼Æ¤§J¡JPÀx¦s®æ®É¡A«hÅã¥Ü$R7+T$3¤§©M­È¡C

¦ý²{¦b·Q³q¤F~¬O¦Û¤v±N¥¦·Q±o¤Ó½ÆÂø¦Ó±¼¶i¦º­J¦P~¥u­n$R7´Á¼Æ©M$R7+T$3¦³¬Û¦P­È´N¬O­×§ï»Ý¨D«áªºµª®×¤F

TOP

¦^´_ 8# ­ã´£³¡ªL
­ã¤j:
¤£¦n·N«ä~¤U¦Cªº¶Q2­Ó¸Ñµª¤½¦¡~
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,$R7+T$3,1,,7),OFFSET($I$6,$R7,1,,7)*(SUMIF(OFFSET($I$6,$R7,1,,7),OFFSET($I$6,T$5,1,,7))>0))),$R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,$R7+T$3,1,,7),OFFSET($I$6,$R7,1,,7)*(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,$R7,1,,7))>0))),$R7+T$3,"")

­n¦p¦ó±N¨ä¤¤Åã¥Ü©ó$R7+T$3ªºJ¡JP¥ô¤@Àx¦s®æ¤§±ø¥ó~
§ï¦¨
Åã¥Ü©ó$R7+T$3ªºJ¡JP¤§¦PÄæ¦ìÀx¦s®æ¡C

ÁÂÁ±z^^

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD