Når vi beregner et simpelt gennemsnit af et givet sæt værdier, antages det, at alle værdierne har samme vægt eller betydning.
For eksempel, hvis du optræder til eksamen, og alle prøverne har en lignende vægt, så vil gennemsnittet af dine samlede karakterer også være det vægtede gennemsnit af dine scoringer.
Men i virkeligheden er dette næppe tilfældet.
Nogle opgaver er altid vigtigere end de andre. Nogle eksamener er vigtigere end de andre.
Og det er der Vægtet gennemsnit kommer ind i billedet.
Her er lærebogens definition af vægtet gennemsnit:
Lad os nu se, hvordan du beregner det vejede gennemsnit i Excel.
Beregning af vægtet gennemsnit i Excel
I denne vejledning lærer du, hvordan du beregner det vejede gennemsnit i Excel:
- Brug af SUMPRODUCT -funktionen.
- Brug af SUM -funktionen.
Så lad os komme i gang.
Beregning af vægtet gennemsnit i Excel - SUMPRODUCT -funktion
Der kan være forskellige scenarier, hvor du skal beregne det vægtede gennemsnit. Nedenfor er tre forskellige situationer, hvor du kan bruge SUMPRODUCT -funktionen til at beregne vægtet gennemsnit i Excel
Nedenfor er tre forskellige situationer, hvor du kan bruge SUMPRODUCT -funktionen til at beregne vægtet gennemsnit i Excel
Eksempel 1 - Når vægtene tilføjer op til 100%
Antag, at du har et datasæt med karakterer scoret af en elev i forskellige eksamener sammen med vægte i procent (som vist nedenfor):
I ovenstående data får en elev karakterer i forskellige evalueringer, men i sidste ende skal den have en endelig score eller karakter. Et simpelt gennemsnit kan ikke beregnes her, da betydningen af forskellige evalueringer varierer.
For eksempel bærer en quiz med en vægt på 10% dobbelt så meget vægt som en opgave, men en fjerdedel af vægten sammenlignet med eksamen.
I et sådant tilfælde kan du bruge SUMPRODUCT -funktionen til at få det vægtede gennemsnit af scoren.
Her er formlen, der giver dig det vejede gennemsnit i Excel:
= SUMPRODUCT (B2: B8, C2: C8)
Sådan fungerer denne formel: Excel SUMPRODUCT -funktion multiplicerer det første element i det første array med det første element i det andet array. Derefter multiplicerer det det andet element i det første array med det andet element i det andet array. Og så videre…
Og endelig tilføjer det alle disse værdier.
Her er en illustration for at gøre det klart.
Eksempel 2 - Når vægte ikke tilføjer op til 100%
I ovenstående tilfælde blev vægtene tildelt på en sådan måde, at det samlede beløb blev op til 100%. Men i virkelige scenarier er det måske ikke altid tilfældet.
Lad os se på det samme eksempel med forskellige vægte.
I ovenstående tilfælde tilføjer vægte op til 200%.
Hvis jeg bruger den samme SUMPRODUCT -formel, vil det give mig det forkerte resultat.
I ovenstående resultat har jeg fordoblet alle vægte, og det returnerer den vejede gennemsnitlige værdi til 153,2. Nu ved vi, at en elev ikke kan få mere end 100 ud af 100, uanset hvor genial han/hun er.
Grunden til dette er, at vægtene ikke fylder op til 100%.
Her er formlen, der får dette sorteret:
= SUMPRODUCT (B2: B8, C2: C8)/SUM (C2: C8)
I ovenstående formel er SUMPRODUCT -resultatet divideret med summen af alle vægtene. Uanset hvad ville vægten altid være op til 100%.
Eksempel 3 - Når vægtene skal beregnes
I det hidtil dækkede eksempel blev vægtene specificeret. Der kan dog være tilfælde, hvor vægtene ikke er direkte tilgængelige, og du skal først beregne vægtene og derefter beregne det vægtede gennemsnit.
Antag, at du sælger tre forskellige typer produkter som nævnt nedenfor:
Du kan beregne den vejede gennemsnitlige pris pr. Produkt ved hjælp af funktionen SUMPRODUCT. Her er formlen, du kan bruge:
= SUMPRODUCT (B2: B4, C2: C4)/SUM (B2: B4)
Deling af SUMPRODUCT -resultatet med SUM af mængder sikrer, at vægtene (i dette tilfælde mængder) tilføjer op til 100%.
Beregning af vægtet gennemsnit i Excel - SUM -funktion
Mens SUMPRODUCT -funktionen er den bedste måde at beregne det vejede gennemsnit i Excel, kan du også bruge SUM -funktionen.
For at beregne det vejede gennemsnit ved hjælp af SUM -funktionen skal du gange hvert element med den tildelte betydning i procent.
Brug af det samme datasæt:
Her er formlen, der giver dig det rigtige resultat:
= SUM (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)
Denne metode er ok at bruge, når du har et par varer. Men når du har mange varer og vægte, kan denne metode være besværlig og fejlbehæftet. Der er en kortere og bedre måde at gøre dette på med SUM -funktionen.
Fortsætter med det samme datasæt, her er den korte formel, der giver dig det vejede gennemsnit ved hjælp af SUM -funktionen:
= SUM (B2: B8*C2: C8)
Tricket, mens du bruger denne formel, er at bruge Control + Shift + Enter, i stedet for bare at bruge Enter. Da SUM -funktionen ikke kan håndtere arrays, skal du bruge Control + Shift + Enter.
Når du rammer Control + Shift + Enter, vil du se krøllede parenteser automatisk vises i begyndelsen og slutningen af formlen (se formellinjen i billedet ovenfor).
Igen skal du sørge for, at vægtene er op til 100%. Hvis det ikke er tilfældet, skal du dividere resultatet med summen af vægtene (som vist herunder ved at tage produkteksemplet):
