Dinamik Excel Filtresi Arama Kutusu (Yazarken verileri ayıklayın)

Excel Filtresi, verilerle çalışırken en çok kullanılan işlevlerden biridir. Bu blog yazısında, verileri arama kutusuna yazdıklarınıza göre filtreleyecek şekilde Dinamik Excel Filtre Arama Kutusunun nasıl oluşturulacağını göstereceğim.

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

Bunun ikili bir işlevi vardır - açılır listeden bir ülkenin adını seçebilir veya verileri arama kutusuna manuel olarak girebilirsiniz ve size eşleşen tüm kayıtları gösterecektir. Örneğin, “I” yazdığınızda, size içinde I harfi olan tüm ülke adlarını verir.

Videoyu İzle - Dinamik Excel Filtresi Arama Kutusu Oluşturma

Dinamik Excel Filtresi Arama Kutusu Oluşturma

Bu Dinamik Excel filtresi 3 adımda oluşturulabilir:

1. Benzersiz bir öğe listesi alma (bu durumda ülkeler). Bu, açılır menüyü oluştururken kullanılacaktır.
2. Arama kutusunun oluşturulması. Burada bir Combo Box (ActiveX Control) kullandım.
3. Verileri Ayarlama. Burada eşleşen verileri çıkarmak için formüller içeren üç yardımcı sütun kullanırdım.

İşte ham verilerin nasıl göründüğü:

YARARLI İPUCU: Verilerinizi bir Excel Tablosuna dönüştürmek neredeyse her zaman iyi bir fikirdir. Bunu, veri kümesindeki herhangi bir hücreyi seçip Control + T klavye kısayolunu kullanarak yapabilirsiniz.

Adım 1 - Benzersiz bir öğe listesi alma

1. Tüm Ülkeleri seçin ve yeni bir çalışma sayfasına yapıştırın.
2. Ülke listesini seçin -> Verilere Git -> Yinelenenleri Kaldır.
3. Yinelenenleri Kaldır iletişim kutusunda, listenin bulunduğu sütunu seçin ve Tamam'ı tıklayın. Bu, kopyaları kaldıracak ve size aşağıda gösterildiği gibi benzersiz bir liste verecektir:
4. Ek bir adım, bu benzersiz liste için adlandırılmış bir aralık oluşturmaktır. Bunu yapmak için:
   • Formül Sekmesine Git -> Adı Tanımla
   • Adı Tanımla İletişim Kutusunda:
     • İsim: Ülke Listesi
     • Kapsam: Çalışma kitabı
     • Şu anlama gelir: =UniqueList!$A$2:$A$9 (Liste A2:A9'da UniqueList adında ayrı bir sekmede var. Benzersiz listenizin bulunduğu yere başvurabilirsiniz)

NOT: 'Yinelenenleri Kaldır' yöntemini kullanırsanız ve daha fazla kayıt ve yeni ülke eklemek için verilerinizi genişletirseniz, bu adımı tekrarlamanız gerekecektir. Alternatif olarak, bu süreci dinamik hale getirmek için bir formül de yapabilirsiniz.

Adım 2 - Dinamik Excel Filtresi Arama Kutusunu Oluşturma

Bu tekniğin çalışması için bir "Arama Kutusu" oluşturmamız ve onu bir hücreye bağlamamız gerekir.

Bu arama kutusu filtresini oluşturmak için Excel'deki Combo Box'ı kullanabiliriz. Bu şekilde, Combo Box'a herhangi bir şey girdiğinizde, gerçek zamanlı olarak bir hücreye de yansıtılacaktır (aşağıda gösterildiği gibi).

İşte bunu yapmak için adımlar:

1. Geliştirici Sekmesi -> Kontroller -> Ekle -> ActiveX Kontrolleri -> Birleşik Kutuya (ActiveX Kontrolleri) gidin.
   • Geliştirici Sekmesi görünmüyorsa, etkinleştirme adımları aşağıda verilmiştir.
2. Çalışma sayfasında herhangi bir yeri tıklayın. Combo Box'ı ekleyecektir.
3. Combo Box'a sağ tıklayın ve Özellikler'i seçin.
4. Özellikler penceresinde aşağıdaki değişiklikleri yapın:
   • Bağlantılı Hücre: K2 (giriş değerlerini göstermek istediğiniz herhangi bir hücreyi seçebilirsiniz. Verileri ayarlarken bu hücreyi kullanacağız).
   • ListFillRange: CountryList (bu, 1. Adımda oluşturduğumuz adlandırılmış aralıktır. Bu, açılır menüdeki tüm ülkeleri gösterecektir).
   • MatchEntry: 2-fmMatchEntryNone (bu, siz yazarken bir kelimenin otomatik olarak tamamlanmamasını sağlar)
5. Açılan Kutu seçiliyken, Geliştirici Sekmesine Git -> Kontroller -> Tasarım Modu'na tıklayın (bu sizi tasarım modundan çıkarır ve şimdi Açılan Kutuya her şeyi yazabilirsiniz. Şimdi, yazdığınız her şey K2 hücresine yansıtılacaktır. gerçek zamanda)

Adım 3 - Verileri Ayarlama

Son olarak, her şeyi yardımcı sütunlarla bağlarız. Verileri filtrelemek için burada üç yardımcı sütun kullanıyorum.

Yardımcı Sütun 1: Tüm kayıtlar için seri numarasını girin (bu durumda 20). Bunu yapmak için ROWS() formülünü kullanabilirsiniz.

Yardımcı Sütun 2: 2. yardımcı sütunda, arama kutusuna girilen metnin ülke sütunundaki hücrelerdeki metinle eşleşip eşleşmediğini kontrol ediyoruz.

Bu, IF, ISNUMBER ve SEARCH işlevlerinin bir kombinasyonu kullanılarak yapılabilir.

İşte formül:

=EĞER(ESAYI(ARA($K$2,D4)),E4,"")

Bu formül, ülke adının bulunduğu hücredeki (K2 hücresine bağlı olan) arama kutusundaki içeriği arayacaktır.

Bir eşleşme varsa, bu formül satır numarasını döndürür, aksi takdirde boş döndürür. Örneğin, Birleşik Giriş Kutusu 'ABD' değerine sahipse, ülkesi 'ABD' olan tüm kayıtların satır numarası olur ve geri kalanların tümü boş olur (“”)

Yardımcı Sütun 3: Yardımcı sütun 3'te, birlikte yığılmış olan Yardımcı Sütun 2'deki tüm satır numaralarını almamız gerekiyor. Bunu yapmak için EĞERHATA ve KÜÇÜK formülleri bir arada kullanabiliriz. İşte formül:

=EĞERHATA(KÜÇÜK($F$4:$F$23,E4),"")

Bu formül, eşleşen tüm satır numaralarını bir araya toplar. Örneğin, Birleşik Giriş Kutusu ABD değerine sahipse, içinde 'ABD' olan tüm satır numaraları birlikte yığılır.

Şimdi sıra numaralarını bir araya getirdiğimizde, sadece bu satır numaralarındaki verileri çıkarmamız gerekiyor. Bu, dizin formülü kullanılarak kolayca yapılabilir (bu formülü, verileri çıkarmak istediğiniz yere ekleyin. Verinin çıkarılmasını istediğiniz sol üst hücreye kopyalayın ve ardından aşağı ve sağa sürükleyin).

=EĞERHATA(INDEX($B$4:$D$23,$G4,SÜTUNLAR($I$3:I3)),"")

Bu formülün 2 kısmı vardır:
İNDEKS - Bu, verileri satır numarasına göre ayıklar.
EĞERHATA - Bu, veri olmadığında boş döner.

İşte sonunda elde ettiğiniz şeyin bir görüntüsü:

Combo Box, bir arama kutusunun yanı sıra bir açılır menüdür. Yalnızca filtrelenmiş kayıtları göstermek için orijinal verileri ve yardımcı sütunları gizleyebilirsiniz. Ayrıca başka bir sayfada ham veri ve yardımcı sütunlara sahip olabilir ve bu dinamik excel filtresini başka bir çalışma sayfasında oluşturabilirsiniz.

Yaratıcı ol! Bazı Varyasyonları Deneyin

Deneyebilir ve gereksinimlerinize göre özelleştirebilirsiniz. Bir yerine birden çok excel filtresi oluşturmak isteyebilirsiniz. Örneğin, Satış Temsilcisinin Mike ve Ülkenin Japonya olduğu kayıtları filtrelemek isteyebilirsiniz. Bu, yardımcı sütunlardaki formülde bazı değişikliklerle aynı adımları izleyerek yapılabilir.

Başka bir varyasyon, birleşik giriş kutusuna girdiğiniz karakterlerle başlayan verileri filtrelemek olabilir. Örneğin, 'I' girdiğinizde, I ile başlayan ülkeleri çıkarmak isteyebilirsiniz (şu anki yapıyla karşılaştırıldığında, I alfabesini içerdiği için size Singapur ve Filipinler'i de verir).

Her zaman olduğu gibi, makalelerimin çoğu okuyucularımın sorularından/yanıtlarından ilham alıyor. Geri bildiriminizi almayı ve sizden öğrenmeyi çok isterim. Düşüncelerinizi yorumlar bölümünde bırakın.

Not: Office 365 kullanıyorsanız, yazarken verileri hızla filtrelemek için FİLTRE işlevini kullanabilirsiniz. Bu eğitimde gösterilen yöntemden daha kolaydır.