Çözüldü Excel Hızlı Düşeyara

[erd5334]

Bu veriler içinde sadece arama işlemi mi yapılacak? yoksa daha sonra farklı işlemler de yapılacak mı

 

[erdalkoccc]

Haluk Bey Dosyanızı 2 Farklı Pc'de deneme yaptım

1. Pc İnternet bilgisayarı

Windows 10 Pro
İşlemci: İ7 6700T 2.80 GHZ
8 Gb Ram
İştim sistemi/Microsoft Office 64 Bit
8 İşlemci

Sonuç : 30 saniye



2. Pc Aslesan Clinte Özel Aselsan Tarafından korumalı Bilgisayar

Windows 8 İşetim sistemi yüklü
16 Gb Ram var
İştim sistemi/Microsoft Office 64 Bit
12 İşlemci

Sonuç : 19 saniye

 

[Haluk]

Birinci bilgisayarın benim Win7 64 Bit üzerindeki 32 Bit Excel yüklü bilgisayardan daha düşük performans göstermesi ilginç...

Vardır bir sebebi ama neyse, bilgi için teşekkürler.

.

 

[erdalkoccc]

Aynen 64 bit olması ve bu kadar yavaş çalışması benimde kafama takıldı büyük ihtimalle pc çok dolu olduğu için arkada çalışan gereğinden fazla program var ve Hardisk/Sdd full dolu büyük ihtimalle bu yüzden yavaş çalıştı

 

[Korhan Ayhan]

Ek bilgi;

Bazı pc'lerin performans ayarı "düşük" olarak ayarlanmış olabiliyor. Belki yavaşlık bundan kaynaklanıyor olabilir.

 

[erdalkoccc]

Korhan Bey dediğiniz gibi de olabilir şirket Pc'leri olunca maalesef müdahale edemiyoruz kısıtlı kullanıcı ile oturum açtığımız için anca "İT" bölümü müdahale edebiliyor böyle durumlara. Pc'ler korumalı olduğu için gereksiz dosyaları silip hafıza açma işlemi bile yapamıyoruz

 

[Haluk]

Ve son alternatif .....

Dosyadaki 500.000 adet veri arasından, VBA kullanarak VLOOKUP (Düşeyara) ile tüm veriler sadece 1-2 saniyede alınıyor.

Dosya linki:

https://drive.google.com/open?id=1OVAFAl4PSnj5FJO68xjhNd8Bg891OdiP

.

 

[erdalkoccc]

Gerçekten inanılmaz bir şey olmuş çok hızlı çalışmakta

 

[Korhan Ayhan]

@Haluk Bey,

#27 nolu mesajdaki dosyanızda da ADO kodları var. Kontrol edebilir misiniz lütfen.

 

[Ziynettin]

@korhan Hocam,

#27. iletideki dosyadaki kodu çalıştırdığımda veriler listelenmiyor. Siz de listeleme oluyor mu?

 

[Haluk]

Korhan Bey, dediğiniz gibi yanlış dosya eklemişim ..... nasıl olmuş, ben de anlamadım.

Dosyayı ilgili mesaja ekledim.

Gerçi şöyle bir durum var; "LOOKUP" sayfasındaki aranılacak veri sayısı çok fazla olursa o kodlar da randımanlı olmayacaktır....

Veri sayısı çok fazla olursa, ADO yöntemi, buna göre çok daha üstün çünkü "LOOKUP" sayfasındaki aranılacak veri sayısı çalışma süresini fazla etkilemez.

Diğer yandan; 10.mesajda Scripting.Dictionary ile yaptığım dosya ADO'ya göre daha yavaş çalışıyor. Belki ben doğru dürüst yazamadım kodu. @Ziynettin bey kendi kodunu benim dosyaya uyarlarsa, daha gerçekçi değerlendirmeler yapabiliriz.

.

 

[Zeki Gürsoy]

Exceldeki listenin Text dosyasına aktarılması mümkünse Text driver hız konusunda Excel driver'a göre daha başarılı.

Şahsi görüşüm, mümkünse sistemden çekilen verilerin Excel'e veya text dosyasına değil de, Access dosyasına çekilmesi yönünde. Access dosyası ile yapılacak sorgular çoğu zaman 1 saniyenin altında olacaktır.

.

 

[MrExcelMr]

@Zeki Gürsoy Bey Selamlar,
Örnek dosya paylaşmanız mümkün mü_? Teşekkür.

 

[Korhan Ayhan]

