Excel har gjort å beregne gjennomsnittet av flere celler til en veldig enkel oppgave - bare bruk funksjonen GJENNOMSNITT (GJENNOMSNITT). Men hva om noen verdier veier mer enn andre? For eksempel, i mange kurs har tester større vekt enn oppgaver. For slike tilfeller er det nødvendig å beregne vektlagt gjennomsnitt.
Excel har ikke en funksjon for å beregne det vektede gjennomsnittet, men det er en funksjon som vil gjøre det meste av jobben for deg: SUMPRODUCT (SUM PRODUKT). Og selv om du aldri har brukt denne funksjonen før, ved slutten av denne artikkelen vil du bruke den som en proff. Metoden vi bruker fungerer i alle versjoner av Excel så vel som andre regneark som for eksempel Google Sheets.
Vi forbereder bordet
Hvis du skal regne ut et vektet gjennomsnitt, trenger du minst to kolonner. Den første kolonnen (kolonne B i vårt eksempel) inneholder poengsummene for hver oppgave eller test. Den andre kolonnen (kolonne C) inneholder vektene. Mer vekt betyr mer innflytelse av oppgaven eller prøven på sluttkarakteren.
For å forstå hva vekt er, kan du tenke på det som en prosentandel av den endelige karakteren din. Dette er faktisk ikke tilfellet, siden vektene i dette tilfellet skal summere seg til 100%. Formelen som vi vil analysere i denne leksjonen vil beregne alt riktig og avhenger ikke av mengden vektene summerer seg til.
Vi legger inn formelen
Nå som tabellen vår er klar, legger vi til formelen i cellen B10 (enhver tom celle vil gjøre). Som med enhver annen formel i Excel, starter vi med et likhetstegn (=).
Den første delen av formelen vår er funksjonen SUMPRODUCT (SUM PRODUKT). Argumenter må stå i parentes, så vi åpner dem:
=СУММПРОИЗВ(
=SUMPRODUCT(
Deretter legger du til funksjonsargumentene. SUMPRODUCT (SUMPRODUKT) kan ha flere argumenter, men vanligvis brukes to. I vårt eksempel vil det første argumentet være et celleområde. B2:B9A som inneholder poengsummene.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Det andre argumentet vil være et celleområde C2:C9, som inneholder vektene. Disse argumentene må skilles med semikolon (komma). Når alt er klart, lukk parentesene:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
La oss nå legge til den andre delen av formelen vår, som vil dele resultatet beregnet av funksjonen SUMPRODUCT (SUMPRODUKT) ved summen av vektene. Vi vil diskutere senere hvorfor dette er viktig.
For å utføre divisjonsoperasjonen fortsetter vi den allerede angitte formelen med symbolet / (rett skråstrek), og skriv deretter funksjonen SUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
For funksjon SUM (SUM) vil vi spesifisere bare ett argument – et celleområde C2:C9. Ikke glem å lukke parentesene etter å ha skrevet inn argumentet:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Klar! Etter å ha trykket på tasten Enter, vil Excel beregne det vektede gjennomsnittet. I vårt eksempel vil det endelige resultatet være 83,6.
Hvordan fungerer det
La oss bryte ned hver del av formelen, og starter med funksjonen SUMPRODUCT (SUMPRODUKT) for å forstå hvordan det fungerer. Funksjon SUMPRODUCT (SUMPRODUKT) beregner produktet av hvert elements poengsum og vekt, og summerer deretter alle de resulterende produktene. Med andre ord finner funksjonen summen av produktene, derav navnet. Så for Oppdrag 1 gang 85 med 5, og for Testen gang 83 med 25.
Hvis du lurer på hvorfor vi må multiplisere verdiene i den første delen, forestill deg at jo større vekten på oppgaven er, jo flere ganger må vi vurdere karakteren for den. For eksempel, Oppgave 2 telt 5 ganger og Avsluttende eksamen – 45 ganger. Derfor Avsluttende eksamen har større innvirkning på sluttkarakteren.
Til sammenligning, når man beregner det vanlige aritmetiske gjennomsnittet, blir hver verdi bare tatt i betraktning én gang, det vil si at alle verdier har lik vekt.
Hvis du kunne se under panseret på en funksjon SUMPRODUCT (SUMPRODUKT), vi så at hun faktisk tror dette:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Heldigvis trenger vi ikke skrive en så lang formel fordi SUMPRODUCT (SUMPRODUCT) gjør alt dette automatisk.
En funksjon i seg selv SUMPRODUCT (SUMPRODUKT) gir oss et stort antall − 10450. På dette tidspunktet kommer den andre delen av formelen inn i bildet: /SUM(C2:C9) or /SUM(C2:C9), som returnerer resultatet til det normale scoreområdet, og gir svaret 83,6.
Den andre delen av formelen er veldig viktig fordi lar deg automatisk korrigere beregninger. Husk at vekter ikke trenger å være 100 %? Alt dette takket være den andre delen av formelen. For eksempel, hvis vi øker en eller flere vektverdier, vil den andre delen av formelen ganske enkelt dividere med den større verdien, noe som igjen resulterer i riktig svar. Eller vi kan gjøre vektene mye mindre, for eksempel ved å spesifisere verdier som 0,5, 2,5, 3 or 4,5, og formelen vil fortsatt fungere riktig. Det er flott, ikke sant?