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

[µo°Ý] ½Ð¯q-¦p¦ó¿z¿ï¥X¦r¦ê¤¤ªº¦U­Ó¹ïÀ³¼Æ¦r

[µo°Ý] ½Ð¯q-¦p¦ó¿z¿ï¥X¦r¦ê¤¤ªº¦U­Ó¹ïÀ³¼Æ¦r

½Ð±Ð¦U¦ì¤j¤j
¦pªþ¥ó¡A¥Ø«e¤w¸g¿z¿ï¥Xt¤ÎL©Ò¹ïÀ³ªº¼Æ­È¡A¥iW«á­±§¨±aªº¸ê®Æ¤Ó¹L²V¶Ã¡A¥B¹ê»Ú¸ê®Æ¦³¦n´X¤dµ§¡A¦¹ÀÉ®×¥u¬O²©öª©ªº½d¨Ò
¥i¦³§ó¦nªº¤è¦¡¥i¥H°µ¥X¤À§O¿z¿ï¥X¸ê®Æ¤¤ªºªø¼e°ª
ÁÂÁÂ

tWL¿z¿ï.rar (7.19 KB)

®×¨ÒÀÉ®×

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2020-6-12 10:34 ½s¿è

¦^´_ 1# chen76771
°²³]AÄæ¸ê®Æ¦r¦êªº³Ì¤jªø«×¬°100­Ó¦r
°}¦C¤½¦¡¡]Shift+Ctrl+Enter¤TÁä»ô«ö¡^
=MID(A2,FIND("L",A2)+1,MAX(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:100")),1)*1),ROW(INDIRECT("1:100")),""))-FIND("L",A2))
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# hcm19522
·PÁ¤j¤j
¸g¹L¦Û¤vªº´ú¸Õ¤Î²z¸Ñ¤§«á¡A¨ÌµM¤£¸Ñ¦¹¤½¦¡¤¤ªº¶ø¯µ
1.lookup¬A¸¹¸Ì­±²Ä¤@­Ó°Ñ¼Æ¬OªÅ¥Õªº
2.³Ì«á¤@­Ó°Ñ¼Æ¸Ì­±¦³­Ó¤j¬A¸¹(1,2,3,4)
ÁÂÁ¸Ѵb

TOP

¦^´_ 3# p212
·PÁÂÁÂÁÂÀ°¦£¡A¥i¨Ì¦¹¤½¦¡Àò±oªºµ²ªG¬O¿ù»~¡A¤£¾å±o­ì¦]¥X¦b­þ

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2020-6-15 09:14 ½s¿è
½Ð±Ð¦U¦ì¤j¤j
¦pªþ¥ó¡A¥Ø«e¤w¸g¿z¿ï¥Xt¤ÎL©Ò¹ïÀ³ªº¼Æ­È¡A¥iW«á­±§¨±aªº¸ê®Æ¤Ó¹L²V¶Ã¡A¥B¹ê»Ú¸ê®Æ¦³¦n´X¤dµ§ ...
chen76771 µoªí©ó 2020-6-11 23:21



    B2 =-LOOKUP(0,-MID($A2,FIND(B$1,$A2)+1,{1,2,3,4,5,6,7,8,9}))

¥¼©R¦W.jpg
2020-6-15 09:14
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

=-LOOKUP(,-MID($A2,FIND(B$1,$A2)+1,{1,2,3,4}))

1 .ªÅ¥Nªí 0      2.MID¬O¨ú«ü©w¦ì¸m°_ {1,2,3,4}¦r  «e+ "-" ¤G§@¥Î  ¼Æ¦r¤Æ¥B¬°­t­È   0>¥ô¤@­t­È  ©Ò¥H§ä¥X³Ì«á­t­È  ³Ì«eLOOKUP +"-' ¦^¥¿­È
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-13 12:51 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«²M·¡°}¦C¯Á¤Þ¸¹³W«h,¾Ç²ß¤è®×¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
20231213_1.jpg
2023-12-13 11:32


[F1].Resize(UBound(Crr) , 3) = Crr:
20231213_2.jpg
2023-12-13 11:33


[F1].Resize(UBound(Crr) + 1, 3) = Crr:
20231213_3.jpg
2023-12-13 11:33


Option Explicit
Sub TEST()
Dim Brr, Crr, Q, i&
'¡ô«Å§iÅܼÆ
Brr = Range([A2], [A65536].End(3))
'¡ô¥OBrrÅܼƬO±a¤JÀx¦s®æ­Èªº¤Gºû°}¦C
ReDim Crr(UBound(Brr), 1 To 3)
'¡ô«Å§iCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V¯Á¤Þ¸¹±q 0 ¨ì Brr°}¦C³Ì¤j¯Á¤Þ¦C¸¹,
'UBound(Brr) ¦P 0 To UBound(Brr) ,0 To ¥i¥H¬Ù²¤
'¾î¦V¯Á¤ÞÄ渹±q 1 ¨ì 3

Q = [{"t","W","L"}]
'¡ô¥OQÅܼƬO¤@ºû°}¦C,Q(1)="t",Q(2)="W",Q(3)="L"
'=[{~}]³o¼Ë¤è¦¡ªº¤@ºû°}¦C¨S¦³0¯Á¤Þ¸¹
'Q = Array("t", "W", "L"):³o¼Ë¤è¦¡ªº¤@ºû°}¦C¦³0¯Á¤Þ¸¹

Crr(0, 1) = Q(1): Crr(0, 2) = Q(2): Crr(0, 3) = Q(3)
'¡ô¦¹Crr°}¦C0¯Á¤Þ¸¹¦C¬O¥Î¨Ó©ñ¼ÐÃD¦C,³o¼Ëªº¤è¦¡¸û¤£·|¼vÅT°j°éªº³]­p,
'°j°é¼Æ ·½ÀY°}¦C¸ê®Æ¹ï»ô¦P¤@­Ó °j°é¼Æ µ²ªG°}¦C¸ê®Æ

For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é±N¸ê®Æ¼g¤Jµ²ªG°}¦C¸Ì
   If Not Brr(i, 1) Like "*t#*W#*L#*" Then GoTo i01
   Crr(i, 1) = Val(Split(Brr(i, 1), Q(1))(1))
   Crr(i, 2) = Val(Split(Brr(i, 1), Q(2))(1))
   Crr(i, 3) = Val(Split(Brr(i, 1), Q(3))(1))
i01: Next
[F1].Resize(UBound(Crr) + 1, 3) = Crr
'¡ô¥Oµ²ªG°}¦C¼g¤JÀx¦s®æ¸Ì,
'UBound(Crr) + 1 ,³o+1ªº­ì¦]¬OResize()ÂX®iÁa¦V¾î¦VÀx¦s®æ½d³ò,·Ó²ßºD¬O¦P°}¦C³Ì¤j¯Á¤Þ¸¹½d³ò,
'¦ý¬OCrr°}¦C¦³0¯Á¤Þ¸¹,¥BUBound(Crr)¬OCrr°}¦C³Ì¤j¯Á¤Þ¦C¸¹,©Ò¥H¥²¶·+1 :

'¶K¤JÀx¦s®æªº¤è¦¡¬O¥H°}¦Cªº³Ì¥ª¤W¨¤¸ê®Æ¶}©l¦V¤U/¦V¥k©Ò»Ý½d³ò¶K¤JÀx¦s®æ,
'³Ì¥ª¤W¨¤°}¦C¶}©l¯Á¤Þ¸¹¬O(1,1),´N±q (1,1)°}¦Cªº³Ì¥ª¤W¨¤¸ê®Æ¶}©l¤U/¥k¼g¤J
'³Ì¥ª¤W¨¤°}¦C¶}©l¯Á¤Þ¸¹¬O(0,1),´N±q (0,1)°}¦Cªº³Ì¥ª¤W¨¤¸ê®Æ¶}©l¤U/¥k¼g¤J
'©Ò¥HCrr°}¦C¦³0¯Á¤Þ¦C¸¹,´N¥²¶·¥H°}¦C³Ì¤j¦C¸¹+1ªºÀx¦s®æ½d³ò¼g¤JÀx¦s®æ¤¤,¤~·|§¹¾ã

End Sub

PS:°}¦C¸Ì¦³¨S¦³(0,0) (1,0) (0,1)³o¨Ç¯Á¤Þ¸¹,¥þ¾Ì¨ä«Å§i
1.ReDim Crr(100,100) ¦P  ReDim Crr(0 To 100,0 To 100) ,¯Á¤Þ¸¹¬O±q0¶}©l,
¦pªG©Ò·Q­nªº¶}©l¯Á¤Þ¸¹Áa¦V¾î¦V³£­n±q1¶}©l,´N¥²¶·ReDim Crr(1 To 100,1 To 100)

2.Brr = Range([A1], [A65536].End(3))
¥OBrrÅܼƬO±a¤JÀx¦s®æ­Èªº¤Gºû°}¦C,³o¼Ëªº¤Gºû°}¦C,¨ä°}¦C¯Á¤Þ¸¹±q(1,1)¶}©l,¦Ó¥B¨S¦³(0,0) (1,0) (0,1)³o¨Ç¯Á¤Þ¸¹
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD