Genel Bakış
AdvancedFilter (Range.AdvancedFilter), bir liste aralığını ayrı bir ölçüt aralığındaki koşullara göre süzen Range yöntemidir. Süzülen kayıtları aynı yerde bırakıp diğer satırları gizleyebilir ya da eşleşen kayıtları başka bir konuma kopyalayabilir. Otomatik Süzgeç'in yetmediği VE/VEYA birleşimli koşullarda ve benzersiz kayıt listelerinde tercih edilir.
Bağımsız değişkenleri adlandırarak (Action:=..., Unique:=...) yazmak, atlanan değişkenlerin yerine virgül bırakma zorunluluğunu ortadan kaldırır ve kodu okunur kılar.
Yöntem işlem sonucunda kullanılabilir bir değer döndürmez; sonucu sayfada (gizlenen satırlarda ya da kopyalanan kayıtlarda) görürsünüz. Süzülen kayıtları saymak için Range üzerinde ayrıca işlem yapmanız gerekir.
Metin ölçütleri varsayılan olarak "ile başlayan" mantığıyla çalışır; yani Ankara yazdığınızda Ankara ile başlayan tüm değerler eşleşir. Tam eşleşme istiyorsanız ölçüt hücresine şu formülü yazın:
Hesaplanan ölçüt de kullanabilirsiniz. Bunun için ölçüt hücresinin başlığını boş bırakın ya da listedeki başlıklardan farklı bir ad verin; formül, listenin ilk veri satırına göreli başvuru yaparak DOĞRU ya da YANLIŞ döndürmelidir. Örneğin maaşı ortalamanın üzerinde olanları süzmek için ölçüt hücresine şunu yazabilirsiniz:
Kopyalamada hedef aralığa yalnızca bazı sütun başlıklarını yazarsanız yalnızca o sütunlar kopyalanır. Bu teknik, Unique:=True ile birleştirildiğinde tek sütunluk benzersiz değer listesi çıkarmanın en kısa yoludur.
Makro çalışınca Şehir değeri Ankara olmayan satırlar (3, 4, 6, 7 ve 9. satırlar) gizlenir; yalnızca başlık satırı ile Ankara kayıtları olan 2, 5 ve 8. satırlar görünür kalır. Ölçüt hücreleri listenin yanındaki satırlarda durduğu için 2. satırın görünür kalması önemlidir; bu örnekte 2. satır bir Ankara kaydıdır. Tüm satırları geri getirmek için FiltreyiTemizle makrosunu çalıştırın.
Excel, B sütunundaki şehirleri ilk göründükleri sırayla E sütununa kopyalar ve tekrarları eler. E2 hücresine İstanbul, E3 hücresine Ankara, E4 hücresine İzmir ve E5 hücresine Bursa yazılır. Asıl liste (A:C sütunları) değişmez. Yeni şehir eklediğinizde sonucun güncellenmesi için makroyu yeniden çalıştırmanız gerekir.
Ölçütler aynı satırda olduğu için iki koşulun birlikte sağlanması gerekir (VE). Eşleşen kayıtlar A10 hücresinden başlayarak sıralanır: ilk kayıt Ayşe Demir kişisinindir ve maaşı 32.000 olarak kopyalanır; sonraki kayıtlar Can Yıldız ve Burak Çelik kişileridir. Asıl liste değişmez; Zeynep Arslan maaş koşulunu, Mehmet Kaya ve Elif Şahin ise departman koşulunu sağlamadığı için sonuca girmez.
Söz dizimi
Yöntem, süzülecek listeyi temsil eden bir Range nesnesi üzerinde çağrılır. Excel arayüzündeki karşılığı Veri > Sırala ve Filtre Uygula > Gelişmiş (Data > Sort & Filter > Advanced) komutudur; iletişim kutusundaki Liste aralığı (List range), Ölçüt aralığı (Criteria range), Başka bir konuma kopyala (Copy to another location) ve Yalnızca benzersiz kayıtlar (Unique records only) seçenekleri yöntemin bağımsız değişkenlerine karşılık gelir.ifade.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)- Action: Zorunludur. Süzme biçimini belirler; xlFilterInPlace listeyi olduğu yerde süzer, xlFilterCopy sonucu başka bir konuma kopyalar.
- CriteriaRange: İsteğe bağlıdır. Başlık satırı dahil ölçüt aralığıdır. Atlanırsa ölçüt uygulanmaz ve tüm kayıtlar süzme sonucuna girer.
- CopyToRange: İsteğe bağlıdır. xlFilterCopy kullanıldığında sonucun yazılacağı hedef aralıktır (başlık hücreleri). xlFilterInPlace ile verilse bile yok sayılır.
- Unique: İsteğe bağlıdır. True ise yinelenen kayıtlar elenir ve yalnızca benzersiz kayıtlar döner; False ise koşulu sağlayan tüm kayıtlar döner. Varsayılan değer False'tur.
Bağımsız değişkenleri adlandırarak (Action:=..., Unique:=...) yazmak, atlanan değişkenlerin yerine virgül bırakma zorunluluğunu ortadan kaldırır ve kodu okunur kılar.
Sabitler ve dönüş değeri
Action bağımsız değişkeni XlFilterAction sabitlerinden birini alır:- xlFilterInPlace (1): Koşula uymayan satırlar gizlenir, liste yerinde kalır.
- xlFilterCopy (2): Koşula uyan kayıtlar CopyToRange ile belirtilen konuma kopyalanır; asıl liste değişmez.
Yöntem işlem sonucunda kullanılabilir bir değer döndürmez; sonucu sayfada (gizlenen satırlarda ya da kopyalanan kayıtlarda) görürsünüz. Süzülen kayıtları saymak için Range üzerinde ayrıca işlem yapmanız gerekir.
Ölçüt aralığı nasıl hazırlanır
Ölçüt aralığının ilk satırı başlıklardır ve bu başlıklar listedeki sütun başlıklarıyla birebir aynı yazılmalıdır. Başlıkların altındaki satırlar koşulları taşır:- VE koşulu: Farklı sütunlardaki koşulları aynı satıra yazın; kayıt, o satırdaki tüm koşulları sağlamalıdır.
- VEYA koşulu: Koşulları alt alta farklı satırlara yazın; kayıt satırlardan herhangi birini sağlarsa listeye girer.
- Karşılaştırma işleçleri: >=30000, <>Ankara, <100 gibi ifadeler kullanılabilir.
- Joker karakterler: Yıldız (*) ve soru işareti (?) metin ölçütlerinde kullanılabilir.
Metin ölçütleri varsayılan olarak "ile başlayan" mantığıyla çalışır; yani Ankara yazdığınızda Ankara ile başlayan tüm değerler eşleşir. Tam eşleşme istiyorsanız ölçüt hücresine şu formülü yazın:
="=Ankara"Hesaplanan ölçüt de kullanabilirsiniz. Bunun için ölçüt hücresinin başlığını boş bırakın ya da listedeki başlıklardan farklı bir ad verin; formül, listenin ilk veri satırına göreli başvuru yaparak DOĞRU ya da YANLIŞ döndürmelidir. Örneğin maaşı ortalamanın üzerinde olanları süzmek için ölçüt hücresine şunu yazabilirsiniz:
Yerinde süzme ile kopyalama farkı
xlFilterInPlace kullanıldığında satırlar silinmez, yalnızca gizlenir; süzmeyi kaldırmak için Veri > Sırala ve Filtre Uygula > Temizle (Data > Sort & Filter > Clear) komutunu ya da kodda ShowAllData yöntemini kullanırsınız. xlFilterCopy kullandığınızda ise asıl listeye dokunulmaz; sonuç hedef aralıktan başlayarak aşağıya yazılır.Kopyalamada hedef aralığa yalnızca bazı sütun başlıklarını yazarsanız yalnızca o sütunlar kopyalanır. Bu teknik, Unique:=True ile birleştirildiğinde tek sütunluk benzersiz değer listesi çıkarmanın en kısa yoludur.
1. Listeyi yerinde süzme
A1:D9 aralığında ürün satışları, F1:F2 aralığında ise ölçüt vardır (başlık Şehir, koşul Ankara). Aşağıdaki kodu standart modüle yapıştırın ve AnkaraKayitlariniSuz makrosunu çalıştırın. İkinci makro, süzmeyi kaldırmak içindir.
VBA
21 satır
Option Explicit
' Ankara kayıtlarını yerinde süzer
Sub AnkaraKayitlariniSuz()
Dim ws As Worksheet
Set ws = ActiveSheet
' Önceden uygulanmış süzme varsa kaldır
If ws.FilterMode Then ws.ShowAllData
' Liste: A1:D9, ölçüt: F1:F2
ws.Range("A1:D9").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=ws.Range("F1:F2"), Unique:=False
End Sub
' Süzmeyi kaldırıp tüm satırları geri getirir
Sub FiltreyiTemizle()
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.FilterMode Then ws.ShowAllData
End SubAdvancedFilter
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ürün | Şehir | Adet | Tutar | Şehir | |
| 2 | Dizüstü Bilgisayar | Ankara | 4 | 98.000,00 | Ankara | |
| 3 | Yazıcı | İzmir | 6 | 21.000,00 | ||
| 4 | Monitör | İstanbul | 10 | 45.000,00 | ||
| 5 | Klavye | Ankara | 25 | 12.500,00 | ||
| 6 | Tablet | Bursa | 5 | 37.500,00 | ||
| 7 | Fare | İstanbul | 40 | 8.000,00 | ||
| 8 | Tarayıcı | Ankara | 3 | 15.600,00 | ||
| 9 | Kulaklık | İzmir | 12 | 9.600,00 |
Satış ListesiExcelDepo v1.0.0
2. Benzersiz değer listesi çıkarma
A1:C11 aralığında satış kayıtları vardır. E1 hücresindeki Şehir başlığı, yalnızca o sütunun kopyalanacağını belirtir. Kodu standart modüle yapıştırıp BenzersizSehirler makrosunu çalıştırın.
VBA
14 satır
Option Explicit
' Şehir sütunundaki benzersiz değerleri E sütununa çıkarır
Sub BenzersizSehirler()
Dim ws As Worksheet
Set ws = ActiveSheet
' Önceki sonucu temizle (başlık E1'de kalır)
ws.Range("E2:E20").ClearContents
' Ölçüt yok; yalnızca E1 başlığındaki sütun, benzersiz olarak kopyalanır
ws.Range("A1:C11").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("E1"), Unique:=True
End SubAdvancedFilter
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Ürün | Şehir | Tutar | Şehir | |
| 2 | Klavye | İstanbul | 12.500,00 | İstanbul | |
| 3 | Fare | Ankara | 8.000,00 | Ankara | |
| 4 | Monitör | İstanbul | 45.000,00 | İzmir | |
| 5 | Yazıcı | İzmir | 21.000,00 | Bursa | |
| 6 | Tablet | Ankara | 37.500,00 | ||
| 7 | Kulaklık | Bursa | 9.600,00 | ||
| 8 | Tarayıcı | İzmir | 15.600,00 | ||
| 9 | Fare | İstanbul | 8.200,00 | ||
| 10 | Klavye | Bursa | 11.900,00 | ||
| 11 | Monitör | Ankara | 46.000,00 |
Şehir ListesiExcelDepo v1.0.0
3. VE koşuluyla süzüp kopyalama
A1:C7 aralığında personel listesi, E1:F2 aralığında ölçüt vardır: Departman Satış ve Maaş en az 30000. Sonuçlar, A9:C9 başlıklarının altına kopyalanacaktır. Kodu standart modüle yapıştırıp SatisPersoneliniKopyala makrosunu çalıştırın.
VBA
15 satır
Option Explicit
' Satış departmanında maaşı 30000 ve üzeri olanları kopyalar
Sub SatisPersoneliniKopyala()
Dim ws As Worksheet
Set ws = ActiveSheet
' Önceki sonuçları temizle (başlıklar A9:C9'da kalır)
ws.Range("A10:C30").ClearContents
' Liste: A1:C7, ölçüt: E1:F2, hedef: A9:C9
ws.Range("A1:C7").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws.Range("E1:F2"), _
CopyToRange:=ws.Range("A9:C9"), Unique:=False
End SubAdvancedFilter
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ad Soyad | Departman | Maaş | Departman | Maaş | |
| 2 | Ayşe Demir | Satış | 32.000 | Satış | >=30000 | |
| 3 | Mehmet Kaya | Muhasebe | 41.000 | |||
| 4 | Zeynep Arslan | Satış | 28.500 | |||
| 5 | Can Yıldız | Satış | 35.000 | |||
| 6 | Elif Şahin | İnsan Kaynakları | 30.500 | |||
| 7 | Burak Çelik | Satış | 31.000 | |||
| 8 | ||||||
| 9 | Ad Soyad | Departman | Maaş | |||
| 10 | Ayşe Demir | Satış | 32.000 | |||
| 11 | Can Yıldız | Satış | 35.000 | |||
| 12 | Burak Çelik | Satış | 31.000 |
PersonelExcelDepo v1.0.0
Kullanırken dikkat edin
- Başlıklar birebir eşleşmeli: Ölçüt aralığındaki başlıklar listedeki başlıklarla aynı yazılmalıdır; fazladan boşluk ya da farklı yazım, koşulun hiçbir kaydı eşleştirmemesine yol açar. Başlıkları elle yazmak yerine listeden kopyalamak en güvenli yoldur.
- Ölçüt aralığında boş satır bırakmayın: Ölçüt aralığına koşul içermeyen boş bir satır dahil ederseniz, bu satır her kaydı sağlar ve süzme fiilen işe yaramaz duruma gelir. CriteriaRange değişkenine yalnızca başlık ve dolu koşul satırlarını verin.
- Yerinde süzmede ölçüt satırı gizlenebilir: xlFilterInPlace tüm sayfa satırını gizler. Ölçüt hücreleriniz listeyle aynı satırlardaysa ve o satır koşulu sağlamıyorsa ölçüt de gizlenir. Ölçütü listenin üstündeki boş satırlara ya da süzmeden etkilenmeyen bir sayfaya koymak daha güvenlidir.
- Hedef alan sessizce üzerine yazılır: xlFilterCopy hedef aralıktan itibaren aşağıdaki hücrelerin üzerine uyarı vermeden yazabilir. Hedefin altında önemli veri bırakmayın; tekrar çalıştırmadan önce eski sonuçları ClearContents ile temizlemeniz iyi bir alışkanlıktır.
- Unique yalnızca kopyalanan sütunlara bakar: Hedef aralığa yalnızca bazı başlıkları yazdıysanız benzersizlik de yalnızca o sütunlar üzerinden değerlendirilir. Tüm sütunlara göre benzersiz kayıt istiyorsanız hedefe tüm başlıkları yazın.
- Süzmeyi kaldırırken FilterMode denetleyin: Süzme uygulanmamışken ShowAllData yöntemini çağırmak hata verebilir. Önce FilterMode özelliğinin True olup olmadığını denetleyin.
- Sonuç dinamik değildir: Veri ya da ölçüt değiştiğinde sonuç kendiliğinden güncellenmez; makroyu yeniden çalıştırmanız gerekir. Microsoft 365 kullanıyorsanız kendiliğinden güncellenen FİLTRE ve BENZERSİZ işlevleri bazı durumlarda daha pratik olabilir.
- İlgili konular:
- Range.AutoFilter: Başlıklardaki açılır okları kullanan basit süzme yöntemi.
- ShowAllData ve FilterMode: Uygulanmış süzmeyi kaldırma ve denetleme.
- Range.RemoveDuplicates: Yinelenen kayıtları listeden kalıcı olarak silme.
- Range.SpecialCells: Görünür hücreleri seçme (xlCellTypeVisible).
- FİLTRE ve BENZERSİZ: Kendiliğinden güncellenen dinamik dizi işlevleri.
- ORTALAMA: Hesaplanan ölçüt formüllerinde sık kullanılır.
