Excel har gjort att beräkna medelvärdet av flera celler till en mycket enkel uppgift – använd bara funktionen GENOMSNITT (MEDEL). Men vad händer om vissa värden väger mer än andra? Till exempel i många kurser väger tester tyngre än uppgifter. För sådana fall är det nödvändigt att beräkna vägt genomsnitt.
Excel har ingen funktion för att beräkna det vägda medelvärdet, men det finns en funktion som kommer att göra det mesta av jobbet åt dig: SUMPRODUCT (SUMMAPRODUKT). Och även om du aldrig har använt den här funktionen förut, i slutet av den här artikeln kommer du att använda den som ett proffs. Metoden vi använder fungerar i alla versioner av Excel såväl som andra kalkylblad som Google Sheets.
Vi förbereder bordet
Om du ska beräkna ett vägt medelvärde behöver du minst två kolumner. Den första kolumnen (kolumn B i vårt exempel) innehåller poängen för varje uppgift eller test. Den andra kolumnen (kolumn C) innehåller vikterna. Mer vikt innebär mer inflytande av uppgiften eller provet på slutbetyget.
För att förstå vad vikt är kan du tänka på det som en procentandel av ditt slutbetyg. I själva verket är detta inte fallet, eftersom vikterna i det här fallet bör läggas till 100 %. Formeln som vi kommer att analysera i den här lektionen kommer att beräkna allt korrekt och beror inte på mängden som vikterna summerar till.
Vi anger formeln
Nu när vår tabell är klar lägger vi till formeln i cellen B10 (vilken som helst tom cell duger). Som med alla andra formel i Excel börjar vi med ett likhetstecken (=).
Den första delen av vår formel är funktionen SUMPRODUCT (SUMMAPRODUKT). Argument måste omges inom parentes, så vi öppnar dem:
=СУММПРОИЗВ(
=SUMPRODUCT(
Lägg sedan till funktionsargumenten. SUMPRODUCT (SUMPRODUKT) kan ha flera argument, men vanligtvis används två. I vårt exempel kommer det första argumentet att vara ett cellintervall. B2:B9En som innehåller poängen.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Det andra argumentet kommer att vara ett cellintervall C2:C9, som innehåller vikterna. Dessa argument måste separeras med semikolon (komma). När allt är klart, stäng parenteserna:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Låt oss nu lägga till den andra delen av vår formel, som kommer att dividera resultatet som beräknas av funktionen SUMPRODUCT (SUMPRODUKT) med summan av vikterna. Vi kommer att diskutera senare varför detta är viktigt.
För att utföra divisionsoperationen fortsätter vi den redan inmatade formeln med symbolen / (rakt snedstreck) och skriv sedan funktionen SUMMA (BELOPP):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
För funktion SUMMA (SUM) vi kommer bara att ange ett argument – ett cellintervall C2:C9. Glöm inte att stänga parentesen efter att ha angett argumentet:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Redo! Efter att ha tryckt på knappen ange, kommer Excel att beräkna det vägda genomsnittet. I vårt exempel blir det slutliga resultatet 83,6.
Hur det fungerar
Låt oss bryta ner varje del av formeln, börja med funktionen SUMPRODUCT (SUMPRODUKT) för att förstå hur det fungerar. Fungera SUMPRODUCT (SUMPRODUKT) beräknar produkten av varje artikels poäng och dess vikt, och summerar sedan alla resulterande produkter. Funktionen hittar med andra ord summan av produkterna, därav namnet. Så för Uppdrag 1 multiplicera 85 med 5, och för Testet multiplicera 83 med 25.
Om du undrar varför vi behöver multiplicera värdena i den första delen, föreställ dig att ju större vikten av uppgiften är, desto fler gånger måste vi överväga betyget för den. Till exempel, Uppgift 2 räknat 5 gånger och Slutprov – 45 gånger. Det är därför Slutprov har större inverkan på slutbetyget.
Som jämförelse, när man beräknar det vanliga aritmetiska medelvärdet, beaktas varje värde endast en gång, det vill säga alla värden har samma vikt.
Om man kunde titta under huven på en funktion SUMPRODUCT (SUMPRODUKT), vi såg att hon faktiskt tror detta:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Som tur är behöver vi inte skriva en så lång formel eftersom SUMPRODUCT (SUMPRODUCT) gör allt detta automatiskt.
En funktion i sig SUMPRODUCT (SUMPRODUCT) ger oss ett stort antal − 10450. Vid denna tidpunkt kommer den andra delen av formeln in i bilden: /SUMMA(C2:C9) or /SUMMA(C2:C9), vilket återställer resultatet till det normala intervallet för poäng, vilket ger svaret 83,6.
Den andra delen av formeln är mycket viktig eftersom du kan korrigera beräkningar automatiskt. Kom ihåg att vikter inte behöver läggas till 100 %? Allt detta tack vare den andra delen av formeln. Till exempel, om vi ökar ett eller flera viktvärden, kommer den andra delen av formeln helt enkelt att dividera med det större värdet, vilket återigen resulterar i rätt svar. Eller så kan vi göra vikterna mycket mindre, till exempel genom att ange värden som 0,5, 2,5, 3 or 4,5, och formeln kommer fortfarande att fungera korrekt. Det är bra, eller hur?