Çözüldü Karma listeden bağlı açılır liste oluşturmak

K

kmlzdmr

Site Üyesi
Excel Versiyonu
Excel 2016
Excel Sürümü
64 Bit
Excel Dili
Türkçe
Merhaba, aşağıda kısmen anlatılan husus için ekli dosyaya göre birbirine bağlı açılır liste oluşturmam gerekli.
"veri" sayfasında B ve G sütunlarında bilgiler var. Satırlar Birincisi "Sınıf", İkincisi "grup" üçüncüsü "Hesap" olarak ayrıştırılmıştır.
"Açılırliste" sayfasındaki sütun başlıklarına göre seçimler yapılarak G H I sütunlarına açılır liste yapılmalı...

1. aşama G Sütunu G9 hücresi ve aşağısı için:
N sütunundaki N9=BLÇ kriterine göre G9 hücresine açılır liste olarak VERİ sayfasındaki ad tanımlamalarından "blalanı" olan B sütunundaki alandaki adlardan açılırliste sayfası 5. satırında bulunan SINIF (G5 hücresi) kriterini de işleme alarak VERİ sayfasındaki G sütununda alan başılığındaki (ad tanımlaması: "Alan_adı") sütunundaki SINIF lara denk gelen B sütunu satırlarındaki sınıfları süzerek g9 hücresine açılır liste yapılacak.
2. aşama G Sütunu H9 hücresi ve aşağısı için:
N sütunundaki N9=BLÇ kriterine göre H9 hücresine açılır liste olarak VERİ sayfasındaki ad tanımlamalarından "blalanı" olan B sütunundaki alandaki adlardan açılırliste sayfası 5. satırında bulunan GRUP (h5 hücresi) kriterini de işleme alarak VERİ sayfasındaki G sütununda alan başılığındaki (ad tanımlaması: "Alan_adı") sütunundaki Grup lara denk gelen B sütunu satırlarındaki grupları süzerek H9 hücresine açılır liste yapılacak.
3. aşama H Sütunu H9 hücresi ve aşağısı için:
N sütunun kriteri ve G sütunundaki Sınıf kriteri H sütunundaki Grup kriteri seçimine göre I9 ve devamı sütuna ilgili alanların altındaki VERİ sayfası G sütunundaki "Hesap" satırındaki alanlar süzülecek.

Ekli dosyada örnek yapmaya çalıştım... Veri sayfası B sütunundaha uzun ve daha fazla Sınıf, Grup ve Hesap lardan oluşmaktadır.
B sütununda ayrıca iki ayrı alan mevcuttur. "blalanı" ve "gtalanı" ...

lütfen konu için yardımcı olabilir misiniz?
 

Ekli dosyalar

Tarihe göre sırala Oylamaya göre sırala
Kodlar Sınıf - Grup - Hesap aralarındaki bağıntıları düzenlemekte...
Sizin yazdığınız Sınıf - Grup - Hesap bilgilerinde, hangi sınıfta hangi grubun ve hangi grubun içinde de hangi hesapların bulundukları tam olarak belirtilmediğinden dolayı kodlar yazıldı.
Aslında, sizin yazdığınız Sınıf - Grup ve Hesap kodlarında düzenleme (Örneğin Sınıf-1 - Grup-1.1 - Hesap-1.1.1 , Hesap-1.1.2 vs.... gibi) yapılarak, onlar da direkt olarak kullanılabilirdi. Formüllerde bu bağıntıların arattırılması sağlanabilirdi.

Tabloların da önce Sınıf ve Sınıfın altında Gruplar ve Grupların altlarında da Hesaplar şeklinde düzenli olması gerekir. Evet BLÇ Grup bu sıralamaya uyuyor ama GT Grup ta böyle sıralı bir durum söz konusu değil...

Kodların oluşturulması için formüle edilmesine gerek yok aslında... Önemli olan bağıntıların düzenlenmesi...

İlla ki, formüle edilsin derseniz de, sizin Sınıf - Grup - Hesap tanımlamalarınızdan hareketle ve de GT tablosundaki sıralama yapısının da düzenlenmesi ile formüle edilmiş şeklini ekteki dosyada bulabilirsiniz.....

Not : Ekteki dosyada, sayfadaki makro kodunda, kolon/satır ekleme/çıkarma olaylarında makro kodunda değişiklik yapılmadan çalışabilmesi amacıyla, Veri Doğrulama açılır listelerin bulundukları hücreler ad tanımlamasıyla adlandırıldı ve bu adlar makroda kullanıldı...
 

Ekli dosyalar

Oyla 0 Olumsuz oyla
Çok teşekkür ederim.
Tablo sistemden çekildiği hali ile verilmiştir. Sınıf Grup Hesap ayrımını manuel yazmıştım. (asıl dosyamda o da formülle buldum tabiki...)
ilk ekli dosyadaki sütun sırasına göre olursa sizin 5. penceredeki ekli dosya formül olan aşağıdaki formül nasıl düzenlenir?
Adlar B sütunu Alan G sütunu olduğunda f

sizin dizi formülünüz.
{=EĞER(G4="sınıf";"BLÇ";EĞER(G4="grup";"BLÇ_"&EĞERSAY($G$4:G4;"sınıf");EĞER(G4="hesap";"BLÇ_"&EĞERSAY($G$4:G4;"Sınıf")&SOLDAN(ARA(2;1/($G$4;G4="grup");$B$4:B4);1))))}


diğer sütun değişkiliğine uygun formül olup 1. pencerdeki ekli dosyay göre formül uyguladığımda 6. satırdan sonra değer hatası vermektedir. Acaba ARA fonksiyonundaki Ara(2;1/ durumundaki rakamlar mı değişecek....
{=EĞER(G4="sınıf";"BLÇ";EĞER(G4="grup";"BLÇ_"&EĞERSAY($G$4:G4;"sınıf");EĞER(G4="hesap";"BLÇ_"&EĞERSAY($G$4:G4;"Sınıf")&SOLDAN(ARA(2;1/($G$4;G4="grup");$B$4:B4);1))))}
 
Moderatörün son düzenlenenleri:
Oyla 0 Olumsuz oyla
Ne yapmak istiyorsunuz TAM olarak...
365 kullanıyorum.. O yazılan DİZİ formül değil. Standart formül... Siz dosyayı açtığınızda Dizi formül olarak görünüyor...

1624701521747.webp
 
Moderatörün son düzenlenenleri:
Oyla 0 Olumsuz oyla
Yapmış olduğunuz formülü 2. pencerde yapmış olduğunuz aşağıdaki formülü dosyadaki E sütununa uygulayabilir misiniz. Lütfen.
Ben uyguladığımda ve formülü aşağıya sürüklediğimde 6. satırda hata almaktayım. Bu hata #AD? hatası olup A, B... harflerinin olduğu satırlara denk gelmektedir.

sizin formül =EĞER(B4="Sınıf";"BLÇ";EĞER(B4="Grup";"BLÇ_"&EĞERSAY($B$4:B4;"Sınıf");EĞER(B4="Hesap";"BLÇ_"&EĞERSAY($B$4:B4;"Sınıf")&SOLDAN(ARA(2;1/($B$4:B4="Grup");$D$4:D4);1))))

2 pencerdeki ekli dosyanın e4 hücresine uygulanan formül {=EĞER(G4="sınıf";"BLÇ";EĞER(G4="grup";"BLÇ_"&EĞERSAY($G$4:G4;"sınıf");EĞER(G4="hesap";"BLÇ_"&EĞERSAY($G$4:G4;"Sınıf")&SOLDAN(ARA(2;1/($G$4;G4="grup");$B$4:B4);1))))}
 
Oyla 0 Olumsuz oyla
Formülü yazarken / aktarırken dikkat etmelisiniz.
..... SOLDAN(ARA(2;1/($G$4;G4="grup");$B$4:B4);1))))
aradaki ; (Noktalı virgül) değil : (iki nokta üst üste olmalı.... Yani bir referans aralığı olmalı....
Yani
..... SOLDAN(ARA(2;1/($G$4:G4="grup");$B$4:B4);1))))
 
Oyla 0 Olumsuz oyla
$G$4:G4
Burada : (İki Nokta üst üste) , kesintisiz bir referans aralığını belirtir.
: (iki nokta üst üste) yerine ; (noktalı virgül) yazdığınızda ise
formüllerin yapısına göre, ya formüldeki yapıdaki ikinci ( ya da bir sonraki) parametreyi ya da kesintili bir alandaki diğer referans hücresini / alanını göstermek için kullanılır.
Burada önemli olan formül (işlev) yapısıdır... ARA işlevi, KESİNTİLİ alanları kabul etmez. Alan KESİNTİSİZ olmalı (yani iki nokta üst üste...)... diğer yandan ise ; (Noktalı virgül) ikincil parametre gibi de değildir. Çünkü bir eşitlik söz konusu olduğundan, Excel onu KESİNTİLİ alan olarak algılamakta ve ARA işlevi de KESİNTİLİ alanda çalışmadığından HATA vermektedir....

HATA aldığınızda, formüllü hücreyi seçin ve Formüller >>> formülü Değerlendir den Değerlendir butonuna basarak adım adım değerlendirin ve HATA nın nereden geldiğini görebilirsiniz.
 
Oyla 0 Olumsuz oyla
koşula bağlı olarak son değeri bulmak için kullanılıyor...
ARA işlevinde aranan dizideki Hatalar yok sayılarak işlev görür.
ARA(2;1/($G$4:G4="grup");$B$4:B4)
örneğine bakarsanız, anlamı şu,
$G$4:G4="grup" $G$4:G4 aralığı Grup değerine eşit olduğunda 1 olmadığında ise 0 dır.
1/($G$4:G4="grup") ile elde edilen 1 ve 0 değerleri 1 bölü işlemi ile 1/1 = 1 ya da 1/0 = #SAYI/0! dan oluşan DİZİ elde edilir.
sonuçta da, ARA işlevi ile elde edilen 1 ve SAYI/0! lardan oluşan dizide 1 den büyük olan 2 değeri arattırılıyor. Diğer bir deyişle en son olan 1 değeri kaçıncı ise $B$4:B4 aralığındaki karşılık gelen değer bulunuyor...

Evet anlatım biraz karışık...
Örnekle açıklamak çok daha iyi...
Ekteki örneği inceleyin... Açıklama da mevcut..
1624705242648.webp
 

Ekli dosyalar

Moderatörün son düzenlenenleri:
Oyla 0 Olumsuz oyla
Sayın #52779 sizin çalışmayı ekteki dosyada uygulama yaptım.
Ancak,
J13 hücresinde GT Grup seçildikten sonraki seçimlerde L3:L12 aralığı ve dolayısı ile M3:M13 aralığı listelenmiyor?
J13 hücresinde BLÇ Grup seçildikten sonraki seçimlerde M3:M13 aralığı listelenmiyor?

GT Grup için L3 hücresindeki aşağıdaki formülde bulunan kırmızı işaretli küçük( deki formül sonucu sayı hatası vermekte.
=EĞER(SATIRSAY(L$3:L3)<=EĞERSAY($E$27:$E$363;SOLDAN($K$2;BUL(" ";$K$2)-1)&"_"&KAÇINCI($L$2;snf;0));İNDİS($A$27:$A$363;KÜÇÜK(EĞER($E$27:$E$363=SOLDAN($K$2;BUL(" ";$K$2)-1)&"_"&KAÇINCI($L$2;snf;0);SATIR($A$27:$A$363)-SATIR($A$27)+1);SATIRSAY(L$3:L3)));"")
ve yine
GT Grup için M3 hücresindeki aşağıdaki formülde bulunan kırmızı işaretli küçük( deki formül sonucu sayı hatası vermekte.
=EĞER(SATIRSAY(M$3:M3)<=EĞERSAY($E$27:$E$363;SOLDAN($K$2;BUL(" ";$K$2)-1)&"_"&KAÇINCI($L$2;snf;0)&SOLDAN($M$2;1));İNDİS($A$27:$A$363;KÜÇÜK(EĞER($E$27:$E$363=SOLDAN($K$2;BUL(" ";$K$2)-1)&"_"&KAÇINCI($L$2;snf;0)&SOLDAN($M$2;1);SATIR($A$27:$A$363)-SATIR($A$27)+1);SATIRSAY(M$3:M3)));"")

A ve E sütunlarındaki veri tabanında sıkıntı mı var? Bulamadım.

A sütundan yazılı değerlerin baş taraflarında boşluk var. Boşluğu kaldırsamda formül çalışmadı.
E sütundaki kodlama malum formülle gelmektedir.
saatlerdir hatamı/hatayı çözemedim.
Lütfen yardımcı olur musunuz?
 

Ekli dosyalar

Oyla 0 Olumsuz oyla
Yazılan formüller gönderdiğiniz ilk dokuman göz önüne alınarak hazırlandı.
Gerçeği yansıtmayacağı aklıma bile gelmedi....

Evet listenizde Grup ve Hesap kriterlerinden önce BOŞLUKlar vardı. O nedenle de başka bir dosya hazırlayıp, özellikle de Örnek dokuman olarak mesajımı gönderdim.

Şu an için maalesef vakit ayıramayacağım. İlk müsait olduğumda bakmaya çalışırım...

Sizden ricam da GERÇEK dokumanınız ne ise, o nu ekleyin ki, defalarca işlem yapmayalım derim.
 
Oyla 0 Olumsuz oyla
52779' Alıntı:
Yazılan formüller gönderdiğiniz ilk dokuman göz önüne alınarak hazırlandı.
Gerçeği yansıtmayacağı aklıma bile gelmedi....

Evet listenizde Grup ve Hesap kriterlerinden önce BOŞLUKlar vardı. O nedenle de başka bir dosya hazırlayıp, özellikle de Örnek dokuman olarak mesajımı gönderdim.

Şu an için maalesef vakit ayıramayacağım. İlk müsait olduğumda bakmaya çalışırım...

Sizden ricam da GERÇEK dokumanınız ne ise, o nu ekleyin ki, defalarca işlem yapmayalım derim.
Genişletmek için tıklayın ...
Sağolun sabırla bekliyorum.
Sizin yaptığınız kitaplarda boşluk koyduğumda sorun olmuyor. Kontrol edebilirsiniz.
 
Oyla 0 Olumsuz oyla
Yanıt için giriş yapmanız veya üye olmanız gerekir.

Benzer Konular Popüler Daha Fazlası

Konuyu başlatan
K
kmlzdmr
Site Üyesi
Katılım
Konum
ANKARA
Konu Bilgi
Durum
Çözüldü 
Forum
Genel Excel Soruları
Başlangıç tarihi
Son yanıt tarihi
Cevaplar
21
Üst