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

[µo°Ý] ±NÀx¦s®æ¤º®e¦h¦C¸ê®Æ©î¸ÑÅã¥Ü

[µo°Ý] ±NÀx¦s®æ¤º®e¦h¦C¸ê®Æ©î¸ÑÅã¥Ü

¦U¦ì¤j¤j¦n
¤p§Ì·Q¤F¨â¤Ñ¤@ª½·Q¤£¥X¸Ñµª
½×¾Â¤º¤]¨S¦³¬ÛÃöªº°Q½×
¬G¤W¨Ó¨D¸Ñ

¦p¤U¹Ï©Ò¥Ü
Àx¦s®æ¤º¦³¦h¦C¸ê®Æ
¦ý­n±N¨ä©î¸Ñ¨Ã¦b®ÇÃ䪺Äæ¦ìÅã¥Ü
¤p§ÌªºVBA¥\¤O¤£°÷ ¥u·|Ū¥XÀx¦s¤º¤ºªº¤º®e¨Ã±N¨ä¶K¤W
¦ý¤£·|©î¸Ñ
­Y¥Î¨ç¼Æ¤]¥u·|¨ìÁY±Æ¦ý¨S¦³¿ìªk©î¸Ñ¦r¦ê
©Ò¥H·Q½Ð°Ý¦U¦ì¤j¤j¬O§_¦³¤èªk¥i¥H¸Ñ¨M~ÁÂÁÂ!!

123.jpg
2018-10-17 20:51

¥X³f½c¸¹.rar (9.08 KB)

A6:A9{=TRIM(MID(SUBSTITUTE(A$2,CHAR(10),REPT(" ",99)),ROW(A1)*99-98,99))

B6:B9{=IFERROR(SUM(LARGE(IF(ISERR(FIND(ROW($1:$99)&"~",A6&"~")),"",ROW($1:$99)),{1,2})*{1,-1})+1+N(B5),"")

G3 ¤U©Ô{=IF(ROW(A1)>MAXA(B$6:B$9),"",LEFT(OFFSET(A$6,SUM(N(ROW(A1)>B$6:B$9)),),9)&MAX(ISNUMBER(FIND(ROW($1:$99)&"~",OFFSET(A$6,SUM(N(ROW(A1)>B$6:B$9)),)))*ROW($1:$99))+ROW(A1)-1-N(OFFSET(B$5,SUM(N(ROW(A1)>B$6:B$9)),)))
5133.png
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

VBA
  1. Sub zz()
  2. Dim ar, b, s$, k, t, m&, n&
  3. ar = Split([a2].Value, Chr(10))
  4. With CreateObject("vbscript.regexp")
  5.     For i = 0 To UBound(ar)
  6.         .Pattern = "[A-Z]+"
  7.         k = .Execute(ar(i))(0)
  8.         .Pattern = "\d+" & k
  9.         t = .Execute(ar(i))(0)
  10.         b = Split(Replace(.Replace(ar(i), ""), k, ""), "~")
  11.         n = 0
  12.         For j = b(0) To b(1)
  13.             s = s & "|" & t & j: n = n + 1: m = m + 1
  14.         Next
  15.     Next
  16.     [g3].Resize(m) = Application.Transpose(Split(Mid(s, 2), "|"))
  17. End With
  18. End Sub
½Æ»s¥N½X

TOP

Sub TEST()
Dim Arr(1 To 20000, 0), A, B, C, i&, N&, T$, TR
For Each A In Range([A2], [A65536].End(xlUp))
For Each B In Split(A, Chr(10))
    T = Left(B, 8): TR = Split(Mid(B, 9), "~")
    For i = Mid(TR(0), 2) To Mid(TR(1), 2)
        N = N + 1: Arr(N, 0) = T & Left(TR(0), 1) & i
    Next
Next: Next
[F3].Resize(N) = Arr
End Sub

TOP

ÁÂÁ¦U¦ì°ª¤âªº¦^ÂÐ!!¤p§Ì¥ý¸Õ¸Õ!!¦³°ÝÃD¦A³Â·Ð¦U°ª¤â¸Ñ´b!!

ÁÂÁ¤j®a!!

TOP

¦^´_ 4# ­ã´£³¡ªL
½Ð°Ý­ã´£³¡ªL ¤j­ô
¥Ø«e´ú¸Õ¤U¨Ó
¦³¤@¤p°ÝÃD·Q¸Õ°Ý
¦p¹Ï©Ò¥Ü
[attach]29559[/attach]
·íÀx¦s®æ¤º®e¨S¦³"~"®Éµ{¦¡·|µo¥Í¿ù»~¡A¤U¹Ï
2018-10-20 11_51_13.jpg
2018-10-20 11:52


·Q½Ð°Ý¦pªG§Ú­n­×§ï¡A¬O§_¥ÎIF¥h§P§O´N¦n
ÁÙ¬O¦³¨ä¥L¤è¦¡
¦]¬°½c¸¹®Ç­n¥[¤W®Æ¸¹¡A©Ò¥H¤p§Ì¥Î¤F¤j¤jªºµ{¦¡­×§ï¤F¤º®e
ÁٽаѦҪþ¥ó~ÁÂÁÂ
  1. Sub TEST1()
  2. Dim Arr(1 To 20000, 0), Arr1(1 To 20000, 0), A, B, C, D, i&, N&, T$, TR
  3.     W = 1
  4. For Each A In Range([I2], [I65536].End(xlUp))
  5. W = W + 1
  6. For Each B In Split(A, Chr(10))
  7.    
  8.     T = Left(B, 8): TR = Split(Mid(B, 9), "~")
  9.     D = Cells(W, 3)
  10.     For i = Mid(TR(0), 2) To Mid(TR(1), 2)
  11.         N = N + 1: Arr(N, 0) = T & Left(TR(0), 1) & i
  12.         N = N + 0: Arr1(N, 0) = D
  13.     Next
  14. Next: Next
  15. [K3].Resize(N) = Arr
  16. [J3].Resize(N) = Arr1
  17. End Sub
½Æ»s¥N½X
test.zip (15.98 KB)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-10-20 13:20 ½s¿è

¨S¦³"~", ¬O§_³o¼Ë L180919-F25 , ¥u¦³°_©l¸¹, ¨S¦³µ²§ô¸¹???

Sub TEST1()
Dim Arr(1 To 20000, 1), A, B, C, W&, D$, i&, N&, T$, TR
For Each A In Range([H2], [H65536].End(xlUp))
W = W + 1: D = Cells(W + 1, 2)
For Each B In Split(A, Chr(10))
    T = Left(B, 8): TR = Split(Mid(B, 9) & "~" & Mid(B, 9), "~")
    For i = Mid(TR(0), 2) To Mid(TR(1), 2)
        N = N + 1: Arr(N, 0) = D: Arr(N, 1) = T & Left(TR(0), 1) & i
    Next
Next: Next
[J:K].ClearContents
[J3:K3].Resize(N) = Arr
End Sub

Mid(B, 9) & "~" & Mid(B, 9) __
F25 Åܦ¨ F25~F25
F1~F10 Åܦ¨ F1~F10~F1~F10
SPLIT ´N¥u¨ú²Ä 1 ¤Î ²Ä 2 ­Ó

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD