Excel'de PivotTable kullanarak gün, ay, çeyrek veya saat başına ortalama nasıl hesaplanır?

Excel’in PivotTable özelliği, verileri özetlemek ve analiz etmek için son derece pratik bir araçtır; özellikle gün, ay, çeyrek veya saat gibi ayrı zaman dilimleri için hızlıca ortalama hesaplamanız gerektiğinde işinizi kolaylaştırır. Aşağıdaki yaklaşım, manuel filtrelemeyi ve tekrarlı hesaplamaları ortadan kaldırarak, verileriniz değiştiğinde kolayca ayarlanabilen etkileşimli bir özet sunar.

1. Tüm orijinal veri tablonuzu (başlıkları da içerecek şekilde) seçin, ardından Ekle sekmesine tıklayıp > PivotTable seçeneğini belirleyin.

2. Görünen PivotTable Oluştur iletişim kutusunda, özetinizi etkin çalışma sayfasına yerleştirmek istiyorsanız Mevcut çalışma sayfası seçeneğini belirleyin. PivotTable’in görünmesini istediğiniz hücreyi seçerek Konum alanını ayarlayın ve ardından Tamam’a tıklayın.
Not: PivotTable’i yeni bir çalışma sayfasında görüntülemek için bunun yerine Yeni çalışma sayfası seçeneğini seçin. Seçilen konumun mevcut verilerle çakışmadığından emin olun; aksi takdirde üzerine yazma uyarıları alabilirsiniz.

3. PivotTable Alan Listesi bölmesinde (genellikle sağda görünür),Tarih/Saat sütununu Satırlar alanına, sayısal veri olan Tutar sütununu ise Değerler alanına sürükleyin. Bu ilk kurulum, verilerinizi her kaydedilen zaman damgasına göre toplar.

4. Sonuçlarınızı belirli dönemlere göre düzenlemek için PivotTable içindeki herhangi bir tarih girişine sağ tıklayın ve bağlam menüsünden Gruplandır seçeneğini seçin. Bu özellik, veri girişlerinizi gün, ay, çeyrek veya saat gibi aralıklara kolayca gruplamanızı sağlar.

5. Gruplandırma iletişim kutusunda, Görealanından tercih ettiğiniz gruplama dönemini seçin (örneğin,)Aylar). Uygulamak için Tamam’a tıklayın. Ardından, Tutar Toplamı değerine sağ tıklayıp Değerleri Özetleme Ölçütü > Ortalama seçeneğini belirleyin. Artık PivotTable, her zaman dilimi grubu için ortalama değeri göstererek anında karşılaştırma ve analiz imkanı sunar.

Döneme dayalı ortalamanızın yeni verilerle güncellenmesi gerekiyorsa, PivotTable’i Çözümle sekmesi > Yenile seçeneğiyle kolayca yenileyebilirsiniz. Özetle, PivotTable, tanımlanmış zaman aralıkları üzerinde ortalama hesaplamak ve incelemek için etkili ve görsel bir yöntem sunar; ancak gruplama yapınız değişirse veya veri biçiminiz tutarsızsa manuel olarak yeniden yapılandırmanız gerekebilir.

Günlük, haftalık, aylık veya yıllık gibi belirli dönemler için sıklıkla ortalama hesaplama ihtiyacınız varsa—özellikle ayrıntılı saatlik veri kümeleriyle çalışıyorsanız—manuel gruplama ve hesaplama süreci tekrarlı hale gelir ve hata yapmaya açık olur. Kutools for Excel, tarih biçimlendirmeyi ve toplu toplamayı kolaylaştıran To Actual ve Gelişmiş Satırları Birleştir gibi özel yardımcı programlar sunarak size önemli ölçüde zaman kazandırır.

1. Tarih ve saati içeren hücreleri seçin ve ardından hedef döneminize uygun biçimlendirme uygulayın. Örneğin, günlük ortalamalar almak için verilerinizi seçin ve Giriş > Sayı Biçimi > Kısa Tarih yolunu izleyin. Bu işlem, Saat Dönüşümü’nü yalnızca tarihe dönüştürür.

Not: Hafta, ay veya yıla göre ortalama almak için Kutools for Excel, zaman damgalarını yalnızca birkaç tıklamayla gerekli biçime dönüştürmenizi sağlayan Tarih Formatını Uygula ve To Actual özelliklerini sunar. Bu sayede tutarlı gruplama ve doğru hesaplama sağlanır.

Şimdi 30 günlük, tüm özellikleriyle ücretsiz deneme sürümünü alın!

2. Biçimlendirilmiş tarihleri ve değerleri içeren tüm veri kümenizi seçip vurgulayın, ardından Excel Şeridi menüsünden Kutools > İçerik > Gelişmiş Satırları Birleştir seçeneğine tıklayın.

3. Açılan iletişim kutusunda, listeden Tarih/Saat sütununuzu seçin, Birincil Anahtar olarak işaretleyin, ardından değer sütununuzu (örneğin, Tutar) seçin ve Hesapla > Ortalama şeklinde yapılandırın. Onaylamak için Tamam’a tıklayın; Kutools her farklı tarih için ortalamaları hemen hesaplayacaktır.

Belirttiğiniz zaman aralıkları için ortalamalar anında hesaplanır ve analiz sürecinizi hızlandırır. Tarih gruplamanız günler yerine ayları veya yılları gösteriyorsa, sonuçlar buna göre toplanacaktır. Ayrıca tarihleri Kutools > Biçim > Tarih Formatını Uygula ile yeniden biçimlendirebilir ve işlemi Kutools > To Actual ile tamamlayabilirsiniz.

