Excel on muutnud mitme lahtri keskmise arvutamise väga lihtsaks ülesandeks – kasutage lihtsalt funktsiooni KESKMINE (KESKMINE). Aga mis siis, kui mõnel väärtusel on rohkem kaalu kui teistel? Näiteks paljudel kursustel on testidel suurem kaal kui ülesannetel. Sellistel juhtudel on vaja arvutada kaalutud keskmine.
Excelis ei ole kaalutud keskmise arvutamise funktsiooni, kuid on olemas funktsioon, mis teeb suurema osa tööst teie eest ära: SUMPRODUCT (SUMMATOODE). Ja isegi kui te pole seda funktsiooni kunagi varem kasutanud, kasutate selle artikli lõpuks seda nagu professionaal. Meie kasutatav meetod töötab kõigis Exceli versioonides ja ka muudes arvutustabelites, näiteks Google'i arvutustabelites.
Valmistame laua ette
Kui kavatsete arvutada kaalutud keskmist, on teil vaja vähemalt kahte veergu. Esimene veerg (meie näites veerg B) sisaldab iga ülesande või testi hindeid. Teine veerg (veerg C) sisaldab kaalusid. Suurem kaal tähendab ülesande või testi suuremat mõju lõpphindele.
Et mõista, mis kaal on, võite seda mõelda protsendina oma lõplikust hindest. Tegelikult see nii ei ole, kuna sel juhul peaksid kaalud kokku moodustama 100%. Selles õppetükis analüüsitav valem arvutab kõik õigesti ja ei sõltu kaalude summast.
Sisestame valemi
Nüüd, kui meie tabel on valmis, lisame lahtrisse valemi B10 (sobib iga tühi lahter). Nagu iga muu Exceli valemi puhul, alustame võrdusmärgiga (=).
Meie valemi esimene osa on funktsioon SUMPRODUCT (SUMMATOODE). Argumendid peavad olema sulgudes, seega avame need:
=СУММПРОИЗВ(
=SUMPRODUCT(
Järgmisena lisage funktsiooni argumendid. SUMPRODUCT (SUMPRODUCT) võib sisaldada mitut argumenti, kuid tavaliselt kasutatakse kahte. Meie näites on esimene argument lahtrite vahemik. B2:B9A, mis sisaldab hindeid.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Teine argument on lahtrite vahemik C2:C9, mis sisaldab raskusi. Need argumendid tuleb eraldada semikooloniga (komaga). Kui kõik on valmis, sulgege sulgud:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Nüüd lisame valemi teise osa, mis jagab arvutatud tulemuse funktsiooniga SUMPRODUCT (SUMPRODUCT) kaalude summa järgi. Miks see oluline on, arutame hiljem.
Jagamistehte sooritamiseks jätkame juba sisestatud valemit sümboliga / (sirge kaldkriips) ja seejärel kirjutage funktsioon SUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Funktsiooni jaoks SUM (SUM) määrame ainult ühe argumendi – lahtrite vahemiku C2:C9. Ärge unustage pärast argumendi sisestamist sulgusid sulgeda:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Valmis! Pärast klahvi vajutamist sisene, Excel arvutab kaalutud keskmise. Meie näites on lõpptulemus 83,6.
Kuidas see töötab?
Jaotame kõik valemi osad, alustades funktsioonist SUMPRODUCT (SUMPRODUCT), et mõista, kuidas see toimib. Funktsioon SUMPRODUCT (SUMPRODUCT) arvutab iga üksuse hinde ja selle kaalu korrutise ning seejärel summeerib kõik saadud tooted. Teisisõnu, funktsioon leiab toodete summa, sellest ka nime. Nii et Ülesanded 1 korrutage 85 5-ga ja jaoks Test korrutage 83 25-ga.
Kui mõtlete, miks me peame esimeses osas olevaid väärtusi korrutama, kujutage ette, et mida suurem on ülesande kaal, seda rohkem kordi peame selle hinnet arvestama. Näiteks, 2. ülesanne loetud 5 korda ja Lõpueksam - 45 korda. Sellepärast Lõpueksam avaldab lõpphinnet suuremat mõju.
Võrdluseks, tavalise aritmeetilise keskmise arvutamisel võetakse iga väärtust arvesse ainult üks kord, see tähendab, et kõik väärtused on võrdse kaaluga.
Kui saaksite vaadata funktsiooni kapoti alla SUMPRODUCT (SUMPRODUCT), nägime, et tegelikult ta usub seda:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Õnneks pole meil vaja nii pikka valemit kirjutada, sest SUMPRODUCT (SUMPRODUCT) teeb seda kõike automaatselt.
Funktsioon omaette SUMPRODUCT (SUMPRODUCT) tagastab meile tohutu arvu − 10450. Siinkohal tuleb mängu valemi teine osa: /SUM(C2:C9) or /SUM(C2:C9), mis tagastab tulemuse normaalsesse skooride vahemikku, andes vastuse 83,6.
Valemi teine osa on väga oluline, kuna võimaldab arvutusi automaatselt korrigeerida. Pea meeles, et kaalud ei pea olema 100%? Seda kõike tänu valemi teisele osale. Näiteks kui suurendame üht või mitut kaaluväärtust, jagatakse valemi teine osa lihtsalt suurema väärtusega, mille tulemuseks on jällegi õige vastus. Või saame kaalusid palju väiksemaks muuta, näiteks määrates väärtused nagu 0,5, 2,5, 3 or 4,5, ja valem töötab endiselt õigesti. See on suurepärane, eks?