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

[µo°Ý] ³o¬qµ{¦¡¦³³¡¤À¤£ÁA¸Ñ

[µo°Ý] ³o¬qµ{¦¡¦³³¡¤À¤£ÁA¸Ñ

G3Àx¦s®æ {=INDEX(D:D,SMALL(IF($A$2:$A$25=$G$1,ROW($A$2:$A$25),65536),ROW(1:1)))&""}
G4Àx¦s®æ {=INDEX(D:D,SMALL(IF($A$2:$A$25=$G$1,ROW($A$2:$A$25),65536),ROW(2:2)))&""}
G5Àx¦s®æ {=INDEX(D:D,SMALL(IF($A$2:$A$25=$G$1,ROW($A$2:$A$25),65536),ROW(3:3)))&""}

¥D­n¥\¯à¬OG1¿é¤J­n´M§äªº¸ê®Æ,¦bA2~A25¤§¶¡§ä´M,§ä¨ì«á¨Ì§Ç¿é¥X¦ÜG3/G4/G5
§ä¨ì¸ê®Æ«áµ{¦¡¹B§@¤w¸g¤F¸Ñ

1.,­Y¬O¨S§ä¨ì¸ê®Æ³o³¡¤À¤£À´,¥i¥HÀ°¦£¸Ñ´b¶Ü?¥H¤U¬O§Úªº°²³]
=INDEX(D:D,SMALL(IF($A$2:$A$25=$G$1,ROW($A$2:$A$25),65536),ROW(3:3)))&""   //°²³]G1¡ÚA2~A25,  
=INDEX(D:D,SMALL(IF(FLASE, ROW(A2:A25),65536),ROW(3:3)))&""   //§ä¤£¨ì©Ò¥H¬OFLASE
=INDEX(D:D,SMALL(65536,ROW(3:3)))&""   <----SMALL(array,k)  ,65536 ¦b³oÃä¬O¿ù»~ªº®æ¦¡? ³oÃä¸Ó¦p¦ó²z¸Ñ?

2.¥t¥~µ{¦¡¤¤¬°¦ó­n¥Î65536 ¯à¶¶«K¸ÑÄÀ¶Ü?
3.­Y¬O«e¤@¦¸·j´MG3~G5¦³¿é¥X¸ê®Æ,«á¤@¦¸·j´M¥u¦³¤@µ§¸ê®ÆG3, G4/G5¬O¦p¦ó°µ¨ì§â¸ê®Æ²M°£

ÁÂÁÂ

¦^´_ 1# morris_lth

§Ú´ú¥X³¡¤À¥\¯à
1.SMALL(IF($A$2:$A$25=$G$1, ROW(A2:A25),65536),ROW(3:3)))
==>³o¬q¨Ì¤£¸Ñ,¸g¹L¹ê´ú,­Y¬OFALSE«hµ²ªG=65536.
¦ý§Úªº²z¸Ñ¬O­Y¬°FALSE µ{¦¡À³¸Ó­nÅܦ¨³o¼ËSMALL(65536,ROW(3:3)) , ¦ý³o¤£²Å¦X»yªk SMALL(array,k)....


2.¥t¥~µ{¦¡¤¤¬°¦ó­n¥Î65536 ¯à¶¶«K¸ÑÄÀ¶Ü?
==>¦pªG¨S´M¨ì¸ê®Æ«h´N¬O¶Ç¦^D65536ªº¤º®e,­Y§ï¦¨100,«h¬O¨S§ä¨ì´N·|¥á¥XD100¤ºªº¸ê®Æ
3.­Y¬O«e¤@¦¸·j´MG3~G5¦³¿é¥X¸ê®Æ,«á¤@¦¸·j´M¥u¦³¤@µ§¸ê®ÆG3, G4/G5¬O¦p¦ó°µ¨ì§â¸ê®Æ²M°£?
==>§e¤W¤]¥¿¬O¦]¬°³o¼Ë¥i¥H§â¦h¾lªº¸ê®Æ²M°£

TOP

¦^´_ 2# morris_lth


¢Ø¢°¡G
=IF($A$2:$A$25=$G$1,ROW($A$2:$A$25),65536)

´å¼Ð¯d¦b¤½¦¡½s¿è®æ¡A¥ý¤£­n«ö ENTER¡A«ö¢Ô¢¸¡A§Y¥i¬Ý¥X°}¦CÅܤơã¡ã
{65536;3;65536;5;6;7;65536;9;10;11;12;13;14;15;16;65536;18;65536;20;65536;22;23;65536;25}

¢µ¢´¢´¢²¢µ¡A¬°¢±¢¯¢¯¢²ª©¤u§@ªí³Ì«á¤@¦C¡A¤@¯ë¥Î¤£º¡¡A¹w³]¬°ªÅ®æ¡I

