Gelişmiş Filtre

Gelişmiş Filtre (Advanced Filter), ölçütleri sayfadaki ayrı bir hücre aralığında tanımlayarak listeyi süzen Excel komutudur. Otomatik Filtre'nin yetmediği durumlarda, yani birden çok sütunu Ve/Veya mantığıyla birleştirmek, formülle ölçüt yazmak, sonucu başka bir yere kopyalamak ya da yinelenen kayıtları ayıklamak için kullanılır.

Nerede bulunur​

Komut Veri > Sırala ve Filtre Uygula > Gelişmiş (Data > Sort & Filter > Advanced) yolunda yer alır. Klavyeden sırasıyla Alt, A, Q tuşlarına basarak da açabilirsiniz. Komutu çalıştırmadan önce liste içindeki herhangi bir hücreyi seçmeniz yeterlidir. Excel listenin sınırlarını kendisi algılar.

İletişim kutusundaki alanlar şunlardır:

• Eylem: Listeyi yerinde süz (Filter the list, in-place) uygun olmayan satırları gizler. Başka bir konuma kopyala (Copy to another location) uygun satırları belirttiğiniz alana kopyalar ve asıl listeye dokunmaz.
• Liste aralığı: (List range) başlık satırı dahil süzülecek veri alanı.
• Ölçüt aralığı: (Criteria range) başlık satırı ve ölçüt satırlarını içeren alan.
• Hedef: (Copy to) yalnızca kopyalama seçiliyken etkindir. Buraya yazacağınız başlık hücreleri, kopyalanacak sütunları ve sırasını belirler.
• Yalnızca benzersiz kayıtlar: (Unique records only) aynı olan satırlardan yalnızca birincisini bırakır.

Ölçüt aralığı nasıl hazırlanır​

Ölçüt aralığı, verinin dışında ama aynı sayfada bir yere kurulur. İlk satırı sütun başlıklarıdır, altındaki satırlar ölçütlerdir. Adımlar:

• Süzmek istediğiniz sütunların başlıklarını listedekiyle birebir aynı yazarak veri alanından ayrı bir yere kopyalayın.
• Başlıkların altına ölçütleri yazın. Aynı satırdaki ölçütler Ve (AND), farklı satırlardaki ölçütler Veya (OR) olarak çalışır.
• Veri içinde bir hücre seçip Veri > Sırala ve Filtre Uygula > Gelişmiş komutunu açın.
• Eylemi seçin, liste aralığını ve ölçüt aralığını girin. Kopyalama seçtiyseniz hedef başlık hücrelerini de belirtin.
• Tamam düğmesine basın.

Ölçütlerde karşılaştırma işleçlerini kullanabilirsiniz: >5000, <=100, <>Ankara gibi. Metin ölçütü 'ile başlayan' mantığıyla çalışır; yani Ank yazarsanız Ankara ile başlayan tüm değerler gelir. Joker karakterlerden * (herhangi bir uzunlukta metin) ve ? (tek karakter) kullanılabilir. Ölçüt aralığında boş bırakılan hücre o sütun için koşul koymaz.

Formülle ölçüt yazma​

Ölçüt olarak formül kullanacaksanız ölçüt sütununun başlık hücresini boş bırakın ya da listedeki hiçbir başlığa benzemeyen bir ad yazın. Altındaki hücreye, listenin ilk veri satırına göreli başvuru veren ve DOĞRU ya da YANLIŞ döndüren bir formül yazın. Veri alanı C2:C11 ise ortalamanın üzerindeki tutarları süzen ölçüt şöyledir:
İlk veri satırına başvuru göreli (C2), karşılaştırılan aralık mutlak ($C$2:$C$11) olmalıdır. Formül hücresi sayfada DOĞRU ya da YANLIŞ gösterir; bu normaldir, süzme her satır için formülü ayrı ayrı değerlendirir.

Yinelenenleri ayıklama ve sonucu yenileme​

Yalnızca benzersiz kayıtlar kutusunu işaretlerseniz ölçüt aralığı bile girmeden yinelenen satırları ayıklayabilirsiniz. Kopyalama eyleminde hedef başlıklarına yalnızca bazı sütun adlarını yazarsanız benzersizlik de yalnızca o sütunlara göre değerlendirilir.

Gelişmiş Filtre, Otomatik Filtre gibi kendiliğinden güncellenmez. Veri ya da ölçüt değişince yerinde süzülmüş listeyi Veri > Sırala ve Filtre Uygula > Yeniden Uygula (Reapply) ile tazeleyebilirsiniz. Süzmeyi kaldırmak için Veri > Sırala ve Filtre Uygula > Temizle (Clear) komutunu kullanın. Kopyalama eylemiyle oluşan çıktı ise sabit değerlerden oluşur ve yeniden filtrelemek için komutu yeniden çalıştırmanız gerekir.

1. Ve ölçütüyle sonucu başka alana kopyalama​

A1:C11 aralığında satış listesi, E1:F2 aralığında ölçüt aralığı hazırdır. Ölçüt aynı satırda olduğu için hem Şehir İstanbul hem Tutar 5000'den büyük olmalıdır. Excel'de listede bir hücre seçin, Veri > Sırala ve Filtre Uygula > Gelişmiş komutunu açın, Başka bir konuma kopyala seçin. Liste aralığına A1:C11, ölçüt aralığına E1:F2, Hedef alanına H1:J1 yazın (H1:J1'e önce Personel, Şehir, Tutar başlıklarını yazın). Örnek dosyada bu işlemi makro yapar.
H1:J1 başlıklarının altına yalnızca iki koşulu birden sağlayan satırlar kopyalanır: İstanbul'daki ve tutarı 5000'in üzerinde olan satışlar. A:C aralığındaki asıl liste olduğu gibi kalır; hiçbir satır gizlenmez.

2. Veya ölçütüyle listeyi yerinde süzme​

A1:C11 aralığında müşteri siparişleri, E1:F3 aralığında ölçüt aralığı vardır. Ölçütler farklı satırlarda olduğu için Şehir Ankara olan ya da Adet değeri 50'den büyük olan kayıtlar listelenir. Excel'de Veri > Sırala ve Filtre Uygula > Gelişmiş komutunda Listeyi yerinde süz seçin, liste aralığına A1:C11, ölçüt aralığına E1:F3 yazın. Ölçüt satırları aynı satır numaralarındaki verilerle çakıştığından, o satırların ölçütü sağlaması örnekte bilerek ayarlanmıştır.
Liste yerinde süzülür: Ankara'daki müşteriler ile adedi 50'nin üzerinde olan müşteriler görünür, diğer satırların satır numaraları mavi renkte gizli kalır. Ölçüt aralığı farklı satırlara yazıldığı için iki koşuldan yalnızca birini sağlayan kayıt da listede yer alır. Süzmeyi kaldırmak için Veri > Sırala ve Filtre Uygula > Temizle komutunu kullanın.

3. Benzersiz kayıtları ayıklama​

A1:C11 aralığında şehir, ürün ve adet bilgisi içeren satış hareketleri vardır; aynı şehir ve ürün çifti birden çok kez geçmektedir. Ölçüt aralığı girmeden Veri > Sırala ve Filtre Uygula > Gelişmiş komutunu açın, Başka bir konuma kopyala ve Yalnızca benzersiz kayıtlar kutusunu işaretleyin. Liste aralığına A1:C11, Hedef alanına E1:F1 yazın; E1 ve F1 hücrelerine Şehir ve Ürün başlıklarını önceden yazın. Örnek dosyada işlemi makro yapar.
E1:F1 başlıklarının altına yalnızca Şehir ve Ürün sütunları kopyalanır ve aynı şehir-ürün çifti tekrar etmez. Adet sütunu hedefte olmadığı için benzersizlik yalnızca iki sütuna göre değerlendirilir. Asıl liste değişmeden kalır.

Kullanırken dikkat edin​

• Başlıklar birebir aynı olmalı: Ölçüt aralığındaki başlıklar listedeki başlıklarla harfi harfine aynı olmalıdır. Fazladan boşluk ya da farklı yazım ölçütün sessizce çalışmamasına neden olur. Başlıkları elle yazmak yerine listeden kopyalamak en güvenlisidir.
• Boş ölçüt satırı her şeyi getirir: Ölçüt aralığına içi boş bir satır dahil ederseniz, bu satır 'koşul yok' anlamına gelir ve tüm kayıtlar süzmeyi geçer. Ölçüt aralığını yalnızca dolu satırları kapsayacak şekilde belirleyin.
• Ölçüt aralığını yerinde süzülen verinin satırlarına koymayın: Yerinde süzme yaparken ölçüt hücreleriniz liste ile aynı satır numaralarındaysa, o satırlar süzmede gizlenebilir. Ölçütleri listenin üstüne ya da bu satırlardan bağımsız bir bölgeye yazmak daha güvenlidir.
• Başka sayfaya kopyalama sınırı: Arayüzden sonucu yalnızca etkin sayfaya kopyalayabilirsiniz. Sonucu başka bir sayfada istiyorsanız komutu hedef sayfadayken başlatın ve liste ile ölçüt aralığını diğer sayfadan seçin.
• Metin ölçütü 'ile başlar' gibi çalışır: Ölçüt hücresine Ankara yazarsanız Ankara ile başlayan tüm değerler eşleşir. Tam eşleşme gerekiyorsa ölçütü eşittir işaretiyle metin biçiminde yazmanız gerekir. Büyük küçük harf ayrımı yoktur.
• Sonuç otomatik güncellenmez: Veri değiştiğinde süzme kendiliğinden yenilenmez. Yeniden Uygula (Reapply) komutunu kullanın. Kopyalama eyleminde ise komutu baştan çalıştırmanız gerekir. Sık kullanılan aralıklara ad vermek (Liste, Ölçüt, Çıktı) yönetimi kolaylaştırır.
• VBA ile kullanım: Range.AdvancedFilter yöntemi aynı işlemi kodla yapar. Action bağımsız değişkeni için xlFilterInPlace ya da xlFilterCopy, ayrıca CriteriaRange, CopyToRange ve Unique kullanılır.
• İlgili konular: Otomatik Filtre, Veri > Yinelenenleri Kaldır, Ad Yöneticisi, Tablo, Yeniden Uygula, Temizle, FİLTRE işlevi (Microsoft 365 ve Excel 2021 sonrası), BENZERSİZ işlevi, ORTALAMA, VE, VEYA, Range.AdvancedFilter yöntemi.

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