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 olmasını ve otomatik olarak güncellenmesini sağlar. Bu yaklaşım, küçükten orta ölçeğe kadar olan veri setlerindeki çoğu rutin analiz görevinde iyi çalışır.

Sayım sonucunu göstermek 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 tutamağını aşağı doğru sürükleyin. Aşağıda gösterildiği gibi:

Notlar ve ipuçları:

• Formülde 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 olarak aralıkları ve referansları (örneğin A2:A24, E2, F2) güncelleyin.
• Sadece aylara göre sayım yapmak istiyorsanız, yılı göz ardı ederek ş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, metin biçimindeki tarihleri değil, hatalardan veya uyuşmazlıklardan kaçının. Formül beklenmedik sonuçlar döndürüyorsa, tarih formatını iki kez kontrol edin.
• Veri setiniz büyükse, performans ve daha kolay bakım için PivotTable'lar veya VBA kullanmayı düşünün.

Bu yöntem, hızlı tarih istatistiklerinin gerekli olduğu ve veri değişikliklerinde sonuçların otomatik olarak güncellenmesini istediğiniz çoğu senaryo için uygundur. Ancak, birden fazla gruplama koşuluyla çalışmak formülleri karmaşık ve bakımı zor hale getirebilir.

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 veya ay & gün gibi daha fazla kombinasyonlar halinde olay sayısını gruplamak ve saymak için sezgisel araçlarından faydalanabilirsiniz. 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, haftanın günü, 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 adları) dönüştürür.

4. Ardından, dönüştürülen grup adlarını 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 Birincil Anahtar olarak ayarlayın ve ona göre gruplayın.
(2) Saymak istediğiniz sütun için (örneğin, Miktar), hesaplamayı Say.
(3) Diğer sütunlar için (örneğin, meyve isimlerini virgülle birleştirme) başka toplama veya birleştirme yöntemleri seçebilirsiniz.
(4) Tıklayın Tamam işlemi tamamlamak için.

Verileriniz artık seçilen dönem başına kayıt sayısını gösterecektir. Aşağıdaki ekran görüntüsüne bakın:

Manuel formüllere kıyasla, Kutools süreci 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. Hem küçük hem de büyük veri setleri için iyi çalışır. Unutmayın, satırları toplu olarak 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 (yıl, ay, çeyrek, saat vb.) göre olayları özetlemek için güçlü ve etkileşimli bir yol sunar—tüm bunlar kolay bir tıkla-yerleştir arayüzü ile yapılır. PivotTable'lar ayrıca hızlı yeniden yapılandırma ve filtreleme imkanı sağlar, bu da veri desenlerini keşfetmek veya yönetim raporları hazırlamak için idealdir.

1. Veri tablonuzu seçin ve ardından Ekle > PivotTable'ye gidin. 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 E1 gibi mevcut bir konum) 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 gösterildiği gibi görünür:

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

5. Ek dönemler (örneğin ay, yıl veya çeyrek) tarafından gruplandırmak için, Satır Etiketleri sütunundaki herhangi bir hücreye sağ tıklayın, Gruplaseçeneğini seçin ve açılan iletişim kutusundan gruplama kriterlerini (örneğin Aylar, Yıllar veya Çeyrekler) seçin ve ardından Tamam.

Tablonuz artık seçilen dönem(lere) göre sayıları gösterir:

Not: Birden fazla dönemde (örneğin ay ve yıl) gruplama yapmak, 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 iyisidir. Hızlı, tek seferlik hücre düzeyinde hesaplamalar veya PivotTable özelliklerine aşina olmayan kullanıcılar için uygun değildir.

Çeşitli zaman dönemlerine göre gruplanmış olay özetlerini tekrar tekrar oluşturmanız gerekiyorsa veya özellikle büyük veri setlerinde sayma işlemini otomatikleştirmek için—verimlilik sağlamak amacıyla—özel bir VBA makrosu etkili bir çözüm olabilir. Veriyi düzenli olarak işlediğinizde, periyodik özet tablolar ürettiğinizde veya formüller veya PivotTable'lar tarafından kolayca ele alınamayan özel gruplamalar (örneğin mali çeyrekler veya haftalar) gerektiğinde bu yöntem 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, 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 geri dönün ve Alt+F8'e basın, CountOccurrencesByPeriod seçin ve Çalıştır'a tıklayın.
• Bir istem, analiz etmek istediğiniz tarih aralığını seçmenizi isteyecektir. Tarihlerinizi içeren ilgili sütunu veya aralığı seçin.
• İkinci bir istem, hangi dönemde gruplanacağını soracaktır: "Yıl", "Çeyrek", "Ay" veya "Hafta" girin (büyük/küçük harf duyarsız).
• Makro, her dönem ve içindeki olay sayısını listeleme adında 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ı değiştirin.
• Tarih sütununun geçerli Excel tarih değerlerini içerdiğinden emin olun; metin dizeleri veya karışık formatlar yanlış sayım veya hatalara neden olabilir.
• Makro esnektir—"Çeyrek" yazarak yıla ve çeyreğe göre hızlıca gruplama yapın veya "Hafta" yazarak haftalık olarak özetleyin.
• Çıktıyı özelleştirmek istiyorsanız (örneğin, daha fazla ayrıntı 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 VBA'ya temel düzeyde aşina olmayı ve uygun çalışma kitabı yönetimi yapmayı gerektirir. Görsel özetleme birleştirmek istiyorsanız, hem PivotTable'ları hem de VBA'yı kullanmayı düşünün.

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

Uygulanabilir senaryo: Tarihlerin bir listesi (örneğin, satış veya katılım verileri) var ve her haftaya kaç giriş düştüğünü saymak istiyorsunuz. Bu yöntem, devam eden analizler için iyi çalışır ve verileriniz sık sık değiştiğinde, 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") haftaları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 yapın (örneğin, 1,2,3, …). Başka bir boş hücrede (örneğin D2), belirli bir hafta numarası için olayları saymak üzere aşağıdaki formülü kullanın (varsayılan olarak B2:B24 hafta numaralarını listeler ve D2 aranan haftayı içerir):

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

Enter tuşuna bastıktan sonra, bu formülü hafta numaralarınızın listesi boyunca aşağı doğru sürükleyin. Her sonuç, o hafta için olay sayısını gösterir.

İpuçları ve önlemler:

• Hem yıl hem de hafta bazında saymak istiyorsanız, farklı yıllardaki girişleri ayırt etmek için şunu 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ı ayarlayın.
• WEEKNUM fonksiyonunun hafta numaralandırması, ayarlara bağlı olarak değişebilir (sistem, ABD/ISO, seçtiğiniz başlangıç günü).
• ISO hafta numaralarını kullanmak istiyorsanız (Avrupa standardı, haftalar Pazartesi başlar ve ilk hafta ilk Perşembe olan haftadır), =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 göstergeler, periyodik özetler ve PivotTable'lar veya ek eklentiler kullanmadan haftalık bazda çapraz tablo sayımı yapmak istediğinizde adapte edilebilir.

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

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

Excel'de filtrelenecek veri/listeye göre sayma