Bir çok insanın karşısına çıkan en büyük sorunlardan bir tanesi bir formda birbiri ile ilişkili iki veya daha fazla liste kullanılması ihtiyacı. Yani bir listede seçilen değere göre başka bir listede farklı seçenekler gösterilmesi. Bunun için basit ancak güncelleme durumunda müdahale isteyen bir yöntem ve biraz daha karmaşık ama çok daha az müdahale isteyen bir yöntem mevcut. İkisini de aşağıdaki sekmelerde bulabilirsiniz.

Bazı formlarda birbirine bağlı listeler oluşturmak gerekebilir. Bunu Veri Doğrulama ile nasıl yapabiliriz? Şimdi oldukça basit olan bu uygulamaya başlayalım sonrasından onu biraz daha ileri seviyeye taşıyacağız. Tabii taşırken de bayaa eğleneceğiz. Şimdi isterseniz birbirine bağlı olmasını istediğimiz listeleri hazırlayalım. Ben bu listeleri genelde ayrı bir sayfada hazırlıyorum. Bu sayede ana formum görünmesini istemediğim verileri barındırmadığından daha temiz kalıyor. Bir tür 5S. Neyse aşağıdaki tabloyu kopyalayarak Excel’de istediğiniz sayfaya kopyalayın.

BölgelerAkdenizDoğu AnadoluEge
AkdenizAntalyaMalatyaİzmir
Doğu AnadoluBurdurErzincanAydın
EgeIspartaElazığMuğla
MersinTunceliManisa
AdanaBingölDenizli
HatayErzurumUşak
OsmaniyeMuşKütahya
KahramanmaraşBitlisAfyon
Kars
Ağrı
Ardahan
Van
Iğdır
Hakkari

Ben bu tabloyu Listeler adlı ayrı bir sayfada oluşturdum. Şimdi de isimleri tanımlayalım. Sırası ile Bölgeler, Akdeniz, DoğuAnadolu (Excel isimlerde boşluk karakteri kullanılmasına izin vermez) ve Ege adlı isimleri tanımladım. Sonrasında aşağıdaki gibi bir tablo oluşturalım.


Veri Doğrulama Bağlantılı Listeler Örneği

Şimdi buradaki örnekte gözüken D3 hücresine tıklayın ve veri doğrulama ekranını açarak Liste seçeneğini seçin. Sonrasında Kaynak kısmına gelip F3 tuşuna basarak gelen pencereden Bölgeler ismini seçin. Burada az önce parantez içinde belirttiği bir sorun ortaya çıkıyor. Bölgeler listesinde “Doğu Anadolu” var ancak isimler içinde “Doğu Anadolu” ismini oluşturmamıza Excel izin vermemişti. Buna çok dikkat edin. Sonraki adımda bunu çözecek bir yöntem kullanmamız gerek. Şimdi D4 hücresine gelin, yine Veri Doğrulama ekranından listeyi seçin ve kaynak kısmına aşağıdaki formülü yazın. Burada çok kısa bir bilgi vereyim; Excel Veri Doğrulama penceresi içinde yer alan kutucuklara formül yazılması esnasında otomatik tamamlama yapmaz. Bu nedenle ben genelde bu formülleri boş bir hücreye yazdıktan sonra kopyalayarak bu alanlara yapıştırırım:

=DOLAYLI(YERİNEKOY($D$3;” “;””))

Burada YERİNEKOY formülünü daha önceki metin formülleri yazımda anlatmıştım. DOLAYLI ise oldukça basit bir formül burada hızlıca değinelim. DOLAYLI formülü yazdığınız metni metin olarak değil adres veya isim olarak ele alır. Yani burada D3 hücresinde yer alan metin içinde varsa boşluklar kaldırılır. Sonrasında elde edilen metin için bir hüücre adresi, hücre aralığı;veya isim var mı kontrol edilir. Var ise bulunan alan geri döndürülür. Tabii bizim D3 hücremizde veri olmadığından burada Excel girilen formülün hata döndürdüğünü söyleyecek, sorun yok “Evet” tuşuna basarak bu pencereyi kapatalım. Zaten biz D3 hücresine sonradan veri gireceğiz.

Şimdi D3 hücresine gelelim ve yandaki aşağı ok düğmesine basarak bir bölge seçelim. Sonra da D4 hücresine gelerek listeyi açalım ve Şehirleri görelim. Eğer D3 hücresine gelip seçili bölgeyi değiştirirsek alttaki hücredeki liste de kendini güncelleyecektir. Aşağıda kısa bir video ile işlem adımlarını gösterdim.


Bağımlı Listede Değişken Aralık İçin İşlem Adımları

