Excel Formül Analizi

semarion · 14 Temmuz 2020

Merhaba,

Elimde 10mb 'lık 14 sheet 'ten oluşan 1 excel dosyası mevcut. Her bir sheet 'in için tablolar ve bu tabloların beslendiği veri tabanları excel içinde de var.
Yazılan her bir formül sonrasında doğal olarak excel daha da kasmaya başladı.

Sistemi en çok yoran, zorlayan formül hangileri ise onları bulmak istiyorum. bunun analizini yapan bir uygulama veya yöntem var mı?

fairfun · 14 Temmuz 2020

böyle bir yöntem olduğunu hiç duymadım ama, eğer çalışma sayfanızın kullandığınız formüller sebebiyle ağırlaştığını düşünüyorsanız, aşağıda belirttiğim işlevleri kullanan formüller sürekli hesaplama yaptıklarından excel'i en fazla yoran formüllerdir:

kaydır,dolaylı,hücre,bilgi,rastgelearada,rastgdizi,s_sayı_üret,şimdi,bügün

Bunun dışında, şüphelendiğiniz formülleri değere çevirerek excel'in hızını test edip hangisinin yavaşlattığını bulabilirsiniz,

semarion · 14 Temmuz 2020

maalesef sanırım dediğiniz gibi değer vererek teker teker denemek zorunda kalacağım teşekkürler.

Necdet · 14 Temmuz 2020

Merhaba,

Yaptığınız her değişiklikte tüm formüller yeniden çalışacaktır.

Yoğun formül varsa ve dosyanız da oldukça büyükse bu normal durumdur.
Formüllerden kurtularak makroya geçebiliyorsanız onu deneyin ya da hesaplamayı excel seçeneklerinden otomatik değil el ile'yi seçin.

Korhan Ayhan · 14 Temmuz 2020

Aşağıdaki kodu kullanabilirsiniz.

Kod çalışınca bir sayfa oluşturup dosyanızdaki tüm sayfaları ve tahmini boyutlarını listeler.

C++:

Option Explicit

Sub Excel_Sayfa_Boyut_Analizi()
    Dim Yol As String, Sayfa As Variant, S1 As Worksheet, Satir As Integer
    Dim Hesaplama_Yontemi As Variant, Gizlilik_Degeri As Integer, Zaman As Double
    
    Zaman = Timer
    
    Hesaplama_Yontemi = Application.Calculation
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    
    On Error Resume Next
    Sheets("Sayfa_Boyut_Analizi").Delete
    On Error GoTo 0
    
    Yol = ThisWorkbook.Path & Application.PathSeparator
    
    Set S1 = Sheets.Add(, Sheets(ThisWorkbook.Sheets.Count))
    S1.Name = "Sayfa_Boyut_Analizi"
    S1.Range("A1:C1") = Array("SAYFA ADI", "HESAPLANAN SAYFA BOYUTU", "SAYFA GİZLİLİK DEĞERİ")
    S1.Range("A1:C1").Font.Bold = True
    S1.Range("A1:C1").Font.ColorIndex = 3
    S1.Range("A1:C1").HorizontalAlignment = xlCenter
    S1.Columns.AutoFit
    
    Satir = 2
    
    For Each Sayfa In ThisWorkbook.Sheets
        If Sayfa.Name <> S1.Name Then
            Gizlilik_Degeri = -1
            Select Case Sayfa.Visible
                Case 0, 2
                Gizlilik_Degeri = Sayfa.Visible
                Sayfa.Visible = -1
            End Select
            Sayfa.Copy
            ActiveWorkbook.SaveAs Yol & Sayfa.Name, 52
            ActiveWorkbook.Close 0
            S1.Cells(Satir, 1) = Sayfa.Name
            S1.Cells(Satir, 2) = FileLen(Yol & Sayfa.Name & ".xlsm") / 1024
            S1.Cells(Satir, 2).Style = "Comma"
            If Gizlilik_Degeri = 0 Then
                S1.Cells(Satir, 3) = "Gizli"
            ElseIf Gizlilik_Degeri = 2 Then
                S1.Cells(Satir, 3) = "Yüksek Gizli"
            Else
                S1.Cells(Satir, 3) = "Görünür"
            End If
            Satir = Satir + 1
            Sayfa.Visible = Gizlilik_Degeri
            Kill Yol & Sayfa.Name & ".xlsm"
        End If
    Next

    S1.Columns.AutoFit
    S1.Select
    
    Set S1 = Nothing
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = Hesaplama_Yontemi
        .DisplayAlerts = True
    End With
    
    MsgBox "Sayfa boyutları listelenmiştir." & Chr(10) & Chr(10) & _
           "İşlem süresi ; " & Format(Timer - Zaman, "0.00") & " Saniye"
End Sub

semarion · 14 Temmuz 2020

Teşekkürler

konas06 · 15 Temmuz 2020

Benim de benzer çalışmalarım var,
Ben formül ve makroları iyi kullanamadığım için şu yöntemi kullanmaya başladım.

Tekrar eden formüllerde birinci satırları hep tutuyorum ancak 2 ve sonrası formülleri formülden kurtarıp değere çeviriyorum ve bunu bir şekil ile makroya atıyorum.
Çok kullanışlı oldu.
Örneğin 300.000 KB lık dosya 20.000 KB ya düşürdüm ve çok da hızlı çalışıyor.

Bir önerim daha var, Sayın Korhan Ayhan ın vermiş olduğu koddan yola çıkarak; acaba dosya genelinde hangi formülün ne kadar kapasite kullanıldığı tespit edilirse belki kasan formül yerine alternatif arayabilirsiniz.

relaxim · 15 Temmuz 2020

Korhan Ayhan' Alıntı:

Aşağıdaki kodu kullanabilirsiniz.

Kod çalışınca bir sayfa oluşturup dosyanızdaki tüm sayfaları ve tahmini boyutlarını listeler.

C++:

Option Explicit

Sub Excel_Sayfa_Boyut_Analizi()
    Dim Yol As String, Sayfa As Variant, S1 As Worksheet, Satir As Integer
    Dim Hesaplama_Yontemi As Variant, Gizlilik_Degeri As Integer, Zaman As Double
   
    Zaman = Timer
   
    Hesaplama_Yontemi = Application.Calculation
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
   
    On Error Resume Next
    Sheets("Sayfa_Boyut_Analizi").Delete
    On Error GoTo 0
   
    Yol = ThisWorkbook.Path & Application.PathSeparator
   
    Set S1 = Sheets.Add(, Sheets(ThisWorkbook.Sheets.Count))
    S1.Name = "Sayfa_Boyut_Analizi"
    S1.Range("A1:C1") = Array("SAYFA ADI", "HESAPLANAN SAYFA BOYUTU", "SAYFA GİZLİLİK DEĞERİ")
    S1.Range("A1:C1").Font.Bold = True
    S1.Range("A1:C1").Font.ColorIndex = 3
    S1.Range("A1:C1").HorizontalAlignment = xlCenter
    S1.Columns.AutoFit
   
    Satir = 2
   
    For Each Sayfa In ThisWorkbook.Sheets
        If Sayfa.Name <> S1.Name Then
            Gizlilik_Degeri = -1
            Select Case Sayfa.Visible
                Case 0, 2
                Gizlilik_Degeri = Sayfa.Visible
                Sayfa.Visible = -1
            End Select
            Sayfa.Copy
            ActiveWorkbook.SaveAs Yol & Sayfa.Name, 52
            ActiveWorkbook.Close 0
            S1.Cells(Satir, 1) = Sayfa.Name
            S1.Cells(Satir, 2) = FileLen(Yol & Sayfa.Name & ".xlsm") / 1024
            S1.Cells(Satir, 2).Style = "Comma"
            If Gizlilik_Degeri = 0 Then
                S1.Cells(Satir, 3) = "Gizli"
            ElseIf Gizlilik_Degeri = 2 Then
                S1.Cells(Satir, 3) = "Yüksek Gizli"
            Else
                S1.Cells(Satir, 3) = "Görünür"
            End If
            Satir = Satir + 1
            Sayfa.Visible = Gizlilik_Degeri
            Kill Yol & Sayfa.Name & ".xlsm"
        End If
    Next

    S1.Columns.AutoFit
    S1.Select
   
    Set S1 = Nothing
   
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = Hesaplama_Yontemi
        .DisplayAlerts = True
    End With
   
    MsgBox "Sayfa boyutları listelenmiştir." & Chr(10) & Chr(10) & _
           "İşlem süresi ; " & Format(Timer - Zaman, "0.00") & " Saniye"
End Sub

Teşekkürler, ellerinize sağlık...

Korhan Ayhan · 15 Temmuz 2020

Merhaba,

Excelde veriler büyümeye başladıkça ister istemez formül kullanımından dolayı yavaşlama kaçınılmazdır.

Daha öncede farklı konularda bu durum dile getirilmişti. Genel olarak bu tarz dosyalarda verimli çalışmak adına hesaplama yöntemi "elle" şeklinde ayarlanarak hızlı çalışma sağlanabilir. Fakat bu yöntemde formüller direk tepkime vermediği için kullanıcılar genelde Excel hesaplama yapmıyor şeklinde yorum yapmaktadırlar. Bu yöntemde sonuçları görmek için F9 tuşuna basmak yeterli olacaktır. Ya da dosyayı kayıt ederek hesaplamaları yapması sağlanabilir. Bende yüklü dosyalarımda bu yöntemi sıklıkla kullanıyorum.

Kullanıcılar genellikle tablolarının genişleyeceğini öngörerek formülleri tüm sütunu kapsayacak şekilde (A:A gibi) ya da biçimlendirmeleri hızlı yapabilmek adına tüm sütunu seçerek yapmaktadırlar. Bu alışkanlıklar maalesef iyi alışkanlıklar değiller. Belki o anlık kullanımlarda pratiklik kazandırıyor ama dosya boyutu konusunda maalesef excel acımasız davranıyor.

Yeni excel sürümlerinde hayatımıza giren ÇOKETOPLA-ÇOKEĞERSAY vb. fonksiyonlar hız olarak daha performanslı çalışmaktadır. Fakat bu fonksiyonları da gereksiz yere binlerce satıra uyguladığınızda maalesef excel hız açısından büyük ölçüde sıkıntı yaşatmaktadır. Microsfot'un sitesinde bu konuyla ilgili araştırma yaparken karşılaştığım bir açıklamayı paylaşmak isterim.

Excel çalışma kitabını bellekte daha az kullanması için temizleyin - Microsoft 365 Apps

Excel çalışma kitaplarında fazla bellek kullanan alanları tanımlar ve çalışma kitabı dosyalarınızın nasıl daha verimli çalışabileceğini açıklar.

docs.microsoft.com

Konuyla ilgili olduğunu düşündüğüm birkaç link;

https://www.excel.web.tr/threads/optimal-bir-excel-dosyasi-icin-altin-kurallar.74806/

https://www.excel.web.tr/threads/formuel-yazma-teknikleri.110365/

yusuf1284 · 5 Mayıs 2022

Hocam 2 sorum olacak
1.Sorum; Verileri tutuğumuz alanın derli toplu (şeritli satırlar ve kenarlıklar) olması için tablo olarak sakladım doğru mu?

2.Sorum; Formül yerine her şeyi makro kodlarıyla yapmak daha mı sağlıklıdır?

Örnek veriyorum verilerin başına sıra numarası verirken

=SATIR()

Formüllünü kullanmak mı?

Personel eklemek için kullandığım kod

Sub Per_Ekle()
Dim Sayfa_Donus
Dim Per_Say As Integer
Sayfa_Donus = ActiveSheet.Name

Application.ScreenUpdating = False

Per_Say = WorksheetFunction.CountA(Range("F7:" & "F99999")) + 7
db_pb.Range("E" & Per_Say).Value = db_pb.Range("E" & Per_Say).Row - 6
db_pb.Range("A1").Select
ActiveSheet.ListObjects("Tablo_db_pb").Resize Range("$E$6" & ":JV" & Per_Say) 'Tabloyu boyutlandır
Worksheets(Sayfa_Donus).Select
db_pb.Range("A6").Value = Per_Say - 6 & " Personel Mevcut"
Application.ScreenUpdating = True

End Sub

Veri tablomdan aradan veri silindiğinde kullanmış olduğum kod

Sub Per_Db_Pb_Tablo_Düzelt() 'aradan satır silme işlemleri için
Dim i As Integer
Dim Per_Say As Integer
Application.ScreenUpdating = False

Per_Say = WorksheetFunction.CountA(Range("F7:" & "F99999")) + 6

For i = 7 To Per_Say 'i den başla Personel_Sayısı kadar tekrarla
db_pb.Range("E" & i).Value = db_pb.Range("E" & i).Row - 6
Next i

ActiveSheet.ListObjects("Tablo_db_pb").Resize Range("$E$6" & ":JV" & Per_Say) 'Tabloyu boyutlandır

db_pb.Range("E" & Per_Say + 1 & ":E9999").ClearContents
db_pb.Range("A6").Value = Per_Say - 6 & " Personel Mevcut"

Application.ScreenUpdating = True
End Sub

(Burada For döngü şeklinde olması fazla veri olduğunda yoruyor başka çözüm aklıma gelmediğinden bu şekilde bıraktım.)

Korhan Ayhan · 5 Mayıs 2022

Merhaba,

Excelde kullanılan fonksiyonlar dinamik olarak çalışmaktadır. Yani anında tepki verirler. Makro kodları ise çalışması için bir tetikleme gerekir. Bu sebeple birçok kişi makrolarında fonksiyonlar gibi çalışmasını beklediği için bir kavram karmaşası ortaya çıkmaktadır. Doğru yöntemi tercih edemedikleri içinde kullanmaktan kaçınmaktadırlar.

Excelde TABLO özelliği gayet kullanışlıdır.

Linkte bazı bilgiler paylaşılmış...

Excel tablolarına genel bakış - Microsoft Desteği

İlgili veri grubunu yönetmeyi ve çözümlemeyi kolaylaştırmak için, bir hücre aralığını Excel tablosuna (daha önce Excel listesi olarak bilinirdi) dönüştürebilirsiniz.

support.microsoft.com

Ek olarak bir dosyada makro kullanıyorsanız bana sorarsanız bir kısım için formül kullanmaya gerek yoktur. Tüm işlemler makro ile yapılabilir. Tabi bu işlemler için temel makro bilgisine sahip olmak gerekir.

yusuf1284 · 5 Mayıs 2022

Korhan Ayhan' Alıntı:
Merhaba,

Excelde kullanılan fonksiyonlar dinamik olarak çalışmaktadır. Yani anında tepki verirler. Makro kodları ise çalışması için bir tetikleme gerekir. Bu sebeple birçok kişi makrolarında fonksiyonlar gibi çalışmasını beklediği için bir kavram karmaşası ortaya çıkmaktadır. Doğru yöntemi tercih edemedikleri içinde kullanmaktan kaçınmaktadırlar.

Excelde TABLO özelliği gayet kullanışlıdır.

Linkte bazı bilgiler paylaşılmış...

Excel tablolarına genel bakış - Microsoft Desteği

İlgili veri grubunu yönetmeyi ve çözümlemeyi kolaylaştırmak için, bir hücre aralığını Excel tablosuna (daha önce Excel listesi olarak bilinirdi) dönüştürebilirsiniz.

support.microsoft.com

Ek olarak bir dosyada makro kullanıyorsanız bana sorarsanız bir kısım için formül kullanmaya gerek yoktur. Tüm işlemler makro ile yapılabilir. Tabi bu işlemler için temel makro bilgisine sahip olmak gerekir.

Tablo olarak devam ediyorum hocam
Hocam şu aşağıdaki kodda yer alan for döngüsü zaman alıyor başka yöntem var mıdır acaba?

Sub Per_Db_Pb_Tablo_Düzelt() 'aradan satır silme işlemleri için
Dim i As Integer
Dim Per_Say As Integer
Application.ScreenUpdating = False

Per_Say = WorksheetFunction.CountA(Range("F7:" & "F99999")) + 6

For i = 7 To Per_Say 'i den başla Personel_Sayısı kadar tekrarla
db_pb.Range("E" & i).Value = db_pb.Range("E" & i).Row - 6
Next i

ActiveSheet.ListObjects("Tablo_db_pb").Resize Range("$E$6" & ":JV" & Per_Say) 'Tabloyu boyutlandır

db_pb.Range("E" & Per_Say + 1 & ":E9999").ClearContents
home.Range("G10").Value = "Sisteme Kayıtlı Personel Sayısı: " & Per_Say - 6
db_pb.Range("A6").Value = " Kayıtlı Personel Sayısı : " & Per_Say - 6

Application.ScreenUpdating = True
End Sub

Korhan Ayhan · 5 Mayıs 2022

Örnek dosyanızı paylaşınız.

yusuf1284 · 5 Mayıs 2022

Hocam verileri rastgele doldurdum kusuruma bakmayın kişisel bilgiler yer aldığından yaklaşık olarak 1000 kişinin verisi eklenecek sisteme Personel kayıt (F_Per_entry) kısmına el atarsan çok iyi olur

Ekle Güncelle ve Sil komutları olacak şekilde

çok fazla şey istedim sanırım

Korhan Ayhan · 5 Mayıs 2022

İlgili kısmı döngüsüz aşağıdaki gibi uygulayabilirsiniz.

C++:

Sub Per_Db_Pb_Tablo_Düzelt() 'aradan satır silme işlemleri için
    Dim i As Integer
    Dim Per_Say As Integer

    Application.ScreenUpdating = False
  
    Per_Say = Cells(Rows.Count, "E").End(3).Row
  
    With Range("E7:E" & Per_Say)
        .Formula = "=IF(F7<>"""",ROW(A1),"""")"
        .Value = .Value
    End With
      
    ActiveSheet.ListObjects("Tablo_db_pb").Resize Range("$E$6" & ":JV" & Per_Say) 'Tabloyu boyutlandır
  
    db_pb.Range("E" & Per_Say + 1 & ":E9999").ClearContents
    home.Range("G10").Value = "Sisteme Kayıtlı Personel Sayısı: " & Per_Say - 6
    db_pb.Range("A6").Value = " Kayıtlı Personel Sayısı : " & Per_Say - 6
  
    Application.ScreenUpdating = True
End Sub

yusuf1284 · 5 Mayıs 2022

Daha hızlı oldu Teşekkür ederim hocam boyut konusunda küçültme için ne yapabilirim home sayfasında yer alan butonların hepsi için sayfa oluşturacağım öneriniz var mı?

Korhan Ayhan · 5 Mayıs 2022

"db_pb" isimli sayfanızda 282 sütunluk bir veri yığını var.

Paylaştığınız dosyanızın boyutu 3,8 MB'dır. Adı geçen sayfada A31 satırından en alta kadar seçerek CTRL- yaparak gereksiz satırları silip dosyanızı kayıt ettiğimde boyutu 229 KB'ta kadar düştü. Bu kadar çok sütunlu bir tabloda bundan başka yapabileceğiniz bir işlemde maalesef yoktur.

yusuf1284 · 5 Mayıs 2022

Korhan Ayhan' Alıntı:
"db_pb" isimli sayfanızda 282 sütunluk bir veri yığını var.

Paylaştığınız dosyanızın boyutu 3,8 MB'dır. Adı geçen sayfada A31 satırından en alta kadar seçerek CTRL- yaparak gereksiz satırları silip dosyanızı kayıt ettiğimde boyutu 229 KB'ta kadar düştü. Bu kadar çok sütunlu bir tabloda bundan başka yapabileceğiniz bir işlemde maalesef yoktur.

Anladım hocam teşekkür ederim formül hiç kullanmadan yapsam iyi olacak sanırım sh_izin sayfasında yarısı formül yarısı makro ile veri çekiyordu tamamını makro yapayım birazda olsa hızlanır

Excel Formül Analizi

semarion

fairfun

semarion

Necdet

Moderatör

Korhan Ayhan

Administrator

semarion

konas06

relaxim

Korhan Ayhan

Administrator

Excel çalışma kitabını bellekte daha az kullanması için temizleyin - Microsoft 365 Apps

yusuf1284

Korhan Ayhan

Administrator

Excel tablolarına genel bakış - Microsoft Desteği

yusuf1284

Excel tablolarına genel bakış - Microsoft Desteği

Korhan Ayhan

Administrator

yusuf1284

Ekli dosyalar

Korhan Ayhan

Administrator

yusuf1284

Korhan Ayhan

Administrator

yusuf1284