Az Excel nagyon egyszerű feladattá tette a több cella átlagának kiszámítását – csak használja a függvényt ÁTLAGOS (ÁTLAGOS). De mi van akkor, ha egyes értékek nagyobb súllyal bírnak, mint mások? Például sok kurzusban a tesztek nagyobb súllyal bírnak, mint a feladatok. Ilyen esetekben számolni kell súlyozott átlag.
Az Excelnek nincs függvénye a súlyozott átlag kiszámítására, de van egy függvény, amely a legtöbb munkát elvégzi Ön helyett: SUMPRODUCT (ÖSSZES TERMÉK). És még ha még soha nem is használta ezt a funkciót, a cikk végére profiként fogja használni. Az általunk használt módszer az Excel bármely verziójában, valamint más táblázatokban, például a Google Táblázatokban működik.
Elkészítjük az asztalt
Ha súlyozott átlagot szeretne kiszámítani, legalább két oszlopra lesz szüksége. Az első oszlop (példánkban a B oszlop) tartalmazza az egyes feladatok vagy tesztek pontszámait. A második oszlop (C oszlop) tartalmazza a súlyokat. A nagyobb súly nagyobb befolyást jelent a feladatnak vagy tesztnek a végső osztályzatra.
Ahhoz, hogy megértse, mi a súly, gondolhatja azt a végső osztályzatának százalékában. Valójában ez nem így van, mivel ebben az esetben a súlyoknak 100%-ot kell adniuk. A képlet, amelyet ebben a leckében elemzünk, mindent helyesen számol ki, és nem függ a súlyok összegétől.
Beírjuk a képletet
Most, hogy a táblázatunk elkészült, hozzáadjuk a képletet a cellához B10 (bármely üres cella megteszi). Mint minden más Excel képletnél, egyenlőségjellel (=) kezdjük.
Képletünk első része a függvény SUMPRODUCT (ÖSSZES TERMÉK). Az argumentumokat zárójelek közé kell tenni, így nyitjuk meg őket:
=СУММПРОИЗВ(
=SUMPRODUCT(
Ezután adja hozzá a függvény argumentumait. SUMPRODUCT (SUMPRODUCT) több argumentumot is tartalmazhat, de általában kettőt használnak. Példánkban az első argumentum egy cellatartomány lesz. B2:B9A, amely tartalmazza a pontszámokat.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
A második argumentum egy cellatartomány lesz C2:C9, amely a súlyokat tartalmazza. Ezeket az argumentumokat pontosvesszővel (vesszővel) kell elválasztani. Ha minden készen van, zárja be a zárójeleket:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Most adjuk hozzá képletünk második részét, amely elosztja a függvény által kiszámított eredményt SUMPRODUCT (ÖSSZEG) a súlyok összegével. Később megbeszéljük, miért fontos ez.
Az osztási művelet végrehajtásához a már bevitt képletet a szimbólummal folytatjuk / (egyenes perjel), majd írja be a függvényt ÖSSZEG (ÖSSZEG):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
A funkcióért ÖSSZEG (SUM) csak egy argumentumot adunk meg – egy cellatartományt C2:C9. Ne felejtse el bezárni a zárójelet az argumentum megadása után:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Kész! A gomb megnyomása után belép, az Excel kiszámítja a súlyozott átlagot. Példánkban a végeredmény az lesz 83,6.
Hogyan működik?
Bontsuk fel a képlet minden részét, kezdve a függvénnyel SUMPRODUCT (SUMPRODUCT), hogy megértse, hogyan működik. Funkció SUMPRODUCT (SUMPRODUCT) kiszámítja az egyes cikkek pontszámának és súlyának szorzatát, majd összegzi az összes eredményt. Más szóval, a függvény megtalálja a szorzatok összegét, innen a név. Így Feladatok 1 szorozd meg 85-öt 5-tel, és -ért A teszt szorozzuk meg 83-at 25-tel.
Ha kíváncsi arra, hogy miért kell az első részben az értékeket szorozni, képzelje el, hogy minél nagyobb a feladat súlya, annál többször kell figyelembe venni az osztályzatot. Például, Feladat 2 5-ször számolva és Záróvizsga – 45 alkalommal. Ezért Záróvizsga nagyobb hatással van a végső osztályzatra.
Összehasonlításképpen, a szokásos számtani átlag kiszámításakor minden értéket csak egyszer veszünk figyelembe, azaz minden érték egyenlő súlyú.
Ha benézhetne egy funkció motorháztetője alá SUMPRODUCT (SUMPRODUCT), láttuk, hogy valójában ezt hiszi:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Szerencsére nem kell ilyen hosszú képletet írnunk, mert SUMPRODUCT (SUMPRODUCT) mindezt automatikusan megteszi.
Önmagában egy funkció SUMPRODUCT (SUMPRODUCT) hatalmas számot ad vissza nekünk − 10450. Ezen a ponton a képlet második része lép életbe: /SZUM(C2:C9) or /SZUM(C2:C9), amely visszaadja az eredményt a normál pontszámtartományba, megadva a választ 83,6.
A képlet második része nagyon fontos, mert lehetővé teszi a számítások automatikus javítását. Emlékszel, hogy a súlyoknak nem kell 100%-ot adniuk? Mindez a képlet második részének köszönhetően. Például, ha növelünk egy vagy több súlyértéket, a képlet második része egyszerűen elosztja a nagyobb értékkel, ami ismét a helyes választ eredményezi. Vagy sokkal kisebbre is tehetjük a súlyokat, például olyan értékek megadásával, mint pl 0,5, 2,5, 3 or 4,5, és a képlet továbbra is megfelelően fog működni. Ez nagyszerű, igaz?