Tarihler sütununun içinden belli bir aya ait alanı seçmek

[asteoros]

Merhaba Arkadaşlar,
Tarih fonksiyonları ile ilgili çok kapsamlı anlatım olmasına rağmen benim ihtiyacım olan konuya hiç değinilmemiş.
A1:A500 aralığında ki tarihleri küçükten büyüğe doğru sıraladıktan sonra, her aya ait tarihleri, yanında ki diğer iki kolonu da içine alacak şekilde ad alanı olarak belirlemek istiyorum. Örneğin OcakGiderleri, ŞubatGiderleri vs.
İşin içinden çıkamadım.
Bilen değerli uzman arkadaşlardan acil yardım bekliyorum.
Önceden teşekkürler, ve emeğinize-bilginize saygılarımla..

 

[Necdet]

Merhaba,

Sorunuzu örnek dosya ile desteklerseniz yanıt almanız daha kolaylaşır.

 

[Necdet]

Merhaba Arkadaşlar,
Tarih fonksiyonları ile ilgili çok kapsamlı anlatım olmasına rağmen benim ihtiyacım olan konuya hiç değinilmemiş.

Sizin istediğinizin tarih fonksiyonları ile uzaktan yakından ilgisi yok.
Sizin istediğiniz AD tanımı. Arama yaptığınızda mutlaka karşınıza çıkacaktır.


Dosya gelmediği için basit bir veri üzerinden AD tanımı oluşturdum, eki inceleyiniz.

Not : Kodları çalıştırdıktan sonra Boş bir hücreyi seçip F3 ve Liste Yapıştırı seçereniz Tüm Ad Tanımları seçtiğiniz hücreden itibaren aşağıya doğru listelenir.

Sub AdTanimla()
    Dim i       As Long
    Dim j       As Long
    Dim SonS    As Long
    Dim bs      As Range
    Dim bt      As Range
    Dim EskiTrh As Integer
    Dim SonKol  As Integer
    Dim s1      As Worksheet
    Set s1 = Sheets("Sayfa1")
    
    s1.Select
    On Error Resume Next
    'Son Kolon numarası bulunuyor
    SonKol = s1.Cells(1, Columns.Count).End(1).Column
    
    'Son Satır Numarası Bulunuyor
    SonS = s1.Cells(Rows.Count, "A").End(3).Row
    
    'Son satır ve son kolona göre sayfa tarihe göre (A sütunu) sıralanıyor
    Range(Cells(2, "A"), Cells(SonS, SonKol)).Sort Key1:=[A1]
    
    'ilk değerler belirlendi
    Set bs = Range("A2")
    EskiTrh = Month(bs)
    
    'Daha önce tanımlanan ve sonu "Giderleri" olan Ad tanımları siliniyor
    For i = ActiveWorkbook.Names.Count To 1 Step -1
        If ActiveWorkbook.Names(i).Name Like "*Giderleri" Then _
            ActiveWorkbook.Names(i).Delete
    Next i
    
    For i = 2 To SonS + 1
        'Okunan hücre ayı, EskiTrh değişkenine eşit değilse yeni aya geçilmiş demektir
        If Month(Cells(i, "A")) <> EskiTrh Then
            j = i - 1
            Set bt = Range("A" & j).Offset(0, 1)
            ActiveWorkbook.Names.Add _
                Name:=Format(bs, "mmmm") & "_Giderleri", _
                RefersTo:="=" & s1.Name & "!" & bs.Address & ":" & bt.Address
            EskiTrh = Month(Cells(i, "A"))
            Set bs = Range("A" & i)
        End If
    Next i
    
End Sub

 

[asteoros]

En iyisi dosya ile net bir şekilde durumu izah etmek

