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

¾Ç¥Í¨Ì¯Z¯Å¦Û°Ê¿z¿ï¸ê®Æ¥X¨Ó

¾Ç¥Í¨Ì¯Z¯Å¦Û°Ê¿z¿ï¸ê®Æ¥X¨Ó

½Ð°Ý°²³]§Ú¦³¤@¥÷excel,¦³­Ó¤u§@ªí(Teacher)¦³µÛ¦Ñ®v¤Î¨ä¯Z¯Å¦WºÙ¦@¨â­ÓÄæ¦ì,
¥t¤@­Ó¤u§@ªí(Student)¦³¤@¥÷¾Ç¥Íªº¦W³æ,¦ý¾Ç¥Í¦W³æ¤¤«o§t¦³¤£¦P¯Z¯Åªº¾Ç¥Í
§Ú·Q­n´N¬O§Q¥Î¦Ñ®v·í¨Ì¾Ú¨Ó¿z¿ï¥X¨ä¯Z¯Å¤Uªº¾Ç¥Í¦³½Ö,¨Ó¶×¥X¦U¦Ñ®vªº¯Z¯Å¾Ç¥Íexcel       ½Ð°Ý¸Ó«ç»ò°µ   ·P®¦~

»¡©ú¤Î¹w·Q.zip (2.93 KB)

1.¾Ç¥Í¯Z¯Å¦W³æ



2.¬O¥i¥Hª½±µ¥Î¤½¦¡²£¥Íµ²ªG

¦^´_ 1# imingho
A4°}¦C¤½¦¡
=IF(SUMPRODUCT(ISNUMBER(FIND(Student!$B$2:$B$20,Class!$D$2))*1)>=ROW(A1),INDEX(Student!A:A,SMALL(IF(ISNUMBER(FIND(Student!$B$2:$B$20,Class!$D$2)),ROW(Student!$A$2:$A$20),""),ROW(A1)),),"")
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


ÁÂÁÂ,    Hsieh µ²ªG¸ò§Ú·Q­nªº¬O¤@¼Ëªº.

TOP

¼W¥[±Æ§Ç¡A±N¦P¯Z¦P¾Ç±Æ¦C¦A¤@°_

A4:B4 °}¦C¤½¦¡
  1. =INDEX(Student!A:B,MOD(SMALL(IF(ISERR(FIND(Student!B$2:B$20,Class!D$2)),4^8,FIND(Student!B$2:B$20,Class!D$2)*100+ROW($2:$20)),ROW()-3),100),{1,2})&""
½Æ»s¥N½X
ª`·N! ¤½¦¡¬°Âù®æ¤½¦¡¡A­n¦P®É¿ï¨ú A4:B4 ¡A¿é¤J¤½¦¡«á¥Î CTRL+SHIFT+ENTER¤TÁä»ô«ö¤è¦¡¿é¤J¡AµM«á¦A¦P¿ïA4:B4¤U©Ô½Æ»s

PS:
Âù®æ¤½¦¡¥i¥H´î¤Ö¤@¥bªº°}¦C­pºâ®É¶¡
¬O§_¥i¥H¦A¨Ï°}¦C­pºâ§ó§Ö¡A¥i¥Hªº¡A¨Ï¥Î¥þ¿ï¦¡°}¦C¤½¦¡¡C

¦³ÂI¨Æµ¥¤@¤U¦A»¡©ú
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

A4:B18 ¥þ¿ï¦¡°}¦C
  1. =INDEX(Student!A:B,MOD(SMALL(IF(ISERR(FIND(Student!B$2:B$20,Class!D$2)),4^8,FIND(Student!B$2:B$20,Class!D$2)*100+ROW($2:$20)),ROW(1:15)),100),{1,2})&""
½Æ»s¥N½X
¥»¨Ò¹w¦ô³Ì¤j¤H¼Æ¬°15¤H©Ò¥H¥Î ROW(1:15)¡A¦¹³¡¤À»Ý¨D¦Û©w

¤@¯ë°}¦C¤½¦¡¨C¤@®æ³£»Ý­pºâ¤@¦¸¤Î±Æ¦C¤@¦¸«D±`¯Ó®É¡A·í¸ê®Æµ§¼Æ¶W¹L¤dµ§®É§ó¬°©úÅã¡A
¨Ò¦p¤@¦ì¦Ñ®v¤W5­Ó¯Z¨C¯Z40¤H®É¡A¤½¦¡°õ¦æ³t«×´N·|¬Û®t 5*40*2 = 400­¿¡C

©Ò¤½¦¡ªºÀ³µø¸ê®Æªº¦h¹è¤Î­pºâ¶q»Ý¨D¿ï¥Î¾A·íªº°}¦C¤½¦¡
¸ê®Æ¶q¦h®É­n´î¤Ö­«½Æ©Ê­pºâ¡A¤@¯ë¥i¥H¥Î»²§UÄæ©Î¥þ¿ï¦¡°}¦C¨Ó¸Ñ¨M¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ imingho ©ó 2013-5-13 18:33 ½s¿è

¦^´_ 5# ML089


    ÁÂÁÂML089,Åý§Ú¤S¾Ç¤F¤@©Û.

TOP

¦^´_ 7# shan0948

¤@¨Ç°ò¥»ªº¥Îªk¥i°Ñ¦Ò¡G
http://office.microsoft.com/zh-tw/excel-help/HA010228458.aspx#BM1

TOP

°}¦C¡D¾A¢ÏÄæ¬Ò¬°¤å¦r¡D¤£¨¾¢ÐÄæªÅ®æ¡G
=INDEX(Student!A:A,SMALL(IF(ISERR(FIND(Student!$B$2:$B$20,$D$2)),4^8,ROW($A$2:$A$20)),ROW(A1)))&""

°}¦C¡D±Æ°£FIND¹ïªÅ®æªº»~§P¡G
=INDEX(Student!A:A,SMALL(IF(ISERR(FIND(TEXT(Student!$B$2:$B$20,"ùÜ"),$D$2)),4^8,ROW($A$2:$A$20)),ROW(A1)))&""

¥þ°ì°}¦C¡G±N¤½¦¡¶K¤J¢Ï¢³¡A¦A¥þ¿ï¢Ï¢³¡G¢Ð¢±¢¯¡A¶i¢Ï¢³¦¨½s¿èª¬ºA¡ACtrl + Shift ¡÷ Enter ¤TÁä»ô«ö
=INDEX(Student!A:B,SMALL(IF(ISERR(FIND(Student!B2:B20,D2)),4^8,ROW(2:20)),ROW(1:20)),{1,2})&""

«DEXCEL¥¿¬ì¡A¥u¬O¾ß§O¤H¤½¦¡­×­×§ï§ï¡A¶È¨Ñ°Ñ¦Ò¡I

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD