[發問] 大筆資料篩選，速度很慢(篩選及排列)

lifedidi

大大好：

小弟有一個工時的EXCEL檔，交給同事使用，

起初100筆or500筆資料都可以很順，功能也都ok！

但是當資料數來到10000筆以上時，開始變慢了！

查詢一筆資料要等很久，雖然還是跑得出來..

請教大大有沒有更好的寫法呢？

有幾個要點：
1.資料要篩選出並貼在Sheet2上
2.貼於Sheet2的資料需要照日期排序
3.統計篩選後資料中 R欄 時間的總和

測試檔資料數1.6W筆，公司電腦要跑2~3分鐘，
有點久...請大大小心:L
測試用.rar (480.01 KB)

下載次數: 37

2013-4-30 15:40

GBKEE

回復 1# lifedidi
試試看

1. Private Sub UserForm_Initialize()   '基本設定
   
2.   '  Set 型號 = CreateObject("Scripting.Dictionary")  '耗時:須跑完所有資料列
   
3.     Dim X As Integer
   
4.    ' Sheets("篩選用").[A2:A2].ClearContents
   
5.     With Sheets("SHEET1")
   
6.         'er = .[A65536].End(3).Row
   
7.         'myrng = .Range("A7:R" & er)        '資料數來到10000筆以上時 ***這會佔用記憶體***
   
8.          .Cells(1, .Columns.Count) = ""
   
