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

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

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

¥»©«³Ì«á¥Ñ aer ©ó 2016-7-24 07:47 ½s¿è

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

sheet1

S1.jpg
2016-7-24 07:41

[

sheet2

S2.jpg
2016-7-24 07:41

³ø¦WÂàÀÉ.zip (11.08 KB)

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

µû¤À¤H¼Æ

    • ML089: ¥©§® +COLUMN($B:$M)^(COLUMN(A$1)>1)ª÷¿ú + 2

TOP

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

TOP

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

TOP

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

TOP

¦^´_ 4# aer


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

TOP

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

TOP

¦^´_ 7# hcm19522


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

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

TOP

¦^´_  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

TOP

¦^´_ 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. For i = 2 To UBound(Arr)
  8. For j = 2 To UBound(Arr, 2)
  9.     If Arr(i, j) <> "" Then
  10.        N = N + 1: T = Split(Trim(Arr(i, j)), "±è")
  11.        Brr(N, 1) = Arr(i, 1)
  12.        Brr(N, 2) = T(0) & "±è"
  13.        Brr(N, 3) = T(1)
  14.     End If
  15. Next j
  16. Next i

  17. With Sheets("Sheet2")
  18.      .UsedRange.Clear
  19.      If N = 0 Then Exit Sub
  20.      .[A1:C1] = Array("©m¦W", "±è¦¸", "¤é´Á")
  21.      .[A2:C2].Resize(N) = Brr
  22.      Application.Goto .[A1]
  23. End With
  24. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD