Ko izračunamo preprosto povprečje danega niza vrednosti, se predpostavlja, da imajo vse vrednosti enako težo ali pomembnost.
Na primer, če se pojavljate na izpitih in imajo vsi izpiti podobno težo, bi bilo povprečje vaših skupnih ocen tudi tehtano povprečje vaših rezultatov.
V resničnem življenju pa to komaj drži.
Nekatere naloge so vedno pomembnejše od drugih. Nekateri izpiti so pomembnejši od drugih.
In tu je Povprečna teža pride na sliko.
Tukaj je definicija tehtanega povprečja v učbeniku:
Zdaj pa poglejmo, kako izračunati tehtano povprečje v Excelu.
Izračun tehtanega povprečja v Excelu
V tej vadnici se boste naučili, kako izračunati tehtano povprečje v Excelu:
- Uporaba funkcije SUMPRODUCT.
- Uporaba funkcije SUM.
Pa začnimo.
Izračun tehtanega povprečja v Excelu - funkcija SUMPRODUCT
Obstajajo lahko različni scenariji, kjer morate izračunati tehtano povprečje. Spodaj so tri različne situacije, v katerih lahko uporabite funkcijo SUMPRODUCT za izračun tehtanega povprečja v Excelu
Spodaj so tri različne situacije, v katerih lahko uporabite funkcijo SUMPRODUCT za izračun tehtanega povprečja v Excelu
Primer 1 - Ko se uteži seštejejo do 100%
Recimo, da imate nabor podatkov z ocenami, ki jih je študent dosegel na različnih izpitih, skupaj z utežmi v odstotkih (kot je prikazano spodaj):
V zgornjih podatkih študent dobi ocene pri različnih ocenjevanjih, na koncu pa mora dobiti končno oceno ali oceno. Enostavnega povprečja tukaj ni mogoče izračunati, saj se pomen različnih ocen razlikuje.
Na primer, kviz z 10-odstotno težo ima dvakrat večjo težo v primerjavi z nalogo, vendar četrtino teže v primerjavi z izpitom.
V tem primeru lahko s funkcijo SUMPRODUCT dobite tehtano povprečje ocene.
Tu je formula, ki vam bo dala uteženo povprečje v Excelu:
= PODROČJE (B2: B8, C2: C8)
Takole deluje ta formula: Excel SUMPRODUCT funkcija pomnoži prvi element prve matrike s prvim elementom druge matrike. Nato pomnoži drugi element prve matrike z drugim elementom druge matrike. In tako naprej…
In na koncu doda vse te vrednosti.
Tukaj je ilustracija, ki pojasnjuje.
Primer 2 - Ko se uteži ne seštevajo do 100%
V zgornjem primeru so bile uteži dodeljene tako, da se je skupna vrednost seštela do 100%. Toda v resničnih življenjskih scenarijih morda ni vedno tako.
Poglejmo si isti primer z različnimi težami.
V zgornjem primeru uteži segajo do 200%.
Če uporabim isto formulo SUMPRODUCT, bo to povzročilo napačen rezultat.
V zgornjem rezultatu sem podvojil vse uteži in vrne tehtano povprečno vrednost 153,2. Zdaj vemo, da študent ne more dobiti več kot 100 od 100, ne glede na to, kako briljanten je.
Razlog za to je, da se uteži ne seštevajo do 100%.
Tukaj je formula, ki bo to uredila:
= PODROČJE (B2: B8, C2: C8)/SUM (C2: C8)
V zgornji formuli je rezultat SUMPRODUCT deljen z vsoto vseh uteži. Torej, ne glede na vse, bi uteži vedno dosegle 100%.
Primer 3 - Kdaj je treba izračunati uteži
V doslej obravnavanem primeru so bile določene teže. Lahko pa obstajajo primeri, ko uteži niso neposredno na voljo, zato morate najprej izračunati uteži in nato izračunati tehtano povprečje.
Recimo, da prodajate tri različne vrste izdelkov, kot je navedeno spodaj:
Tehtano povprečno ceno na izdelek lahko izračunate s funkcijo SUMPRODUCT. Tukaj je formula, ki jo lahko uporabite:
= PODROČJE (B2: B4, C2: C4)/SUM (B2: B4)
Če delite rezultat SUMPRODUCT s SUM količin, se zagotovi, da se uteži (v tem primeru količine) povečajo do 100%.
Izračun tehtanega povprečja v Excelu - funkcija SUM
Čeprav je funkcija SUMPRODUCT najboljši način za izračun tehtanega povprečja v Excelu, lahko uporabite tudi funkcijo SUM.
Če želite izračunati tehtano povprečje s funkcijo SUM, morate pomnožiti vsak element z dodeljeno pomembnostjo v odstotkih.
Z uporabo istega nabora podatkov:
Tukaj je formula, ki vam bo dala pravi rezultat:
= SUM (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)
Ta metoda je primerna za uporabo, če imate nekaj predmetov. Če pa imate veliko predmetov in uteži, je ta metoda lahko okorna in nagnjena k napakam. S funkcijo SUM je to krajši in boljši način.
Če nadaljujemo z istim nizom podatkov, tukaj je kratka formula, ki vam bo dala tehtano povprečje s funkcijo SUM:
= SUM (B2: B8*C2: C8)
Trik med uporabo te formule je, da uporabite Control + Shift + Enter, namesto da uporabite samo Enter. Ker funkcija SUM ne more upravljati nizov, morate uporabiti Control + Shift + Enter.
Ko pritisnete Control + Shift + Enter, boste videli, da se kodrasti oklepaji samodejno pojavijo na začetku in na koncu formule (glejte vrstico s formulami na zgornji sliki).
Še enkrat se prepričajte, da uteži segajo do 100%. Če se ne, morate rezultat razdeliti na vsoto uteži (kot je prikazano spodaj, z vzorcem izdelka):