Necdet Bey,
Keşke bu kadar hızlı cevap verebileceğinizi tahmin etseydim. Çok teşekkürler.
Sorumun net anlaşılmadığı ortada.
Ekte ki dosyayı incelediğiniz zaman sormak istediğim o kadar net anlaşılacaktır ki, izahat için buradan yazacağım her kelime sadece gereksizlik ifade edecektir..
Önceden teşekkürlerimi borç bilir saygılarımı sunarım.

 

[Necdet]

Merhaba,

İlk sorunuzla örnek dosyadaki sorun arasında dünya kadar fark var.

Doğru anladıysam Özet Tablo konusunu inceleyiniz.

 

[asteoros]

Özet tablo ile işin içinden çıkamadım. Tarihlerin aynı sütunda yazılmış olması karmaşık ve içinden çıkılmaz hale getiriyor.
Her kalem gelirinin aylara göre tutarının bulunması, meselenin can damarı dır.
Yapmaya çalıştığım da zaten bundan başka bir şey değildir.
Tarihlerin, ad tanımlaması yönteminden bahsetmemin sebebi, ETOPLA veya ÇOKETOPLA formülleridir.
Eminim ki , dosyayı gördükten sonra en pratik yöntemi anlamışsınızdır.
Saygılarımla.

 

[Necdet]

Özet tablo olanağı ile istediğinizi yapmak 3 saniyenizi alır.

Burada tarihlerle ilgili tek yapacağınız şey tarihi gruplandırmaktır.

Hem Özet Tablo hemde Formülle yapılmış dosyayı inceleyiniz.


Formülle yapıldığında

G3

=TOPLA.ÇARPIM((AY($A$3:$A$144)=SÜTUN(A$1))*($B$3:$B$144=$F3)*($C$3:$C$144))

=SUMPRODUCT((MONTH($A$3:$A$144)=COLUMN(A$1))*($B$3:$B$144=$F3)*($C$3:$C$144))

G3 te yazılan bu formülü diğer hücrelere kopyalayınız.

 

[asteoros]

Sayenizde yeni bir formül öğrendim

Necdet Bey,
Şimdiye kadar ETOPLA ve ÇOKETOPLA formüllerinden başka böyle bir durumda kullanılabilecek başka bir formül olduğunu bilmiyordum. Sayenizde TOPLA.ÇARPIM işlevini de öğrenmiş oldum. Binlerce teşekkürler..
Ben bir yandan da macro larla tanışmaya ve işlevleri macrolarla da uygulamaya çalışıyorum. Acaba TOPLA.ÇARPIM işlevini sayfanın koduna nasıl yazabilirim?
Başka bir deyişle; ben sadece gelir gider sayfasına gelir giderleri işlerken başka bir sayfada otomatik olarak bu girdiler tablo haline gelsin istiyorum.
Tekrar tekrar teşekkürler, selam ve saygılarımla..

 

[asteoros]

TOPLA.ÇARPIM Formülü için Acil Yardım!!

Necdet Bey,
Formül mükemmel çalışıyor ama mantığını çözemedim A$1 den başlayarak B, C, D,... referans olarak gösterdiğiniz hücrelerin bu formülle alakasını bir türlü çözemedim. Bu formülün ayrıntılı açıklaması öğrenmeliyim. aksi halde Kopyala-Yapıştır seviyesinde kalacağım..
Önceden Teşekkürler..
Saygılarımla..

 

[Necdet]

Necdet Bey,
Formül mükemmel çalışıyor ama mantığını çözemedim A$1 den başlayarak B, C, D,... referans olarak gösterdiğiniz hücrelerin bu formülle alakasını bir türlü çözemedim. Bu formülün ayrıntılı açıklaması öğrenmeliyim. aksi halde Kopyala-Yapıştır seviyesinde kalacağım..
Önceden Teşekkürler..
Saygılarımla..

Sayın Yurttas'ın Çalışmalarını inceleyiniz. Ayrıntılı açıklamaları orada bulabilirsiniz.

Sumproduct-Topla.Çarpım Fonksiyonu ve Uygulamaları

 

[aslan525]

hocam bu tarihi nasıl buluyor ben bunu kendime göre uyarlasam ocak şubat mart vs.bunları hiç kullanmamışsınız açıklarmısınız dediğiniz sayfada bu konuya değinilmemiş yardım için şimdiden teşekkür ederim
saygılarla

 

[Necdet]

Merhaba,

((AY($A$3:$A$144)=SÜTUN(A$1))

fonksiyonda SÜTUN(A$1) 1 değeri üretir, Sağa doğru gidince SÜTUN(B$1) olur ki o da 2 değerini üretir.

Dolayısıyla AY($A$3:$A$144) ay kodlarını belirler, bunu da sütun fonksiyonu ile karşılaştırır.

 

[aslan525]

hocam teşekkür ederim bu 2 şubat 1 ocak oluyor değilmi peki bende hem 2009 hem 2010 hem 2011 varsa bunları nasıl ayırt edebiliriz saygılarla mesela ocak 2009 la ocak 2010 u nasıl ayırt edecek yardımcı olursanız sevinirim şimdiden teşekkür ederim
saygılarla

 

[Necdet]

Birden fazla yıl varsa hem yukarıdaki fonksiyonlarda olduğu gibi AY fonksiyonunu kullanmak gerek hemde YIL fonksiyonu eklemek gerekecektir.

Yani :

TOPLA.ÇARPIM((AY(A1:A10)=1)*(YIL(A1:A10)=2010)*(....)*(....))

gibi kullanabilirsiniz. Anlamı 2010 yılının Ocak Ayı şartına bağlı olarak demek.

 

[aslan525]

hocam bunu ilk yaptığınıza uyarlayabilirmisiniz size zahmet ben yapamadım çok teşekkür ediyorum yardımlarınız için
saygılarla

 

[aslan525]

hocam oldu tamam teşekkür ederim eline beynine sağlın
saygılarla

 

[asteoros]

A$1 ile Tarih(ay) arasında ki bağlantı sorunu

Üstad sizi çok yoruyorum hakkınızı helal edin!
Belki de ben tam olarak ifade edemiyorum. =TOPLA.ÇARPIM((AY($A$3:$A$144)=SÜTUN(A$1))
Kırmızı ile belirttiğim alan boş olduğu halde ay ay tarih farkı için referans teşkil ediyor. İşte bunu nasıl yaptığınızı anlayamadım.
Lütfen daha anlaşılır bir şekilde bu formülün izahını yapın.
Emeğinize ve yüreğinize saygılar..
ve önceden teşekkürler..

 

[aslan525]

hocam kusura bakmazsa ben söyleyeyim sutun(a$1) ocak demek oluyor b şubat c mart excelin kendi özelliğinden kaynaklanıyor
saygılarla

 

[Necdet]

Merhaba,

SÜTUN(A$1) fonksiyonu 1 değeri döndürüyor, a1 hücresinin değeri olmuş ya da olmamış önemli değil.

SÜTUN(B1) de 2 değerini üretir çünkü B1 ikinci sütundadır. Yine b2 nin içeriği önemli değil.

 

[asteoros]

A$1 ile Tarih(ay) arasında ki bağlantı sorunu

Üstadım sizi çok yoruyorum kusura bakmayın hakkınızı helal edin! Gösterdiğiniz sayfayı da inceledim. Bu sayede formülün mantığını da anlıyor gibiyim. Ama asıl kafama takılan soru sizin kullandığınız formülde saklıdır. Daha net bir şekilde o ayrıntıyı tekrar sormak istiyorum:
=TOPLA.ÇARPIM((AY($A$3:$A$144)=SÜTUN(A$1))
kırmızı ile işaretli olan alanın ile ayları nasıl işaret edebiliyorsunuz? Lütfen bunu açıklayın. Buradaki ayrıntıyı çözemedim.
Emeğinize ve bilginize saygılar ve önceden teşekkürler..