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

[µo°Ý] ¬Û¦P¸ê®ÆÄæ¦ì§R°£/«ü©w¸ê®Æ¦C­«·s±Æ¦C

[µo°Ý] ¬Û¦P¸ê®ÆÄæ¦ì§R°£/«ü©w¸ê®Æ¦C­«·s±Æ¦C

¥»©«³Ì«á¥Ñ marklos ©ó 2012-4-3 19:28 ½s¿è

½Ð¨DÀ°¦£~
Sheet1 Åܦ¨Sheet2
(¬Û¦P¸ê®ÆÄæ¦ì§R°£,¶À¦â¼Ð¥Ü)


Sheet2 Åܦ¨ Sheet3
(DÄæ¦pªG¦³¸ê®Æ, ¬õ¦r¼Ð¥Ü ,´¡¤J¤U¤@Äæ¦ì,¨Ã½Æ»s¨äB/D/G/H/IÄæ¦ìªº¸ê®Æ)


µ²ªG Sheet3


·PÁÂÀ°¦£

Excel-Q4.rar (9.97 KB)

¦^´_ 1# marklos
«Øij: ½Ð¦b Sheet1 A : J Ä檺²Ä¤@¦C¥[¤W¸ê®ÆªºÄæ¦ì¦WºÙ ¥Î¶i¶¥¿z¿ï ¥i±o¨ì¤£­«½Æ¸ê®Æ
  1. Sub EX()
  2. Sheet3.Cells.Clear
  3. Sheet1.UsedRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet3.Range("A1"), Unique:=True
  4. End Sub
½Æ»s¥N½X
(DÄæ¦pªG¦³¸ê®Æ, ¬õ¦r¼Ð¥Ü ,´¡¤J¤U¤@Äæ¦ì,¨Ã½Æ»s¨äB/D/G/H/IÄæ¦ìªº¸ê®Æ)
¬Ý¤£¤F¸Ñ  SHEET3ªºµ²ªG  ¥¦ªºÄæ¦ì¤Ö¤F¤@¦ì,¥u¦³¨ì IÄæ SHEET1ªº³Ì«áÄæ¦ì¬OJÄæ

TOP

¶i¶¥¿z¿ï½T¹ê¥i¥H±o¨ì¤£­«½Æªº¸ê®Æ~~·PÁÂ!!!
¥t¥~(DÄæ¦pªG¦³¸ê®Æ, ¬õ¦r¼Ð¥Ü ,´¡¤J¤U¤@Äæ¦ì,¨Ã½Æ»s¨äB/D/G/H/IÄæ¦ìªº¸ê®Æ)
¤À¸Ñ°Ê§@¬°
1.Sheet2¤¤ªºD12(DÄæ¦pªG¦³¸ê®Æ),D13´¡¤J·s¦C,
2.D12²¾°Ê¨ìC13ªº¦ì¸m
3.½Æ»sB12/D12/G12/H12/I12ªº¸ê®Æ¦ÜB13/D13/G13/H13/I13
4.¨ä¾lDÄæ¦pªG¦³¸ê®Æ~¨Ì¦¹Ãþ±À

TOP

¦^´_ 3# marklos

Sheet1 A : J Ä檺²Ä¤@¦C­n¥ý¥[¤W¸ê®ÆªºÄæ¦ì¦WºÙ
  1. Sub EX()
  2. Sheet3.Cells.Clear
  3. Sheet1.UsedRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet3.Range("A1"), Unique:=True
  4. With Sheet3
  5.   For R = .[D2].End(xlDown).Row To 2 Step -1
  6.     If .Cells(R, "D") <> "" Then
  7.       .Rows(R + 1).EntireRow.Insert
  8.       .Cells(R + 1, "B").Resize(1, 9).Value = .Cells(R, "B").Resize(1, 9).Value
  9.       .Cells(R + 1, "C") = .Cells(R, "D")
  10.     End If
  11.   Next R
  12.   .[D2:D65536] = ""
  13. End With
  14. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# marklos
  1. Sub ex()
  2. Dim ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheets("Sheet1")
  5. For Each a In .Range(.[A1], .[A1].End(xlDown))
  6. x = Join(Application.Transpose(Application.Transpose(a.Resize(, 10).Value)), Chr(9))
  7. If IsEmpty(d(x)) Then
  8.   d(x) = x
  9. y = Split(x, Chr(9))
  10. If a.Offset(, 3) <> "" Then
  11. For i = 1 To 2
  12. If i = 2 Then y(0) = ""
  13.   ReDim Preserve ar(s)
  14.   ar(s) = y
  15.   s = s + 1
  16. Next
  17. Else
  18. ReDim Preserve ar(s): ar(s) = y: s = s + 1
  19. End If
  20. End If
  21. Next
  22. End With
  23. With Sheets("Sheet3")
  24. Application.DisplayAlerts = False
  25. .[A1].Resize(s, 10) = Application.Transpose(Application.Transpose(ar))
  26. Application.DisplayAlerts = True
  27. End With
  28. End Su
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦pªG¥u¬O³æ¯Â±NSheet2 Åܦ¨Sheet3 ¨ºÀ³¸Ó¦p¦ó?
³æ¯Â±NDÄ檺¸ê®Æ°Å±µ¨ì¤U¤@¦CªºCÄæ¦ì , ¨Ã±N­ì¥»ªº¨º¤@¦Cªº³¡¥÷Àx¦s®æ¸ê®Æ½Æ»s¨ì¤U¤@¦CCÄæ¦ì~

TOP

¦^´_ 6# marklos
  1. Sub EX()
  2. Sheet3.Cells.Clear
  3. X = 1
  4. With Sheet2
  5.   For R = 1 To .[A65536].End(xlUp).Row
  6.     .Range("A" & R & ":J" & R).Copy Sheet3.Range("A" & X)
  7.     X = X + 1
  8.     If .Range("D" & R) <> "" Then
  9.        .Range("A" & R & ":J" & R).Copy Sheet3.Range("A" & X)
  10.        Sheet3.Range("D" & X - 1).Copy Sheet3.Range("C" & X)
  11.        X = X + 1
  12.     End If
  13.   Next R
  14. End With
  15. Sheet3.[D2:D65536] = ""
  16. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD