Arama Önerileri ile bir Excel Açılır Listesi Oluşturun

Hepimiz Google'ı günlük rutinimizin bir parçası olarak kullanıyoruz. Özelliklerinden biri, Google'ın akıllıca davrandığı ve yazarken bize bir öneri listesi verdiği arama önerisidir.

Bu öğreticide, Excel'de aranabilir bir açılır listenin nasıl oluşturulacağını, yani siz yazarken eşleşen öğeleri gösterecek bir açılır listenin nasıl oluşturulacağını öğreneceksiniz.

Aşağıda bu öğreticinin bir videosu bulunmaktadır (metni okumak yerine bir video izlemeyi tercih etmeniz durumunda).

Excel'de Aranabilir Açılır Liste

Bu eğitimin amacı için, GSYİH'ye göre İlk 20 ülkenin verilerini kullanıyorum.

Amaç, arama çubuğuna yazarken eşleşen seçeneklerle bir açılır liste gösterecek şekilde bir arama öneri mekanizmasına sahip bir excel açılır listesi oluşturmaktır.

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

Devam etmek için örnek dosyayı buradan indirin

Excel'de aranabilir açılır liste oluşturmak üç parçalı bir işlem olacaktır:

1. Arama kutusunu yapılandırma.
2. Verileri Ayarlama.
3. Çalışması için kısa bir VBA Kodu yazma.

Adım 1 - Arama Kutusunu Yapılandırma

Bu ilk adımda, bir birleşik giriş kutusu kullanacağım ve onu, siz yazdığınızda metnin gerçek zamanlı olarak bir hücreye yansıtılması için yapılandıracağım.

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

1. Geliştirici Sekmesi -> Ekle -> ActiveX Denetimleri -> Birleşik Kutu (ActiveX Denetimi) seçeneğine gidin.
   • Şeritte geliştirici sekmesini bulamama ihtimaliniz vardır. Varsayılan olarak gizlidir ve etkinleştirilmesi gerekir. Excel'deki şeritte geliştirici sekmesini nasıl alacağınızı öğrenmek için burayı tıklayın.
2. İmlecinizi çalışma sayfası alanına getirin ve herhangi bir yeri tıklayın. Bir birleşik giriş kutusu ekleyecektir.
3. Açılan Kutuya sağ tıklayın ve Özellikler'i seçin.
4. Özellikler iletişim kutusunda aşağıdaki değişiklikleri yapın:
   • OtomatikKelime Seçimi: Yanlış
   • BağlantılıHücre: B3
   • ListFillRange: DropDownList (2. adımda bu adla adlandırılmış bir aralık oluşturacağız)
   • MatchEntry: 2 - fmMatchEntryNone

(B3 Hücresi, Birleşik Giriş Kutusuna bağlıdır; bu, Birleşik Giriş Kutusuna yazdığınız her şeyin B3'e girileceği anlamına gelir.

4. Geliştirici sekmesine gidin ve Tasarım Modu'na tıklayın. Bu, Combo Box'a metin girmenizi sağlayacaktır. Ayrıca, B3 hücresi birleşik giriş kutusuna bağlı olduğundan, birleşik giriş kutusuna girdiğiniz herhangi bir metin de gerçek zamanlı olarak B3'e yansıtılacaktır.

Adım 2 - Verileri Ayarlama

Artık arama kutusu hazır olduğuna göre, verileri yerine yerleştirmemiz gerekiyor. Buradaki fikir, arama kutusuna herhangi bir şey yazdığınız anda, yalnızca içinde o metni içeren öğeleri göstermesidir.

Bunu yapmak için kullanacağız

• Üç yardımcı sütun.
• Bir dinamik adlandırılmış aralık.

Yardımcı Sütun 1

Aşağıdaki formülü F3 hücresine koyun ve tüm sütun için sürükleyin (F3:F22)

=--EKSAYI(EĞERHATA(ARAMA($B$3,E3,1),""))

Bu formül, Açılan Kutudaki metin soldaki ülke adında olduğunda 1 döndürür. Örneğin, UNI yazarsanız, yalnızca üniversiteAmerika Birleşik Devletleri ve üniversiteted Kingdom 1'dir ve kalan tüm değerler 0'dır.

Yardımcı Sütun 2

Aşağıdaki formülü Hücre G3'e koyun ve tüm sütun için sürükleyin (G3:G22)

=EĞER(F3=1,EĞERSAY($F$3:F3,1),"") 

Bu formül, Açılan Kutu metninin ülke adıyla eşleştiği ilk durum için 1, ikinci geçtiği için 2, üçüncü için 3 vb. döndürür. Örneğin, UNI yazarsanız, G3 hücresi Amerika Birleşik Devletleri ile eşleştiği için 1'i ve Birleşik Krallık ile eşleştiği için G9 2'yi görüntüler. Hücrelerin geri kalanı boş olacaktır.

Yardımcı Sütun 3

Aşağıdaki formülü H3 hücresine koyun ve tüm sütun için sürükleyin (H3:H22)

=EĞERHATA(INDEX($E$3:$E$22,MATCH(SATIR($G$3:G3),$G$3:$G$22,0)),"") 

Bu formül, eşleşen tüm adları aralarında boş hücre olmadan bir araya toplar. Örneğin, UNI yazarsanız, bu sütun 2 ve 9'u birlikte gösterir ve kalan tüm hücreler boş olur.

Dinamik Adlandırılmış Aralığı Oluşturma

Artık yardımcı sütunlar yerinde olduğuna göre, dinamik adlandırılmış aralığı oluşturmamız gerekiyor. Bu adlandırılmış aralık, yalnızca birleşik giriş kutusuna girilen metinle eşleşen değerlere başvurur. Açılır kutudaki değerleri göstermek için bu dinamik adlandırılmış aralığı kullanacağız.

Not: 1. adımda ListFillRange seçeneğine DropDownList girdik. Şimdi aynı isimle adlandırılmış aralığı oluşturacağız.

İşte onu oluşturma adımları:

1. Formüller -> Ad Yöneticisi'ne gidin.
2. Ad yöneticisi iletişim kutusunda Yeni'yi tıklayın. Bir Yeni Ad iletişim kutusu açacaktır.
3. Ad Alanına DropDownList girin
4. Başvuru Alanına şu formülü girin: =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Adım 3 - VBA Kodunu Çalıştırmak

Neredeyse geldik.

Son kısım ise kısa bir VBA kodu yazmaktır. Bu kod, arama kutusuna yazarken eşleşen öğeleri/adları gösterecek şekilde açılır menüyü dinamik hale getirir.

Bu kodu çalışma kitabınıza eklemek için:

1. Çalışma Sayfası sekmesine sağ tıklayın ve Kodu Görüntüle'yi seçin.
2. VBA penceresinde, aşağıdaki kodu kopyalayıp yapıştırın:

   Özel Alt ComboBox1_Change() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Alt Bitiş

Bu kadar!!

Siz yazarken eşleşen öğeleri gösteren kendi Google Arama çubuğunuzla hazırsınız.

Daha iyi bir görünüm ve his için B3 hücresini Combo Box ile kaplayabilir ve tüm yardımcı sütunları gizleyebilirsiniz. Artık bu harika Excel numarasıyla biraz gösteriş yapabilirsiniz.

Devam etmek için dosyayı buradan indirin

Ne düşünüyorsun? Bu arama önerisi açılır listesini işinizde kullanabilir misiniz? Yorum bırakarak düşüncelerinizi bana bildirin.

Bu öğreticiyi beğendiyseniz, aşağıdaki Excel eğitimlerini de beğeneceğinize eminim:

• Dinamik Filtre - Yazarken eşleşen verileri çıkarın.
• Açılır liste seçimine dayalı olarak Verileri Çıkarın.
• Excel'de Bağımlı Açılır Listeler Oluşturma.
• Excel DÜŞEYARA İşlevini Kullanmaya Yönelik En İyi Kılavuz.
• Excel'de bir açılır listede birden çok seçim nasıl yapılır.
• Excel'de Onay Kutusu Nasıl Eklenir ve Kullanılır.