Bu uygulama çok güzel bir uygulama olmasına rağmen bir kaç sorunu da beraberinde getiriyor.

  1. Bölge isimlerinin olduğu liste statik. Bu durumda yeni bir bölge eklediğimizde bu bölgeye ait şehir listesini ve listeyi gösterecek ismi de el ile oluşturmamız lazım.
  2. Bölgelerdeki şehir isimlerinin yer aldığı isimler de sabit. Yani herhangi bu listelere veri eklediğimizde ilgili ismin gösterdiği alanı da el ile güncellememiz gerek.
  3. Form sayfasında Bölge seçilip ardından şehir seçtiğimizde sonradan bölgeyi değiştirdiğinizde daha önce seçilmiş olan şehir bilgisi silinmez ek olarak Excel hata da vermez. Bu ise hatalı bir sonuca yol açar

Şimdi bir sonraki örneklerde ilk iki sorunun çözümünü bulabilirsiniz. Ancak 3. maddedeki sorun için kesin bir çözüm yok. Ben burada veri doğrulama ile hatalı hücre arka planını kırmızı yaparak kullanıcının dikkatini bu alana çekmeyi sağlıyorum.

Bu uygulama için kullanabileceğiniz örnek dosyaya buradan indirebilirsiniz.

Bu örnekte değişken aralık ve bu aralıkta yer alan seçeneklere göre başka bir aralıktaki değerleri birbirine bağlı olarak iki farklı hücrede liste halinde göstermenin nasıl yapılacağını anlatacağım. Bu ders için daha önceden gösgtermiş olduğum değişken aralık formüllerini kullanacağım. Bu yüzden henüz bu yazımı okumadı iseniz lütfen buraya tıklayarak önce o yazıyı okuyun.

Önceki örnekten farklı olarak 2 tane isim tanımlayacağız. İlk isim bölgelerin olduğu ana liste. İkincisi ise ana listeden seçilen değere göre otomatik olarak ilgili aralığı bularak geri döndüren isim. İlk listedeki değere göre arama yapılacağı ve değişken bir aralık olacağı için bu formül biraz karışık bir formül olacak. O yüzden çok dikkatli olun.

Anlatacağım formüllerde yer alan Sayfa1’i formun yer aldığı sayfa adi, sayfa2’yi ise listelerin yer aldığı sayfa adı ile değiştirmeyi de sakın unutmayın.

İlk formülümüz çok kolay. zaten daha önce yapmış olduğumuz bir örnekti. Benim bölgeler listem B3’ten başlıyor:

=KAYDIR(‘Sayfa2’!$B$3;1;0;BAĞ_DEĞ_DOLU_SAY(‘Sayfa2’!$B:$B)-1;1)

Bu formülü isim tanımlama penceresinde oluşturacağımız Bölgeler adlı ismin Başvuru Yeri kutusuna yazalım.

Ne yapmak istediğimi kısaca anlatayım. Öncelikle kullanıcı menüdeki ilk açılır pencereden bir seçenek seçecek. Bu seçeneği ben listelerimin yer aldığı 2. sayfada aratıp hangi sütunda yer aldığını bulacağım, sonra bu sütunda toplamda kaç tane satır olduğunu hesaplayıp bir aralık bulacağım, en son bulduğum bu değerleri bir KAYDIR formülünde yerlerine yazacağım ki o aralık otomatik olarak oluşsun Aşağıdaki resimde ne yapmak istediğimi kısaca anlatmaya çalıştım.

Bağımlı Listede Değişken Aralık İçin İşlem Adımları

Örneği yapmak için önceki sayfada vermiş olduğum tabloyu kopyalayın lütfen. Sonrasında ilk formülümüz olan aranan değeri bir satır boyunca bulma formülümüzü yazalım (Kaçıncı formülü için buraya bakabilirsiniz):

=KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)

Benim örnek dosyamda “Doğu Anadolu” F sütununda yani 6. sütunda yer aldığından 6 rakamını geri döndürüyor. Sonrasında 6. sütunun adresini elde etmemiz gerek. Bunu İNDİS formülü ile yapabiliriz (İndis formülü burada anlatılıyor):

=İNDIS(‘Sayfa2’!$A:$XFD;0;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0))

Bu formül ile sayfanın 6. sütununu tamamen geri döndürdük. Aslında burada başka ve fakat çok daha karmaşık bir formül daha kullanabiliriz ancak İNDİS çok daha kısa ve anlaşılır bir formül. Sonrasında bu sütunda kaç tane satıra veri girildiğini bulacağız (Bağ_Değ_Dolu_Say formülünü burada bulabilirsiniz):

=BAĞ_DEĞ_DOLU_SAY(İNDIS(‘Sayfa2’!$A:$XFD;0;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)))

Başlık satırı ile beraber 16 adet satır var. Şimdi bu bulduklarımızı kaydır formülüne koyalım (Kaydır formülünü bu yazımda anlatmıştım):

=KAYDIR(‘Sayfa2’!$A$3;1;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)-1;
BAĞ_DEĞ_DOLU_SAY(İNDIS(‘Sayfa2’!$A:$XFD;0;KAÇINCI(‘Sayfa1′!$D$3;’Sayfa2’!$3:$3;0)))-1;1)

Çok uzun bir formül oldu. Kısaca yaptığımız Listelerin yer aldığı 2. sayfadaki A3 hücresini alıyoruz, başlık nedeni ile bir satır aşağıya kaydırıyoruz, form sayfasında aradığımız değer olan Doğu Anadolu ifadesini bulduğumuz sütun sayısının bir eksiği kadar kaydırıyoruz ve bu sütunda bulunan dolu hücre sayısı kadar yükseklikte bir aralığı, başlık satırını çıkarak geri döndürüyoruz.

“Şehirler” adında yeni bir isim tanımlayarak “Başvuru Yeri” kutusuna bu formülü yapıştıralım. Artık listemize yeni elemanlar eklediğimizde sürekli yeni isim oluşturmak durumunda kalmayacağımız gibi tek yapmamız gereken ana listemize eklenmiş yeni madde ile aynı ada sahip yeni bir listeyi oluşturmak olacaktır. Bu formül ile alt listelere de istediğimiz kadar ekleme de yapabiliriz. Yani dışarıdan ek bir müdahale gerektirmeyen hazırla ve unut tarzı formlar oluşturmuş oluruz.

Burada tek bir sıkıntı olabilir o da 16.384 adetten fazla liste ihtiyacıdır. Ancak bunun pek olası olmadığını düşünüyorum.

Örnek dosya için buraya tıklayın lütfen. Bu dosyada Koşullu biçimlendirme ile bir önceki sekmede anlatmış olduğum 3. sorun için çözümü de ekledim. Şehir hücresine tıklayarak koşullu biçimlendirme ile ilgili formülü görebilirsiniz. Aşağıda konu ile ilgili bir uygulama örneğinin videosunu da ekliyorum.


Bağımlı Listede Değişken Aralık İçin İşlem Adımları
İlginizi Çekebilecek Diğer Yazılar
  1. Merhaba,
    Diyelim bir sayfada 5 farklı bölgeden gelen üretim verilerinden önemli gördüklerimi içeren günlük özet bir tablom var. Ayrı bir data sayfasında ben her bir bölge için gelen günlük tüm verileri tek tek işliyorum. Yapmak istediğim ilk sayfada veri doğrulama ile oluşturduğum listeden ilgili tarihi seçtiğim zaman, bana adreslediğim günlük verileri data sayfasından çekip ilgili hücrelere yazan bir uygulama yapmak istiyorum. Bunu kaydır komutu ya da başka bir komut ile en kolay şekilde nasıl yapacağımı bir türlü çözemedim. Özetle tablomdaki verilerin seçtiğim güne göre gösterilmesini istiyorum.

    • Özet tablo derken tam olarak neyi kastediyorsunuz? Excel’in özet tablosunu mu yoksa birden fazla kaynaktan alınarak birleştirilmiş bir tablo mu? Eğer çoklu kaynaklardan gelen bilgilerin toplandığı bir tablo ise Excel Özet Tablo kullanarak bu tarz verileri hızlıca çekebilirsiniz. Yok zaten bir özet tablo var ise bu durumda Özetverial formülü ile özet tabloda yer alan verileri başka bir tabloya taşıyabilirsiniz. Ancak özet tablodaki farklı veriler her zaman aynı adette olmayacağından bu tarz bir formül ile oluşturulan tabloların kontrol edilerek tüm değerleri içerecek şekilde güncellenmesi gereklidir.
      Bir başka yöntem ise Power Query kullanmaktır. Excel 2010’a ek bir yükleme ile eklenebilen bu özellik ile tablolar içinde sorgular yaparak sonuçları başka tablolara aktarma işlemi yapılabilir.

  2. Hocam merhabalar, sizlere bir sorum olacak bu anlattığınız listeyi excell de uyguladım ancak google e- tablolarda uygulayamıyorum. Bu konu hakkında bilginiz var mı?

    • Merhaba,
      Google sheets programı üzerine fazla bir bilgim yok. Excel ile aynı olan komutları var ancak çalışma mantığı biraz daha farklı olabiliyor. O yüzden yardımcı olamıyorum.

  3. Erol Bey merhaba,

    Çalışmanız çok faydalı olmuş emeğinize sağlık. Bir sorum olacak. Aynı sütunda tekrarlanan verileri combobox’a getirmemesi için ne yapmamız gerekiyor ? Makro olmadan bir yolu var mıdır ?

    Başarılarınızın devamını dilerim.

    • Merhaba,

      Önce bir formül ile tekrarlanan verileri içermeyen bir liste oluşturmanız gerekir. Sonrasında bu yeni listeyi combobox’un input range özelliğine girmeniz gerek. Tabii bu eşsiz değerleri farklı bir sütunda tutmanız gerek. Eşsiz değerleri tuttuğunuz bu listeyi bir değişken ad olarak tanımlayıp bu değişken adı da combobox’un input range özelliğine tanıtmanız gerek. Bu bahsettiğim VBA’daki combobox. Eğer hücre içinde açılan listeden bahsediyorsanız bu bahsettiğim aralığı veri doğrulama da kullanmanız gerek.

Leave a Reply