Excel

Vægtet gennemsnit

Weighted Average

Excel-formel: Vægtet gennemsnitGenerisk formel
= SUMPRODUCT (weights,values)/ SUM (weights)
Resumé

For at beregne et vægtet gennemsnit kan du bruge SUMPRODUKT fungerer sammen med SUM-funktion . I eksemplet vist er formlen i G5, kopieret ned,:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

hvor vægte er navngivet rækkevidde I5: K5.



Forklaring

Et vægtet gennemsnit, også kaldet et vægtet gennemsnit, er et gennemsnit, hvor nogle værdier tæller mere end andre. Med andre ord har nogle værdier mere 'vægt'. Vi kan beregne et vægtet gennemsnit ved at gange værdierne til gennemsnittet med tilsvarende vægte og derefter dividere summen af ​​resultaterne med summen af ​​vægte. I Excel kan dette repræsenteres med den generiske formel nedenfor, hvor vægte og værdier er celleområder:



excel tilføj 1 til hver celle i en kolonne
 
= SUMPRODUCT (weights,values)/ SUM (weights)

I det viste regneark vises score for 3 tests i kolonne C til E, og vægten er i navngivet rækkevidde vægte (I5: K5). Formlen i celle G5 er:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

Når vi arbejder indefra og ud, bruger vi først SUMPRODUCT-funktionen til at multiplicere vægte med tilsvarende score og summere resultatet:



 
= SUMPRODUCT (weights,C5:E5) // returns 88.25

SUMPRODUCT multiplicerer først de tilsvarende elementer i de to arrays sammen og returnerer derefter summen af ​​produktet:

 
= SUMPRODUCT ({0.25,0.25,0.5},{90,83,90}) = SUMPRODUCT ({22.5,20.75,45}) =88.25

Resultatet divideres derefter med summen af ​​vægten:

 
=88.25/ SUM (weights) =88.25/ SUM ({0.25,0.25,0.5}) =88.25/1 =88.25

Da formlen kopieres ned i kolonne G, det navngivne interval vægte I5: K5 ændres ikke, da den opfører sig som en absolut reference . Scorerne i C5: E5 blev dog angivet som en relativ reference , opdater i hver ny række. Resultatet er et vægtet gennemsnit for hvert navn på listen som vist. Gennemsnittet i kolonne F beregnes kun som reference med GENNEMSNIT-funktion :



 
= AVERAGE (C5:E5)

Vægte, der ikke svarer til 1

I dette eksempel er vægtene konfigureret til at tilføje op til 1, så deleren altid er 1, og resultatet er den værdi, der returneres af SUMPRODUCT. En god egenskab ved formlen er dog, at vægten ikke behøver at tilføje op til 1.

For eksempel kunne vi bruge en vægt på 1 til de første to tests og en vægt på 2 til finalen (da finalen er dobbelt så vigtig), og det vægtede gennemsnit vil være det samme:

Excel-vægtet gennemsnit med brugerdefinerede vægte

hvordan man låser en celle i Excel 2010

I celle G5 løses formlen således:

 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights) = SUMPRODUCT ({1,1,2},{90,83,90})/ SUM (1,1,2) = SUMPRODUCT ({90,83,180})/ SUM (1,1,2) =353/4 =88.25

Bemærk: værdierne i krøllede seler {} ovenfor er områder udtrykt som arrays .

Transportere vægte

SUMPRODUCT-funktionen kræver, at arraydimensioner er kompatible. Hvis dimensioner ikke er kompatible, returnerer SUMPRODUCT en #VALUE-fejl. I nedenstående eksempel er vægtene de samme som det originale eksempel, men de er anført i a lodret rækkevidde:

Excel-vægtet gennemsnit med TRANSPOSE

For at beregne et vægtet gennemsnit med den samme formel er vi nødt til at 'vende' vægtene ind i et vandret array med TRANSPOSE-funktion sådan her:

 
= SUMPRODUCT ( TRANSPOSE (weights),C5:E5)/ SUM (weights)

Efter TRANSPOSE kører den lodrette matrix:

 
= TRANSPOSE ({0.250.250.5}) // vertical array

bliver til:

 
={0.25,0.25,0.5} // horizontal array

Og fra dette punkt opfører formlen sig som før.

Læs mere: lodrette og vandrette arrays .

Vedhæftede filer Fil exceljet-vægtet gennemsnit. xlsx Forfatter Dave Bruns


^