Soru fonksiyon yavaşlığı

[incsoft]

Arkadaşlar çalışma kitabımda bulunan sayfalara göre toplam adet hesaplattırmak için aşağıdaki fonksiyonu 1 arkadaşımız aracılığıyla yapmıştık ancak bunda bir yavaşlık söz konusu nedenini anlayalamadım. Görevi E sütunundaki numaraların tüm sayfalardaki F sütunundaki karşılıklarını bulup toplamını almak. Kısacası İşemrinin toplam üretimini buldurmak. Bunun için bir değişiklik yapılarak hızlandırılması sağlanabilirmi? 1-2-3....31 ve birde Toplam diye sayfalar var hepsinin toplamlarını alıyor.



Option Explicit
Function K_TOPLA(Kriter As Variant) As Double
Dim Sayfa As Worksheet, Son As Long, Veri As Variant, X As Long

For Each Sayfa In ThisWorkbook.Worksheets
Son = Sayfa.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Veri = Sayfa.Range("E1:F" & Son).Value
For X = LBound(Veri) To UBound(Veri)
If Veri(X, 1) = Kriter Then
If IsNumeric(Veri(X, 2)) Then K_TOPLA = K_TOPLA + Veri(X, 2)
End If
Next
Next
End Function

 

[Korhan Ayhan]

Merhaba,

Sanırım fonksiyonu ben hazırlamıştım.

Fonksiyonda zaten DİZİ yöntemi kullanılıyor. Yani en hızlı yöntemlerden biridir.

Şöyle basit bir hesaplama yapalım;

1 ile 31 arası sayfalarınız var. Bu sayfalarda ortalama 250 satırlık veriniz olsa; 250 Satır * 2 Sütun = 500 Hücre sorguya alınıyor. 500 Hücre x 31 Sayfa = 15.500 Hücre sorgulanmış oluyor.

Yani siz dosyanızda her kaydet ya da F9 tuşuna basarak hesaplama yaptığınızda bu 15.500 hücre işleme alınmaktadır. Eğer dosyanızda başka fonksiyonlarda varsa bu hesaplama işlemine onları da dahil ederseniz excelin can çekişmesi normaldir.

Bu fonksiyona şu şekilde hız kazandırılabilir. Eğer 1-31 arası sayfalarda veriler sabit satırlarda ise E:F sütun aralığında veri olan tüm hücrelerin kontrolü yerine sabit satıra odaklanarak işlemin daha hızlı sonuç vermesi sağlanabilir. Bu şekilde kısıtlamalar verebilirseniz hızlandırabiliriz.

 

[incsoft]

Merhaba,

Sanırım fonksiyonu ben hazırlamıştım.

Fonksiyonda zaten DİZİ yöntemi kullanılıyor. Yani en hızlı yöntemlerden biridir.

Şöyle basit bir hesaplama yapalım;

1 ile 31 arası sayfalarınız var. Bu sayfalarda ortalama 250 satırlık veriniz olsa; 250 Satır * 2 Sütun = 500 Hücre sorguya alınıyor. 500 Hücre x 31 Sayfa = 15.500 Hücre sorgulanmış oluyor.

Yani siz dosyanızda her kaydet ya da F9 tuşuna basarak hesaplama yaptığınızda bu 15.500 hücre işleme alınmaktadır. Eğer dosyanızda başka fonksiyonlarda varsa bu hesaplama işlemine onları da dahil ederseniz excelin can çekişmesi normaldir.

Bu fonksiyona şu şekilde hız kazandırılabilir. Eğer 1-31 arası sayfalarda veriler sabit satırlarda ise E:F sütun aralığında veri olan tüm hücrelerin kontrolü yerine sabit satıra odaklanarak işlemin daha hızlı sonuç vermesi sağlanabilir. Bu şekilde kısıtlamalar verebilirseniz hızlandırabiliriz.

Evet hocam siz yapmıştınız. Burda ayın 31 günü standart ancak birde önceki ay verileri için Sayfa1 isminde kısım var orası standart değil sürekli değişken gösteriyor. Bilgisayar dahi değiştirdim yine hızlandıramadım. Bu yöntem bana çok önemli hocam eğer çözüm bulabilirsek çok sevinirim. Sizler gibi bu konuda uzman olmadığımız için çözümler üretemiyoruz..
Teşekkürler.

 

[Korhan Ayhan]

İŞEMRİ numaraları sayfalarda sabit satırda ise yada tekrar etmiyorsa (mükerrer değilse) hız kazandırabiliriz.

 

[incsoft]

İŞEMRİ numaraları sayfalarda sabit satırda ise yada tekrar etmiyorsa (mükerrer değilse) hız kazandırabiliriz.

işemri numaraları sabit satırlarda değil hocam aynen. Bugün E84 de olan yarın E30 da olabilir ya da aynısı E84 de de olabilir E90'da da ... Çünkü o işemri numarası bugün 1.vrd'da atılır yarın 2.vrd'da atılır ya da bugün 1 tezgahta yarın diğerinde olabilir

 

[Korhan Ayhan]

Birde aşağıdaki fonksiyonu deneyin bakalım hızda bir fark olacak mı?

Option Explicit

Function K_TOPLA(Kriter As Variant) As Double
    Dim Sayfa As Worksheet, Son As Range, Bul As Range, Adres As String

    Application.Volatile True

    For Each Sayfa In ThisWorkbook.Worksheets
        Set Son = Sayfa.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Not Son Is Nothing Then
            Set Bul = Sayfa.Range("E1:E" & Son.Row).Find(Kriter, , , xlWhole)
            If Not Bul Is Nothing Then
                Adres = Bul.Address
                Do
                    If IsNumeric(Bul.Offset(0, 1)) Then
                        K_TOPLA = K_TOPLA + Bul.Offset(0, 1)
                    End If
                    Set Bul = Sayfa.Range("E1:E" & Son.Row).Find(Kriter, Bul)
                Loop While Not Bul Is Nothing And Bul.Address <> Adres
            End If
        End If
    Next
End Function

 

[incsoft]

Birde aşağıdaki fonksiyonu deneyin bakalım hızda bir fark olacak mı?

Option Explicit

Function K_TOPLA(Kriter As Variant) As Double
    Dim Sayfa As Worksheet, Son As Range, Bul As Range
   
    Application.Volatile True

    For Each Sayfa In ThisWorkbook.Worksheets
        Set Son = Sayfa.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Not Son Is Nothing Then
            Set Bul = Sayfa.Range("E1:E" & Son.Row).Find(Kriter, , , xlWhole)
            If Not Bul Is Nothing Then
                If IsNumeric(Bul.Offset(0, 1)) Then K_TOPLA = K_TOPLA + Bul.Offset(0, 1)
            End If
        End If
    Next
End Function

E1:E olmuş sanırım yanlış hocam onu E1:F yaptım denedim ancak daha da yavaş çalışıyor. E1:E doğrumu acaba? Birde ne değişiklik yapmıştınız hız için?

 

[Korhan Ayhan]

"E1:E" doğrudur.

Yaptığım değişiklik;

Döngüyü DİZİ (Array) mantığından koparıp CTRL+F yani FIND (BUL) komutuna dönüştürmek oldu.

Direkt aranan veriye odaklanmış olduk. Diğer satırları gözardı et dedik ama yine hız olarak avantaj sağlamadı. Çünkü arama (FIND) yaparken de bir şekilde hücrelere bakıyor.

Zaten ilk önerimde ki DİZİ (Array) yöntemi en hızlı tekniklerden birisidir.

 

[incsoft]

"E1:E" doğrudur.

Yaptığım değişiklik;

Döngüyü DİZİ (Array) mantığından koparıp CTRL+F yani FIND (BUL) komutuna dönüştürmek oldu.

Direkt aranan veriye odaklanmış olduk. Diğer satırları gözardı et dedik ama yine hız olarak avantaj sağlamadı. Çünkü arama (FIND) yaparken de bir şekilde hücrelere bakıyor.

Zaten ilk önerimde ki DİZİ (Array) yöntemi en hızlı tekniklerden birisidir.

örnek attım hocam

 

[Korhan Ayhan]

Paylaştığınız dosyada işlem çok uzun sürmüyor.

 

[incsoft]

Paylaştığınız dosyada işlem çok uzun sürmüyor.

daha bu birkaç gün hocam birde 31 gün olduğunu düşünelim. Ondan dolayı sıkıntı yaşıyorum.

 

[incsoft]

Şöyle birşey hızlı olabilirmi acaba? Günlük olarak o sayfadaki verileri bir yere sipariş ve üretim olarak atacak ve sonra ÇOKETOPLA,TOPLA.ÇARPIM vb.. komutlarla ordan bulacak.. Buna görüşünüz nedir?

 

[Korhan Ayhan]

#6 nolu mesajımda önerdiğim alternatif fonksiyon eksik oldu. Sayfalardaki sadece ilk bulduğu İŞEMRİ numarasını dikkate alıyordu. Düzenlemeyi yaptım. Ama hız olarak artı katmayacaktır. Sadece bilgi vermek için açıklama yazmak istedim.

Son hali paylaştığınız dosyada ki sayfa sayısını çoğalttım. İki fonksiyonu hız olarak test ettim. Sayfalarda 65. satıra kadar veri var.

DİZİ yöntemi ile çalışan fonksiyon yaklaşık 5 saniyede hesaplıyor.
FIND yöntemi ile çalışan fonksiyon ise son düzenleme ile yaklaşık 10 saniyede hesaplıyor.

Asıl dosyanızda satır sayıları fazla ise süreler de buna bağlı olarak değişecektir.

İ5 6. Nesil İşlemci 8 GB Ram olan laptobumda elde ettiğim sonuçlardır.

Elbette sayfalardaki verileri bir sayfaya aktarıp oradan daha basit fonksiyonlarla sonuç alınabilir. Bu durumda aktarım işlemi için bir buton kullanmak gerekecektir.

 

[incsoft]

#6 nolu mesajımda önerdiğim alternatif fonksiyon eksik oldu. Sayfalardaki sadece ilk bulduğu İŞEMRİ numarasını dikkate alıyordu. Düzenlemeyi yaptım. Ama hız olarak artı katmayacaktır. Sadece bilgi vermek için açıklama yazmak istedim.

Son hali paylaştığınız dosyada ki sayfa sayısını çoğalttım. İki fonksiyonu hız olarak test ettim. Sayfalarda 65. satıra kadar veri var.

DİZİ yöntemi ile çalışan fonksiyon yaklaşık 5 saniyede hesaplıyor.
FIND yöntemi ile çalışan fonksiyon ise son düzenleme ile yaklaşık 10 saniyede hesaplıyor.

Asıl dosyanızda satır sayıları fazla ise süreler de buna bağlı olarak değişecektir.

İ5 6. Nesil İşlemci 8 GB Ram olan laptobumda elde ettiğim sonuçlardır.

Elbette sayfalardaki verileri bir sayfaya aktarıp oradan daha basit fonksiyonlarla sonuç alınabilir. Bu durumda aktarım işlemi için bir buton kullanmak gerekecektir.

Öncelikle teşekkürler hocam ancak dosyayı göremedim

 

[Korhan Ayhan]

Dosya eklemedim. Sadece senaryoyu açıkladım. Dosyanın aslı zaten sizde var. Orada deneyebilirsiniz.

 

[incsoft]

Dosya eklemedim. Sadece senaryoyu açıkladım. Dosyanın aslı zaten sizde var. Orada deneyebilirsiniz.

ETOPLA ile sizce bu mümkünmüdür Korhan bey?

 

[muratboz06]

Deneyiniz.

Option Explicit
Function K_TOPLA(Kriter As Variant) As Double
    TurnOnSpeed TRUE
    Dim Sayfa As Worksheet, Son As Long, Veri As Variant, X As Long
    
    For Each Sayfa In ThisWorkbook.Worksheets
        Son = Sayfa.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Veri = Sayfa.Range("E1:F" & Son).Value
        For X = LBound(Veri) To UBound(Veri)
            If Veri(X, 1) = Kriter Then
                If IsNumeric(Veri(X, 2)) Then K_TOPLA = K_TOPLA + Veri(X, 2)
            End If
        Next
    Next
    TurnOnSpeed FALSE
End Function

Public PriorCalcMode As Variant

Public Function TurnOnSpeed(x As Boolean)
    
    '-----------------------------
    'Thanks for downloading the code.
    'Please visit our channel for a quick explainer on how to use this code.
    'Feel free to update the code as per your need and also share with your friends.
    'Download free codes from http://vbaa2z.blogspot.com
    'Support our channel: youtube.com/vbaa2z
    'Author: L Pamai (vbaa2z.team@gmail.com)
    '-----------------------------
    
    If x = TRUE Then
        With Application
            PriorCalcMode = Application.Calculation
            .ScreenUpdating = FALSE
            .DisplayAlerts = FALSE
            .EnableEvents = FALSE
            .Cursor = xlWait
            .Calculation = xlCalculationManual
        End With
        
    ElseIf x = FALSE Then
        
        With Application
            .ScreenUpdating = TRUE
            .DisplayAlerts = TRUE
            .EnableEvents = TRUE
            .StatusBar = FALSE
            .Cursor = xlDefault
            .Calculation = PriorCalcMode
        End With
    End If
    
End Function

Kaynak: https://vbaa2z.blogspot.com/2016/10/speed-up-vba-code.html

 

[Korhan Ayhan]

Dosyanıza "Veri" adında bir sayfa ekledim.

Bu sayfadaki butona tıkladığınızda ya da bu sayfayı açmak için tıkladığınızda sayfalardaki veriler (Tarih, İşemri Numarası, Üretim Miktarı, Sipariş Miktarı) aktarılmaktadır.

Günlere ait sayfalarda ise birden fazla ölçüte ihtiyacınız olabilir düşüncesiyle ÇOKETOPLA kullandım. Dilerseniz ETOPLA olarak revize edip kullanabilirsiniz.

 

[incsoft]

Deneyiniz.

Option Explicit
Function K_TOPLA(Kriter As Variant) As Double
    TurnOnSpeed TRUE
    Dim Sayfa As Worksheet, Son As Long, Veri As Variant, X As Long
   
    For Each Sayfa In ThisWorkbook.Worksheets
        Son = Sayfa.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Veri = Sayfa.Range("E1:F" & Son).Value
        For X = LBound(Veri) To UBound(Veri)
            If Veri(X, 1) = Kriter Then
                If IsNumeric(Veri(X, 2)) Then K_TOPLA = K_TOPLA + Veri(X, 2)
            End If
        Next
    Next
    TurnOnSpeed FALSE
End Function

Public PriorCalcMode As Variant

Public Function TurnOnSpeed(x As Boolean)
   
    '-----------------------------
    'Thanks for downloading the code.
    'Please visit our channel for a quick explainer on how to use this code.
    'Feel free to update the code as per your need and also share with your friends.
    'Download free codes from http://vbaa2z.blogspot.com
    'Support our channel: youtube.com/vbaa2z
    'Author: L Pamai (vbaa2z.team@gmail.com)
    '-----------------------------
   
    If x = TRUE Then
        With Application
            PriorCalcMode = Application.Calculation
            .ScreenUpdating = FALSE
            .DisplayAlerts = FALSE
            .EnableEvents = FALSE
            .Cursor = xlWait
            .Calculation = xlCalculationManual
        End With
       
    ElseIf x = FALSE Then
       
        With Application
            .ScreenUpdating = TRUE
            .DisplayAlerts = TRUE
            .EnableEvents = TRUE
            .StatusBar = FALSE
            .Cursor = xlDefault
            .Calculation = PriorCalcMode
        End With
    End If
   
End Function

Kaynak: https://vbaa2z.blogspot.com/2016/10/speed-up-vba-code.html

 

[incsoft]

Dosyanıza "Veri" adında bir sayfa ekledim.

Bu sayfadaki butona tıkladığınızda ya da bu sayfayı açmak için tıkladığınızda sayfalardaki veriler (Tarih, İşemri Numarası, Üretim Miktarı, Sipariş Miktarı) aktarılmaktadır.

Günlere ait sayfalarda ise birden fazla ölçüte ihtiyacınız olabilir düşüncesiyle ÇOKETOPLA kullandım. Dilerseniz ETOPLA olarak revize edip kullanabilirsiniz.

güzel bir metod hocam ancak kullanacak kişilerin her seferinde veri gitmesi birde yapılan bir eklenti ya da değişiklikten sonra CTRL+S ya da normal kaydet komutu vermesi gerekli. Veri sayfasına gitmeden belli saniyede veri kendini güncelleme yapabilirmi? Muhteşem derecede hızlı ancak kullanacak arkadaşlar bilgisayar konusunda çok iyi olmadıklarından tek eksisi dediğim gibi veriye gitmeden güncelleme yapması ve kaydet komutu vermeden komutu uygulamaması. Bunlarda olursa tek kelime muhteşem hocam.