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

[µo°Ý] ¸ß°Ý¨âµ§¥H¤W¸ê®Æ¦p¦ó¨ú¥æ¶°¦X¨Ö·s¼W(¥H¸Ñ¨M)

[µo°Ý] ¸ß°Ý¨âµ§¥H¤W¸ê®Æ¦p¦ó¨ú¥æ¶°¦X¨Ö·s¼W(¥H¸Ñ¨M)

¥»©«³Ì«á¥Ñ ivan731129 ©ó 2011-3-2 10:27 ½s¿è

½Ð°Ý¦U¦ì«e½úÃö©ó·Q­n¦X¨Ö¨â­Ó¥H¤WªºÀÉ®× ©Î¬O ¦P¤@ÀɮפU¤£¦P­¶­±ªº¥æ¶° ¸Ó¦p¦ó¥h¼g¥¨¶°©O?
Àɮ׮榡¦p¤U¡A·Qª¾¹D­n¥H¬Y¶µ¥Ø¬°¹ïÀ³°Ñ³yªº¼gªk¡A¦]¬°Â½¤F«e­±ªº°ÝÃD¨S§ä¨ì¤ñ¸û¦X¥Î¨Ó­×§ïªº¡C
·Q½Ð«e½ú­ÌÀ°¦£¡C

>>¿¤¥«§O-§Ç¸¹-¾Ç®Õ½s¸¹-¾Ç®Õ¦W-Ãþ§O-¹q¸Ü-¦a§}-±Ð¾Ç¥Î³~-³q¹L¤é´Á-¥Ó½Ð¨ì´Á¤é´Á-¤H¼Æ<<
TEST1.rar (5.61 KB)
PS.ÀÉ®×­«½Æªº³¡¥÷¥i¯à¶È¦³¦WºÙ¡A¥Î³~ªº¶µ¥Ø·|¤£¦P¡A¦]¦¹¥æ¶°µ²ªG¬O¾Ç®Õ¦b¦UÀɮ׳£¦³¥X²{ªº¤~·|¬D¥X¨ì·s­¶­±·s¼W
¦b´ú¸Õ¤¤´N¬O­n¤T­Ó­¶­±Àɸ̳£¦³¥X²{ªº­]®ßªº¾Ç®Õ¦³¥æ¶°¿é¥X¨ì·s­¶­±


¤W¤@­ÓÀɮצü¥G¥Î¨ì¸û·sªºª©¥»¡A¥t¥~¥I03ª©ªº
TEST.rar (4.49 KB)

¦^´_ 1# ivan731129
½Ðªþ½d¨ÒÀÉ®× ¤W¨Ó

TOP

¥»©«³Ì«á¥Ñ ivan731129 ©ó 2011-3-2 09:42 ½s¿è

·PÁÂGBKEE ¤j§Ö³tªº¦^µª¡A¤£¹L´ú¸Õ®Éµo²{¥L°õ¦æ¬°¦³¥æ¶°´N¿é¥X
¦ý¬O§Ú¥²¶·­n¦h­ÓÀɮפº³£¦³¤~¿é¥Xªº¸Ü­n¦p¦ó­×§ï¡C
´N¬O·í¦³¤TÀɪº¸Ü¡A­]®ß¦a°Ï­n¬O¦³¨â­Ó¾Ç®Õ ¤@­Ó¥X²{¦b¨âÀɤ¤¡B¤@­Ó¥X²{¦b¤TÀɤ¤
¨º§Ú¥u­n¿é¥X¤T­Ó³£¦³¥X²{¨º©Òªº¸Ü­n¦p¦ó­×§ï©O?

TOP

¦^´_ 3# ivan731129
03ª©
  1. Sub Ex()
  2.     Dim D(1 To 2) As Object, R, SH As Worksheet, T As String, I As Integer, AR(), A
  3.     Dim ShCount As Integer
  4.     Set D(1) = CreateObject("SCRIPTING.DICTIONARY")
  5.     Set D(2) = CreateObject("SCRIPTING.DICTIONARY")
  6.     With Sheets("¶°¦XÀÉ")
  7.         .Cells.Clear
  8.         For Each SH In Sheets
  9.             If SH.Name = "¶°¦XÀÉ" Then Exit For
  10.             ShCount = ShCount + 1
  11.             For Each R In SH.Range("A1").CurrentRegion.Rows
  12.                 T = R.Cells(1) & "," & Join(Application.Transpose(Application.Transpose(R.Cells(1, 1).Resize(1, 8))), ",")
  13.                 If D(1).Exists((T)) = False Then
  14.                     D(1)(T) = Array(False, 1)
  15.                     D(2)(T) = Array(Join(Application.Transpose(Application.Transpose(R)), ","))
  16.                 Else
  17.                      D(1)(T) = Array(True, D(1)(T)(1) + 1)
  18.                      If R.Row <> 1 Then
  19.                         AR = D(2)(T)
  20.                         ReDim Preserve AR(UBound(AR) + 1)
  21.                         AR(UBound(AR)) = Join(Application.Transpose(Application.Transpose(R)), ",")
  22.                         D(2)(T) = AR
  23.                     End If
  24.                 End If
  25.             Next
  26.         Next
  27.         For Each R In D(1).KEYS
  28.             If D(1)(R)(0) = True And D(1)(R)(1) = ShCount Then  'D(1)(R)(1) = ShCount ¨C­Ó¸ê®Æ®w³£¥X²{
  29.                 For Each A In D(2)(R)
  30.                     I = I + 1
  31.                     .Cells(I, 1).Resize(1, UBound(Split(A, ",")) + 1) = Split(A, ",")
  32.                 Next
  33.             End If
  34.         Next
  35.     End With
  36. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# ivan731129
  1. Sub Ex()
  2. Dim Sh As Worksheet, A As Range, k%, r&, Mystr$
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. For Each Sh In Sheets
  6.   With Sh
  7.      If .Name <> "¶°¦XÀÉ" Then
  8.      k = k + 1
  9.         For Each A In .Range(.[A1], .Cells(.Rows.Count, 1).End(xlUp))
  10.             Mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, 11))), ",")
  11.             If IsEmpty(d(A.Value)) Then '¨S¦³ªº¶µ¥Ø´N¥[¤J
  12.                d(A.Value) = d(A.Value) + 1 '­pºâ¦¸¼Æ
  13.                d1(A.Value) = Mystr '¬ö¿ý¤º®e
  14.                ElseIf d1(A.Value) = Mystr Then '¬Û¦P®É­pºâ¦¸¼Æ
  15.                d(A.Value) = d(A.Value) + 1 '­pºâ¦¸¼Æ
  16.                ElseIf d1(A.Value) <> Mystr Or d(A.Value) <> k Then '¦³¤£¦P©Î¬O»P¤u§@ªí¼Æ¶q¤£¦P
  17.                d1.Remove A.Value '²¾°£¶µ¥Ø
  18.             End If
  19.         Next
  20.     End If
  21. End With
  22. Next
  23. With Sheets("¶°¦XÀÉ")
  24. .Cells = ""
  25. For Each ky In d1.keys
  26.    r = r + 1
  27.    .Cells(r, 1).Resize(, 11) = Split(d1(ky), ",") '¼g¤J¶°¦XÀÉ
  28. Next
  29. End With
  30. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ ivan731129 ©ó 2011-3-2 10:22 ½s¿è

´ú¸Õok¤F¡A¤£¹LHsieh¤jªº¥¨¶°¤£ª¾¬O¹ïÀ³­þ¶µ¥Ø·|Åܦ¨³æµ§¸ê®Æ ¡C
¦ý¬O¤Ó·PÁ¨â¦ìªO¥Dªº¼ö¤ß«ü¾É¡A
§Ú­n¦n¦n¬ã¨s¤@¤U¼gªk¤F¡C

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD