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.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , , , ,

Leave a Reply