Excel'de aranabilir açılır liste için nihai kılavuz

Excel'de açılır listeler oluşturmak, veri girişini kolaylaştırır ve hataları en aza indirir. Ancak daha büyük veri setleriyle çalışırken, uzun listelerde kaydırma yapmak zahmetli hale gelir. Yazmaya başlayıp aradığınız öğeyi hızlıca bulmak daha kolay olmaz mıydı? "Aranabilir açılır liste" tam da bu rahatlığı sunar. Bu kılavuz, Excel'de böyle bir liste oluşturmak için dört yöntemi adım adım anlatacak.

• Excel 365'te aranabilir açılır liste
• Aranabilir açılır liste oluştur (Excel 2019 ve sonrası için)
• Aranabilir açılır liste kolayca oluştur (tüm Excel sürümleri için)
• Combo kutusu ve VBA ile aranabilir açılır liste oluştur (daha karmaşık)

Video: Aranabilir açılır liste oluştur

Excel 365'te aranabilir açılır liste

Excel 365, veri doğrulama açılır listelerine çok beklenen bir özellik ekledi: listede arama yapabilme yeteneği. Aranabilir işlevsellik sayesinde kullanıcılar, öğeleri daha hızlı bir şekilde bulabilir ve seçebilirler. Normalde olduğu gibi açılır listeyi yerleştirdikten sonra, sadece açılır liste içeren bir hücreye tıklayın ve yazmaya başlayın. Liste, yazılan metne uygun şekilde anında filtrelenir.

Bu durumda, hücreye San yazdığımda açılır liste, San Francisco ve San Diego gibi San ile başlayan şehirleri filtreler. Ardından fareyle bir sonuç seçebilir veya yön tuşlarını kullanarak Enter'a basabilirsiniz.

Aranabilir açılır liste oluştur (Excel 2019 ve sonrası için)

Excel 2019 veya daha yeni sürümlerini kullanıyorsanız, bu bölümdeki yöntem de Excel'de bir açılır listeyi aranabilir hale getirmek için kullanılabilir.

Sheet1'deki A2:A8 aralığındaki verileri kullanarak Sheet2'nin A2 hücresinde bir açılır liste oluşturduğunuzu varsayalım (sağdaki resim), aşağıdaki adımları izleyerek bu listeyi aranabilir hale getirebilirsiniz.

Adım 1. Arama öğelerini listeleyen bir yardımcı sütun oluşturun

Burada, kaynak verilerle eşleşen öğeleri listeleyen bir yardımcı sütuna ihtiyacımız var. Bu durumda, yardımcı sütunu Sheet1'in D sütununda oluşturacağım.

1. D sütunundaki ilk hücreyi (D1) seçin ve bu durumda "Arama sonuçları" gibi bir sütun başlığı girin.
2. Aşağıdaki formülü D2 hücresine girin ve Enter.

   =FILTER(A2:A8;ISNUMBER(SEARCH(Sheet2!A2;A2:A8));"Bulunamadı")

   

Adım 2: Açılır listeyi yeniden yapılandırın

1. Açılır liste hücresini seçin (bu durumda Sheet2'nin A2 hücresini seçiyorum), ardından Veri > Veri Doğrulama > Veri Doğrulama'ya gidin.
   
2. İçinde Veri Doğrulama diyalog kutusunda, şu şekilde yapılandırmanız gerekiyor.
   1. Altında Seçenekler sekmesinde, düğmesine tıklayın Kaynak kutusunda.
      
   2. Veri Doğrulama diyalog kutusu, Adım 1'deki formülü içeren hücreye (örneğin, D2) yönlendirir, bir # sembolü ekleyin ve Kapat düğmesine tıklayın.
      
   3. Hata Uyarısı sekmesine gidin, Geçersiz veri girildikten sonra hata uyarısı göster onay kutusunun işaretini kaldırın ve son olarak değişiklikleri kaydetmek için Tamam düğmesine tıklayın.
      

Sonuç

Şimdi Sheet2'nin A2 hücresindeki açılır liste aranabilir hale geldi. Hücreye metin yazın, açılır okuna tıklayarak açılır listeyi genişletin ve yazılan metne uygun şekilde anında filtrelenecek olan listeyi göreceksiniz.

Aranabilir açılır liste kolayca oluştur (tüm Excel sürümleri için)

Yukarıdaki yöntemlerin çeşitli sınırlamaları göz önüne alındığında, işte size çok etkili bir araç - Kutools for Excel'in Açılır Listeyi Aranabilir Yap, Otomatik-açılır özelliği. Bu özellik tüm Excel sürümlerinde kullanılabilir ve size basit bir kurulumla açılır listede istediğiniz öğeyi kolayca aramanızı sağlar.

Kutools for Excel'i indirip yükledikten sonra, Kutools > Açılır Liste > Açılır Listeyi Aranabilir Yap, Otomatik-açılır seçeneğini etkinleştirmek için bu özelliği seçin. Açılır Listeyi Aranabilir Yap diyalog kutusunda şunları yapmanız gerekiyor:

1. Aranabilir açılır listeler olarak ayarlanması gereken açılır listeleri içeren aralığı seçin.
2. Tıklayın Tamam ayarları tamamlamak için.
   

Sonuç

Belirtilen aralıktaki bir açılır liste hücresine tıkladığınızda, sağda bir liste kutusu belirir. Metin yazarak listeyi anında filtreleyebilir, ardından bir öğe seçebilir veya yön tuşlarını kullanarak Enter'a basarak hücreye ekleyebilirsiniz.

Combo kutusu ve VBA ile aranabilir açılır liste oluştur (daha karmaşık)

Eğer belirli bir açılır liste türü belirtmeden sadece aranabilir bir açılır liste oluşturmak istiyorsanız, bu bölüm size alternatif bir yaklaşım sunar: görevi gerçekleştirmek için Combo kutusu ve VBA kodu kullanmak.

Aşağıdaki ekran görüntüsünde gösterildiği gibi A sütununda bir ülke adı listesi olduğunu ve şimdi bunları aranabilir açılır listelerin kaynak verisi olarak kullanmak istediğinizi varsayalım, bunu şu şekilde yapabilirsiniz.

Çalışma sayfanızda bir veri doğrulama açılır listesi yerine bir Combo kutusu eklemeniz gerekir.

1. Eğer Geliştirici sekmesi şeritte görünmüyorsa, şu şekilde etkinleştirebilirsiniz: Geliştirici sekmesini etkinleştirebilirsiniz.
   1. Excel 2010 veya sonraki sürümlerde, Dosya > Seçenekler'e tıklayın. Excel Seçenekleri diyalog kutusunda, sol bölmedeki Özelleştir Ribbon'u tıklayın. Ribbon'u Özelleştir listesine gidin, Geliştirici kutusunu işaretleyin ve ardından Tamam düğmesine tıklayın. Ekran görüntüsüne bakın:
      
   2. Excel 2007'de, Office düğmesine > Excel Seçenekleri'ne tıklayın. Excel Seçenekleri diyalog kutusunda, sol bölmedeki Popüler'i tıklayın, Şeritte Geliştirici sekmesini göster kutusunu işaretleyin ve son olarak Tamam düğmesine tıklayın.
      
2. Geliştirici sekmesi göründükten sonra Geliştirici > Ekle > Combo kutusu'na tıklayın.
   
3. Çalışma sayfasında bir Combo kutusu çizip sağ tıklayın ve ardından sağ tıklama menüsünden Özellikler'i seçin.
   
4. İçinde Özellikler diyalog kutusunda, şunları yapmanız gerekiyor:
   1. AutoWordSelect alanında False seçin;
   2. LinkedCell alanında bir hücre belirtin. Bu durumda, A12 giriyoruz;
   3. MatchEntry alanında 2-fmMatchEntryNone seçin;
   4. ListFillRange alanına DropDownList yazın;
   5. Özellikler diyalog kutusunu kapatın. Ekran görüntüsüne bakın:
      
5. Şimdi Geliştirici > Tasarım Modu'na tıklayarak tasarım modunu kapatın.
6. C2 gibi boş bir hücre seçin, aşağıdaki formülü girin ve Entertuşuna basın. Ardından, aynı formülü içeren hücreleri otomatik doldurmak için C9 hücresine kadar AutoFill tutamacını sürükleyin. Ekran görüntüsüne bakın:

   =--ISNUMBER(IFERROR(SEARCH($A$12;A2;1);""))

   
   Notlar:

   1. $A$12, 4. adımda LinkedCell olarak belirttiğiniz hücredir;
   2. Yukarıdaki adımları tamamladıktan sonra, artık test edebilirsiniz: combo kutusuna bir C harfi girin ve ardından C karakterini içeren hücrelere referans veren formül hücrelerinin 1 sayısıyla doldurulduğunu görebilirsiniz.
7. D2 hücresini seçin, aşağıdaki formülü girin ve Entertuşuna basın. Ardından, AutoFill tutamacını D9 hücresine kadar sürükleyin.

   =IF(C2=1;COUNTIF($C$2:C2;1);"")

   
8. E2 hücresini seçin, aşağıdaki formülü girin ve Entertuşuna basın. Ardından, aynı formülü uygulamak için AutoFill tutamacını E9 hücresine kadar sürükleyin.

   =IFERROR(INDEX($A$2:$A$9;MATCH(ROWS($D$2:D2);$D$2:$D$9;0));"")

   
9. Şimdi bir isim aralığı oluşturmanız gerekiyor. Lütfen Formüller > Ad Tanımla'ya tıklayın.
   
10. İçinde Yeni Ad diyalog kutusunda, DropDownList yazın Ad kutusuna, aşağıdaki formülü Şuna Başvurur kutusuna girin ve ardından Tamam düğmesine tıklayın.

    =$E$2:INDEX($E$2:$E$9;MAX($D$2:$D$9);1)
    

    
11. Şimdi, Geliştirici > Tasarım Modu'na tıklayarak tasarım modunu açın. Ardından, Microsoft Visual Basic for Applications penceresini açmak için Combo kutusuna çift tıklayın.
12. Aşağıdaki VBA kodunu Kopyala ve Yapıştır butonuna tıklayarak Kod düzenleyicisine yapıştırın.
    
    VBA kodu: açılır listeyi aranabilir yap

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Microsoft Visual Basic for Applications penceresini kapatmak için Alt + Q tuşlarına basın.

Artık, combo kutusuna bir karakter girildiğinde, bulanık bir arama yapacak ve ilgili değerleri listede gösterecektir.

İlgili makaleler: