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

VBA´M§ä­«½Æ

VBA´M§ä­«½Æ

µ{¦¡½X¬O¦bºô¤W¨ú¨Ó®M¥Îªº,

    ¦ý·j´M³t«×¹ê¦b¤ÓºC¤F.

    ¬O§_¦³§ó¦nªº»yªk¥i¥[§Ö·j´M³t«×

    ÁÂÁÂ!

    Rept.rar (525.45 KB)

¦^´_ 1# Qin

Rept+1.rar (446.33 KB)

§Ö¤@¨Ç¨Ç(µ{§Çtest2)¡A¦r¨åª«¥ó¤]´N³o¼Ë¤F¡A´Á«Ý¦³§ó¦nªº¼gªk

µ{§Çtest1=­ìµ{§Ç+­p®É¾¹

¸Ü»¡...."Öظ´" ¬O¤°»ò·N«ä?
  1. Private Sub test2()
  2.     t1 = Timer
  3.     Application.ScreenUpdating = False
  4.     Dim arr As Range, brr()
  5.     Dim i As Long, Rn As Long
  6.     Dim Dict As Object
  7.     On Error Resume Next
  8.     Set Dict = CreateObject("scripting.dictionary")
  9.     With ActiveSheet
  10.        Set arr = Intersect(.UsedRange, .Columns(3))
  11.        Rn = arr.Cells.Count
  12.         ReDim brr(1 To Rn)
  13.         For i = 1 To Rn
  14.           Dict(arr(i).Value) = Dict(arr(i).Value) + 1
  15.         Next i
  16.         For i = 1 To Rn
  17.             If Dict(arr(i).Value) <> 1 Then brr(i) = "­«ÂÐ"
  18.         Next i
  19.         .Columns(2) = ""
  20.         .Range("b1").Resize(Rn, 1) = Application.Transpose(brr)
  21.     End With
  22.     Application.ScreenUpdating = True
  23.     MsgBox "test2¦@¯Ó®É" & Round(Timer - t1, 3) & "¬í"
  24. End Sub
½Æ»s¥N½X

TOP

Sub test_01()
Dim Arr, xD, i&, T$, U&, TM
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
For i = 1 To UBound(Arr)
    T = Arr(i, 1):  U = xD(T):  Arr(i, 1) = ""
    If U > 0 Then Arr(U, 1) = "­«ÂÐ": xD(T) = -1: U = -1
    If U < 0 Then Arr(i, 1) = "­«ÂÐ"
    If U = 0 Then xD(T) = i
Next i
[B2].Resize(UBound(Arr)) = Arr
MsgBox Timer - TM
End Sub

¤­¸U¦hµ§, ¬ù¤@¬í¤º¥i§¹¦¨~~

TOP

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

§Ú­ì¥»Áٶ̶̦a·Q»¡¥Î for°j°é¥[¤Wcountif  
µM«á´N·í¾÷¤F  XD

TOP

¦^´_ 4# a5007185


¦pªG¥ÎVBA, «D¤£±o¤w¤~¥h¥Î¨ç¼Æ, ¾¨¶qÁקK,
¤@¯ëªº¤½¦¡¸Ñ, COUNTIF¤]¬O¯S§OºC, ¸ê®Æ¤@¦h, ·Ç¥dÀÉ!

TOP

Sub test_02()
Dim Arr, Brr, xD, i&, TM
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
ReDim Brr(1 To UBound(Arr), 0)
For i = 1 To UBound(Arr)
    xD(Arr(i, 1) & "") = xD(Arr(i, 1) & "") + 1
Next i
For i = 1 To UBound(Arr)
    If xD(Arr(i, 1) & "") > 1 Then Brr(i, 0) = "­«ÂÐ"
Next
[B2].Resize(UBound(Arr)) = Brr
MsgBox Timer - TM
End Sub

³o¬O°ò¥»®M¸ô, ¥Î¤F¨â­Ó°j°é, ºC¤@¨Ç¨Ç~~
xD(Arr(i, 1) & "")  ¥[ "" ¬O¬°¨¾¤î[¯Â¼Æ¦r]¦b¼Æ­È®æ¦¡»P¤å¦r®æ¦¡¤£¦P¦Ó²£¥Í®t²§!

TOP

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

  ÁÂÁ­ã¤j

   ¸ò¤§«eªº³t«×¬Û¤ñ ,²{¦b¦n¹³§¤¤W°ªÅK...

TOP

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


·Ç¤j¤Ó¼F®`°Õ¡A­ì¨Ó¦r¨åª«¥ó¿é¤Jªºkey¬O¦r¦ê«¬ºAªº¸Ü¡A³t«×¥i¥H´£ª@¨º»ò¦h!!!

¦pªG¬O«D¦r¦ê¡A³t«×¾ã­ÓºC¤U¨Ó!   

¥t¥~¥Î¦r¨å°O¿ý¤W¤@­Ó­«ÂЪº¦C¸¹¨Ã¥B¤@°_¿é¤J"­«ÂÐ"ªº¼gªk¤]«Ü´Î¡A¥i¥H¥u¥Î¤@­Ó°j°é

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-8-31 09:12 ½s¿è

¦^´_ 7# Qin
»²§UÄæ+±Æ§Ç+IF¤½¦¡
  1. Sub Ex()
  2.     Dim xTime As Date
  3.     xTime = Time
  4.     Debug.Print Time
  5.     With Range("C2:C" & [C2].End(xlDown).Row) '¸ê®ÆÄæ
  6.         .Offset(, 1) = "=ROW()"    '»²§UÄæ
  7.         .CurrentRegion.Sort KEY1:=.Cells(1), Header:=xlYes    '±Æ§Ç¥H¸ê®ÆÄ欰¥DÁä
  8.         .Offset(, -1) = "=IF(OR(RC[1]=R[-1]C[1], RC[1]=R[1]C[1]),""­«½Æ"","""")"    '­nÅã¥Ü­«½ÆªºÄæ¼g¤W¤½¦¡
  9.         .CurrentRegion.Value = .CurrentRegion.Value         '±N¤½¦¡Âର¼Æ­È
  10.         .CurrentRegion.Sort KEY1:=.Cells(1, 2), Header:=xlYes  '±Æ§Ç¥H»²§UÄ欰¥DÁä :ÁÙ­ì¸ê®ÆÄæ­ì¦³ªº±Æ¦C
  11.         .Offset(, 1) = ""   '²M°£»²§UÄæ
  12.     End With
  13.     Debug.Print Time
  14.     MsgBox Application.Text(Time - xTime, ["­p®É ss ¬í"])
  15. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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


·Ç¤j¤Ó¼F®`°Õ¡A­ì¨Ó¦r¨åª«¥ó¿é¤Jªºkey¬O¦r¦ê«¬ºAªº¸Ü¡A³t«×¥i¥H´£ª@¨º»ò¦h!!!

¦pªG ...
n7822123 µoªí©ó 2018-8-31 01:11

3­Ó»yªk,¥i¬ÝÀ´2­Ó,°ß¿W­ã¤jªº²Ä1­Óµ{¦¡½X,§Ú·Q¤F¦n¤[,°w¹ï¥H¤U»yªk
For i = 1 To UBound(Arr)
    T = Arr(i, 1):  U = xD(T):  Arr(i, 1) = ""
    If U > 0 Then Arr(U, 1) = "­«ÂÐ": xD(T) = -1: U = -1
    If U < 0 Then Arr(i, 1) = "­«ÂÐ"
    If U = 0 Then xD(T) = i
Next i
ÁÙ¬O·Q¤£³z¬°¦ó¦p¦¹´N¯à§PÂ_¥X­«ÂлP§_,¨º¦ì¤j¤j¥i¥HÀ°¦£¸Ñ»¡¤@¤U¶Ü?

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD