Excel'de Birden Fazla Sonuç Getirme: DÜŞEYARA Yerine FİLTRE

DÜŞEYARA neden yalnız ilk sonucu getirir?

DÜŞEYARA aranan değeri bulduğu ilk satırda durur ve o satırdaki değeri döndürür. Sipariş listesinde Deniz Market üç kez geçiyorsa formül her zaman ilk siparişi, yani peyniri getirir. Bu davranış bir hata değil, fonksiyonun tasarımıdır; dolayısıyla DÜŞEYARA'yı zorlamak yerine birden çok satır döndürebilen bir fonksiyona geçmek gerekir.

Microsoft 365 ve Excel 2021'de bu fonksiyon FİLTRE'dir. Eski sürümlerde ise aynı sonuca İNDİS ve TOPLAMA'dan oluşan daha uzun bir formülle ulaşılır.

FİLTRE ile bütün eşleşen satırlar

FİLTRE iki argümanla çalışır: döndürülecek alan ve hangi satırların alınacağını belirten koşul. Ürün ve tutar sütunlarını seçip koşul olarak müşteri sütununun Deniz Market'e eşit olmasını verirsiniz. Formül yazdığınız hücreden başlayarak aşağı ve sağa doğru yayılır.

=FİLTRE(B2:C9;A2:A9="Deniz Market")

=FILTER(B2:C9,A2:A9="Deniz Market")

Sonuç üç satır ve iki sütundan oluşur. Altındaki ya da sağındaki hücrelerden biri doluysa formül #YAYILMA! hatası verir; sonuç alanını boş bırakmanız yeterlidir.

Döndürülecek alanı istediğiniz gibi seçebilirsiniz. Yalnız ürün adları gerekiyorsa B2:B9 yazarsınız; müşteri adı da sonuçta görünsün istiyorsanız A2:C9 yazarsınız. Sütunların sırasını değiştirmek isterseniz alanı tek tek seçmek yerine sonucu bir başka dinamik dizi fonksiyonuna verirsiniz; sıralama için SIRALA bunun en sık görülen örneğidir.

Aranan müşteriyi formüle sabit yazmak yerine bir hücreye bağlayın. =FİLTRE(B2:C9;A2:A9=F1) yazdığınızda F1'deki adı değiştirerek her müşterinin siparişlerini aynı alanda görebilirsiniz.

Sonuç bulunamadığında

Koşulu sağlayan satır yoksa FİLTRE #HESAPLA! hatası döndürür. Üçüncü argüman bu durumda gösterilecek değeri belirler: =FİLTRE(B2:C9;A2:A9=F1;"Sipariş yok"). Özellikle aranan değerin bir açılır listeden seçildiği raporlarda bu argümanı boş bırakmamak iyi bir alışkanlıktır.

Sonuçları tek hücrede birleştirmek

Bazen eşleşmeleri alt alta değil, tek hücrede virgülle ayrılmış olarak görmek istersiniz. Müşteri listesinin yanına "aldığı ürünler" sütunu eklemek buna iyi bir örnektir. FİLTRE'nin sonucunu METİNBİRLEŞTİR'e verdiğinizde yayılma olmaz ve tek bir metin elde edersiniz.

=METİNBİRLEŞTİR(", ";DOĞRU;FİLTRE(B2:B9;A2:A9="Deniz Market"))

=TEXTJOIN(", ",TRUE,FILTER(B2:B9,A2:A9="Deniz Market"))

METİNBİRLEŞTİR'in ikinci argümanı DOĞRU olduğunda boş hücreler atlanır. Böylece ürün adı girilmemiş siparişler metinde arka arkaya iki virgül bırakmaz.

Birden fazla koşulla sonuç getirmek

FİLTRE'de koşulları çarparak VE, toplayarak YA DA mantığı kurarsınız. Deniz Market'in 500 TL üzerindeki siparişleri için koşul (A2:A9="Deniz Market")*(C2:C9>500) olur ve peynir ile zeytin döner. Sonucu tutara göre sıralamak isterseniz formülü SIRALA ile sarabilirsiniz: =SIRALA(FİLTRE(B2:C9;A2:A9=F1);2;-1).

Eski sürümler için İNDİS ve TOPLAMA

FİLTRE olmayan sürümlerde eşleşen satırların numaralarını tek tek bulmak gerekir. (SATIR($A$2:$A$9)-1)/($A$2:$A$9="Deniz Market") ifadesi koşulu sağlayan satırlar için sıra numarasını, sağlamayanlar için #SAYI/0! hatasını üretir. TOPLAMA'nın 15 numaralı hesabı KÜÇÜK'tür ve 6 seçeneği hataları atlar. Böylece TOPLAMA n'inci eşleşmenin numarasını, İNDİS de o satırdaki ürünü getirir.

=EĞERHATA(İNDİS($B$2:$B$9;TOPLAMA(15;6;(SATIR($A$2:$A$9)-1)/($A$2:$A$9="Deniz Market");2));"")

=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-1)/($A$2:$A$9="Deniz Market"),2)),"")

Formüldeki 2'yi 1, 2, 3 diye değiştirerek birinci, ikinci ve üçüncü eşleşmeyi alırsınız. Pratikte bu sayı yerine SATIR(A1) yazılır ve formül aşağı çekildikçe 1, 2, 3 olarak artar. Eşleşmeler bitince TOPLAMA hata verir, EĞERHATA da boş metin döndürür.

Aynı işi KÜÇÜK ve EĞER ile kuran yaygın bir yazım da vardır: =İNDİS($B$2:$B$9;KÜÇÜK(EĞER($A$2:$A$9=$F$1;SATIR($A$2:$A$9)-1);SATIR(A1))). Bu biçim Excel 2019 ve öncesinde Ctrl+Shift+Enter ile dizi formülü olarak girilmelidir. TOPLAMA'lı formül ise Excel 2010'dan bu yana normal Enter ile çalışır.

Kaç sonuç geleceğini önceden görmek

FİLTRE'nin kaç satıra yayılacağını bilmek, sonuç alanının altına başka bir tablo koyarken işe yarar. =EĞERSAY(A2:A9;F1) aranan müşterinin kaç siparişi olduğunu verir. Örnek listede Deniz Market için sonuç 3'tür.

Eski sürüm formülünü kopyalarken de bu sayıdan yararlanırsınız. Formülü en fazla beklediğiniz eşleşme kadar satıra çekmek yeterlidir; fazla satırlar boş görünür.

Raporunuza uygulayın

Raporunuzda aranan değeri tek bir hücreye koyun ve sonuç alanının altını boş bırakın. Microsoft 365 kullanıyorsanız FİLTRE'yi üçüncü argümanıyla birlikte yazın; eski sürümlerle paylaşılan dosyalarda İNDİS ve TOPLAMA formülünü beklediğiniz en fazla eşleşme kadar satıra kopyalayın.