Yleinen kaava
=SUMPRODUCT(weights,values)/SUM(weights)
Yhteenveto
Painotetun keskiarvon laskemiseksi voit käyttää SUMPRODUCT-funktiota yhdessä SUM-funktion kanssa. Esitetyssä esimerkissä G5: n kaava, kopioitu alaspäin, on:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
missä painot on nimetty alue I5: K5.
Selitys
Painotettu keskiarvo, jota kutsutaan myös painotetuksi keskiarvoksi, on keskiarvo, jossa jotkut arvot laskevat enemmän kuin toiset. Toisin sanoen joillakin arvoilla on enemmän "painoa". Voimme laskea painotetun keskiarvon kertomalla arvot keskiarvoon vastaavilla painoilla ja jakamalla sitten tulosten summa painojen summalla. Excelissä tämä voidaan esittää alla olevalla yleisellä kaavalla, jossa painot ja arvot ovat solualueita:
=SUMPRODUCT(weights,values)/SUM(weights)
Esitetyssä laskentataulukossa 3 testin tulokset näkyvät sarakkeissa C - E ja painot ovat nimettyjen alueiden painot (I5: K5). Solun G5 kaava on:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Työskentelemällä sisältä ulospäin, käytämme ensin SUMPRODUCT-funktiota painojen kertomiseen vastaavilla pisteillä ja summan tuloksen:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT kertoo ensin kahden ryhmän vastaavat elementit yhteen ja palauttaa sitten tuotteen summan:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Tulos jaetaan sitten painojen summalla:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Kun kaava kopioidaan alas sarakkeeseen G, nimetyt alueiden painot I5: K5 eivät muutu, koska se käyttäytyy absoluuttisena viitteenä. Suhteellisena viitteenä syötetyt C5: E5-pisteet kuitenkin päivittyvät jokaiselle uudelle riville. Tulos on painotettu keskiarvo jokaiselle luettelossa olevalle nimelle kuvan mukaisesti. Sarakkeen F keskiarvo lasketaan viitteeksi vain AVERAGE-toiminnolla:
=AVERAGE(C5:E5)
Painot, jotka eivät ole yhtä
Tässä esimerkissä painot on määritetty lisäämään yhteen, joten jakaja on aina 1, ja tulos on SUMPRODUCTin palauttama arvo. Kaavan mukava piirre on kuitenkin se, että painoja ei tarvitse lisätä yhteen.
Voisimme esimerkiksi käyttää painoa 1 kahdessa ensimmäisessä testissä ja painoa 2 finaalissa (koska finaali on kaksi kertaa niin tärkeä) ja painotettu keskiarvo on sama:
Solussa G5 kaava on ratkaistu seuraavasti:
=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
Huomaa: Edellä olevien kiharoiden aaltosulkeiden () arvot ovat alueita, jotka ilmaistaan matriiseina.
Painojen siirtäminen
SUMPRODUCT-toiminto edellyttää, että taulukon mitat ovat yhteensopivat. Jos mitat eivät ole yhteensopivia, SUMPRODUCT palauttaa #ARVO -virheen. Alla olevassa esimerkissä painot ovat samat kuin alkuperäisessä esimerkissä, mutta ne on lueteltu pystysuoralla alueella:
Painotetun keskiarvon laskemiseksi samalla kaavalla meidän on "käännettävä" painot vaakasuoraan ryhmään TRANSPOSE-toiminnolla seuraavasti:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Kun TRANSPOSE on suoritettu, pystysuora taulukko:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
tulee:
=(0.25,0.25,0.5) // horizontal array
Ja tästä lähtien kaava käyttäytyy kuten aiemmin.
Lue lisää: pysty- ja vaakasuuntaiset taulukot.
