Excel je izračun prosjeka više ćelija učinio vrlo lakim zadatkom – samo upotrijebite funkciju PROSJEK (PROSJEČNO). Ali što ako neke vrijednosti imaju veću težinu od drugih? Na primjer, u mnogim predmetima testovi imaju veću težinu od zadataka. Za takve slučajeve potrebno je izračunati prosječne težine.
Excel nema funkciju za izračun ponderiranog prosjeka, ali postoji funkcija koja će obaviti većinu posla umjesto vas: SUMPRODUCT (ZBIRNI PROIZVOD). Čak i ako nikada prije niste koristili ovu značajku, do kraja ovog članka koristit ćete je kao profesionalac. Metoda koju koristimo funkcionira u bilo kojoj verziji Excela, kao i u drugim proračunskim tablicama kao što su Google tablice.
Pripremamo stol
Ako ćete izračunati ponderirani prosjek, trebat će vam najmanje dva stupca. Prvi stupac (stupac B u našem primjeru) sadrži bodove za svaki zadatak ili test. Drugi stupac (stupac C) sadrži pondere. Veća težina znači veći utjecaj zadatka ili testa na konačnu ocjenu.
Da biste razumjeli što je težina, možete je zamisliti kao postotak vaše konačne ocjene. Zapravo, to nije slučaj, budući da bi u ovom slučaju utezi trebali iznositi 100%. Formula koju ćemo analizirati u ovoj lekciji izračunat će sve ispravno i ne ovisi o iznosu koji utezi zbroje.
Upisujemo formulu
Sada kada je naša tablica spremna, dodajemo formulu u ćeliju B10 (bilo koja prazna ćelija će poslužiti). Kao i kod svake druge formule u Excelu, počinjemo znakom jednakosti (=).
Prvi dio naše formule je funkcija SUMPRODUCT (ZBIRNI PROIZVOD). Argumenti moraju biti u zagradama, pa ih otvaramo:
=СУММПРОИЗВ(
=SUMPRODUCT(
Zatim dodajte argumente funkcije. SUMPRODUCT (SUMPRODUCT) može imati više argumenata, ali obično se koriste dva. U našem će primjeru prvi argument biti raspon ćelija. B2:B9A koji sadrži bodove.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Drugi argument bit će raspon ćelija C2:C9, koji sadrži utege. Ovi argumenti moraju biti odvojeni točkom i zarezom. Kada je sve spremno zatvorite zagrade:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Dodajmo sada drugi dio naše formule, koji će podijeliti rezultat izračunat funkcijom SUMPRODUCT (SUMPRODUCT) zbrojem težina. Kasnije ćemo razgovarati o tome zašto je to važno.
Za izvođenje operacije dijeljenja simbolom nastavljamo već unesenu formulu / (ravna kosa crta), a zatim napišite funkciju IZNOS (IZNOS):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Za funkciju IZNOS (SUM) navest ćemo samo jedan argument – raspon ćelija C2:C9. Ne zaboravite zatvoriti zagrade nakon unosa argumenta:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Spreman! Nakon pritiska tipke ući, Excel će izračunati ponderirani prosjek. U našem primjeru, konačni rezultat će biti 83,6.
Kako funkcionira
Razdvojimo svaki dio formule, počevši od funkcije SUMPRODUCT (SUMPRODUCT) da biste razumjeli kako funkcionira. Funkcija SUMPRODUCT (SUMPRODUCT) izračunava umnožak ocjene svake stavke i njene težine, a zatim zbraja sve dobivene umnoške. Drugim riječima, funkcija pronalazi zbroj umnožaka, otuda i naziv. Dakle za Zadaci 1 pomnožite 85 sa 5, i za Ispit pomnožite 83 sa 25.
Ako se pitate zašto trebamo množiti vrijednosti u prvom dijelu, zamislite da što je veća težina zadatka, to više puta moramo uzeti u obzir ocjenu za njega. Na primjer, Zadatak 2 brojao 5 puta i Završni ispit – 45 puta. Zato Završni ispit ima veći utjecaj na konačnu ocjenu.
Za usporedbu, pri izračunavanju uobičajene aritmetičke sredine svaka se vrijednost uzima u obzir samo jednom, odnosno sve vrijednosti imaju jednaku težinu.
Ako možete pogledati ispod haube funkcije SUMPRODUCT (SUMPRODUCT), vidjeli smo da ona zapravo vjeruje u ovo:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Srećom, ne trebamo pisati tako dugu formulu jer SUMPRODUCT (SUMPRODUCT) sve to radi automatski.
Funkcija sama po sebi SUMPRODUCT (SUMPRODUCT) vraća nam ogroman broj − 10450. U ovom trenutku, drugi dio formule stupa na scenu: /SUM(C2:C9) or /SUM(C2:C9), koji vraća rezultat u normalni raspon rezultata, dajući odgovor 83,6.
Drugi dio formule vrlo je važan jer vam omogućuje automatsko ispravljanje izračuna. Zapamtite da zbroj utega ne mora biti 100%? Sve to zahvaljujući drugom dijelu formule. Na primjer, ako povećamo jednu ili više vrijednosti težine, drugi dio formule jednostavno će se podijeliti s većom vrijednošću, što će opet rezultirati točnim odgovorom. Ili možemo učiniti težine puno manjim, na primjer navođenjem vrijednosti poput 0,5, 2,5, 3 or 4,5, a formula će i dalje ispravno raditi. Super je, zar ne?