麻辣家族討論版版 - Powered by Discuz! Board

標題: VBA尋找重複 [打印本頁]

作者: Qin 時間: 2018-8-30 00:08 標題: VBA尋找重複

程式碼是在網上取來套用的,

但搜尋速度實在太慢了.

是否有更好的語法可加快搜尋速度

謝謝!

[attach]29291[/attach]

作者: n7822123 時間: 2018-8-30 02:55

回復 1# Qin

[attach]29294[/attach]

快一些些(程序test2)，字典物件也就這樣了，期待有更好的寫法

程序test1=原程序+計時器

話說...."笭葩" 是什麼意思?

Private Sub test2()
t1 = Timer
Application.ScreenUpdating = False
Dim arr As Range, brr()
Dim i As Long, Rn As Long
Dim Dict As Object
On Error Resume Next
Set Dict = CreateObject("scripting.dictionary")
With ActiveSheet
Set arr = Intersect(.UsedRange, .Columns(3))
Rn = arr.Cells.Count
ReDim brr(1 To Rn)
For i = 1 To Rn
Dict(arr(i).Value) = Dict(arr(i).Value) + 1
Next i
For i = 1 To Rn
If Dict(arr(i).Value) <> 1 Then brr(i) = "重覆"
Next i
.Columns(2) = ""
.Range("b1").Resize(Rn, 1) = Application.Transpose(brr)
End With
Application.ScreenUpdating = True
MsgBox "test2共耗時" & Round(Timer - t1, 3) & "秒"
End Sub

複製代碼

作者: 准提部林 時間: 2018-8-30 10:24

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

五萬多筆, 約一秒內可完成~~

作者: a5007185 時間: 2018-8-30 11:32

回復 3# 准提部林

我原本還傻傻地想說用 for迴圈加上countif
然後就當機了 XD

作者: 准提部林 時間: 2018-8-30 11:45

回復 4# a5007185

如果用VBA, 非不得已才去用函數, 儘量避免,
一般的公式解, COUNTIF也是特別慢, 資料一多, 準卡檔!

作者: 准提部林 時間: 2018-8-30 11:52

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

這是基本套路, 用了兩個迴圈, 慢一些些~~
xD(Arr(i, 1) & "") 加 "" 是為防止[純數字]在數值格式與文字格式不同而產生差異!

作者: Qin 時間: 2018-8-30 20:55

回復 3# 准提部林

謝謝准大

跟之前的速度相比 ,現在好像坐上高鐵...

作者: n7822123 時間: 2018-8-31 01:11

回復 6# 准提部林

準大太厲害啦，原來字典物件輸入的key是字串型態的話，速度可以提昇那麼多!!!

如果是非字串，速度整個慢下來!

另外用字典記錄上一個重覆的列號並且一起輸入"重覆"的寫法也很棒，可以只用一個迴圈

作者: GBKEE 時間: 2018-8-31 08:39

本帖最後由 GBKEE 於 2018-8-31 09:12 編輯

回復 7# Qin
輔助欄+排序+IF公式

Sub Ex()
Dim xTime As Date
xTime = Time
Debug.Print Time
With Range("C2:C" & [C2].End(xlDown).Row) '資料欄
.Offset(, 1) = "=ROW()" '輔助欄
.CurrentRegion.Sort KEY1:=.Cells(1), Header:=xlYes '排序以資料欄為主鍵
.Offset(, -1) = "=IF(OR(RC[1]=R[-1]C[1], RC[1]=R[1]C[1]),""重複"","""")" '要顯示重複的欄寫上公式
.CurrentRegion.Value = .CurrentRegion.Value '將公式轉為數值
.CurrentRegion.Sort KEY1:=.Cells(1, 2), Header:=xlYes '排序以輔助欄為主鍵 :還原資料欄原有的排列
.Offset(, 1) = "" '清除輔助欄
End With
Debug.Print Time
MsgBox Application.Text(Time - xTime, ["計時 ss 秒"])
End Sub

複製代碼

作者: 千暉尋 時間: 2018-9-27 15:07

回復准提部林

準大太厲害啦，原來字典物件輸入的key是字串型態的話，速度可以提昇那麼多!!!

如果 ...
n7822123 發表於 2018-8-31 01:11

3個語法,可看懂2個,唯獨准大的第1個程式碼,我想了好久,針對以下語法
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
還是想不透為何如此就能判斷出重覆與否,那位大大可以幫忙解說一下嗎?

作者: jackyq 時間: 2018-9-28 08:37

本帖最後由 jackyq 於 2018-9-28 08:40 編輯

回復 10# 千暉尋

你看不懂是因為多了2段藍字跳板 , 跳板內又存在著 U 在 -1, 列位置二值之間來回交替切換

你可以把跳板移除 , 等效如下

Sub test_01a()
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(i, 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

作者: 准提部林 時間: 2018-9-28 10:01

本帖最後由准提部林於 2018-9-28 10:04 編輯

回復 10# 千暉尋

U=xD(T) 先取出字典的ITEM值, 若T值還未丟進字典, U 值為0(或"")

U=0  表示尚未掃過的KEY, 先給值 i 保留後用 (相當于列號)
U>0  表示是第2次掃到的, 其ITEM值是上一次保留的"列號", 順勢將上一次的位置標示"重覆", 再將 ITEM 改成 -1
U<0  表示已掃過2次以上, 直接標示"重覆"

作者: 千暉尋 時間: 2018-9-28 16:45

回復千暉尋

U=xD(T) 先取出字典的ITEM值, 若T值還未丟進字典, U 值為0(或"")

U=0 表示尚未掃過的 ...
准提部林發表於 2018-9-28 10:01

謝謝準大的說明,簡明扼要,我曾試過,第1次出現U值都是0 ,想不通為何,還在鑽牛角尖想說0到底是KEY值還是ITEM值,原來兩者都不是,是根本還沒寫入,一語警醒夢中人!

作者: Qin 時間: 2018-10-3 23:08

回復 12# 准提部林

尋找同一欄重覆資料

(只顯示多個重覆中的其中的1個)

[attach]29491[/attach]

作者: 准提部林 時間: 2018-10-4 11:20

回復 14# Qin

只標示有重覆的[最後一個]

Sub test_03()
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 = UBound(Arr) To 1 Step -1
T = Arr(i, 1): U = xD(T): Arr(i, 1) = ""
If U > 0 Then Arr(U, 1) = "Rept": xD(T) = -1
If U = 0 Then xD(T) = i
Next i
[B2].Resize(UBound(Arr)) = Arr
MsgBox Timer - TM
End Sub

作者: Qin 時間: 2018-10-7 14:24

回復 15# 准提部林

這是原本想要的搜尋結果, 但自己發錯的問題...
無論如何, 謝謝准大..

Rept code
40000001
Rept 40000001
40000002
Rept 40000002
40000003
40000004
Rept 40000004
40000005
Rept 40000005
40000006
Rept 40000006
Rept 40000006
Rept 40000006

Sub test_04()
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 = UBound(Arr) To 1 Step -1
T = Arr(i, 1): U = xD(T): Arr(i, 1) = ""
If U > 0 Then Arr(U, 1) = "Rept": xD(T) = -1: U = -0
If U = 0 Then xD(T) = i
Next i
[B2].Resize(UBound(Arr)) = Arr
MsgBox Timer - TM
End Sub

作者: Andy2483 時間: 2023-5-16 14:46

回復 3# 准提部林

謝謝前輩
後學藉此帖學習前輩的方案,方案學習心得註解如下,請前輩在指導

執行前:
[attach]36371[/attach]

執行結果:
[attach]36372[/attach]

Option Explicit
Sub test_01()
Dim Arr, xD, i&, T$, U&, TM
'↑宣告變數
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
'↑令xD變數是字典
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
'↑令Arr變數是二維陣列,以儲存格值帶入陣列中
For i = 1 To UBound(Arr)
'↑設順迴圈
T = Arr(i, 1): U = xD(T): Arr(i, 1) = ""
'↑令T變數是 Arr陣列值(關鍵字),
'令U變數是關鍵字查xD字典的item值
'令清除掉Arr陣列裡的值
If U > 0 Then Arr(U, 1) = "重覆": xD(T) = -1: U = -1
'↑如果U變數大於0,代表此關鍵字在Arr陣列i列值還不是 "重覆",
'也就是初次檢查到重覆!就令Arr陣列U變數列值是 "重覆"字串,
'令此關鍵字的item改為-1,令U變數是 -1
If U < 0 Then Arr(i, 1) = "重覆"
'↑如果U變數小於0,代表Arr陣列這迴圈列值是重複的,
'就令Arr陣列i迴圈列值是 "重覆"字串
If U = 0 Then xD(T) = i
'↑如果U變數是 0,代表此關鍵字是第1次出現,
'只以此關鍵字當key,item是i迴圈數要納入xD字典裡就好
Next i
[B2].Resize(UBound(Arr)) = Arr
'↑令Arr陣列值從[B2]擴展的儲存格中寫入
MsgBox Timer - TM
End Sub

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)