Ana içeriğe atla

Excel'de aranabilir açılır listeye ilişkin 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 kümelerinde uzun listeler arasında gezinmek zahmetli hale gelir. Öğenizi yalnızca yazıp hızlı bir şekilde bulmak daha kolay olmaz mıydı? A "aranabilir açılır liste" bu kolaylığı sunuyor. Bu kılavuz, Excel'de böyle bir liste oluşturmak için dört yöntem boyunca size yol gösterecektir.


Video


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

Excel 365, veri doğrulama açılır listelerine çok beklenen bir özelliği ekledi: liste içinde arama yapma yeteneği. Aranabilir işlevsellik sayesinde kullanıcılar, öğeleri daha verimli bir şekilde hızlı bir şekilde bulabilir ve seçebilir. Açılır listeyi her zamanki gibi ekledikten sonra, açılır listenin bulunduğu hücreye tıklayın ve yazmaya başlayın. Liste, yazılan metinle eşleşecek şekilde anında filtrelenecektir.

Bu durumda yazıyorum San hücrede ve açılır listede, arama terimiyle başlayan şehirler filtrelenir SanGibi San Francisco ve San Diego. Daha sonra farenizle bir sonuç seçebilir veya ok tuşlarını kullanıp Enter tuşuna basabilirsiniz.

notlar:
  • The Arama her kelimenin ilk harfinden başlatılır açılır listede. Herhangi bir kelimenin başlangıç ​​karakteriyle eşleşmeyen bir karakter girerseniz eşleşen öğeler listede görüntülenmez.
  • Bu özellik yalnızca Excel 365'in en son sürümünde mevcuttur.
  • Excel sürümünüz bu özelliği desteklemiyorsa burada şunları öneririz: Aranabilir Açılır Liste özelliği Kutools for Excel. Excel sürüm sınırlaması yoktur ve etkinleştirildikten sonra, yalnızca ilgili metni yazarak açılır listede istediğiniz öğeyi kolayca arayabilirsiniz. Ayrıntılı adımları görüntüleyin.

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

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

Sayfa2'nin A2 hücresinde (sağdaki resim) Sayfa2'in (soldaki resim) A8:A1 aralığındaki verileri kullanarak bir açılır liste oluşturduğunuzu varsayalım, listeyi aranabilir hale getirmek için şu adımları izleyin.

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

Burada kaynak verilerinizle eşleşen öğeleri listelemek için bir yardımcı sütuna ihtiyacımız var. Bu durumda yardımcı sütunu oluşturacağım. sütun D of Sheet1.

  1. İlk hücreyi seç D1 D sütununa girin ve " gibi sütun başlığını girinArama Sonuçları" bu durumda.
  2. Aşağıdaki formülü D2 hücresine girin ve tuşuna basın. Keşfet.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
notlar:
  • Bu formülde, A2: A8 kaynak veri aralığıdır. Sayfa2!A2 açılır listenin konumudur; bu, açılır listenin Sayfa2'nin A2'sinde bulunduğu anlamına gelir. Lütfen bunları kendi verilerinize göre değiştirin.
  • Sayfa2'nin A2 kısmındaki açılır listeden hiçbir öğe seçilmezse formül, yukarıdaki resimde gösterildiği gibi kaynak verilerdeki tüm öğeleri görüntüleyecektir. Tersine, eğer bir öğe seçilirse, D2 bu öğeyi formülün sonucu olarak görüntüler.
2. Adım: Açılır listeyi yeniden yapılandırın
  1. Açılır liste hücresini seçin (bu durumda Sayfa2'nin A2 hücresini seçiyorum), ardından seç'e gidin Veri > veri Doğrulama > veri Doğrulama.
  2. içinde veri Doğrulama iletişim kutusunda aşağıdaki gibi yapılandırmanız gerekir.
    1. Altında Ayarlar sekmesinde, düğmesi Kaynak kutu.
    2. The veri Doğrulama iletişim kutusu Sayfa1'e yönlendirecektir, Adım 2'deki formülü içeren hücreyi (örneğin, D1) seçin, bir # simgesini tıklayın ve Kapanış düğmesine basın.
    3. Git Hata Uyarısı sekmesini tıklayın, Geçersiz veri girildikten sonra hata uyarısı göster onay kutusunu seçin ve son olarak OK butonuna basarak değişiklikleri kaydedin.
Sonuç

Sayfa2'nin A2 hücresindeki açılır liste artık aranabilir. Hücreye metni yazın, açılır listeyi genişletmek için açılır oka tıklayın; listenin, yazılan metinle eşleşecek şekilde anında filtrelendiğini göreceksiniz.

notlar:
  • Bu yöntem yalnızca Excel 2019 ve sonraki sürümlerde kullanılabilir.
  • Bu yöntem aynı anda yalnızca bir açılır liste hücresinde çalışır. Açılan listelerin Sayfa3'deki A8'ten A2'e kadar olan hücrelerde aranabilmesini sağlamak için, yukarıda belirtilen adımların her hücre için tekrarlanması gerekir.
  • Açılır liste hücresine metin yazdığınızda, açılır liste otomatik olarak genişlemez; manuel olarak genişletmek için açılır oka tıklamanız gerekir.

Kolayca aranabilir açılır liste oluşturun (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 sizin için çok etkili bir araç: Kutools for Excel's Açılır Listeyi Aranabilir Hale Getirin, Otomatik Açılırözellik. Bu özellik Excel'in tüm sürümlerinde mevcuttur ve basit bir kurulumla açılır listede istediğiniz öğeyi kolayca aramanıza olanak tanır.

Sonra Kutools for Excel'i indirme ve yüklemeseçin Kutools > Açılır liste > Açılır Listeyi Aranabilir Hale Getirin, Otomatik Açılır Bu özelliği etkinleştirmek için. İçinde Açılır Listeyi Aranabilir Hale Getirin iletişim kutusu, yapmanız gerekenler:

  1. Aranabilir açılır listeler olarak ayarlanması gereken açılır listeleri içeren aralığı seçin.
  2. Tıkla OK ayarları tamamlamak için.
Sonuç

Belirtilen aralıktaki bir açılır liste hücresine tıkladığınızda sağda bir liste kutusu görünür. Listeyi anında filtrelemek için metni yazın, ardından bir öğe seçin veya ok tuşlarını kullanın ve tuşuna basın. Keşfet hücreye eklemek için.

notlar:
  • Bu özellik şunları destekler: kelimelerin herhangi bir konumundan arama. Bu, bir kelimenin ortasında veya sonunda bulunan bir karakteri girseniz bile eşleşen öğelerin bulunup görüntüleneceği anlamına gelir; bu da daha kapsamlı ve kullanıcı dostu bir arama deneyimi sunar.
  • Bu özellik hakkında daha fazla bilgi edinmek için lütfen Bu sayfayı ziyaret.
  • Bu özelliği uygulamak için lütfen Kutools for Excel'i indirip yükleyin İlk.

Birleşik giriş kutusu ve VBA (daha karmaşık) ile aranabilir açılır liste oluşturun

Belirli bir açılır liste türü belirtmeden yalnızca aranabilir bir açılır liste oluşturmak istiyorsanız. Bu bölümde alternatif bir yaklaşım sunulmaktadır: görevi gerçekleştirmek için VBA kodlu bir Birleşik giriş kutusu kullanmak.

Aşağıdaki ekran görüntüsünde gösterildiği gibi A sütununda bir ülke adları listeniz olduğunu ve şimdi bunları arama açılır listelerinin kaynak verileri olarak kullanmak istediğinizi varsayalım, bunu yapmak için aşağıdakileri yapabilirsiniz.

Çalışma sayfanıza veri doğrulama açılır listesi yerine bir Birleşik Giriş kutusu eklemeniz gerekir.

  1. Eğer Geliştirici sekmesi şeritte görüntülenmiyorsa, etkinleştirebilirsiniz. Geliştirici sekmesi aşağıdaki gibidir.
    1. Excel 2010 veya sonraki sürümlerde, fileto > Opsiyonlar. Ve bu da Excel Seçenekleri iletişim kutusunu tıklayın Şerit özelleştirme sol bölmede. Şeridi Özelleştir liste kutusuna gidin, Geliştirici ve ardından OK buton. Ekran görüntüsüne bakın:
    2. Excel 2007'te Office düğmesi> Excel Seçenekleri. In Excel Seçenekleri iletişim kutusunu tıklayın Popüler sol bölmede, kontrol edin Şeritte Geliştirici sekmesini göster kutusunu seçin ve son olarak OK düğmesine basın.
  2. gösterdikten sonra Geliştirici sekmesini tıklayın Geliştirici > Ekle > Açılan kutu.
  3. Çalışma sayfasına bir Birleşik giriş kutusu çizin, sağ tıklayın ve ardından seçin Emlaklar sağ tıklama menüsünden.
  4. içinde Emlaklar iletişim kutusu, yapmanız gerekenler:
    1. seç Yanlış içinde Otomatik Sözcük Seçimi alan;
    2. İçinde bir hücre belirtin BağlantılıHücre alan. Bu durumda A12 giriyoruz;
    3. seç 2-fmMatchEntryYok içinde Maç Girişi alan;
    4. Tip Açılır liste içine ListeFillRange alan;
    5. Kapat Emlaklar iletişim kutusu. Ekran görüntüsüne bakın:
  5. Şimdi tıklayarak tasarım modunu kapatın. Geliştirici > Tasarım Modu.
  6. C2 gibi boş bir hücre seçin, aşağıdaki formülü girin ve tuşuna basın. Keşfet. Hücreleri aynı formülle otomatik olarak doldurmak için Otomatik Doldurma Tutamağını C9 hücresine sürüklerler. Ekran görüntüsüne bakın:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    notlar:
    1. $ A $ 12 olarak belirttiğiniz hücredir BağlantılıHücre 4. adımda;
    2. Yukarıdaki adımları tamamladıktan sonra şimdi test edebilirsiniz: Açılan kutuya bir C harfi girin ve ardından C karakterini içeren hücrelere referans veren formül hücrelerinin 1 rakamıyla doldurulduğunu görebilirsiniz.
  7. D2 hücresini seçin, aşağıdaki formülü girin ve tuşuna basın. Keşfet. Ardından Otomatik Doldurma Tutamağını D9 hücresine sürükleyin.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. E2 hücresini seçin, aşağıdaki formülü girin ve tuşuna basın Keşfet. Ardından, aynı formülü uygulamak için Otomatik Doldurma Kolu'nu aşağı E9'a sürükleyin.
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. Şimdi bir ad aralığı oluşturmanız gerekiyor. lütfen tıklayın formül > Adı Tanımla.
  10. içinde Yeni isim iletişim kutusunda, şunu yazın: Açılır liste içinde İsim kutusuna aşağıdaki formülü girin Anlamına gelir ve ardından OK düğmesine basın.
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. Şimdi tıklayarak tasarım modunu açın. Geliştirici > Tasarım Modu. Ardından, açmak için Combo kutusuna çift tıklayın. Uygulamalar için Microsoft Visual Basic pencere.
  12. Aşağıdaki VBA kodunu kopyalayıp Kod düzenleyiciye yapıştırın.
    VBA kodu: açılır listeyi aranabilir hale getirin
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. Basın Ara Toplam + Q kapatmak için anahtarlar Uygulamalar için Microsoft Visual Basic pencere.

Artık açılan kutuya bir karakter girildiğinde, bulanık bir arama yapacak ve ardından listedeki ilgili değerleri listeleyecektir.

not: VBA kodunu ileride kullanmak üzere saklamak için bu çalışma kitabını bir Excel Makro Etkin Çalışma Kitabı dosyası olarak kaydetmeniz gerekir.

En İyi Ofis Üretkenliği Araçları

Kutools for Excel - Kalabalıktan Ayrılmanıza Yardımcı Olur

🤖 Kutools AI Yardımcısı: Aşağıdakilere dayalı olarak veri analizinde devrim yaratın: Akıllı Yürütme   |  Kodunu oluşturun  |  Özel Formüller Oluşturun  |  Verileri Analiz Edin ve Grafikler Oluşturun  |  Kutools İşlevlerini Çağır...
Popüler Özellikler: Yinelenenleri Bul, Vurgula veya Tanımla  |  Boş Satırları Sil  |  Veri Kaybı Olmadan Sütunları veya Hücreleri Birleştirin  |  Formülsüz Tur ...
Süper VArama: Çoklu Kriterler  |  Çoklu Değer  |  Çoklu Sayfalarda  |  Bulanık Arama...
Av. Açılır liste: Kolay Açılır Liste  |  Bağımlı Açılır Liste  |  Çoklu Seçim Açılır Liste...
Sütun Yöneticisi: Belirli Sayıda Sütun Ekleme  |  Sütunları Taşı  |  Gizli Sütunların Görünürlük Durumunu Değiştir  Sütunları şununla karşılaştır: Aynı ve Farklı Hücreleri Seçin ...
Öne Çıkan Özellikler: Izgara Odağı  |  Tasarım görünümü  |  Büyük Formül Çubuğu  |  Çalışma Kitabı ve Sayfa Yöneticisi | Kaynak Kütüphanesi (Otomatik metin)  |  Tarih Seçici  |  Çalışma Sayfalarını Birleştirin  |  Hücreleri Şifrele/Şifresini Çöz  |  E-postaları Listeye Göre Gönder  |  Süper Filtre  |  Özel Filtre (kalın/italik/üstü çizili filtre...) ...
En İyi 15 Araç Seti12 Metin Tools (Metin ekle, Karakterleri Kaldır ...)  |  50+ Grafik Türleri (Gantt şeması ...)  |  40+ Pratik Formüller (Yaşı doğum gününe göre hesapla ...)  |  19 sokma Tools (QR Kodunu Girin, Yoldan Resim Ekle ...)  |  12 Dönüştürme Tools (Sayılardan Kelimelere, Para Birimi Dönüştürme ...)  |  7 Birleştir ve Böl Tools (Gelişmiş Kombine Satırları, Excel Hücrelerini Böl ...)  |  ... ve dahası

Kutools for Excel 300'den Fazla Özelliklere Sahiptir, İhtiyacınız Olanın Bir Tık Uzağınızda Olmasını Sağlıyoruz...

Açıklama


Office Sekmesi - Microsoft Office'te Sekmeli Okuma ve Düzenlemeyi Etkinleştir (Excel dahil)

  • Düzinelerce açık belge arasında geçiş yapmak için bir saniye!
  • Her gün yüzlerce fare tıklamasını azaltın, fare eline veda edin.
  • Birden çok belgeyi görüntülerken ve düzenlerken üretkenliğinizi% 50 artırır.
  • Tıpkı Chrome, Edge ve Firefox gibi Verimli Sekmeleri Office'e (Excel dahil) getirir.