¾Ç¥Í¨Ì¯Z¯Å¦Û°Ê¿z¿ï¸ê®Æ¥X¨Ó
- ©«¤l
- 56
- ¥DÃD
- 11
- ºëµØ
- 0
- ¿n¤À
- 115
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- windows 7
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-13
- ³Ì«áµn¿ý
- 2023-7-13
|
¾Ç¥Í¨Ì¯Z¯Å¦Û°Ê¿z¿ï¸ê®Æ¥X¨Ó
½Ð°Ý°²³]§Ú¦³¤@¥÷excel,¦³Ó¤u§@ªí(Teacher)¦³µÛ¦Ñ®v¤Î¨ä¯Z¯Å¦WºÙ¦@¨âÓÄæ¦ì,
¥t¤@Ó¤u§@ªí(Student)¦³¤@¥÷¾Ç¥Íªº¦W³æ,¦ý¾Ç¥Í¦W³æ¤¤«o§t¦³¤£¦P¯Z¯Åªº¾Ç¥Í
§Ú·Qn´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 |
|
|
|
|
|
|
- ©«¤l
- 56
- ¥DÃD
- 11
- ºëµØ
- 0
- ¿n¤À
- 115
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- windows 7
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-13
- ³Ì«áµn¿ý
- 2023-7-13
|
¦^´_ 2# Hsieh
ÁÂÁÂ, Hsieh µ²ªG¸ò§Ú·Qnªº¬O¤@¼Ëªº. |
|
|
|
|
|
|
- ©«¤l
- 2025
- ¥DÃD
- 13
- ºëµØ
- 0
- ¿n¤À
- 2053
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN7
- ³nÅ骩¥»
- Office2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_¥«
- µù¥U®É¶¡
- 2011-3-2
- ³Ì«áµn¿ý
- 2024-3-14
|
¼W¥[±Æ§Ç¡A±N¦P¯Z¦P¾Ç±Æ¦C¦A¤@°_
A4:B4 °}¦C¤½¦¡- =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! ¤½¦¡¬°Âù®æ¤½¦¡¡An¦P®É¿ï¨ú A4:B4 ¡A¿é¤J¤½¦¡«á¥Î CTRL+SHIFT+ENTER¤TÁä»ô«ö¤è¦¡¿é¤J¡AµM«á¦A¦P¿ïA4:B4¤U©Ô½Æ»s
PS:
Âù®æ¤½¦¡¥i¥H´î¤Ö¤@¥bªº°}¦Cpºâ®É¶¡
¬O§_¥i¥H¦A¨Ï°}¦Cpºâ§ó§Ö¡A¥i¥Hªº¡A¨Ï¥Î¥þ¿ï¦¡°}¦C¤½¦¡¡C
¦³ÂI¨Æµ¥¤@¤U¦A»¡©ú |
|
{...} ªí¥Ü»Ýn¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡
|
|
|
|
|
- ©«¤l
- 2025
- ¥DÃD
- 13
- ºëµØ
- 0
- ¿n¤À
- 2053
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN7
- ³nÅ骩¥»
- Office2007
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_¥«
- µù¥U®É¶¡
- 2011-3-2
- ³Ì«áµn¿ý
- 2024-3-14
|
A4:B18 ¥þ¿ï¦¡°}¦C- =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¤½¦¡
|
|
|
|
|
- ©«¤l
- 56
- ¥DÃD
- 11
- ºëµØ
- 0
- ¿n¤À
- 115
- ÂI¦W
- 0
- §@·~¨t²Î
- windows
- ³nÅ骩¥»
- windows 7
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-13
- ³Ì«áµn¿ý
- 2023-7-13
|
¥»©«³Ì«á¥Ñ imingho ©ó 2013-5-13 18:33 ½s¿è
¦^´_ 5# ML089
ÁÂÁÂML089,Åý§Ú¤S¾Ç¤F¤@©Û. |
|
|
|
|
|
|
- ©«¤l
- 186
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 218
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-11-12
- ³Ì«áµn¿ý
- 2014-4-15
|
¦^´_ 7# shan0948
¤@¨Ç°ò¥»ªº¥Îªk¥i°Ñ¦Ò¡G
http://office.microsoft.com/zh-tw/excel-help/HA010228458.aspx#BM1 |
|
|
|
|
|
|
- ©«¤l
- 2833
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2889
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-10
|
°}¦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 |
|
|
|
|
|
|