Excel'de her yıl/çeyrek/ay/hafta başına düşen olay sayısını nasıl sayarsınız?

Belirli bir yılda veya ayda belirli bir olayın kaç kez gerçekleştiğini hızlıca bulmanız gerektiğinde, formüller esnek ve dinamik bir yaklaşım sunar. SUMPRODUCT ile birlikte yerleşik tarih fonksiyonlarını kullanarak, yılı, ayı veya bunların herhangi bir kombinasyonunu baz alarak doğrudan sayımı hesaplayabilirsiniz. Bu, kaynağınızdaki veriler değiştiğinde özetlemenizin doğru ve otomatik olarak güncellenmesini sağlar. Bu yaklaşım, küçükten orta büyüklüğe kadar olan veri setlerinin çoğu rutin analiz görevinde iyi çalışır.

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

=TOPLA.ÇARPIM((AY($A$2:$A$24)=F2)*(YIL($A$2:$A$24)=$E$2))

Formülü girdikten sonra, formülü gerektiğinde diğer satırlara uygulamak için hücrenin Otomatik Doldurma işaretini aşağı sürükleyin. Aşağıda gösterildiği gibi:

Notlar ve ipuçları:

• Formüldeki AY($A$2:$A$24)=F2 ve YIL($A$2:$A$24)=$E$2, F2'deki belirtilen ay ve E2'deki yıl ile eşleşen kriterlerdir. Veri düzeninize uygun şekilde aralıkları ve referansları (örneğin A2:A24, E2, F2) güncelleyin.
• Sadece aya göre sayım yaparken, yılı göz ardı etmek için şu formülü kullanın:
  =TOPLA.ÇARPIM(1*(AY($A$2:$A$24)=F2))
• Tarih sütununun gerçek Excel tarih değerlerini içerdiğinden emin olun, metin formatlı tarihlerden kaçının, aksi halde hatalar veya uyuşmazlıklar oluşabilir. Formül beklenmedik sonuçlar döndürüyorsa, tarih formatını tekrar kontrol edin.
• Veri setiniz büyükse, performans ve daha kolay bakım için PivotTable veya VBA kullanmayı düşünün.

Bu yöntem, hızlı tarih istatistikleri gerektiren çoğu senaryo için uygundur ve veri değişikliklerinde sonuçların otomatik olarak güncellenmesini istediğinizde faydalıdır. Ancak, çoklu gruplama koşullarıyla çalışmak formülleri karmaşık hale getirebilir ve bakımı zorlaştırabilir.

Kutools for Excel yüklüyse, karmaşık formüller oluşturmadan yıl, ay, hafta içi, gün veya yıl & ay veya ay & gün gibi daha fazla kombinasyonlarla gruplama ve olay sayısını sayma işlemlerinde sezgisel araçlarından yararlanabilirsiniz. Bu yaklaşım özellikle görsel, menü tabanlı bir çözüm arayan kullanıcılar için oldukça etkilidir.

1. Tarihlerinizi içeren sütunu seçin ve Kutools > Biçim > Tarih Formatını Uygula'ya tıklayın. Aşağıdaki iletişim kutusu görünecektir:

2. Tarih Formatını Uygula iletişim kutusunda, sayım gereksiniminize karşılık gelen biçimlendirme stilini seçin (örneğin ay, yıl, hafta içi, gün vb.) ve ardından Tamam'a tıklayın. Örneğin, aya göre sayım yapmak için "Mar" seçin.

3. Tarih sütunu hala seçiliyken Kutools > Gerçek Değere Dönüştür'e tıklayın. Bu adım, tüm tarihleri sonraki adımlarda daha kolay gruplama için görüntülenen değerlere (örneğin ay isimleri) dönüştürür.

4. Ardından, dönüştürülmüş grup isimlerini ve ilişkili verileri (örneğin Miktar veya Kategori sütunları) içeren aralığı seçin. Şuraya gidin: Kutools > Metin > Gelişmiş Satırları Birleştir. Aşağıdaki arayüzü göreceksiniz:

5. Gelişmiş Satırları Birleştir iletişim kutusunda:
(1) Tarih sütununuzu Ana Anahtar olarak ayarlayın ve ona göre gruplayın.
(2) Saymak istediğiniz sütun için (örneğin Miktar), hesaplama işlemini şu şekilde ayarlayın: Say.
(3) Diğer sütunlar için başka toplama veya birleştirme yöntemleri seçebilirsiniz (örneğin meyve isimlerini virgülle birleştirin).
(4) Şuna tıklayın: Tamam işlemek için.

Verileriniz şimdi seçilen her dönem için kayıt sayısını gösterecektir. Aşağıdaki ekran görüntüsüne bakın:

Manuel formüllere kıyasla, Kutools işlemi basitleştirir, insan hatasını azaltır ve sık sık gruplanmış sayım yapan ve formül karmaşıklığından kaçınmak isteyen kullanıcılar için oldukça uygundur. Bu hem küçük hem de büyük veri setleri için iyi çalışır. Unutmayın, toplu olarak satırları dönüştürmeden veya birleştirmeden önce verilerinizi yedekleyin.

PivotTable'lar, büyük veri setlerini analiz etmek ve bir veya daha fazla zaman boyutuna göre olayları özetlemek için güçlü ve etkileşimli bir yöntem sunar — yıl, ay, çeyrek, saat vb. — hepsi kolay bir tıklatma arabirimiyle. PivotTable'lar ayrıca hızlı yeniden yapılandırma ve filtreleme imkanı sunar, bu da veri desenlerini keşfetmek veya yönetim raporları hazırlamak için idealdir.

1. Veri tablonuzu seçin ve ardından şuraya gidin: Ekle > PivotTable. PivotTable Oluştur iletişim kutusu görünür.

2. İletişim kutusunda, PivotTable'ı nereye yerleştireceğinizi belirtin (yeni çalışma sayfası veya mevcut bir konum, örneğin E1 hücresi) ve ardından Tamam.

3. PivotTable Alanları bölmesinde, Tarih alanını Satırlar bölümüne ve Miktar (veya hedef alan) alanını Değerler bölümüne sürükleyin. Varsayılan olarak değerler toplanabilir.

PivotTable aşağıdaki ekran görüntüsünde olduğu gibi görünür:

4. Değer hesaplamasını sayım olarak değiştirmek için değer sütun başlığına (örneğin Miktar Toplamı) sağ tıklayın ve ardından Değerleri Özetleme Şekli > Say.

5. Ek sürelerle (örneğin ay, yıl veya çeyrek) gruplamak için, Satır Etiketleri sütunundaki herhangi bir hücreye sağ tıklayın, seçin Grupla, ve iletişim kutusundan gruplama kriterlerini (örneğin Aylar, Yıl veya Çeyrekler) seçin ve ardından Tamam.

Tablonuz şimdi seçilen dönem(lere) göre sayımı gösterecektir:

Not: Birden fazla süreyle (örneğin ay ve yıl) gruplama, Satır Etiketlerinde ekstra seviyeler ekler. Gruplama alanlarını yeniden düzenleyebilirsiniz (örneğin, Yıllar altına taşıyın Tarih) PivotTable Alanları bölmesinde özet görünümünüzü ayarlamak için.

Bu yaklaşım, periyodik gruplama, karşılaştırma ve özetleme gerektiren büyük ve dinamik veri setleri için en uygundur. Hızlı, tek seferlik hücre düzeyinde hesaplamalar veya PivotTable özelliklerine aşina olmayan kullanıcılar için daha az uygundur.

Çeşitli zaman dönemlerine göre gruplanmış olay özetlerini tekrar tekrar oluşturmanız gerekiyorsa veya özellikle büyük veri setlerinde sayım sürecini otomatikleştirmek istiyorsanız, özel bir VBA makrosu etkili bir çözüm olabilir. Bu yöntem, verileri düzenli olarak işlediğinizde, periyodik özet tablolar oluşturduğunuzda veya formüller veya PivotTable'lar tarafından kolayca ele alınmayan özel gruplamalar (örneğin mali çeyrekler veya haftalar) gerektiğinde oldukça uygundur.

Tam işlem adımları:

• Herhangi bir makroyu ilk kez çalıştırmadan önce çalışma kitabınızı yedekleyin.
• Geliştirici > Visual Basic'e tıklayarak VBA düzenleyicisini açın.
• Ekle > Modül'e tıklayın ve 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

Kod girdikten sonra:

• Excel'e geri dönün ve Alt+F8'e basın, CountOccurrencesByPeriod'i seçin ve Çalıştır'a tıklayın.
• Bir istem size analiz etmek için tarih aralığını seçmenizi soracak. Tarihlerinizi içeren ilgili sütun veya aralığı seçin.
• İkinci bir istem hangi dönemde gruplanacağını sorar: "Yıl", "Çeyrek", "Ay" veya "Hafta" girin (büyük/küçük harf duyarsız).
• Makro, her dönemin ve içindeki olay sayısının listelendiği yeni bir çalışma sayfası olan Occurrence_Summary oluşturacaktır.

Sorun giderme ve ipuçları:

• Makro güvenlik uyarısı alırsanız, Dosya > Seçenekler > Güven Merkezi > Makro Ayarları'nda makro ayarlarını ayarlayın.
• Tarih sütununun geçerli Excel tarih değerlerini içerdiğinden emin olun; metin dizeleri veya karışık formatlar yanlış sayımlara veya hatalara neden olabilir.
• Makro esnektir — "Çeyrek" yazarak yılı ve çeyreği hızlıca gruplandırabilir veya "Hafta" yazarak haftalık bazda özetleyebilirsiniz.
• Çıktıyı özelleştirmek isterseniz (örneğin daha fazla detay eklemek), makroyu ek sütunları veya hesaplama kurallarını işlemek üzere değiştirebilirsiniz.

Bu çözüm toplu raporlama veya periyodik analiz için sağlamdır ancak temel VBA bilgisine ve uygun çalışma kitabı yönetimine dayanır. Görsel özetleme birleştirmek istiyorsanız, hem PivotTable'ları hem de VBA'yı kullanmayı düşünün.

Haftalık bazda giriş veya olay sıklığını saymak, satış takibi, proje yönetimi ve kaynak tahsisi gibi durumlarda yaygın bir gereksinimdir. Excel, WEEKNUM fonksiyonu sunar, bu da belirli bir tarihin yıl içindeki hafta numarasını döndürür, böylece formüller kullanarak verileri haftalık bazda gruplamayı kolaylaştırır.

Uygulanabilir senaryo: Satış veya katılım verileri gibi bir tarih listesi var ve her haftaya kaç giriş düştüğünü saymak istiyorsunuz. Bu yöntem, devam eden analizler için ve verileriniz sık sık değiştiğinde iyi çalışır çünkü sayım otomatik olarak güncellenir.

1. Boş bir sütunda (örneğin B2), A sütunundaki her tarih için hafta numarasını hesaplamak üzere aşağıdaki formülü girin:

=WEEKNUM(A2,1)

İkinci argüman ("1") haftanın Pazar günü başladığını gösterir (haftanın Pazartesi günü 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 bir listesini oluşturun (örneğin, 1,2,3,…). Başka bir boş hücrede (örneğin D2), belirli bir hafta numarası için olay sayısını saymak üzere aşağıdaki formülü kullanın (B2:B24'ün hafta numaralarını listelediğini ve D2'nin aranacak haftayı içerdiğini varsayarak):

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

Enter'a bastıktan sonra, bu formülü hafta numaraları listeniz için aşağı çekin. Her sonuç, o hafta için olay sayısını gösterecektir.

İpuçları ve önlemler:

• Farklı yıllardaki girişleri ayırt etmek için hem yılı hem de haftayı saymak 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 ve G2 hedef hafta numarasıdır. Gerektiğinde sütun aralıklarını ve referanslarını ayarlayın.
• WEEKNUM fonksiyonunun hafta numaralandırması, ayarlamaya bağlı olarak değişebilir (sistem, ABD/ISO, seçilen başlangıç günü).
• ISO hafta numaralarını kullanıyorsanız (Avrupa standardı, haftalar Pazartesi başlar ve ilk hafta ilk Perşembe ile başlar), =ISOWEEKNUM(A2) kullanın (Excel2013 ve sonrası için).
• Doğru sonuçlar için tüm tarih değerlerinizin geçerli Excel tarih formatında olduğundan her zaman emin olun.

Bu yöntem, dinamik veri tabloları için esnektir ve PivotTable veya ek eklentiler kullanmadan haftalık bazda çapraz tablo sayımı yapmak istediğinizde panolar, periyodik özetler için adapte edilebilir.

Excel'de iki tarih arasında düşen hafta sonu/hafta içi sayısını sayma

Excel'de tarih/ay/yıl ve tarih aralığına göre Countif

Excel'de filtrelenebilir veri/liste için kriterlerle Countif