Excel'de Bağımlı Açılır Liste Nasıl Yapılır?

Bağımlı açılır liste nasıl çalışır?

Bağımlı açılır listede ikinci listenin seçenekleri ilk listede seçilen değere göre değişir. Kategori hücresinde Sebze seçildiğinde Ürün hücresinde yalnız Domates, Biber ve Patlıcan görünür. Meyve seçilirse aynı hücre Elma, Armut, Muz ve Kiraz'ı gösterir.

Bunun en yaygın yolu adlandırılmış aralıklarla DOLAYLI'yı birleştirmektir. Her alt listeye üst seçenekle aynı adı verirsiniz, ikinci listenin kaynağı da ilk hücredeki metni aralık adına çevirir.

Alt listeleri hazırlamak

Her kategoriyi bir sütuna yazın: en üst satırda kategori adı, altında o kategorinin ürünleri. Meyve, Sebze ve Bakliyat başlıklı üç sütun bu düzenin basit bir örneğidir. Bütün sütunları başlıklarıyla birlikte seçin.

Ardından Formüller sekmesinin Tanımlı Adlar grubunda Seçimden Oluştur'a tıklayın ve yalnız Üst satır kutusunu işaretli bırakın. Excel her sütunu başlığındaki adla adlandırır: B2:B4 aralığı artık Sebze adını taşır. Oluşan adları aynı gruptaki Ad Yöneticisi'nden görebilirsiniz.

Alt listeleri ayrı bir sayfada tutmak, ana tabloyu sade bırakır. Adlar çalışma kitabının tamamında geçerli olduğu için liste başka sayfada dursa da DOLAYLI onları bulur. Sayfayı gizleyebilir, ama Ad Yöneticisi'nde adların doğru aralığa baktığını kontrol etmeyi unutmayın.

Ad oluştururken boş hücreler de aralığa girer. Meyve sütunu dört, Bakliyat iki ürün içeriyorsa ve dört satırı birlikte seçtiyseniz Bakliyat adı iki boş hücreyi de kapsar. Açılır listede boş satırlar görmemek için her sütunu kendi uzunluğunda ayrı ayrı adlandırabilirsiniz.

İki listeyi birbirine bağlamak

İlk hücreye normal bir açılır liste ekleyin; kaynağı kategori başlıklarının bulunduğu satırdır. İkinci hücrede Veri Doğrulama'yı açıp İzin Ver'de Liste'yi seçin ve Kaynak kutusuna =DOLAYLI($E2) yazın. DOLAYLI, E2'deki "Sebze" metnini aynı adlı aralığa çevirir.

Formüldeki dolar işareti yalnız sütunu sabitler. Bu ayarı Ürün sütununun tamamına uyguladığınızda her satır kendi kategori hücresine bakar. Örneğin 50 satırlık bir sipariş formunda E2:E51 kategori, F2:F51 ürün sütunuysa doğrulamayı F2:F51'i seçerek bir kerede kurarsınız; F30'daki liste E30'daki kategoriye göre açılır. İlk hücre henüz boşken Excel kaynağın hata verdiğini bildirebilir; Evet diyerek devam edebilirsiniz.

Adlandırma kurallarındaki tuzak

Aralık adlarında boşluk bulunamaz. "Ev Aletleri" başlığından Seçimden Oluştur, "Ev_Aletleri" adını üretir. İlk listede "Ev Aletleri" seçildiğinde DOLAYLI bu adı bulamaz ve ikinci liste açılmaz. Çözüm kaynağı =DOLAYLI(YERİNEKOY($E2;" ";"_")) yapmaktır.

Adlar sayıyla da başlayamaz. "2026 Modelleri" gibi bir başlık kullanacaksanız kategori adını harfle başlayacak şekilde değiştirin.

Ad tanımlamadan formülle bağımlı liste

Kategori sayısı çoksa her biri için ad tanımlamak zahmetlidir. KAÇINCI ile KAYDIR'ı birleştirerek kaynak aralığı formülle de üretebilirsiniz. İlk adım, seçilen kategorinin kaçıncı sütunda olduğunu bulmaktır.

=KAÇINCI(E2;A1:C1;0)

=MATCH(E2,A1:C1,0)

KAYDIR bu sütun numarasıyla A2'den sağa kayar ve o sütundaki ürün aralığını döndürür. BAĞ_DEĞ_DOLU_SAY ile sarmalandığında formülün doğru sütunu bulduğunu görürsünüz: Sebze sütununda üç ürün vardır.

=BAĞ_DEĞ_DOLU_SAY(KAYDIR(A2;0;KAÇINCI(E2;A1:C1;0)-1;4;1))

=COUNTA(OFFSET(A2,0,MATCH(E2,A1:C1,0)-1,4,1))

Kaynak kutusuna yazacağınız formül, yüksekliği bu sayıyla ayarlanmış KAYDIR'dır: =KAYDIR($A$2;0;KAÇINCI($E2;$A$1:$C$1;0)-1;BAĞ_DEĞ_DOLU_SAY(KAYDIR($A$2;0;KAÇINCI($E2;$A$1:$C$1;0)-1;10;1));1). Uzun görünse de yeni bir kategori sütunu eklediğinizde yalnız başlık aralığını genişletmeniz yeter. İNDİS aynı aralığın ilk öğesini göstererek formülü test etmenize yardım eder.

=İNDİS(A2:C5;1;KAÇINCI(E2;A1:C1;0))

=INDEX(A2:C5,1,MATCH(E2,A1:C1,0))

Üç seviyeli listeye genişletmek

Aynı yöntem ikinci bir bağımlılık için de çalışır. İl seçildikten sonra ilçe, ilçe seçildikten sonra mahalle listesi açılabilir. Her seviyede alt liste, bir üst seviyede seçilecek değerle aynı adı taşır.

Seviye sayısı arttıkça ad sayısı hızla büyür. 10 il ve her ilde 15 ilçe varsa yalnız ilçeler için 10 ad, mahalleler için 150 ad gerekir. Bu ölçekte listeleri tek bir uzun tabloda tutup KAYDIR ya da Microsoft 365'teki FİLTRE fonksiyonuyla aralık üretmek daha yönetilebilirdir.

Kategori değişince eski seçim kalır

Bağımlı listenin bilinen bir eksiği vardır. Önce Sebze ve Domates seçip sonra kategoriyi Meyve yaparsanız Ürün hücresinde Domates kalmaya devam eder. Veri doğrulama yalnız yeni girişi denetler, var olan değeri silmez.

Bu tutarsızlığı yakalamak için yan sütuna =EĞERSAY(DOLAYLI($E2);F2)>0 gibi bir kontrol formülü koyabilir ya da koşullu biçimlendirmeyle uyumsuz satırları renklendirebilirsiniz. Şimdi kendi kategori listenizi sütunlara yazın, Seçimden Oluştur ile adlandırın ve ikinci hücreye =DOLAYLI($E2) kaynağını bağlayın.