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

¦p¦ó¨ú¥XAÄæ²Ä15½X¥H«áªº¦r

¦p¦ó¨ú¥XAÄæ²Ä15½X¥H«áªº¦r

°ÝÃD¡G
­n¨ú¥X¹³BÄ椺¦r¡A¥HAÄæ¨Ó¬Ý¡ABÄæ¬O¨ú¥XAÄæ²Ä15½X¥H«áªº¦r¡A½Ð°Ý¦p¦ó³]©w¤½¦¡

¦p¦ó¨ú¥XAÄæ²Ä15½X¥H«áªº¦r.zip (6.16 KB)

joyce

¥»©«³Ì«á¥Ñ naruto018 ©ó 2017-10-20 17:53 ½s¿è

¦^´_ 1# leiru

B2¶ñ¤J(¨ä¾l¤U©Ô)
  1. =MID(A2,15,LEN(A2))
½Æ»s¥N½X

TOP

=LOOKUP(1,0/MID(A2,ROW($120),1),MID(A2,ROW($221),99))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¨â¦ìnaruto018 & ML089
¥i¥H³Â·ÐML089¸ÑÄÀ¤½¦¡·N«ä¡A·P®¦=LOOKUP(1,0/MID(A2,ROW($1:20),1),MID(A2,ROW($2:21),99))
joyce

TOP

¦^´_ 4# leiru


    ­Y©T©w15¦r¨Ï¥Î2¼Ó¤½¦¡´N¥i¥H

=LOOKUP(1,0/MID(A2,ROW($1:20),1),MID(A2,ROW($2:21),99))
¬O§ä³Ì«á¤@­Ó¼Æ¦r¤§«áªº¤å¦r

0/MID(A2,ROW($1:20),1) ­Y¨ú¥X¬O "0" ®É¤]·|¬O¿ù»~¡AÀ³¸Ó­×¥¿¦p¤U
=LOOKUP(1,-MID(A2,ROW($1:20),1),MID(A2,ROW($2:21),99))

-"A"  = ¿ù»~
-"0"  = 0
-"9" = -9
LOOKUP¨Ï¥Î 1§ä¡A¦]¬°   -MID(A2,ROW($1:20),1) ¥X¨Óªº­È ·|¦³ ¿ù»~¤Î 0 -1 -2 ...-9¬Ò¤ñ1¤p¡A©Ò¥H·|¥H³Ì«á¤@­Ó ¼Æ­È¬° §ä¨ì¦^¶Ç¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089


    ÁÂÁÂ
joyce

TOP

½Ð°ÝML089

MID(A2,ROW($2:21),99)===>¬°¦ó­n±q²Ä2­Ó§ä°_

TOP

½Ð°ÝML089

MID(A2,ROW($2:21),99)===>¬°¦ó­n±q²Ä2­Ó§ä°_
rph µoªí©ó 2017-10-24 16:42



=LOOKUP(1,-MID(A2,ROW($1:20),1),MID(A2,ROW($2:21),99))

"123ABC" ­n¨ú¥X "ABC" ®É¡A¥i¥H§ä¨ì ³Ì«á¼Æ¦r¨ä¦ì¸m¬O3¡A¨ú¥X MID("123ABC",3+1,99)

­ì¤½¦¡
=LOOKUP(1,-MID(A2,ROW($1:20),1),MID(A2,ROW($1:20)+1,99))
±N +1 ®É¥[¤J ROW($1:20) ´NÅܦ¨ ROW($2:21)
=LOOKUP(1,-MID(A2,ROW($1:20),1),MID(A2,ROW($2:21),99))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD