TOP
Bağlantılı açılır listeler
Tanım
Excel'de hızlı ve kolay bir şekilde yapabilirsiniz açılır liste oluştur , ancak hiç bağımlı bir açılır liste oluşturmayı denediniz mi? Böyle bir liste oluşturmanın birkaç yolu vardır, o yüzden onlara bir göz atalım.
Yöntem 1. INDIRECT işlevi
Bu teknik, basit bir şey yapabilen INDIRECT fonksiyonunun uygulanmasına dayanır; belirtilen herhangi bir hücrenin içeriğini Excel'nin anlayacağı bir aralık adresine dönüştürür. Yani, hücre "metni içeriyorsa" А1 ", o zaman işlev sonuç olarak hücreye bir başvuru verecektir А1 . Hücre " kelimesini içeriyorsa Auto ", daha sonra işlev, adı taşıyan adlandırılmış aralığa bir referans verecektir. Auto vesaire.
Örneğin, bu araba modelleri listesini ele alalım Toyota, Ford Ve Nissan :
Toyota modellerinin tüm listesini seçin (hücreden) А2 ve listenin sonuna kadar) ve bu aralığa bir ad verin Toyota menüde Ekle - Ad - Ata (Insert - Name - Define) . Daha sonra aynı işlemi listelerle tekrarlayacağız. Ford Ve Nissan aralıkların adlarını buna göre belirterek Ford Ve Nissan .
İsim atarken Excel'deki aralık adlarının boşluk, noktalama işareti içermemesi ve bir harfle başlaması gerektiğini unutmayın. Bu nedenle araba markalarından birinde bir boşluk varsa (örneğin Ssang Yong), o zaman hücrede ve aralık adında bir alt çizgi ile değiştirilmesi gerekir (ör. Ssang_Yong).
Şimdi bir araba markası seçmek için ilk açılır listeyi oluşturalım. Boş bir hücre seçin ve menüyü açın Veri - Kontrol Et (Data - Validation) , ardından açılır listeden Veri tipi bir seçenek seçin Liste ve sahada Kaynak - marka adlarına sahip hücreleri vurgulayın (örneğimizde sarı hücreler). Tıkladıktan sonra ОК ilk açılır liste hazır:
Şimdi ilk listede seçilen markanın modellerini görüntüleyecek ikinci bir açılır liste oluşturalım. Önceki durumda olduğu gibi boş bir hücre seçin ve menüyü açın Veri - Kontrol Et - daha öte Liste . Alan içerisinde Kaynak aşağıdaki formülü girmeniz gerekecektir:
=INDIRECT(F3)
Nerede:
- F3 - ilk açılır listenin bulunduğu hücrenin adresi - bunu kendi adresinizle değiştirin.
Tüm. Tıkladıktan sonra TAMAM ikinci listenin içeriği, ilk listede seçilen aralığın adına göre seçilecektir.
Bu yöntemin eksileri:
- OFFSET tipi formüllerle belirtilen dinamik aralıklar, ikincil (bağımlı) aralıklar olarak davranamaz. Bunları birincil (bağımsız) liste için kullanabilirsiniz, ancak ikincil listenin formüller olmadan katı bir şekilde tanımlanması gerekir.
- İkincil aralık adları, birincil açılır liste öğeleriyle eşleşmelidir. Yani, boşluklu metin içeriyorsa, bunların alt çizgi vb. ile değiştirilmesi gerekecektir.
- Birçok adlandırılmış aralığı manuel olarak oluşturmanız gerekir.
Yöntem 2. Eşleşme listesi (OFFSET ve MATCH)
Bu yöntem, aşağıdaki türden marka-model eşleşmelerinin sıralanmış bir listesini gerektirir:
Markaların birincil açılır listesini oluşturmak için yukarıda açıklanan genel yöntemi kullanabilirsiniz:
- D1:D3 aralığının adını verin (örn. Markalar )
- Sekmede seçin Veri (Data) takım Veri doğrulama (Data validation)
- Açılır listeden bir kontrol seçeneği seçin Liste (List) ve kalite olarak belirtin Kaynaklar (Source) = Markalar veya yalnızca D1:D3 hücrelerini seçin (eğer listeyle aynı sayfadalarsa).
Ancak bağımlı bir model listesi için, dinamik olarak yalnızca belirli bir markanın model hücrelerine atıfta bulunacak olan OFFSET işleviyle adlandırılmış bir aralık oluşturmanız gerekecektir. Bunun için:
- Ctrl + F3 tuşlarına basın veya düğmeyi kullanın Ad yöneticisi (Name manager) sekmede Formüller (Formulas) . 2003'ten önceki sürümlerde bu bir menü komutuydu Ekle - Ad - Ata (Insert - Name - Define)
- Herhangi bir adla yeni bir adlandırılmış aralık oluşturun (örn. Modeller ) ve sahada Bağlantı (Reference) pencerenin alt kısmına aşağıdaki formülü manuel olarak girin:
=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )
Referanslar mutlak olmalıdır (işaretlerle) $ ). Enter tuşuna bastıktan sonra sayfa adları otomatik olarak formüle eklenecektir.
OFFSET işlevi, başlangıç hücresine göre verilen sayıda satır ve sütun kadar kaydırılan, istenen boyuttaki bir aralığa bir referans verebilir. Daha anlaşılır versiyonda bu fonksiyonun söz dizimi şu şekildedir:
=OFFSET( start_hücresi ; vitesi küçültmek ; Sağ sağıturma ; range_size_in_rows ; range_size_in_columns )
Bu yüzden:
- start_hücresi - listemizin ilk hücresini alıyoruz, yani A1 ;
- vitesi küçültmek - Basitçe ifade etmek gerekirse, seçilen markaya (G7) sahip hücrenin seri numarasını verilen aralıkta (sütunlar) veren MATCH işlevini sayarız VE );
- Sağ sağıturma =1 , bu nedenle bitişik sütundaki modellere atıfta bulunmak istiyoruz ( İÇİNDE );
- range_size_in_rows - ihtiyacımız olan değerlerin listesindeki (A sütunu) oluşum sayısını sayabilen COUNTIF işlevini kullanarak hesaplıyoruz - araba markaları ( G7 );
- range_size_in_columns =1 , bu yüzden modelleri içeren bir sütuna ihtiyacımız var.
Sonuç şöyle bir şey olmalı:
Hücreye oluşturulan formüle göre bir açılır liste eklemeye devam ediyor G8 . Bunun için:
- Bir hücre seçin G8
- Sekmede seçin Veri (Data) takım Veri doğrulama (Data validation) veya menüde Veri - Kontrol Et (Data - Validation)
- Açılır listeden kontrol seçeneğini seçin Liste (List) ve olarak girin Kaynaklar (Source) işareti aralığımızın ismine eşittir, yani = Modeller .
Konuyla ilgili makaleler: