Excel'de ağırlıklı ortalama nasıl hesaplanır?
Ortalama, verilere genel bir bakış elde etmenin en iyi yoludur. Ancak bazen ortalama size taraflı bir değer verir. Bu durumlarda [her zaman] en iyi yol ağırlıklı ortalamayı hesaplamaktır.
Ve en iyi yolun SUMproduct ve SUM işlevlerini birleştirerek bir formül kullanmak olduğunu buldum.
Bu yöntemin uygulanması basit ve kolaydır. Bu yüzden bugün bu yazımda SUMproduct ve SUM kullanarak Excel’de ağırlıklı ortalamanın nasıl hesaplanacağını sizlerle paylaşmak istiyorum.
Normal ve ağırlıklı ortalama arasındaki fark
Aşağıdaki örnekte normal ortalama olarak 1658 ve ağırlıklı ortalama olarak 1445’e sahibiz. Bu farkı iki noktada açıklığa kavuşturayım.
- Birincisi: 1658’i (ortalama) 68’le (miktar) çarparsanız 112742 elde edersiniz ki bu da toplam tutara eşit değildir. Ancak 1445’i (ağırlıklı ortalama) 68’le (miktar) çarparsanız toplam tutara eşit olan 98289 sonucunu elde edersiniz.
- İkincisi: Ürün-1 en düşük fiyata ve en yüksek adede sahipken, Ürün-4 en yüksek fiyata ve en düşük adede sahiptir.
Normal ortalamayı hesaplarken miktarı dikkate almazsınız ve miktarda bir değişiklik olması durumunda ortalama fiyata bir etkisi olmaz. Ağırlıklı ortalamada ise miktarı ağırlık olarak alabilirsiniz.
Excel’de ağırlıklı ortalamayı hesaplama adımı
Ağırlıklı ortalamayı hesaplamak için yukarıdaki örnekte size gösterdiğim veri tablosunun aynısını kullanıyoruz.
Excel dosyasını alın
Şimdi bu iki basit adımı izleyin.
- C7 hücresine şu formülü girin: =TOPLAÇARP(B2:B6;C2:C6)/TOPLA(B2:B6).
- Tamam’ı tıklayın.
Artık fiyat ve ürünün ağırlıklı ortalaması olarak 1445’e sahipsiniz. Söylediğim gibi SUMproduct, Excel’de ağırlıklı ortalama veya ağırlıklı ortalama hesaplamanın basit bir yoludur. SUMPROP’un en iyi yanı, tek bir hücrede bir diziyi çoğaltıp ekleyebilmesidir.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)Bu nasıl çalışır
Anlamak için bu formülü parçalara ayırmamız gerekiyor.
SUMproduct öncelikle tüm ürünler için fiyat ve miktar çarpımını hesaplayacak ve hepsinin toplamını döndürecektir. Bundan sonra bazı işlevler size miktarın toplamını verecektir. Ve sonunda her iki fonksiyonun değerlerini bölerek ağırlıklı ortalamayı elde edeceksiniz.
Şuna bir göz atın: ÖZET
Ağırlıklı hareketli ortalamayı hesaplayın
Biraz daha ileri gidelim. Veri analizi kısmına geçelim. Aynı formülü kullanarak ağırlıklı hareketli ortalamayı da hesaplayabilirsiniz.
Excel dosyasını alın
İşte adımlar:
- Aşağıdaki formülü D4 hücresine girin ve sonuna sürükleyin. =TOPLAÇARP(B2:B4;C2:C4)/TOPLA(C2:C4)
- Bu formülü sürüklediğinizde her ay için 3 aya göre hareketli ortalamayı hesaplayacaktır.
Bu nasıl çalışır
Yukarıdaki D4 hücresindeki anlık görüntüyü kontrol ederseniz Ocak, Şubat ve Mart aylarına ait hareketli ortalamayı elde edersiniz. Ve D5 hücresinde Şubat, Mart ve Nisan aylarının hareketli ortalamasını görüyorsunuz.
Yani her yeni aya geçtiğinizde, içinde bulunduğunuz ay ve son iki ay dahil olmak üzere hareketli ortalamayı alacaksınız. Formülünüzü sürüklerken hücre referanslarınızı göreceli hale getirmeniz yeterli.