[µo°Ý] ¾î¦¡¸ê®ÆÂà´«¬°ª½¦¡¸ê®Æ

aer

[½Ð°Ý¤½¦¡¦p¦ó¼g¡A¤~¯à±Nsheet1(¤W¹Ï¥Ü) ªº¾î¦¡¸ê®Æ¡AÂà´«¬°sheet2(¤U¹Ï¥Ü) ªºª½¦¡¸ê®Æ¡HÁÂÁ¡I

¤U¸ü (218.94 KB)
sheet1

2016-7-24 07:41

[

¤U¸ü (90.72 KB)
sheet2

2016-7-24 07:41

­ã´£³¡ªL

A2¡D°}¦C¤½¦¡¡G¡eShift + Ctrl ¡÷ Enter¡f¤TÁä
=TRIM(MID(SUBSTITUTE(INDIRECT("Sheet1!"&TEXT(SMALL(IF(Sheet1!$B$2:$M$64="",4^8,ROW($2:$64))*100+COLUMN($B:$M)^(COLUMN(A$1)>1),ROW(A1)),"!r0c00"),)&"","±è","±è"&REPT(" ",99)),99^(COLUMN(A$1)>2),99))

hcm19522

http://blog.xuite.net/hcm19522/twblog/435047307

aer

¦^´_ 2# ­ã´£³¡ªL
ÁÂÁª©¥D¡I¤½¦¡´ú¸Õ¨S°ÝÃD¡C
¤£¹L¡A¬O§_¥i¸ÑÄÀ¤@¤U¤½¦¡·N«ä¡H·PÁ±z¡I

aer

¦^´_ 3# hcm19522
ÁÂÁ±zªº¦^ÂСI

­ã´£³¡ªL

¦^´_ 4# aer


³o¤½¦¡¤£¦n¸ÑÄÀ, ±N¤½¦¡¤À¬q, ½Ð¸ÕµÛ¦Û¦æ¥h²z¸Ñ:
Xl0000011.rar (62.92 KB)

¤U¸ü¦¸¼Æ: 17

2016-7-24 21:15

hcm19522

¦^´_ 6# ­ã´£³¡ªL
"­ã¤j" ¥©§®¦w±Æµ{¦¡ ¥iÀ´¤£©ö¼g
·Q½Ð±Ð,"!r0c00"),)&"" ,¦¹¬q¥[ &"" ¦ó·N

­ã´£³¡ªL

¦^´_ 7# hcm19522


ÁתŮæ¥X²{0, ±N¨ä¥h±¼¥i¨£¤À©ú!

³oºØ¤½¦¡ºC, ¸ê®Æ¦h, ³Ì¦n¥ÎVBA

aer

¦^´_  hcm19522


ÁתŮæ¥X²{0, ±N¨ä¥h±¼¥i¨£¤À©ú!

³oºØ¤½¦¡ºC, ¸ê®Æ¦h, ³Ì¦n¥ÎVBA
­ã´£³¡ªL µoªí©ó 2016-7-25 12:40

¤]½Ð±z½ç±ÐVBA µ{¦¡¡A¦]¸ê®Æ±Nªñ¦³1000µ§¡AµL­­·P¿E¡I

­ã´£³¡ªL

¦^´_ 9# aer


µ{¦¡½X«Ü²³æ, ¦h¬Ý´X¦¸À³¥i¥H²z¸Ñ:

1. Sub TEST()
   
2. Dim Arr, Brr, i&, j%, T, N&
   
3. With Sheets("Sheet1").UsedRange
   
4.      Arr = .Value
   
5.      ReDim Brr(1 To .Count, 1 To 3)
   
6. End With
   
7. 
   
8. For i = 2 To UBound(Arr)
   
9. For j = 2 To UBound(Arr, 2)
   
10.     If Arr(i, j) <> "" Then
    
11.        N = N + 1: T = Split(Trim(Arr(i, j)), "±è")
    
12.        Brr(N, 1) = Arr(i, 1)
    
13.        Brr(N, 2) = T(0) & "±è"
    
14.        Brr(N, 3) = T(1)
    
15.     End If
    
16. Next j
    
17. Next i
    
18. 
    
19. With Sheets("Sheet2")
    
20.      .UsedRange.Clear
    
21.      If N = 0 Then Exit Sub
    
22.      .[A1:C1] = Array("©m¦W", "±è¦¸", "¤é´Á")
    
23.      .[A2:C2].Resize(N) = Brr
    
24.      Application.Goto .[A1]
    
25. End With
    
26. End Sub

½Æ»s¥N½X