İleri Ders 2: Dinamik Diziler ile FİLTRE, SIRALA ve BENZERSİZ

Dinamik dizi ne demek?

Dinamik dizi, tek hücreye yazdığınız formülün sonucunu gerektiği kadar hücreye kendiliğinden yaymasıdır. Microsoft 365 ve Excel 2021'den itibaren FİLTRE gibi bir formül beş satır sonuç bulursa bu beş satırı alt alta doldurur. Kaynak veri değişince sonuç alanı da büyür ya da küçülür.

Yayılan alanın yalnızca ilk hücresinde formül vardır. Diğer hücreleri seçtiğinizde formül çubuğunda gri renkli bir formül görürsünüz ve onları tek tek düzenleyemezsiniz. Yayılma alanında önceden yazılmış bir değer varsa Excel sonucu yazamaz ve #TAŞMA! hatası verir. Hücreyi boşaltınca formül çalışır.

Bu dersteki örneklerde sekiz satırlık bir satış listesi kullanılıyor. Listede bölge, müşteri ve sipariş tutarı var.

FİLTRE ile koşullu liste

FİLTRE, bir aralıktan koşulu sağlayan satırları döndürür: =FİLTRE(dizi; ekle; [boşsa]). İkinci argüman her satır için DOĞRU ya da YANLIŞ üreten bir karşılaştırmadır.

4.000 TL ve üzerindeki siparişlerin müşterilerini görmek için =FİLTRE(B2:B9;C2:C9>=4000) yazarsınız. Sonuç Akın Gıda, Bera Yapı ve Deniz Lojistik olarak üç satıra yayılır. Hiçbir satır koşula uymazsa formül hata verir. Üçüncü argümana "Kayıt yok" gibi bir metin yazarak bunu önlersiniz.

İki koşulu birlikte aramak için karşılaştırmaları çarparsınız. (A2:A9="İzmir")*(C2:C9>3000) hem İzmir'de olan hem 3.000 TL'yi geçen satırları seçer. Toplama işareti ise "ya da" anlamına gelir.

SIRALA ile düzenli rapor

SIRALA, bir diziyi seçtiğiniz sütuna göre sıralar: =SIRALA(dizi; [sıralama_dizini]; [sıralama_düzeni]). Sıralama düzeni 1 küçükten büyüğe, -1 büyükten küçüğe demektir.

SIRALA'nın asıl gücü, FİLTRE'nin sonucunu almasıdır. İzmir siparişlerini tutara göre büyükten küçüğe dizmek için =SIRALA(FİLTRE(B2:C9;A2:A9="İzmir");2;-1) yazarsınız. İlk satırda 4.900 TL ile Deniz Lojistik çıkar. Bu formülü İNDİS ile sarıp yalnızca birinci satırı alırsanız bölgenin en büyük müşterisini tek hücrede görürsünüz.

BENZERSİZ ile tekrarsız liste

BENZERSİZ, bir aralıktaki her değeri bir kez listeler. Listede Kaya Tekstil ile Ova Tarım ikişer kez geçiyor. =BENZERSİZ(B2:B9) altı farklı müşteriyi alt alta yazar.

Kaç farklı müşteri olduğunu sormak için sonucu SATIRSAY ile sayarsınız. =SATIRSAY(BENZERSİZ(B2:B9)) 6 döndürür. BAĞ_DEĞ_DOLU_SAY da çoğu durumda aynı sonucu verir. Ancak boş hücreleri BENZERSİZ tek bir boş satır olarak listelediği için kaynak aralıkta boşluk varsa sayınız bir fazla çıkabilir.

FİLTRE sonucunu hesapta kullanmak

FİLTRE'nin döndürdüğü dizi, başka bir fonksiyonun girdisi olabilir. =TOPLA(FİLTRE(C2:C9;A2:A9="Ankara")) Ankara siparişlerinin toplamını, yani 11.600 TL'yi verir. Bu basit toplam için ÇOKETOPLA da yeterlidir, ama ÇOKETOPLA'nın karşılığı olmayan fonksiyonlarda FİLTRE işe yarar.

Ankara siparişlerinin ortanca tutarını bulmak için =ORTANCA(FİLTRE(C2:C9;A2:A9="Ankara")) yazarsınız ve sonuç 4.200 TL olur. Excel'de "ÇOKEĞERORTANCA" gibi bir fonksiyon olmadığı için eski sürümlerde bu hesap dizi formülü gerektirirdi. Aynı yöntem BÜYÜK, KÜÇÜK ve RANK.EŞİT gibi fonksiyonlara da uygulanır.

Bir haftalık rapor senaryosu

Bu üç fonksiyonun birlikte nasıl çalıştığını görmek için her pazartesi hazırlanan bir bölge raporunu düşünün. Satış ekibi siparişleri tek bir listeye giriyor; yönetici ise her bölgenin müşterilerini ciroya göre sıralı görmek istiyor.

Rapor sayfasında B1 hücresine bölge adını yazdığınız bir alan açın. A3'e =SIRALA(FİLTRE(Veri!B2:C200;Veri!A2:A200=B1;"Kayıt yok");2;-1) yazdığınızda seçilen bölgenin müşterileri ve tutarları büyükten küçüğe dizilir. Bölge adını değiştirmek, raporun tamamını yeniden kurar.

Aynı sayfada D1 hücresine =BENZERSİZ(Veri!A2:A200) yazarsanız bölgelerin güncel listesini de elde edersiniz. Yeni bir bölgeye ilk sipariş girildiğinde liste kendiliğinden uzar. Bu listeyi Veri Doğrulama'daki açılır listenin kaynağı yapmak için D1# başvurusunu kullanabilirsiniz; böylece B1'e yalnızca geçerli bölgeler seçilebilir.

Eski sürümlerle uyumluluk

Bu üç fonksiyon Excel 2019 ve öncesinde yoktur. Dosyayı eski sürümde açan biri formül hücrelerinde #AD? görür. Ortak çalıştığınız kişilerin sürümünü bilmiyorsanız raporu bitirdikten sonra sonuçları kopyalayıp yalnızca değer olarak yapıştırdığınız ayrı bir kopya göndermek güvenli bir yoldur.

Yayılan bir sonuca başka formülden başvurmak için ilk hücrenin sonuna # işareti koyarsınız. E2'deki FİLTRE sonucunun tamamı E2# ile çağrılır ve sonuç büyüdükçe başvuru da büyür.

Sıradaki adım

Alıştırmada her bölgede kaç farklı müşteri olduğunu tek formülle hesaplayacaksınız. FİLTRE ile bölgenin müşterilerini çekip BENZERSİZ ile tekrarları atmanız, ardından SATIRSAY ile saymanız gerekiyor. Formülü kurarken her katmanı ayrı bir hücrede deneyip sonucun yayılışını izlemek, hatayı bulmayı kolaylaştırır.

=FİLTRE(B2:B9;C2:C9>=4000)

=FILTER(B2:B9,C2:C9>=4000)

=SATIRSAY(BENZERSİZ(B2:B9))

=ROWS(UNIQUE(B2:B9))

=İNDİS(SIRALA(FİLTRE(B2:C9;A2:A9="İzmir");2;-1);1;1)

=INDEX(SORT(FILTER(B2:C9,A2:A9="İzmir"),2,-1),1,1)