Excel’de ağırlıklı ortalama nasıl hesaplanır?

Aşağıdaki ekran görüntüsünde gösterildiği gibi bir alışveriş listeniz olduğunu varsayalım. Excel’in AVERAGE (ORTALAMA) işlevi, ağırlık veya miktar dikkate alınmadan ortalama fiyatı verir; ancak bu tür durumlarda daha doğru sonuç almak için ağırlıklı ortalama hesaplamak gerekir. Böylece, daha yüksek ağırlığa veya sıklığa sahip öğeler sonucu daha fazla etkileyerek gerçek birim maliyetini çok daha iyi yansıtır.

Ağırlıklı ortalama fiyatı hesaplamak için aşağıdaki gibi SUMPRODUCT (ÇARPIMTOPLA)ve SUM (TOPLA)işlevlerinin birleşimini kullanın:

F2 gibi boş bir hücre seçin ve aşağıdaki formülü girin:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

ve sonucu almak için Enter tuşuna basın.

Not: Bu formülde C2:C18 aralığı Ağırlık sütununu, D2:D18 aralığı ise Fiyat sütununu temsil eder. Bu aralıkları kendi veri yapınıza göre güncelleyin. SUMPRODUCT (ÇARPIMTOPLA) işlevi her ağırlığı ilgili fiyatla çarparak sonuçları toplar; SUM (TOPLA) işlevi ise ağırlıkların toplamını alır ve böylece doğru ağırlıklı ortalamayı hesaplar. Aralıklarınızın eşit uzunlukta olduğundan ve verilerinizde uyumsuz ya da boş hücreler bulunmadığından emin olun; aksi takdirde hesaplama hataları oluşabilir.

Hesaplanan ağırlıklı ortalama, tercihinize göre çok fazla veya çok az ondalık basamak gösteriyorsa, hücreyi seçin ve ardından görüntülenen ondalık basamak sayısını gerektiği gibi ayarlamak için Ondalık Arttır düğmesine veya Ondalık Azalt düğmesine tıklayın; bu düğmeleri Ana Sayfa sekmesinden bulabilirsiniz.

#VALUE! gibi bir hata alırsanız, başvurulan her hücrenin sayısal bir değer içerdiğinden ve aralıkların tutarlı olduğundan emin olun. Doğru sonuçlar elde etmek için hesaplama aralığınıza başlık satırını dahil etmemeye özen gösterin. Daha büyük veri kümeleriyle çalışırken, açıklık ve bakım kolaylığı açısından adlandırılmış aralıkları tercih etmeyi düşünün.

Önceki formül, tüm öğeler için ağırlıklı ortalama fiyatı hesaplar. Ancak pratik analizlerde genellikle yalnızca belirli kategoriler için ağırlıklı ortalama fiyat istenir; örneğin sadece "Elma" için ağırlıklı ortalama fiyatı bulmak gibi. Böyle durumlarda formülü, kriterlerinize uygun bir koşul ekleyerek özelleştirebilirsiniz.

Bunu yapmak için F8 gibi boş bir hücre seçin ve aşağıdaki formülü girin:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Ardından, belirli kriterlerinize uyan ağırlıklı ortalamayı hesaplamak için Enter tuşuna basın. Bu formül, öğe koşuluyla eşleştiğinde (örneğin “Elma”) yalnızca ilgili ağırlık ve fiyat çiftini çarpar, bu çarpımları toplar ve sonucu söz konusu öğe için toplam ağırlığa böler.

Not: Burada B2:B18 meyve sütunu, C2:C18 ağırlık ve D2:D18 fiyat sütunudur. “Elma” yerine gerektiğinde başka bir öğe yazabilirsiniz. Bu yöntem, tek bir koşula göre filtreleme yapmak için idealdir; ancak birden fazla kritere (örneğin meyve türü ve tedarikçi) göre filtreleme yapmanız gerekiyorsa yardımcı bir sütun veya daha gelişmiş bir formül kullanmanız gerekir.

Formülü uyguladıktan sonra netlik açısından ondalık basamak sayısını ayarlamak isteyebilirsiniz. Sonuç hücresini seçin ve gösterilen ondalık basamak sayısını değiştirmek için Ondalık Arttır veya Ondalık Azalt düğmelerini Ana Sayfa sekmesinden kullanın.

Formül beklenmedik bir sonuç verirse, kriterlerinizin hedef aralığınızda eşleşen değerler içerdiğinden emin olun ve sayısal olması gereken sütunlarda boş hücre veya metin girişi bulunup bulunmadığını kontrol edin.

Bazı durumlarda, boyutu değişken, eksik değerler içeren veya aynı anda birden fazla kritere göre esnek filtreleme gerektiren aralıklar üzerinde ağırlıklı ortalamaları sık sık hesaplamanız gerekebilir. Formülleri veya aralıkları manuel olarak güncellemek yerine, bu işlemi bir VBA makrosuyla otomatikleştirmek zaman kazandırır ve özellikle büyük ya da düzenli olarak güncellenen veri kümeleriyle çalışırken hata riskini önemli ölçüde azaltır.

Ağırlıklı ortalamalar için VBA makrosu oluşturmak ve kullanmak şöyle yapılır:

1. Geliştirici > Visual Basicseçeneğine tıklayın (veya)Alt + F11 tuşlarına basın) ve Microsoft Visual Basic for Applications düzenleyici penceresini açın. Ardından Ekle > Modül seçeneğine tıklayıp aşağıdaki kodu yeni modül penceresine yapıştırın:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. Çalıştırmak için F5tuşuna basın (veya) Çalıştır düğmesine tıklayın).
Ardından sırayla aralıkları seçmeniz istenecektir (kriter aralığı—gerekiyorsa atlanabilir, ağırlık aralığı ve değer aralığı). Hesaplamanızı filtrelemek için belirli kriterler girebilir veya tüm verileri dikkate almak için boş bırakabilirsiniz. Tablonuz düzenli olarak büyüyüp değişiyorsa, makro dinamik aralık desteğine sahiptir—bu da onu son derece pratik kılar!

Son olarak, ağırlıklı ortalama sonucunu gösteren bir ileti kutusu görüntüleyeceksiniz.

İpuçları:

• Bu yaklaşım, tekrarlı ağırlıklı ortalama analizini otomatikleştirir ve ek filtreleme ya da çıktı seçeneklerini işlemek üzere daha da genişletilebilir hale getirilir.
• Aralık seçimleri eşit uzunlukta olmalı ve veri türleri tutarlı olmalıdır.
• Gösterildiği gibi temel hata işleme mekanizmalarını ekleyin (örneğin, geçerli bir ağırlık bulunamadığında veya ağırlıkların toplamı sıfıra eşit olduğunda).
• Yalnızca filtrelenmiş/görünür satırlara uygulamak istiyorsanız, kodu özel hücre numaralandırmasıyla daha da geliştirebilirsiniz.

Makroyla ilgili izin veya güvenlik sorunlarıyla karşılaşırsanız, kodu çalıştırmadan önce Excel ayarlarınızda makroların etkin olduğundan emin olun.

Excel’de yuvarlama ile ortalama aralık

Excel’de ortalama değişim oranı

Excel’de ortalama/bileşik yıllık büyüme oranını hesaplama

Excel’de hareketli/tekrarlanan ortalama

Excel’de kümülatif toplam /ortalama