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

[µo°Ý] §R°£­«½Æ«á¡A´M§ä­«½Æ«È¤á¸¹½X¥[Á`­È

[µo°Ý] §R°£­«½Æ«á¡A´M§ä­«½Æ«È¤á¸¹½X¥[Á`­È

½Ð±Ð¦U¦ì°ª¤â~
²{¦³­Ó¤u§@ªí

¤w¸g¥Î
  1. Sub ²¾°£³æ¸¹­«½Æ()
  2. Set dic = CreateObject("scripting.dictionary")
  3. For i = Range("G65536").End(3).Row To 1 Step -1
  4. If dic.Exists(Cells(i, "G").Value) Then
  5. Rows(i).Delete
  6. Else
  7. dic(Cells(i, "G").Value) = ""
  8. End If
  9. Next i
  10. End Sub
½Æ»s¥N½X
§R°£³æ¸¹­«½Æªº
¦ý¤§«á»Ý­n¨Ì«È¤á½s¸¹(AÄæ)  ­Y¦³¬Û¦Pªº½s¸¹ »Ý­n¥[Á`EÄæ
(GÄæ³æ¸¹¥i©¿²¤ ¥L¬O®³¨ÓÁקK³æ¸¹KEY­«½Æ)
·Q½Ð°Ý¸Ó«ç»òÄ~Äò°µ©O?
³Ì«á·Q§e²{ªºµ²ªG


ªþ¥ó: ´ú¸Õ.zip (510.46 KB)

¦^´_ 1# Changbanana

¬Ý°_¨Ó§Aªº¯Á¤ÞKEY°£¤F«È¤á½s¸¹¤§¥~¡AÁÙ¶·¥]¬ABÄ檺sell§@¬°KEY­È¡A¤~¦X¥G§A·Q§e²{ªºµ²ªG§a¡H

TOP

¦^´_ 2# Kubi

    ©êºp©êºp
    BÄæ§Ñ°O§ï¤F
   KEY ­È¥u¦³AÄæ
    1.png

    10301==>¤p©ú
    10302==>¤j¶¯
    10303==>ßNµØ
   
    ¨ä¥L¸ê®Æ¥i¥H©¿²¤
    ¥D­nKEY ­È¬OA Äæ AÄæ¤@¼Ëªº¥[Á`EÄæ(¦X¨Ö)

TOP

¥»©«³Ì«á¥Ñ Kubi ©ó 2016-9-26 15:34 ½s¿è

¦^´_ 3# Changbanana
¥i¬O½s¸¹10302¥]¬A¤j¶¯»P¤p©ú¡A¦p¦ó°Ï§O¡H

TOP

¦^´_ 4# Kubi


    10302¤@©w¬O¤j¶¯
   
    ÀɮרS§ï¨ì
    §ó·s¹LªºÀÉ®×: ´ú¸Õ.rar (349.5 KB)

TOP

¦^´_ 5# Changbanana
¸Õ¬Ý¬Ý¡C
µ²ªG·|¼g¦bI:OÄæ
  1. Sub test()
  2.     Dim arr()
  3.     Dim dic As Object
  4.     Set dic = CreateObject("scripting.dictionary")
  5.     For i = 2 To Range("A65536").End(3).Row
  6.         If dic.Exists(Cells(i, 1).Value) Then
  7.             dic(Cells(i, 1).Value) = dic(Cells(i, 1).Value) + Cells(i, 5).Value
  8.         Else
  9.             dic(Cells(i, 1).Value) = Cells(i, 5).Value
  10.             n = n + 1
  11.             ReDim Preserve arr(1 To 7, 1 To n)
  12.             For j = 1 To 7
  13.                 arr(j, n) = Cells(i, j).Value
  14.             Next j
  15.         End If
  16.     Next i
  17.     For i = 1 To n: arr(5, i) = dic(arr(1, i)): Next i
  18.     Columns("I:O").ClearContents
  19.     [I1].Resize(1, 7) = [A1].Resize(1, 7).Value
  20.     [I2].Resize(n, 7) = Application.Transpose(arr)
  21. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# Kubi


·PÁÂk¤jªº¤j¤O¬Û§U~
¶]¥Xµ²ªG¬O¥¿½Tªº
·Q½Ð±Ð¤@¤U
  1.       ReDim Preserve arr(1 To 7, 1 To n)
  2.             For j = 1 To 7
  3.                 arr(j, n) = Cells(i, j).Value
  4.             Next j
½Æ»s¥N½X
³o­Ó¥Îªk¥i¤£¥i¥H¸Ñ»¡¤@¤U~ ÁÂÁ±z:)

TOP

¦^´_ 7# Changbanana
²³æ¨Ó»¡´N¬O±q¸ê®ÆºÝÂ^¨ú¬Y¦C¸ê®ÆÀx¦s©ó¤Gºû°}¦C(arr)¤¤³Æ¥Î¡C
¦Ü©ó ReDim ½Ð°Ñ¾\©x¤èª©»¡©ú(´å¼Ð°±¦bReDim¤å¦r¤¤«á¦A«öF1Áä)¡A·|¤ñ§Ú¸ÑÄÀªº§ó²M·¡¡C

TOP

Sub TEST()
Dim Arr, Brr, xD, T&, i&, j%, U&, N&
Arr = Range([A1], [A65536].End(xlUp)(1, 7))
Set xD = CreateObject("scripting.dictionary")
ReDim Brr(1 To UBound(Arr), 1 To 7)
For i = 2 To UBound(Arr)
¡@¡@T = Arr(i, 1): U = xD(T)
¡@¡@If U > 0 Then Brr(U, 5) = Brr(U, 5) + Arr(i, 5): GoTo 101
¡@¡@N = N + 1: U = N: xD(T) = N
¡@¡@For j = 1 To 7: Brr(U, j) = Arr(i, j): Next
101: Next i
If N > 0 Then [J1].Resize(N, 7) = Brr
End Sub

¦P¼ËÅÞ¿è¡A¤£¦P¼gªk¡A¦Û¦æ¥h´¢¼¯¡ã¡ã
¤Wºô¥h§ä ¦r¨åÀÉ ¤Î array°}¦C ªº¸ê®Æ¤Î»¡©ú¡AÁA¸Ñ¤F¡A¨ä¥¦³£Â²³æ¡I
¡@
¡@

TOP

¦^´_ 8# Kubi

¦^´_ 9# ­ã´£³¡ªL


  ¦nªº~~~ÁÂÁÂK¤j©M­ã¤j~~
  §Ú¦AºCºC¬ã¨s
  ÁÂÁ§A­Ìªº¦^ÂÐ^^

TOP

        ÀR«ä¦Û¦b : ¤p¨Æ¤£°µ¡B¤j¨ÆÃø¦¨¡C
ªð¦^¦Cªí ¤W¤@¥DÃD