Orta Ders 9: Veri Doğrulama ve Açılır Liste

Veri doğrulama neden gerekir?

Veri doğrulama, bir hücreye girilebilecek değerleri sınırlayarak hatayı kaynağında durdurur. Birden fazla kişinin doldurduğu bir talep formunda "Muhasebe", "muhasebe" ve "Muhasbe" aynı sütunda yan yana durabilir. Bu üç yazım, ETOPLA ve pivot tablo için üç ayrı departman demektir.

Yanlış veriyi sonradan temizlemek, baştan engellemekten çok daha fazla zaman alır. Açılır liste kullanıcının seçim yapmasını sağlar. Sayı sınırı da 15 yerine yanlışlıkla 150 yazılmasını durdurur.

Kural nereden eklenir?

Kural eklemek için hücreleri seçip Veri sekmesindeki Veri Doğrulama komutunu açarsınız. Ayarlar sekmesindeki İzin Verilen listesi kuralın türünü belirler: Tam Sayı, Ondalık, Liste, Tarih, Saat, Metin Uzunluğu ya da Özel.

Adet sütunu için Tam Sayı'yı seçip 1 ile 100 arasını girdiğinizde, bu aralığın dışındaki bir sayı ya da ondalıklı bir değer kabul edilmez. Tarih sütununda "bugünden ileri olamaz" gibi bir kural için Tarih türünü ve küçük eşittir koşulunu kullanabilirsiniz.

Açılır liste oluşturmak

Açılır liste, İzin Verilen alanında Liste seçilerek kurulur. Kaynak kutusuna değerleri noktalı virgülle yazabilirsiniz: Satış;Muhasebe;Depo. Liste uzunsa ya da sık değişiyorsa değerleri ayrı bir sütunda tutup Kaynak'a o aralığı vermek daha pratiktir, örneğin =$F$2:$F$4.

Kaynak listesini bir Excel tablosuna çevirirseniz yeni departman eklediğinizde açılır liste de büyür. Bunun için kaynak alanında tablo sütununu doğrudan değil, DOLAYLI ile ya da tanımlı bir ad üzerinden vermek gerekir. Basit kullanımda listeyi biraz geniş bir aralık olarak seçip altına boş satır bırakmak da işe yarar.

Mevcut veriyi denetlemek

Veri doğrulama kuralı yalnız yeni girişleri denetler, kural konmadan önce yazılmış değerlere dokunmaz. Elinizde zaten dolu bir tablo varsa önce geçersiz kayıtları bulmanız gerekir. Bunun için kuralın mantığını bir formülle yeniden kurarsınız.

=EĞERSAY(F2:F4;B4)>0

=COUNTIF(F2:F4,B4)>0

EĞERSAY, B4'teki "Pazarlama" değerinin F2:F4 listesinde kaç kez geçtiğini sayar. Sonuç 0 olduğu için karşılaştırma YANLIŞ döner, yani bu kayıt listede yok.

=VE(C3>=1;C3<=100)

=AND(C3>=1,C3<=100)

İkinci talebin adedi 150'dir ve 1-100 aralığının dışında kalır. VE, iki koşulun birlikte sağlanıp sağlanmadığına bakar ve YANLIŞ döner. Veri sekmesinde Veri Doğrulama menüsünün altındaki Geçersiz Verileri Daire İçine Al komutu da aynı denetimi görsel olarak yapar.

Giriş iletisi ve hata uyarısı

Giriş İletisi sekmesine yazdığınız metin, kullanıcı hücreyi seçtiğinde küçük bir not olarak görünür. "1 ile 100 arasında tam sayı girin" gibi kısa bir açıklama, formu ilk kez dolduran birinin hata yapma ihtimalini azaltır.

Hata Uyarısı sekmesi kural ihlalinde ne olacağını belirler ve üç stil sunar. Durdur geçersiz değeri hiç kabul etmez. Uyarı ve Bilgi ise kullanıcıya sorar, onaylarsa değeri yine yazar; bu iki stili yalnız gerçekten istisnaya izin vermek istediğinizde kullanın.

Sık karşılaşılan sorunlar

Veri doğrulama, kopyala yapıştır ile atlatılabilir. Başka bir yerden kopyalanan değer hücreye yapıştırıldığında kural da üzerine yazılır. Bu yüzden önemli formlarda zaman zaman yukarıdaki denetim formüllerini çalıştırmak iyi bir alışkanlıktır.

Açılır liste görünmüyorsa Ayarlar sekmesindeki Hücre içi açılan liste kutusunun işaretli olduğunu kontrol edin. Kaynak aralığı başka bir sayfadaysa da formülün o sayfayı doğru gösterdiğine bakın, ör. =Listeler!$A$2:$A$10.

Ortak harcama formu örneği

Bir ekibin aynı dosyaya harcama kaydı girdiğini düşünün. Kategori sütunu açılır listeyle Ulaşım, Yemek, Konaklama ve Kırtasiye seçenekleriyle sınırlanır. Tutar sütunu 0'dan büyük ondalık sayı kabul eder. Tarih sütunu da yalnız içinde bulunulan yılı kabul edecek biçimde ayarlanır.

Ay sonunda ETOPLA ile kategori toplamlarını aldığınızda "yemek", "Yemek " ve "Yemekler" gibi farklı yazımlarla uğraşmazsınız, çünkü listede yalnız bir yazım vardır. Hata Uyarısı'na "Tutarı TL olarak, ondalık için virgülle girin" gibi bir açıklama yazmak da yanlış girişte kullanıcının ne yapacağını anlamasını sağlar.

Formu paylaşmadan önce birkaç geçersiz değer girip kuralların gerçekten çalıştığını kendiniz test edin.

Kategori listesini ayrı bir Listeler sayfasında tutup sayfayı gizlemek, kullanıcıların kaynağı yanlışlıkla değiştirmesini de önler.

Sıradaki adım

Alıştırmada dört talep satırını hem departman listesine hem adet sınırına göre denetleyen bir Kontrol sütunu kuracaksınız. Ardından kendi dosyanızda aynı sütunlara açılır liste ve tam sayı kuralı ekleyip yeni bir satır girmeyi deneyin.