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
Etiketler: , ,
  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.

  4. office 2010 da deniyorum, bu örneği hata almıyorum ancak bölge seçimi yapıldıktan sonra şehirler gelmiyor. ÖRNEK dosya da indirilemiyor.

    • Merhaba,

      Örnek dosyayı indirmeyi denediğimde bende bir hata vermedi. Siz bir hata alıyor musunuz?

      Bu tarz formüllerde, eğer formülü veri doğrulama kısmına yazarsanız, formülde oluşabilecek herhangi bir hatayı fark etme olasılığınız olmaz. Önce formülü hücre içine yazarak bir kontrol etmenizde fayda var.

      Ek olarak Excel’den buraya formülleri aktarırken tırnak işareti farklı bir karakter olarak geliyor. Bunları düzeltmeniz gerekebilir.

      Saygılarımla,

  5. Örnek dosya için buraya tıklayın lütfen. Bu dosyada Koşullu biçimlendirme….

    bu örnek dosyaya tıklandığında 404 hatası veriyor.

  6. Bu uygulama çok güzel bir uygulama olmasına rağmen bir kaç sorunu da beraberinde getiriyor. başlığının 3. maddesini aşağıdaki gibi bir makro ile çözüm bulunabilir.

    excel.web.tr de görmüştüm… Yusuf44 rumuzlu bir arkadaş eklemişti…

    Private Sub Worksheet_Change(ByVal Target As Range) ‘Yusuf44
    If Intersect(Target, [G3]) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = “”
    End Sub

    • Merhaba,

      Bu yöntem tabii ki mükemmel değil, ancak kıyaslandığında avantajları da var. Şahsen bu tarz işlemler için makro kullanmayı pek sevmiyorum, en büyük nedeni bazı işletmelerde makro güvenliği ayarları kullanıcıya bırakılmıyor ve tamamen engelleniyor. Bu durumda hazırladığınız sayfa tamamen işlevsiz kalıyor. Düzenleme gerektiğinde herkesin makro bilgisi olmadığından sıkıntı yaşanabiliyor.

      Bu nedenle eğer Excel formülleri ile yapılabilecek ise mutlaka formül ile yapmaya çalışın. Eğer formüller ile yapılamayacağı kesin ise makro kullanımını düşünün.

      Saygılarımla,

  7. Merhana
    Üstteki ders anlatım giflerini hangi programla yapıyorsunuz. Bir sürü cümleyle anlatmak yerine bu şekilde yapmak çok daha güzel olmuş. Gif yapmayı biliyorum ama photoshop ile… Buradaki gifler sanki video vb. şeklinde çekilim birleştirilmiş boyutlarıda çok değil. Öğretmenim öğrencilere ders anlatımı için kullanmak isterim.

    • Merhaba,

      Ekran görüntüsü almak için tamamen ücretsiz olan ScreentoGif programını kullanıyorum. Eğer ders anlatmak için kullanacaksanız ve uzun videolar çekecekseniz ekran kaydetme araçlarını kullanmanızı tavsiye ederim.

      Saygılarımla,

  8. Teşekkürler ScreentoGif programını indirip denedim. Güzel gerçekten. Uzun videolar çekmeyeceğim örneğin bir problemin çözümünü hızlıca göstermek vb. gibi..Bu programı bilmiyordum öğrenmiş olduk. Teşekkürler.

  9. Merhaba,

    Öncelikle bizlere sunduğunuz bilgiler için teşekkürler.

    Ek olarak sizin örnek çalışmanız üzerinden örneklendirirsek:
    1- listelerde illerin yanına plaka kodlarını yazdığımızı varsayalım
    2- bölgeyi, ili seçip plaka kodu verisini çekmek istiyorum.

    Kaçıncı ile bölgenin sütununu buluyorum, ancak kaçıncı formülü sonucu elde ettiğim sayıyı A:A gibi bir dizi yapıp, indis ile birlikte kullanamıyorum. Bu konuda yardımınızı rica ediyorum.

    • Merhaba,

      A:A adresini INDİS ile beraber kullandığınızda satırlardan oluşan bir dizi elde edersiniz. Burada KAÇINCI ile elde ettiğiniz rakamı İNDİS formülünde satır kısmında kullanmaya dikkat etmeniz gerek:

      =İNDİS(A:A;KAÇINCI(….);)

      gibi. Eğer satır yerine sütun kısmında kullanırsanız “#BAŞ!” hatası alırsınız.

      Eğer satır kısmında kullanmanıza rağmen işlev bir değer döndürmüyor ise “Formüller” sekmesinden “Formül Değerlendir” seçeneği ile hatanın tam olarak nerede olduğunu görebilirsiniz.

      Örnek dosya veya veri olmadığından ancak bunları tavsiye edebilirim.

      Ek olarak A:A şeklinde bir aralık kullanmanızı tavsiye etmiyorum. Çünkü Excel tüm aralığı dikkate alacağından bu tarz başvuruların çok kullanılması performans sorunlarına yol açabilir, döngüsel başvurulara sebep olabilir.

      Saygılarımla,

    • Örnek dosyada koşullu biçimlendirme kısmına girerseniz orada kullandığım formülü bulaiblirsiniz.

      • Merhaba, Dosyayı indirdim ama bende aynı hatada kırmızıyla uyarı vermiyor. Koşullu biçimlendirme kısmına girdiğimde de bir koşul göremiyorum. Bu nedenle sormuştum.

        • Merhaba,
          Değişken aralık ile birbirine bağlı liste sekmesindeki örnek dosyada var bahsettiğim yöntem. Diğer örnek dosyaya eklememişim.

          • Teşekkür ederim yardımlarınız için. Çok güzel bir çalışma olmuş, ikinci sekmeyi hiç kontrol etmedim daha da güzelmiş formüller 🙂 tekrar teşekkürler.

  10. Merhablar Elinize sağlık aynen uyguladım çalıştı. Rica etsem aşağıdaki şekilde bir düzenleme için yardımcı olur musunuz?
    Aynı yöntemi çok çeşitli gelir gider gurupları için kullanacağım. Formul tek hücrede çalışıyor fakat aşağı doğru çok fazla satır girişi lazım.
    Şöyleki satır satır veri giricelek her satır için ayrı ayrı yazma yerine aşağı doğru hücrelere kopyalamak mümkün müdür ?

    • Merhaba,

      İlk sekmede anlattığım yöntem ile Veri DOğrulama alanına girdiğiniz “=DOLAYLI(YERİNEKOY(D3;” “;””))” formülünde D3 göreceli başvuru. Veri doğrulama yapmak istediğiniz aralığı seçerek “=DOLAYLI(YERİNEKOY(C3;” “;””))” gibi bir formül girerseniz, C3 her satırda satır numarasına göre güncellenir, C4, C5… gibi. Bu durumda doğrulama aralığı olarak her satırda C sütununa denk gelen hücredeki değeri alarak ona göre bir liste oluşturur.

      İkinci sekmede anlattığım yöntem için Şehirler ismini “=KAYDIR(Listeler!$A$3;1;KAÇINCI(Form!$D4;Listeler!$3:$3;0)-1;BAĞ_DEĞ_DOLU_SAY(İNDİS(Listeler!$1:$1048576;0;KAÇINCI(Form!$D4;Listeler!$3:$3;0)))-1;1)” şeklinde güncellerseniz bölge adını yine satırdan alacağından her satırda farklı bir bölge kullanma imkanınız olur.

      Saygılarımla,

Leave a Reply