Súlyozott átlag számítása kritériummal

Excelmarketing - Excel VBA makró blog - Súlyozott átlag számítása kritériummal

Mi a különbség a sima átlag és a súlyozott átlag között?

Kezdjük a legelején egyből egy példával. Alább készítettem egy táblázatot, melyben két tanuló matek és kémia jegyeit találhatjuk. A jegyeknek típusa is van, a különböző típusoknak pedig súlya. A témazáró számít a legtöbbet, a röpdolgozat pedig a legkevesebbet.

táblázat letöltése csv-ben

Számoljuk ki Kati matek átlagát, természetesen még mindenféle súlyozás nélkül! Simán csak az átlagot.

Alapvetően össze kell adnunk Kati matek jegyeinek az értékét és el kell ossztanunk a matek jegyeinek darabszámával. Több módszerrel is kiszámolhatjuk, íme a képlet(ek):

=(5+3+4+4+5)/5
=SZUM(C2:C6)/DARAB(C2:C6)
=ÁTLAG(C2:C6)

4,2 lesz az átlag

Angolul:
=AVERAGE(C2:C6)

Következő lépésben viszont már súlyozzuk ezeket a matekjegyeket! Azaz számoljuk ki Kati matek jegyeinek súlyozott átlagát. A különbség itt az lesz, hogy minden egyes jegyet meg kell szorozni a súlyával és úgy szummázni őket, majd ezt a szummát el kell osztani a súlyok összegével.

 
=(5*2+3*3+4*2+4*1+5*3)/(2+3+2+1+3)
=SZORZATÖSSZEG(C2:C6;E2:E6)/SZUM(E2:E6)

4,18 lesz a súlyozott átlag

Angolul:
=SUMPRODUCT(C2:C6,E2:E6)/SUM(E2:E6)

Érdemes megjegyezni a SZORZATÖSSZEG képletet ha sűrűn kell súlyozott átlagot számolnunk. Nagyon hasznos függvény, a cikk második felében pedig még mélyebben megismerjük. 🙂

Szóval látjuk már a különbséget a sima átlag és a súlyozott átlag között, tudjuk már a képleteket hozzá, és ki is tudjuk már számolni a súlyozott átlagunkat is. Viszont mi van akkor, ha a táblázatunk nem ilyen szép rendezett, valamint vagy 10.000 sorból állna. Hogyan tudunk kritérium mentén súlyozott átlagot számolni?

Kritérium alkalmazása súlyozott átlagnál

Első körben nézzük meg közelebbről ezt a SZORZATÖSSZEG függvényt, hiszen jóval többet tud, mint amennyit feltételeznék róla. Fokozatosan bonyolítsuk a képletünket.

=SZORZATÖSSZEG(C2:C22)

Tehát a fenti esetben 62 lesz az eredmény, azaz szummázza az értékeket a SZORZATÖSSZEG függény ezen alkalmazása. Mintha csak egy SZUM függényt használtunk volna. De menjünk is tovább: 

=SZORZATÖSSZEG((A2:A22="Kati")*C2:C22)

Ebben az esetben már 41 lesz az eredmény, azaz csak azokat a mezőket szummáza, ahol "Kati" van az első oszlopban. Ezt pedig teljesen egyenértékű a SZUMHA függvénnyel, azzal is meg lehetett volna oldani eddig a pontig. De nézzük csak tovább:

=SZORZATÖSSZEG((A2:A22="Kati")*(B2:B22="matek")*C2:C22)

21 lesz az eredmény, itt már "Kati" és a "matek" is kritérium. Ahol ezek az értékek, ott adja össze csak a C oszlop értékeit. Ennek is van még közismertebb alternatívája, alapvetően több kritérium mentén történő szummázásra a SZUMHATÖBB függvényt használjuk. De mi súlyozni akarunk majd, úgyhogy kitartunk a SZORZATÖSSZEG mellett és továbbbonyolítjuk kicsit:

=SZORZATÖSSZEG((A2:A22="Kati")*(B2:B22="matek")*C2:C22*E2:E22)

A képletünkbe beletettük a súly oszlopot is szorozva. Azaz összeszorozza a jegyet a súlyával, de csak a kritériumoknak megfelelő sorokban. Ezzel az eredmény pillanatnyilag 46 lett. Ezzel még nem megyünk sokra ugyan, viszont ez a fenti képlet lesz a súlyozott átlagunk teljes képletének első része, a számlálója (osztandója).
Következő lépésben pedig a nevezőt (osztót) tesszük bele: 

=SZORZATÖSSZEG((A2:A22="Kati")*(B2:B22="matek")*C2:C22*E2:E22)/SZORZATÖSSZEG((A2:A22="Kati")*(B2:B22="matek")*E2:E22)

Ezzel tehát 4,18 lett a súlyozott átlag, ugyanaz ami korábban a manuális módon is kijött. Figyeljük meg, hogy a nevezőben is a SZORZATÖSSZEG képletet alkalmaztunk, pont azért, hogy lássunk, így is meg lehet oldani. Viszont a nevező résznél a SZUMHATÖBB képletet is lehetett volna használni. Ekkor így nézne ki:

=SZORZATÖSSZEG((A2:A22="Kati")*(B2:B22="matek")*C2:C22*E2:E22)/SZUMHATÖBB(E:E;A:A;"Kati";B:B;"matek")

Ez utóbbi angolul: 
=SUMPRODUCT((A2:A22="Kati")*(B2:B22="matek")*C2:C22*E2:E22)/SUMIFS(E:E,A:A,"Kati",B:B,"matek") 

Amire viszont mindenképpen figyelj!

Láthatod, hogy mindegyik tartományban (B2:B22), ami a SZORZATÖSSZEG függvényben szerepel, pontosan kiírom, hogy mettől meddig. Itt sajnos nem alkalmazhatod azt, hogy B:B, mert úgy nem fog működni.
Ez egy picit kényelmetlenné és rugalmatlanná tudja tenni a súlyozott átlag számítást Excelben, ezért ne felejsd el megváltoztatni a képletedet, ha újabb sorokkal bővül a táblád!

Bízok benne, hogy sikerült érthetően elmagyarázni, hogy hogyan tudod kiszámolni egy vagy több kritérium mentén a súlyozott átlagot Excelben vagy éppen Google Sheet-ben. Bármi kérdés van, írj nyugodtan itt kommentben, vagy üzenetben.

Mennyire találtad hasznosnak ezt cikket?
[Összes szavazat: 4 Átlag értékelés: 5]