Excel Forum

Excel Forum (http://www.excel.web.tr/index.php)
-   Beyin Fırtınası (http://www.excel.web.tr/forumdisplay.php?f=142)
-   -   Veri tabanından seçilen kriterlere göre sorgulama (http://www.excel.web.tr/showthread.php?t=30754)

Levent Menteşoğlu 19-04-2007 02:23

Veri tabanından seçilen kriterlere göre sorgulama
 
1 Eklenti(ler)
Değerli Üyelerimiz

Aslında sorgulama konusu ile ilgili forumumuzda çok sayıda örnek mevcut, buna rağmen tüm çözümlerin birarada bulunması amacıyla bu konuyla ilgili bir soru hazırladım. VBA veya Fonksiyonlarla geliştireceğiniz çözümleri paylaşmanızı rica ederim.

Soru: Aşağıdaki resimde görüldüğü gibi toplam 50.000 satır veri içeren 5 sütunlu (A-E arası) bir veri tabanımız mevcuttur. Bu veri tabanından, G2-K2 arasındaki hücrelere yazılan kriterlere uygun veriler sorgulanarak rapor sayfasında listelenecektir. Tüm kriterlerin doldurulması zorunlu olmayacak ve sadece doldurulan kriterlere göre sorgulama yapılabilecektir.

Sorgulamada kullanılan dikkat edilecek özelliklerden önemli olan ikisi şöyledir,

1-Başlangıç tarihi eşit veya büyük, Bitiş tarihi ise eşit veya küçük şeklinde sorgulama yapılacaktır. Herhangi birisi boş ise diğerinin kriteri geçerlidir ayrıca her ikiside boş ise tüm tarihler dikkate alınacaktır.

2-Miktar, büyük veya eşit şeklinde sorgulanacaktır. Eğer boş bırakılırsa tüm miktarlar dikkate alınacaktır.

http://www.excel.web.tr/derres/leventm/arama.JPG

Not: Veri sayısını özellikle fazla miktarda tuttum. Bundaki amacımda tasarlanacak çözümde sorgulama hızınıda dikkate alabilmenizdir.

hamitcan 19-04-2007 12:28

Advanced filter yöntemiyle bir örnek yaptım. Yalnız burada Başlangıç ve Bitiş tarihlerini Geliş tarihi olarak değiştirmek gerekiyor.

Kod:

Private Sub CommandButton1_Click()
  [b1:e50001].AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[g1:k2], CopyToRange:=Sayfa1.[b1:e1], Unique:=False
End Sub


Korhan Ayhan 19-04-2007 13:14

1 Eklenti(ler)
Selamlar,

Bu soru için excelin yerleşik işlevlerinden filtreleme yönteminin en uygun çözüm olduğunu düşünüyorum.

Ekte bu yöntem ile ilgili çözümlemeyi bulabilirsiniz.


Uygulanan kod ve açıklamaları;

Kod:

Sub ÖZET_RAPOR()
    Application.ScreenUpdating = False
    Set SL = Sheets("liste")
    Set SR = Sheets("rapor")
    Kriter1 = SL.[G2]
    Kriter2 = SL.[H2]
    Kriter3 = SL.[I2]
    Kriter4 = SL.[J2]
    Kriter5 = SL.[K2]
    SR.Columns("A:E").Clear
    SL.Select
    [A1].Select
    Selection.AutoFilter
    If Kriter1 = "" Then
    Selection.AutoFilter Field:=2
    Else
    Selection.AutoFilter Field:=2, Criteria1:=Kriter1
    End If
    If Kriter2 = "" Then
    Selection.AutoFilter Field:=3
    Else
    Selection.AutoFilter Field:=3, Criteria1:=Kriter2
    End If
    If Kriter3 = "" And Kriter4 = "" Then
    Selection.AutoFilter Field:=4
    ElseIf Kriter3 <> "" And Kriter4 = "" Then
    Selection.AutoFilter Field:=4, Criteria1:=">=" & CLng(CDate(Kriter3))
    ElseIf Kriter3 = "" And Kriter4 <> "" Then
    Selection.AutoFilter Field:=4, Criteria1:="<=" & CLng(CDate(Kriter4))
    ElseIf Kriter3 <> "" And Kriter4 <> "" Then
    Selection.AutoFilter Field:=4, Criteria1:=">=" & CLng(CDate(Kriter3)), Operator:=xlAnd, Criteria2:="<=" & CLng(CDate(Kriter4))
    End If
    If Kriter5 = "" Then
    Selection.AutoFilter Field:=5
    Else
    Selection.AutoFilter Field:=5, Criteria1:=">=" & Kriter5
    End If
    SL.[A1].CurrentRegion.Copy
    SR.Select
    [A1].Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("A:E").EntireColumn.AutoFit
    [A1].Select
    SL.Select
    Selection.AutoFilter
    SR.Select
    Application.ScreenUpdating = True
    SAY = WorksheetFunction.CountA(SR.[A2:A65536])
    If SAY = 0 Then
    MsgBox "VERDİĞİNİZ KRİTERLERE UYGUN KAYIT BULUNAMAMIŞTIR.", vbExclamation, "DİKKAT !"
    SL.Select
    Exit Sub: End If
    If SAY > 0 Then MsgBox "VERDİĞİNİZ KRİTERLERE UYGUN  " & Format(SAY, "#,##0") & "  ADET KAYIT BULUNMUŞTUR.", vbInformation
End Sub
 
'UYGULANAN KODUN AÇIKLAMALARI
'1. SATIR > MAKROMUZA İSİM VERİYORUZ.
'2. SATIR > İŞLEMLER YAPILIRKEN GÖZÜ YORMAMAK İÇİN EKRAN HAREKETLERİNİ PASİF YAPIYORUZ.
'3-4. SATIR > SAYFA İSİMLERİNİ KISALTILMIŞ SABİTLERE ATIYORUZ.
'5-9. SATIR > RAPOR ALMAK İÇİN KULLANILACAK HÜCRELERİNDEKİ [G2:K2] DEĞERLERİ DEĞİŞKENLERE ATIYORUZ.
'10. SATIR > RAPOR SAYFASINDAKİ DAHA ÖNCE ALINAN RAPORA AİT VERİLERİ SİLİYORUZ.
'11. SATIR > LİSTE İSİMLİ SAYFAYI SEÇİYORUZ.
'12. SATIR > LİSTE İSİMLİ SAYFANIN A1 HÜCRESİNİ SEÇİYORUZ.
'13. SATIR > DAHA ÖNCE UYGULANAN FİLTREYİ KALDIRIYORUZ.
'14-18. SATIR > LİSTE İSİMLİ SAYFADAKİ G2 (ÜRÜN ADI) HÜCRESİNİN BOŞ OLUP OLMADIĞI KONTROL EDİLİYOR.EĞER BOŞSA TÜM ÜRÜNLER RAPORA DAHİL EDİLİYOR.
'19-23. SATIR > LİSTE İSİMLİ SAYFADAKİ H2 (RENK) HÜCRESİNİN BOŞ OLUP OLMADIĞI KONTROL EDİLİYOR.EĞER BOŞSA TÜM RENKLER RAPORA DAHİL EDİLİYOR.
'24-32. SATIR > LİSTE İSİMLİ SAYFADAKİ I2 (BAŞLANGIÇ TARİHİ) VE J2 (BİTİŞ TARİHİ) HÜCRELERİNİN BOŞ OLUP OLMADIĞI KONTROL EDİLİYOR.EĞER BOŞSA TÜM TARİHLER RAPORA DAHİL EDİLİYOR.
'33-37. SATIR > LİSTE İSİMLİ SAYFADAKİ K2 (MİKTAR) HÜCRESİNİN BOŞ OLUP OLMADIĞI KONTROL EDİLİYOR.EĞER BOŞSA TÜM MİKTARLAR RAPORA DAHİL EDİLİYOR.
'38. SATIR > LİSTE İSİMLİ SAYFADAKİ KRİTERLERE UYGUN VERİLER SÜZÜLÜP SÜZÜLMÜŞ ALAN KOPYALANIYOR.
'39. SATIR > RAPOR İSİMLİ SAYFAYI SEÇİYORUZ.
'40. SATIR > RAPOR İSİMLİ SAYFANIN A1 HÜCRESİNİ SEÇİYORUZ.
'41. SATIR > KOPYALANAN VERİLER A1 HÜCRESİNDEN İTİBAREN YAPIŞTIRILIYOR.
'42. SATIR > KOPYALAMA İŞLEMİ HALA AKTİF HALDE OLDUĞUNDAN PASİF HALE GETİRİLİYOR.
'43. SATIR > RAPOR İSİMLİ SAYFADAKİ [A:E] SÜTUNLARI EN UYGUN GENİŞLİK AYARINA GÖRE AYARLANIYOR.
'44. SATIR > RAPOR İSİMLİ SAYFANIN A1 HÜCRESİNİ SEÇİYORUZ.
'45. SATIR > LİSTE İSİMLİ SAYFAYI SEÇİYORUZ.
'46. SATIR > UYGULANAN FİLTREYİ KALDIRIYORUZ.
'47. SATIR > RAPOR İSİMLİ SAYFAYI SEÇİYORUZ.
'48. SATIR > DAHA ÖNCE İŞLEMLER YAPILIRKEN GÖZÜ YORMAMAK İÇİN EKRAN HAREKETLERİNİ PASİF YAPMIŞTIK.BU İŞLEMİ TEKRAR AKTİF HALE GETİRİYORUZ.
'49. SATIR > RAPOR İSİMLİ SAYFADAKİ [A2:A65536] ARALIĞINDAKİ DOLU HÜCRELERİ SAYDIRIP SIFIR DEĞERİNE EŞİT OLUP OLMADIĞINI SORGULUYORUZ.BU DEĞERİ SAY İSİMLİ DEĞİŞKENE ATIYORUZ.
'50-51. SATIR > BU SORGU SONUCU SIFIR İSE KULLANICIYA KRİTERLERE UYGUN KAYIT OLMADIĞINA DAİR BİLGİLENDİRME MESAJI VERİYORUZ.
'52. SATIR > UYGUN KAYIT BULUNAMADIĞI İÇİN LİSTE İSİMLİ SAYFAYI SEÇİYORUZ.
'53. SATIR > SORGU SONUCU SIFIR ÇIKTIĞI İÇİN MAKROYU SONLANDIRIYORUZ.
'54. SATIR > 49. SATIRDAKİ SORGU SONUCU SIFIRDAN BÜYÜKSE KODLAR BU SATIRA OTOMATİK OLARAK GEÇECEKTİR.KULLANICIYA VERDİĞİ KRİTERLERE UYGUN KAYIT SAYISINI BELİRTEN BİLGİLENDİRME MESAJI VERİYORUZ.
'55. SATIR > MAKROMUZU SONLANDIRIYORUZ.


AS3434 19-04-2007 14:05

Sorunun çözümü için fonksiyonlarla biraz uğraştım ama daha 10.000. satırda dosya 13 MB'a çıktı ve hesaplama 1 dakikayı geçti. Tabi bunda seçmiş olduğum fonksiyonların ve hesaplama yönteminin de etkisi büyük. En mantıklı çözüm VBA ile yapılması.

Zeki Gürsoy 19-04-2007 14:47

1 Eklenti(ler)
Hem ADO hem de DAO ile yapılmış örnek.
Ek olarak sıralama kriteri ekledim.

AS3434 19-04-2007 17:10

1 Eklenti(ler)
Renk sütunu da seçeneğe dahil edilmiş, 1000 satırlık veri ile oluşmuş dosyanın, fonksiyonla yapabildiğim çözümü ekte.
Yalnız, dediğim gibi satır sayısı artınca dosya ebatları yükseliyor ve hesaplama uzun zaman alıyor.

Necdet Yeşertener 19-04-2007 23:30

1 Eklenti(ler)
Bende birşeyler yapmaya çalıştım, amacım yanıt vermek değil, öğrenmek içindir.

Korhan Ayhan 20-04-2007 10:27

Selamlar,

Arkadaşlar eklediğim dosyada kullanıcıyı bilgilendirmek için kullandığım mesajda problem vardı. Bu hatayı giderip dosyayı güncelledim.

acemiler 20-04-2007 11:54

Merhaba
 
Nejdet Yesersener'in verdiği cevapta veri süzülüp başka bir sayfaya aktarılıyor. Peki Aranacak veri bir satırda değilde alt alta 50 satırda değişik aranacak veriler olsa bu 50 satırdaki verileri bulup süzebilir mi? Teşekkürler

Korhan Ayhan 20-04-2007 12:07

Selamlar,

Sn. acemiler sorularınızı bu başlık altına değilde makrolar kısmına bu linki ekleyerek sorarsanız daha faydalı olacaktır. Zira bu bölüme sorulan soruyla ilgili cevapların yazılması daha uygun olacaktır.


Saat 18:10

Powered by vBulletin Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.