01 od 01
Funkcija SUMPRODUCT Excel
Uteženi in neponovljeni povprečni pregled
Običajno pri izračunu povprečne ali aritmetične sredine ima vsaka številka enako vrednost ali težo.
Povprecje se izracuna tako, da dodaja vrsto številk skupaj in nato to skupno razdeli s številom vrednosti v obsegu .
Primer bi bil (2 + 3 + 4 + 5 + 6) / 5, ki daje neponovljeno povprečje 4.
V Excelu se taki izračuni preprosto izvajajo s funkcijo AVERAGE .
Tehtano povprečje pa po drugi strani meni, da je ena ali več številk v dosegu vredno več ali pa imajo večjo težo kot druge številke.
Na primer, nekatere ocene v šoli, kot so vmesni in zaključni izpiti, so ponavadi vredne več kot redni testi ali naloge.
Če se povprečje uporabi za izračun končne ocene študenta, bodo imeli srednji in končni izpiti večjo težo.
V Excelu se lahko tehtana povprečja izračunajo z uporabo funkcije SUMPRODUCT .
Kako deluje funkcijo SUMPRODUCT
Kaj SUMPRODUCT naredi, pomnoži elemente dveh ali več matrik, nato pa dodate ali povzemite izdelke.
Na primer, v primeru, da sta dve argumentaciji za funkcijo SUMPRODUCT vneseni dve matriki s štirimi elementi:
- prvi element matrike1 pomnoži s prvim elementom v matriki2;
- drugi element matrike1 pomnoži z drugim elementom matrike2;
- tretji element matrike1 pomnoži tretji element matrike2;
- četrti element matrike1 pomnoži četrti element matrike2.
Nato so produkti štirih množitvenih operacij povzeti in jih funkcija vrne kot rezultat.
Sintaksa in argumenti funkcije SUMPRODUCT SUMPRODUCT
Sintaksa funkcije se nanaša na postavitev funkcije in vključuje ime, oklepaj in argumente funkcije.
Sintaksa za funkcijo SUMPRODUCT je:
= SUMPRODUCT (array1, array2, array3, ... array255)
Argumenti za funkcijo SUMPRODUCT so:
array1: (zahtevan) prvi array array.
array2, array3, ... array255: (neobvezno) dodatne matrike, do 255. Z dvema ali več nizi funkcija pomnoži elemente vsake matrike skupaj in nato doda rezultate.
- elementi matrike so lahko sklicevanja na celice na lokacijo podatkov v delovnem listu ali številke, ki jih ločijo aritmetični operaterji - npr. plus (+) ali minus znaki (-). Če vnesete številke, ne da bi jih operaterji ločili, jih Excel obravnava kot besedilne podatke. Ta primer je zajet v spodnjem primeru.
Opomba :
Vsi argumenti array morajo biti enake velikosti. Ali, z drugimi besedami, v vsaki matriki mora biti enako število elementov. Če ne, SUMPRODUCT vrne #VALUE! vrednost napake.
Če kateri koli niz elementov niso številke - kot so besedilni podatki - SUMPRODUCT jih obravnava kot ničle.
Primer: Izračunajte tehtano povprečje v Excelu
Primer, prikazan na zgornji sliki, izračunava tehtano povprečje končne znamke študenta s funkcijo SUMPRODUCT.
Funkcija to doseže z:
- množenje različnih znamk glede na njihov posamezni faktor teže;
- dodajanje izdelkov teh množičnih operacij skupaj;
- je skupna vrednost utežnega faktorja 7 (1 + 1 + 2 + 3) za štiri ocenjevalce razdelila zgoraj navedeno vsoto.
Vnos tehtalne formule
Kot večina drugih funkcij v Excelu, se SUMPRODUCT običajno vnese v delovni list z uporabo pogovornega okna funkcije. Ker formula za ponderiranje SUMPRODUCT uporablja nestandarden način - rezultat funkcije se deli s faktorjem teže - formula za utež mora biti vnesena v celico delovnega lista .
Naslednji koraki so bili uporabljeni za vnos tehtalne formule v celico C7:
- Kliknite celico C7, da postanete aktivna celica - mesto, kjer bo prikazana končna ocena učenca
- V celico vnesite naslednjo formulo:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Pritisnite tipko Enter na tipkovnici
- Odgovor 78.6 se mora pojaviti v celici C7 - vaš odgovor ima lahko več decimalnih mest
Neponderirano povprečje za iste štiri znamke bi bilo 76,5
Ker je študent imel boljše rezultate za svoje vmesne in končne izpite, je tehtanje povprečja pripomoglo k izboljšanju njegove splošne ocene.
Formalne variacije
Če želimo poudariti, da so rezultati funkcije SUMPRODUCT deljeni z vsoto uteži za vsako ocenjevalno skupino, je bil delilnik - del deljenja - vnesen kot (1 + 1 + 2 + 3).
Skupno tehtalno formulo bi bilo mogoče poenostaviti z vnosom številke 7 (vsota uteži) kot delitelja. Formula bi bila potem:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Ta izbira je v redu, če je število elementov v utežni matriki majhno in jih je mogoče zlahka dodati skupaj, vendar pa postane manj učinkovito, saj število elementov v utežnem polju narašča, zaradi česar je njihov dodatek težji.
Druga možnost in verjetno najboljša izbira - ker uporablja referenčne številke celic in ne številke v skupnem delu delilnika - je uporaba funkcije SUM za skupno delitev z naslednjo formulo:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Običajno je najbolje vnesti referenčne številke celic in ne dejanske številke v formule, saj poenostavi njihovo posodabljanje, če se spremenijo podatki formule.
Na primer, če bi bili utežni faktorji za dodelitve spremenjeni na 0,5 v primeru in pri preskusih na 1,5, bi bilo treba prva dva oblika formule ročno urejati, da bi delilec popravil.
V tretji različici je treba posodobiti samo podatke v celicah B3 in B4, formula pa bo preračunala rezultat.