Genel formül
=SUMPRODUCT(weights,values)/SUM(weights)
Özet
Ağırlıklı ortalamayı hesaplamak için, SUMPRODUCT işlevini TOPLA işleviyle birlikte kullanabilirsiniz. Gösterilen örnekte, G5'teki formül aşağıdaki gibidir:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
nerede ağırlıkları adlı aralık I5 geçerli: K5.
Açıklama
Ağırlıklı ortalama olarak da adlandırılan ağırlıklı ortalama, bazı değerlerin diğerlerinden daha önemli olduğu bir ortalamadır. Başka bir deyişle, bazı değerler daha fazla "ağırlığa" sahiptir. Değerleri ortalamaya karşılık gelen ağırlıklarla çarpıp ardından sonuçların toplamını ağırlıkların toplamına bölerek ağırlıklı ortalamayı hesaplayabiliriz. Excel'de bu, ağırlıkların ve değerlerin hücre aralıkları olduğu aşağıdaki genel formülle temsil edilebilir:
=SUMPRODUCT(weights,values)/SUM(weights)
Gösterilen çalışma sayfasında, 3 testin puanları C ile E arasındaki sütunlarda görünür ve ağırlıklar adlandırılmış aralık ağırlıklarında (I5: K5) bulunur. G5 hücresindeki formül:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
İçten dışa doğru çalışarak, önce ağırlıkları karşılık gelen puanlarla çarpmak ve sonucu toplamak için SUMPRODUCT işlevini kullanıyoruz:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT önce iki dizinin karşılık gelen öğelerini birlikte çarpar, ardından ürünün toplamını döndürür:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Sonuç daha sonra ağırlıkların toplamına bölünür:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Formül G sütununa kopyalandığında, adlandırılmış aralık ağırlıkları I5: K5, mutlak bir referans gibi davrandığı için değişmez. Ancak, göreceli referans olarak girilen C5: E5'teki puanlar her yeni satırda güncellenir. Sonuç, gösterilen listedeki her isim için ağırlıklı ortalamadır. F sütunundaki ortalama, yalnızca ORTALAMA işlevi ile referans olarak hesaplanır:
=AVERAGE(C5:E5)
Toplamı 1 olmayan ağırlıklar
Bu örnekte, ağırlıklar 1'e kadar toplanacak şekilde yapılandırılmıştır, bu nedenle bölen her zaman 1'dir ve sonuç SUMPRODUCT tarafından döndürülen değerdir. Bununla birlikte, formülün güzel bir özelliği, ağırlıkların 1'e kadar eklenmesine gerek olmamasıdır.
Örneğin, ilk iki test için 1 ağırlık ve final için 2 ağırlık kullanabiliriz (çünkü final iki kat daha önemlidir) ve ağırlıklı ortalama aynı olacaktır:
G5 hücresinde formül şu şekilde çözülür:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Not: Yukarıdaki süslü parantezler () içindeki değerler, diziler olarak ifade edilen aralıklardır.
Ağırlıkları değiştirme
SUMPRODUCT işlevi, dizi boyutlarının uyumlu olmasını gerektirir. Boyutlar uyumlu değilse, SUMPRODUCT bir # DEĞER hatası döndürür. Aşağıdaki örnekte ağırlıklar orijinal örnekle aynıdır, ancak dikey bir aralıkta listelenmiştir:
Aynı formülle ağırlıklı ortalamayı hesaplamak için, ağırlıkları aşağıdaki gibi TRANSPOSE işlevi ile yatay bir diziye "çevirmemiz" gerekir:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
TRANSPOSE çalıştıktan sonra, dikey dizi:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
şu hale gelir:
=(0.25,0.25,0.5) // horizontal array
Ve bu noktadan sonra formül eskisi gibi davranıyor.
Daha fazlasını okuyun: dikey ve yatay diziler.
