Excel je učinio izračunavanje prosjeka više ćelija vrlo lakim zadatkom – samo koristite funkciju AVERAGE (PROSJEČNO). Ali šta ako neke vrijednosti imaju veću težinu od drugih? Na primjer, u mnogim kursevima testovi imaju veću težinu od zadataka. Za takve slučajeve potrebno je izračunati prosjećna težina.
Excel nema funkciju za izračunavanje ponderisanog prosjeka, ali postoji funkcija koja će obaviti većinu posla umjesto vas: SUMPRODUCT (SUM PROIZVOD). Čak i ako nikada ranije niste koristili ovu funkciju, do kraja ovog članka ćete je koristiti kao profesionalac. Metoda koju koristimo radi u bilo kojoj verziji Excela, kao i u drugim tabelama kao što su Google Sheets.
Pripremamo sto
Ako ćete izračunati ponderisani prosjek, trebat će vam najmanje dvije kolone. Prva kolona (kolona B u našem primjeru) sadrži bodove za svaki zadatak ili test. Druga kolona (kolona C) sadrži pondere. Veća težina znači veći uticaj 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, jer bi u ovom slučaju ponderi trebali iznositi 100%. Formula koju ćemo analizirati u ovoj lekciji izračunat će sve ispravno i ne ovisi o iznosu koji zbrajaju težine.
Unosimo formulu
Sada kada je naša tabela spremna, dodajemo formulu u ćeliju B10 (bilo koja prazna ćelija će poslužiti). Kao i kod svake druge formule u Excelu, počinjemo sa znakom jednakosti (=).
Prvi dio naše formule je funkcija SUMPRODUCT (SUM PROIZVOD). Argumenti se moraju staviti u zagrade, pa ih otvaramo:
=СУММПРОИЗВ(
=SUMPRODUCT(
Zatim dodajte argumente funkcije. SUMPRODUCT (SUMPRODUCT) može imati više argumenata, ali se obično koriste dva. U našem primjeru, prvi argument će biti raspon ćelija. B2:B9A koji sadrži rezultate.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Drugi argument će biti raspon ćelija C2:C9, koji sadrži težine. Ovi argumenti moraju biti odvojeni tačkom i zarezom (zarezom). Kada je sve spremno, zatvorite zagrade:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Sada dodajmo drugi dio naše formule, koji će podijeliti rezultat izračunat funkcijom SUMPRODUCT (SUMPRODUCT) zbirom pondera. Kasnije ćemo raspravljati zašto je ovo važno.
Da bismo izvršili operaciju dijeljenja, nastavljamo već unesenu formulu sa simbolom / (ravna kosa crta), a zatim napišite funkciju SUMA (SUMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Za funkciju SUMA (SUM) specificiraćemo samo jedan argument – opseg ćelija C2:C9. Ne zaboravite zatvoriti zagrade nakon što unesete argument:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Spremni! Nakon pritiska na tipku ući, Excel će izračunati ponderisani prosjek. U našem primjeru, konačni rezultat će biti 83,6.
Kako radi
Razložimo svaki dio formule, počevši od funkcije SUMPRODUCT (SUMPRODUCT) da biste razumjeli kako to funkcionira. Funkcija SUMPRODUCT (SUMPRODUCT) izračunava proizvod rezultata svake stavke i njene težine, a zatim zbraja sve rezultirajuće proizvode. Drugim riječima, funkcija pronalazi zbir proizvoda, otuda i naziv. Dakle za Zadaci 1 pomnožite 85 sa 5, i za Test 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, više puta moramo uzeti u obzir ocjenu za njega. Na primjer, Zadatak 2 izbrojano 5 puta i Završni ispit – 45 puta. Zbog toga Završni ispit ima veći uticaj na konačnu ocjenu.
Za usporedbu, pri izračunavanju uobičajene aritmetičke sredine svaka vrijednost se uzima u obzir samo jednom, odnosno sve vrijednosti imaju jednaku težinu.
Ako biste mogli pogledati ispod haube neke 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 moramo pisati tako dugačku formulu jer SUMPRODUCT (SUMPRODUCT) sve ovo radi automatski.
Funkcija sama po sebi SUMPRODUCT (SUMPRODUCT) nam vraća ogroman broj − 10450. U ovom trenutku na scenu stupa drugi dio formule: /SUM(C2:C9) or /SUM(C2:C9), koji vraća rezultat u normalan raspon rezultata, dajući odgovor 83,6.
Drugi dio formule je vrlo važan jer vam omogućava da automatski ispravite proračune. Zapamtite da težine ne moraju da budu do 100%? Sve to zahvaljujući drugom dijelu formule. Na primjer, ako povećamo jednu ili više vrijednosti težine, drugi dio formule će jednostavno podijeliti s većom vrijednošću, što će opet rezultirati tačnim odgovorom. Ili možemo učiniti težine mnogo manjim, na primjer specificiranjem vrijednosti poput 0,5, 2,5, 3 or 4,5, a formula će i dalje raditi ispravno. Odlično je, zar ne?