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

fantersy

¦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~ÁÂÁÂ!!

¤U¸ü (54.5 KB)

2018-10-17 20:51

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

¤U¸ü¦¸¼Æ: 10

2018-10-17 20:47

­ã´£³¡ªL

¨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 ­Ó

fantersy

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

¤U¸ü (24.64 KB)

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)

¤U¸ü¦¸¼Æ: 1

2018-10-20 12:00

fantersy

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

ÁÂÁ¤j®a!!

­ã´£³¡ªL

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

ikboy

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

hcm19522

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)),)))