Hızlı Düşeyara

ccuneyt13 · 15 Şubat 2023

Merhaba,
Başlığı özellikle @Korhan Ayhan Bey'in dikkatini çekmesi açısında "Hızlı" olarak açtım

Ekte gerçek dosyamdaki aynı değerlerin olduğu bir sayfadan diğerine yapılan düşeyara işlemi var.
(sütun ve sayfa isimleri farklı gerçek dosyada. Ayrıca 80bin ila 100bin civarı veri oluyor.)

Bunu VBA ile aşağıdakine benzer şekil de hızlı yapabilmem için yardımcı olabilir misiniz.

Soru - VLookup / Düşeyara Daha hızlı kod yada alternatif

Merhaba, Aşağıdaki kod ile kendi dosyamda şunu yapmaya çalışıyorum. Yaklaşık 300.000 satırın üzerinde veriden ilgili sütundan düşeyara ile fatura eşleştirmesi yapmaya çalışıyorum. Ancak satır sayısı çok olduğu için excel ciddi anlamda ağırlaşıyor. Aşağıdaki kodlar ile alabildiğim sonucu...

www.excel.web.tr

Buradakini uyarlamaya çalıştım ama yarım yamalak bilgimle beceremedim.

Teşekkür ederim.

Saygılarımla.

bmutlu966 · 15 Şubat 2023

Deneyin. İşinizi görecektir.

ccuneyt13 · 16 Şubat 2023

bmutlu966' Alıntı:
Deneyin. İşinizi görecektir.

Sn. @bmutlu966 gerçek verimde denedim yaklaşık 20 sn de tamamladı.

Bende kendi çapımda aşağıdaki gibi yapmıştım oda 10-15 sn civarında bitiriyor.

Kod:

Sub Duseyara()
   Dim Satirsay, S1
    Satirsay = Sheets("Sayfa1").Cells(Rows.Count, 1).End(xlUp).Row
    Set S1 = ThisWorkbook.Sheets("Sayfa1")
    With Sheets("Sayfa1").Range("CC2:CC" & Satirsay)
        .Formula = "=VLookup(CB2, 'ÖDEME'!C:D, 2, 0)"
        .Copy
        .PasteSpecial xlPasteValues
        .Replace "#N/A", Replacement:=0
        Application.CutCopyMode = False
    End With
    End Sub

Katkı ve emeğiniz için teşekkür ederim.
1-2 saniyede yapabilmeye imkan olur mu acaba diye düşünmeden edemiyorum

Saygılar.

Korhan Ayhan · 16 Şubat 2023

1 saniye bile çok aslında...

ccuneyt13 · 16 Şubat 2023

Korhan Ayhan' Alıntı:
1 saniye bile çok aslında...

Sayın @Korhan Ayhan hocam daha önce ETOPLA için çok uzun sürede yaparken sizin değerli katkınız ile 2 sn gibi bir sürede tamamlanan bir kod iletmiştiniz.

VBA ile yapılan ETOPLA İşleminin Hızını Arttırma Yolu

Merhaba, Bir listem var, ve listemde TC ve Borç Tutarı bilgileri var, Aşağıdaki kod ile TC'ye göre borç tutarlarını etopla ile toplatıyorum, Sorunum şu; Listem 100 bin satırdan oluşuyor ve bu kod yaklaşık 45 dakika sürüyor. Bu noktada yaptığım işlemi kısa sürede (bir kaç dakika da) yapabilecek...

www.excel.web.tr

Merakım bu sebepledir ki acaba bunun içinde olabiliyor mu.
"Oldu olacak düğmeye bile basmadan yapsın" gibi bir arsızlık yaptığımı düşünmeyin lütfen sayın hocam

Mevcut süre makul tabiki.
Yöntemi merak etmiştim açıkçası.
Selam ve Saygılarımla

bmutlu966 · 16 Şubat 2023

Korhan Ayhan' Alıntı:
1 saniye bile çok aslında...

Sanırım bu kodlar Korhan hocamıza ait. Ben sadece uyarladım. Deneyiniz.

Kod:

Sub getir()
  
    Zaman = Timer
    
    Set o = Sheets("Ödeme")
    Set s = Sheets("Sayfa1")
    Range("B2:B65000").ClearContents

    Son = o.Cells(o.Rows.Count, 1).End(3).Row
    Data = o.Range("A2:B" & Son).Value
    
    Son = s.Cells(s.Rows.Count, 1).End(3).Row
    aranan = s.Range("A2:A" & Son).Value
    
    ReDim Liste(1 To UBound(aranan), 1 To 2)
    
    For x = LBound(aranan) To UBound(aranan)
        Kontrol = False
        For y = LBound(Data) To UBound(Data)
            If aranan(x, 1) = Data(y, 1) Then
                Say = Say + 1
                Liste(Say, 1) = Data(y, 2)
                Kontrol = True
                Exit For
            End If
            If Say = UBound(Liste, 1) Then GoTo 10
        Next
        If Kontrol = False Then
            Say = Say + 1
            Liste(Say, 1) = "Yok"
        End If
    Next
    
10  If Say > 0 Then
        Son = s.Cells(s.Rows.Count, 1).End(3).Row
        s.Range("B2:B" & Son).ClearContents
        s.Range("B2").Resize(Say, 2) = Liste
    End If
    
    MsgBox "Islem Tamam" & Chr(10) & Chr(10) & _
           "Süre ; " & Format(Timer - Zaman, "0.00") & " Saniye", vbInformation
End Sub

ccuneyt13 · 16 Şubat 2023

bmutlu966' Alıntı:
Sanırım bu kodlar Korhan hocamıza ait. Ben sadece uyarladım. Deneyiniz.

Kod:

Sub getir() Zaman = Timer Set o = Sheets("Ödeme") Set s = Sheets("Sayfa1") Range("B2:B65000").ClearContents ..... End Sub

Sn. @bmutlu966 hiç bir değişiklik yapmadan örnek dosyaya modül ile ekledim.
Her iki sayfadaki veriyi arttırdım sadece, yaklaşık 90 bin.
10 dakika çalıştı, sonlandırdım.
Verdiğiniz ilk yöntemle devam edeceğim. Alakanıza teşekkür ederim.
Saygılar.

Korhan Ayhan · 17 Şubat 2023

Deneyiniz.

C++:

Option Explicit

Sub Fast_Vlookup()
    Dim S1 As Worksheet, S2 As Worksheet, My_Array As Object
    Dim My_Data As Variant, X As Long, Process_Time As Double
   
    Process_Time = Timer
   
    Application.ScreenUpdating = False
   
    Set S1 = Sheets("Sayfa1")
    Set S2 = Sheets("Ödeme")
    Set My_Array = VBA.CreateObject("Scripting.Dictionary")
   
    S1.Range("B:B").ClearContents
   
    My_Data = S2.Range("A1").CurrentRegion.Value
   
    For X = LBound(My_Data, 1) To UBound(My_Data, 1)
        My_Array.Item(My_Data(X, 1)) = My_Data(X, 2)
    Next
   
    My_Data = S1.Range("A1").CurrentRegion.Value
   
    ReDim My_List(1 To UBound(My_Data, 1), 1 To 1)
   
    For X = LBound(My_Data, 1) To UBound(My_Data, 1)
        If My_Array.Exists(My_Data(X, 1)) Then
            My_List(X, 1) = My_Array.Item(My_Data(X, 1))
        Else
            My_List(X, 1) = "YOK"
        End If
    Next
   
    S1.Range("B1").Resize(UBound(My_Data, 1)) = My_List
   
    My_Array.RemoveAll
    Erase My_Data
    Erase My_List
   
    Set S1 = Nothing
    Set S2 = Nothing
    Set My_Array = Nothing
   
    Application.ScreenUpdating = True
   
    MsgBox "İşleminiz tmamlanmıştır." & vbCrLf & _
           "İşlem süresi ; " & Format(Timer - Process_Time, "0.00") & " Saniye"
End Sub

ccuneyt13 · 17 Şubat 2023

Korhan Ayhan' Alıntı:
Deneyiniz.

Sayın @Korhan Ayhan Hocam 1 saniye bile fazla derken boşuna dememişsiniz.
0,97 saniyede sonuç verdi.
Hocam elinize sağlık.
Bu yöntem için daha fazla bilgi edinmek istiyorum. Normal fonksiyon ile yapılan uzun sürerken sizin bu yöntemde hızlı yapmasının temel farkını ve farklı kodları uyarlaya bilmek ve araştırmak için konu yada başlık tavsiye edebilir misiniz.
Tekrar çok teşekkür ederim,
Saygılar.

Korhan Ayhan · 17 Şubat 2023

Güzel sonuç..

Bu yöntemde hem bilgisayarın Ram'ini kullandık, hem de tabiri caizse roket gibi hızlı çalışan Scripting.Dictionary nesnesini kullandık. Forumda zaten benim ve diğer arkadaşların bolca paylaşımları bulunuyor.

Formüller dinamik çalıştıkları için dosyada fazla yer kaplarlar. Hele ki dosyanızdaki veri sayısı ve kullanılan sütun sayısı fazla ise yavaşlama sorunları kaçınılmazdır.

Bunu aşağıdaki yöntemlerle basitçe test edebilirsiniz...

Boş bir excel dosyasını masaüstüne kayıt ettim. Dosya boyutu 14,4 KB
Sonra aynı dosyayı açıp A sütunundaki tüm hücrelere yani 1048576 hücreye AAAAA (5 karakter) yazıp dosyayı kayıt ettim. Dosya boyutu 5,43 MB
Sonra aynı dosyayı açıp A1 hücresine =TOPLA(B1:Z1) formülü yazıp tüm sütuna sürükledim. Dosya boyutu 9,46 MB

Farkındaysanız daha hiç biçimlendirme işlemi yapmadık.

Gördüğünüz gibi dosya boyutunda her karakterin her işlemin önemi çok büyük oluyor. Bu sebeple tablo kurgularken bol keseden atmamak gerekiyor.

Konuyla ilgili kaynak olarak ben genellikle yabancı forumları kullanıyorum. Translate aracılığı ile sayfaları Türkçe'ye çevirip inceliyorum ve denemeler yapıyorum.

Burada önemli detay ise aramak istediğiniz anahtar kelimedir.

Örnek verecek olursam mesela bana çok hızlı çalışan bir ETOPLA işlemi gerekiyorsa ve bu işlemi makro ile yapmak istiyorsam Google ile "fast sumif vba code" ifadesi ile arama yapıyorum.

Bu şekilde biraz emekle kolayca ilerleyebilirsiniz.

Kod yazmayı bilmiyorsanız artık yapay zeka yardımıyla bu işlerde kolaylaştı. Linkteki gibi yöntemleri deneyebilirsiniz.

ChatGPT ve Excel | Yapay Zeka

ChatGPT yapay zeka sistemi ve excel denemeleri.

www.excel.web.tr

ccuneyt13 · 17 Şubat 2023

Korhan Ayhan' Alıntı:
Güzel sonuç..

Bu yöntemde hem bilgisayarın Ram'ini kullandık, hem de tabiri caizse roket gibi hızlı çalışan Scripting.Dictionary nesnesini kullandık. Forumda zaten benim ve diğer arkadaşların bolca paylaşımları bulunuyor.

Formüller dinamik çalıştıkları için dosyada fazla yer kaplarlar. Hele ki dosyanızdaki veri sayısı ve kullanılan sütun sayısı fazla ise yavaşlama sorunları kaçınılmazdır.

Bunu aşağıdaki yöntemlerle basitçe test edebilirsiniz...

Boş bir excel dosyasını masaüstüne kayıt ettim. Dosya boyutu 14,4 KB
Sonra aynı dosyayı açıp A sütunundaki tüm hücrelere yani 1048576 hücreye AAAAA (5 karakter) yazıp dosyayı kayıt ettim. Dosya boyutu 5,43 MB
Sonra aynı dosyayı açıp A1 hücresine =TOPLA(B1:Z1) formülü yazıp tüm sütuna sürükledim. Dosya boyutu 9,46 MB

Farkındaysanız daha hiç biçimlendirme işlemi yapmadık.

Gördüğünüz gibi dosya boyutunda her karakterin her işlemin önemi çok büyük oluyor. Bu sebeple tablo kurgularken bol keseden atmamak gerekiyor.

Konuyla ilgili kaynak olarak ben genellikle yabancı forumları kullanıyorum. Translate aracılığı ile sayfaları Türkçe'ye çevirip inceliyorum ve denemeler yapıyorum.

Burada önemli detay ise aramak istediğiniz anahtar kelimedir.

Örnek verecek olursam mesela bana çok hızlı çalışan bir ETOPLA işlemi gerekiyorsa ve bu işlemi makro ile yapmak istiyorsam Google ile "fast sumif vba code" ifadesi ile arama yapıyorum.

Bu şekilde biraz emekle kolayca ilerleyebilirsiniz.

Kod yazmayı bilmiyorsanız artık yapay zeka yardımıyla bu işlerde kolaylaştı. Linkteki gibi yöntemleri deneyebilirsiniz.

ChatGPT ve Excel | Yapay Zeka

ChatGPT yapay zeka sistemi ve excel denemeleri.

www.excel.web.tr

Sayın @Korhan Ayhan hocam, hem kod için emek verdiniz hem de lütfedip detaylı anlatımda bulundunuz.
Sabrınız, emeğiniz ve nezaketiniz için çok teşekkür ederim,
Saygılar

Hızlı Düşeyara

ccuneyt13

Soru - VLookup / Düşeyara Daha hızlı kod yada alternatif

Ekli dosyalar

bmutlu966

Ekli dosyalar

ccuneyt13

Korhan Ayhan

Administrator

ccuneyt13

VBA ile yapılan ETOPLA İşleminin Hızını Arttırma Yolu

bmutlu966

ccuneyt13

Korhan Ayhan

Administrator

Ekli dosyalar

ccuneyt13

Korhan Ayhan

Administrator

ChatGPT ve Excel | Yapay Zeka

ccuneyt13

ChatGPT ve Excel | Yapay Zeka