Dünya ekonomisine en çok katkı sağlayan ürün nedir diye sorsalar hiç düşünmeden Excel derim sanırım. Kesinlikle sınırsız bir program. Dün karar verdim artık zamanım olursa bloguma excel ile ilgili bildiğim/öğrendiğim ve benim çok işime yarayan özellikleri koyacağım. ilk olarak veri doğrulamada(data validation) en çok kullanılan listeleme özelliğini anlatayım dedim. Umarım işinize yarar

1-Basit Liste

Basit liste ile kastım bir hücrenin sadece belirli değerler alabilmesini kapsıyor ve bu belirli değerlerde sabit.

Basit bir örnek seçtim. Bir renk bilgisi almak istiyoruz(B2 hücresine) ve sadece Siyah,Beyaz,Kahverengi ve Mor seçeneklerine izin veriyoruz.

Veri doğrulamayı ayarlamadan önce hangi seçeneklerin olacağını bir yere listelemek gerekiyor. İlk örnekte F2:F5 arasına yazdım.

Değerleri belirttikten sonra Veri doğrulama adımından girip Listeyi seçtikten sonra kaynak olarak F2:F5 kısmını belirterek işlemi tamamlayabiliriz. (Eşit= işareti koymayı unutmayın. = koyunca yazılan bilgiyi değişken olarak görüyor. eğer = olmaz ise yazılan ne ise sadece onu anlar)

ilk kısım oldukça kolay. Sanırım anlaşılmıştır.

Birçok durumda F kolonuna yazdığımız gibi olası seçimleri kullanıcıya göstermek istemeyiz. Bunu yapmanın iki yolu var: kolay yolu F kolonunu gizlemek. biraz daha zor yolu ise başka bi Sayfa ya seçimleri yazmak.
Başka bi Sayfa(sheet) ya yazdığınızda, Veri doğrulama kısmından başka Sayfanın hücrelerini direk hedef olarak gösteremiyorsunuz(Yani =Sayfa2!F2:F5 girmenize izin vermiyor)
Başka sayfa verisini kullanmak için o veriyi seçip aşağıdaki gibi adlandırmak gerekiyor(Örnekte verileri seçtikten sonra sol üstten RenkListesi olarak isimlendirdim). Adlandırma işlemini yaptıktan sonra farklı sayfalarda =RenkListesi kullandığınızda biraz önce tanımlanmış alana ulaşabiliriz.

2- Bağımlı Veri Doğrulama

Bağımlı veri doğrulama daha çok ihtiyaç duyulan ama yapılmasıda biraz daha karışık gözüken bir durum. Bağımlı veri doğrulamada doğrulama işlemini başka bir hücredeki değere göre değiştiriyorsunuz.
Örnek olarak basit liste örneğindeki renklere alt renkler ekleyelim. Yani kullanıcı renk seçtikten sonra alt renk seçsin. Ana renk olarak Siyah seçerse sadece “Koyu Siyah”,”Açık Siyah” gibi alt renk seçebilsin(açık siyahta nasıl olacaksa )

Bu işlemi yapmak için 2 yöntem göstericeğim. Birincisini eskiden beri kullanıyordum, ikincisini ise yeni keşfettim çünkü 1. yöntem yapmak istediğimde işe yaramıyordu.

2.1 Bağımlı Veri Doğrulama – Değişken Adı kullanarak

Değişken oluşturmayı biraz önce basit liste yaparken kullanmıştık. Burdada benzer bir şekilde her alt renk seçimlerini ayrı ayrı değişken olarak adlandırmak gerekiyor.
Bunu yaparken dikkat edilmesi gereken bi nokta var. Adların belli bir formatı olması lazım. Ben altRenkAnaRenk olarak isimlendirdim AnaRenk kısmı ana rengin ismi ile değiştirilip yazılıyor. Yani altRenkSiyah, altRenkBeyaz … gibi.

isimlendirmeyi yukarıdaki gibi ayrı ayrı yaptıktan sonra, veri doğrulamayı aşağıdaki şekilde ayarlayabilirsiniz.

=DOLAYLI(”altRenk” & $B$2)

kısa açıklamak gerekirse : “altRenk” & $B$2 ile bir kelime oluşturuyor. Buda B2 nin değerine göre (yani ana renk) Beyaz, Siyah olabiliyor. yani totalde “altRenkBeyaz”, “altRenkSiyah” diye bir kelime oluşuyor. =Dolaylı() fonksiyonunu kullanarakta verilen kelimenin bir değişken olduğunu ve bu değişkenin değerini getirmesini istiyoruz. yani aslında bir hücreye B2 yazmak ile DOLAYLI(”B2″) yazmak aynı şeydir. Neyse konumuz dolaylı() değil.

2.2 Bağımlı Veri Doğrulama – Kaydır() fonksiyonunu kullanarak

Bu yöntemi geçen gün internette aradım ve buldum. Arama nedenim şuydu: değişken adı kullanarak veri doğrulama yapmak oldukça kolay. fakat bazı durumlarda ana seçim listeniz çok kalabalık olabiliyor. Mesela benim yapmak istediğim excelde araç markaları ve markalara göre modeller vardı. Buda 632 marka ve bu markaların 2600e yakın modeli olan bir listeden oluşuyor. Eğer ilk yöntemi kullanırsam benim bu 2600 model kaydını markalarına göre ayırıp 632 tane değişken oluşturmam gerekiyor(modelGrubuCitroen, modelGrubuBMW, modelGrubuMazda …) Buda tabiiki yapılcak iş değil.
Eğer böyle bir durumla karşılaşırsanız aşağıdaki gibi bir yöntem işinizi görür. Gene renk örneğinden gidersek, verimizi aşağıdaki gibi 2 kolon olarak hazırlıyoruz. sol tarafta ana renkler, sağ tarafta alt renkleri

Daha sonra sol kısmı komple değişken adı olarak(altRenkFiltre), sonrada sağ kısmı değişken adı olarak(altRenkler) oluşturun.
Daha sonra veri doğrulaması yapıcağımzı alana aşağıdaki kodu yazın :

Kod başlangıçta biraz karışık gelebilir ama anlatınca kolay.
Kaydır() fonksiyonu çoklu seçim yapılmış bir alandan istenen bölgeyi çağırmaya yarıyor. biz burda alan olarak altRenkleri seçtik yani tüm olası seçenekleri.
Daha sonra KAÇINCI() fonksiyonunu kullanarak ana rengimizin (B2) altRenkFiltre listesinde ilk kaçıncı satırda olduğunu buluyoruz(Bir çeşit search yapıyor ve B2 değerini bulur bulmaz kaçıncı satırda bulduğunu dönüyor).
Üçüncü parametre olan sütun a 0 olarak ilk sütunu dönmesini istiyoruz (zaten aslında altRenkler tek boyutlu)
Dördüncü paramterede ise kaç satır alıcağımızı istiyor bizde EĞERSAY() fonksiyonu ile B2 değerinden altRenkFiltrede kaç tane olduğunu bulduruyoruz.
Yani kısaca KAYDIR şunu yapıyor :
-Hangi verileri bakıcam
-Hangi satırdan sonra almaya başlıycam (KAÇINCI() fonksiyonu ile buluyoruz)
-Hangi sütunu alıcam (0 yani ilk sütun)
-Kaç satır birden alıcam (EĞERSAY ile kaç tane alması gerektiğini bulup veriyoruz.

Fonksiyonu parçalayıp hücrelere yazıp ne sonuçlar döndüğünü bularak daha rahat anlayabilirsiniz.

Umarım işinize yarar. kolay gelsin