Excel'de birbirine bağlı açılır listeler nasıl yapılır?

Bağımlı açılır liste nedir, ne işe yarar?

Bağımlı (kademeli) açılır liste, bir hücredeki seçime göre bağlı hücredeki seçeneklerin değiştiği veri girişi düzenidir. Klasik örnek il ve ilçedir: kullanıcı ilk hücrede bir il seçince bağlı hücrede yalnızca o ile ait ilçeler görünür. Aynı mantık departman ve çalışan, ürün grubu ve ürün, bölge ve satış temsilcisi gibi ilişkilerde de kullanılır.

Amaç tutarlı veri girişidir. Serbest metin yazılan alanlarda aynı değer farklı yazımlarla girilir ve sonradan pivot tablo ya da filtre bu kayıtları ayrı sayar. Bağımlı liste hem seçenek sayısını daraltıp hızlı giriş sağlar hem de geçersiz kombinasyonları (başka bir ile ait ilçe gibi) baştan engeller.

Klasik yöntem: ad tanımlama ve DOLAYLI işlevi

Bu klasik yöntemin mantığı, listeleri kategori adıyla adlandırılmış aralıklar olarak tanımlayıp bağlı listenin kaynağını DOLAYLI işleviyle ilk hücrenin değerine bağlamaktır. Adımlar: • Bir kaynak sayfa hazırlayın. Her ana kategoriye ayrı sütun açın; sütunun başlığı kategorinin adı, altı ise alt seçeneklerdir. • Her sütunun alt değerlerini seçip adını, başlıktaki kategori adıyla tamamen aynı olacak şekilde tanımlayın. Ana kategori listesi için de ayrı ad verin. • Ana veri girişi hücresine Veri sekmesindeki Veri Doğrulama’yı uygulayın: İzin ver olarak Liste, kaynak olarak ana kategori adı. • Bağımlı hücreye de Veri Doğrulama uygulayın: kaynak alanına DOLAYLI işlevini yazıp ilk hücreyi başvuru olarak verin.

DOLAYLI, ilk hücredeki metni aralık adı olarak yorumlar ve o adın karşılığı aralığı kaynak yapar. İl hücresinde bir şehir seçildiğinde, aynı adı taşıyan aralık ilçe listesi olarak açılır.

Bu yöntem hızlı kurulur, ancak ad tanımları ile kategori yazımları eşleşmek zorundadır. Listeyi sonradan değiştirdiğinizde ad aralığının da güncellendiğinden emin olmalısınız.

Boşluk, rakam ve özel karakter sorunu

Ad tanımlarında boşluk bulunamaz ve bir ad rakamla başlayamaz. Kategori değerleriniz “Orta Anadolu” gibi boşluklu ya da rakamla başlayan ifadelerse DOLAYLI, böyle bir ad bulamaz ve bağımlı liste hata verir.

Çözüm olarak kategori adlarını kaynak listede alt çizgili yazabilirsiniz; ancak kullanıcıya alt çizgili metin göstermek hoş değildir. Daha iyi bir seçenek, ad aralıklarını alt çizgiyle tanımlayıp DOLAYLI içindeki başvuruyu bir metin işleviyle sarmaktır: seçilen değerdeki boşlukları alt çizgiye çeviren işlev önce çalışır, sonucu DOLAYLI okur. Böylece kullanıcı boşluklu metni seçer, formül arka planda alt çizgili adı bulur.

Özel karakterlerde de aynı mantık geçerlidir. Tire, nokta ve parantez gibi karakterler ad tanımında sorun çıkarabilir; kategorileri kod ya da kısa ad ile eşleştirip kullanıcıya açık adı ayrı bir sütundan göstermek, sağlam bir yaklaşımdır.

Alternatif yöntem: tablo ve dinamik dizi işlevleri

Excel’in dinamik dizi işlevlerini destekleyen sürümlerinde ad tanımı gerektirmeyen bir yöntem kurabilirsiniz. Tüm kombinasyonlar tek bir Excel tablosunda tutulur: bir sütunda kategori, yanındaki sütunda alt seçenek. Ana liste için benzersiz değerleri çıkaran dinamik dizi işlevi kullanılır; sonuç taşan bir aralık olarak yayılır. Bağımlı liste için FİLTRE işlevi, kategorisi ilk hücreyle eşleşen alt seçenekleri döndürür.

Veri Doğrulama’nın kaynak kutusuna bu taşan alanın başvurusu, hücre adresinin sonuna taşma işareti eklenerek verilir. Böylece liste uzayıp kısaldıkça açılır menü de kendiliğinden değişir; yeni kayıt eklendiğinde ad tanımlarını elle güncellemek gerekmez.

Bu yöntemin avantajı bakımının kolay olmasıdır: veri tek bir tabloda yaşar, yeni kategori eklemek bir satır eklemek kadar basittir. Dezavantajı, eski Excel sürümlerinde çalışmamasıdır; dosyayı paylaşacağınız kişilerin sürümünü önceden kontrol edin.

Zincirleme seviyeler ve eski seçimin kalması

Aynı mantık daha derin seviyelere genişletilebilir: il, ilçe, mahalle gibi. Her seviye bir önceki hücreyi başvuru alır. Ad tanımlı yöntemde her ilçe için ayrı bir ad aralığı gerekeceğinden dosya hızla kalabalıklaşır; bu durumda dinamik dizi yöntemi çok daha yönetilebilir kalır.

Bağımlı listelerin bilinen bir zayıflığı vardır: kullanıcı ilk hücrede il değiştirdiğinde bağlı hücredeki eski ilçe seçimi silinmez ve geçersiz bir kombinasyon (yeni ilde bulunmayan eski ilçe) kayıt altında kalabilir. Çözüm yolları şunlardır: • Hücre değişiminde bağımlı hücreyi temizleyen küçük bir makro yazmak. Bu, Makro Programlama konusudur ve dosyanın makro destekli kaydedilmesini gerektirir. • Ek bir kontrol sütunuyla geçerli kombinasyonu denetlemek: eşleşme tablosunda kombinasyonu arayıp bulunmuyorsa koşullu biçimlendirmeyle satırı kırmızıya boyamak.

Her ikisi de uygulanmazsa veri girişi sonrası tutarsızlık kontrolü ayrıca yapılmalıdır.

Sık yapılan hatalar

• Ana hücre boşken bağımlı hücre hatası: ilk hücre boşsa DOLAYLI bir ad bulamaz. Hata uyarısı sekmesinde boş hücreleri yoksay seçeneğini ve kullanıcıya açıklayıcı bir mesaj tanımlayın.
• Ad ile başlık yazımının uyuşmaması: bir harf, bir boşluk ya da büyük küçük harf farkı bağlantıyı koparır. Listeleri kaynak sayfada tek yerde tutun, kopyalayıp yapıştırın.
• Kaynak listeyi başka çalışma sayfasında unutmak: kaynak sayfayı gizleyip korumaya almak iyi bir pratiktir ancak kendiniz de erişim yolunu belgeleyin.
• Yapıştırmayla doğrulamayı silmek: bir hücreye değer yapıştırmak Veri Doğrulama kuralını ezer. Girişe açık alanda yapıştırmayı engelleyen bir koruma ya da periyodik kontrol gerekir.
• Yeni kategori eklemeyi unutmak: ad tanımlı yöntemde ana listeye de aralık eklemek gerekir.
• Tüm kategorileri denemeden yayına almak: dosyayı dağıtmadan önce her kategoriyi tek tek seçip bağlı listeyi kontrol edin.

Giriş ekranı bir kez kurulduktan sonra veri sayfasındaki liste kaynaklarını bakım sorumlusu tek kişiye bağlayın; birden fazla kişi düzenlerse yazım farkları kaçınılmaz olur.

Bu beceri Mindset programlarında nerede çalışılır?

Excel Orta Düzey programının Veri Doğrulama ve Koşullu Biçimlendirme modülü, açılır liste ve geçerlilik kurallarının temelini verir; İleri Arama ve Basit Referans Formülleri modülü ise başvuru mantığını destekler. Veri giriş formu hazırlayan ofis ve operasyon ekipleri için doğrudan ilgilidir.

Microsoft Excel ile Veri Analizi Eğitimi (İleri Excel) programı, Veri Analizi ve İleri Formüller, İleri Seviye İşlevler ve İleri Tablo Yönetimi ve Makro Programlama ve Otomasyon başlıklarıyla dinamik dizi tabanlı kaynakları ve seçim temizleme makrosunu kapsar. Ortak veri giriş şablonu standartlaştırmak isteyen kurumsal ekipler bu seviyeleri birlikte değerlendirebilir.