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

½Ð°Ýsheet Äæ¦ìÂà´«°ÝÃD

¦^´_ 10# Hsieh

¥i¥H¸ÑÄÀ¤@¤U³o¤@¬q¶Ü
¯uªº¬Ý¤£¤jÀ´
For i = 1 To UBound(Ay, 2)
ReDim Preserve Ary(7, s)
Ary(0, s) = Ar(1, 1): Ary(1, s) = Ar(1, 2): Ary(6, s) = Ar(1, 4)
   For j = 1 To UBound(Ay, 1)
   Ary(j + 1, s) = Ay(j, i)
   Next
   s = s + 1
Tony

TOP

¦^´_ 11# tonycho33
  1. Sub Âà¸m()
  2. Dim A As Range, Ar(), Ay(), i%, j%, Ary(), s&
  3. With Sheets("a")
  4. For Each A In .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants) '¦bA2¥H¤U¦³¸ê®ÆªºÀx¦s®æ°µ°j°é
  5. Ar = A.Resize(, 4) 'AÄæ¦V¥kÂX®i¦¨Ä檺½d³ò¡A¨ú±o¤u³æ¡B®Æ¸¹¡BºK­n¡B¼Æ¶q
  6. Ay = .Range(A.Offset(, 6), A.Offset(, 6).End(xlToRight).Offset(2)).Value '±qGÄæ¦V¥k¨ì¸Ó¦C¸ê®Æ§À¦V¤U2¦Cªº½d³ò¦s¤J°}¦CÅܼÆ
  7. For i = 1 To UBound(Ay, 2) '°j°é±q1¶}©l¨ì¸ê®Æ°}¦CªºÄæ¼Æ
  8. ReDim Preserve Ary(7, s) '¡AÂX®i°ÊºA°}¦C¦¹°}¦C¦³7¦C¡AÂX®i¦¨sÄæ
  9. Ary(0, s) = Ar(1, 1): Ary(1, s) = Ar(1, 2): Ary(6, s) = Ar(1, 4) '±N¤u³æ¡B®Æ¸¹¡B¼Æ¶q¼g¤J°}¦C
  10.    For j = 1 To UBound(Ay, 1)
  11.    Ary(j + 1, s) = Ay(j, i) '±N¨C­Ó¤uµ{ªº3­Ó¶µ¥Ø¼g¤J°}¦C
  12.    Next
  13.    s = s + 1 '·Ç³Æ¤U¦¸°ÊºA°}¦CÂX¼WªºÄæ¼Æ
  14. Next
  15. Next
  16. End With
  17. Sheets("b").UsedRange.Offset(1) = "" '²MªÅ­ì¨Ó¸ê®Æ
  18. Sheets("b").[A2].Resize(s, 7) = Application.Transpose(Ary) '±N°}¦C¼g¤Jb¤u§@ªí
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 10# Hsieh


    Äæ¦ì¼W¥[®É
¤Ï¬õ¹ïÀ³ªºÀx¦s®æ¦n¹³·|¹ï¤£¨ì
½Ð°Ý­n¦p¦ó¸Ñ¨M©O
ÁÂÁÂ

sheet Äæ¦ìÂà´« - ½Æ»s.rar (16.52 KB)

Tony

TOP

¦^´_ 13# tonycho33

§Aªº¶µ¥Ø­«½Æ¡A½Ð»¡©ú¤ñ¹ï³W«h
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 14# Hsieh


    §A¦n
¤ñ¹ï³W«h¬O¤u³æ¥[¤uµ{¨Ó§PÂ_¹ïÀ³ªºjÄæ¦pªG¬Ook
´N¤Ï¬õ
Tony

TOP

¦^´_ 14# Hsieh


sheet Äæ¦ìÂà´«.rar (27.92 KB)
§A¦n´X­Ó°ÝÃD½Ð±Ð¤@¤U
1.¥Ø«eªºÄæ¦ì¦pªGÂX¥R¤§«á
b sheet·|¥´¶Ã

2.key ok«á
¤§«e¹ïÀ³¤Ï¬õ¥i¥H3­Ó³sÄòÀx¦s®æ¤@°_
²{¦b¦n¹³´N¥u¦³¤@­Ó

½Ð¨ó§U¸Ñ¨M³Ì·sªþ¥óÀɪº¤º®e
ÁÂÁÂ
Tony

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2011-12-22 00:10 ½s¿è

¦^´_ 16# tonycho33

   1.¤W¦¸¶Wª©´£¨Ñ¤§Àɮ׬Ookªº(ÂX¥RÄæ¦C§¡¥i)
   2.­Y§AÂX¥RÄæ¦C¦³°ÝÃD
    ½Ð§âÂX¥RÄæ¦Cªº­ì©l¸ê®Æ«Ø¦n¤W¶Ç ¦AÅý¦^µªªÌ¥h§@´ú¸Õ
    ¦Ó¤£¬O§â§Ë¶Ãªº¸ê®Æ¤W¶Ç ¥ô¦ó¤H¤]¬Ý¤£¥X°ÝÃD¥X¦b¨º
sheet Äæ¦ìÂà´«.zip (24.5 KB)

TOP

¦^´_ 10# Hsieh

   ¶È¦³1¶µ¤uµ{®É(¤uµ{1)
   Âà¸m®É b¤u§@ªí¸Ó¤u³æ·|¦h¶]¥X249¦C
  ¨£¤W¼ÓÀÉ®×

TOP

¦^´_ 16# tonycho33

ªí®æªº¦X¨ÖÀx¦s®æ°ÝÃD
  1. Sub Âà¸m()
  2. Dim A As Range, Ar(), Ay(), i%, j%, Ary(), s&
  3. r = 2
  4. With Sheets("a")
  5. Do Until .Cells(r, 1) = ""
  6. Set A = .Cells(r, 1)
  7. Ar = A.Resize(, 4)
  8. k = Application.CountA(.Range(A.Offset(, 6), A.Offset(, 6).End(xlToRight)))
  9. Ay = A.Offset(, 6).Resize(3, k).Value
  10. For i = 1 To UBound(Ay, 2)
  11. ReDim Preserve Ary(7, s)
  12. Ary(0, s) = Ar(1, 1): Ary(1, s) = Ar(1, 2): Ary(6, s) = Ar(1, 4)
  13.    For j = 1 To UBound(Ay, 1)
  14.    Ary(j + 1, s) = Ay(j, i)
  15.    Next
  16.    s = s + 1
  17. Next
  18. r = r + 3
  19. Loop
  20. End With
  21. Sheets("b").UsedRange.Offset(1) = ""
  22. Sheets("b").[A2].Resize(s, 7) = Application.Transpose(Ary)
  23. End Sub

  24. Private Sub Worksheet_Activate()
  25. Set d = CreateObject("Scripting.Dictionary")
  26. With Sheet6
  27. For Each A In .Range("J:J").SpecialCells(xlCellTypeConstants)
  28.    If A = "ok" Then
  29.    mystr = Join(Application.Transpose(Application.Transpose(A.Offset(, -9).Resize(, 5).Value)), "")
  30.    d(mystr) = d.Count
  31.    End If
  32. Next
  33. End With
  34. With Me
  35. r = 2
  36. Do Until .Cells(r, 1) = ""
  37. Set A = .Cells(r, 1)
  38. mystr = A & A.Offset(, 1)
  39.    For Each c In .Range(A.Offset(, 6), A.Offset(, 6).End(xlToRight))
  40.       temp = mystr & Join(Application.Transpose(c.Resize(3, 1)), "")
  41.       If d.exists(temp) = True Then c.Resize(3, 1).Interior.ColorIndex = 38 Else c.Resize(3, 1).Interior.ColorIndex = 0
  42.    Next
  43. r = r + 3
  44. Loop
  45. End With
  46. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 19# Hsieh


½Ð°Ý¤§«e¥ý¥Ña sheetÂনb sheetÄæ¦ì®æ¦¡
¦bb sheet¤¤«ü©wÄæ¦ìjÄæ¿é¤J¡yok¡z
«h¹ïÀ³ªºa sheet¯à°÷¤Ï¬õ

¥i¥HÂà´«¦¨¦pªGH2~H4¤Ï¬õ§ï¦¨¡y§R°£¡z¡AµM«á«áÄòI2~I4»¼¸É¦V¥ª¦ì²¾¤@®æ
I2~I4 ¡÷ H2~H4
J2~J4 ¡÷ I2~I4
¥H¦¹Ãþ±À
ÁÂÁÂ

sheet Äæ¦ìÂà´«.rar (24.59 KB)

Tony

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD