Excel'de haftalık ortalama nasıl hesaplanır?

Diyelim ki aşağıdaki ekran görüntüsüne benzer bir satın alma tablonuz var. Bu örnek, Excel'in yardımcı sütunlarını ve yerleşik fonksiyonlarını kullanarak haftalık ortalamaları adım adım hesaplamayı detaylı olarak göstermektedir. Bu yöntem, adım adım manuel işlemler tercih eden ve hesaplama sürecinin tamamının görünür olmasını isteyen çoğu kullanıcı için uygundur.

Adım 1: Her satırı hafta numarasıyla etiketlemek için bir yardımcı sütun ekleyin:

Girin HaftaNUM Hücre D1'e yazın ve ardından Hücre D2'ye şu formülü girin =HAFTANUM(A2;2)(Burada A2, Tarih/Saat sütunundaki satın alma tarihinize atıfta bulunuyor. İkinci argüman olan 2, Excel'e haftanın Pazartesi günü başladığını varsaymasını söyler, bu da çoğu iş senaryosuna uygun gelir. Eğer haftanız Pazar günü başlıyorsa, 1 kullanabilirsiniz). Daha sonra, hafta numaralarını doldurmak için dolgu tutamacını aşağı çekin. Şimdi her satırın hangi haftaya ait olduğunu göreceksiniz.

Adım 2: Bu adım, verileri haftaya göre gruplamaya olanak tanır, ancak farklı yıllardaki aynı haftaları ayırt etmek isteyebilirsiniz. Bu durumda, yılı belirtmek için ekstra bir yardımcı sütun ekleyin:

Girin Yıl Hücre E1’e yazın. Hücre E2’ye şunu yazın =YIL(A2) (yeniden, A2 satın alma tarih hücresidir). Dolgu tutamacını aşağı çekin ve artık verilerinizde hafta ve yıl sütunları daha doğru gruplama için yer alıyor.

Adım 3: Şimdi haftalık ortalamayı hesaplayın. Hücre F1’e yazın Ortalama. Hücre F2’ye aşağıdaki formülü girin: =EĞER(VE(D2=D1;E2=E1);"";ORTALAMAYA.GÖRE($C$2:$C$39;$D$2:$D$39;D2;$E$2:$E$39;E2)). Bundan sonra, dolgu tutamacını hedeflenen aralığa kadar aşağı çekin.

Bu formül, aynı yıla ait aynı haftadaki miktarların ortalamasını hesaplar ve her benzersiz (hafta, yıl) kombinasyonunun ilk örneğinde sonucu gösterir (aynı gruptaki diğer satırlar boş gösterilir).

Notlar:
(1) Bir yılda bir hafta için birden fazla giriş varsa, yalnızca ilgili ilk satır ortalama değeri gösterir; diğer satırlar açıklık için boş bırakılır.
(2) D1 ve D2 hafta numarası sütunlarına, E1 ve E2 yıl sütunlarına atıfta bulunur, $C$2:$C$39 ortalamasını almak istediğiniz miktar değerleri aralığıdır, $D$2:$D$39 hafta numarası sütunudur, $E$2:$E$39 ise yıl sütunudur; bu aralıkları ve referansları veri kümenize göre gerektiği gibi ayarlayın.
(3) Eğer yılları göz önünde bulundurmak istemiyorsanız ve yalnızca hafta numarasına göre ortalama almak istiyorsanız, F2'de şu formülü kullanın: =EĞER.ORTALAMA($D$2:$D$39;D2;$C$2:$C$39) ve aşağıya doğru doldurun. Bu, yılları ayırt etmeden basit haftalık ortalamayı verecektir. Aşağıda gösterilen örnek sonuç:

İpucu: Çok büyük veri kümeleri için formülleri aşağıya doğru kopyalamak Excel'i yavaşlatabilir. Böyle durumlarda, daha ölçeklenebilir bir çözüm için aşağıdaki VBA otomasyonu veya pivot tablo yaklaşımına bakın.

#BÖL/0! hataları alıyorsanız, bu genellikle veri kümenizde o haftaya karşılık gelen hiçbir giriş olmadığı anlamına gelir. Yardımcı sütunlarınızın ve miktar sütunlarınızın boş veya uyumsuz veri türleri içermediğinden emin olun.

Bu yöntem, Kutools for Excel’in Gelişmiş Satırları Birleştir yardımcı programını tanıtır ve bu sayede haftalık ortalamaları formüller yazmadan toplu şekilde hesaplamayı kolaylaştırır. Kutools, özellikle orta ila büyük listelerde tekrarlayan gruplandırma ve hesaplamalar yaparken karmaşık gruplama ve ortalama görevlerini basitleştirerek zamandan tasarruf eder ve manuel hataları azaltır.

1. Bir yardımcı sütunda Hafta Hücre D1’e yazın. D2’ye şu formülü yazın =HAFTANUM(A2;2) (A2 = tarih hücresi). Her kaydın hafta numarasıyla etiketlenmesi için aşağıya doğru doldurun. Bu sütun, Kutools'un kayıtları haftaya göre gruplamasına olanak tanır.

2. Yeni Hafta sütununu içeren tabloyu seçin ve ardından Kutools > Metin > Gelişmiş Satırları Birleştirseçeneğine tıklayın. Kutools seçili alanlar üzerinde çalıştığından, seçiminizin ilgili verileri, birleştirilecek sütunları ve haftalık gruplama sütununu kapsadığından emin olun.

3. Açılan Sütunlara Göre Satırları Birleştir iletişim kutusunda aşağıdaki işlemleri ayarlayın:
(1) Meyve sütununa tıklayın ve bunu Birleştir (virgülle ayırıcı olarak kullanarak) ayarlayın, böylece meyve adlarını haftalık olarak bir arada tutar.
(2) Miktar sütunu için işlemi Hesapla > Ortalama olarak ayarlayın, böylece haftalık ortalama otomatik olarak sağlanır.
(3) Hafta sütununu Anahtar olarak atayın, böylece gruplama haftalara göre yapılır.
(4) Tıklayın Tamam işlemi başlatmak için.

Kutools, tüm kayıtları hızlıca haftalara göre gruplayacak, ilgili tüm meyveleri listeleyecek ve aşağıdaki gibi temiz bir yeni tabloda ortalamayı gösterecektir:

Avantajlar: Büyük tablolar için hızlı işleme, minimum manuel hata ve net çıktı. Notlar: Başlamadan önce yardımcı sütunlarınızın doğru olduğundan emin olun, çünkü oradaki hatalar sonuçları etkileyebilir. Eğer hafta numaraları birden fazla yılda tekrar ediyorsa, bir Yıl yardımcı sütunu eklemeniz ve doğruluk için hafta ve yıla göre gruplamanız gerekebilir.

Pivot Tablo yöntemi, Excel'in etkileşimli özetleme yeteneklerinden yararlanarak verilerinizi haftalara göre gruplandırır ve haftalık ortalamaları hızlıca hesaplar. Bu yaklaşım, görsel sürükle-bırak tercih eden, esnek veri keşfi ve kaynak verileriniz değiştiğinde sonuçları kolayca güncellemek isteyen kullanıcılar için en iyisidir. Büyük veri kümeleri için pratiktir ve manuel formüllere gerek duymaz.

Adım1: Tablonuza hafta numarası için bir yardımcı sütun ekleyin. Boş bir sütunun ilk hücresinde (örneğin, D2), şu formülü girin: =HAFTANUM(A2;2), satın alma tarihine atıfta bulunarak. Her satırın yılın hangi haftasına ait olduğunu etiketlemek için aşağıya doğru doldurun.

Adım2: Yeni Hafta sütununu da içeren tüm tablonuzu seçili hale getirin, ardından Ekle > PivotTablo'ya gidin. İletişim kutusunda, aralığınızı onaylayın ve yeni veya mevcut bir çalışma sayfası seçin.

Adım3: Pivot Tablo Alan Listesi panelinde:

• Hafta sütununu Satırlar alanına sürükleyin.
• İsteğe bağlı olarak, verileri yıla göre ayırmak için bir Yıl sütunu (=YIL(A2) ile oluşturulan) Hafta'nın üzerine Satırlara sürükleyin.
• Miktar sütununu Değerler alanına sürükleyin. Açılır menüsünü tıklayın > Alan Ayarları ve Ortalama olarak ayarlayın.

Pivot Tablo, artık her hafta veya (yıl, hafta) grubu için ortalama miktarı otomatik olarak görüntüleyecektir. Yeni veri ekledikten sonra tabloyu herhangi bir zamanda yenileyebilir ve sonuçları anında güncelleyebilirsiniz. Bu yöntem, diğer özet istatistikleri keşfetmek veya gerektiğinde farklı zaman dönemlerine göre yeniden gruplamak için özellikle esnektir.

İpuçları: Verileriniz birden fazla yılı kapsıyorsa, farklı yıllardaki aynı haftaları birleştirmemek için her zaman Yıl ve Hafta alanlarını ekleyin. Eğer haftalar için tarih aralıklarını göstermek istiyorsanız, her haftanın başlangıç tarihini listelemek için hesaplanmış bir yardımcı sütun ekleyebilirsiniz. Boş satırlar veya beklenmedik sonuçlar görüyorsanız, yardımcı sütunlarınızda boşluk olmadığından ve tablonuzun en iyi sonuçlar için Excel tablosu olarak düzgün biçimlendirildiğinden (Ctrl+T) emin olun.

Avantajlar: Manuel formüller yok, veri değişikliklerine göre dinamik güncelleme, büyük veri kümelerini sorunsuz destekler. Sınırlamalar: Hafta numarası için bir yardımcı sütun gerektirir; orijinal tarihler tanınan bir tarih formatında olmalıdır.

Excel'de yıl/ay/tarih bazında ortalama hesaplama

Excel'de günün ortalama zaman damgası

Excel'de pivot tablo ile günlük/aylık/üç aylık/saatlik ortalama hesaplama