Çözüldü Kriterli açılır liste kutusu liste oluşturma

K

kmlzdmr

Site Üyesi
Excel Versiyonu
Excel 2016
Excel Sürümü
64 Bit
Excel Dili
Türkçe
Merhaba,
Ekli dosyaya kriter seçimine göre açılır liste ve kriterlere uyanların listesinin getirilmesi ihtiyacım var. Lütfen yardımcı olabilir misiniz?
İki ayrı hücreye formül gerekli:
-H3 hücresindeki "Tür1" ve/veya J3 hücresindeki "Tür2" seçimli kriterlere göre "veri" sayfasındaki isim sütunu süzülmüş değerlerini Sonuç sayfası F3 hücresine açılır liste olarak getirilmesi sağlanacak.
-3. satırdaki Tür1 yada Tür2 hücrelerindeki alan boş olursa boş olan kriterler tamamı listelenecek
-O3 hücresinde ise Kriterlere göre O3 hücresinden aşağı kriterlerle eşleşenlerin aşağıya doğru listelenecek.
-Tabidir ki kriterlerde olmayanlar listelenmeyecek…
 

Ekli dosyalar

Tarihe göre sırala Oylamaya göre sırala
Merhabalar O3 hücresi için aşağıdaki formülü kullanabilirsiniz. Sonra Ad Tanımlama ile Boşluksuz Veri doğrulama yaparak istediğiniz sonuçlara ulaşabilirisiniz. Dosyanız ektedir. İnceleyiniz.

[formul]=EĞERHATA(EĞER(VE($J$3="";$H$3="");İNDİS(VERİ!$C$11:$C$95;SATIR(A1));EĞER($J$3="";İNDİS(VERİ!$C$11:$C$95;TOPLAMA(15;6;SATIR(VERİ!$C$11:$C$95)-SATIR(VERİ!$C$11)+1/(SONUÇ!$H$3=VERİ!$AC$11:$AC$95);SATIR(A1)));EĞER($H$3="";İNDİS(VERİ!$C$11:$C$95;TOPLAMA(15;6;SATIR(VERİ!$C$11:$C$95)-SATIR(VERİ!$C$11)+1/(SONUÇ!$J$3=VERİ!$BC$11:$BC$95);SATIR(A1)));İNDİS(VERİ!$C$11:$C$95;TOPLAMA(15;6;SATIR(VERİ!$C$11:$C$95)-SATIR(VERİ!$C$11)+1/((SONUÇ!$H$3=VERİ!$AC$11:$AC$95)*(SONUÇ!$J$3=VERİ!$BC$11:$BC$95));SATIR(A1))))));"")[/formul]
 

Ekli dosyalar

Oyla 0 Olumsuz oyla
Ben de alternatif cevap vereyim.

-- Sn. @KMLZDMR 'in EĞERHATA işleviyle ilgili sıkıntısını bildiğim için bu formülü veriyorum.
Birinci formülü O2 hücresine (liste başlığı hücresi biliyorum, formülün konumunun burası olması da şart değil aslında) uygulayın.
(diğer formüllerdeki O2 yerine bu formülü kullanmayı tercih ederseniz O2 hücresini kullanmanıza gerek kalmaz)
Formülün sonucu, hem F3 veri doğrulama listesinin eleman sayısı, oluşacak listenin eleman sayısıdır.
-- Esas listeleme formülü: ikinci formülü O3 hücresine dizi formülü olarak uygulayıp aşağı doğru (kaynaktaki dolu satır adeti kadar) kopyalayın,
-- Veri doğrulama formülü: F3 hücresindeki veri doğrulama formülü olarak üçüncü formülü uygulayın.

Kod: 
You must log in to view content
(1 satır)
Kod: 
You must log in to view content
(1 satır)
Kod: 
You must log in to view content
(1 satır)
 
Oyla 0 Olumsuz oyla
Ömer BARAN' Alıntı:
Ben de alternatif cevap vereyim.

-- Sn. @KMLZDMR 'in EĞERHATA işleviyle ilgili sıkıntısını bildiğim için bu formülü veriyorum.
Birinci formülü O2 hücresine (liste başlığı hücresi biliyorum, formülün konumunun burası olması da şart değil aslında) uygulayın.
(diğer formüllerdeki O2 yerine bu formülü kullanmayı tercih ederseniz O2 hücresini kullanmanıza gerek kalmaz)
Formülün sonucu, hem F3 veri doğrulama listesinin eleman sayısı, oluşacak listenin eleman sayısıdır.
-- Esas listeleme formülü: ikinci formülü O3 hücresine dizi formülü olarak uygulayıp aşağı doğru (kaynaktaki dolu satır adeti kadar) kopyalayın,
-- Veri doğrulama formülü: F3 hücresindeki veri doğrulama formülü olarak üçüncü formülü uygulayın.

Kod: 
You must log in to view content
(1 satır)
Kod: 
You must log in to view content
(1 satır)
Kod: 
You must log in to view content
(1 satır)
Genişletmek için tıklayın ...
Sayın Ömer BARAN üstadım,
Sizin uygun görmediğiniz bir husus oluştu. (kusura bakmayın)
C3 için veri doğrulama içine uygulanacak =KAYDIR($O$3;;;$O$2; ) formül O sütunundaki formülleri yok sayarak açılır listeye kriterleri süzerek getirmek için formül nasıl yapılır?
 
Oyla 0 Olumsuz oyla
KMLZDMR' Alıntı:
Sayın Ömer BARAN üstadım,
Sizin uygun görmediğiniz bir husus oluştu. (kusura bakmayın)
C3 için veri doğrulama içine uygulanacak =KAYDIR($O$3;;;$O$2; ) formül O sütunundaki formülleri yok sayarak açılır listeye kriterleri süzerek getirmek için formül nasıl yapılır?
Genişletmek için tıklayın ...
Merhabalar veri doğrulamada dinamik olarak boşluksuz olarak listelemek için ad tanımlamaya formülü böyle yazabilirsiniz. Ekli dosyada olduğu için incelersiniz diye yazmak istememiştim.

[formul]=KAYDIR(SONUÇ!$O$2;1;;TOPLA.ÇARPIM(--(UZUNLUK(SONUÇ!$O$3:$O$30)>0));1)[/formul]
 
Oyla 0 Olumsuz oyla
Sayın #mehmet2300
6. pencerede yazdığım / anlatmaya çalıştığım husus; Sonuç sayfasında O sütunu hiç olmayacak/kullanılmayacak.
Bu durumda C3 hücresi için ad tanımlama / veri doğrulama formülü nasıl yapılmalı ki H3, J3 kriterlerine göre C3 hücresinde açılır liste halinde kriterlere uyanlar gelsin!
 
Oyla 0 Olumsuz oyla
Sn. @mehmet2300 bir şey diyorsa boşuna değildir. (y)
Bence de kendisinin fikri aynen doğrudur.

Mesele, o listenin o sayfada olup olmamasıysa, formülü veri tablosunun olduğu sayfada uygulayıp, listeyi orada oluşturup,
asıl sayfanızda bu alana atıf yaparak veri doğrulamayı kullanabilirsiniz.

Ya da, listeleme olmadan makroyla veri doğrulama listesi oluşturmak kesin çözüm olur.
 
Oyla 0 Olumsuz oyla
Ben yine de makro ile çözüm önerisinde bulunayım. Forumda benzer yöntem var mıydı net hatırlamıyorum. Yoksa da örnek olsun.

Aşağıdaki kodu SONUÇ sayfasının kod bölümüne yapıştırın (module değil).
Böylece, H3 ve J3'de değişiklik olduğunda liste yenilenir. Denemeler yaparsınız.

Kırmızı işaretlediğim kısım, H3 ve J3'te değişiklik olduğunda F3 içeriğini boşaltır.
Bu silme işlemi, elbette şart değil, kodun bu kısmını silerek de kullanabilirsiniz ama bence mantıklı olanı bu.

VBA: 
You must log in to view content
(13 satır)
 
Oyla 0 Olumsuz oyla
Sayın Ömer Baran üstadım,
F3 hücresindeki veri doğrulama / liste alanını ve O sütunundaki formülleri sildim.
F5 leri (üç adet) f3 yaptım.
Sonuç sayfasında "Geliştirici" sekmesinde "Visual Basic" açıp kodu yapıştırdım.
H3 J3 içeriğinde değişiklik yaptığımda listelenme gelmedi.
makroyu nasıl aktif edeceğim?
Nerede hata yapıyorum?
 

Ekli dosyalar

  • Adsız.webp
    Adsız.webp
    171.2 KB · Görüntüleme: 3
Oyla 0 Olumsuz oyla
Ömer BARAN' Alıntı:
Cevabımı iyi okumamışsınız sanırım.
Genişletmek için tıklayın ...
Üstadım,
Sayfa2(SONUÇ) sayfasında ALT+F11 / Sonuç sayfasında "Geliştirici" sekmesinde "Visual Basic" açıp kodu yapıştırdım.
modül açmadım. Başkaca nelere dikkat etmeliyim?
Diğer yandan, makronun çalışması H3 ve/veya J3 içeriğinde değişiklik yapınca mı aktif oluyor?
Makronun yazılı olduğu üst alan başlığı Worksheet sağ tarafı Change yazılı

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H3, J3")) Is Nothing Then Exit Sub
v = Sheets("VERİ").Range("A11:BH" & Sheets("VERİ").Cells(Rows.Count, 3).End(3).Row).Value
[F3].Validation.Delete: [F3].Value = ""
With CreateObject("Scripting.Dictionary")
For s = 1 To UBound(v)
If ([H3] = "" Or v(s, 29) = [H3]) And ([J3] = "" Or v(s, 55) = [J3]) Then
If Not .Exists(v(s, 3)) Then: .Add v(s, 3), ""
End If
Next
[F3].Validation.Add Type:=xlValidateList, Formula1:=Join(.Keys, ",")
End With
End Sub
 
Oyla 0 Olumsuz oyla
@KMLZDMR
Alt taraftan, açılır listenin olduğu, işlem yapılacak sayfanın adına (SONUÇ) fareyle sağ tıklayıp KOD GÖRÜNTÜLEyi seçin,
(VBA ekranının ilgili yeri kendiliğinden açılır zaten) VBA ekranında sağdaki boş alana kodu yapıştırın. İşlem tamam.

Kod H3 ve/veya J3'te değişiklik olduğunda işlemi yapar.
Kodda verilerin alınacağı sayfanın adı da kullanılıyor. Gerçek belgedeki sayfa adı farklıysa kodda ilgili kısmı değiştirmeniz gerekir.
Ayrıca artık belgeniz makro içeren belge olduğundan, *.xls veya *.xlsm yahut *.xlsb uzantılı olarak kaydedilmelidir.

.
 
Oyla 0 Olumsuz oyla
Kodda, dosyada bir sorun yok.
Sadece 1 defaya mahsus, H3 veya J3 hücresinde F2+ENTER yapılması yeterli, sonrası otomatik akacaktır.

Belki sadece H3 ve J3 seçimine uyan hiç veri olmaması seçeneğini göz önüne alarak;
kırmızı satırın üstüne yeşil olan satırı ekleyin. (sonuç yoksa en azından "YOK" ibaresi görünsün.
YOK ibaresi görünsün istemiyorsanız çift tırnak arasındaki YOK ibaresini silersiniz (liste boş gelir).

VBA: 
You must log in to view content
(2 satır)
 
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
Excel Formül Soruları
Başlangıç tarihi
Son yanıt tarihi
Cevaplar
21
Üst