Dinamik Diziler ve Arama İşlevleriyle Analiz Yapma

Dinamik diziler ve arama işlevleriyle analiz yapma (Dynamic Arrays and Lookup Functions), Excel'de tek bir formülle birden çok hücreye yayılan sonuçlar üretmenizi ve tablolar arasında hızlı eşleştirme yapmanızı sağlayan yöntemdir. FİLTRE (FILTER), BENZERSİZ (UNIQUE), SIRALA (SORT) ve ÇAPRAZARA (XLOOKUP) gibi işlevlerle veriyi süzer, benzersiz listeler çıkarır ve değerleri başka tablolardan getirirsiniz. Dinamik dizi işlevleri Microsoft 365 ve Excel 2021'de bulunur; eski sürümlerde İNDİS ve KAÇINCI ikilisi aynı işi görür.

Dinamik dizi nedir, taşma nasıl çalışır​

Dinamik dizi işlevleri, tek bir hücreye yazdığınız formülün sonucunu komşu hücrelere kendiliğinden yaymasını sağlar. Bu davranışa taşma (spill), sonucun kapladığı alana da taşma aralığı denir. Formülü yalnızca ilk hücreye yazarsınız; aşağı doğru kopyalamanız ya da Ctrl+Shift+Enter ile girmeniz gerekmez.

Taşma aralığının tamamı boş olmalıdır. Aralıkta dolu ya da birleştirilmiş bir hücre varsa Excel #TAŞMA! (#SPILL!) hatası verir; engelleyen hücreyi temizlediğinizde formül çalışır. Taşma aralığının tamamına D2# gibi başvurabilirsiniz; # işareti, D2 hücresinden başlayan taşma aralığının güncel boyutunu temsil eder.

Aşağıdaki formül, B sütunu İstanbul olan satırların tamamını tek hücrede döndürür:
Kaynak veriyi Ekle > Tablolar > Tablo (Insert > Tables > Table) ile Excel tablosuna dönüştürüp formülde tablo sütunlarına başvurursanız, eklenen yeni satırlar sonuca kendiliğinden katılır.

ÇAPRAZARA, İNDİS ve KAÇINCI ile arama​

ÇAPRAZARA (XLOOKUP), aranan değeri bir aralıkta bulur ve başka bir aralıktan karşılığını döndürür. Bağımsız değişkenler sırasıyla aranan değer, aranan aralık, döndürülecek aralık, bulunamazsa gösterilecek değer, eşleşme modu ve arama modudur; ilk üçü zorunludur. Varsayılan eşleşme tam eşleşmedir, bu yüzden DÜŞEYARA'daki gibi son bağımsız değişkene YANLIŞ yazmanız gerekmez. Döndürülecek aralık, aranan aralığın solunda da olabilir.

Örnek bir ürün fiyatı araması:
İNDİS (INDEX) ve KAÇINCI (MATCH) ikilisi aynı işi her Excel sürümünde yapar. KAÇINCI aranan değerin aralıktaki konumunu bulur, İNDİS bu konumdaki değeri verir. Eşleşme türü olarak 0 yazarsanız tam eşleşme aranır:
ARA (LOOKUP) işlevi ise yalnızca sıralı veriyle güvenilir çalışan eski bir işlevdir; yeni çalışmalarda ÇAPRAZARA tercih edilir.

FİLTRE, BENZERSİZ ve SIRALA ile veri analizi​

FİLTRE (FILTER) koşula uyan satırları döndürür. Üçüncü bağımsız değişkene hiç sonuç çıkmazsa gösterilecek değeri yazmazsanız, eşleşme olmadığında hata alırsınız. BENZERSİZ (UNIQUE) yinelenen değerleri ayıklayıp benzersiz listeyi verir; büyük küçük harf ayrımı yapmaz. SIRALA (SORT) bir diziyi belirtilen sütuna göre sıralar: ikinci bağımsız değişken sıralanacak sütunun sırası, üçüncüsü artan için 1, azalan için -1 değeridir.

Bu işlevleri iç içe kullanabilirsiniz. İstanbul satışlarını tutara göre büyükten küçüğe sıralamak için:
Benzersiz değerlerin toplamını almak için BENZERSİZ sonucunu ÇOKETOPLA içinde ölçüt olarak verebilirsiniz; üçüncü örnekte bu yöntem uygulanmıştır.

1. Ürün koduna göre ad ve fiyat getirme​

Soldaki ürün listesinden, D sütunundaki koda göre ürün adı ve birim fiyat getirilecek. E sütunu ÇAPRAZARA, F sütunu İNDİS-KAÇINCI kullanır; listede olmayan kod için ikisi de Bulunamadı yazar. E2 hücresindeki formül:
K-104 kodu için E2 hücresi Kulaklık, F2 hücresi 890,00 değerini verir. K-102 kodunda ürün Fare, fiyat 275,00 olur. Listede bulunmayan K-999 kodunda iki formül de Bulunamadı sonucunu verir. Mutlak başvurular ($ işaretli) sayesinde formülü aşağı kopyalayabilirsiniz.

