Gelişmiş filtre ve biraz sihir

Excel kullanıcılarının büyük çoğunluğu için, "veri filtreleme" kelimesi akıllarına geldiğinde, yalnızca sekmedeki olağan klasik filtre Veri – Filtre (Veri — Filtre):

Böyle bir filtre kuşkusuz tanıdık bir şeydir ve çoğu durumda işe yarayacaktır. Ancak, aynı anda birkaç sütunda çok sayıda karmaşık koşula göre filtrelemeniz gereken durumlar vardır. Buradaki olağan filtre çok uygun değil ve daha güçlü bir şey istiyorum. Böyle bir araç olabilir gelişmiş filtre, özellikle biraz “dosya ile bitirme” ile (geleneğe göre).

Temel

Başlamak için, veri tablonuzun üzerine birkaç boş satır ekleyin ve tablo başlığını buraya kopyalayın - bu, koşulları içeren bir aralık olacaktır (açıklık için sarı renkle vurgulanmıştır):

Sarı hücreler ile orijinal tablo arasında en az bir boş satır olmalıdır.

Daha sonra filtrelemenin gerçekleştirileceği kriterleri (koşulları) girmeniz gereken sarı hücrelerdedir. Örneğin, III çeyreğinde Moskova “Auchan” da muz seçmeniz gerekiyorsa, koşullar şöyle görünecektir:

Filtrelemek için, aralıktaki kaynak verileri içeren herhangi bir hücreyi seçin, sekmeyi açın Veri Ve tıklayın Ayrıca (Veri — Gelişmiş). Açılan pencerede, verileri içeren bir aralık zaten otomatik olarak girilmelidir ve yalnızca koşul aralığını belirtmemiz gerekecek, yani A1:I2:

Lütfen koşullar aralığının “marj ile” tahsis edilemeyeceğini, yani fazladan boş sarı çizgiler seçemeyeceğinizi unutmayın, çünkü koşullar aralığındaki boş bir hücre Excel tarafından bir kriterin yokluğu ve tamamı boş olarak algılanır. satırı, tüm verileri ayrım gözetmeksizin görüntülemek için bir istek olarak.

anahtar Sonucu başka bir konuma kopyalayın listeyi bu sayfada değil (normal bir filtrede olduğu gibi) filtrelemenize izin verir, ancak seçilen satırları başka bir aralığa boşaltmanıza izin verir, bu daha sonra alanda belirtilmesi gerekir Sonucu aralığa koy. Bu durumda bu fonksiyonu kullanmıyoruz, bırakıyoruz. Filtre listesi yerinde ve tıklayın OK. Seçilen satırlar sayfada görüntülenecektir:

Makro Ekleme

“Peki, burada kolaylık nerede?” sor ve haklı çıkacaksın. Sarı hücrelere sadece ellerinizle koşulları girmeniz değil, aynı zamanda bir iletişim kutusu açmanız, oraya aralıkları girmeniz, tuşuna basmanız yeterlidir. OK. Üzücü, katılıyorum! Ama “geldiklerinde her şey değişir ©” – makrolar!

Gelişmiş bir filtreyle çalışmak, koşullar girildiğinde, yani herhangi bir sarı hücreyi değiştirerek, gelişmiş filtreyi otomatik olarak çalıştıracak basit bir makro kullanılarak büyük ölçüde hızlandırılabilir ve basitleştirilebilir. Geçerli sayfanın sekmesine sağ tıklayın ve komutu seçin Kaynak metin (Kaynak kodu). Açılan pencerede aşağıdaki kodu kopyalayıp yapıştırın:

Private Sub Worksheet_Change(Aralık Olarak ByVal Hedef) Kesişmiyorsa(Target, Range("A2:I5")) O Zaman Hata Yok Sonraki ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Bu prosedür, geçerli çalışma sayfasındaki herhangi bir hücre değiştirildiğinde otomatik olarak çalışacaktır. Değiştirilen hücrenin adresi sarı aralığa (A2:I5) düşerse, bu makro tüm filtreleri (varsa) kaldırır ve genişletilmiş filtreyi A7'den başlayarak kaynak veri tablosuna yeniden uygular, yani her şey anında, hemen filtrelenir sonraki koşulu girdikten sonra:

Yani her şey çok daha iyi, değil mi? 🙂

Karmaşık sorguları uygulama

Artık her şey anında filtrelendiğine göre, nüansların biraz daha derinlerine inebilir ve gelişmiş filtrede daha karmaşık sorguların mekanizmalarını parçalarına ayırabiliriz. Tam eşleşmeleri girmenin yanı sıra, yaklaşık bir arama gerçekleştirmek için çeşitli joker karakterleri (* ve ?) ve matematiksel eşitsizlik işaretlerini çeşitli koşullarda kullanabilirsiniz. Karakter durumu önemli değil. Anlaşılır olması için olası tüm seçenekleri bir tabloda özetledim:

İnce noktalar:

• * işareti, herhangi bir sayıda karakter anlamına gelir ve ? - herhangi bir karakter.
• Metin ve sayısal sorguları işleme mantığı biraz farklıdır. Bu nedenle, örneğin, 5 numaralı bir koşul hücresi, beş ile başlayan tüm sayıları aramak anlamına gelmez, ancak B harfine sahip bir koşul hücresi, B*'ye eşittir, yani B harfi ile başlayan herhangi bir metni arayacaktır.
• Metin sorgusu = işaretiyle başlamıyorsa, zihinsel olarak sonuna * koyabilirsiniz.
• Tarihler ABD formatında ay-gün-yıl olarak ve kesirli olarak girilmelidir (Excel ve bölgesel ayarlarınız olsa bile).

Mantıksal bağlaçlar VE-VEYA

Farklı hücrelerde fakat aynı satırda yazılan koşulların mantıksal bir operatör tarafından birbirine bağlı olduğu kabul edilir. И (VE):

Şunlar. muzları benim için üçüncü çeyrekte, tam olarak Moskova'da ve aynı zamanda Auchan'dan filtreleyin.

Koşulları mantıksal bir işleçle bağlamanız gerekiyorsa OR (TD), o zaman sadece farklı satırlara girilmeleri gerekir. Örneğin, Moskova şeftali için müdür Volina'nın tüm siparişlerini ve Samara'da üçüncü çeyrekteki tüm soğan siparişlerini bulmamız gerekirse, bu aşağıdaki gibi bir dizi koşulda belirtilebilir:

Bir sütuna iki veya daha fazla koşul koymanız gerekiyorsa, sütun başlığını ölçüt aralığında çoğaltabilir ve altına ikinci, üçüncü vb. girebilirsiniz. terimler. Örneğin, Mart ile Mayıs arasındaki tüm işlemleri seçebilirsiniz:

Genel olarak, "bir dosyayla bitirdikten" sonra, gelişmiş bir filtre, bazı yerlerde klasik bir otomatik filtreden daha kötü olmayan, oldukça iyi bir araç olarak ortaya çıkıyor.

• Makrolarda süper filtre
• Makro nedir, Visual Basic'te makro kodu nereye ve nasıl eklenir
• Microsoft Excel'de akıllı tablolar