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

[µo°Ý] °}¦C°ÝÃD

[µo°Ý] °}¦C°ÝÃD

§Ú¦³1­Ó Excel ·Q¥Î°}¦C¥hŪ¨ú¤@¨Ç¸ê®Æ, ¥ÑSheet1 §â¸ê®Æ¶Ç¨ìSheet2
¥»¨Ó§Ú¬O¥Î¥H¤Uªº¤½¦¡©ñ¦bSheet2¥h°µ¬O¨S¦³°ÝÃDªº
=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$E$2E$100="RESIGN",ROW($2100),4^8),ROW()-1))&""
¦ý¬O¦pªG§Ú·Q¸ê®Æ¥Ñ¦C41¶}©l¥hŪ¨ú, §Ú§â¤½¦¡¤]§ï¤F, ´N¤£ª¾¹D¬°¤°»òÁ`¬OŪ¨ú¤£¨ì
¦³¤H¥i¥H«ü±Ð§Ú¶Ü ??

Sheet1
¡@¡@¡@¡@AÄæ¡@¡@¡@¡@BÄæ¡@¡@CÄæ¡@¡@DÄæ¡@¡@¡@¡@EÄæ
¦C1¡@¡@NAME¡@¡@¡@SEX¡@¡@AGE¡@¡@SALARY¡@¡@STATUE
¦C2¡@¡@Tommy¡@¡@¡@M¡@¡@¡@27¡@¡@18000¡@¡@¡@ACTIVE
¦C3¡@¡@Peter¡@¡@¡@M¡@¡@¡@22¡@¡@8000¡@¡@¡@ ACTIVE
¦C4¡@¡@Grace¡@¡@¡@F¡@¡@¡@33¡@¡@12000¡@¡@¡@RESIGN
¦C5¡@¡@Kitty¡@¡@¡@F¡@¡@¡@23¡@¡@8000¡@¡@¡@ ACTIVE
¦C6¡@¡@Yellow¡@¡@ M¡@¡@¡@40¡@¡@15000¡@¡@¡@FIRE OUT
¦C7¡@¡@Nita¡@¡@¡@ F¡@¡@¡@23¡@¡@8000¡@¡@ ¡@ACTIVE
¦C8¡@¡@Frederic¡@ M¡@¡@¡@35¡@¡@24000¡@¡@¡@RESIGN
.....
¦C41¡@¡@NAME¡@¡@¡@SEX¡@¡@AGE¡@¡@SALARY¡@¡@STATUE
¦C42¡@¡@Tommy¡@¡@¡@M¡@¡@¡@27¡@¡@18000¡@¡@¡@ACTIVE
¦C43¡@¡@Peter¡@¡@¡@M¡@¡@¡@22¡@¡@8000¡@¡@¡@ ACTIVE
¦C44¡@¡@Grace¡@¡@¡@F¡@¡@¡@33¡@¡@12000¡@¡@¡@RESIGN

Sheet2¡@(RESIGN)
¡@¡@¡@¡@AÄæ¡@¡@¡@¡@BÄæ¡@¡@CÄæ¡@¡@DÄæ¡@¡@¡@¡@EÄæ
¦C1¡@¡@NAME¡@¡@¡@SEX¡@¡@AGE¡@¡@SALARY¡@¡@STATUE
¦C2¡@¡@¤½¦¡¡@
¦C3¡@

=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$E$41:$E$100="RESIGN",ROW($41:$100),4^8),ROW()-1))&""
Ū±o¨ì§r¡A¬O§_§Ñ¤F¤TÁä¡H

TOP

¥»©«³Ì«á¥Ñ mixmichael ©ó 2010-11-19 18:36 ½s¿è

¦^´_ 2# oobird


    §Ú«ö¤F°Ú ~
¤£¹L¨S¦³µ²ªG¥X¨Ó
¥t¥~ÁÙ·Q°Ý¤@¤U ~ ¦pªG§Ú·Q¦b Sheet2 ¤¤ ¦C50  ©ñ¤J¬Û¦P¤½¦¡¥i¥H¶Ü ??
¤§«e§Ú³o¼Ë°µ¥¦¤]¬O¤£¦æ  ~

TOP

¦^´_ 3# mixmichael
±Æ°£¤½¦¡¥´¿ù¡AŪ¨ú¤£¨ì¸ê®Æªº¥i¯à­ì¦]¦³...
1.¤u§@ªí¤@ 41¦C¤§«á¡A¨S¦³¥ô¦ó¸ê®Æ¡C
2.¤u§@ªí¤@ 41¦C¤§«áªºEÄæ¦ì ¨S¦³²Å¦X"RESIGN"±ø¥óªº¸ê®Æ¡C
3.EÄæ¦ì²Å¦X±ø¥óªºÀx¦s®æ§t¦³ªÅ¥ÕÁä¡A¨Ò¦p" RESIGN"©Î"RESIGN "¡C
3.

TOP

ÁÂÁ§A­Ì, §Ú¦Û¤v¦A¸Õ¤@¤U¥ý
¦pªG¤£¦æ, ¦A°Ý¤j®a ~

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2010-11-21 23:49): ½Ð¤W¶ÇÀÉ®×

¥»©«³Ì«á¥Ñ mixmichael ©ó 2010-11-22 11:23 ½s¿è

¦^´_ 4# solely


§Ú¦A¸Õ°µ¤F¤@¤U ~
¬Oªº, §Ú¥¿¦psolely ©Ò»¡, §Ú¦b¤u§@ªí¤@ 41¦C¤§«á¡A¨S¦³¥ô¦ó¸ê®Æ¡C
¬O§ä¥X°ÝÃD¤F, ¥i¬O§Úªº¤u§@ªí¤@¤¤¥»¨Ó¬O¤À¶}2³¡¥÷, ¤j¬ù´N¬O 1-40 ¦C¬°¤W¥b³¡¥÷,  ¦C 45-70 ´N¬O¤U¥b³¡¥÷
·í¤¤  ¦C41 ~ 44 ¯d¤U¨Ó¥Î§@¤À¶}2 ­Ó³¡¥÷,
¦Ó¥B¤u§@ªí¤G¤¤,  §Ú¤]¬O¤À¶}¤F¤W¤U 2 ­Ó³¡¥÷, ¤W¥b³¡¥÷¬O¤u§@ªí¤G¤¤­ì¦³ªº¸ê®Æ,
¤U¥b³¡¥÷¬OŪ¨ú ¤u§@ªí¤@  ¦C45-70 ªº¸ê®Æ©ñ¦b¤u§@ªí¤Gªº ¦C45-70 ¤¤
·Q½Ð°Ý³o¼Ë°µ¥i¦æ¶Ü ??
½Ð«ü±Ð¤@¤U, ÁÂÁÂ

array.zip (7.49 KB)

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-11-22 21:08 ½s¿è

¼Ó¥Dªº¤½¦¡¤]¥´¿ù¤F~
sheet2
B16=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$F$13:$F$20="RESIGN",ROW($13:$20),4^8),ROW()-15))&""

TOP

§Ú.....¥i¥HÁ|¤âµo°Ý¶Ü?¬°¤°»ò«_¸¹·|Åܦ¨ªí±¡²Å¸¹...XDD

TOP

¦^´_ 8# solely

¦p¹Ï¤Ä¿ï¸T¥Îªí±¡§Y¥i
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

­ì¨Ó¦p¦¹XD ·PÁª©¥D~
©Ò¥H¡A¤å¤¤­Y¦³¨Ï¥Îªí±¡²Å¸¹ªº§Ö³tÁä¡A
´N¤£©y§Q¥Î¡u§Ö³t¦^´_¥DÃD¡v¨Óµoªí¦^´_¤F¡C

TOP

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD