..::.. Çok Sütunlu Sayfadaki Verilere Gelişmiş Filtre Uygulanması ..::..

[Ömer BARAN]

Merhabalar, konu gelişmiş filtre makrosu ile ilgilidir.
Örneklere baktım ancak kendime uyarlayamadım.

Ekteki belgede HAM isimli sayfanın A7'den başlayarak 7'nci satırı başlık satırı olup, dolu sütunlar DQ sütununa kadar (şu an bazı sütunlarda veri yok) devam ediyor (yani 121 sütun var). Veriler 8'inci satırdan itibaren yer alıyor.

Bu sayfanın A4 hücresindeki veri doğrulama yöntemiyle A7Q7 aralığını (yani tablomun başlıkları) veri doğrulama listesi kaynağı olarak kullanıyorum.

A4 hücresindeki değerin (veri doğrulama yoluyla seçtiğim sütun başlığı) değişikliği ile tetiklenen ve seçtiğim başlığın bulunduğu sütundaki verilerin benzersiz ve alfabetik olarak BENSERSİZ adlı sayfanın ikinci satırından itibaren (tabi birinci satırda başlıklar olacak şekilde) sıralanması lazım.

Maksadım hem gereksiz sütunları aktarmamış olmak ve hem de aktardığım sütunlarda alfabetik benzersiz sıralama yapmış olmak.
A sütunundan itibaren ilk boş sütuna aktarma yapılmış olması ve daha önce aktarılan bir sütun adını seçmem halinde de aktarılan verilerin silinerek yenilerinin aktarılması doğru olacaktır.

İlgilenecek arkadaşlara şimdiden teşekkürler.

 

[Muhammet Okumuş]

Dosyayı inceleyin.
A4 değişirse kod çalışır.

 

[Ömer BARAN]

.................................

Maksadım hem gereksiz sütunları aktarmamış olmak ve hem de aktardığım sütunlarda alfabetik benzersiz sıralama yapmış olmak.
A sütunundan itibaren ilk boş sütuna aktarma yapılmış olması ve daha önce aktarılan bir sütun adını seçmem halinde de aktarılan verilerin silinerek yenilerinin aktarılması doğru olacaktır.
.................................

Dosyayı inceleyin.
A4 değişirse kod çalışır.

Sayın Okumuş ilgi ve destek tekrar teşekkürler, sizi bugün çok meşgul ettim ama yukarıdaki kırmızı bölüm gerçekleşmiyor.
Örneğin önce "Bölüm Adı" başlığını seçip veriyi aktardıktan sonra "Birim Adı" başlığını seçtiğimde yine A sütununa veri yazılıyor, yani bir önceki veriler gidiyor.
Kırmızı bölümdeki maksadımı sanırım tam anlatamadım.
Kodun yapması gereken, daha önce "Bölüm Adı"nı seçip aktardığımıza ve bu veriler de A sütununa aktarıldığına göre; "Birim Adı"nı seçtiğimde bu verilerin bir sonraki sütun olan B sütununa aktarılması lazım idi.
Ancak daha evvel seçtiğim ve A sütununa aktarılan veri başlığını ( Bölüm Adı) tekrar seçersem, bu verinin bulunduğu sütunu bulup (A sütunu) üstüne yazması gerekiyordu.
Umarım izah edebilmişimdir. Bilemiyorum ama; belki daha evvel aktarılan başlıkların hangileri olduğunun bir listesini HAM sayfasında tutmanın işlemi kolaylaştırması mümkün olabilir.

 

[Muhammet Okumuş]

Peki Bölüm adında veri yoksa o birim adını kopyalasın mı?

 

[Ömer BARAN]

Elbette sayın Okumuş, verinin olmaması başlığın yok sayılması sonucunu doğurmaması lazım. Neticede tablom sürekli veri işlenecek olan bir tablo, satır sayısına ilişkin bir üst limit gerekli sanırım ama, onu da yüksek bir sayı kullanarak aşacağımı düşünüyorum.
Aktarılan başlıkları ilk veri doğrulama listesinin dışına çıkartıp, yeni bir hücreye aktarılanlar için de bir veri doğrulama (örneğin A5) yöntemi belki işe yarayabilir. Böylece ilk defa aktarılacaklar A4'teki veri doğrulamadan, aktarılmış başlıklardaki güncelleme ise örneğin A5'deki veri doğrulama üzerinden yapılabilir.
Bu söylediğim köklü değişikliği gerektiriyorsa yok sayabilirsiniz, belki fikir olur diye söylüyorum.

 

[Muhammet Okumuş]

Bu kodu bir deneyin.

Sub Benzersiz()

adet = WorksheetFunction.CountIf(Sheets("BENZERSİZ").Range("A1Q1"), Range("A4"))
If adet = 0 Then
x = WorksheetFunction.CountA(Sheets("BENZERSİZ").Range("A2Q2")) + 1
Else
x = WorksheetFunction.Match(Range("A4"), Sheets("BENZERSİZ").Range("A1Q1"), 0)
End If

süt = WorksheetFunction.Match(Range("A4"), Range("A7Q7"), 0)
Range(Cells(7, süt), Cells(1000, süt)).Copy Sheets("BENZERSİZ").Cells(1, x)
Sheets("BENZERSİZ").Select

Range(Cells(1, x), Cells(10000, x)).RemoveDuplicates Columns:=1, Header:=xlYes
Range(Cells(1, x), Cells(10000, x)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Range(Cells(2, x), Cells(10000, x)).Sort Cells(2, x), xlAscending

End Sub

 

[Ömer BARAN]

Sayın Okumuş listelemenin alfabetik olmasını düşünme nedenim kolay erişmek.
Aslında bir kaç başlık için bana alfabetik değil SIKLIK kriterine göre sıralamam gerekecek ama onu sanırım formüllerle hallederim. Kod üzerinden bir kısmını alfabetik, bir kısmını SIKLIK kriterine göre aktarmak sanırım sıkıntı yaratacaktır. Ya da A4'teki veri doğrulamanın yanındaki B4 hücresine de sıralama kriterini yazsam veya veri doğrulama ile seçsem ve başlığı ondan sonra seçsem en ideali bu olur tabi. Yeni sıkıntı yaratmak istemem ama.

NOT: Siz yeni kodu gönderirken sanırım aynı anda bu mesajı yazmışım.

 

[Muhammet Okumuş]

Sub Benzersiz()
Set b = Sheets("BENZERSİZ")
Set h = Sheets("HAM")
adet = WorksheetFunction.CountIf(b.Range("A1Q1"), h.Range("A4"))
If adet = 0 Then
x = WorksheetFunction.CountA(b.Range("A2Q2")) + 1
Else
x = WorksheetFunction.Match(h.Range("A4"), b.Range("A1Q1"), 0)
End If

süt = WorksheetFunction.Match(h.Range("A4"), h.Range("A7Q7"), 0)
h.Range(h.Cells(7, süt), h.Cells(1000, süt)).Copy b.Cells(1, x)
b.Select

b.Range(b.Cells(1, x), b.Cells(10000, x)).RemoveDuplicates Columns:=1, Header:=xlYes
b.Range(b.Cells(1, x), b.Cells(10000, x)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
b.Range(b.Cells(2, x), b.Cells(10000, x)).Sort b.Cells(2, x), xlAscending

End Sub

Ham sayfasına

Private Sub Worksheet_selectionChange(ByVal Target As Range)
If Intersect(Range("A7Q7"), Target) Is Nothing Then Exit Sub
If ActiveCell = "" Then Exit Sub
Range("A4") = ActiveCell.Value
Benzersiz
End Sub

Kodunu kopyalayın. Tek tıklama ile işlemleri daha hızlı yaparsınız.

 

[Ömer BARAN]

Merhaba kod alfabetik sıralama bakımından sorunsuz çalışıyor.
Sıralama kriteri konusunda bir şeyler yapmak mümkün müdür acaba?
Şöyle ki; en azından "UYRUK", "BÖLÜM" ve "BİRİM" sütunlarının tekrarlanma kriterine göre en çok tekrarlanan en üstte olacak şekilde sıralanması gerekecek, bu durumda şayet kodda değişiklik mümkün olup olmadığını söylerseniz, ilave sütunlarla formüller kullanarak bu sıralamayı elde edeceğim.
Kolay gelsin.

 

[Muhammet Okumuş]

Peki o sıklık değerlerini nerede belirteceksiniz?

 

[Ömer BARAN]

Sayın Okumuş, size zhmet veriyorum, kusura bakmayınız.
Değer olarak değil, sıralamada kullanılması yeterli.
Yani örneğin bölüm adını aktarırken kendi sütunundaki tekrarlanma sayısı en fazla olan en üstte olacak şekilde aktarmaktan bahsediyorum.
Alfabetik dizilim, veri doğrulama kaynağı olarak kullanınca anlamlı oluyor. Oysa neticede ben en sık kullanılan ilk 10 bölümü ayrı ayrı sütun başlığı yapıp (11'inci sütuna da DİĞER başlığında toplayıp, UYRUK sütunundaki yine en çok tekrarlanan ilk 10 uyruğu satır başlığı yapacağım, 11'inci satıra da DİĞER diyerek bir tablo oluşturacağım.
Bundan sonra da Örneğin en çok tekrarlanan uyruk Azerbaycan ve en çok tekrarlanan bölüm Çocuk Acil, bu ikisinin kesişimine TOPLA.ÇARPIM fonksiyonu (bu konuda da bir yardım ricam olmuş onun için de bir konu açmıştım) ile uyruğu azerbaycan olup şu bölümde tedavi olan hasta sayısı gibi hesaplamalar yapacağım.
En iyisi sizi şimdi fazla meşgul etmeyeyim. Ben şu an cep telefonundan yazıyorum. Bilgisayar başına geçince bu tabloma ait örnek dosya ekleyeyim, böylece detayda kaybolmak yerine SONUÇ odaklı yazışmış ve sizi meşgul etmiş olurum. Akşama doğru örnek dosyayı eklerim.
Neticede; her sütun başlığını her diğeriyle karşılaştıracak esnekliğe ulaşabilir miyim bilemiyorum ama istatistik sonuçlar elde etmeyi hedeflediğimi söyleyeyim şimdilik. Sağlıcakla kalın. Dosya eklediğimde siteden size özel mesaj yazarak haberdar ederim.

 

[Muhammet Okumuş]

Peki her kriter için ne kadar değer olacağı belli mi?

 

[Muhammet Okumuş]

Dosyayı inceleyin. Sarı renkli 6. satıra yazacağınız değerler sıklık değerini ifade ediyor. 6. satıra 3 yazdığınızı varsayarsak, o sütunda en fazla olan 3 değeri ilk 3e geriye kalanları alfabetik sıralar.

 

[Ömer BARAN]

Dosyayı inceleyin. Sarı renkli 6. satıra yazacağınız değerler sıklık değerini ifade ediyor. 6. satıra 3 yazdığınızı varsayarsak, o sütunda en fazla olan 3 değeri ilk 3e geriye kalanları alfabetik sıralar.

Sayın OKUMUŞ ilginiz için teşekkürler, zahmetler verdim.
Sıralama ve aktarma olayı sanırım tamamdır.
Daha evvel baktığınız, verileri sütunlara dağıtma işlemi de bu belgemin AN sütunundaki veriler idi bu sütundaki verilerin dağıtılması işlemini aynı sayfada mevcut tablomun sağında uygun alan olarak EA sütunundan başlayarak yapmam lazım, neticede her bir satır bir hasta kaydı ve hasta ile tanılar bir birinden ayrılmaması gereken (aynı satırda olması gereken) veriler ve şu anki haliyle en fazla tanı sayısı 11 olduğuna göre EA-EK aralığında dağıtılmış olacak. Daha fazla TANI ihtimaline karşılık tanıların dağıtıldığı alanı EA-EZ olarak düşüneceğim (26 sütun)
Benzersiz isimli sayfaya ise VERİ TABANI sayfasındaki tam liste üzerinden EĞERSAY gibi bir fonksiyonla tanı rastlanma sayılarını bulup, buradan en fazla tekrarlanan ilk 10 tanıyı benzersiz isimli sayfaya almayı düşünüyorum. benzersiz isimli sayfaya aldığım veriler daha sonra birbirleriyle karşılaştırma yapacağım tabloların satır ve sütun başlıklarını oluşturacak.
Neyse tanıları sütunlara dağıtma işlemine ait makroda değişiklik yaparak asıl tabloma uyarlamaya çalışayım. Tıkanırsam yardımınızı rica ederim artık.
Emek ve zaman ayırdığınız için tekrar teşekkürler.
Bu arada kuracağım tabloda kullanacağım TOPLA.ÇARPIM fonksiyonu ile ilgi BENZERSİZ sayısını dikkate alan fonksiyonda yaşadığım sorun için açtığım konu linki aşağıdaki gibidir. Bir kişi cevap vermiş ancak ilave sütun kurmamı önermiş, ilave sütun olmaksızın mümkün olması gerektiğini düşündüğümden bu konu da açıkta duruyor.
http://www.excel.web.tr/f47/topla-arpim-fonksiyonunda-benzersiz-deoerleri-dahil-etme-t145433.html

 

[Ömer BARAN]

Sayın Okumuş tekrar merhabalar; veri aktarma kodlarında (sarı zeminli satırı çok güzel düşünmüşsünüz ve makro başlık hücresine fareyle tıklandığında çalışıyor bu biraz riskli ama benim için MUHTEŞEM ÖTESİ olmuş);

1. Tanıların Sütunlara Dağıtılması Makrosu, TANILAR Sayfası Üstünden çalışmak yerine; verilerin HAM sayfası AN Sütunundan Alınıp, HAM sayfası EA-EZ Aralığına Yazarak Çalışması,
2. Veri Aktarma İşleminde Sarı Zeminli 6'ncı Satırdaki Rakam Silindiğinde Aktarılmış İlgili Sütunun Da Başlığıyla Beraber Silinmesi (Bu çok da elzem değil aslında),

için nasıl değişiklikler yapmam lazım acaba?

3.Yaş Başlıklı L Sütunu Benzersiz Sayfasına Aktarmak yerine bu sütundaki

=EĞER(L8="";"";EĞER(0+EĞERHATA(SOLDAN(L8;BUL(" yıl";L8;1)-1);0)<18;"0-17";EĞER(VE(0+EĞERHATA(SOLDAN(L8;BUL(" yıl";L8;1)-1);0)>17;0+EĞERHATA(SOLDAN(L8;BUL(" yıl";L8;1)-1);0)<66);"18-65";EĞER(0+EĞERHATA(SOLDAN(L8;BUL(" yıl";L8;1)-1);0)>65;"65+";""))))

fonksiyonu ile 0-17, 18-64, 65+ diye üç gruba ayırıp bunlar üzerinden işlem yapacağım.
Bunu da makroyla halletmek mümkün müdür acaba?

Sizi hakikaten çok uğraştırdım ve benim konuyu yakından takip ettiğinizi de biliyorum. Haklarınızı helal ediniz.

 

[Muhammet Okumuş]

1. Hastalığın yazıldığı hastalık veri tabanı nerde yazılacak?
EA 1. TANI
EB. 2 TANI
....
EK 11. TANI
şeklinde mi yapmak istiyorsunuz?
2. Bunun yerine veri aktarıldıktan sonra aktarılan veriler silinse nasıl olur?
3. Bu kısımda yaş yerine 0-17 mi yazacak?

 

[Muhammet Okumuş]

Dosya üzerinde açıklarsanız daha sağlıklı olur.

 

[Ömer BARAN]

Merhabalar Sayın Okumuş.
Haklarınız birikmeye devam ediyor.
Dosyamı biraz ayıkladım ama yine de ortalıkta dolaşmaması icap eden bilgiler kalmış olabilir.

Sayın Ömer'in ve bzace'nin verdiği formüller dizi formülleri olması hasebiyle bilgisayarın çok yavaşlamasına (4 çekirdekli intel.i5 işlemciyi bile kilitleyecek CPU'nun %98'ini meşgul edecek hale geldi) neden olduğundan birkaç fonksiyon dışında onları da sildim.
Tabi mevcut dizi formüllerinin de koda bağlanması sanki olmazsa olmaz gibi duruyor. (Şu an itibariyle yazılması gereken dizi formülü adedi 1100'den fazla) Tüm tablolar dolduğunda bilgisayarımın kilitlenmesine neden olacağı kesin gibi duruyor.
Bu yola girince dosya üzerinde ben hiç bir şey yapamaz hale geliyorum (kod işinden pek anlamadığımız malum) ama sanki zorunda kalacakmışım gibi duruyor. Sonunda bana da öğreteceksiniz anlaşılan. Ama o zaman çok daha fazla meşgul edeceğim sizleri. Neyse.

Sizin eklediğiniz TANI AYIKLAMA kodunda değişiklik yaptım (veri alacağı yeri ve veri yazacağı yeri değiştirmeye çalıştım ve kodu çalışmaz hale getirdim maalesef) .

BENZERSİZ SAYFASINA AKTARMA kodunun çalışmasının başlık satırının seçilmesine bağlanması oldukça sıkıntı yarattı. Sarı zeminli satır uygulaması oldukça fonksiyonel oldu ama.
Belki bu kodun çalışması işini geçici olarak durdurabilmek için düzenleme yapmak gerekebilir. Neticede sıklık sırasına göre sarı zemine yazılan değer kadarını sıklık kriterine göre diğerlerini alfabetik aktarma işlemi sürekli yapılacak bir işlem değil, o nedenle bir hücreye belli bir şey yazılmasına veya bir aktif/pasif şeklinde çalışan Togle Buton gibi bir şeye bağlamakta yarar var.
Tanıların birleştirilmiş hali HAM sayfasının AN sütununda yer alıyor.
Verdiğim zahmetlerden ötürü kusuruma bakmayınız.

Aslında sonuç tablolarımın tam esneklik kazanması için (HAM sayfasındaki her bir sütun başlığının, bir diğeri ile karşılaştırılabilir olması) çok şeye ihtiyacım var ama böyle şeylere ancak kod yazmayı biraz olsun öğrendiğimde girişirim. Şimdilik bu tabloların oluşmasıyla idare edeceğim artık.

 

[Muhammet Okumuş]

Dizi formüllerini makro ile değer haline getirmek gerek. Güncelle makrosu ile veri eklendiğinde değerleri güncellemiş olur böylece dosya yükünü azaltırız.

 

[Muhammet Okumuş]

Dosyada bir tane Data sayfası oluşturdum. Her hasta no için verileri buraya alsak ve buradan tablolara aktaramaz mıyız? Bu sayfadaki 4. satır(sarı dolgu) kısımda formüller var. Diğer satırları değer olarak kaydettim. Acaba buradaki verilerden bir şey yapılabilir mi? Ben tam olarak ne yapmak istediğinizi bilmediğim için tıkandım. 4. satırdaki formülleri inceleyiniz. Mantık hatası var mı?