Excel tablolarında dosyaları düzenlerken yardımcı elemanlardan veri doğrulama çok sık kullanılan araçlardan biridir. Seçmiş olduğumuz bir listeyi açılır pencere içerisinde listeler ve böylece ilgili alanlarda hücre içerisine herhangi bir giriş yapmadan, istediğimiz veriyi liste içerisinden seçebiliriz. Bu şekilde kullanıcı tarafından oluşan hataları önleyip istenmeyen verilerin girişini engelleyebiliriz.

Ancak bu veri doğrulamanın bir takım eksik yönleri vardır. Örneğin bir satış tablosunda ürünlerin isimlerini, müşteri tablosunda müşteri isimlerini listeden seçerken içerisinde filtre yapmamıza izin vermez. Böyle bir özelliği yoktur. Yüzlerce hatta binlerce kayıttan oluşan bir liste içerisinde de aradığımızı bulmak imkansız hale gelebilir. Peki bir çözümü yok mudur?  Evet tabii ki vardır. VBA (Visual Basic Application) ile bu sorunu kolayca halledebiliriz. Ama gelin, hiç VBA kodu yazmadan bu sorunun üzerinden Excel’de var olan fonksiyonları kullanarak farklı bir biçimde nasıl gelebiliriz onu inceleyelim.

 

1.Excel’de Veri Doğrulama

 

 

Bir excel sayfasında, sahip olduğumuz listeyi bir hücrede nasıl açılır pencere yardımı ile görebiliriz öncelikle ona bakalım. Çalışma sayfasında, verileri liste halinde görmek istediğimiz hücreyi seçtikten sonra Data sekmesinde, Data Tools alanında Data Validation butonu bulunmaktadır. Bu butona tıkladığımızda karşımıza gelen data validation penceresinin ayarlar sekmesi bize, o hücre için izin verilen değerleri gösterir. Bu seçenek otomatik olarak herhangi bir değer olarak gelir. Yani biz o hücrenin herhangi bir özelliğine müdehale etmeden istediğimiz veri tipini girebiliriz. Herhangi bir değer seçeneğini, Liste olarak değiştirirsek pencerenin altında bir kaynak alanı açılır. Bu kaynağa tıklayarak liste halinde görmek istediğimiz verileri hem yazabiliriz hem de bir sayfadan dizi biçiminde seçebiliriz. Böylece verileri belli bir hücreye manuel olarak yazmaktan veya kullanıcı tarafından veri girişi esnasında oluşabilecek hataların önüne geçmiş oluruz.

 

 

 

 

 

Peki böyle bir liste oluşturmak bütün sorunların çözümü olabilir mi? Maalesef cevabımız Hayır. Çünkü Excel’in veri doğrulama özelliği, kullanıcıyı bir hücreye izin verilen değerleri girmesini sağlamak amacıyla yapılır. Kullanıcı istediği girişi yapamaz.  Bu yüzden Veri doğrulamada veri seçimi yapılabilir. Veri girişi yapılamaz. Veri girişi yapamayacağı için herhangi bir filtrelemeye de olanak sağlamak.  Ancak girişte de belirttiğimiz gibi, bu listeler yüzlerce hatta binlerce veriden oluşabilir. O zaman da kullanıcının istediği veriyi liste içinde arayıp bulması uzun zaman gerektirebilir, hatta imkansız hale gelebilir. Biraz uzun ancak çok etkili ve ilginç bir şekilde excel fonksiyonları kullanarak Veri doğrulama içerisinde filtreleme gerçekleştirelim.

 

1.Veri Doğrulamada Filtreleme İşlemi

i)Dinamik Liste Kayıtlarını Belirlemek

 

 

Temelde bu işlemi gerçekleştirebilmemiz için, elimizdeki veri listesini dinamik hale getirmemiz gerekir. Veri doğrulama özelliğinin bulunduğu hücreye herhangi bir giriş yaptığımızda, yazmış olduğumuz veriyi içeren kayıtlardan yeni bir liste oluşturmalıyız.  O zaman ilk olarak, yazmış olduğumuz veri, listenin içerisinde var mı ona bakmalıyız. Kayıtların bulunduğu sayfada  Search() fonksiyonu ile bu işlemi gerçekleştirebiliriz. Yaptığı işlem, aradığımız verinin, bir kayıtın içinde var olup olmadığına bakar. Eğer varsa kaçıncı karakterinden itibaren içeriyor bize o sayıyı verir. İçermiyorsa bir “#Value” hatası alırız. Ancak bize kaçıncı karakterden itibaren içerdiği bilgisi gerekmediği için, bu fonksiyondan dönen değerin sayı olup olmadığı bilgisi yeterlidir. Çünkü dinamik listemizi oluştururken hücre içine yazmış olduğumuz veri hangi kayıtlarda varsa o kayıtların gelmesi gerekmektedir.  Sayı bilgisini de alacağımız fonksiyon Isnumber() Fonksiyonudur.bu fonksiyon ile seçmiş olduğum hücre içindeki veri sayı mı değil mi ona bakar. Sayı ise ‘True’ aksi takdirde ‘False’ değer döndürür. Bu True ve False değerlerini de bir If() fonksiyonu kullanarak Sayi haline çevirelim ki listemizi oluşturabilelim. 

Bu üç fonksiyon şu şekilde yazılmalıdır.

 

IF(ISNUMBER(SEARCH($L$4;$C$2:$C$48)); 1;0)

 