9.         .Range("D6", .[D6].End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
   
10.         '進階篩選 D欄 不重複資料 Unique:=True 到最後一欄
    
11.         X = 2
    
12.         Do While .Cells(X, .Columns.Count) <> ""
    
13.             ComboBox1.AddItem .Cells(X, .Columns.Count)
    
14.             X = X + 1
    
15.         Loop
    
16.         .Columns(.Columns.Count).EntireColumn.Clear   '清除 最後一欄資料
    
17.     End With
    
18. End Sub
    
19. Private Sub ComboBox1_Change()  '選擇 下拉式選單1 立即顯示總總時間;可不用查詢鈕
    
20.     If ComboBox1.ListIndex = -1 Then   '不在下拉式選單的清單內
    
21.         MsgBox "專案編號 編號 " & ComboBox1 & " 不正確"
    
22.     Else
    
23.         Application.ScreenUpdating = False
    
24.         With Sheets("SHEET1")
    
25.             .Range("a6").AutoFilter Field:=4, Criteria1:=ComboBox1            'AutoFilter:  原資料庫上自動篩選.
    
26.             With .Range("r:r").SpecialCells(xlCellTypeVisible)
    
27.                 TextBox1.Value = Application.Text(Application.Sum(.Cells), "[hh]:mm")
    
28.             End With
    
29.             .AutoFilterMode = False
    
30.         End With
    
31.         Application.ScreenUpdating = True
    
32.     End If
    
33. End Sub
    
34. Private Sub CommandButton3_Click()  '離開鈕
    
35.     Unload UserForm1
    
36. End Sub
    
37. Private Sub UserForm_Terminate()    '重新排列
    
38.     'Sheets("SHEET1").Range("A7:R" & er) = myrng   '重新填上資料耗時
    
39. End Sub

複製代碼

lifedidi

回復 2# GBKEE


    GBKEE大大好：

測試過OK，但是沒有將篩選的資料貼於SHEET2，是否請大大在編一下，

小弟研究一下大大的註解;P 感謝。

Hsieh

回復 3# lifedidi
用你原來的進階篩選
統計部分改用函數計算即可

1. Private Sub CommandButton1_Click()  '查詢鈕
   
2.     Dim d1 As Date, d2 As Date
   
3.     Dim Srng As Range, Crng As Range, Orng As Range
   
4.     Set Srng = Sheets("SHEET1").[A6].CurrentRegion
   
5.     Set Crng = Sheets("篩選用").[A1:A2]
   
6.     With Sheets("SHEET2")
   
7.         Set Orng = .[A6]
   
8.         .[A1:W65536].ClearContents
   
9.         Srng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Crng, CopyToRange:=Orng
   
10.         If .[A7].Value = "" Then
    
11.             MsgBox "沒資料", vbCritical + vbOKOnly, "注意"
    
12.             Exit Sub
    
13.         End If
    
14.     TextBox1.Value = Application.Text(Application.Sum(.[R:R]), "[hh]:mm")
    
15.     End With
    
16. End Sub

複製代碼

lifedidi

回復 4# Hsieh


大大好：

改用大大的語法後，run起來有快很多，但還是有個地方有問題
請問大大一個問題，
我一直交叉測試發現到，
篩選並貼上的語法，很費時間，
程式碼如下：

1. Dim HH As Range, KK As Range, PP As Range
   
2. Set PP = .[A6]
   
3.        .[A1:W65536].ClearContents
   
4. HH.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=KK,

複製代碼

【假設五千筆資料】
run起來，資料一筆一筆貼上，貼到五千筆需要不少時間，
(我將儲存格拉至5000列處，按執行，發現要一段時間才會run到五千筆，因此判斷是這問題)
可是我把一樣的資料原封不動的貼到新sheet檔(new excel file)，在run，不到一秒就跑完了，(........)

(PS)整個excel檔案有約20個userform每個都篩選不同條件，小弟在猜想，這樣會不會造成excel執行時的緩慢？
請益大大意見，小弟現在頭好暈=.= 感謝

GBKEE

回復 5# lifedidi
run起來，資料一筆一筆貼上，貼到五千筆需要不少時間，
為何要 資料一筆一筆貼上???

lifedidi

回復 4# Hsieh

大大好：

因為同事需要【工時加總(單、多條件篩選)】【資料瀏覽】的功能，所以目前小弟先以這功能去加強。

請參考附件。

【建立在相同狀況下】
50,000筆資料 處理數度快
3,000筆資料 處裡數度卻較慢

請大大抽空時測試看看，謝謝。

小弟繼續研究XD

    qq.rar (953.9 KB)

下載次數: 16

2013-5-2 15:32

lifedidi

回復 6# GBKEE


大大好：

不知道是因為公司電腦關係還是其他元素，
如同上面的回覆，

30000筆資料是快速的copy上sheet (上方的回覆內容打錯50000，更正為30000)

3000筆資料卻是一筆一筆的copy上sheet

(一筆一筆貼上是誇飾法..請見諒)

GBKEE

回復 8# lifedidi
不會罷!!  
CPU 雙處理器 3.40 GHz  1GB的RAM
測試 3000筆資料 費時1秒 , 30000筆資料 費時4秒.

1. Private Sub CommandButton1_Click()  '查詢鈕
   
2.     Dim d1 As Date, d2 As Date, T As Date
   
3.     Dim Srng As Range, Crng As Range, Orng As Range
   
4.     T = Time
   
5. ' 程式碼.... 為何不用4# Hsieh 超版的程式碼
   
6. '程式碼....
   
7. '程式碼....
   
8.     TextBox1.Value = Format(hh, "00") & ":" & Format(mm, "00")
   
9.     MsgBox Application.Text(Time - T, "[SS]秒")  '顯示執行過程的時間
   
10. End Sub

複製代碼

如在30000筆資料的工作表上用自動篩選取的資料會更快的

1. Private Sub ComboBox1_Change()  '選擇 下拉式選單1 立即顯示總總時間;可不用查詢鈕
   
2.     Dim T As Date
   
3.     T = Time
   
4.     If ComboBox1.ListIndex = -1 Then   '不在下拉式選單的清單內
   
5.         TextBox1 = ""
   
6.         MsgBox "專案編號 編號 " & ComboBox1 & " 不正確"
   
7.     Else
   
8.         Application.ScreenUpdating = False
   
9.         With Sheets("工時資料庫")
   
10.             .Range("a6").AutoFilter Field:=4, Criteria1:=ComboBox1            'AutoFilter:  原資料庫上自動篩選.
    
11.             With .Range("r:r").SpecialCells(xlCellTypeVisible)
    
12.                 TextBox1.Value = Application.Text(Application.Sum(.Cells), "[hh]:mm")
    
13.             End With
    
14.             .AutoFilterMode = False
    
15.         End With
    
16.         Application.ScreenUpdating = True
    
17.         MsgBox Format(Time - T, " SS 秒")
    
18.     End If
    
19. End Sub

複製代碼

lifedidi

回復 9# GBKEE


    to GBKEE大大

小弟的語法：

【A.於sheet1篩選資料】→【B.於sheet2貼上資料】→【C.sheet2做時間加總】

※小弟在於B.會跑很久，下班回到家也是同樣情形，情形大概如附檔(影片)

3000筆資料.rar (886.57 KB)

下載次數: 10

2013-5-2 18:43

30000筆資料.rar (416.4 KB)

下載次數: 12

2013-5-2 18:43

真邪門XD

GBKEE大大的語法：

【於sheet1篩選資料】→【於sheet1時間加總】

※請問大大篩選資料可以多條件篩選嗎？假設同時要篩選：專案編號、職工編號、日期‧‧‧的條件。(請參考EXCEL檔案第二種查詢)

──────────────────────────────────────────────────────────────
Hsieh大大的程式碼也會有同樣的問題(複製貼上需要時間)，

但是我將時間加總的語法改成Hsieh大大的語法，比較簡潔，哈XD

──────────────────────────────────────────────────────────────
GBKEE大大太神了吧，運算時間竟然也可以算的出來...

下載 (80.37 KB)

2013-5-2 19:23

下載 (140.69 KB)

2013-5-2 19:23