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

¦³­­¨îªº¸ê®Æ¿z¿ï¤£­«½Æ

¦³­­¨îªº¸ê®Æ¿z¿ï¤£­«½Æ

½Ð·Q°Ý¦U¦ì«e½ú
¦³Ãö©ó¤U¦C·Ó¤ù
¿z¿ï®Æ¸¹ªº«e¤T½X(A1:A27)
¤£­«½Æ¦Ó¥B¦³¤j¤p¶¶§Ç
¤@¯ëªº¿z¿ï¥i¥H¨Ï¥Î INDEX + SMALL ´N¥i¥H¹F¨ì¤F
¥i¬O¦A¥[¤W¦³¿ï¾Ü¤º®eªº¤è¦¡¤p§Ì§Ú´N°µ¤£¥X¨Ó¤F

¦³­­¨îªº¿z¿ï_proc.jpg (55.23 KB)

¦³­­¨îªº¿z¿ï_proc.jpg

¦³­­¨îªº¿z¿ï.rar (1.6 KB)

¦³­­¨îªº¿z¿ï

³¯ªü±l

¦^´_ 7# ML089


    ¤Ó·PÁ±z¤F  :D
³¯ªü±l

TOP

¦]¬°§Aªº³f¸¹¬°¼Æ¦rµLªkª½±µ¨Ï¥Î ¸U¥Î¦r¤¸* ¬d¸ß¡A»Ý¥ýÂର¤å¦r®æ¦¡

{=SMALL(IF(MATCH(LEFT(A$2:A$27,8)&"*",A$1:A$27&"",)=ROW($2:$27),--LEFT(A$2:A$27,8)),ROW(A1))}
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089


    ·PÁ±zªº«ü¾É , ªþ¥óÀɬ°¤p§Ì¦Û¤v½Õ¾ã¬°8½X¿z¿ïªº , ¥i¬O¥X²{ "#N/A"

¦³­­¨îªº¿z¿ï(®Æ¸¹¼W¥[¬°8½X).JPG (131.18 KB)

¦³­­¨îªº¿z¿ï(®Æ¸¹¼W¥[¬°8½X).JPG

¦³­­¨îªº¿z¿ï(®Æ¸¹¼W¥[¬°8½X).rar (2.31 KB)

¦³­­¨îªº¿z¿ï(®Æ¸¹¼W¥[¬°8½X)

³¯ªü±l

TOP

¦^´_ 4# cjd080

{=SMALL(IF(MATCH(LEFT(A$2:A$27,3)&"*",A:A,)=ROW($2:$27),--LEFT(A$2:A$27,3)),ROW(A1))}

½Ð±Ð±z¨â­Ó°ÝÃD

       ²Ä¤@ : "--LEFT" ¤£²M·¡¥Îªk , ºô¸ô¤W¤]¬d¤£¨ì .

¦^ÂÐ: -- ¬O¼Æ¾Ç¹Bºâ¡A­t­t±o¥¿¡A¦]¬°LEFT¨ç¼Æ¨ú¥X«á­nÂର¼Æ­È¤~¯à¥ÎSMALL¨ç¼Æ¨Ó±Æ§Ç¡A±N -- §ï¬° 1* ¤]¥i¥H¡A³o¼Ë¤ñ¸û®e©ö²z¸Ñ¡C


       ²Ä¤G : ¦pªG§Úªº®Æ¸¹¼Æ¦r·|¦]¬°¤£¦PÃþ«¬ , »Ý¤ñ¹ï¤£­«½Æªº¼Æ¤l·|¼W¥[¨ì¤K½X , ¬O¤£¬O¤½¦¡¤ºªº LEFT ª½±µ­×§ï¨ì²Ä¤K½X§Y¥i .

¦^ÂÐ: ¬Oªº¡ALEFT(..., 8) ¨ú8¦ì
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089


    ½Ð±Ð±z¨â­Ó°ÝÃD

       ²Ä¤@ : "--LEFT" ¤£²M·¡¥Îªk , ºô¸ô¤W¤]¬d¤£¨ì .
       ²Ä¤G : ¦pªG§Úªº®Æ¸¹¼Æ¦r·|¦]¬°¤£¦PÃþ«¬ , »Ý¤ñ¹ï¤£­«½Æªº¼Æ¤l·|¼W¥[¨ì¤K½X , ¬O¤£¬O¤½¦¡¤ºªº LEFT ª½±µ­×§ï¨ì²Ä¤K½X§Y¥i .
³¯ªü±l

TOP

http://blog.xuite.net/hcm19522/twblog/370432403

TOP

¦^´_ 1# cjd080


    {=SMALL(IF(MATCH(LEFT(A$2:A$27,3)&"*",A:A,)=ROW($2:$27),--LEFT(A$2:A$27,3)),ROW(A1))}
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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