Şimdiye kadar bu Veri Doğrulama serisinde, Excel'de veri doğrulama ile farklı teknikler kullanarak normal açılır liste ve dinamik açılır liste oluşturmayı öğrendik.
Ve bugün, bu bölümde, farklı yöntemler kullanarak Microsoft Excel'de Bağımlı Açılır Listeyi nasıl oluşturacağınızı göstereceğiz.
Bağımlı Açılır Liste, basamaklı veri doğrulaması olarak da bilinir ve veri doğrulamasını içeren diğer hücrede seçilen değere bağlı olarak açılır listedeki seçenekleri sınırlar. Başka bir deyişle, ikinci açılır listede görüntülenecek değerleri belirleyen ilk açılır listede seçilen değere bağlıdır.
Bu, büyük verilerle veya bazı dinamik raporlarla çalışmanın çok yaygın bir senaryosudur.nd hücre, ilk açılır menüde seçilen liste öğesine bağlı bir liste görüntüler.
Excel'de bildiğimiz gibi, belirli bir görevi yapmanın birçok yolu vardır ve benzer şekilde, Excel'de bağımlı veri doğrulama oluşturmanın birçok yolu vardır. Ve bugün, bağımlı veri doğrulama listesi oluşturmak için 5 farklı teknik göstereceğiz.
Ham veriler herhangi bir sırada veya biçimde olabilir ve aradığınızı elde etmek için verileri veya biçimi her değiştiremezsiniz.
Bu nedenle, bağımlı açılır listeyi elde etmek için bir veri seti ancak 3 farklı formatta aldık. Ve görebileceğiniz gibi, verilerimiz A sütunundan E sütununa kadar olan sol tarafta ve J & K sütunundaki sağ tarafta beklenen çıktımız olacak. Sütun J birincil doğrulamaya sahip olacak. K sütunu bağımlı olacak ve J sütununda seçilen değere bağlı olarak değerleri gösterecektir.
1NS Örnek:-
2nd Örnek:-
3rd Örnek:-
1NS Örnek:-
A8 sütunundan E13'e kadar her ürün kodu için ürün listemiz var. Ve J10'daki ürün kodunu, ardından seçilen ürün koduna bağlı olarak K10 hücresindeki bir ürün adını seçmek istiyoruz.
İlk Yöntem:-
İlk yöntem çok basit ve kısadır ve bağlı açılır listeyi elde etmek için sadece 3 adım gerekir. Ancak, yalnızca menzilinizde herhangi bir değişiklik yapmadığınız sürece başarıyla çalışır. Bir kez, verilerinizi değiştirdiğinizde, güncellenmiş basamaklı veri doğrulamasını almak için ilk olarak adlandırılmış aralığı değiştirmeniz gerekecektir.
Aşağıda verilen adımları izleyin:-
- A8'den E13'e kadar tüm tabloyu seçin
- Ardından “Formüller” sekmesine gidin, ardından “Tanımlanmış Adlar” kategorisi altında “Seçimden Oluştur” seçeneğine tıklayın.
- CTRL+SHIFT+F3 klavye kısayolunu da kullanabilirsiniz.
- Seçimlerden Ad Oluştur iletişim kutusu görünecektir
- Diğer satır ve sütunların adlarını oluşturmak için hangi satır ve sütunların kullanılacağını doğrulamayı ister. Adları oluşturmak için "üst satırı" kullanmayı ve 2'nin işaretini kaldırmayı onaylıyoruz.nd seçeneği ve ardından Tamam'a tıklıyoruz
Not: - Alt çizgi ve nokta dışındaki boşluklar ve diğer özel karakterlere ad olarak izin verilmez. Varsayılan olarak, alt çizgiye dönüştürülecektir. Bu nedenle, kelimeleri ayırmak için alt çizgi karakteri ve nokta kullanın. Ayrıca ilk harf bir sayı olamaz; bir harf, alt çizgi veya ters eğik çizgi olmalıdır.
- Şimdi, her aralığın bir adı olduğunu doğrulamak için “Ad Yöneticisi” ne gidiyoruz (CTRL + F3 tuşlarına basın)
- Orada mevcut 5 adlandırılmış aralığın tümünü görebiliriz
- Ayrıca her aralık adının dizenin ortasında boş yerine alt çizgi olduğunu görebiliriz.
Şimdi açılır liste oluşturacağız:-
- J10 hücresini seçin ve Veri Doğrulama iletişim kutusunu açmak için ALT++D+L tuşlarına basın
- Liste > öğesini seçin ve ardından Kaynak sekmesinde A8:E8 aralığını girin
- Tamam'a tıklayın
- Şimdi K10 hücresinde bağımlı liste oluşturacağız
- ALT+D+L tuşlarına basarak Veri Doğrulama iletişim kutusunu açın
- Listeyi seçin, kaynakta bu işlevi girin:- =DOLAYLI(YİNEDE($J$10," ","_"))
Veri doğrulamada, bağımlı listeyi oluşturmak için, birincil veri doğrulama listesine dayalı değeri döndürmek için DOLAYLI işlevini kullandık. Alt çizgiyi boşlukla değiştirmek için DOLAYLI işlevin içinde SUBSTITUTE işlevini kullanacağız.
- Tamam'a tıklayın
J10 hücresinde herhangi bir ürün kodu seçtiğimizde, seçilen ürün kodunun ürün listesi K10 hücresinde görünecektir. Örneğin: - ETV 501'i seçtik, şimdi bağımlı ürün listesinin K10 hücresinde göründüğünü görebilirsiniz.
Not: - Listede görünmeyecek ürün adını ve ürün kodunu ekleyeceğiniz zaman.
Örneğin: - Ürün 26'yı ETV 505 ürün kodunun altına ekledik ancak ETV 505 ürününü seçtiğimizde eklenen ürün açılır listede görünmüyor.
Böylece, basit tekniği kullanarak sadece 3 basit adımda bağımlı açılır liste oluşturabilirsiniz.
2nd Örnek:-
Bu örnekte, bu dikey tabloda gösterildiği gibi verileriniz olduğunda bağımlı bir açılır listenin nasıl alınacağını göreceğiz.
Bağımlı açılır liste oluşturmak için iki farklı yöntem kullanacağız. Her ikisi de neredeyse benzer tekniklerdir. Ancak, biri adlandırılmış aralığa sahip değildir ve diğeri adlandırılmış aralığa sahip olacaktır.
1NS Yöntem:-
Aynısını yapmak için OFFSET, MATCH & COUNTIF fonksiyonlarını birlikte kullanacağız.
Dinamik aralığı oluşturmak için OFFSET fonksiyonunun kullanıldığını bildiğimiz için, bu nedenle “Dinamik veri doğrulama” listesi oluşturmak için dinamik aralığı döndürmek için OFFSET fonksiyonunu kullanıyoruz.
KAÇINCI, Excel'deki bir listedeki bir öğenin göreli konumunu döndürmek için kullanılır. Ve burada, sayfadaki aralığımızda birincil açılır listede seçilen kategoriyi eşleştirmemize yardımcı olacak ve bir sayı döndürecektir.
Ve EĞERSAY, ölçütlerle eşleşen hücrelerin sayısını almak için kullanılır. Ve burada, EĞERSAY işlevini kullanarak gösterilecek satır sayısını saymak için bunu kullanacağız.
Aşağıda verilen adımları izleyin:-
- Birincil veri doğrulama listemizi oluşturacağımız J21 hücresini seçin.
- Veri Doğrulama iletişim kutusunu açmak için ALT+D+L tuşlarına basın
- İzin verilen kategoriden liste seçin
- Kaynak sekmesine tıklayın ve B20:B24 aralığını seçin
- Ve Tamam'a tıklayın
- K21 hücresine gidin ve veri doğrulama iletişim kutusunu bir kez daha açın
- Ardından Liste'yi seçiyoruz ve kaynakta aşağıdaki işlevi giriyoruz:
- =KAYDIR($E$19,MATCH($J$21,$D$20:$D$32,0),0,EĞERSAY($D$20:$D$32,$J$21))
- Tamam'a tıklayın
- K21 hücresinde, seçilen ürün kodunun ilgili tüm değerlerini görebiliriz:-
Yani, fonksiyonda hücre referanslarını alarak bağımlı listeyi bu şekilde alabilirsiniz.
2nd Yöntem:-
Sonraki yöntemde, basamaklı veri doğrulamasını almak için aynı işlevde adlandırılmış aralığı kullanacağız. Öncelikle ürün kodu için dinamik liste oluşturmalıyız. Verilere herhangi bir yeni ürün eklenmesi durumunda, açılır menü aynısını gösterecek şekilde güncellenmelidir.
Aynısını yapmak için aşağıda verilen adımları izleyin:-
- B19'u seçin, ardından "Ad Yöneticisi" penceresini açmak için CTRL + F3 tuşlarına basın
- Şimdi “Yeni” ye tıklıyoruz ve “Ad Tanımla” iletişim kutusu beliriyor
- Adın zaten ad kutusunda göründüğünü görebiliriz - bunun nedeni “Ad Yöneticisi” penceresini açmadan önce B9'u seçmemizdir. Ve B19'da metin olduğu için istersek başka bir isimle değiştirebiliriz.
- Aşağıdaki söz formülünü girin: -
=OFFSET('BağımlıDropDownList'!$B$20,0,0,COUNTA('BağımlıDropDownList'!$B$20:$B$32))
- Tamam'a tıklayın
Benzersiz ürünler için dinamik bir liste oluşturduğumuz için şimdi D sütunundaki ürün kod aralığı için dinamik aralık oluşturacağız.
Benzersiz ürün için izlediğimiz adımların aynısını izleyin: -
- D19 hücresini seçin, Ad Tanımla iletişim kutusunu açın
- Adın zaten orada olduğunu göreceksiniz
- Başvuruda, aşağıdaki formülü girin: -
=OFFSET('Bağımlı Açılan Liste'!$D$20,0,0,COUNTA('Bağımlı Açılan Liste'!$D$20:$D$35))
- Tamam'a tıklayın
- Artık her iki dinamik aralık da hazır. Yani J22'ye gidip “ALT + D + L” tuşlarına basıyoruz ve “Liste” yi seçiyoruz.
- Kaynakta, “Unique Product Code” için tanımladığımız adlandırılmış aralığı alacağız, bu nedenle mevcut tüm adlandırılmış aralıkları görmek için F3'e basıyoruz.
- “Benzersiz Ürün Kodu” isimli aralığı görebiliriz, bu yüzden üzerine tıklıyoruz ve ardından Tamam'a tıklıyoruz ve enter'a basıyoruz.
- Enter tuşuna bastığımız anda, benzersiz ürün kodlarının listesini tutan J22 hücresinde aşağı açılan oku görüyoruz.
- K22 hücresini seçin ve “Veri Doğrulama” iletişim kutusunu açın
- Son yöntemde kullandığımız işlevin aynısını ancak adlandırılmış aralıkla kullanacağız.
- Listeyi seçin ve ardından kaynakta aşağıdaki formülü girin: -
=KAYDIR($E$19,KAÇINCI($J$22,Ürün_Kodu,0),0,EĞERSAY(Ürün_Kodu,J22))
- Tamam'a tıklayın
- Şimdi, birincil açılır menünün yanı sıra alt ürün listesine sahibiz
- J22'den “ETV-101” ürününü seçin ve K22'de sadece bu “ETV-101” ürününün altına giren isimleri görebiliriz. Ve J22'de herhangi bir ürünü (“ETV-103) değiştirdiğimizde, K22 bu kod için karşılık gelen değerleri görüntüler.
Şimdi listeye yeni bir ürün kodu eklediğimizde ne olacağını göreceğiz? Bu açılır listeler güncellenecek mi?
Listeye yeni ürün ekleyelim; Aşağıda verilen adımları izleyin: -
- Unique_Prod_Code listesine ürün kodu ekleyin
- Ayrıca verilere Ürün_Kodu ve Ürün_Adı ekleyin: -
- Şimdi açılır listeyi kontrol edin - eklenen ürün kodu ve adı görünüyor
3rd Örnek:-
Doğrudan tablodan dinamik başlıklara sahibiz ve ürün yelpazesine yeni ürünler ekleyeceğiz. Tablo, 1 için kullandığımız formatın aynısıdır.NS yöntem.
4NS Yöntem:-
Aşağıda verilen adımları izleyin:-
- A40:E40 başlığını seçin
- Önce başlıklar için dinamik aralığı oluşturun, "Ad Tanımla" iletişim kutusunu açın
- Adın yerine “Başlık” yazın ve ardından “belirtilen” içine aşağıdaki formülü girin: -
- Aşağıdaki işlevi girin: -
- =OFFSET('Bağımlı Açılan Liste'!$A$40,,,,COUNTA('Bağımlı Açılan Liste'!40$:40$))
- Tamam'a tıklayın
- Dinamik "Yön" aralığı artık hazır
Ve şimdi her başlık için adlandırılmış aralığı oluşturacağız, aşağıda verilen adımları izleyin: -
- A40 ile E50 arasındaki tabloyu seçin
- CTRL + SHIFT + F3 klavye kısayolu
- 2'nin işaretini kaldırıyoruznd seçenek
- Ve Tamam'a tıklamadan önce, 1'in olduğundan emin olun.NS “Üst sıra” seçeneği seçili
- Şimdi her iki aralıkla da hazırız
Şimdi ebeveyn açılır listesini hazırlayacağız
- J42 hücresini seçin
- Veri Doğrulama iletişim kutusunu açın
- Daha sonra “Liste” seçtikten sonra, başlıklar için adlandırılmış aralığı almak için kaynakta F3 tuşuna basıyoruz. “Heading” e tıklıyoruz ve ardından Tamam'a tıklayıp enter'a basıyoruz. Şimdi J42'de ebeveyn listesine sahibiz
- Öğe Detayı listesini oluşturmak için hücreyi seçinK42
- ALT+D+L tuşlarına basarak Veri Doğrulama iletişim kutusunu açın
- Liste'yi seçin, ardından Kaynak sekmesinde aşağıdaki işlevi girin: -
- =KAYDIR(DOLAYLI(YEDEK($J$42," ","_")),,,COUNTA(DOLAYLI(YEDEK($J$42," ","_"))))
- Tamam'a tıklayın
Şimdi J42'de bir öğe seçin, diyelim ki “Item 01”i seçtik ve K42 açılır listesine bakın. Ve önceki 3 yöntem gibi burada da bağımlı listemiz var.
Ee başka? İlk örnekte, listeye herhangi bir ürün ekleyemiyordunuz, ancak burada herhangi bir yeni ürünü ekleyebilirsiniz. Diyelim ki bu öğeye yeni ürün ekleyelim. A45'e gidiyoruz ve “ETV-501 Prod 05” yazıp K42'ye dönüyoruz ve işte başlıyoruz. Görüyorsunuz, yeni ürün eklendi.
- Şimdi yeni öğenin altına birkaç ürün ekleyin
“Item 06”yı seçtiğimizde K42'ye gidiyoruz ve açılır listeye tıklıyoruz. Şaşırtıcı bir şekilde, aşağı açılan oka tıkladığımızda hiçbir şey olmuyor. Çünkü her şeyi dinamik yarattık ve tablo için dinamik aralık oluşturmayı unuttuk, bu yüzden ürünler alt listede görüntülenmiyor.
Bunun için farklı teknikler kullanmamız gerekiyor. Bunu yapmanın iki yöntemi vardır. Tabloyu oluşturabilir veya yalnızca OFFSET işlevini kullanabilirsiniz. Ve bir sonraki yöntemde OFFSET işlevini kullanacağız ve tablo aralığını genişletme hilesini de göreceğiz.
- Yani önce J43'e gidip “ALT + D + L” tuşlarına basıyoruz.
- “Liste” seçiyoruz ve ardından kaynakta F3'e basıyoruz ve “Başlık” seçiyoruz ve Tamam'a tıklıyoruz ve ardından enter'a basıyoruz.
- Şimdi K43'e geçiyoruz ve “Liste” seçtikten sonra “Source”a gidip aşağıdaki fonksiyona giriyoruz.
=OFFSET($A$40,1,KAÇINCI($J$43,$40:$40,0)-1,COUNTA(OFFSET($A$40,1, KAÇINCI($J$43,$40:$40,0)-1,1000) ,1)))
- Tamam'a tıklayın
Şimdi geri dönüyoruz ve J43 hücresinde “Madde 06'yı seçiyoruz ve K43'e dönüyoruz ve açılır oka tıklıyoruz. Ancak bu zaman listesi yeni ürün için eklediğimiz ürünleri gösterir. Ve ilk ürün olan “ETV-506 Prod 01”i seçiyoruz.
Her tür veri biçiminde farklı yöntemler kullanarak bağımlı açılır listeyi bu şekilde oluşturabilirsiniz.
Video: Microsoft Excel'de 5 Farklı Teknik Kullanarak Excel'de Bağımlı (Basamaklı) Açılır Liste Oluşturma
Kullanımına hızlı başvuru için video bağlantısına tıklayın. Yeni kanalımıza abone olun ve bizimle öğrenmeye devam edin!
Bloglarımızı beğendiyseniz, Facebook'ta arkadaşlarınızla paylaşın. Ayrıca bizi Twitter ve Facebook'ta da takip edebilirsiniz.
Sizden haber almayı çok isteriz, işimizi nasıl iyileştirebileceğimizi, tamamlayabileceğimizi veya yenileyebileceğimizi ve sizin için daha iyi hale getirebileceğimizi bize bildirin. Bize e-posta sitesinde yazın