Formül ile Veri Doğrulama oldukça geniş bir konu. Burada tüm yeteneklerini anlatmak mümkün değil. Ancak yol göstermesi açısından bir kaç örnek yapmak istiyorum. Aşağıda ilgili örnekleri bulabilirsiniz.
Yan yana hücrelerimizde yüzde toplamı yaptığımızı varsayalım. Pareto analizi gibi. Ve ardışık hücrelere girilen verilerin toplamı %100 olmalı. daha fazla değil. Buna göre veri doğrulama nasıl yapabiliriz?
Aşağıdaki gibi bir tablomuz olduğunu varsayalım.
Neden 1 | Neden 2 | Neden 3 | Neden 4 | Neden 5 | Neden 6 | Neden 7 | Neden 8 | Neden 9 | Diğer |
---|---|---|---|---|---|---|---|---|---|
25 | 20 | 16 | 12 | 8 | 6 | 3 | 2 | 1 | 7 |
Tabloyu A-J sütunlarına kopyaladığınızı varsayıyorum. Bu tablonun 2. satırındaki verilerin toplamı 100 olmalıdır. Eğer Excel’de Yüzde hücre formatını seçti isek verilerin 1’den küçük olması da gerek. İsterseniz önce formülümüzü yazalım. İlk şartımız değerin 1’den küçük olması:
=A2<1
Dikkat ederseniz hem satır hem de sütun değerlerinin önünde $ işareti yok. Bunun nedeni eğer geniş bir aralıkta veri doğrulama yapar isek doğrulamanın, değer girilmiş hücreye göre ayrı ayrı yapılmasını sağlamak. Eğer başlarına $ işareti koyarsak sadece $A$2 hücresindeki veriye göre veri doğrulama yapılır. Bu kısa açıklamadan sonra ikinci şartımız olan A:J aralığı toplamının 1’e eşit veya küçük olması formülünü yazalım:
=TOPLA($A2:$J2)<=1
Burada da dikkat ederseniz sadece sütunlardan önce $ işareti var. Bunun amacı veri doğrulamanın çok satır dağılması halinde her zaman o satırın A ve J hücreleri arasını almasını sağlamak. Şimdi bu iki şartı birleştirmemiz gerek.
=VE(A2<1;TOPLA($A2:$J2)<=1)
Şimdi bu formülü Veri Doğrulama alanına girelim. Önce A-J aralığında hangi satırlarda veri doğrulama yapılmasını istiyorsak o aralığı seçelim. Sonra Özel seçeneğine tıklayalım ve açılan yeni Formül kutusuna bu formülümüzü yazalım. Artık bu hücrelere 1’den büyük rakam girilemez ve aralığın toplamı da 1’den büyük olamaz. Aşağıdaki videoda sonuçları gösterdim:
Bir tablonun herhangi bir sütununda birbirinin aynı sayının ya da metnin olmasını istemiyorsanız ne tür bir formül kullanmamız gerekir? Hızlıca bir bakalım. Örnek olarak aşağıdaki tabloyu alabiliriz.
ÇalışanNo | Ad | Soyad | Tarih |
---|---|---|---|
1000 | Ahmet | Çağ | 01/08/2016 |
1001 | Mehmet | Demir | 08/08/2017 |
1002 | Hasan | Ulak | 10/12/2017 |
1003 | Merve | Bayrak | 08/02/2018 |
Bu tabloda ilk sütun çalışan için bizim tanımladığımız bir kimlik numarası ve her bir çalışan için tek bir numara tanımlanabilir. Diğer bir ifade ile her bir numara sadece tek bir çalışana ait olmalıdır. Bunu ürün için de düşünebilirsiniz. Burada nasıl bir veri doğrulama kullanalım ki her sayı tek olsun. Öncelikle Çalışan No’sunun A sütununda olduğunu varsayarak A:A sütununu komple seçiyoruz. Sonra Veri Doğrulama kısmına girip Özel diyoruz ve açılan kutucuğa aşağıdaki formülü yazıyoruz.
=EĞERSAY($A:$A;$A1)<=1
Burada gördüğünüz gibi A:A sütununda değeri A1’de ye alan değer ile aynı olan hücrelerin sayısını buluyoruz ve bu sayı 1’e eşit ve küçük olmalı. Yani sadece 1 tane olmalı. Bu formülde $A1 ifedesine dikkat çekmek istiyorum. Daha önce de anlattığım üzere sadece sütunu sabitleyerek satırın doğrulamanın yapılacağı satıra göre alınmasını sağlıyoruz bu sayede doğrulama yapılan her bir satır için o An hücresindeki veri alınıyor.Yine bir ek bilgi olarak $A:$A yerine siz bir değişken aralık tanımlayarak bu aralığı da kullanabilirsiniz.
Peki bir adım ileri taşıyalım. Diyelimki bizim personel numaralarımız 4 rakamdan oluşacak. Bunu nasıl kontrol edebiliriz? Öncelikle girilen değerin bir rakam olup olmadığını kontrol edelim:
=ESAYIYSA($A1)
Sonrasında girilen sayının uzunluğunu kontrol edelim:
=UZUNLUK($A1)=4
şimdi de tüm bu şartları birleştirelim:
=VE(EĞERSAY($A:$A;$A1)<=1;ESAYIYSA($A1); UZUNLUK($A1)=4)
Bu üç şartı birleştirdiğimizde artık kullanıcı birbirinin aynısı olan, 4 haneden daha kısa olan ve rakam olmayan bir ifadeyi bu hücreye giremez. Tabii bu kadar karmaşık bir kontrol olduğundan ikinci sekmedeki bilgilendirme kısmına da düzgün bir açıklama girmek kullanıcıyı bilgilendirmek açısından çok faydalı olacaktır.
Tabii son olarak aşağıdaki kısa videoda işlem adımlarını da gösterdim.
Ad, soyad, adres gibi veri alanlarına girilen verilerin daha sonra kullanılması ve ya eşleştirilmesi esnasında yaşanan en büyük sıkıntılardan bir tanesi metnin başında ve sonunda görünmeyen boşluk gibi karakterler olmasıdır. Veri girişi esnasında kullanıcının bu tarz hatalar yapmasını nasıl engelleriz? Fromülümüz oldukça basit daha önce yazdığım metin formülleri yazısını okudu iseniz bu formülü siz de yazabilirsiniz:
=($A1=KIRP($A1))
A1 hücresine girilen değer ile bu hücreye girilen değerin başındaki, sonundaki ve kelimeler arasında fazladan girilmiş boşluklar atılmış hali aynı olmalıdır. Misal kullanıcı ad soyad giriyor, bu iki kelime arasında çift boşluk bıraktı veya başında sonunda fazladan bir boşluk verdi ise bu formül hata verir.
Eğer yazılan metin içinde hiç boşluk olmamasını istiyorsak:
=($A1=YERINEKOY($A1;” “;””)
formülünü kullanabiliriz. Burada YERİNEKOY ile metin içindeki boşluklar tamamen temizleniyor. Elde edilen sonuç kullanıcının girdiği veri ile kıyaslanıyor.
Eğer metin içinde belli bir karakter veya metinden olması gerekiyor ise bunun için de kullanacağımız formül:
=BUL(” “;$A1;1)=>1
Metin içinde boşluk karakteri olması gerekiyor ise (ad soyad) gibi bu formülü kullanabilirsiniz. Veya bir ürünü tanımlamak için kullandığınız kod içinde metin olması gerekiyor ise o metni bu formül içinde kullanarak kullanıcının her defasında standart giriş yapmasını sağlayabilirsiniz.
Veri doğrulama içi yapılabilecek daha bir çok örnek var. Ben aklıma gelen ve çok sık kullandığım bir kaç tanesini buraya ekledim. Eğer sizlerin burada gözükmesini istediğiniz örnekler var ise lütfen aban bilgi verin.
Merhaba;
Veri doğrulama kullanarak bir sütundaki hücrelere sadece “34-2019-” veya “34-2020- ile başlayan girişlerin yapılması (8 karakter) kısıtlama yapabilir miyiz?
Merhaba
değerin B2 hücresinde olduğunu düşünerek aşağıdaki gibi bir formül kullanabilirsiniz. Ben hızlıca yazdım. Buradaki 8 değerini ve metin ifadelerini kendi ihtiyacınıza göre güncellemeniz gerekir.
=VE(UZUNLUK($B2)=8;YADA(EĞERHATA(MBUL(“34-2019-“;$B2;1);YANLIŞ);EĞERHATA(MBUL(“34-2020-“;$B2;1);YANLIŞ)))
merhaba,
veri doğrulamanın içine düşeyara kullanmak istiyorum yardımcı olabilirmisiniz ?
Merhaba,
Veri doğrulama olarak misal hücre değeri ile vlookup sonucunu kıyaslamak istiyorsanız yapabilirsiniz. Tam olarka ne tür bir kıyas yapmak istiyorsunuz?
Saygılarımla,