Excel, Bu Fonksiyonlarla On Kaplan Gücünde : Dinamik Dizi Fonksiyonları

Selam!

Başlığımızın konusu olan fonksiyonlar, hayatımıza 365 sürümü ile birlikte giren (ilk testleri 2018 de yapılmış)  Dinamik Dizi Fonksiyonları.

Bu yazıda, Excel' in bu yeni süper güçlerine odaklanıyoruz.

(Konuya girmeden bir konuya açıklık getireyim, Formül mü demeliyim Fonksiyon mu? sorusunun cevabı : Excel’de “fonksiyon” terimi doğru; “formül” ise bu fonksiyon(lar)ı ve operatörleri bir araya getirerek yazdığımız ifadenin adı. Yazı içerisinde bazen formül bazen fonksiyon yazabilirim aklında olsun.)

Haydi Başlayalım.

Dinamik Dizi Formülleri Nedir? (Bak formül yazdım)

Dinamik dizi formülleri, sonucunu tek bir hücre yerine, birden fazla hücreye yayan ("spill" olarak bilinen) formüllerdir. Geleneksel formüllerden farkı, sen formülü sadece tek bir hücreye yazarsın, ancak Excel sonuçları otomatik olarak aşağıdaki veya yandaki boş hücrelere yayar.

Bu, manuel olarak kopyala-yapıştır yapmanı veya karmaşık formüller yazmanı ortadan kaldırır. En iyi yanı ise, kaynak veriler değiştiğinde, formülün sonuçları da anında güncellenir. Süper yani, Aspirin gibi Formül. Her derde deva.

Bir Yerden Başlamak Lazım, Dinamik Dizi Fonksiyonları ile Tanışın

En popüler olanların listesini aşağıda bulabilirsin.

• FİLTRE (FILTER): Büyük bir veri tablosundan belirli kriterlere uyan satırları çeker ve dinamik bir liste oluşturur. Bildiğimiz Filtrenin (canımız) formül hali.

• Örnek: =FİLTRE(A2:C100; B2:B100="Ankara") formülü, sadece "Ankara" satışlarını listeleyen dinamik bir tablo oluşturur.

• BENZERSİZ (UNIQUE): Bir veri listesindeki yinelenen değerleri otomatik olarak kaldırır ve benzersiz bir liste oluşturur. Bu da Yinelenenleri Kaldır özelliğinin formül hali.

• • Örnek: =BENZERSİZ(B2:B100) formülü, satış raporunuzdaki tüm benzersiz müşteri isimlerini tek formülle listeler.

• SIRALA (SORT) & SIRALAÖLÇÜT (SORTBY): Bir veri listesini belirlediğiniz kritere göre sıralar. Ama farklı bir alanda yapar bu işi yani orijinal veriye dokunmaz.

• Örnek: =SIRALA(A2:C100; 2; -1) formülü, tüm satış verilerini ikinci sütundaki değere göre (satış miktarı) en yüksekten en düşüğe doğru sıralar. -1 yerine 1 yazarsan en düşükten yükseğe doğru sıralar.

• RASGDİZİ (RANDARRAY): Belirlediğin boyutta ve aralıkta, rastgele sayılardan oluşan bir dizi üretir.

• Örnek: =RASGDİZİ(5; 100; 1; 500) formülü, 5 satırlık ve 100 sütunluk, 1 ile 500 arasında rastgele sayılarla dolu bir tablo oluşturur. Örnek veri oluşturmak için birebir.

Bir de Bunlar Var

• DİZİYAZ (SEQUENCE): Belirli bir başlangıç, artış ve boyutta sayı dizisi üretir.

• Not: Bu fonksiyon çok taze, bazı Excel sürümlerinde henüz yer almayabilir. Eğer bulamazsan, Excel' e yeni bir güncelleme gelip gelmediğini kontrol et.

• Örnek: =DİZİYAZ(10;1;100;5) formülü, 100’den başlayıp 5’er artan 10 sayılık bir liste oluşturur.

• YATAYYIĞ (HSTACK) / DÜŞEYYIĞ (VSTACK): Birden fazla aralığı yatay veya dikey olarak tek bir alanda birleştirir. Tabloları dinamik olarak birleştirmek için süper bir yöntem.

• METİNBİRLEŞTİR (TEXTJOIN): Birden fazla metin aralığını belirli bir ayraçla tek bir hücrede birleştirir.

Peki Eski Formüller ile Araları Nasıl?

Güzel haber, çok iyi. TOPLA, ORTALAMA ve MAK gibi eski, alışılagelmiş fonksiyonlarla birlikte kullanılabiliyorlar.

• Örnek: =ORTALAMA(FİLTRE(B2:B100; C2:C100="Ankara")) formülü, karmaşık formüller kullanmak yerine, sadece "Ankara" bölgesindeki satışların ortalamasını alarak dinamik bir sonuç üretir.

En Önemli Nokta: Taşma (Spill) Alanı ve #TAŞMA! Hatası

Bu hata ile yeni yeni karşılaşmaya başladık. Dinamik dizi formülleri süperler, ama bazen #TAŞMA! hatası veriyorlar.  Bu, Excel' in formül sonucunu yayması için yeterli alana sahip olmadığı anlamına geliyor.

• Taşma Alanı Özellikleri:

• Bir dinamik dizi formülü yazdığında, sonuçların yayıldığı alan otomatik olarak mavi bir kenarlıkla vurgulanır.

• Sadece formülün yazıldığı ilk hücre düzenlenebilir. Diğer hücreler "gölge formül" olarak görünür ve düzenlenemez.

• Hatayı Neden Alırız?

• Engellenmiş Hücreler:

Formülün sonuçlarını yaymak istediği alanda başka veriler veya bir formül var.

• Birleştirilmiş Hücreler:

Formülün sonuçlarını yaymak istediği alanda birleştirilmiş hücreler bulunuyor.

• Yetersiz Boş Alan:

Formülün, yayılmak istediği yönde sayfada yeterli boş alana sahip değil.

• Çıkış Aralığında Hata Değeri:

Formülün kaynak verisinde bir hata değeri (#YOK, #AD? vb.) varsa, #TAŞMA! hatası alabilirsin.

Çözüm: Hatanın nedenine göre engelleyen verileri temizle, birleştirilmiş hücreleri ayır veya formül için yeterli boş alan olduğundan emin ol.

Peki Niye Kullanayım Bu Fonksiyonları? 

• Daha Az Manuel İş:

Artık manuel kopyalama, yapıştırma veya karmaşık formüller yazmana gerek kalmaz.

• Daha Az Hata:

Tek bir formülde hata yapma olasılığı, onlarca hücreye formül yazmaktan çok daha azdır.

• Dinamik Sonuçlar:

Kaynak veriler değiştiğinde, dinamik formül sonuçları anında güncellenir. Bu, raporlarını daima güncel tutar.

Peki hiç negatif yönü yok mu diyorsan, evet var. Çok büyük veri setlerinde dinamik diziler, her değişiklikte yeniden hesaplandığı için işlemciyi zorlayabilir. Bu yüzden formülleri mümkün olduğunca dar aralıklara uygulamak iyi bir çözüm olabilir.

Bu konuda hazırladığım rehber sayfasına buradan ulaşabilirsin. Dinamik Dizi Fonksiyonları Rehberi