Excel on tehnyt useiden solujen keskiarvon laskemisesta erittäin helppoa – käytä vain funktiota KESKIMÄÄRIN (KESKIVERTO). Mutta entä jos joillakin arvoilla on enemmän painoarvoa kuin toisilla? Esimerkiksi monilla kursseilla kokeilla on enemmän painoarvoa kuin tehtävillä. Tällaisissa tapauksissa on tarpeen laskea painotettu keskiarvo.
Excelissä ei ole funktiota painotetun keskiarvon laskemiseen, mutta on funktio, joka tekee suurimman osan työstä puolestasi: SUMPRODUCT (SUMMATUOTE). Ja vaikka et olisi koskaan käyttänyt tätä ominaisuutta aiemmin, tämän artikkelin loppuun mennessä käytät sitä kuin ammattilainen. Käyttämämme menetelmä toimii kaikissa Excelin versioissa sekä muissa laskentataulukoissa, kuten Google Sheetsissä.
Valmistamme pöydän
Jos aiot laskea painotetun keskiarvon, tarvitset vähintään kaksi saraketta. Ensimmäinen sarake (esimerkissämme sarake B) sisältää kunkin tehtävän tai testin pisteet. Toinen sarake (sarake C) sisältää painot. Suurempi paino tarkoittaa enemmän tehtävän tai testin vaikutusta loppuarvosanaan.
Ymmärtääksesi, mikä paino on, voit ajatella sitä prosentteina lopullisesta arvosanastasi. Itse asiassa näin ei ole, koska tässä tapauksessa painojen summan tulisi olla 100 %. Kaava, jota analysoimme tällä oppitunnilla, laskee kaiken oikein, eikä se riipu painojen summasta.
Syötetään kaava
Nyt kun taulukkomme on valmis, lisäämme kaavan soluun B10 (mikä tahansa tyhjä solu käy). Kuten minkä tahansa muun Excelin kaavan kohdalla, aloitamme yhtäsuuruusmerkillä (=).
Kaavamme ensimmäinen osa on funktio SUMPRODUCT (SUMMATUOTE). Argumentit on suljettava, joten avaamme ne:
=СУММПРОИЗВ(
=SUMPRODUCT(
Lisää seuraavaksi funktion argumentit. SUMPRODUCT (SUMPRODUCT) voi sisältää useita argumentteja, mutta yleensä käytetään kahta. Esimerkissämme ensimmäinen argumentti on solualue. B2:B9A, joka sisältää pisteet.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Toinen argumentti on solualue C2:C9, joka sisältää painot. Nämä argumentit on erotettava puolipisteellä (pilkulla). Kun kaikki on valmis, sulje sulut:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Lisätään nyt kaavamme toinen osa, joka jakaa funktiolla lasketun tuloksen SUMPRODUCT (SUMPRODUCT) painojen summalla. Keskustelemme myöhemmin, miksi tämä on tärkeää.
Jakotoiminnon suorittamiseksi jatkamme jo syötettyä kaavaa symbolilla / (suora kauttaviiva) ja kirjoita sitten funktio SUMMA (SUMMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Toiminnan vuoksi SUMMA (SUM) määritämme vain yhden argumentin – solualueen C2:C9. Älä unohda sulkea sulkuja argumentin kirjoittamisen jälkeen:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Valmis! Näppäimen painamisen jälkeen enter, Excel laskee painotetun keskiarvon. Esimerkissämme lopputulos on 83,6.
Kuinka se toimii
Erotetaan jokainen kaavan osa aloittaen funktiosta SUMPRODUCT (SUMPRODUCT) ymmärtääksesi, miten se toimii. Toiminto SUMPRODUCT (SUMPRODUCT) laskee kunkin tuotteen pistemäärän ja painon tulon ja summaa sitten kaikki tuloksena saadut tuotteet. Toisin sanoen funktio löytää tulojen summan, josta myös nimi. Siis varten Tehtävät 1 kerro 85 5:llä ja for Koe kerro 83 25:llä.
Jos ihmettelet, miksi meidän pitää kertoa arvot ensimmäisessä osassa, kuvittele, että mitä suurempi tehtävän paino on, sitä useammin meidän on harkittava sen arvosanaa. Esimerkiksi, Tehtävä 2 laskettu 5 kertaa ja Loppukoe – 45 kertaa. Siksi Loppukoe sillä on suurempi vaikutus lopulliseen arvosanaan.
Vertailun vuoksi tavanomaista aritmeettista keskiarvoa laskettaessa jokainen arvo otetaan huomioon vain kerran, eli kaikilla arvoilla on sama paino.
Jos voisit katsoa toiminnon konepellin alle SUMPRODUCT (SUMPRODUCT), näimme, että hän itse asiassa uskoo tähän:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Onneksi meidän ei tarvitse kirjoittaa niin pitkää kaavaa, koska SUMPRODUCT (SUMPRODUCT) tekee kaiken tämän automaattisesti.
Toiminto sinänsä SUMPRODUCT (SUMPRODUCT) palauttaa meille valtavan luvun − 10450. Tässä vaiheessa kaavan toinen osa tulee peliin: /SUMMA(C2:C9) or /SUMMA(C2:C9), joka palauttaa tuloksen normaalille pistemäärälle ja antaa vastauksen 83,6.
Kaavan toinen osa on erittäin tärkeä, koska sen avulla voit korjata laskelmia automaattisesti. Muista, että painojen ei tarvitse olla 100 %? Kaikki tämä kaavan toisen osan ansiosta. Jos esimerkiksi lisäämme yhtä tai useampaa painoarvoa, kaavan toinen osa yksinkertaisesti jaetaan suuremmalla arvolla, mikä taas johtaa oikeaan vastaukseen. Tai voimme tehdä painoista paljon pienempiä, esimerkiksi määrittämällä arvoja, kuten 0,5, 2,5, 3 or 4,5, ja kaava toimii edelleen oikein. Se on hienoa, eikö?