Haluk Bey,

Sizin son eklediğiniz dosyayı 1 milyon veride 30 bin veriyi arayacak şekilde çalıştırdım. İşlemin tamamlanması 5 dakika sürdü. Veri arttıkça süre uzadı.

Üyemizin eklediği 50 MB boyutundaki dosyada aşağıdaki yapıyı denediğimde 65 saniye civarında sonuç verdi. Alternatif olması açısında paylaşmak istedim.

Sub Fast_Vlookup()
    Dim S1 As Worksheet, Son As Long, Zaman As Double
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
    Zaman = Timer
   
    Set S1 = ActiveSheet
    Son = S1.Cells(S1.Rows.Count, 1).End(3).Row
    S1.Range("F2:J" & S1.Rows.Count).ClearContents
   
    With S1.Range("F2:J" & Son)
        .Formula = "=VLOOKUP(A2,'" & S1.Name & "'!$K:$K,1,0)"
        .Value = .Value
    End With

    Set S1 = Nothing

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
   
    MsgBox "İşleminiz tamamlanmıştır." & Chr(10) & Chr(10) & _
           "İşlem süresi ; " & Format(Timer - Zaman, "0.00") & " Saniye"
End Sub

 

[Ziynettin]

Haluk Bey,

"Scripting.Dictionary " metodu ile yapılan çalışma.

Dosya İndir

Sub test_1()
Dim S1 As Worksheet, s2 As Worksheet
Dim dic As Object, i As Long
Dim a(), b(), c()
t = TimeValue(Now)
Set S1 = Sheets("Data")
Set s2 = Sheets("LOOKUP")
Set dic = CreateObject("scripting.dictionary")
Set dic1 = CreateObject("scripting.dictionary")
a = S1.Range("A2:E" & S1.Cells(Rows.Count, 1).End(3).Row).Value
b = s2.Range("A2:A" & s2.Cells(Rows.Count, 1).End(3).Row).Value
    For i = 1 To UBound(b)
        dic(b(i, 1)) = b(i, 1)
    Next i
    ReDim c(1 To UBound(b), 1 To 4)
    For i = 1 To UBound(a)
        If dic.exists(a(i, 1)) Then
            dic1(a(i, 1)) = i
        End If
    Next i

    For i = 1 To UBound(b)
        For j = 1 To 4
            c(i, j) = a(dic1(b(i, 1)), j + 1)
        Next j
    Next i
s2.Range("K2:N" & s2.Cells(Rows.Count, "K").End(3).Row) = ""
s2.[K2].Resize(UBound(b), 4) = c
    MsgBox CDate(TimeValue(Now) - t), vbInformation
End Sub

 

[Haluk]

Ziynettin Bey;

Son eklediğiniz dosya muhteşem......

500.000 adet veri arasında 30.000 adet veriye ait diğer bilgileri 3 saniyede getirdi. Çok iyi bir performans.

.

 

[Seyit Tiken]

Büyük verilerle çalışmak iş akışı için gerçekten sorun teşkil edebiliyor, bazen salt kodlarla çalışmakta istenilen sonucu vermeyebilir. Excel'in doğasında programcılık mantığı var, bazen esnek ve pratik bir akıl bizi fazlaca enstrümanlardan kurtarabiliyor. Olayı anladığım kadarıyla söylüyorum : Büyük verilerle çalışılıyorsa, Kod destekli PivotTable veya Tablo kullanırsak bu sorunun üstesinden gelemezmiyiz? Performansı düşük bilgisayarımda, TABLO üzerinden Bir Milyon küsur satırda araması ve bulması 1 Saniye sürdü.

 

[Haluk]

Seyit Bey, burada yapılmaya çalışılan iş; sizin verdiğiniz örneğe göre 1 Milyon satırda 30 Bin veriye ait diğer bilgilerin sayfa üzerinde listelenmesi.

Yani; Vlookup-Düşeyara ile 30.000 veriye ait diğer sütunlardaki bilgilerin 1.000.000 veri arasından çekilerek sayfaya yazılması.

@Zeki Gürsoy 'un, verilerin Access tablosuna yazılarak bu işin yapılmasına dair düşüncesiyle ilgili bir deneme yapmadım ama; gördüğüm kadarıyla @Ziynettin Beyin en son kodu bu işi mükemmel yapıyor.

Ama, özellikle bu çapta büyük verilerin ayrı bir dosyada bulunması her zaman emniyetli olacaktır.

.