1. AMAÇ
Bazen çok uzun bir veri doğrulama listesi kullandığınızda, istediğiniz seçeneği listeden bulmak çok zordur. Listeyi girdiğiniz harflere göre arayarak sadece uyan girdileri göstermek listede daha az madde gösterilmesini sağlar ve seçimi oldukça kolaylaştırır. Bu yazımda makro kullanmadan bunu nasıl yapabileceğinizi göstereceğim.
İsim listesini ben GitHub üzerinden çektim. Birden fazla olan girdileri eledim. Yaklaşık 1100 tane isimden oluşan bir liste elde ettim bu nedenle tabloyu burada paylaşmıyorum. İsimlerin olduğu örnek dosyayı buradan indirebilirsiniz.
2. UYGULAMA
Örnek dosyada isimlerin yer aldığı liste A2:A1131 aralığında, B sütununu veri doğrulama için kullanacağım liste için ayırdım. İsim Seçme hücresi ise E2 hücresi. Buraya daha sonra bir liste veri doğrulama ekleyeceğiz.
Yapacağım işlem sırası ile E2 hücresindeki karakterlerin listedeki isimlerin hangilerinin içinde yer aldığını bulmak, bu hücrelerin satır değerlerini almak ve en sonunda bu değerleri ayrı bir listeye yerleştirmek.
a. Aranan Değerin Hangi Hücre Değerlerinde Olduğunu Bulmak
Aramak istediğim değeri E2 hücresinde yer alıyor. Bu değeri de A2:A1131 aralığında bulmam gerek. Bunun için MBUL işlevini kullanacağım:
=MBUL($E$2; $A$2:$A$1131)
Bu formül aradığım değer hücre içinde yer alıyor ise hangi karakterden itibaren bulunduğunu ifade eden bir rakam, bulunmuyor ise “#DEĞER!” hatası döndürecektir. Yani elimde #DEĞER!, #DEĞER!, 2, 1,… gibi bir liste oluşacaktır. Bu nedenle döndürülen değerin bir rakam olup olmadığını kontrol etmem gerekiyor. Bunu da ESAYIYSA işlevi ile yapacağım:
=ESAYIYSA(MBUL($E$2; $A$2:$A$1131)
Bu sayede elimdeki liste YANLIŞ, YANLIŞ, DOĞRU, DOĞRU,… şekline dönüşecektir. Bu değer YANLIŞ ise aradığım değer hücre içinde yok, doğru ise hücre içinde var anlamına gelmekte. Excel 365 kullananlar bu formülü yazdığı zaman B2:B1131 aralığına formül sonuçları yansıyacaktır. Daha eski Excel sürümünü kullananlar formülü aşağıya doğru çoğaltabilir.
Formülün çalışıp çalışmadığını görmek için E2 hücresine “ALİ” yazıyorum.
b. Hücrelerin Satır Değerlerini Çekmek
Bundan sonraki aşamada aradığım değerin hangi satır numarasında olduğunu bulmam gerek. İsimlerin yer aldığı aralığın satır numaralarını bu formül sonuçlarına böleceğim:
=SATIR($A$2:$A$1131)/ ESAYIYSA(MBUL($E$2; $A$2:$A$1131)
Bu formül ile artık elimde #BÖL/0!, #BÖL/0!, 4, 5,… gibi hatalardan ve satır numaralarından oluşan bir liste oluşacak. Bunu da TOPLAMA işlevini kullanarak sadece rakamları çekecek şekilde düzenleyebilirim:
=TOPLAMA(16; 5; SATIR($A$2:$A$1131)/ESAYIYSA(MBUL($E$2; $A$2:$A$1131); SATIRSAY($A$1:$A1))-1
Burada dikkat etmeniz gereken husus A1 satırında başlık olduğundan bulduğumuz satır değerlerini bir eksiltemiz gerektiği. Bu formülü B2 hücresine yazıp aşağıya doğru tüm aralığa çoğalttığımda artık elimde sadece satır numaraları ve hata değerleri olacak. Hata değerlerini şimdilik es geçiyorum.
c. Eşleşen Hücre Değerlerini Çekmek
Satır numaralarını aldıktan sonra eşleşen değerleri çekmek için İNDİS işlevini kullanacağız:
=İNDİS(TOPLAMA(16;5;SATIR($A$2:$A$1131)/ESAYIYSA(MBUL($E$2;$A$2:$A$1131);SATIRSAY($A$1:$A1))-1;)
Bu formül ile eşleşen değerleri çektim ancak tabii ana listedeki tüm değerlerde eşleşme olmadığı için hata değerleri de geldi. Şimdi bu hata değerlerini EĞERHATA işlevini kullanarak eleyeceğim:
=EĞERHATA(İNDİS(TOPLAMA(16;5;SATIR($A$2:$A$1131)/ESAYIYSA(MBUL($E$2;$A$2:$A$1131); SATIRSAY($A$1:$A1))-1;);””)
Bundan sonra da bir isim tanımlamam gerek. Bunun için KAYDIR işlevini kullanacağım. İsim yöneticisi kısmına girip “İsimListesi” adında yeni bir isim oluşturuyorum ve başvuru kısmına aşağıdaki formülü yazıyorum:
=KAYDIR($B$2;;EĞERSAY($B$2:$B$1131;”*?”);1)
Bu ismi veri doğrulama olarak E2 hücresine tanımladığımda işlem tamamlanmış oluyor. Bu yöntemi kullanmak için önce hücre içine çift tıklayarak bulmak istediğiniz harfleri girmeniz gerek. Ardından hücre yanındaki oka basarak listenin güncellenmesini sağlayarak istediğiniz isimlerin gelmesini sağlamalısınız.