TOP

¦^´_ 2# morris_lth


¦^´_ 1# morris_lth

§Ú´ú¥X³¡¤À¥\¯à
1.SMALL(IF($A$2:$A$25=$G$1, ROW(A2:A25),65536),ROW(3:3)))
==>³o¬q¨Ì¤£¸Ñ,¸g¹L¹ê´ú,­Y¬OFALSE«hµ²ªG=65536.
¦ý§Úªº²z¸Ñ¬O­Y¬°FALSE µ{¦¡À³¸Ó­nÅܦ¨³o¼ËSMALL(65536,ROW(3:3)) , ¦ý³o¤£²Å¦X»yªk SMALL(array,k)....

2.¥t¥~µ{¦¡¤¤¬°¦ó­n¥Î65536 ¯à¶¶«K¸ÑÄÀ¶Ü?
==>¦pªG¨S´M¨ì¸ê®Æ«h´N¬O¶Ç¦^D65536ªº¤º®e,­Y§ï¦¨100,«h¬O¨S§ä¨ì´N·|¥á¥XD100¤ºªº¸ê®Æ
3.­Y¬O«e¤@¦¸·j´MG3~G5¦³¿é¥X¸ê®Æ,«á¤@¦¸·j´M¥u¦³¤@µ§¸ê®ÆG3, G4/G5¬O¦p¦ó°µ¨ì§â¸ê®Æ²M°£?
==>§e¤W¤]¥¿¬O¦]¬°³o¼Ë¥i¥H§â¦h¾lªº¸ê®Æ²M°£



¦^ÂÐ1
4^8 = 65536 ¡A2007ª©¥H«eªºª©¥»³Ì«á¤@¦C¬° 65536¡A¤@¯ë°²³]§A¤£·|¥Î¨ì²Ä65536¦C¡A·í¤£²Å¦X¸ê®Æ®É±N¸ê®Æ«ü¼Ð«ü¦V²Ä65536¦C¡A³o¼Ë¤½¦¡´N·|¨ú¨ìªÅ¥ÕÄæ¸ê®Æ¡C


¦^ÂÐ1
SMALL(IF($A$2:$A$25=$G$1, ROW(A2:A25),65536),ROW(3:3))) ³o°}¦C¸ê®Æ§PÂ_
==>SMALL(65536,ROW(3:3))   ³o³æµ§¸ê®Æ§PÂ_¡A¤£¬O¤W­z¤½¦¡ªº°õ¦æ¹Lµ{
==>SMALL({.....65536......},ROW(3:3))  , {.....65536......}ªí¥Ü¦³24µ§¸g¹LIF§PÂ_¡A²Å¦X®É¬°¸Ó¦C½X 2...25¡A¤£²Å¦X®É¬°65536
³Ì«á¥Î SMALL ¨ú ROW(3:3) ¨ú²Ä3¤pªº¦C¸¹¡C


¦^ÂÐ3
SMALL(IF($A$2:$A$25=$G$1, ROW(A2:A25),65536),ROW(3:3)))
¦Ò¼{¥u¦³¤@µ§²Å¦X®É¤½¦¡§ï¬°
=IFERROR(SMALL(IF($A$2:$A$25=$G$1, ROW(A2:A25)),ROW(3:3))),"")
¾A¥Î©ó EXCEL2007ªO¥H¤W
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁÂÀ°¦£.
³o¬qµ{¦¡­Y¬O¥ÎVBA¤è¦¡¨Ó¼g,¸Ó¦p¦ó°µ©O? ²{¦b¨SÀYºüªü~

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD