1. Amaç
Bu liste veri doğrulama örneğinde listedeki her bir öğeden sadece 1 tane seçilebilmesini anlatacağım. Öncelikle bunun birden fazla yöntemi mevcut. Yardımcı sütun kullanarak yapabilirsiniz, yine farklı yöntemleri mevcut. Ben hem yardımcı sütun kullanmayarak nasıl yapılacağını göstermek istiyorum.
Uygulamayı kendi başınıza yapmak isterseniz örnek değerleri aşağıdaki listede bulabilirsiniz. Dosya üzerinden ilerlemek için ise buradan dosyayı indirebilirsiniz.
| İsimler |
| ALİ |
| BAHATTİN |
| BARIŞ |
| BİRSEN |
| BÜNYAMİN |
| EMRE |
| ERGÜN |
| FATİH |
| FERDİ |
| GAMZE |
| GÜLŞAH |
| HATİCE |
| HAYATİ |
| IRAZCA |
| İLHAN |
| JALE |
| KUBİLAY |
| MAHMUT |
| MANSUR |
| MİRAÇ |
| MÜGE |
| NALAN |
| ÖZGÜR |
| REYHAN |
| REZAN |
| SEMİH |
| SERDAL |
| SERKAN |
| ŞENAY |
| YÜCEL |
2. Uygulama
Tüm isimlerin yer aldığı liste (isim listesi) D2:D32 arasında yer alıyor. Ad girişlerinin olduğu aralıkta (seçili isimler) B2:B33 aralığında yer almakta. Veri doğrulamayı bu aralıkta yapacağım.
Yapmak istediğim şey, isim listesinde yer alıp seçili isimler listesinde yer almayan değerleri bulmak, bu değerlerin satır numaralarını çıkarmak ve sonrasında İNDİS işlemi ile bunları ayrı bir listeye çekmek ve veri doğrulamada bu listeyi kullanmak.
a.Hangi İsimlerin Seçildiğini Bulmak
İlk yapacağımız işlem seçtiğimiz isimlerin listede olup olmadığını kontrol etmek olacak. Bunun için EĞERSAY işlevini kullanacağız:
=EĞERSAY($B$3:$B$32;$D$3:$D$32)
Bu formül bize isim listesinde yer alan hangi isimleri seçili isimler listesinde toplam kaç tane olduğunu, yani var olup olmadığını verecektir. Excel 365 kullananlar bu formülü yazdıklarında alt alta 0’lardan oluşan bir liste elde eder. Daha eski Excel sürümleri için:
=İNDİS(EĞERSAY($B$3:$B$32;$D$3:$D$32);SATIRSAY($B$3:$B3);)
Formülünü yazıp aşağıya kadar çoğaltırlarsa aynı sonucu elde ederler. Bu yöntemi sonraki formülleri test etmek için de kullanabilirsiniz. Şimdi formülün çalışıp çalışmadığını görmek için B3 hücresine “Barış” yazalım her iki formül de 1,0,0 şeklinde bir liste döndürecektir.
b. Seçilmeyen İsimlerin Satır Numarasını Döndürmek
Seçim listesinde hangi isimlerin seçildiğini bulduk ancak bize seçilmeyen isimlerin listesi lazım. Bu yüzden ben bu formülü DEĞİL işlevi ile birleştireceğim:
=DEĞİL(EĞERSAY($B$3:$B$32;$D$3:$D$32)
Bu formül ile eğer isim daha önce girilmiş ise “YANLIŞ”, girilmemiş ise “DOĞRU” değerlerinden oluşan bir liste verecektir.
Şimdi yapmamız gereken iş SATIR işlevi ile satır numarasını bu formül ile elde ettiğimiz değerlere bölmek:
=SATIR($B$3:$B$32)/DEĞİL(EĞERSAY($B$3:$B$32;$D$3:$D$32)
Bu formül sonrası “#SAYI/0!”,4,5,… gibi bir liste oluşacaktır. Gördüğünüz gibi seçili olan isimlerin olduğu satır numaraları hata döndürüyor, olmayanlar ise satır numarası.
Sonraki aşamada bu listedeki hata değerlerini dikkate almadan sadece satır numaralarını döndürmemiz gerek. Bunun için TOPLAMA işlevini kullanacağım:
=TOPLAMA(15;6;SATIR($B$3:$B$32)/DEĞİL(EĞERSAY($B$3:$B$32;$D$3:$D$32);SATIRSAY($B$3:$B3))
Benim verilerim 3. Satırdan itibaren başladığından bulunan değerlerden ilk 2 satırı çıkarmam gerek.
=TOPLAMA(15;6;SATIR($B$3:$B$32)/DEĞİL(EĞERSAY($B$3:$B$32;$D$3:$D$32);SATIRSAY($B$3:$B3))-2
Bu formülü yazıp aşağıya doğru çoğalttığınızda listedeki hataları dikkate almadan tüm satır numaralarını alt alta elde edersiniz, son değerin bir hata döndürdüğüne dikkat edin, bunu daha sonra çözeceğiz.
c. Seçim Listesinde Yer Almayan Değerleri Çekme Ve Veri Doğrulama
Elimizde satır numaraları olduğuna göre artık İNDİS işlevi ile bu satırlara denk gelen değerleri yeni listemize çekebiliri:
=İNDİS($D$3:$D$32;TOPLAMA(15;6;SATIR($B$3:$B$32)/DEĞİL(EĞERSAY($B$3:$B$32;$D$3:$D$32) ;SATIRSAY($B$3:$B3))-2;)
Bu formülü yazıp aşağıya doğru çoğalttığınızda elimizde sadece seçilmemiş isimlerin yer aldığı bir liste olacak, ancak listenin sonunda seçilen değer kadar “#SAYI” hatası elde edeceksiniz. Şimdi bu hatayı da EĞERHATA işlevi ile kaldıralım
=EĞERHATA(İNDİS($D$3:$D$32;TOPLAMA(15;6;SATIR($B$3:$B$32)/ DEĞİL(EĞERSAY($B$3:$B$32;$D$3:$D$32);SATIRSAY($B$3:$B3))-2;);””)
Bu formül ile hataları da eledikten sonra sadece seçilmemiş isimlerin olduğu bir liste oluşacak. Şimdi bu ismi KAYDIR işlevini kullanarak bir değişken aralık olarak tanımlayalım:
=KAYDIR($E$3;;;EĞERSAY($E$3:$E$32;”*?”);1)
Bu formülü İsim Tanımlama kısmına girerek “İsimListesi” adında yeni bir isim oluşturarak “Başvuru” kısmına ekliyorum. Artık Veri Doğrulama için gerekli her şeye sahibim. B3:B32 aralığını seçiyorum ve Veri sekmesinde Veri Doğrulama kısmına girerek “Liste” seçeneğini seçiyorum. Kaynak kısmına:
=İsimListesi
Formülünü yazıyorum. Artık bu aralıkta bir kez seçtiğim isim bir daha seçilemeyecek şekilde listeden çıkarılıyor.
Süpersin
Teşekkürler