[µo°Ý] ·s±b¸¹©m¦WÂà´«¤§ºÃ´b?

cypd

·s±b¸¹©m¦WÂà´«¤§ºÃ´b?

¤U¸ü (256.03 KB)

2020-8-12 23:02

¦p¹Ï©Ò¥Ü:
1.¸ê®Æ¨Ó·½¬°¹q¸£Âà´«®æ¦¡(±q¨ä¥LÀɮ׽ƻs¶K¤W¥¼§ïÅܨä®æ¦¡-E2:E597)
2.(E2:E597¤§®æ¦¡¤£¤@¡A¦³¼Æ¦r(§t¥þ«¬¼Æ¦r)¡B¤¤¤å©m¦W+¼Æ¦r°ÑÂø¡K)
3.(E2:E597¸ê®Æ¨Ó·½¼Æ¦r¬°±b¸¹¥N½X-±b¸¹+·s±b¸¹²V¦X)
4.¸g¥Ñ½Æ»s¸ê®Æ¨Ó·½¹q¸£Âà´«®æ¦¡¶K¤W¦Ü (¹w³] E2:E1001°Ï¶¡)
5.§Æ±æµ²ªG¬° KÄæ§ó·s±b¸¹(»Ý 4 ½X) ¤Î LÄæ²£¥Í±b¸¹¹ïÀ³¤§©m¦W
6.¶K¤W±b¸¹(¡B·s)Äæ¦ì E2:E1001¡A¸g¥Ñ¤½¦¡Âà´«¬° KÄ欰·s±b¸¹¤Î LÄæ²£¥Í±b¸¹¹ïÀ³¤§©m¦W
7.­Y KÄ椺¤§±b¸¹¸g¬d®Ö«D·s±b¸¹(B2:B93)«h LÄæ¬ÛÃö¦ì§}Åã¥Ü µL¦¹¥N½X
8.(E2:E1001°Ï¶¡¤º§t¦³¤¤¤å©m¦W©Î©m¦W+¼Æ¦r©Î²Å¸¹¡K)¸Ó¦p¦óÂà´«¦¨·s±b¸¹???
¡¯¦]¬° E2:E597¤º®e±b¸¹®æ¦¡¤£¤@µ§¼Æ¸ê®Æ¦h¬Æ¬°§xÂZ¡AÃø¥H¿ëÃѱb¸¹¬°¦ó¤H?)

jcchiang

¦^´_ 1# cypd


°µ±b¸¹ÃѧO
G2=IF(ISERROR(VALUE(LEFT(E2,1))),IF(ISERROR(VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),TRIM(E2),VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),VALUE(LEFT(SUBSTITUTE(E2,"-",""),4)))
±b¸¹Âà´«
I2=IFERROR(VLOOKUP(G2,IF({1,0},C:C,B:B),2,0),IFERROR(VLOOKUP(G2,IF({1,0},A:A,B:B),2,0),G2))
²£¥Í©m¦W
J2=IFERROR(VLOOKUP(I2,IF({1,0},B:B,A:A),2,0),"µL¦¹¥N½X")
¥t¥~¦bGÄæ³]©w¤½¦¡¬OÁקKIÄ椽¦¡¹Lªø,¬Ý¨ì²´ªá
¥H¤W¬O¥H©Ò´£¨Ñªº¸ê®Æ®æ¦¡°µ¸ÑŪ,¦p¦³¨ä¥L®æ¦¡½Ð¦Û¦æ­×§ï

cypd

¦^´_ 2# jcchiang

«D±`·PÁ±zªº¥Î¤ß«ü¾É

1.±b¸¹ÃѧO
G2=IF(ISERROR(VALUE(LEFT(E2,1))),IF(ISERROR(VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),TRIM(E2),VALUE(LEFT(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-",""),4))),VALUE(LEFT(SUBSTITUTE(E2,"-",""),4)))

2.±b¸¹Âà´«(GÄæ³]©w¤½¦¡¬OÁקKIÄ椽¦¡¹Lªø)
I2=IFERROR(VLOOKUP(G2,IF({1,0},C:C,B:B),2,0),IFERROR(VLOOKUP(G2,IF({1,0},A:A,B:B),2,0),G2))

3.²£¥Í©m¦W
J2=IFERROR(VLOOKUP(I2,IF({1,0},B:B,A:A),2,0),"µL¦¹¥N½X")

­«ÂI¦b...I Ä椽¦¡Âà´«®É·|¾É­PCPU­pºâ:(1~100³B²z¾¹)"1~100%

¤U¸ü (14.01 KB)

2020-8-13 14:35

¬O§_¯à¦A°w¹ï(.I Ä椽¦¡)¦¹°ÝÃD¦A¦æ­×¥¿ÅýÀÉ®×°õ¦æ§ó¶¶ºZ...·P®¦

jcchiang

¦^´_ 3# cypd

G2=IF(ISERROR(VALUE(LEFT(E2,1))),IF(ISERROR(VALUE(SUBSTITUTE(UPPER(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-","")),"Z",""))),TRIM(E2),VALUE(SUBSTITUTE(UPPER(SUBSTITUTE(RIGHT(E2,2*LEN(E2)-LENB(E2)),"-","")),"Z",""))),VALUE(LEFT(SUBSTITUTE(UPPER(SUBSTITUTE(E2,"-","")),"Z",""),4)))
I2=IFERROR(INDEX(B:B,MATCH(G2,C:C,0),1),G2)
J2=IFERROR(INDEX(A:A,MATCH(I2,B:B,0),1),IFERROR(INDEX(A:A,MATCH(I2,A:A,0),1),"µL¦¹¥N½X"))

jcchiang

¦^´_ 4# jcchiang

§Ñ¤FIÄæ¬O©ñ¤u¸¹
I2=IFERROR(INDEX(B:B,MATCH(G2,C:C,0),1),IFERROR(INDEX(B:B,MATCH(G2,A:A,0),1),G2))
J2=IFERROR(INDEX(A:A,MATCH(I2,B:B,0),1),"µL¦¹¥N½X")

­ã´£³¡ªL

F2/°}¦C¤½¦¡:»²§U¤½¦¡
=IFERROR(-LOOKUP(1,-MID(E2,MIN(FIND(ROW($1:$10)-1,ASC(E2)&1/17)),{1,2,3,4})),"")

K2:
=IF(F2="","",IFERROR(INDEX(B:B,-LOOKUP(1,IF({1,0},-MATCH(F2,B:B,),-MATCH(F2,C:C,)))),F2))
L2:
=IF(K2="","",IFERROR(INDEX(A:A,MATCH(K2,B:B,)),"µL¦¹¥N½X"))

cypd

¦^´_ 6# ­ã´£³¡ªL
·PÁ ­ã´£³¡ªL  ¤£§[«ü¾É

F2/°}¦C¤½¦¡:»²§U¤½¦¡
=IFERROR(-LOOKUP(1,-MID(E2,MIN(FIND(ROW($1:$10)-1,ASC(E2)&1/17)),{1,2,3,4})),"")

1.°ÝÃD:­Y¶K¤W¤§¥N½X¬°¤¤¤å©m¦W®É  ¤W­z°}¦C¤½¦¡µLªk¥¿½T²£¥Í©Ò­n¤§µ²ªG...¦p (F564~F597)

¤U¸ü (185.98 KB)

2020-8-14 23:56

2.°ÝÃD:E499¤§Àx¦s®æ¤º¼Æ¾Ú¬° 32-2
            E562¤§Àx¦s®æ¤º¼Æ¾Ú¬° 55-0
            ¦P¼Ë¬O¤T¦ì¼Æ¥[-¡A¸g¤½¦¡Âà´««á¬°¦ó?  
            E499¤§Àx¦s®æ¤º¼Æ¾Ú¬° 32-2Âà´«¬° 11720
            E562¤§Àx¦s®æ¤º¼Æ¾Ú¬° 55-0Âà´«¬° 0050
Âà´««áªº¥N½X 11720¡B0050 »P©m¦W·s±b¸¹¥N½X¤£¦P«o¯àÅã¬O¬Û¹ïÀ³©m¦W?
(¬O§_¯à±N FÄ檺¤½¦¡­×¥¿¨ú E Ä檺¼Æ¦r¬°¥D¤ñ¸û¦n¿ëÃÑ ¨Ò-933(0933)   32-2(0322)   55-0 Z(0550)

¤U¸ü (239.82 KB)

2020-8-14 23:57

cypd

¦^´_ 5# jcchiang

¤ô°Õ!!°w¹ï­«ÂI¦b...I Ä椽¦¡Âà´«®É·|¾É­PCPU­pºâ:(1~100³B²z¾¹)"1~100%
¸g¹L  jcchiang °ª¤â­×¥¿«á¶K¤W±b¸¹¥N½X«á°õ¦æ«D±`¶¶ºZ   ^^·P®¦

¥t¥~­Y¬OÀx¦s®æ¤º¤§¼Æ¾Ú¬°  15-9B  B15-9  505-A...
·|§e²{   #VALUE!  B15-9  #VALUE! ¿ù»~!¸Ó¦p¦ó­×¥¿¤½¦¡?

¤U¸ü (154.37 KB)

2020-8-15 00:32

­ã´£³¡ªL

¦^´_ 7# cypd

¯Â©m¦W¦p¦óÅã¥Ü???
=IFERROR(-LOOKUP(1,-SUBSTITUTE(MID(E2,MATCH(,MID(E2,ROW($1:$19),1)*0,),{1,2,3,4,5}),"-",)),TRIM(E2))

cypd

¦^´_ 9# ­ã´£³¡ªL

¯u¬O¤Ó¯«¤F!!
©Ò¹Jªº°ÝÃD¥Ñ ­ã´£³¡ªL ª©¥D«ü¾É¤§¤½¦¡¤wªï¤b©ó¸Ñ
±Nªñ 1000 ¦hµ§ªºÂ±b¸¹(®æ¦¡°Ñ®t¤£»ô...)
¸g¥H¤W°ª¤â«ü¾É  2~3 ¬í´NÂà´«§¹¦¨   ^^
·P®¦   :victory: