Excel'in 'Gizli' Maliyeti: Uçucu Formüller ve Performans Üzerindeki Etkileri

Excel'in 'Gizli' Maliyeti: Uçucu Formüller ve Performans Üzerindeki Etkileri

Selam !

Excel dosyaların beklenmedik bir şekilde yavaşlıyor ve her küçük değişiklikte donup kalıyorsa, dakikalarca süren hesaplamalar yüzünden sinir sahibi oluyorsan. Doğru yerdesin. Bu sorunun kaynağı, çoğu kullanıcının farkında olmadığı, Excel'in uçucu (volatile) formülleri olabilir.

Peki, uçucu formüller tam olarak nedir ve neden bir Excel dosyasının performansını bu kadar etkiler? Bu sorunun cevabını somut örneklerle bulalım.

Haydi başlayalım.

Uçucu Formüller: Nedir, Neden Var ve Nasıl Çalışırlar?

Excel normalde sadece bir hücredeki değer değiştiğinde o hücreyi ve ona bağlı diğer formülleri yeniden hesaplar. Ancak, uçucu formüller bu kurala uymaz. Adından da anlaşılacağı gibi, bu formüller "uçucudur" ve bir Excel dosyasında herhangi bir şey değiştiğinde, hatta siz hiçbir şey yapmasanız bile (örneğin dosya açıldığında veya F9 tuşuna basıldığında), tüm formüllerle birlikte yeniden hesaplanırlar.

Durum kritik aslında, uçucu formüllerin yeniden hesaplanması sadece bir hücredeki veri girişiyle tetiklenmiyor. Excel' deki bazı eylemleri, formüllerin yeniden hesaplanmasını tetikliyor bunlar da "uçucu eylemler" olarak tanımlanır. Bu eylemler, çalışma kitabının yapısında meydana gelen değişiklikleri içerir ve Excel'in bağımlılık ağacının ilgili bölümlerini yeniden hesaplama için "kirli" olarak işaretlemesine neden olur. Bu eylemlerden bazıları şunlar:

• Satır veya sütun ekleme, silme veya taşıma.
• Otomatik filtreleme (Autofilter) kriterlerini seçme.
• Satır veya sütun ayırıcılarına tıklama.
• Tanımlı adlar (Named Ranges) ekleme, değiştirme veya silme.
• Sayfa adını değiştirme veya çalışma kitabındaki sayfanın pozisyonunu değiştirme.

Yani, bir satırı silme ya da bir filtreleme işlemi gibi basit görünen eylemlerin, tüm çalışma kitabının yeniden hesaplanmasına yol açabileceğini fark etmiyoruz. Örneğin, ALTTOPLAM() – SUBTOTAL() fonksiyonunun gizli satırları göz ardı etme yeteneği, Excel'in bir satır gizleme/gösterme eylemini hesaplama tetikleyicisi olarak kullanmasını zorunlu kılar. Bu, görünürde basit bir eylemin arkasında yatan karmaşık bir mantıksal zincirdir ve performans sorunlarının gizli bir kaynağını oluşturur.

Bu durum, özellikle on binlerce satır ve karmaşık formüller içeren büyük dosyalarda tam bir performans kabusuna dönüşebilir.

Konunun daha da detayına buradan ulaşabilirsin. Volatile functions | HyperFormula (v3.0.1), https://hyperformula.handsontable.com/guide/volatile-functions.html

 Peki Hangi Formüller Bunlar?

En yaygın uçucu formüller:

• RASTGELEARADA() - RAND(): Rastgele sayı üretir.
• ŞİMDİ() - NOW(): Geçerli tarih ve saati verir.
• BUGÜN() - TODAY(): Geçerli tarihi verir.
• KAYDIR() - OFFSET(): Belirtilen bir referanstan belirli satır ve sütun kadar öteye kaydırır. Dinamik aralıklar oluşturmak için büyük bir esneklik sunsa da, bu esneklik performans açısından ağır bir bedel ödemenize neden olabilir.
• DOLAYLI() - INDIRECT(): Metin dizesiyle belirtilen referansı gerçek bir hücre referansına dönüştürür.

Uçucu Formüllerin Ölçülebilir Etkisi ve Alternatifleri

Bir Excel dosyasının yavaşlamasının ana nedeni, hesaplama motorunun yoğun çalışmasıdır. Siz farkında olmasanız da uçucu formüller bir "zincirleme reaksiyon" başlatır. Bu durumu daha iyi anlamanız için bir karşılaştırma yapalım.

Senaryo: 10.000 satırlık bir veri setinde arama yapıyorsunuz.

• OFFSET ile Arama: OFFSET formülü, dinamik bir aralık seçmek için kullanışlıdır ancak uçucu olduğu için her değişiklikte tüm çalışma sayfasının yeniden hesaplanmasını tetikler. Bu, özellikle büyük dosyalarda hesaplama süresini saniyelere, hatta dakikalara çıkarabilir.

• INDEX(MATCH()) ile Arama: INDEX ve MATCH formülleri ise uçucu değildir. Bu ikiliyi kullanarak yaptığınız aramalarda Excel, sadece ilgili hücreleri hesaplar ve gereksiz yeniden hesaplamaları engeller. Deneyler, 10.000 satırlık bir veri setinde INDEX(MATCH()) formülünün OFFSET' e kıyasla %90'a kadar daha hızlı olabildiğini göstermektedir.

Konuya ilişkin daha detaylı bilgiye buradan ulaşabilirsin. Excel performance - Improving calculation performance | Microsoft Learn https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance

Hız için Doğru Formül Tercihleri

• OFFSET() yerine: Neredeyse her zaman daha hızlı olan INDEX(MATCH()) kombinasyonunu kullanmak daha mantıklı gibi.

• INDIRECT() yerine: Eğer Microsoft 365 kullanıyorsanız, INDIRECT' in yaptığı birçok işlevi çok daha hızlı ve hatasız bir şekilde yerine getirebilen XLOOKUP idealdir Eski Excel sürümleri için ise VLOOKUP veya HLOOKUP formüllerini tercih edilebilir.

Konuya ilişkin detaylı bilgi : https://www.mrexcel.com/board/threads/what-is-fastest-formula-and-input-setting-between-index-match-index-xmatch-and-xlookup.1224430/

• Yoğun SUMIF / COUNTIF Kullanımı: Her ne kadar bu formüller uçucu olmasa da, büyük veri setlerinde yoğun bir şekilde kullanıldıklarında performans sorunlarına yol açabilirler. Bu tür senaryolarda Power Query veya VBA makrolarını kullanarak daha verimli çözümler üretebilirsiniz.

Konuya ilişkin detaylı bilgi : https://fastercapital.com/content/Performance-Optimization--Performance-Optimization--Making-COUNTIFS-Work-Faster-in-Large-Excel-Datasets.html

Hesaplama Modları ve İleri Seviye Çözümler

Excel'in hesaplama modlarını anlamak, performans sorunlarını yönetmede size büyük bir avantaj sağlar.

• Otomatik (Automatic): Varsayılan ayardır. Her değişiklikte anında yeniden hesaplama yapar.
• Manuel (Manual): Sadece F9 tuşuna basıldığında veya dosya kaydedildiğinde yeniden hesaplama yapar. Uçucu formüllerin yarattığı sorunları geçici olarak durdurmak için idealdir.

• F9: Açık olan tüm çalışma kitaplarındaki değişen formülleri yeniden hesaplar.
• Shift+F9: Yalnızca etkin çalışma sayfasındaki değişen formülleri yeniden hesaplar.
• Ctrl+Alt+F9: Tüm açık çalışma kitaplarındaki tüm formüllerin tam yeniden hesaplamasını zorlar.
• Ctrl+Shift+Alt+F9: Bağımlılık ağacını yeniden oluşturur ve tam bir yeniden hesaplama yapar.

• Yarı Otomatik (Automatic except for Data Tables): Veri tabloları hariç her değişiklikte yeniden hesaplama yapar.

Büyük ve karmaşık bir dosya üzerinde çalışırken "Manuel Hesaplama" moduna geçmek, gereksiz yeniden hesaplamaları durdurarak çalışma hızınızı büyük ölçüde artırır.

Power Query ve VBA ile Performans Kontrolü

Büyük veri setleri için en iyi çözüm, uçucu formüllere hiç ihtiyaç duymamaktır.

• Power Query: Veri temizleme, dönüştürme ve birleştirme işlemleri için güçlü bir araçtır. Formül tabanlı çözümler yerine Power Query ile verilerinizi işlemek, dosyanızın formül yükünü ciddi şekilde azaltır.

• VBA: Daha karmaşık işlemler için VBA makroları yazarak, formül tabanlı hesaplamaların performans yükünü ortadan kaldırabilirsin.

Kendi Dosyanı Performansını Nasıl Analiz Edersin?

Yazının başında bahsedildiği gibi, Excel dosyalarınızın nerede yavaşladığını anlamak için somut araçlar kullanabilirsiniz.

• Excel'in Dahili Araçları: Excel'de Formüller sekmesinde yer alan "Hesaplamayı İzle" (Evaluate Formula) aracıyla karmaşık formüllerin adım adım nasıl çalıştığını görebilirsiniz.

• Performansı Denetle Aracı: 365 sürümünü kullanıyorsan Gözden Geçir menüsünde yeni bir araç olarak eklendi. Dosyadaki en yavaş formülleri ve hücreleri tespit etmenize yardımcı olabilir.

Bilinçli Kullanım Güç Katar

Excel'deki uçucu formüller, doğru kullanıldığında esneklik sağlasa da büyük veri setlerinde performans sorunlarının temel kaynağı olabiliyor. Bu yazıdaki ipuçlarını uygulayarak, OFFSET ve INDIRECT gibi formüllerin daha hızlı ve güvenilir alternatiflerini kullanabilir, hesaplama modlarını doğru yönetebilir ve Power Query gibi araçlarla çalışma veriminizi maksimuma çıkarabilirsin.

Excel'de hız kazanmak için her zaman daha basit ve daha verimli bir yol var.