PivotTable veya eklentiler kullanmadan doğrudan formülle hesaplama tercih eden kullanıcılar için Excel’in yerleşik ÇOKORTALAMA, ÇOKTOPLA ve ÇOKSAY gibi işlevleri, belirli dönemler için esnek, hücre tabanlı ortalama hesaplamaları sunar. Bu yöntem, özelleştirilmiş hesaplamalara ihtiyacınız olduğunda, tablo yenilemelerinden kaçınmak istediğinizde veya sonuçları verilerinizin hemen yanında görmek istediğinizde mükemmeldir.

Günlük ortalama değerleri hesaplamak için formül kullanımına ilişkin bir örnek aşağıdadır:

1. Verilerinizin A sütununda (A2:A100) tarihler ve B sütununda (B2:B100) sayısal veriler içerdiğini varsayalım. Yeni bir sütuna (örneğin C2 hücresine), belirli bir gün için (örneğin A2 hücresindeki tarih için) ortalama hesaplayan aşağıdaki formülü girin:

=AVERAGEIFS(B$2:B$100, A$2:A$100, A2)

2. Formülü uygulamak için Enter tuşuna basın. Tüm tarihler için hesaplamayı yapmak üzere formülü verilerinizin yanındaki hücrelere aşağı doğru kopyalayın.
İpucu: Günlük ortalamaların yalnızca her benzersiz gün için bir kez görünmesini istiyorsanız, öncelikle verilerinizi benzersiz tarihlere göre sıralayın veya filtreleyin, ardından formülü buna göre uygulayın.

Rutin olarak çok büyük veri kümeleri işleyen ya da farklı dönemler için ortalama hesaplamalarını tekrarlayan kullanıcılar için, iş akışınızı VBA makrolarıyla otomatikleştirmek tutarlılığı ve verimliliği önemli ölçüde artırabilir. Makrolar, verileri gün, ay, çeyrek veya saate göre gruplandırıp ortalamaları otomatik olarak hesaplayarak manuel tekrarı tamamen ortadan kaldırır. Bu yaklaşım, ileri düzey Excel kullanıcıları ile hesaplamaların sıkça yeniden çalıştırılması veya yeni sayfalara uyarlanması gereken senaryolar için idealdir.

1. Başlamak için, Geliştirici Araçları > Visual Basic öğelerine tıklayarak VBA düzenleyicisini açın. Microsoft Visual Basic for Applications penceresi görüntülendiğinde, Ekle > Modül seçeneğine tıklayın ve aşağıdaki kodu modüle kopyalayın:

Sub AverageByPeriod()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim periodCol As String, valueCol As String
    Dim dict As Object
    Dim cell As Range
    Dim periodKey As String
    Dim i As Long, lastRow As Long
    Dim sumDict As Object, countDict As Object
    
    Set ws = ActiveSheet
    periodCol = "A" ' Date/Time column
    valueCol = "B" ' Value column
    lastRow = ws.Cells(ws.Rows.Count, periodCol).End(xlUp).Row
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set sumDict = CreateObject("Scripting.Dictionary")
    Set countDict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        ' Grouping by month example; change to format for day/hour/quarter if needed
        periodKey = Format(ws.Cells(i, periodCol).Value, "yyyy-mm")
        
        If Not dict.Exists(periodKey) Then
            dict.Add periodKey, dict.Count + 1
            sumDict.Add periodKey, ws.Cells(i, valueCol).Value
            countDict.Add periodKey, 1
        Else
            sumDict(periodKey) = sumDict(periodKey) + ws.Cells(i, valueCol).Value
            countDict(periodKey) = countDict(periodKey) + 1
        End If
    Next i
    
    ws.Cells(1, 4).Value = "Period"
    ws.Cells(1, 5).Value = "Average"
    
    i = 2
    Dim k As Variant
    For Each k In dict.Keys
        ws.Cells(i, 4).Value = k
        ws.Cells(i, 5).Value = sumDict(k) / countDict(k)
        i = i + 1
    Next k
End Sub

2. Kodu yapıştırdıktan sonra çalıştırmak için düğmesine tıklayın. Makro, verilerinizi (A ve B sütunlarından, 2. satırdan başlayarak) okuyacak, seçtiğiniz döneme göre (şu anda ay olarak ayarlanmış) gruplayacak ve her grubun ortalamasını D ve E sütunlarında gösterecektir.

İpuçları:

• Günlere göre gruplamak için Format(..., "yyyy-aa-gg") satırını değiştirin.
• Üç aylık (çeyreklik) gruplama için şunu kullanın:periodKey = "Q" & WorksheetFunction.RoundUp(Month(ws.Cells(i, periodCol).Value) /3,0) & "-" & Year(ws.Cells(i, periodCol).Value)
• Her zaman sütunlarınızın ()periodCol, valueCol) veri düzeninizle eşleştiğinden emin olun.

Önlemler:

• Boş bir sonuç veya hata alırsanız, gruplama sütununuzda boş hücrelerin veya tarih olmayan değerlerin bulunmadığından emin olun.
• Sütun atamalarını gerektiği gibi ayarlayın—verileriniz A ve B sütunlarından farklı sütunlarda başlıyorsa, periodCol ve valueCol değerlerini buna göre güncelleyin.
• Makroları çalıştırmadan önce işinizi mutlaka kaydedin; böylece istenmeyen veri değişikliklerini önleyebilirsiniz.

Excel'de yıla/ay/güne göre ortalama alma

Excel'de günün zaman damgalarının ortalamasını alma

Excel'de haftalık ortalama alma