*$L$4:  Veri doğrulama yaptığımız hücre

**$C$2:$C$48: Listemizin bulunduğu alan.

 

Aradığımız değerin hangi kayıtların içerisinde olduğunu öğrendik. Var olanlara 1, olmayanlara 0 yazdık. Ancak bu veri dinamik listemizi oluşturmak çin yeterli değil. Bir liste oluşturacak kaç kaydın içerisinde bu veri var bunu bulmamız gerekir. Bize kayıt sayısı lazım. Hemen ufak bir numara ile if fonksiyonun içerisinde bir MAX() fonksiyonu kullanarak bu sayıların kimülatif bir şekilde artmasını sağlayabiliriz.  Böylece sayısı ile birlikte kayıtları bir alandan çekebiliriz. Fonksiyonun son hali;

 

IF(ISNUMBER(SEARCH($L$4;$C$2:$C$48));MAX($B$1:B1)+1;0)

 

*$B$1:B1: fonksiyon grubunu yazdığımız hücre.

 

 

i)Belirlenen Listeyi Ayıklamak

 

Listemizi belirledik. Veri doğrulama yaptığımız hücre içerisine yazmış olduğumuz veri hangi kayıtların içersinde var sayısı ile belirlendi. Şimdi sıra bu kayıtları almaya geldi.

Kayıtları almak için VLOOKUP() fonksiyonunu kullanacağız. Ancak bu fonksiyonu kullanırken, satır kayıtları belirlerken kullanmış olduğumuz max fonksiyonundan dönen değerler bizim aranan değerlerimiz olacak. Sonuçta ilk fonksiyon ile benzersiz sayılar da elde etmiş olduk. Aranan değer için de ROWS() Fonksiyonunu kullanacağız. Çağırdığımız alandan itibaren bize satır numarasını verir. Kaçıncı satırda çağırırsak çağıralım bize ilk değer olarak 1 döndürür. Bunun da kimülatif bir şekilde artmasını istediğimiz için, max fonksiyonunda kullandığımız yöntemi kullanacağız.

 

VLOOKUP(ROWS($E$2:E2);$B$2:$C$48;2;0)

*$E$2:E2: Vlookup fonksiyonunu çağırdığımız hücre.

*$B$2:$C$48: İlk fonksiyonun ve yanındaki listenin bulunduğu sütun aralığı.

 

Fonksiyonu yazdıktan sonra listemiz kadar aşağı çektiğimizde boş olan hücrelere yine #value hatası döndürecek. Bu hataları görmek istemediğimiz için Vlookup fonksyionunu IFERROR() içerisine koyalım. Fonksiyonun son hali;

 

IFERROR(VLOOKUP(ROWS($E$2:E2);$B$2:$C$48;2;0);"")   Şeklinde olacaktır.

 

ii)Dinamik Listeyi Data Validation İçerisine Yerleştirmek

 

Yeni listemizi elde ettik. Ücüncü aşama artık bu listeyi dizi halinde çekip, veri doğrulama içerisinde çağırabilmeye geldi. Excel’in çok amaçlı OFFSET() fonksiyonu ile bu işi halledebiliriz. Amaç her arama yaptığımızda değişen bu dinamik listeyi çekebilmek. Bunun için OFFSET() fonksiyonunu kullanacağız. 

 

OFFSET($E$2;;;COUNTIF(E:E;"?*"))

 

*$E$2 : Dinamik listemizin başladığı hücre

**Countif(E:E;"?*"): Bu sütunda herhangi bir veri içeren hücre sayısını döndürür.

 

Sonuç olarak fonksiyon, liste dizisi döndürecek duruma geldi. Bu fonksiyonu bir ad tanımlaması yaparak çağırabiliriz.

 

Formulas sekmesinde Defined Names alanında Name Manager iconuna bastığımızda karşımıza Ad tanımlama penceresi gelecek.  New dediğimizde bizden bir yeni bir ad isteyecek. İstediğimiz bir isimi verebiliriz. Pencerenin altında da bu ismin nereyi temsil edeceğini belirtecek Refers alanına, yazmış olduğumuz OFFSET() fonksiyonu yapıştırıp pencereyi kapayalım.

 

Elimizde var olan bir listeden, dinamik bir liste oluşturduk. Bu listeye de bir isim verdik. Son aşamada da oluşturduğumuz bu listeyi DV içerisinde çağırmaya geldi.

 

iii)Data Validation’da bu ismi çağırmak

Filtreleme özelliği olan bir Data Validation oluşturmak istediğimiz hücreyi tıklayarak, Yine Data -> Data Validation-> Settings -> List işlemini gerçekleştirip, kaynak olarak herhangir bir alan değil, OFFSET() fonksiyonu için tanımlamış olduğumuz ad’ı başına “=” eşittir yazarak yazıyoruz.

 

Son olarak da Data Validation-> Error Alert alanına girerek, geçersiz bir veri girdiğimiz zaman karşımıza çıkan hata uyarısını ortadan kaldıralım. Böylece Data Validation içerisine bir şey yazmak istediğimizde hem hata oluşmayacak hem de tanımlamış olduğumuz ad ile, yazmış olduğumuz veri listede hangi kayıtlarda var bize onları listeyecek.

 

Böylece Veri Doğrulama içerisinde Filtreleme işlemini gerçekleştirmiş olduk.

 

 

Kubilay TAŞTUTAR