1. Amaç

Daha önce buradan ulaşabileceğiniz ve birbirine bağlı olarak içeriği değişen liste oluşturma için hazırladığım yöntemi, Excel 365 ve 2021 sürümlerinde gelen dinamik dizi işlevlerini kullanarak nasıl daha hızlı ve kolay yapabileceğimize değinmek istiyorum.

2. Uygulama

Buradan ilgili dosyayı indirebilirsiniz. Dosyada 2 sayfa var. İlk sayfada bağlı listelerin yer aldığı oldukça basit bir tablo var. İkinci sayfada ise tüm şehirler ve hangi bölgelerde yer aldığını gösterir bir liste var.

Ben burada daha rahat çalışabilmek adına bölge ve şehirlerin yer aldığı listeyi bir tablo haline getireceğim.İsim olarak ta BolgeSehir adını kullanacağım.

a. Bölge İsimleri Açılır Liste Oluşturma

Elimdeki tabloda bölge ve şehir isimleri olan bir tablo olduğundan bana benzersiz bölge isimlerinden oluşan bir liste lazım. Bunun için BENZERSİZ işlevini kullanacağım ve Listeler sayfasının E2 hücresine aşağıdaki formülü yazarak bu listeyi elde edeceğim:

=BENZERSİZ(BolgeSehir[Bölge])

Tablonun bölge alanında yer alan tüm benzersiz değerlerin olduğu bir liste oluştu. Eğer daha eski bir Excel sürümü kullanıyorsanız korkmanıza gerek yok buradaki yazımdaki yöntemi kullanara benzersiz değerleri çekerek bir liste elde edebilirsiniz. Ve elde ettiğiniz bu listeden değişken aralıklar kullanarak otomatik genişleyen bir isim oluşturabilirsiniz. 

Burada isteğe bağlı olarak elde ettiğimiz değerleri sıralayarak listemizin daha düzgün görünmesini de sağlayabiliriz. Bunun için de SIRALA işlevini kullanacağız.

=SIRALA(BENZERSİZ(BolgeSehir[Bölge]))

Şimdi Form sayfasındaki D3 hücresine seçip veri doğrulama penceresini açacağım ve Liste seçip Kaynak alanına aşağıdaki formülü yazacağım:

=Listeler!$E$3#

En sondaki “#” işaretinin dizi formüllerinde döndürülen listenin tamamını seçtiğini tekrar hatırlatayım. Bu sayede tabloya ne kadar değer eklersem ekleyeyim açılır listemde tüm değerler yer alacak.

Eğer eski Excel için bahsettiğim yöntemi kullanıyorsanız burada bölgeler için oluşturduğunuz değişken aralık ismini kullanmanız gerek

b. Seçilen Bölge İsmine Göre Şehirleri Süzmek

Form sayfasında seçilen bölgeye göre tablonun şehir kısmındaki verileri süzerek bir listeye dönüştürmem lazım. Bunu da FİLTRE işlevi ile yapabilirim. Tekrar Listeler sayfasına geçip F3 hücresine aşağıdaki formülü yazacağım:

=FİLTRE(BolgeSehir[Şehir];BolgeSehir[Bölge]=Form!$D$3;””)

Eğer Form sayfasında herhangi bir şehir seçilmedi ise bu durumda formül hata verecektir. formülün son parametresi herhangi bir değer bulunamaması durumunda boş bir değer gösterilmesini sağlıyor.

Burada da şehir isimlerini küçükten büyüğe sıralamak istersem bir önceli işlemde olduğu gibi SIRALA işlevi ile bu formülü birleştireceğim.

=SIRALA(FİLTRE(BolgeSehir[Şehir];BolgeSehir[Bölge]=Form!$D$3;””))

Bu elde ettiğim listeyi de Form sayfasının D4 hücresine veri doğrulama olarak gireceğim. Burada Kaynak alanına aşağıdaki ifadeyi giriyorum:

=Listeler!$G$3#

Artık elimde birbirine bağlı ve diğer yöntemle karşılaştırıldığında çok daha basit bir bağlı liste oluşturmuş oluyorum.

Eğer eski Excel sürümü kullanıyorsanız buradaki yazıma benzer bir yöntem ile aşağıdaki formülü oluşturabilir ve seçilen şehre göre tüm şehirleri bulabilirsiniz:

=EĞERHATA(İNDİS(BolgeSehir[Şehir];TOPLAMA(15;6;SATIR(BolgeSehir[Bölge])/ (BolgeSehir[Bölge]=Form!$D$3);SATIRSAY($I$3:$I3)););””)

Yine değişken aralık tanımlama ile bunu otomatik genişleyen bir aralığa dönüştürüp isim olarak tanımlamanız gerek. Ve veri doğrulamada kaynak kısmında bu ismi kullanmayı da unutmayın.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , ,

Leave a Reply