Splošna formula
=SUMPRODUCT(weights,values)/SUM(weights)
Povzetek
Za izračun tehtanega povprečja lahko uporabite funkcijo SUMPRODUCT skupaj s funkcijo SUM. V prikazanem primeru je formula v G5, kopirana navzdol,:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
kjer je utež imenovano območje I5: K5.
Pojasnilo
Tehtano povprečje, imenovano tudi tehtano povprečje, je povprečje, pri katerem nekatere vrednosti štejejo več kot druge. Z drugimi besedami, nekatere vrednosti imajo večjo "težo". Tehtano povprečje lahko izračunamo tako, da vrednosti pomnožimo s povprečjem z ustreznimi utežmi, nato pa vsoto rezultatov delimo z vsoto uteži. V Excelu je to mogoče predstaviti s spodnjo generično formulo, kjer so uteži in vrednosti obseg celic:
=SUMPRODUCT(weights,values)/SUM(weights)
Na delovnem listu prikazani rezultati za 3 teste pojavijo v stolpcih C do E, in uteži so poimenovani obseg uteži (i5: K5). Formula v celici G5 je:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Če delamo od znotraj navzven, najprej uporabimo funkcijo SUMPRODUCT, da uteži pomnožimo z ustreznimi rezultati in seštejemo rezultat:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT najprej pomnoži ustrezne elemente obeh nizov, nato vrne vsoto zmnožka:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Rezultat se nato deli z vsoto uteži:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Ko se formula kopira v stolpec G, se poimenovane uteži obsega I5: K5 ne spremenijo, saj se obnaša kot absolutna referenca. Vendar se rezultati v C5: E5, vneseni kot relativni sklic, posodobijo v vsaki novi vrstici. Rezultat je tehtano povprečje za vsako ime na seznamu, kot je prikazano. Povprečje v stolpcu F se izračuna samo za referenco s funkcijo AVERAGE:
=AVERAGE(C5:E5)
Uteži, ki niso vsote 1
V tem primeru so uteži konfigurirane tako, da seštejejo do 1, tako da je delilec vedno 1, rezultat pa je vrednost, ki jo vrne SUMPRODUCT. Lepa lastnost formule pa je, da uteži ni treba seštevati do 1.
Na primer, za prva dva testa bi lahko uporabili utež 1 in utež 2 za finale (saj je finale dvakrat pomembnejše), tehtano povprečje pa bo enako:
V celici G5 je formula rešena takole:
=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
Opomba: V zgornjih zavitih oklepajih () so vrednosti obsegi, izraženi kot polja.
Prenos uteži
Funkcija SUMPRODUCT zahteva, da so mere matrike združljive. Če dimenzije niso združljive, bo SUMPRODUCT vrnil napako #VARUE. V spodnjem primeru so uteži enake prvotnemu primeru, vendar so navedene v navpičnem območju:
Za izračun tehtanega povprečja z isto formulo moramo uteži "obrniti" v vodoravno matriko s funkcijo TRANSPOSE, kot je ta:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Po zagonu programa TRANSPOSE se navpična matrika:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
postane:
=(0.25,0.25,0.5) // horizontal array
In od tega trenutka se formula obnaša kot prej.
Preberite več: navpična in vodoravna polja.
