Excel'de bir açılır liste nasıl yapılır

Açılır liste, bilgilerle çalışmayı daha rahat hale getirmeye yardımcı olabilecek inanılmaz derecede kullanışlı bir araçtır. Bir hücrede, diğerlerinde olduğu gibi çalışabileceğiniz birkaç değeri aynı anda içermeyi mümkün kılar. İhtiyacınız olanı seçmek için, ok simgesine tıklamanız yeterlidir, ardından uXNUMXbuXNUMXbis değerlerinin bir listesi görüntülenir. Belirli bir hücreyi seçtikten sonra, hücre otomatik olarak onunla doldurulur ve formüller buna göre yeniden hesaplanır.

Excel, bir açılır menü oluşturmak için birçok farklı yöntem sunar ve ayrıca bunları esnek bir şekilde özelleştirmenize olanak tanır. Bu yöntemleri daha ayrıntılı olarak analiz edelim.

Liste oluşturma süreci

Bir açılır menü oluşturmak için “Veri” – “Veri Doğrulama” yolundaki menü öğelerine tıklayın. “Parametreler” sekmesini bulmanız ve daha önce açılmadıysa üzerine tıklamanız gereken bir iletişim kutusu açılacaktır. Bir çok ayarı var ama “Data Type” maddesi bizim için önemli. Tüm anlamlar arasında “Liste” doğru olanıdır.

Açılır listeye bilgilerin girildiği yöntemlerin sayısı oldukça fazladır.

1. Aynı iletişim kutusunun aynı sekmesinde bulunan “Kaynak” alanında noktalı virgülle ayrılmış liste öğelerinin bağımsız gösterimi.
   

   

2. Değerlerin ön gösterimi. Kaynak alanı, gerekli bilgilerin mevcut olduğu aralığı içerir.
   

   

3. Adlandırılmış bir aralık belirtme. Bir öncekini yineleyen bir yöntem, ancak yalnızca aralığı önceden adlandırmak gerekir.
   

   

Bu yöntemlerden herhangi biri istenen sonucu verecektir. Gerçek hayattaki durumlarda açılır listeler oluşturma yöntemlerine bakalım.

Listedeki verilere göre

Diyelim ki farklı meyve türlerini açıklayan bir tablomuz var.

Bu bilgi kümesine dayalı bir açılır menüde bir liste oluşturmak için aşağıdakileri yapmanız gerekir:

1. Gelecekteki liste için ayrılmış hücreyi seçin.
2. Şeritteki Veri sekmesini bulun. Orada “Verileri doğrula”ya tıklıyoruz.
   

   

3. “Veri Türü” öğesini bulun ve değeri “Liste” olarak değiştirin.
   

   

4. “Kaynak” seçeneğini gösteren alana istediğiniz aralığı girin. Liste kopyalanırken bilgilerin kaymaması için mutlak referansların belirtilmesi gerektiğini lütfen unutmayın.
   

Ayrıca birden fazla hücrede tek seferde liste oluşturma işlevi vardır. Bunu başarmak için hepsini seçmeli ve daha önce açıklanan adımların aynısını uygulamalısınız. Yine mutlak referansların yazıldığından emin olmanız gerekir. Adresin sütun ve satır adlarının yanında dolar işareti yoksa, sütun ve satır adlarının yanına $ işareti gelene kadar F4 tuşuna basarak eklemeniz gerekir.

Manuel veri kaydı ile

Yukarıdaki durumda, gerekli aralık vurgulanarak liste yazılmıştır. Bu uygun bir yöntemdir, ancak bazen verileri manuel olarak kaydetmek gerekir. Bu, çalışma kitabındaki bilgilerin tekrarlanmasını önlemeyi mümkün kılacaktır.

İki olası seçeneği içeren bir liste oluşturma göreviyle karşı karşıya olduğumuzu varsayalım: evet ve hayır. Görevi gerçekleştirmek için gereklidir:

1. Liste için hücreye tıklayın.
2. “Veri”yi açın ve orada bize tanıdık gelen “Veri Kontrolü” bölümünü bulun.
   

   

3. Yine, “Liste” türünü seçin.
   

   

4. Burada “Evet; Hayır” kaynak olarak. Bilgilerin numaralandırma için noktalı virgül kullanılarak manuel olarak girildiğini görüyoruz.

Tamam'a tıkladıktan sonra aşağıdaki sonucu elde ederiz.

Ardından, program uygun hücrede otomatik olarak bir açılır menü oluşturacaktır. Kullanıcının açılır listede öğe olarak belirttiği tüm bilgiler. Birkaç hücrede liste oluşturma kuralları, bilgileri noktalı virgül kullanarak manuel olarak belirtmeniz gerektiği tek istisna dışında, öncekilere benzer.

OFFSET işlevini kullanarak bir açılır liste oluşturma

Klasik yönteme ek olarak, işlevi kullanmak mümkündür. BERTARAFaçılır menüler oluşturmak için.

Sayfayı açalım.

Açılır liste işlevini kullanmak için aşağıdakileri yapmanız gerekir:

1. Gelecekteki listeyi yerleştirmek istediğiniz ilgi hücresini seçin.
2. Sırayla “Veri” sekmesini ve “Veri Doğrulama” penceresini açın.
   

   

3. Set listesi". Bu, önceki örneklerle aynı şekilde yapılır. Son olarak aşağıdaki formül kullanılır: =KAYDIR(A$2$;0;0;5). Argüman olarak kullanılacak hücrelerin belirtildiği yere giriyoruz.

Ardından program, meyvelerin bir listesini içeren bir menü oluşturacaktır.

Bunun sözdizimi şudur:

=KAYDIR(referans,line_offset,sütun_offset,[yükseklik],[genişlik])

Bu fonksiyonun 5 argümanı olduğunu görüyoruz. İlk olarak offset yapılacak ilk hücre adresi verilir. Sonraki iki bağımsız değişken, kaç satır ve sütunun dengeleneceğini belirtir. Bizden bahsetmişken, Yükseklik argümanı 5'tir çünkü listenin yüksekliğini temsil eder. 

Excel'de veri değiştirmeli açılır liste (+ OFFSET işlevini kullanarak)

Verilen durumda BERTARAF sabit bir aralıkta bulunan bir açılır menü oluşturmaya izin verilir. Bu yöntemin dezavantajı, öğeyi ekledikten sonra formülü kendiniz düzenlemeniz gerekmesidir.

Yeni bilgi girme desteğine sahip dinamik bir liste oluşturmak için şunları yapmanız gerekir:

1. İlgilenilen hücreyi seçin.
2. “Veri” sekmesini genişletin ve “Veri Doğrulama” üzerine tıklayın.
3. Açılan pencerede tekrar “Liste” öğesini seçin ve veri kaynağı olarak aşağıdaki formülü belirtin: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Tamam 'ı tıklatın.

Bu bir işlev içerir COUNTIF, kaç hücrenin dolduğunu hemen belirlemek için (çok daha fazla sayıda kullanım olmasına rağmen, buraya sadece belirli bir amaç için yazıyoruz).

Formülün normal şekilde çalışabilmesi için formülün yolunda boş hücre olup olmadığının izlenmesi gerekir. Olmamalılar.

Başka bir sayfadan veya Excel dosyasından veriler içeren açılır liste

Başka bir belgeden veya aynı dosyada bulunan bir sayfadan bilgi almanız gerekiyorsa klasik yöntem çalışmaz. Bunun için fonksiyon kullanılır. DOLAYLI, başka bir sayfada veya genel olarak bir dosyada bulunan bir hücreye doğru biçimde bir bağlantı girmenize izin verir. Aşağıdakileri yapmanız gerekir:

1. Listeyi yerleştirdiğimiz hücreyi etkinleştirin.
2. Bildiğimiz pencereyi açıyoruz. Daha önce diğer aralıklar için kaynakları belirttiğimiz yerde, biçimde bir formül belirtilir. =DOLAYLI(“[Liste1.xlsx]Sayfa1!$A$1:$A$9”). Doğal olarak, Liste1 ve Sayfa1 yerine kitap ve sayfa adlarınızı sırasıyla ekleyebilirsiniz. 

Dikkat! Dosya adı köşeli parantez içinde verilmiştir. Bu durumda Excel, şu anda kapalı olan dosyayı bilgi kaynağı olarak kullanamaz.

Ayrıca, dosya adının yalnızca gerekli belge, listenin ekleneceği klasörle aynı klasörde bulunuyorsa anlamlı olduğuna da dikkat edilmelidir. Değilse, bu belgenin adresini tam olarak belirtmelisiniz.

Bağımlı Açılır Listeler Oluşturma

Bağımlı liste, içeriği kullanıcının başka bir listedeki seçiminden etkilenen bir listedir. Önümüzde, her birine bir isim verilmiş üç aralık içeren bir masa açık olduğunu varsayalım.

Sonuçları başka bir listede seçilen seçenekten etkilenen listeler oluşturmak için bu adımları izlemeniz gerekir.

1. Aralık adlarıyla 1. liste oluşturun.
   

   

2. Kaynak giriş noktasında gerekli göstergeler tek tek vurgulanır.
   

   

3. Kişinin seçtiği bitki türüne göre 2. bir liste oluşturun. Alternatif olarak, ilk listede ağaçları belirtirseniz, ikinci listedeki bilgiler “meşe, gürgen, kestane” ve ötesi olacaktır. Veri kaynağının girdisi yerine formülü yazmak gerekir. =DOLAYLI(E3). E3 – 1.=DOLAYLI(E3) aralığının adını içeren hücre. E3 - listenin adını taşıyan hücre 1.

Şimdi her şey hazır.

Açılır listeden birden çok değer nasıl seçilir?

Bazen sadece bir değere öncelik vermek mümkün değildir, bu nedenle birden fazla değer seçilmelidir. Ardından sayfa koduna bir makro eklemeniz gerekir. Alt + F11 tuş kombinasyonunu kullanarak Visual Basic Düzenleyicisi açılır. Ve kod oraya eklenir.

Özel Alt Çalışma Sayfası_Değişikliği (Aralık Olarak ByVal Hedefi)

    On Error Resume Next

    Kesişmiyorsa(Hedef, Aralık(«Е2:Е9»)) Hiçbir Şey Değildir ve Hedef.Hücre Sayısı = 1 O zaman

        Application.EnableEvents = Yanlış

        Len (Target.Offset (0, 1)) = 0 ise

            Hedef.Ofset (0, 1) = Hedef

        başka

            Target.End (xlToRight) .Offset (0, 1) = Hedef

        Eğer son

        Target.ClearContents

        Application.EnableEvents = True

    Eğer son

End Sub 

Hücrelerin içeriğinin aşağıda gösterilebilmesi için editöre aşağıdaki kodu ekliyoruz.

Özel Alt Çalışma Sayfası_Değişikliği (Aralık Olarak ByVal Hedefi)

    On Error Resume Next

    Kesişmiyorsa(Hedef, Aralık(«H2:K2»)) Hiçbir Şey Değildir ve Hedef.Hücre Sayısı = 1 O zaman

        Application.EnableEvents = Yanlış

        Len (Target.Offset (1, 0)) = 0 ise

            Hedef.Ofset (1, 0) = Hedef

        başka

            Target.End (xlDown) .Offset (1, 0) = Hedef

        Eğer son

        Target.ClearContents

        Application.EnableEvents = True

    Eğer son

End Sub

Ve son olarak, bu kod bir hücreye yazmak için kullanılır.