2. Şehre göre süzüp tutara göre sıralama​

Satış temsilcilerinin şehir ve tutar bilgileri A:C sütunlarında yer alıyor. D2 hücresine yazılan tek formül, İstanbul satırlarını süzer ve satış tutarına göre büyükten küçüğe sıralar; sonuç D:F sütunlarına taşar, bu yüzden bu alan boş bırakılmıştır:
D2 hücresindeki tek formül sonucu sağa ve aşağıya taşırır; en yüksek satışı yapan temsilci Zeynep Arslan olarak ilk satırda görünür. Formüldeki şehir adını değiştirirseniz taşma aralığı kendiliğinden küçülür ya da büyür. Koşula uyan satır yoksa hücrede Kayıt yok yazar.

3. Benzersiz şehirler ve şehir toplamları​

Satış listesindeki şehirler tekrar ediyor. D2 hücresindeki BENZERSİZ formülü benzersiz şehir listesini çıkarır; E2 hücresindeki formül aynı sırayla her şehrin toplam tutarını hesaplar. İki formül de aşağı taşar, bu yüzden D ve E sütunlarının altı boş bırakılmıştır:
D2 hücresindeki formül şehirleri ilk göründükleri sırayla listeler; ilk şehir İstanbul olur. E2 hücresindeki formül de aynı sırayla çalıştığı için bu şehrin toplamı 53.050,00 değerini verir. Kaynak veriye yeni bir şehir eklediğinizde iki liste de kendiliğinden genişler; ancak aralık başvurusu A2:A9 olarak sabit kaldığı için, aralığı yeni satırları kapsayacak şekilde genişletmeyi ya da Excel tablosu kullanmayı unutmayın.

Sık sorulan sorular​

• Excel 2016 ya da 2019'da çalışır mı? FİLTRE, BENZERSİZ, SIRALA ve ÇAPRAZARA yalnızca Microsoft 365 ve Excel 2021'de bulunur. Eski sürümlerde İNDİS-KAÇINCI ve DÜŞEYARA kullanın.
• ÇAPRAZARA, DÜŞEYARA'nın yerine geçer mi? Çoğu durumda evet; sola doğru arama yapabilir ve sütun numarası gerektirmez. Dosyayı eski sürüm kullanan biriyle paylaşacaksanız DÜŞEYARA ya da İNDİS-KAÇINCI daha güvenlidir.
• #TAŞMA! hatasını neden alıyorum? Taşma aralığındaki bir hücre dolu ya da birleştirilmiştir; formül ayrıca Excel tablosunun içine yazılmışsa da taşamaz. Engelleyen hücreyi temizleyin ya da formülü tablo dışına taşıyın.
• BENZERSİZ büyük küçük harfi ayırır mı? Hayır, Ankara ile ANKARA aynı değer sayılır.

Kullanırken dikkat edin​

• Sürüm gereksinimi: FİLTRE, BENZERSİZ, SIRALA ve ÇAPRAZARA yalnızca Microsoft 365 ve Excel 2021'de bulunur. Dosyayı eski sürüm kullanan biriyle paylaşacaksanız bu işlevler yerine İNDİS-KAÇINCI ve DÜŞEYARA gibi uyumlu işlevleri tercih edin.
• Taşma aralığını boş bırakın: Sonucun taşacağı hücrelerde veri, boşluk karakteri ya da birleştirilmiş hücre varsa #TAŞMA! hatası oluşur. Hatalı hücreyi seçtiğinizde çıkan uyarı simgesi engelleyen hücreyi göstermenize yardımcı olur.
• Excel tablosu içinde taşma olmaz: Dinamik dizi formülleri Excel tablosunun içine yazılırsa taşamaz. Formülü tablonun dışındaki bir hücreye yazın; kaynak olarak tabloyu kullanabilirsiniz.
• Boş sonucu yönetin: FİLTRE işlevinde koşula uyan satır yoksa hata döner. Üçüncü bağımsız değişkene Kayıt yok gibi bir metin yazın. ÇAPRAZARA'da da dördüncü bağımsız değişken aynı işi görür; İNDİS-KAÇINCI kullanıyorsanız formülü EĞERHATA ile sarın.
• PivotTable ile farkı: Dinamik dizi formülleri kaynak veri değiştiğinde anında yenilenir, PivotTable ise yenileme gerektirir. Çok boyutlu ve etkileşimli özet raporlar için PivotTable, kurala bağlı süzme ve sıralama için dinamik dizi formülleri daha uygundur.
• İlgili konular:
  • FİLTRE, BENZERSİZ, SIRALA
  • ÇAPRAZARA, ARA
  • İNDİS, KAÇINCI, DÜŞEYARA
  • ÇOKETOPLA, EĞERHATA
  • Excel tabloları ve yapısal başvurular
  • PivotTable (Özet Tablo)

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.