Beregning af det vægtede gennemsnit i Excel (ved hjælp af formler)

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 skal til eksamen, og alle prøverne har den samme vægt, så vil gennemsnittet af dine samlede karakterer også være det vægtede gennemsnit af dine karakter.

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 her det vægtet gennemsnit kommer ind i billedet.

Lad os nu se, hvordan du beregner det vægtede gennemsnit i Excel.

Beregning af vægtet gennemsnit i Excel

I denne artikel lærer du, hvordan du beregner det vægtede gennemsnit i Excel:

  • Ved brug af SUMPRODUKT-funktionen.
  • Ved brug af SUM-funktionen.

Så lad os komme i gang.

Beregning af vægtet gennemsnit i Excel – SUMPRODUKT-funktion

Der kan være forskellige scenarier, hvor du skal beregne, det vægtede gennemsnit. Nedenfor er tre forskellige situationer, hvor du kan bruge SUMPRODUKT-funktionen til at beregne vægtet gennemsnit i Excel.

Nedenfor er tre forskellige situationer, hvor du kan bruge SUMPRODUKT-funktionen til at beregne vægtet gennemsnit i Excel.

Eksempel 1 – Når vægtene summeres op til 100%

Antag, at du har et datasæt med karakterer for 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 eleven have en endelig karakter. Et simpelt gennemsnit kan ikke beregnes her, da betydningen af forskellige evalueringer varierer.

For eksempel bærer en af quizzerne en vægt på 10%, dobbelt så meget vægt som en prøve, men en fjerdedel af vægten sammenlignet med eksamen.

I et sådant tilfælde kan du bruge SUMPRODUKT-funktionen til at få det vægtede gennemsnit af karaktererne.

Her er formlen, der giver dig det, vægtede gennemsnit i Excel:

=SUMPRODUKT(B2:B8;C2:C8)

Sådan fungerer denne formel: Excel SUMPRODUKT-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 summeres alle disse værdier.

Her er en illustration for at gøre det klart.

Eksempel 2 – Når vægte ikke summeres op til 100%

I ovenstående tilfælde blev vægtene tildelt på en sådan måde, at den samlede værdi blev 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 summeres vægtene op til 200%.

Hvis jeg bruger den samme SUMPRODUKT-formel, vil det give mig det forkerte resultat.

I ovenstående resultat har jeg fordoblet alle vægte, og det returnerer den vægtede gennemsnitlige værdi til 151. 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ægten ikke summeres op til 100%.

Her er formlen, der vil løse dette:

=SUMPRODUKT(B2:B8;C2:C8)/SUM(C2:C8)

I ovenstående formel er SUMPRODUKT-resultatet divideret med summen af alle vægtene. Uanset hvad ville den summeret vægt altid være 100%.

Et praktisk eksempel på forskellige vægte er, når virksomheder beregner de vægtede gennemsnitlige kapitalomkostninger. For eksempel, hvis et selskab har skaffet kapital ved hjælp af gæld, egenkapital og aktier, vil disse blive omsat til forskellige omkostninger. Virksomhedens regnskabsteam beregner derefter de vægtede gennemsnitlige kapitalomkostninger, der repræsenterer kapitalomkostningerne for hele virksomheden.

Eksempel 3 – Når vægtene skal beregnes

I eksempel ovenfor blev vægtene specificeret. Der kan dog være tilfælde, hvor vægte 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 vægtede gennemsnitlige pris pr. produkt ved hjælp af funktionen SUMPRODUKT. Her er formlen, du kan bruge:

=SUMPRODUKT(B2:B4;C2:C4)/SUM(B2:B4)

Divisionen af SUMPRODUKT resultatet med SUM af antal sikrer at vægtene (i dette tilfælde antal) summeres op til 100%.

Beregning af vægtet gennemsnit i Excel – SUM-funktion

Mens SUMPRODUKT -funktionen er den bedste måde at beregne det vægtede gennemsnit i Excel, kan du også bruge SUM-funktionen.

For at beregne det vægtede gennemsnit ved hjælp af SUM-funktionen skal du gange hvert element med den tildelte betydning i procent.

Brug 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 elementer. Men når du har mange elementer og vægte, kan denne metode være besværlig og tilbøjelig til at indeholde fejl. Der er en kortere og bedre måde at gøre dette på med SUM-funktionen.

Fortsæt med det samme datasæt, her er den korte formel, der giver dig det, vægtede gennemsnit ved hjælp af SUM-funktionen:

=SUM(B2:B8*C2:C8)

Tricket, mens du bruger denne formel, er at bruge Ctrl + Shift + Enter, i stedet for bare at bruge Enter. Da SUM-funktionen ikke kan håndtere arrays, skal du bruge Ctrl + Shift + Enter.

Når du trykker Ctrl + Shift + Enter, vil du automatisk se krøllede parenteser vises i begyndelsen og slutningen af formlen (se formellinjen i billedet ovenfor).

Igen skal du sørge for, at vægtene summerer op til 100%. Hvis det ikke er tilfældet, skal du dividere resultatet med summen af vægtene (som vist nedenfor ved at tage produkteksemplet):

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.

3 × four =