Excel'de karmaşık bir açılır liste nasıl yapılır?
Ayrıca oku
Excel'de bir tablo dolduruyorsanız ve örneğin bir ürünün adı veya bir çalışanın adı gibi bir sütundaki veriler bazen tekrarlanabiliyorsa, her seferinde gerekli parametreyi girmemek için açılır listeyi bir kez oluşturmak ve ondan bir değer seçmek daha kolay ve kolaydır.
Makalede, bir Excel elektronik tablosunda çeşitli türlerde açılır listelerin nasıl yapılacağına bakacağız.
Basit bir açılır liste oluşturun
Bunu yapmak için A1: A7 hücrelerinde listede görüntülenecek verileri giriyoruz. Şimdi açılır liste oluşturacağımız hücreyi seçin - B2.
"Veri" sekmesine gidin ve "Veri Doğrulama" düğmesine tıklayın.
"Seçenekler" sekmesinde, "Veri türü" alanında "Liste" öğesini seçin. Kaynak alanına değerleri çeşitli şekillerde girebilirsiniz:
1 - liste için değerleri noktalı virgülle ayırarak manuel olarak girin;
2 - açılır liste için verilerin girildiği hücre aralığını belirtin;
3 - İsimleri olan hücreleri seçin, üzerlerine sağ tıklayın ve menüden "Bir isim ata" seçeneğini seçin.
B2 hücresini seçin ve "Kaynak" alanına "=" yazın, ardından oluşturulan adı yazın.
Böylece, Excel'de basit bir açılır liste oluşturduk.
Bir sütun için bir başlığınız varsa ve her satırı değerlerle doldurmanız gerekiyorsa, bir hücre değil, bir hücre aralığı seçin - B2:B9. Ardından, açılır listeden her hücrede istenen değeri seçmek mümkün olacaktır.
Açılır Listeye Değerler Ekleme - Dinamik Liste
Bu durumda istenen aralığa değerler ekleyeceğiz ve bunlar otomatik olarak açılır listeye eklenecektir.
Hücre aralığını seçin - D1:D8, ardından "Giriş" sekmesinde "Tablo Olarak Biçimlendir"i tıklayın ve herhangi bir stil seçin.
Verilerin konumunu onaylıyoruz ve "Başlıklı tablo" alanını işaretliyoruz.
En üste tablonun başlığını - "Çalışanlar" yazıyoruz ve onu verilerle dolduruyoruz.
Açılır listeyi içerecek hücreyi seçin ve "Veri Doğrulama" düğmesine tıklayın. Bir sonraki pencerede, "Kaynak" alanına şunu yazın: =DOLAYLI("Tablo1"). Sayfada bir tablom var, bu yüzden ikincisi varsa "Tablo1" yazarım - "Tablo2" vb.
Şimdi listemize yeni bir çalışan ismi ekleyelim: Ira. Açılır listede göründü. Tablodan herhangi bir ismi çıkarırsak listeden de çıkarılacaktır.
Başka bir sayfadaki değerleri içeren açılır liste
Açılır listelerin olduğu tablo bir sayfada ve bu listelerin verileri başka bir sayfadaysa, bu işlev bize çok yardımcı olacaktır.
Sayfa 2'de bir hücre veya hücre aralığı seçin, ardından "Veri Doğrulama" düğmesine tıklayın.
Sayfa 1'e gidin, imleci "Kaynak" alanına getirin ve istediğiniz hücre aralığını seçin.
Artık Sayfa 1'e ad ekleyebilirsiniz, bunlar Sayfa 2'deki açılır listelere eklenecektir.
Bağımlı açılır listeler oluşturma
Diyelim ki üç aralığımız var: çalışanların adları, soyadları ve ikinci adları. Her biri için bir ad atamanız gerekir. Bu aralığın hücrelerini seçiyoruz, boş da olabilir - zamanla onlara açılır listede görünecek verileri eklemek mümkün olacaktır. Farenin sağ tuşu ile üzerlerine tıklıyoruz ve listeden "Bir isim ata" yı seçiyoruz.
Birincisi "Ad", ikincisi - "Soyadı", üçüncüsü - "Baba" olarak adlandırılır.
Atanan isimlerin yazılacağı başka bir aralık yapalım. Buna "Çalışanlar" diyelim.
Aralıkların adlarından oluşacak ilk açılır listeyi yapıyoruz. E1 hücresini seçin ve Veri sekmesinde Veri Doğrulama'yı seçin.
"Veri türü" alanında, kaynak alanında "Liste"yi seçin - ya "=Çalışanlar" girin ya da bir ad verilmiş bir hücre aralığı seçin.
İlk açılır liste oluşturuldu. Şimdi F2 hücresinde, birincisine bağlı olması gereken ikinci bir liste oluşturacağız. İlkinde “Ad” ı seçersek, ikincisinde soyadları listesi, “Soyadı” - bir soyad listesi seçersek görüntülenir.
Hücreyi seçin ve Veri Doğrulama düğmesine tıklayın. "Veri türü" alanında "Liste"yi seçin, kaynak alanına şunu girin: =DOLAYLI($E$1). Burada E1, ilk açılır listeye sahip hücredir.
Bu prensibe göre, bağımlı açılır listeler yapabilirsiniz.
Gelecekte, adın verildiği aralıktaki değerleri girmeniz gerekecek, örneğin "Soyadı". "Formüller" sekmesine gidin ve "Ad Yöneticisi"ne tıklayın. Şimdi, aralığın adına "Soyadı" nı seçin ve en altta son C3 hücresi yerine C10 yazın. Onay işaretini tıklayın. Bundan sonra, aralık artacak ve ona açılır listede otomatik olarak görünecek olan verileri eklemek mümkün olacaktır.
Artık Excel'de açılır liste yapmayı biliyorsunuz.
Aynı anda birkaç hücreden oluşan bir açılır liste nasıl oluşturulur (adın bir maliyeti olduğunu varsayalım)
Teşekkürler, hepsi iyi çalıştı.
Veri kontrolünün açık olduğu pencere diğer pencerelerle, özellikle başka bir sayfayla çalışmaya izin vermediğinden, başka bir sayfadaki değerleri içeren açılır liste çalışmaz!
Bağımlı açılır liste, Excel şablonlarının kullanıcıları tarafından sıklıkla övülen bir numara yapmanızı sağlar. İşi kolaylaştıran ve hızlandıran bir numara. Formlarınızı rahat ve keyifli hale getirecek bir numara.
Bir Excel hücresinde bağımlı açılır liste oluşturma örneği
Satıcıların mal sipariş ettiği belgeleri doldurmak için uygun bir form oluşturmak üzere bağımlı bir açılır liste kullanma örneği. Tüm ürün yelpazesinden satacakları ürünleri seçmek zorunda kaldılar.
Her satıcı önce bir ürün grubu, ardından bu gruptan belirli bir ürün belirledi. Form, grubun tam adını ve belirli bir öğe dizinini içermelidir. Bunu elle yazmak çok zaman alıcı (ve sinir bozucu) olacağından, çok hızlı ve kolay bir çözüm buldum - 2 bağımlı açılır liste.
İlki, tüm ürün kategorilerinin bir listesiydi, ikincisi, seçilen kategorideki tüm ürünlerin bir listesiydi. Bu nedenle, önceki listede yapılan seçime bağlı olarak bir açılır liste oluşturdum (burada iki bağımlı açılır listenin nasıl oluşturulacağına dair materyal bulacaksınız).
Ev bütçesi şablonunun kullanıcısı, harcama kategorisi ve alt kategorisinin gerekli olduğu durumlarda aynı sonucu elde etmek ister. Verilerin bir örneği aşağıdaki şekildedir:
Örneğin, Eğlence kategorisini seçersek, alt kategorilerin listesi şöyle olmalıdır: Sinema, Tiyatro, Havuz. Ev bütçenizde daha detaylı bilgileri analiz etmek istiyorsanız çok hızlı bir çözüm.
Excel'e Bağlı Açılır Menüde Kategorileri ve Alt Kategorileri Listeleme
Ev bütçesinin önerilen versiyonunda, yalnızca bir kategoriyle sınırlı olduğumu itiraf ediyorum, çünkü benim için böyle bir harcama bölümü oldukça yeterli (giderlerin / gelirlerin adı bir alt kategori olarak kabul ediliyor). Ancak, bunları alt kategorilere ayırmanız gerekiyorsa, aşağıda anlattığım yöntem idealdir. Kullanmaktan çekinmeyin!
Ve nihai sonuç şöyle görünür:
Alt kategorilerin bağımlı açılır listesi
Bunu başarmak için, tek bir açılır liste oluştururken yaptığımızdan biraz farklı bir veri tablosu oluşturmamız gerekiyor. Tablo şöyle görünmelidir (aralık G2:H15):
Çalışan kaynak Excel elektronik tablosu
Bu tabloda bir kategori ve yanına alt kategorilerini girmelisiniz. Kategori adı, alt kategori sayısı kadar tekrarlanmalıdır. Verilerin Kategori sütununa göre sıralanması çok önemlidir. Bu, formülü daha sonra yazarken son derece önemli olacaktır.
İlk görüntüdeki tablolar da kullanılabilir. Elbette formüller farklı olacaktır. Hatta bir zamanlar nette böyle bir çözüm buldum ama hoşuma gitmedi çünkü listenin sabit bir uzunluğu vardı: bu da bazen listenin boş alanlar içerdiği ve bazen tüm öğeleri göstermediği anlamına gelir. Elbette bu sınırlamadan kaçınabilirim, ancak çözümümü daha çok sevdiğimi itiraf ediyorum, bu yüzden o çözüme geri dönmedim.
Tamam ozaman. Şimdi, bağımlı bir açılır liste oluşturma adımlarını tek tek anlatacağım.
1. Hücre aralığı adları
Bu isteğe bağlı bir adımdır, onsuz bunu sorunsuz bir şekilde halledebiliriz. Ancak, formülleri hem yazmayı hem de okumayı çok daha kolaylaştırdıkları için adları kullanmayı seviyorum.
İki aralığı adlandıralım. Tüm kategorilerin listesi ve kategorilerin çalışma listesi. Bunlar, A3:A5 (ilk görüntüdeki yeşil tablodaki kategorilerin listesi) ve G3:G15 (mor çalışma sayfasındaki yinelenen kategorilerin listesi) aralıkları olacaktır.
Bir kategori listesini adlandırmak için:
- A3:A5 aralığını seçin.
- Ad alanına (formül çubuğunun solundaki alan) "Kategori" adını girin.
- Enter tuşu ile onaylayın.
WorkList olarak adlandırabileceğiniz G3:G15 kategori iş listesi aralığı için de aynısını yapın. Bu aralığı formülde kullanacağız.
2. Bir kategori için açılır liste oluşturun
Basit olacak:
- Listeyi yerleştirmek istediğiniz hücreyi seçin. Benim durumumda bu A12.
- VERİ menüsünden Veri Doğrulama aracını seçin. Girdi Değerlerini Doğrula penceresi görünür.
- Veri türü olarak "Liste"yi seçin.
- Kaynak için şunu girin: =Kategori (aşağıdaki resim).
- OK ile onaylayın.
Sonuç şudur:
Kategori için açılır liste.
3. Bir alt kategori için bağımlı bir açılır liste oluşturun
Şimdi eğlenceli olacak. Listelerin nasıl oluşturulacağını biliyoruz - bunu sadece bir kategori için yaptık. Yalnızca bir soru: "Excel'e yalnızca belirli bir kategori için olan değerleri seçmesini nasıl söylerim?" Muhtemelen tahmin edebileceğiniz gibi, burada bir çalışma sayfası ve tabii ki formüller kullanacağım.
B12 hücresinde bir açılır liste oluşturmak için zaten bildiğimiz şeyle başlayalım. Bu hücreyi seçin ve Veri/Veri Doğrulama'yı tıklayın ve veri türünü Liste olarak ayarlayın.
Liste kaynağında aşağıdaki formülü girin:
"Giriş değerlerini kontrol et" penceresinin görünümü:
Bağımlı açılır listedeki bir alt kategori için giriş değerlerinin doğrulanması
Gördüğünüz gibi, tüm bağımlı liste hilesi, OFFSET işlevini kullanmaktır. Neredeyse hepsi. KAÇINCI ve EĞERSAY işlevleri ona yardımcı olur. OFFSET işlevi, aralıkları dinamik olarak tanımlamanıza olanak tanır. İlk olarak, aralık kaydırmanın başlaması gereken hücreyi tanımlarız ve sonraki bağımsız değişkenlerde boyutunu tanımlarız.
Örneğimizde, aralık, çalışma sayfasındaki Alt Kategori sütununda hareket edecektir (G2:H15). Fonksiyonumuzun da ilk argümanı olan H2 hücresinden hareket etmeye başlayacağız. Formülde H2 hücresi mutlak referans olarak yazılmıştır, çünkü birçok hücrede açılır listeyi kullanacağımızı varsayıyorum.
Çalışma sayfası Kategori'ye göre sıralandığından, açılır liste için kaynak olması gereken aralık, seçilen kategorinin ilk bulunduğu yerden başlayacaktır. Örneğin, Yiyecek kategorisi için H6:H11 aralığını, Taşıma kategorisi için H12:H15 aralığını vb. görüntülemek istiyoruz. Değişiklikler, aralığın başlangıcı ve yüksekliğidir (yani, listedeki öğelerin sayısı).
Aralığın başlangıcı, H2 hücresine göre, Kategori sütununda ilk meydana gelen kategorinin konum numarası kadar (sayı olarak) aşağı kaydırılacaktır. Bir örnekle anlamak daha kolay olacaktır: Yiyecek kategorisi aralığı H2 hücresine göre 4 hücre aşağı kaydırılır (H2'den 4 hücreden başlar). Alt Kategori sütununun 4. hücresinde (İşListesi adlı bir aralık olduğu için başlık dahil değildir), Yiyecek kelimesi vardır (ilk geçtiği yer). Bu gerçeği, aralığın başlangıcını fiilen belirlemek için kullanırız. MATCH işlevi bunun için bize hizmet edecek (OFFSET işlevinin ikinci argümanı olarak sunulmuştur):
Aralığın yüksekliği COUNTIF işlevi tarafından belirlenir. Kategorideki, yani Beslenme kelimesindeki tüm oluşumları sayar. Bu kelime kaç kez geçiyor, kaç pozisyon bizim aralığımızda olacak. Bir aralıktaki konumların sayısı, yüksekliğidir. İşte işlev:
Elbette, her iki işlev de yukarıda açıklanan KAYDIRMA işlevine zaten dahil edilmiştir. Ayrıca, hem MATCH hem de COUNTIF'te WorkList adlı bir aralığa başvuru olduğunu unutmayın. Daha önce de belirttiğim gibi, aralık adı kullanmanıza gerek yok, sadece $H3: $H15 yazabilirsiniz. Ancak, bir formülde aralık adlarının kullanılması, okumayı daha basit ve kolay hale getirir.
Bu kadar:
Excel'de Bağımlı Açılır Liste Örneği İndir
Tek bir formül, o kadar basit değil ama işi kolaylaştırıyor ve veri girişi hatalarına karşı koruyor!