Excel VBA Otomatik Filtresi: Örneklerle Tam Bir Kılavuz

VBA'da kullanılabilecek birçok Excel işlevi de mevcuttur - ve Otomatik filtre yöntemi böyle bir işlevdir.

Bir veri kümeniz varsa ve onu bir ölçüt kullanarak filtrelemek istiyorsanız, Veri şeridindeki Filtre seçeneğini kullanarak bunu kolayca yapabilirsiniz.

Daha gelişmiş bir sürümünü istiyorsanız, Excel'de de gelişmiş bir filtre var.

O zaman Neden Otomatik Filtreyi VBA'da Kullanıyorsunuz?

Yalnızca verileri filtrelemeniz ve bazı temel şeyler yapmanız gerekiyorsa, Excel arayüzünün sunduğu dahili Filtre işlevine bağlı kalmanızı tavsiye ederim.

Otomasyonunuzun bir parçası olarak verileri filtrelemek istediğinizde (veya verileri daha hızlı filtrelemenizi sağlayarak zamandan tasarruf etmenize yardımcı oluyorsa) VBA Otomatik Filtreyi kullanmalısınız.

Örneğin, açılır bir seçime göre verileri hızlı bir şekilde filtrelemek ve ardından bu filtrelenmiş verileri yeni bir çalışma sayfasına kopyalamak istediğinizi varsayalım.

Bu, bazı kopyala-yapıştır ile birlikte dahili filtre işlevi kullanılarak yapılabilir, ancak bunu manuel olarak yapmanız çok zaman alabilir.

Böyle bir senaryoda, VBA Autofilter kullanmak işleri hızlandırabilir ve zamandan tasarruf sağlayabilir.

Not: Bu örneği (açılır bir seçime dayalı olarak verileri filtreleme ve yeni bir sayfaya kopyalama üzerine) bu öğreticide daha sonra ele alacağım.

Excel VBA Otomatik Filtre Sözdizimi

İfade. Otomatik Filtre( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• İfade: Bu, otomatik filtreyi uygulamak istediğiniz aralıktır.
• Tarla: [İsteğe bağlı argüman] Bu, filtrelemek istediğiniz sütun numarasıdır. Bu, veri kümesinde soldan sayılır. Dolayısıyla, verileri ikinci sütuna göre filtrelemek istiyorsanız, bu değer 2 olacaktır.
• Kriter1: [İsteğe bağlı argüman] Bu, veri kümesini filtrelemek istediğiniz kriterdir.
• Şebeke: [İsteğe bağlı argüman] Kriter 2'yi de kullanıyorsanız, Operatör bazında bu iki kriteri birleştirebilirsiniz. Aşağıdaki operatörler kullanılabilir: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Kriter2: [İsteğe bağlı argüman] Bu, veri kümesini filtreleyebileceğiniz ikinci kriterdir.
• VisibleDropDown: [İsteğe bağlı argüman] Filtre açılır simgesinin filtrelenmiş sütunlarda görünmesini isteyip istemediğinizi belirtebilirsiniz. Bu argüman DOĞRU veya YANLIŞ olabilir.

İfade dışında, diğer tüm argümanlar isteğe bağlıdır.

Herhangi bir argüman kullanmamanız durumunda, filtre simgelerini sütunlara uygular veya kaldırır.

Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub

Yukarıdaki kod, yalnızca Otomatik Filtre yöntemini sütunlara uygular (veya zaten uygulanmışsa kaldıracaktır).

Bu basitçe, eğer sütun başlıklarında filtre ikonlarını göremiyorsanız, yukarıdaki kod çalıştırıldığında onu görmeye başlayacağınız ve eğer görebiliyorsanız, o zaman kaldırılacağı anlamına gelir.

Filtrelenmiş verileriniz varsa, filtreleri kaldıracak ve size tam veri kümesini gösterecektir.

Şimdi, kullanımını netleştirecek Excel VBA Otomatik Filtre kullanmanın bazı örneklerini görelim.

Örnek: Bir Metin koşuluna dayalı olarak Verileri Filtreleme

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve bunu "Öğe" sütununa göre filtrelemek istediğinizi varsayalım.

Aşağıdaki kod, öğenin 'Yazıcı' olduğu tüm satırları filtreleyecektir.

Sub FilterRows() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer" End Sub

Yukarıdaki kod Sayfa1'e atıfta bulunur ve içindeki A1'e (veri kümesindeki bir hücredir) atıfta bulunur.

Öğe sütunu, veri kümemizdeki soldan ikinci sütun olduğundan, burada Field:=2'yi kullandığımızı unutmayın.

Şimdi düşünüyorsanız - bunu neden bir VBA kodu kullanarak yapmam gerekiyor? Bu, dahili filtre işlevi kullanılarak kolayca yapılabilir.

Haklısın!

Tüm yapmak istediğiniz buysa, dahili Filtre işlevini daha iyi kullanın.

Ancak kalan öğreticiyi okurken, bunun güçlü otomasyon oluşturmak için bazı ekstra kodlarla birleştirilebileceğini göreceksiniz.

Ancak size bunları göstermeden önce, tüm AutoFilter yönteminin neler yapabileceğini size göstermek için birkaç örnek vereyim.

Buraya Tıkla örnek dosyayı indirmek ve takip etmek için.

Örnek: Aynı Sütunda Birden Çok Ölçüt (VE/VEYA)

Aynı veri setine sahip olduğumu ve bu sefer öğenin 'Yazıcı' veya 'Projektör' olduğu tüm kayıtları filtrelemek istediğimi varsayalım.

Aşağıdaki kod bunu yapardı:

Sub FilterRowsOR() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector" End Sub

Burada kullandığımı unutmayın xlOR Şebeke.

Bu, VBA'ya hem ölçütleri kullanmasını hem de iki ölçütten herhangi biri karşılanırsa verileri filtrelemesini söyler.

Benzer şekilde AND kriterlerini de kullanabilirsiniz.

Örneğin, miktarın 10'dan büyük, 20'den küçük olduğu tüm kayıtları filtrelemek istiyorsanız, aşağıdaki kodu kullanabilirsiniz:

Sub FilterRowsAND() Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20" End Sub

Örnek: Farklı Sütunlarla Birden Çok Kriter

Aşağıdaki veri setine sahip olduğunuzu varsayalım.

Otomatik Filtre ile aynı anda birden çok sütunu filtreleyebilirsiniz.

Örneğin, öğenin 'Yazıcı' ve Satış Temsilcisinin 'İşaret' olduğu tüm kayıtları filtrelemek istiyorsanız aşağıdaki kodu kullanabilirsiniz:

Sub FilterRows() with Worksheets("Sheet1").Range("A1") .AutoFilter alanı:=2, Criteria1:="Printer" .AutoFilter field:=3, Criteria1:="Mark" End with End Sub ile

Örnek: Otomatik Filtreleme Yöntemini Kullanarak İlk 10 Kaydı Filtreleme

Aşağıdaki veri setine sahip olduğunuzu varsayalım.

Aşağıda (miktar sütununa göre) size ilk 10 kaydı verecek kod verilmiştir:

Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items End Sub

Yukarıdaki kodda ActiveSheet kullandım. İsterseniz sayfa adını kullanabilirsiniz.

Bu örnekte, ilk 5 öğeyi almak istiyorsanız, sadece içindeki sayıyı değiştirin. Kriter1:=”10″ 10'dan 5'e.

Yani ilk 5 öğe için kod şöyle olacaktır:

Sub FilterRowsTop5() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10Items End Sub

Garip görünebilir, ancak kaç tane en iyi öğeyi isterseniz isteyin, Operatör değeri her zaman aynı kalır. xlTop10Items.

Benzer şekilde, aşağıdaki kod size en alttaki 10 öğeyi verecektir:

Sub FilterRowsBottom10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10Items End Sub

Ve en alttaki 5 öğeyi istiyorsanız, içindeki sayıyı değiştirin. Kriter1:=”10″ 10'dan 5'e.

Örnek: Otomatik Filtreleme Yöntemini Kullanarak İlk Yüzde 10'u Filtreleyin

Aynı veri setine sahip olduğunuzu varsayalım (önceki örneklerde kullanıldığı gibi).

Aşağıda, size ilk yüzde 10'luk kayıtları verecek olan kod verilmiştir (miktar sütununa göre):

Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Percent End Sub

Veri setimizde 20 kaydımız olduğu için ilk 2 kaydı (toplam kayıtların %10'u) döndürecektir.

Örnek: Otomatik Filtrede Joker Karakterleri Kullanma

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım:

Öğe adının 'Board' kelimesini içerdiği tüm satırları filtrelemek istiyorsanız, aşağıdaki kodu kullanabilirsiniz:

Sub FilterRowsWildcard() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*" End Sub

Yukarıdaki kodda, * (yıldız) joker karakterini (kriter olan) 'Board' kelimesinden önce ve sonra kullandım.

Yıldız işareti herhangi bir sayıda karakteri temsil edebilir. Bu, içinde 'tahta' kelimesi bulunan herhangi bir öğeyi filtreleyecektir.

Örnek: Filtrelenmiş Satırları Yeni Bir Sayfaya Kopyalayın

Kayıtları sadece kriterlere göre filtrelemek değil, filtrelenmiş satırları da kopyalamak istiyorsanız aşağıdaki makroyu kullanabilirsiniz.

Filtrelenen satırları kopyalar, yeni bir çalışma sayfası ekler ve ardından bu kopyalanan satırları yeni sayfaya yapıştırır.

Sub CopyFilteredRows() Dim rng As Range Dim ws As Worksheets("Sayfa1").AutoFilterMode = False ise MsgBox "Filtrelenmiş satır yok" Exit Sub End If Set rng = Worksheets("Sayfa1").AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range("A1") End Sub

Yukarıdaki kod, Sayfa1'de filtrelenmiş satır olup olmadığını kontrol eder.

Filtrelenmiş satır yoksa, bunu belirten bir mesaj kutusu gösterecektir.

Filtrelenmiş satırlar varsa, bunları kopyalar, yeni bir çalışma sayfası ekler ve bu satırları yeni eklenen çalışma sayfasına yapıştırır.

Örnek: Hücre Değerine Göre Verileri Filtreleme

Bir açılır listeyle birlikte VBA'da Otomatik Filtreyi kullanarak, açılır listeden bir öğe seçtiğiniz anda o öğe için tüm kayıtların filtrelendiği bir işlevsellik oluşturabilirsiniz.

Aşağıda gösterildiği gibi bir şey:

Buraya Tıkla örnek dosyayı indirmek ve takip etmek için.

Bu tür bir yapı, verileri hızlı bir şekilde filtrelemek ve ardından işinizde daha fazla kullanmak istediğinizde faydalı olabilir.

Bunu yapacak kod aşağıdadır:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field :=2, Criteria1:=Range("B2") End If End If End Sub

Bu, yalnızca çalışma sayfasında bir değişiklik olduğunda ve hedef hücre B2 olduğunda (açılır listenin bulunduğu yer) çalıştırılan bir çalışma sayfası olay kodudur.

Ayrıca, kullanıcının açılır menüden 'Tümü'nü seçip seçmediğini kontrol etmek için If Then Else koşulu kullanılır. Tümü seçilirse, tüm veri kümesi gösterilir.

Bu kod bir modüle YERLEŞTİRİLMEMİŞTİR.

Bunun yerine, bu verileri içeren çalışma sayfasının arka ucuna yerleştirilmesi gerekir.

Bu kodu çalışma sayfası kod penceresine yerleştirme adımları şunlardır:

1. VB Düzenleyiciyi açın (klavye kısayolu - ALT + F11).
2. Proje Gezgini bölmesinde, bu filtreleme işlevini istediğiniz Çalışma Sayfası adına çift tıklayın.
3. Çalışma sayfası kodu penceresinde yukarıdaki kodu kopyalayıp yapıştırın.
4. VB Düzenleyiciyi kapatın.

Şimdi açılır listeyi kullandığınızda, verileri otomatik olarak filtreleyecektir.

Bu, yalnızca çalışma sayfasında bir değişiklik olduğunda ve hedef hücre B2 olduğunda (açılır listenin bulunduğu yer) çalıştırılan bir çalışma sayfası olay kodudur.

Ayrıca, kullanıcının açılır menüden 'Tümü'nü seçip seçmediğini kontrol etmek için If Then Else koşulu kullanılır. Tümü seçilirse, tüm veri kümesi gösterilir.

VBA kullanarak Excel Otomatik Filtresini AÇMA/KAPATMA

Bir hücre aralığına Otomatik Filtre uygularken, bazı filtreler zaten yerinde olabilir.

Önceden uygulanmış otomatik filtreleri kapatmak için aşağıdaki kodu kullanabilirsiniz:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = False End Sub

Bu kod, tüm sayfaları kontrol eder ve uygulanan filtreleri kaldırır.

Tüm sayfadan değil, yalnızca belirli bir veri kümesinden filtreleri kapatmak istiyorsanız, aşağıdaki kodu kullanın:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End If End Sub

Yukarıdaki kod, halihazırda filtrelerin olup olmadığını kontrol eder.

Filtreler zaten uygulanmışsa, onu kaldırır, aksi takdirde hiçbir şey yapmaz.

Benzer şekilde, AutoFilter'ı açmak istiyorsanız aşağıdaki kodu kullanın:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End If End If Sub

Otomatik Filtrenin Zaten Uygulanıp Uygulanmadığını Kontrol Edin

Birden fazla veri kümesine sahip bir sayfanız varsa ve halihazırda filtre bulunmadığından emin olmak istiyorsanız, aşağıdaki kodu kullanabilirsiniz.

Sub CheckforFilters() ActiveSheet.AutoFilterMode = True ise MsgBox "Zaten yerinde filtreler var" Else MsgBox "Filtre yok" End If End Sub

Bu kod, sayfada filtreler bulduğunda 'Zaten yerinde Filtreler var' mesajını görüntüleyen bir mesaj kutusu işlevi kullanır, aksi takdirde 'Filtre yok' ifadesini gösterir.

Tüm Verileri Göster

Veri kümesine uygulanmış filtreleriniz varsa ve tüm verileri göstermek istiyorsanız aşağıdaki kodu kullanın:

Sub ShowAllData() If ActiveSheet.FilterMode Sonra ActiveSheet.ShowAllData End Sub

Yukarıdaki kod, FilterMode'un DOĞRU veya YANLIŞ olup olmadığını kontrol eder.

Doğruysa, bir filtre uygulanmış demektir ve tüm verileri göstermek için ShowAllData yöntemini kullanır.

Bunun filtreleri kaldırmadığını unutmayın. Filtre simgeleri hala kullanılabilir durumda.

Korumalı Sayfalarda Otomatik Filtre Kullanma

Varsayılan olarak, bir sayfayı koruduğunuzda filtreler çalışmaz.

Halihazırda filtreleriniz varsa, korumalı sayfalarda bile çalıştığından emin olmak için Otomatik Filtre'yi etkinleştirebilirsiniz.

Bunu yapmak için, sayfayı korurken Otomatik Filtre Kullan seçeneğini işaretleyin.

Bu, filtreleriniz zaten yerindeyken çalışır, ancak bir VBA kodu kullanarak Otomatik Filtreler eklemeye çalışmanız durumunda çalışmaz.

Sayfa korumalı olduğundan, herhangi bir makronun çalışmasına ve Otomatik Filtrede değişiklik yapmasına izin vermez.

Bu nedenle, çalışma sayfasını korumak için bir kod kullanmanız ve içinde otomatik filtrelerin etkinleştirildiğinden emin olmanız gerekir.

Bu, dinamik bir filtre oluşturduğunuzda faydalı olabilir (örnekte ele aldığım bir şey - 'Hücre Değerine dayalı Verileri Filtrele').

Aşağıda, sayfayı koruyacak, ancak aynı zamanda, içindeki VBA makrolarının yanı sıra Filtreleri kullanmanıza izin verecek kod bulunmaktadır.

Private Sub Workbook_Open() with Worksheets("Sheet1") .EnableAutoFilter = True .Protect Password:="password", Contents:=True, UserInterfaceOnly:=True End with End Sub

Bu kodun ThisWorkbook kod penceresine yerleştirilmesi gerekiyor.

Kodu ThisWorkbook kod penceresine yerleştirme adımları şunlardır:

1. VB Düzenleyiciyi açın (klavye kısayolu - ALT + F11).
2. Proje Gezgini bölmesinde, ThisWorkbook nesnesine çift tıklayın.
3. Açılan kod penceresinde yukarıdaki kodu kopyalayıp yapıştırın.

Çalışma kitabını açıp makroları etkinleştirir etkinleştirmez, makroyu otomatik olarak çalıştıracak ve Sayfa1'i koruyacaktır.

Ancak bunu yapmadan önce, 'EnableAutoFilter = True' belirleyecektir, bu da filtrelerin korumalı sayfada da çalışacağı anlamına gelir.

Ayrıca, "UserInterfaceOnly" argümanını "True" olarak ayarlar. Bu, çalışma sayfası korunurken VBA makroları kodunun çalışmaya devam edeceği anlamına gelir.

Aşağıdaki VBA Eğitimlerini de Beğenebilirsiniz:

• Excel VBA Döngüleri.
• Kalın Yazı Tipi Biçimlendirmesiyle Hücreleri Filtreleyin.
• Makro Kaydetme.
• VBA Kullanarak Verileri Sıralayın.
• Excel'de Çalışma Sayfası Sekmelerini Sıralayın.