Veri Analizinde Ustalık: FİLTRELE, EŞLEŞTİR ve ARA.TOPLAM

FİLTRELE, EŞLEŞTİR ve ARA.TOPLAM işlevleri, statik Excel tablolarını dinamik analiz araçlarına dönüştürür. Bu işlevler, koşullara göre gerekli verileri anında seçmenize, listedeki öğelerin konumlarını bulmanıza ve filtre tarafından gizlenen kayıtları yok sayarak yalnızca görünür satırların toplamını hesaplamana olanak tanır. Hantal özet tablolar kullanmadan raporlar oluşturmak için ideal bir kombinasyondur. Aşağıda her bir işlevin somut kullanım örnekleri ve bunların birlikte nasıl kullanılacağı açıklanmaktadır.

FİLTRELE İşlevi ile Dinamik Filtreleme

FİLTRELE işlevi (Excel 365 ve Excel 2021+ sürümlerinde mevcuttur), belirtilen kriterlere uyan bir veri dizisi döndürür. Standart otomatik filtrenin aksine, bu formülün sonucu komşu hücrelere otomatik olarak "yayılır" ve kaynak veriler değiştiğinde güncellenir.

Sözdizimi: =FİLTRELE(dizi; koşul; [boşsa])

• dizi: Filtrelenmesi gereken veri aralığı.
• koşul: Mantıksal ifade (örneğin, B2:B10="Moskova").
• boşsa: Uygun satır bulunamazsa görünecek metin veya değer (#BOŞLUK! hatasından korur).

Pratik Örnek

B sütununun "Bölge", D sütununun ise "Tutar" olduğu A2:D10 aralığında bir satış tablosu olduğunu varsayalım. Moskova'ya ait tüm satışların listesini almak için:

=FİLTRELE(A2:D10; B2:B10="Moskova"; "Bölgeye ait veri yok")

Aynı anda birden fazla koşul uygulamak gerekirse (mantıksal "VE"), koşullar çarpılır. Örneğin, tutarı 5000'den büyük olan Moskova satışlarını bulmak için:

=FİLTRELE(A2:D10; (B2:B10="Moskova") * (D2:D10>5000))

EŞLEŞTİR ile Öğenin Konumunu Bulma

EŞLEŞTİR işlevi (İngilizce: MATCH), değerin kendisini döndürmez, bunun yerine belirtilen aralıktaki konum numarasını (satır veya sütun) gösterir. Bu, eski VLOOKUP (DÜŞEYARA) işlevine modern ve daha esnek bir alternatif olan İNDEKS işlevi ile bağlantı kurmak için anahtar bir araçtır.

Sözdizimi: =EŞLEŞTİR(aranan_değer; arama_dizisi; [eşleşme_türü])

• 0 — Tam eşleşme (%99 durumda kullanılır).
• 1 — En yakın küçük değeri arama (artan sıralama gerektirir).
• -1 — En yakın büyük değeri arama (azalan sıralama gerektirir).

Kullanım Örneği

A2:A10 sütununda ürünlerin listelendiğini varsayalım. "Armut" ürününün kaçıncı sırada olduğunu öğrenmemiz gerekiyor:

=EŞLEŞTİR("Armut"; A2:A10; 0)

"Armut" aralığın üçüncü hücresinde bulunuyorsa, formül 3 sayısını döndürür.

Bu sonuç genellikle aynı satırdaki başka bir sütundan veri çekmek için İNDEKS işlevi içinde kullanılır:

=İNDEKS(D2:D10; EŞLEŞTİR("Armut"; A2:A10; 0))

Bu kombinasyon, A sütununda "Armut"u bulacak ve D sütunundan karşılık gelen tutar değerini döndürecektir.

Akıllı Toplam Hesaplama: ARA.TOPLAM

ARA.TOPLAM işlevinin (İngilizce: SUBTOTAL) en önemli özelliği, manuel olarak veya filtre kullanılarak gizlenen satırları yok saymasıdır. Normal TOPLA işlevi her şeyi hesaplar, ancak bu işlev yalnızca ekranda görünenleri hesaplar.

Sözdizimi: =ARA.TOPLAM(işlev_numarası; aralık)

İşlev numarası işlemi belirler:

• Sayılar 1–11: Manuel olarak gizlenen satırları dahil eder, ancak filtrelenenleri yok sayar.
• Sayılar 101–111: Tüm gizli satırları (hem manuel hem de filtre ile gizlenenleri) yok sayar.

Uygulama Örneği

Bir tabloyu filtreleyip yalnızca "Moskova" bölgesini bıraktığınızda, formül:

=ARA.TOPLAM(109; D2:D100)

otomatik olarak yalnızca görünür satırların toplamını yeniden hesaplayacaktır. Filtre kaldırıldığında toplam tüm verileri içerecek şekilde güncellenecektir.

Kapsamlı Örnek: Analitik Gösterge Paneli

Bu işlevlerin birleştirilmesi, tek bir hücrede veya küçük bir aralıkta güçlü raporlar oluşturmaya olanak tanır.

Görev: "Elektronik" kategorisindeki filtrelenmiş ürünler arasında en yüksek satışın toplamını bulun.

1. Önce "Elektronik" kategorisi için tutar dizisini filtreleyelim: FİLTRELE(D2:D100; C2:C100="Elektronik")
2. Bu filtrelenmiş listedeki maksimum değeri bulalım: MAKS(FİLTRELE(...))
3. En yüksek satışa sahip ürünü görüntülemek gerekirse, filtrelenmiş dizi üzerinde İNDEKS+EŞLEŞTİR kombinasyonunu kullanırız.

Dinamik bir liste için toplam elde etmeye yönelik basit bir kombinasyon örneği:

=TOPLA(FİLTRELE(D2:D100; (C2:C100="Elektronik")*(D2:D100>1000)))

Burada, FİLTRELE hemen gerekli diziyi oluşturduğu için ara toplamlar kullanmadan 1000 rubleden pahalı elektronik satışlarını topluyoruz. Ancak, veriler kullanıcı tarafından Excel arayüzü aracılığıyla zaten filtrelenmişse, "Toplam" son satırı için ARA.TOPLAM(109; D2:D100) vazgeçilmez olmaya devam edecektir.

Sık Yapılan Hatalar ve Çözümleri

• FİLTRELE'de #BOŞLUK! (SPILL!) Hatası: Formülün sonucunu yazdırmaya çalıştığı hücrelere bir şey engel olduğunda (başka verilerle dolu veya birleştirilmiş) ortaya çıkar. Formülün altına ve sağına yer açın.
• EŞLEŞTİR'de #YOK Hatası: Veri türünü kontrol edin. 5 sayısı ve "5" metni eşit değildir. Aranan değer ve aralıkta aynı formatın olduğundan emin olun.
• ARA.TOPLAM'da Yanlış Toplam: 109 yerine 9 kodunu (veya tam tersini) kullandınız. Unutmayın: 109 tamamen gizlenen her şeyi yok sayar, 9 ise yalnızca filtrelenenleri yok sayar.

SSS

FİLTRELE ile normal otomatik filtre arasındaki temel fark nedir? Otomatik filtre satırları görsel olarak gizler, ancak bunlar tabloda kalır. FİLTRELE işlevi, sayfanın başka bir yerinde yeni bir veri kopyası oluşturur ve bu kopya, kaynak tabloyu etkilemeden diğer formüllerde kullanılabilir.

EŞLEŞTİR soldan sağa ve sağdan sola arama için kullanılabilir mi? Evet. Yalnızca ilk sütunda arama yapan DÜŞEYARA'nın aksine, EŞLEŞTİR bir değeri aralığın herhangi bir sütununda arayabilir ve İNDEKS aynı satırdaki başka herhangi bir sütundan veri döndürebilir.

Neden toplam satırları için ARA.TOPLAM, TOPLA'dan daha iyidir? Detaylı analiz için tabloyu filtrelemeyi planlıyorsanız, TOPLA gizli veriler dahil tüm verilerin genel toplamını göstererek resmi bozar. ARA.TOPLAM ise şu anda gördüğünüz şeyin toplamını gösterir.