Özel Alt Çalışma Sayfası_Değişikliği (Aralık Olarak ByVal Hedefi)

    On Error Resume Next

    Kesişmiyorsa(Hedef, Aralık(«C2:C5»)) Hiçbir Şey Değildir ve Hedef.Hücre Sayısı = 1 O zaman

        Application.EnableEvents = Yanlış

        yeniDeğer = Hedef

        Uygulama.Geri Al

        oldval = Hedef

        Len (eski değer) <> 0 ise ve eski değer <> yeniDeğer O zaman

            Hedef = Hedef & «,» ve yeniDeğer

        başka

            Hedef = yeniDeğer

        Eğer son

        Len (newVal) = 0 ise Target.ClearContents

        Application.EnableEvents = True

    Eğer son

End Sub

Aralıklar düzenlenebilir.

Arama ile açılır liste nasıl yapılır?

Bu durumda, başlangıçta farklı bir liste türü kullanmanız gerekir. “Geliştirici” sekmesi açılır, ardından “Ekle” – “ActiveX” öğesine tıklamanız veya dokunmanız (ekran dokunuyorsa) gerekir. Bir birleşik giriş kutusu vardır. Bu listeyi çizmeniz istenecek ve ardından belgeye eklenecektir.

Ayrıca, ListFillRange seçeneğinde bir aralığın belirtildiği özellikler aracılığıyla yapılandırılır. Kullanıcı tanımlı değerin görüntülendiği hücre, LinkedCell seçeneği kullanılarak yapılandırılır. Ardından, program otomatik olarak olası değerleri önereceği için ilk karakterleri yazmanız yeterlidir.

Otomatik veri ikameli açılır liste

Ayrıca, aralığa eklendikten sonra verilerin otomatik olarak değiştirildiği bir işlev de vardır. Bunu yapmak kolaydır:

1. Gelecekteki liste için bir hücre kümesi oluşturun. Bizim durumumuzda, bu bir renk kümesidir. Onu seçiyoruz.
   

   

2. Ardından, tablo olarak biçimlendirilmesi gerekiyor. Aynı addaki düğmeye tıklamanız ve tablo stilini seçmeniz gerekir.
   

   

   

Ardından, “OK” düğmesine basarak bu aralığı onaylamanız gerekir.

Ortaya çıkan tabloyu seçiyoruz ve A sütununun üstünde bulunan giriş alanından ona bir isim veriyoruz.

İşte bu, bir tablo var ve ihtiyacınız olan bir açılır listenin temeli olarak kullanılabilir:

1. Listenin bulunduğu hücreyi seçin.
2. Veri Doğrulama iletişim kutusunu açın.
   

   

3. Veri tipini “Liste” olarak ayarlıyoruz ve değerler olarak = işareti üzerinden tablonun adını veriyoruz.
   

   

   

Her şey, hücre hazır ve aslında ihtiyacımız olduğu gibi içinde renklerin isimleri gösteriliyor. Artık, sonuncudan hemen sonra biraz daha aşağıda bulunan bir hücreye yazarak yeni konumlar ekleyebilirsiniz.

Tablonun avantajı, yeni veri eklendiğinde aralığın otomatik olarak artmasıdır. Buna göre, liste eklemenin en uygun yolu budur.

Açılır liste nasıl kopyalanır?

Kopyalamak için Ctrl + C ve Ctrl + V tuş kombinasyonunu kullanmak yeterlidir. Böylece açılır liste biçimlendirme ile birlikte kopyalanacaktır. Biçimlendirmeyi kaldırmak için, “değerlerdeki koşullar” seçeneğinin ayarlandığı özel bir yapıştırma kullanmanız gerekir (bağlam menüsünde, bu seçenek listeyi kopyaladıktan sonra görünür).

Açılır liste içeren tüm hücreleri seçin

Bu görevi gerçekleştirmek için, "Bul ve Seç" grubundaki "Bir hücre grubu seçin" işlevini kullanmanız gerekir.

Bundan sonra, “Veri Doğrulama” menüsünde “Tümü” ve “Bunlar aynı” öğelerini seçmeniz gereken bir iletişim kutusu açılacaktır. İlk öğe tüm listeleri seçer ve ikincisi yalnızca belirli olanlara benzer olanları seçer.