01 01
Excel SUMPRODUCT funktsioon
Kaalutud ja kaalutud keskmine ülevaade
Tavaliselt on keskmise või aritmeetilise keskmise arvutamisel iga numbri väärtus või kaal.
Keskmine arvutatakse, lisades numbrite hulga kokku ja jagades selle kokku vahemike väärtuste arvuga.
Näiteks oleks (2 + 3 + 4 + 5 + 6) / 5, mis annab kaalumata keskmise 4.
Excelis on selliseid arvutusi lihtne teostada, kasutades funktsiooni AVERAGE .
Teisest küljest peab kaalutud keskmine kaaluma ühe või enama arvuliselt rohkem väärtust või olema suurem kui teised numbrid.
Näiteks on teatud koolilõpud, näiteks kesk- ja lõpueksamid tavaliselt väärt rohkem kui tavalised testid või ülesanded.
Kui keskmistamist kasutatakse üliõpilase lõpliku märgi arvutamiseks, antakse keskastme ja lõpueksamitele suurem kaal.
Exceli kaalutud keskmisi saab arvutada funktsiooni SUMPRODUCT abil.
Kuidas funktsioon SUMPRODUCT toimib?
Mida SUMPRODUCT kasutab, korrutab kahe või enama massiivi elemente ja seejärel lisab või summeerib tooteid.
Näiteks olukorras, kus mõlemad massiivid koos nelja elemendiga sisestatakse argumendina SUMPRODUCT funktsiooni jaoks:
- array1 esimene element korrutatakse esimese elemendiga array2;
- array1 teine element korrutatakse array2 teise elemendiga;
- array1 kolmas element korrutatakse array2 kolmanda elemendiga;
- array1 neljas element korrutatakse array2 neljanda elemendiga.
Seejärel summeeritakse nelja korrutustegevuse tooted ja tulemuseks tagastatakse funktsioon.
Excel SUMPRODUCT funktsiooni süntaks ja argumendid
Funktsiooni süntaks viitab funktsiooni kujule ja sisaldab funktsiooni nime, sulgudes ja argumente.
SUMPRODUCT funktsiooni süntaks on:
= SUMPRODUCT (array1, array2, array3, ... array255)
SUMPRODUCTi funktsiooni argumendid on järgmised:
array1: (vajalik) esimene array argument.
array2, array3, ... array255: (valikuline) täiendavad massiivid, kuni 255. Kahe või enama massiiviga korratakse funktsiooniga iga massiivi elemente koos ja lisab tulemused.
- massiivi elemendid võivad olla raamistiku viited töölehe andmete asukohale või aritmeetikaalaste operaatorite eraldatud arvud, näiteks pluss (+) või miinusmärgid (-). Kui numbrid sisestatakse operaatorite poolt eraldamata, käsitleb Excel neid tekstiandmetega. Selline olukord on allpool toodud näites kajastatud.
Märkus :
Kõik massiivi argumendid peavad olema sama suurusega. Teisisõnu peab igas massiivis olema sama palju elemente. Kui ei, siis SUMPRODUCT tagastab #VALUE! vea väärtus.
Kui mingi massiivi elemendid ei ole numbrid - näiteks tekstivälised - SUMPRODUCT käsitleb neid nullidega.
Näide: Exceli kaalutud keskmine arvutamine
Eespool toodud kujutis näitab õpilase lõpliku märgi kaalutud keskmist SUMPRODUCT funktsiooni kasutades.
Funktsioon täidab seda järgmiselt:
- korrutades erinevaid kaubamärke nende individuaalse kaaluteguriga;
- nende mitmekordistavate tegevuste toodete lisamine koos;
- jagunes eespool nimetatud summa nelja hindamise kaaluteguriga 7 (1 + 1 + 2 + 3) kokku.
Kaalumisvalemi sisestamine
Nagu enamik teisi Exceli funktsioone, sisestatakse tavaliselt SUMPRODUCT töölehe abil funktsiooni dialoogiaknas . Kuid kuna kaalumisvalemil kasutatakse SUMPRODUCTt mittestandardsel viisil - funktsiooni tulemus jagatakse kaalufaktoriga - kaalumisvalem tuleb sisestada töölehe lahtrisse.
Kaalumisvalemi sisestamiseks raku C7 kasutati järgmisi etappe:
- Klõpsake lahtris C7, et see oleks aktiivne lahter - asukoht, kus kuvatakse õpilase viimane märk
- Sisestage lahtrisse järgmine valem:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Vajutage klaviatuuril Enter klahvi
- Vastus 78.6 peaks ilmuma lahtrisse C7 - teie vastuses võib olla rohkem kümnendkohti
Samade nelja kaubamärgi kaalutud keskmine oleks 76,5
Kuna üliõpilane oli paremaid tulemusi oma vahe- ja lõpueksamite jaoks, aitas keskmine kaalumine kaasa tema üldise hinnangu paranemisele.
Vormide variatsioonid
Selleks, et rõhutada, et SUMPRODUCTi funktsioonide tulemused jagatakse iga hindamisrühma kaalude summaga, sisestati jagaja - jagamise teostajaks (1 + 1 + 2 + 3).
Üldist kaalumisvalemit saab lihtsustada, kui jagja 7 sisestada (kaalude summa). Valem oleks siis:
= SUMPRODUCT (B3: B6, C3: C6) / 7
See valik on hea, kui kaalumisrežiimis olevate elementide arv on väike ja neid saab kergesti kokku panna, kuid see muutub vähem efektiivseks, kuna kaalumisrežiimi elementide arv suurendab nende lisamise raskust.
Teine võimalus ja ilmselt parim valik - kuna see kasutab pigem divisorit kui numbreid, siis kasutatakse SUMi funktsiooni jagja kokku, kusjuures valem on:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Tavaliselt on parimal juhul pigem raku viidete kui tegelike arvude sisestamine valemidesse, kuna see lihtsustab nende värskendamist, kui valemi andmed muutuvad.
Näiteks kui omistuste kaalutegureid muudeti näiteks näites 0,5 ja testideks 1,5, tuleb valemi kaks esimest vormi jagada käsitsi jaotise parandamiseks.
Kolmanda variandi korral tuleb ainult rakkude B3 ja B4 andmeid uuendada ja valem arvutab tulemus uuesti.