Generička formula
=SUMPRODUCT(weights,values)/SUM(weights)
Sažetak
Da biste izračunali ponderirani prosjek, možete koristiti funkciju SUMPRODUCT zajedno sa funkcijom SUM. U prikazanom primjeru, formula u G5, kopirana dolje, je:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
gdje je težina imenovani raspon I5: K5.
Obrazloženje
Ponderirani prosjek, koji se naziva i ponderirana srednja vrijednost, prosjek je u kojem se neke vrijednosti računaju više od drugih. Drugim riječima, neke vrijednosti imaju veću "težinu". Ponderirani prosjek možemo izračunati množenjem vrijednosti s prosjekom odgovarajućim ponderima, a zatim zbroj rezultata podijeliti zbrojem pondera. U Excelu se to može prikazati generičkom formulom u nastavku, gdje su težine i vrijednosti rasponi ćelija:
=SUMPRODUCT(weights,values)/SUM(weights)
Na prikazanom radnom listu, rezultati za 3 ispitivanja pojavljuju se u stupcima C do E, a ponderi su u imenovanim ponderima opsega (I5: K5). Formula u ćeliji G5 je:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Radeći iznutra prema van, prvo koristimo funkciju SUMPRODUCT za pomnoženje pondera odgovarajućim rezultatima i zbrajanje rezultata:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT prvo množi odgovarajuće elemente dva polja, a zatim vraća zbroj proizvoda:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Rezultat se zatim dijeli zbrojem pondera:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Kako se formula kopira dolje u stupac G, imenovani utezi raspona I5: K5 se ne mijenjaju, jer se ponaša kao apsolutna referenca. Međutim, ocjene u C5: E5, unesene kao relativna referenca, ažuriraju se u svakom novom retku. Rezultat je ponderirani prosjek za svako ime na popisu kako je prikazano. Prosjek u stupcu F izračunava se samo za referencu s AVERAGE funkcijom:
=AVERAGE(C5:E5)
Ponderi koji se ne zbrajaju na 1
U ovom su primjeru težine konfigurirane da zbrajaju do 1, tako da je djelitelj uvijek 1, a rezultat je vrijednost koju vraća SUMPRODUCT. Međutim, lijepa značajka formule je da težine ne trebaju zbrajati do 1.
Na primjer, mogli bismo upotrijebiti uteg 1 za prva dva testa i uteg 2 za finale (jer je finale dvostruko važnije), a ponderirani prosjek bit će isti:
U ćeliji G5 formula je riješena ovako:
=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
Napomena: vrijednosti u kovrčavim zagradama () gore su rasponi izraženi kao nizovi.
Transponiranje utega
Funkcija SUMPRODUCT zahtijeva da dimenzije niza budu kompatibilne. Ako dimenzije nisu kompatibilne, SUMPRODUCT će vratiti pogrešku #VALUE. U donjem primjeru težine su jednake izvornom primjeru, ali su navedene u okomitom rasponu:
Da bismo izračunali ponderirani prosjek s istom formulom, moramo "preokrenuti" utege u vodoravni niz s funkcijom TRANSPOSE:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Nakon pokretanja TRANSPOSE, okomiti niz:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
postaje:
=(0.25,0.25,0.5) // horizontal array
I od ove se točke formula ponaša kao i prije.
Pročitajte više: okomiti i vodoravni nizovi.
