Tarihe Göre Değerleri Almak

[htsumer]

Ekli dosyada cari kartlara ait tüm sayfalarda "TAHSİL" adı altında tahsilatklar yazılı. İstediğim Tarihe göre bu tahsilatları raporlamak.
Örneğim
11/01/2018 tarihi (Ocak ayına ait) "TAHSİL" yazan kısmına tahsil ücretini alıp raporlayacak. Ocak ayında birden fazla Ocak var ise toplayarak getirecek.
Aynı şekilde Şubat mart vs.. sorgulama yapacak.

Daha öncede yazdım ama bir cevap alamadım başka bir şekilde tekrar soruyorum. Sizlerden ricam..

 

[Ömer BARAN]

Merhaba.

Bu işlem için bence makro yerine formül kullanmak daha pratik.
Formül ile çözüm isterseniz aşağıdaki formülü Detaylı Rapor sayfası D3 hücresine uygulayın ve sağa ve aşağı doğru kopyalayın.

-- Formülde yıl kontrolü de ekledim bunu istemiyorsanız (yani sadece ay kontrolü yeterli ise) formüldeki (YIL(DOLAYLI("'"&$B3&"'!A10:A83"))=2018)* kısmını silerek kullanabilirsiniz.
-- Formülde C sütunlarındaki TAHSİL ibaresi aranmıyor, onun yerine E sütununun koşullu toplamı alınıyor.
.

=[COLOR="red"]TOPLA.ÇARPIM[/COLOR](([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!C10:C83")="[B][COLOR="Blue"][SIZE="4"]TAHSİL[/SIZE][/COLOR][/B]")*([COLOR="red"]AY[/COLOR]([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!A10:A83"))=SÜTUN(A$1))*([COLOR="Red"]DOLAYLI[/COLOR]("'"&$B3&"'!E10:E83")))
[COLOR="Blue"][B]NOT: [/B]Formül, yıl kontrolü kaldırılıp, C sütununda TAHSİL yazanları hesaplayacak şekilde güncellendi.[/COLOR]

 

[htsumer]

Merhaba.

Bu işlem için bence makro yerine formül kullanmak daha pratik.
Formül ile çözüm isterseniz aşağıdaki formülü Detaylı Rapor sayfası D3 hücresine uygulayın ve sağa ve aşağı doğru kopyalayın.

-- Formülde yıl kontrolü de ekledim bunu istemiyorsanız (yani sadece ay kontrolü yeterli ise) formüldeki (YIL(DOLAYLI("'"&$B3&"'!A10:A83"))=2018)* kısmını silerek kullanabilirsiniz.
-- Formülde C sütunlarındaki TAHSİL ibaresi aranmıyor, onun yerine E sütununun koşullu toplamı alınıyor.
.

=[COLOR="Red"]TOPLA.ÇARPIM[/COLOR](([COLOR="red"]YIL[/COLOR]([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!A10:A83"))=2018)*([COLOR="red"]AY[/COLOR]([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!A10:A83"))=[COLOR="red"]SÜTUN[/COLOR](A$1))*([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!E10:E83")))

Ömer bey çok teşekkür ederim ama nasıl oluyor anlayamadım. Tüm cari kartlarında sorguluyor..
formülde !A10:A83 açılımı nedir? Biraz açıklarmısınız yüzlerce sayfayıda sorgulayacakmı?
Ayrıca TAHSİL dışında o sütunlara başka tahsilatlarda işlenecek bu nedenle sadece TAHSİL olanları almamız gerekli.. Dikkat ederseniz cari kartın Tahsil yekünü toplamı tek hücrede (F4) var. Ordan almak kolay RAPORLAR kısmında makro onu alıyor.
Benim istediğim TAHSİL yazan hücre değerleri..

 

[htsumer]

Örneğin bir müşteriye SAİR GELİRLER yazdığımda o parayıda topluyor..

 

[Ömer BARAN]

İlk cevabımda, C sütunundaki TAHSİL kelimesine bakılmadığın belirtmiştim.
İlle de bu da kontrol edilsin istiyorsanız, formüle ilave yaptım, sayfayı yenileyerek ilk formül cevabımı kontrol edin (yıl kontrolünü de kaldırdım.

F4 hücresiyle ilgili durum şudur; F4'te TOPLAM var AY KONTROLÜ YOK.

Bir önceki sorunuzla (DOLAYLI işlevi) ilgili olarak ise aşağıdaki şekilde açıklama yapılabilir.
B sütunundaki ismin birebir aynısı olacak şekilde sayfa ismi varsa işlem sorunsuz gerçekleşir.
Eğer B sütununda isim olup, ilgili isme ait sayfa olmayabilirse, formüle bir hata kontrolü eklemek gerekir.

Formülün açıklamasına gelirsek; formülde iki adet (yıl kontrolünü yok sayıyorum) DOLAYLI işlevi var. Bunların parantez içinde kalan kısımlarını ayrı bir hücreye, başına = işareti ekleyerek uygulayın ve aşağı doğru kopyalayın,
formüllerin oluşturacağı metinlerden biri ilgili sayfanın A sütununa, diğeri E sütununa göre işlem yapılacak ALANın ADRESİ olur.

Sanırım yeterli ama belki formüle küçük bir ilave şöyle olabilir =EĞER($B3="";"";...verdiğim formül...)

 

[htsumer]

Yemin ederim süpersin.. Fonksiyonların bu işlevini hiç görmedim bilmiyordum.. Allah razı olsun tam nokta atışı oldu şimdi. Sayfa isimleri değişmiyor onlara ait özel makrom var değişmesi imkansız.. Ayrıca listeye isimler sayfa adına göre makro atıyor..
Çok çok teşekkür ediyorum size.. Süperrrr..süper

 

[Ömer BARAN]

Son bir hatırlatma; formül, hücre içeriğinin tam olarak TAHSİL olmasını kontrol eder.
Örneğin hücrede TAHSİLAT yazarsa onu toplamaz.
İçerir gibi kullanmak için formüldeki TAHSİL kelimesi yerine *TAHSİL* yazın.

Listelemeyi yapsanız da, meslek mensuplarının TAHSİLAT sorununa bir faydası yok.
İnşallah bu tür sorununuz çok değildir. Manisa'ya selamlar.

 

[htsumer]

Yok yok Veri Kontrol ile sabitliyorum. Sorun değil programı ben kullanıyorum ama yineden başkası için kontrolüm var..
A.Selam.. İyi Akşamlar

 

[htsumer]

Merhaba.

Bu işlem için bence makro yerine formül kullanmak daha pratik.
Formül ile çözüm isterseniz aşağıdaki formülü Detaylı Rapor sayfası D3 hücresine uygulayın ve sağa ve aşağı doğru kopyalayın.

-- Formülde yıl kontrolü de ekledim bunu istemiyorsanız (yani sadece ay kontrolü yeterli ise) formüldeki (YIL(DOLAYLI("'"&$B3&"'!A10:A83"))=2018)* kısmını silerek kullanabilirsiniz.
-- Formülde C sütunlarındaki TAHSİL ibaresi aranmıyor, onun yerine E sütununun koşullu toplamı alınıyor.
.

=[COLOR="red"]TOPLA.ÇARPIM[/COLOR](([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!C10:C83")="[B][COLOR="Blue"][SIZE="4"]TAHSİL[/SIZE][/COLOR][/B]")*([COLOR="red"]AY[/COLOR]([COLOR="red"]DOLAYLI[/COLOR]("'"&$B3&"'!A10:A83"))=SÜTUN(A$1))*([COLOR="Red"]DOLAYLI[/COLOR]("'"&$B3&"'!E10:E83")))
[COLOR="Blue"][B]NOT: [/B]Formül, yıl kontrolü kaldırılıp, C sütununda TAHSİL yazanları hesaplayacak şekilde güncellendi.[/COLOR]

Hocam biliyorum ilk başta soruma ilave etmem gerekirdi ama bilirsiniz sonradan fark ediliyor.
Tahsil'lerde 2017 yılıda mevcut bu nedenle 2017 tahsil edilenler gelmiyor. Bunuda ilave edebilirseniz sevinceğim. K.Bakmayın

 

[Ömer BARAN]

Hangi formülde karar kıldığınızı bilemiyoram.

Örnek belge üzerinden söyler misiniz?
Hangi sayfada hangi bilgi gelmesi gerekirken gelmiyor ya da gelmemesi gerekirken geliyor?

Yani hücre adresi belirtirek;
-- olması gereken sonuç nedir?
-- kullandığınız formül hangisidir?
-- formül sonucu gelen değer nedir?
soruyu/sorunu netleştirir misiniz?
.

 

[htsumer]

Hangi formülde karar kıldığınızı bilemiyoram.

Örnek belge üzerinden söyler misiniz?
Hangi sayfada hangi bilgi gelmesi gerekirken gelmiyor ya da gelmemesi gerekirken geliyor?
C ve D sütunu arasına bir sütun ekleyip 2017 TAHSİL gibi olabilir. 2017 Yılı ay bazında olmasına gerek yok

Yani hücre adresi belirtirek;
-- olması gereken sonuç nedir?
-- kullandığınız formül hangisidir?
-- formül sonucu gelen değer nedir?
soruyu/sorunu netleştirir misiniz?
.

Şu Formülü Kullanıyorum.
öRNEK DOSYADA Bilal OK Sayfasına bakın oraya 2017 tarihli Tahsil yazdım. 200 TL rapora almasını istiyorum..

=EĞER($B3="";"";TOPLA.ÇARPIM((DOLAYLI("'"&$B3&"'!C10:C83")="TAHSİLAT")*(AY(DOLAYLI("'"&$B3&"'!A10:A83"))=SÜTUN(A$1))*(DOLAYLI("'"&$B3&"'!E10:E83"))))

 

[htsumer]

Ömer bey uğraşmayın boşverin. Tarihleri 01/01/2018 yaptım istediğim sonucu verdi nasılsa..2017 tarihleride makbuz nosunun oraya yazdım..

 

[Ömer BARAN]

Tarih değiştirmeniz anlamlı olmaz. Formülü tüm ay sütunlarına kopyalamışsanız, tarihi değiştirseniz de, yazdığınız yeni tarihin AY bilgisine göre tutar toplama dahil edilir.

Tavsiyem, Detaylı Rapor sayfasında uygun bir hücreye yılı seçimi imkanı eklemeniz ve yıl bilgisini de oradan kontrol ettirmeniz yönünde olacak.

Ben örneğin yıl seçimi için Rapor sayfası P2 hücresine VERİ DOĞRULAMA =>LİSTE özelliğini kulanarak yıl seçimi ekledim ve bu hücrede yılı seçtim.

D3 hücresindeki formülü de aşağıdaki şekilde değiştirdim ve tüm alana bu formüllü hücreyi kopyaladım.
Artık P2 hücresinde seçilen yıla ait TAHSİL tutarları listelenir hale geldi.
.

=EĞER($B3="";"";TOPLA.ÇARPIM((DOLAYLI("'"&$B3&"'!C10:C83")="TAHSİL")[COLOR="Red"]*([B]YIL[/B](DOLAYLI("'"&$B3&"'!A10:A83"))=[B][COLOR="Blue"]$P$2[/COLOR][/B])[/COLOR]*(AY(DOLAYLI("'"&$B3&"'!A10:A83"))=SÜTUN(A$1))*(DOLAYLI("'"&$B3&"'!E10:E83"))))

 

[htsumer]

Çok çok teşekkür ediyorum size. Bu seçenekte güzel oldu.. Şimdi güzelleşti raporlama sayenizde. İnanın fonksiyonlarda gayet iyisiniz. Bu sitede 14 yıldır varım bi yaşıma daha girdim. Bu tür işlevlerin makro harici olacağını hiç bilmiyordum.. Sağolun..

 

[Ömer BARAN]

Estağfurullah, önemli olan ihtiyacın görülmesi elbette.

Formüllerde EĞER işlevinin ötesine geçmek lazım.

Şahsen ben kendi durumumu İDARE EDER olarak tanımlayabilirim.
(Önemli kısmı iş/ilgi alanımla bağlantısı olmayan işlevler olmak üzere, hiç kullanmadığım-bilmediğim birçok yerleşik işlev mevcut)

Tasiyem forumu sıkı takip etmeniz, kendi ihtiyacınız/sorununuz yok iken de forumda vakit geçirmeniz, soruları ve cevapları incelemeniz,
aynı sonuçlara acaba başka işlevler kullanarak da ulaşılabilir mi diye düşünüp alternatif formüller oluşturmaya çalışmanız,
formül çubuğuna örneğin =İNDİS( yazdıktan sonra hemen soldaki f(x) düğmesine tıklayıp Ms.Excel'in kendi açıklamalarını gözden geçirmeniz,
Ms.Excel'in MENÜ ÇUBUĞUNU vs biraz fazlaca kurcalamanız yönünde olacak.

Forumda rastladığınız kapsamlı formülleri, kullanılan işlevlere göre parantez işaretlerini takip ederek parça parça farklı hücrelere uygulayıp,
bu parçaların karmaşık ana formüldeki işlevlerini anlamaya çalışmanız,
tüm satıra/tüm sütuna atıf yapan formüller oluşturmak yerine formülün hesaplamaya tabi tutacağı alanı daraltmanızı sağlayacak
(bu tabiki hız ve performans anlamına geliyor) işlevler üzerinde durup örneğin DİNAMİK ALAN TANIMLAMASI gibi konulara öncelik vermeniz yararlı olur.

Muhasebe/finans ile ilgilenen kişilerin; genellikle analitik düşünce yapısına sahip olmaları büyük avantaj.

İyi çalışmalar, faydalı FORUMlar.
.

 

[htsumer]

Çok sağolun.. Bir yerde işimize ait konulara bakıyoruz. Hepsini inceleme imkanımız yok tabi. Ama yinede dipsiz bir kuyu bu..
Sizede iyi forumlar. Sağlıcakla kalın Ömer bey..

 

[htsumer]

Ömer bey, bu fonksiyona kafaya taktım ben.. Mantığını anlamaya çalışıyorum..

=EĞER($B3="";"";TOPLA.ÇARPIM((DOLAYLI("'"&$B3&"'!C10:C83")="TAHSİL")*(YIL(DOLAYLI("'"&$B3&"'!A10:A83"))=$P$2)*(AY(DOLAYLI("'"&$B3&"'!A10:A83"))=SÜTUN(A$1))*(DOLAYLI("'"&$B3&"'!E10:E83"))))

Kodda A$1 ne işe yarıyor. A$1 başvurduğu hücrelerde bişeyok.. Valla biraz açıklarmısınız..

 

[htsumer]

Ömer hocam,

Şimdi sizin verdiğiniz kodlar şunlar

=EĞER($B3="";"";TOPLA.ÇARPIM((DOLAYLI("'"&$B3&"'!C10:C83")="TAHSİL")*(YIL(DOLAYLI("'"&$B3&"'!A10:A83"))=$P$2)*(AY(DOLAYLI("'"&$B3&"'!A10:A83"))=SÜTUN(A$1))*(DOLAYLI("'"&$B3&"'!E10:E83"))))

Eyvallah hiç sorun yok. Fakat,
Benim bir rapor alma kodum var. Kod aynen şöyle

Sub rapor_al_YENİSİ()
Application.ScreenUpdating = False
On Error Resume Next
Sheets("RAPORLAR").Range("b3:k65536").ClearContents
Sheets("RAPORLAR").Range("b3:k65536").Borders.LineStyle = xlNone

Set S1 = ThisWorkbook.Worksheets("ANA MENÜ")
Set S2 = ThisWorkbook.Worksheets("RAPORLAR")
For i = 2 To S1.Range("b65536").End(xlUp).Row

Set S3 = ThisWorkbook.Worksheets(S1.Cells(i, 2).Value)
sonsatir = S2.Range("b65536").End(xlUp).Row + 1
S2.Cells(sonsatir, 2) = S3.Cells(1, "b")
S2.Cells(sonsatir, 3) = S3.Cells(3, "b")
S2.Cells(sonsatir, 4) = S3.Cells(2, "f")
S2.Cells(sonsatir, 5) = S3.Cells(11, "D")
S2.Cells(sonsatir, 6) = S3.Cells(10, "D")
S2.Cells(sonsatir, 7) = S3.Cells(4, "f")
S2.Cells(sonsatir, 8) = S3.Cells(5, "f")
S2.Cells(sonsatir, 9) = S3.Cells(7, "f")
'S2.Cells(sonsatir, 8) = S3.Cells(4, "f")
S2.Range("a" & sonsatir & ":i" & sonsatir).Borders.LineStyle = xlContinuous
Next i
Application.ScreenUpdating = True
MsgBox [A1] & " TAMAMLANMIŞTIR.", vbInformation
End Sub

Şimdi RAPOR alma kodum sayfaları tek tek sorgulayıp istenilen verileri alıyor. Sorun burda başlıyor. Her sayfaya gelip örneğin F4 hücresine geldiğinde kod çalışırken büyük ihtimal sizin kodlarda o esnada sorgulama yapıyor dolaısıyla eskiden 1 dakika bile sürmeyen RAPOR alma süresi şimdi nerdeyse 2 dakikayı buluyor.
Bunu şu şekilde test ederek anladım."Detaylı Rapor" sayfasını silip "RAPORLAR" sayfasından Rapor tuşuna basarak.. Sildikten sonra çok rahat raporlama verdi.
Ben sizin fonksiyonları kullanacağım ama rapor sayfasıda lazım.
Makro kodlarında revize edebilirmiyiz. Yada o makroyu kullanmayacağım..

 

[Ömer BARAN]

Merhaba.

Belirttiğiniz formülde kullanılan SÜTUN(A$1) kısmı, formül sağa doğru kopyalandığında sırayla 1, 2, 3, .... 12 değerini elde ediyor.
Bu kısmın amaca AY NUMARASInı bulmak. Yani A1'deki veriyi değil A1'in sütun numarasını kullanıyor. $ işareti olmasa da aynı işlemi yapar.
Belirttiğiniz kod'un çalışmasıyla da bir bağlantısı yok, tek bağlantısı belgedeki FORMÜL sayısını artırmaktan ibaret.

rapor_al_YENİSİ adını verdiğiniz kodun;
-- başlangıç kısmının mavi satırlardaki gibi,
-- bitiş kısmının da kırmızı satırlardaki gibi
olmasını sağlayarak dener misiniz?

Böylece işlem süresini de ölçmüş olursunuz.
Bu haliyle kod benim bilgisayarımda 1-1,2 saniye de işlemi tamamlıyor.
Bir deneyin bakalım.
.

[COLOR="Blue"][B]Sub rapor_al_YENİSİ()[/B]
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
zaman = Timer
On Error Resume Next
Sheets("RAPORLAR").Range("a3:H65536").ClearContents[/COLOR]
.....................
.....................
[COLOR="Red"]Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox [A1] & " TAMAMLANMIŞTIR." & vbLf & vbLf & _
    "İşlem süresi: " & Format(Timer - zaman, "0.0") & " saniye.", vbInformation
[B]End Sub[/B][/COLOR]

 

[htsumer]

Öncelikle bilgilendirme için teşekkür ederim. İnanmayacaksınız ama 0,9 saniye sürdü. Timer kodunu ekleyince anıında raporladı onu silince 3-4 dakika sürüüyor. ben bundan bişe anlamadım ama sorun çözüldü ama noldu anlamadım..

Gerçek dosyadaki Eski kodlarım:

'Sub rapor_al_YENİSİ()
'Application.ScreenUpdating = False
'On Error Resume Next
'Sheets("RAPORLAR").Range("b3:k65536").ClearContents
'Sheets("RAPORLAR").Range("b3:k65536").Borders.LineStyle = xlNone
'
'Set S1 = ThisWorkbook.Worksheets("ANA MENÜ")
'Set S2 = ThisWorkbook.Worksheets("RAPORLAR")
'For i = 2 To S1.Range("b65536").End(xlUp).Row
'
'Set S3 = ThisWorkbook.Worksheets(S1.Cells(i, 2).Value)
'sonsatir = S2.Range("b65536").End(xlUp).Row + 1
'S2.Cells(sonsatir, 2) = S3.Cells(1, "b")
'S2.Cells(sonsatir, 3) = S3.Cells(3, "b")
'S2.Cells(sonsatir, 4) = S3.Cells(2, "f")
'S2.Cells(sonsatir, 5) = S3.Cells(11, "D")
'S2.Cells(sonsatir, 6) = S3.Cells(10, "D")
'S2.Cells(sonsatir, 7) = S3.Cells(4, "f")
'S2.Cells(sonsatir, 8) = S3.Cells(5, "f")
'S2.Cells(sonsatir, 9) = S3.Cells(7, "f")
''S2.Cells(sonsatir, 8) = S3.Cells(4, "f")
'S2.Range("a" & sonsatir & ":i" & sonsatir).Borders.LineStyle = xlContinuous
'Next i
'Application.ScreenUpdating = True
'MsgBox [A1] & " TAMAMLANMIŞTIR.", vbInformation
'End Sub

Siz TİMER ilavesi ile yenilenen kodlarım

Sub rapor_al_YENİSİ()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
zaman = Timer
On Error Resume Next
' B SÜTUNU OLARAK GÜNCELLENDİ
Sheets("RAPORLAR").Range("b3:k65536").ClearContents
Sheets("RAPORLAR").Range("b3:k65536").Borders.LineStyle = xlNone

Set S1 = ThisWorkbook.Worksheets("ANA MENÜ")
Set S2 = ThisWorkbook.Worksheets("RAPORLAR")
For i = 2 To S1.Range("b65536").End(xlUp).Row

Set S3 = ThisWorkbook.Worksheets(S1.Cells(i, 2).Value)
sonsatir = S2.Range("b65536").End(xlUp).Row + 1
S2.Cells(sonsatir, 2) = S3.Cells(1, "b")
S2.Cells(sonsatir, 3) = S3.Cells(3, "b")
S2.Cells(sonsatir, 4) = S3.Cells(2, "f")
S2.Cells(sonsatir, 5) = S3.Cells(11, "D")
S2.Cells(sonsatir, 6) = S3.Cells(10, "D")
S2.Cells(sonsatir, 7) = S3.Cells(4, "f")
S2.Cells(sonsatir, 8) = S3.Cells(5, "f")
S2.Cells(sonsatir, 9) = S3.Cells(7, "f")
'S2.Cells(sonsatir, 8) = S3.Cells(4, "f")
S2.Range("a" & sonsatir & ":i" & sonsatir).Borders.LineStyle = xlContinuous
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox [A1] & " TAMAMLANMIŞTIR." & vbLf & vbLf & _
"İşlem süresi: " & Format(Timer - zaman, "0.0") & " saniye.", vbInformation
End Sub