Excel je naredil izračun povprečja več celic zelo preprosto nalogo – preprosto uporabite funkcijo POVPREČNA (POVPREČNO). Kaj pa, če imajo nekatere vrednote večjo težo kot druge? Na primer, pri mnogih tečajih imajo testi večjo težo kot naloge. Za takšne primere je treba izračunati Povprečna teža.
Excel nima funkcije za izračun tehtanega povprečja, obstaja pa funkcija, ki bo opravila večino dela namesto vas: SUMPRODUCT (VSETA ZMNOŽEK). In tudi če še nikoli niste uporabljali te funkcije, jo boste do konca tega članka uporabljali kot profesionalec. Metoda, ki jo uporabljamo, deluje v kateri koli različici Excela, pa tudi v drugih preglednicah, kot je Google Preglednice.
Pripravimo mizo
Če boste izračunali tehtano povprečje, boste potrebovali vsaj dva stolpca. Prvi stolpec (v našem primeru stolpec B) vsebuje rezultate za vsako nalogo ali test. Drugi stolpec (stolpec C) vsebuje uteži. Večja teža pomeni večji vpliv naloge ali testa na končno oceno.
Če želite razumeti, kaj je teža, si jo lahko predstavljate kot odstotek vaše končne ocene. Dejansko temu ni tako, saj bi morale v tem primeru uteži znašati 100 %. Formula, ki jo bomo analizirali v tej lekciji, bo vse izračunala pravilno in ni odvisna od količine, ki jo seštejejo uteži.
Vnesemo formulo
Zdaj, ko je naša tabela pripravljena, dodamo formulo v celico B10 (katera koli prazna celica bo zadostovala). Kot pri vsaki drugi formuli v Excelu začnemo z enačajem (=).
Prvi del naše formule je funkcija SUMPRODUCT (VSETA ZMNOŽEK). Argumenti morajo biti v oklepajih, zato jih odpremo:
=СУММПРОИЗВ(
=SUMPRODUCT(
Nato dodajte argumente funkcije. SUMPRODUCT (SUMPRODUCT) ima lahko več argumentov, vendar se običajno uporabljata dva. V našem primeru bo prvi argument obseg celic. B2:B9A, ki vsebuje rezultate.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Drugi argument bo obseg celic C2:C9, ki vsebuje uteži. Ti argumenti morajo biti ločeni s podpičjem (vejico). Ko je vse pripravljeno, zaprite oklepaje:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Sedaj pa dodamo drugi del naše formule, ki bo razdelila rezultat, izračunan s funkcijo SUMPRODUCT (SUMPRODUCT) z vsoto uteži. Zakaj je to pomembno, bomo razpravljali pozneje.
Za izvedbo operacije deljenja že vneseno formulo nadaljujemo s simbolom / (ravna poševnica) in nato napišite funkcijo SUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Za funkcijo SUM (SUM) podali bomo le en argument – obseg celic C2:C9. Ne pozabite zapreti oklepajev po vnosu argumenta:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
pripravljena! Po pritisku tipke Vnesite, bo Excel izračunal tehtano povprečje. V našem primeru bo končni rezultat 83,6.
Kako deluje
Razčlenimo vsak del formule, začenši s funkcijo SUMPRODUCT (SUMPRODUCT), da razumete, kako deluje. funkcija SUMPRODUCT (SUMPRODUCT) izračuna zmnožek ocene vsakega elementa in njegove teže, nato pa sešteje vse nastale produkte. Z drugimi besedami, funkcija najde vsoto produktov, od tod tudi ime. Torej za Naloge 1 pomnožite 85 s 5 in za Preizkus pomnožite 83 s 25.
Če se sprašujete, zakaj moramo množiti vrednosti v prvem delu, si predstavljajte, da večja kot je teža naloge, večkrat moramo upoštevati oceno zanjo. na primer 2. naloga prešteti 5-krat in Končni izpit – 45-krat. Zato Končni izpit ima večji vpliv na končno oceno.
Za primerjavo, pri izračunu običajne aritmetične sredine se vsaka vrednost upošteva samo enkrat, to pomeni, da imajo vse vrednosti enako težo.
Če bi lahko pogledali pod pokrov funkcije SUMPRODUCT (SUMPRODUCT), videli smo, da dejansko verjame v to:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Na srečo nam ni treba napisati tako dolge formule, ker SUMPRODUCT (SUMPRODUCT) vse to naredi samodejno.
Funkcija sama po sebi SUMPRODUCT (SUMPRODUCT) nam vrne ogromno število − 10450. Na tej točki pride v poštev drugi del formule: /SUM(C2:C9) or /SUM(C2:C9), ki vrne rezultat v normalno območje rezultatov in poda odgovor 83,6.
Drugi del formule je zelo pomemben, saj omogoča samodejno popravljanje izračunov. Se spomnite, da ni treba, da seštevek uteži znaša 100 %? Vse to zahvaljujoč drugemu delu formule. Na primer, če povečamo eno ali več vrednosti teže, se bo drugi del formule preprosto delil z večjo vrednostjo, kar bo ponovno povzročilo pravilen odgovor. Lahko pa naredimo uteži veliko manjše, na primer tako, da določimo vrednosti, kot je 0,5, 2,5, 3 or 4,5, in formula bo še vedno delovala pravilno. Super je, kajne?