ML089

A1 =SUM(MID(A1,{4,10},{5,3})*{1000,1})

ML089

¦^´_ 8# Qin

¦^ÂÐ 8¼Ó·sÃD¥Ø
±N "-" ³B´¡¤J0¡Aªø«×§ï¬°15¦ì

=REPLACE(A1,FIND("-",A1),1,REPT(0,16-LEN(A1)))

ML089

¦^´_ 12# Qin

=VLOOKUP(E2,A2:B6,2,0)
­n§ï¬°
=VLOOKUP(E2,A$2:B$9,2,0)
½d³ò­n¥Î$Âê¦í¡A¤£µM¤U©Ô®É½d³ò¦^¶]±¼
½d³ò¤j¤p¤]­n­×§ï


E3¬O¤å¦r¡AA3¬O¼Æ¦r
E6¬O¤å¦r¡AA4¬O¼Æ¦r
©Ò¥H¥ÎVLOOKUP¬d¸ß¤£¨ì

ML089

¦^´_ 12# Qin

­Y½s½X¦³¤å¼Æ¦r®É(¤£«Øij)¡A¸Õ¸Õ¤U¦¡
f2 =INDEX(B:B,-LOOKUP(,-MATCH(IF({1,0},E2,--E2),A:A,)))

ML089

¦^´_ 16# Qin

¨S¦³¤Ç°t¦p¤U¡Aªø«×¤£¼Ë

10000000000001a
100000000000001a

150000000002160
15002160

15gb00000000435
15GB0435


20000000000336a
200000000000336a

ML089

¦^´_ 18# Qin

F2 =VLOOKUP(SUBSTITUTE(D2,"-","*"),IF({1,0},A$2:A$99&"",B$2:B$99),2,)
°}¦C¤½¦¡¡A»Ý¥ÎCTRL+SHIFT+ENTER¤TÁä¿é¤J¤½¦¡


Test_2.rar (8.54 KB)

¤U¸ü¦¸¼Æ: 3

2017-3-22 23:09

ML089

¦^´_ 20# Qin

¥[¤WIF§PÂ_ DÄæ¬O§_¦³­È¡AµL­È®É¬°ªÅ¥Õ

{ =IF(D2="","",VLOOKUP(SUBSTITUTE(D2,"-","*"),IF({1,0},A$2:A$99&"",B$2:B$99),2,)) }

AÄæ¸ê®Æ¬° Table_Array ªº Index-key ³Ì¦n¯à¬O©T©wªø«×¡A²Î¤@¬°¤å¦r¡A¹ï¬d¸ß¤ñ¸û®e©ö¡C
¥Ø«e AÄæ ¦³¤å¦r¡B¼Æ¦r«¬ºA¡AIndex-keyªøµu¤£¤@­P¡A
ÁöµM ¥i¥H¥Î  «e¦r¦ê + "*" + «á¦r¦ê ¨Ó¬d¸ß¡A¦ý¤]¦³¥i¯à·|¦³»~§P±¡ªp

¨Ò¦p
A1*123¬d¸ß

¤U¦C³£¥i¥H²Å¦X¡A·|Àu¥ý¿ï¨ú²Ä¤@­Ó
A10000123
A12000123
A1A123123