Excel’de verileri yıla, çeyreğe, aya veya haftaya göre nasıl gruplayıp oluşum sayısını hesaplarım?

Belirli bir yıl ya da ayda bir olayın kaç kez gerçekleştiğini hızlıca öğrenmeniz gerektiğinde, formüller esnek ve dinamik bir çözüm sunar. Yerleşik tarih işlevlerini SUMPRODUCT ile birleştirerek, yıl, ay veya bunların herhangi bir kombinasyonuna göre doğrudan sayım yapabilirsiniz. Böylece özetiniz, Kaynak Veri değiştiğinde otomatik olarak güncellenir ve her zaman doğru kalır. Bu yöntem, küçük ila orta ölçekli veri kümelerinde çoğu rutin analiz görevi için mükemmel sonuçlar verir.

Sayım sonucunu görüntülemek istediğiniz boş bir hücre seçin ve aşağıdaki formülü girin:

=SUMPRODUCT((MONTH($A$2:$A$24)=F2)*(YEAR($A$2:$A$24)=$E$2))

Formülü girdikten sonra, hücrenin Otomatik Doldurma tutamacını aşağı doğru sürükleyerek formülü ihtiyaç duyduğunuz kadar çok satıra uygulayın. Aşağıdaki gibi:

Notlar ve ipuçları:

• Formüldeki MONTH($A$2:$A$24)=F2 ve YEAR($A$2:$A$24)=$E$2, sırasıyla F2 hücresindeki ayı ve E2 hücresindeki yılı eşleştiren kriterlerdir. Aralık ve referanslarınızı ()A2:A24, E2, F2) veri düzeninize uygun şekilde güncellemeyi unutmayın!
• Yalnızca aya göre bir sayıma ihtiyacınız varsa ve yılı göz ardı etmek istiyorsanız şunu kullanın:
  =SUMPRODUCT(1*(MONTH($A$2:$A$24)=F2))
• Hata veya uyuşmazlıklardan kaçınmak için tarih sütununun metin biçiminde değil, gerçek Excel tarih değerleri içerdiğinden emin olun. Formülünüz beklenmedik sonuçlar veriyorsa, tarih biçimlendirmesini mutlaka tekrar kontrol edin.
• Veri kümeniz büyükse, performans ve daha kolay bakım açısından Özet Tabloları (PivotTable) veya VBA kullanmayı düşünün.

Bu yöntem, hızlı tarih istatistiklerine ihtiyaç duyan ve veri değişikliklerinde sonuçların otomatik olarak güncellenmesini istediğiniz çoğu senaryo için idealdir. Ancak çoklu gruplama koşullarıyla çalışmak, formüllerin karmaşıklığını artırabilir ve bakımını zorlaştırabilir.

Kutools for Excel yüklüyse, karmaşık formüller oluşturmadan yıl, ay, haftanın günü, gün veya yıl & ay ya da ay & gün gibi kombinasyonlara göre olayları kolayca gruplayıp saymanıza olanak tanıyan kullanıcı dostu araçlarından yararlanabilirsiniz. Bu yaklaşım, görsel ve menü tabanlı bir çözüm arayan kullanıcılar için özellikle verimlidir.

1. Tarihlerin bulunduğu sütunu seçin ve Kutools > Biçim > Tarih Formatını Uygula öğelerine tıklayın. Aşağıdaki iletişim kutusu görünecektir:

2. Tarih Formatını Uygula iletişim kutusunda, sayım gereksiniminize uygun biçimlendirme stilini seçin (örneğin ay, yıl, haftanın günü, gün vb.), ardından Tamam’a tıklayın. Örneğin, aya göre sayım yapmak için "Mar" seçeneğini belirleyin.

3. Tarih sütunu hâlâ seçiliyken, Kutools > Gerçek Biçime öğelerine tıklayın. Bu adım, tüm tarihleri sonraki adımlarda daha kolay gruplamanız için Görüntülenen Değer’e (örneğin ay isimleri) dönüştürür.

4. Ardından, dönüştürülmüş Grup Adı’nızı ve ilişkili verilerinizi (örneğin Tutar veya Kategori sütunları) içeren aralığı seçin. Kutools > İçerik > Gelişmiş Satırları Birleştir öğelerine gidin. Aşağıdaki arayüzü göreceksiniz:

5. Gelişmiş Satırları Birleştir iletişim kutusunda:
(1) Gruplama yapmak için tarih sütununuzu Birincil Anahtar olarak ayarlayın.
(2) Saymak istediğiniz sütun için (örneğin, Tutar), hesaplama yöntemini Say olarak belirleyin.
(3) Diğer sütunlar için farklı toplama veya birleştirme yöntemleri seçebilirsiniz (örneğin, meyve isimlerini virgülle birleştirin).
(4) İşlemi başlatmak için Tamam’a tıklayın.

Verileriniz artık seçilen döneme göre kayıt sayılarını gösterecek. Aşağıdaki ekran görüntüsüne göz atın:

Kutools, manuel formüllere kıyasla süreci büyük ölçüde basitleştirir, insan hatasını en aza indirir ve formül karmaşıklığından kaçınmak isteyen, sık sık gruplandırılmış sayım yapan kullanıcılar için idealdir. Hem küçük hem de büyük veri kümelerinde sorunsuz çalışır. Satırları toplu olarak dönüştürmeden veya birleştirmeden önce verilerinizi yedeklemeyi unutmayın.

Özet Tablolar (PivotTables), büyük veri kümelerini analiz etmek ve yıl, ay, çeyrek, saat vb. bir veya daha fazla zaman boyutuna göre olayları özetlemek için güçlü ve etkileşimli bir yol sunar. Tüm bunlar kolay bir tıkla yapılabilir. Ayrıca Özet Tablolar, hızlı yeniden yapılandırma ve filtreleme imkânı sağladığından, veri kalıplarını incelemek veya yönetim raporları hazırlamak için idealdir.

1. Veri tablonuzu seçin ve ardından Ekle > Özet Tablo öğelerine gidin. Özet Tablo Oluştur iletişim kutusu görünür.

2. İletişim kutusunda Özet Tablo’nun nereye yerleştirileceğini belirtin (Yeni çalışma sayfası veya E1 hücresi gibi mevcut bir konum) ve ardından Tamam’a tıklayın.

3. Özet Tablo Alanları bölmesinde, Tarih alanını Satırlar bölümüne; Tutar (veya hedef alan) alanını ise Değerler bölümüne sürükleyin. Varsayılan olarak değerler toplanabilir.

Özet Tablo aşağıdaki ekran görüntüsündeki gibi görünür:

4. Değer hesaplamasını bir sayım işlemine dönüştürmek için değer sütun başlığına (örneğin, Tutar Toplamı) sağ tıklayın ve ardından Değerleri Özetleme Ölçütü > Say.

5. Ek dönemlere göre gruplamak için (örneğin ay, yıl veya çeyrek), Satır Etiketleri sütunundaki herhangi bir hücreye sağ tıklayın, Gruplandır seçeneğini belirleyin ve açılan iletişim kutusundan gruplama ölçütlerini (örneğin Aylar, Yıllar veya Çeyrekler) seçtikten sonra Tamam.

Tablonuz artık seçilen dönem(ler)e göre sayımları göstermektedir:

Not:Birden fazla döneme göre gruplama (örneğin ay ve yıl), Satır Etiketleri’ne ekstra düzeyler ekler. Özet görünümünüzü ayarlamak için Özet Tablo Alanları bölmesinde gruplama alanlarını yeniden sıralayabilirsiniz (örneğin,)Yıllar alanını Tarih alanının altına taşıyabilirsiniz).

Bu yaklaşım, düzenli olarak gruplama, karşılaştırma ve özetleme gerektiren büyük ve dinamik veri kümeleri için en ideal seçenektir. Ancak hızlı, geçici hücre düzeyinde hesaplamalar yapmak isteyenler veya Özet Tablo özelliklerine alışkın olmayan kullanıcılar için pek uygun değildir.

Farklı zaman dilimlerine göre tekrarlayan özetler oluşturmanız gerektiğinde ya da büyük veri kümelerinde sayma işlemlerini verimlilik amacıyla otomatikleştirmek istiyorsanız, özel bir VBA makrosu etkili bir çözüm olabilir. Bu yöntem, verileri düzenli olarak işliyorsanız, periyodik özet tabloları üretiyorsanız veya formüllerle ya da Özet Tablolarla kolayca gerçekleştirilemeyen özel gruplamalara (örneğin mali çeyrekler veya haftalar) ihtiyacınız olduğunda özellikle uygundur.

İşlem adımları:

• Çalışma kitabınızı herhangi bir makroyu ilk kez çalıştırmadan önce yedekleyin.
• Geliştirici > Visual Basic öğelerine tıklayarak VBA düzenleyicisini açın.
• Ekle > Modül’e tıklayın, ardından aşağıdaki kodu Modül penceresine kopyalayıp yapıştırın.

Sub CountOccurrencesByPeriod()
    Dim lastRow As Long
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim periodType As String
    Dim dict As Object, key As Variant
    Dim dateRange As Range, cell As Range
    Dim outputRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set dateRange = Application.InputBox("Select date range:", xTitleId, Selection.Address, Type:=8)
    
    periodType = Application.InputBox("Count by (Year/Quarter/Month/Week):", xTitleId, "Month", Type:=2)
    
    If dateRange Is Nothing Or periodType = "" Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dateRange
        If IsDate(cell.Value) Then
            Select Case LCase(periodType)
                Case "year"
                    key = Year(cell.Value)
                Case "quarter"
                    key = "Q" & WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0) & " " & Year(cell.Value)
                Case "month"
                    key = Format(cell.Value, "yyyy-mm")
                Case "week"
                    key = "W" & WorksheetFunction.WeekNum(cell.Value) & " " & Year(cell.Value)
                Case Else
                    key = Format(cell.Value, "yyyy-mm")
            End Select
            
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell
    
    Set summaryWs = Worksheets.Add(After:=ws)
    summaryWs.Name = "Occurrence_Summary"
    
    summaryWs.Range("A1").Value = "Period"
    summaryWs.Range("B1").Value = "Occurrences"
    
    outputRow = 2
    For Each key In dict.Keys
        summaryWs.Cells(outputRow, 1).Value = key
        summaryWs.Cells(outputRow, 2).Value = dict(key)
        outputRow = outputRow + 1
    Next key
    
    MsgBox "Summary completed in sheet 'Occurrence_Summary'.", vbInformation
End Sub

Kodu girdikten sonra:

• Excel’e dönün ve Alt+F8 tuşlarına basın, CountOccurrencesByPeriod seçeneğini belirleyin ve Çalıştır’a tıklayın.
• Bir iletişim kutusu, analiz edilecek Tarih Aralığı’yi seçmenizi isteyecektir. Tarihleri içeren ilgili sütunu veya aralığı seçin.
• İkinci bir iletişim kutusu, hangi döneme göre gruplama yapılacağını sorar: "Yıl", "Çeyrek", "Ay" veya "Hafta" girin (büyük/küçük harf duyarlı değildir).
• Makro, her dönemin içindeki olay sayısını listeleyen "Yeni çalışma sayfası" adında bir Occurrence_Summary oluşturur.

Sorun Giderme ve İpuçları:

• Makro güvenlik uyarısıyla karşılaşırsanız, ayarları şu yoldan değiştirebilirsiniz: Dosya > Seçenekler > Güven Merkezi > Makro Ayarları.
• Tarih sütununuzun geçerli Excel tarih değerleri içerdiğinden emin olun; metin dizgileri veya karışık biçimler yanlış sayımlara veya hatalara neden olabilir.
• Makro esnektir—yıllara ve çeyreklere göre hızlıca gruplama yapmak için "Çeyrek", haftalık özetlemek için "Hafta" girin.
• Çıktıyı özelleştirmek isterseniz (örneğin daha fazla ayrıntı eklemek için), makroyu ek sütunları veya hesaplama kurallarını işleyecek şekilde uyarlayabilirsiniz.

Bu çözüm, toplu raporlama ve periyodik analiz için sağlam bir seçenektir; ancak temel düzeyde VBA bilgisi ile doğru çalışma kitabı yönetimi gerektirir. Görsel özetleme yapmayı düşünüyorsanız, hem Özet Tabloları hem de VBA’yı değerlendirmenizi öneririz.

Girişlerin veya olayların haftalık sıklığını saymak, satış takibi, proje yönetimi ve kaynak tahsisi gibi alanlarda yaygın bir gereksinimdir. Excel, verilen bir tarihin yıl içindeki hafta numarasını döndüren HAFTA.NU işleviyle verileri hafta bazında gruplamayı kolaylaştırır.

Uygulanabilir senaryo: Tarihlerden oluşan bir listeniz varsa (örneğin satış veya katılım verileri) ve yılın her haftasına düşen giriş sayısını öğrenmek istiyorsanız, bu yöntem tam size göre! Verileriniz sık sık değişiyor ve sürekli analiz gerekiyorsa, sayılardaki güncellemeler otomatik olarak gerçekleştiği için bu yöntem mükemmel sonuçlar verir.

1. A sütunundaki her tarih için hafta numarası değerini hesaplamak üzere boş bir sütuna (örneğin B2 hücresine) aşağıdaki formülü girin:

=WEEKNUM(A2,1)

İkinci bağımsız değişken ("1") haftaların Pazar gününden başladığını belirtir (Pazartesi gününden başlamasını istiyorsanız "2" olarak değiştirin). Bu formülü tarih verilerinizin tüm satırlarına kopyalayın.

2. Özetlemek istediğiniz hafta numaralarının listesini oluşturun (örneğin: 1, 2, 3, …). Başka bir boş hücreye (örneğin D2), belirli bir hafta numarasının kaç kez geçtiğini hesaplamak için aşağıdaki formülü girin (B2:B24 aralığının hafta numaralarını içerdiğini ve D2 hücresinin aranacak haftayı gösterdiğini varsayalım):

=COUNTIF($B$2:$B$24, D2)

Enter tuşuna bastıktan sonra bu formülü Hafta numarası listeniz boyunca aşağı doğru sürükleyin. Her sonuç, ilgili hafta için oluşum sayısını gösterir.

İpuçları ve önlemler:

• Farklı yıllardaki kayıtları ayırt etmek için hem yıla hem de haftaya göre sayım yapmak istiyorsanız şu formülü kullanın:

  =SUMPRODUCT((YEAR($A$2:$A$24)=$F$2)*(WEEKNUM($A$2:$A$24,1)=G2))

  Burada F2 hedef yılı, G2 ise hedef hafta numarasını temsil eder. Sütun aralıklarını ve referansları ihtiyacınıza göre güncelleyin.
• Excel’in WEEKNUM işleviyle hesaplanan hafta numarası, sistem ayarlarınıza, ABD/ISO standardına veya seçtiğiniz haftanın başlangıç gününe göre değişebilir.
• ISO Hafta Numarası standardını kullanıyorsanız (Avrupa standardı; haftalar Pazartesi başlar ve ilk hafta, yılın ilk Perşembe’sini içeren haftadır), Excel 2013 ve sonraki sürümlerde şu formülü kullanın:=ISOWEEKNUM(A2).
• Doğru sonuçlar almak için tüm tarih değerlerinizin geçerli Excel Tarih formatı’inde olduğundan her zaman emin olun.

Bu yöntem, dinamik veri tabloları için esnektir ve pano oluşturma, periyodik özetler veya Özet Tabloları veya ek eklentiler kullanmadan haftalık çapraz tablolarla sayımlar yapmak istediğinizde uyarlanabilir.

Excel'de iki tarih arasında kaç hafta sonu/hafta içi günü olduğunu sayma

Excel'de tarihe/ay/yıla ve Tarih Aralığı göre SAYEĞER ile sayma

Excel'de filtrelenmiş verileri/listeyi kriterlere göre SAYEĞER ile sayma