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

[erdalkoccc]

Arkadaşlar Merhaba

Benim elimde 3.000.000 data var 3 sheette ben bu 3.000.000 dataya düşeyara yapmak istiyorum ve 3.000.000 "data içinde aratılacak" 30.000 kayıt var.
Düşeyara işlemini başlatınca 1 gün den fazla kadar sürüyor sonuçlanması acaba ben bunu makro ile döngü falan kullanarak hızlandırabilir miyim sizin bilginiz var mı bu konuda.

Teşekkür Ederim Şimdiden.
İyi Çalışmalar.

 

[Haluk]

Veri ve arama yapılacak kayıt adetleriniz biraz fazlaymış ama örnek dosya eklerseniz, bakabiliriz ....

.

 

[erdalkoccc]

Dosyanın örnegi budur düşeyarayı aşağı kadar çekince çok uzun sürüyor işlem

https://drive.google.com/open?id=17vI6bAyJsFPiL6o1hFwPrWo6vR3LM0sR

 

[Haluk]

Örnek dosyanız yaklaşık 50 MB civarında ve gerçekten Excel'i en çok yoran formüllerden biri olan VLOOKUP-DÜŞEYARA bir çok sütunda fazla sayıda kullanılmış.

Dosyanın açılması bile benim PC'de 2-3 dakika sürdü. Eğer bir de 3.000.000 veri arasında bu formülleri kullanmaya kalksam herhalde işlemciden dumanlar çıkardı.

Öncelikle; Excel'i bu tür bir veritabanı olarak kullanmanız yanlış bir seçim olmuş... Formüller ve verilerin bu yüklü aynı dosyada olması, çok büyük risk.

Diyelim ki Excel'den başka imkanınız yok (pek sanmıyorum ama, diyelim ki öyle olsun....) ilk önce dosyayı *.xlsb olarak binary formatta kaydedin. Ben yapınca, dosya boyutu 50 MB'dan, 7 MB'a düştü. Böylece, dosyanın kaydetme, açılma, kapanma hızları artacaktır.

Daha sonra, kullanacağınız VLOOKUP formülünün sınırlarını A:A, K:K gibi değil de sınırlayarak kullanmaya çalışın.

Örneğin, F2 hücresindeki formülü aşağıdaki gibi kullanın...

=VLOOKUP(A2;$K2:$K32000;1;0)

Bunları deneyin, gelişmelere göre duruma bakarız...

.

 

[erdalkoccc]

Aslında örnek Dosyam 50 MB değildi "950 MB" di ben verileri silerek küçülttüm datayı. Normalde 950 MB' da çalışıyorum.
Aslında Sql Servere Yüklesem veri tabanımı daha hızlı çalışabilir fakat Şirket bilgisayarında çalıştığım için kısıtlı kulanıcı ile oturum açmaktayız ve Sql server'a giriş yetkim olmadığı için Sql servere yükleme yapamıyorum tek çare düşeyara kalıyor.
Dediğinizi uyguladım biraz daha hızlandı.
Teşekkür ederim.

 

[Haluk]

Ben de tam onu demek istemistim.

SQL Server falan gibi araclar kullanmaniz gerekir. Hele bir de dosya boyutu 950 MB ise , Excel ve VBA ile tam olarak randiman alamazsiniz.

Su anda bilgisayar basinda degilim, daha sonra bir ara size VBA ile hazirlanmis bir ornek dosya eklerim. Eger VBA ile araniz iyiyse, kendinize uyarlayabilirsiniz.

.

 

[erdalkoccc]

Aynen Biraz iyi Vba ile aram İnternet'te bulduğum kodları hep kendi dosyalarıma uyarlaya uyarlaya öğrendim biraz genelde kayıt makrosu kullanıyorum tüm işlerimi görüyordu. Siz örneği yollarsanız ben kendime uyarlayabilirim kodu. Çok Teşekkür Ederim.

 

[Ziynettin]

VBA ile;

Kodu herhangi çalışma sayfasında deneyiniz.

Sub test()
Application.ScreenUpdating = False
Z = TimeValue(Now)
Set dc1 = CreateObject("scripting.dictionary")
a = Range("K2:K" & Cells(Rows.Count, "K").End(3).Row).Value
    For i = 1 To UBound(a)
        dc1(a(i, 1)) = a(i, 1)
    Next i
b = Range("A2:E" & Cells(Rows.Count, 1).End(3).Row).Value
ReDim c(1 To UBound(b), 1 To 5)
    For i = 1 To UBound(b)
        For j = 1 To 5
            c(i, j) = dc1(b(i, j))
        Next j
    Next i
[F2].Resize(UBound(b), 5) = c
Application.ScreenUpdating = True
MsgBox CDate(TimeValue(Now) - Z)
End Sub

 

[erdalkoccc]

Çok teşekkür ederim ayırdığınız zaman ve ilgi için sorunsuz bir şekilde hızlı çalışıyor kod.

 

[Haluk]

Ben de Ziynettin Bey gibi Scripting.Dictionary nesnesinin ADO'ya göre daha hızlı olacağını düşünmüştüm.

Ekli dosyada 100.000 veri x 5 Sütun = 500.000 veri içerisinden deneme amaçlı yapılan test sonucunda 30 Adet x 5 Sütun veriler yaklaşık 10 saniye süre içinde alınmaktadır. Süre tabii ki; Excel'in 32 veya 64 bit mimari olup olmamasına ve bilgisayarın donanım konfügürasyonuna göre değişkenlik gösterir.

Sorgulanacak veri adedi süreyi çok fazla etkilemez.

.

 

[erdalkoccc]

Çok Teşekkür ederim size de fakat rar dosyanızı açamadım bağlantı yollayabilir misiniz ?

 

[Haluk]

Link aşağıdadır....

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

Aslında "Altın Üye" olursanız, dosya indirme ve yükleme imkanlarından yararlanırsınız...

.

 

[erdalkoccc]

Çok teşekkür ederim Haluk Bey ayırdığınız zaman ve ilgi için sizin yazmış olduğunuz kodda sorunsuz bir şekilde hızlı çalışıyor ellerinize sağlık.

 

[Haluk]

Verdiğim dosya sizin PC'de kaç saniyede sonuçları getirdi?

.

 

[erdalkoccc]

Verdiginizi kodu kendi excelime uyarladım 5.000.000 data içinde 30.000 bin kayıtı 20 saniye civarında buldu çok iyi bir sonuç.
Aynı işlemi düşeyara ile yapsaydım cok uzun bir süre sonucu beklerdim. Bu arada pc 64 bit win 10 8 gb ram i5 işlemci 7.nesil

 

[Haluk]

PC donanımız bayağı güzel, söz konusu süreyi en çok etkileyen unsurlardan biri de; kullandığınız Excel'in 64 Bit olması.

.

 

[erdalkoccc]

Aynen 64 bit olması çok etkiliyor. Tekrardan her şey için teşekkür ederim

 

[Haluk]

Benim Windows 64 Bit üzerindeki Excel 32 Bit ile 500.000 adet veriyle yaptığım deneme, yaklaşık 4 dakika (240 saniye) sürdü.

Piyangodan para çıksa da, PC ve Excel'i güncelleyebilsem ...

.

 

[erdalkoccc]

Sizin baya bi sürmüş güncelleye bilirseniz 64 bit çok iyi olur çok hız katar size inşallah bulursunuz piyangoyu

 

[Haluk]

Piyangodan paranın çıkacağı yok, başımızın çaresine bakalım diye düşünerek ADO/SQL ile ekteki dosyayı hazırladım....

Benim PC'de bu dosyadaki 500.000 adet veri sorgulanarak, yaklaşık 25 saniyede sonuçlar alınmaktadır.... yani, yukarıdaki 18 No'lu mesajımdaki yönteme göre %90 daha hızlı.

Dosyada işin esprisi; LOOKUP sayfasındaki sorgulanacak veriler ilk önce yine ADO ile sıralanmakta, daha sonra yine ADO/SQL ile hazırlanan bir query'nin içinde bir Subquery kullanarak 5 sütunun verileri alınmaktadır.

Sizin PC'deki çalışma süresini cidden merak ediyorum...

Dosyanın indirme bağlantısı:

https://drive.google.com/open?id=1MisaRAn2iOFK6gaiST0QgXFXNY2-ejiA

.