Excel'de Dinamik Açılır Liste Nasıl Yapılır?

Sabit açılır liste neden yetmez?

Veri doğrulamayla kurulan açılır liste, kaynak olarak verdiğiniz aralığa bağlı kalır. Kaynağı A2:A10 olarak tanımladıysanız 11. satıra eklenen kategori listede görünmez. Kaynak sütunda aynı değer birden çok kez geçiyorsa listede de tekrar eder.

Dinamik açılır liste bu iki sorunu birlikte çözer: kaynak büyüdükçe genişler ve tekrarları göstermez. Örneklerde sekiz ürün ve dört kategori içeren bir ürün listesi kullanılıyor. Amaç, ürün girerken kategori hücresinde yalnız bu dört seçeneği göstermek ve yeni bir kategori eklendiğinde listeyi elle güncellememektir.

Kurulum üç parçadan oluşur: tekrarsız kaynak listeyi üreten formül, bu listeye bağlanan veri doğrulama ve gerekirse eski sürümler için bir alternatif.

Tekrarsız ve sıralı kaynak listeyi üretmek

İlk adım, açılır listenin okuyacağı temiz bir kaynak oluşturmaktır. Microsoft 365 ve Excel 2021'de BENZERSİZ tekrarları atar, SIRALA da sonucu alfabetik dizer. Formülü boş bir sütunun ilk hücresine, örneğin D2'ye yazarsınız.

=SIRALA(BENZERSİZ(B2:B9))

=SORT(UNIQUE(B2:B9))

Kaynak aralığı baştan geniş tutun: B2:B100 gibi bir aralıkta boş hücreler de olacağı için BENZERSİZ boşluğu da bir değer sayar ve sıfır gösterir. Bunu önlemek için =SIRALA(BENZERSİZ(FİLTRE(B2:B100;B2:B100<>""))) yazımını kullanın.

Listenin kaç seçenek içerdiğini görmek isterseniz SATIRSAY işinizi görür. Bu sayı, kaynağa yeni kategori eklendiğinde büyür.

=SATIRSAY(BENZERSİZ(B2:B9))

=ROWS(UNIQUE(B2:B9))

Veri doğrulamada yayılma başvurusu

Açılır listeyi koyacağınız hücreyi seçip Veri sekmesindeki Veri Doğrulama komutunu açın. Ayarlar sekmesinde izin verilen tür olarak Liste'yi seçin. Kaynak kutusuna =$D$2# yazın ve Tamam'a basın.

Sondaki # işareti, D2'den başlayan yayılma alanının tamamı anlamına gelir. Kaynağa yeni bir kategori eklendiğinde BENZERSİZ'in sonucu bir satır uzar, açılır liste de bu satırı kendiliğinden gösterir. Aralığı D2:D20 gibi sabit yazmak bu avantajı ortadan kaldırır.

Yardımcı listeyi görünür bırakmak istemiyorsanız onu ayrı bir sayfaya taşıyabilirsiniz. Kaynak kutusuna bu durumda =Listeler!$A$2# gibi sayfa adını içeren bir başvuru yazılır.

Eski sürümlerde KAYDIR ile dinamik aralık

Dinamik dizi desteği olmayan Excel 2019 ve öncesinde aralığı KAYDIR ile genişletirsiniz. KAYDIR, başlangıç hücresinden itibaren yüksekliği dolu hücre sayısı kadar olan bir aralık döndürür. Kategoriler B sütunundaysa kaynak kutusuna =KAYDIR($B$2;0;0;BAĞ_DEĞ_DOLU_SAY($B:$B)-1;1) yazılır; eksi 1, başlık hücresini sayımdan çıkarır.

=SATIRSAY(KAYDIR(B2;0;0;BAĞ_DEĞ_DOLU_SAY(B2:B50);1))

=ROWS(OFFSET(B2,0,0,COUNTA(B2:B50),1))

KAYDIR'ın argümanlarını sırayla okumak formülü anlaşılır kılar. İlk argüman başlangıç hücresidir. Sonraki iki sıfır, aralığın bu hücreden kaydırılmadığını gösterir. Dördüncü argüman yükseklik, beşinci argüman genişliktir ve liste tek sütun olduğu için 1 yazılır.

Bu yöntem kaynakta boş hücre olmadığında doğru çalışır ve tekrarları ayıklamaz. Kaynak sütunu zaten tekrarsız bir kategori listesiyse eski sürümler için yeterlidir.

Seçime bağlı ikinci liste

Dinamik dizilerle kurulan listeler, birinci listedeki seçime göre değişen ikinci bir listeyi de kolaylaştırır. G2'de kategori seçildiğini düşünün. Boş bir hücreye, örneğin H2'ye =SIRALA(FİLTRE(A2:A100;B2:B100=G2)) yazdığınızda yalnız o kategorinin ürünleri listelenir.

Ürün hücresinin veri doğrulama kaynağına =$H$2# yazmanız yeterlidir. Kullanıcı G2'de Kırtasiye'yi seçtiğinde ürün listesinde defter ve kalem, Elektronik'i seçtiğinde kulaklık ve mouse görünür.

G2 boşken FİLTRE #HESAPLA! hatası verir ve açılır liste boş kalır. FİLTRE'nin üçüncü argümanına "Önce kategori seçin" gibi bir metin yazarak kullanıcıya ne yapacağını gösterebilirsiniz.

Excel tablosuyla kurulan alternatif

Kaynak listeyi Ctrl+T ile Excel tablosuna dönüştürmek de aralığı kendiliğinden genişletir. Ancak veri doğrulama kutusu Kategoriler[Ad] gibi yapılandırılmış başvuruları doğrudan kabul etmez. Bunun yerine =DOLAYLI("Kategoriler[Ad]") yazılır ya da tablo sütununa Formüller sekmesindeki Ad Yöneticisi ile bir ad verilip o ad kullanılır.

Tablo yöntemi bütün sürümlerde çalışır, ama tekrarları ayıklamaz. Tekrarsız ve sıralı bir listeye ihtiyacınız varsa ve Microsoft 365 kullanıyorsanız yayılma başvurusu en sade çözümdür.

Listenizi kurun

Kategori ya da müşteri adlarının bulunduğu sütunu belirleyin ve boş bir sütuna SIRALA ile BENZERSİZ formülünü yazın. Ardından açılır listeyi bu alana # işaretiyle bağlayın ve kaynağa yeni bir değer ekleyerek listenin güncellendiğini kontrol edin.