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 ORTALAMA fonksiyonu size ağırlık veya miktarı dikkate almaksızın ortalama fiyatı verecektir, ancak bu tür durumlarda daha doğru bir yaklaşım ağırlıklı ortalama hesaplamaktır. Bu yöntem, nihai sonuca daha fazla etki etmesi için daha yüksek ağırlıklara veya frekanslara sahip öğeleri dikkate alarak gerçek birim maliyetini daha iyi yansıtır.

Ağırlıklı ortalama fiyatı hesaplamak için, aşağıdaki gibi TOPLAHÇARPIM ve TOPLA fonksiyonlarını birleştirin:

F2 gibi boş bir hücre seçin, ardından 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 Ağırlık sütununa, D2:D18 ise Fiyat sütununa atıfta bulunur. Veri düzeninize göre bu aralıkları gerektiği gibi ayarlayın. TOPLAHÇARPIM fonksiyonu her bir ağırlığı ilgili fiyata çarpar ve sonuçları toplar, TOPLA ise ağırlıkların toplamını verir — böylelikle doğru ağırlıklı ortalama elde edilir. Eşit uzunlukta aralıklar kullandığınızdan emin olun ve verilerinizde uyumsuz veya boş hücreler olmadığından emin olun, çünkü bu durum hesaplama hatalarına yol açabilir.

Hesaplanan ağırlıklı ortalamanın tercihinize göre çok fazla veya çok az ondalık basamak göstermesi durumunda, hücreyi seçin ve ardından Ondalık Artır düğmesine tıklayın veya Ondalık Azalt düğmesine tıklayın üzerindeki Ana Sayfa sekmesinden gösterilen ondalık basamakları gerektiği gibi ayarlayın.

#DEĞER! gibi bir hata ile karşılaşırsanız, her bir referans hücrenin sayısal bir değer içerdiğini ve aralıkların tutarlı olduğundan emin olun. Ayrıca, doğru sonuçlar için hesaplama aralığında başlık satırını hariç tutun. Daha büyük veri setleriyle çalışırken, açıklık ve bakım kolaylığı için adlandırılmış aralıklar kullanmayı düşünün.

Önceki formül tüm öğeler için ağırlıklı ortalama fiyatı hesaplar. Pratik analizde, tüm öğeler yerine belirli kategoriler için ağırlıklı ortalama hesaplamak isteyebilirsiniz; örneğin, yalnızca Elma için ağırlıklı ortalama fiyatı bulmak isteyebilirsiniz. Bu tür durumlarda, kriterlerinize dayalı bir koşul ekleyerek formülü geliştirebilirsiniz.

Bunu yapmak için, boş bir hücreyi (örneğin F8) 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 kriterlerinizi karşılayan ağırlıklı ortalamayı hesaplamak için Enter tuşuna basın. Bu formül, öğe koşula (“Elma” durumunda olduğu gibi) uyduğunda her bir ağırlık ve fiyat çiftini çarpar, toplar ve yalnızca bu öğeye ait ağırlıkların toplamına böler.

Not: Burada, B2:B18 Meyve sütunu, C2:C18 Ağırlık, ve D2:D18 Fiyat'tır. “Elma”yı başka bir öğeyle değiştirin. Bu yöntem, tek bir koşula göre filtrelemede iyi çalışır; birden fazla kritere göre filtreleme yapmanız gerekiyorsa (örneğin, meyve türü ve tedarikçi), yardımcı bir sütun veya daha gelişmiş bir formül gerekli olabilir.

Formülü uyguladıktan sonra, netlik için ondalıkları ayarlamak isteyebilirsiniz. Sonuç hücresini seçin ve Ondalık Artır veya Ondalık Azalt düğmelerini Ana Sayfa sekmesinden gösterilen ondalık basamakları değiştirin.

Formül beklenmedik bir sonuç döndürürse, kriterlerinizin hedef aralığınızda eşleştiğinden emin olun ve sayısal olması gereken sütunlarda boş hücreler veya metin girişleri olmadığından emin olun.

Bazı durumlarda, boyutu değişen, eksik değer içeren veya aynı anda birden fazla kriter uygulanması gereken aralıklar üzerinde sık sık ağırlıklı ortalama hesaplaması yapmanız gerekebilir. Formülleri veya aralıkları manuel olarak güncellemek yerine, bir VBA makrosuyla hesaplamayı otomatikleştirmek zaman kazandırır ve özellikle büyük veya düzenli olarak güncellenen veri setleriyle uğraşırken hata olasılığını azaltır.

İşte ağırlıklı ortalama için bir VBA makrosu oluşturup kullanma adımları:

1. Geliştirici > Visual Basic (veya Alt + F11) tuşlarına basarak Microsoft Visual Basic for Applications düzenleyici penceresini açın. Ardından Ekle > Modül'e tıklayın ve 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. Tuşa basın F5 (veya Çalıştır düğmesine tıklayın) yürütme işlemini başlatmak için.
Kriter aralığını (bu gerekmiyorsa atlanabilir), ağırlık aralığını ve değer aralığını adım adım seçmeniz istenecektir. Hesaplamayı filtrelemek için belirli bir kriter girebilir veya tüm verileri dikkate almak için boş bırakabilirsiniz. Makro dinamik veri aralıklarını destekler, bu nedenle tablonuz düzenli olarak büyür veya değişirse pratik bir seçenektir.

Son olarak, ağırlıklı ortalama sonucunu listeleyen bir ileti kutusu alacaksınız.

İpuçları:

• Bu yaklaşım, tekrarlayan ağırlıklı ortalama analizini otomatikleştirir ve ek filtreleme veya çıktı seçeneklerini işlemek için daha da genişletilebilir.
• Seçilen aralıkların eşit uzunlukta olduğundan ve veri tiplerinin tutarlı olduğundan emin olun.
• Gösterildiği gibi temel hata işleme ekleyin (örneğin, geçerli ağırlıklar bulunmadığında veya ağırlık toplamı sıfır olduğunda).
• Yalnızca filtrelenmiş/görünen satırlara uygulamak istiyorsanız, kodu özel hücre numaralandırması ile daha da geliştirebilirsiniz.

İzin veya makro 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ı hesaplama

Excel'de hareketli/ortalama kaydırma

Excel'de çalışan toplam /ortalama