Amikor egy adott értékkészlet egyszerű átlagát számítjuk ki, akkor feltételezzük, hogy minden értéknek azonos súlya vagy jelentősége van.
Például, ha megjelenik a vizsgákra, és az összes vizsga hasonló súllyal rendelkezik, akkor az összes pontjainak átlaga is a pontszámok súlyozott átlaga lesz.
A való életben azonban ez aligha van így.
Néhány feladat mindig fontosabb, mint a többi. Néhány vizsga fontosabb, mint a többi.
És ez az, ahol Súlyozott átlag jön a képbe.
Íme a súlyozott átlag tankönyvi definíciója:
Most nézzük meg, hogyan kell kiszámítani a súlyozott átlagot Excelben.
Súlyozott átlag kiszámítása Excelben
Ebben az oktatóanyagban megtudhatja, hogyan kell kiszámítani a súlyozott átlagot Excelben:
- A SUMPRODUCT funkció használata.
- A SUM funkció használata.
Kezdjük hát el.
Súlyozott átlag kiszámítása Excelben - SUMPRODUCT függvény
Különféle forgatókönyvek lehetnek, amikor ki kell számítani a súlyozott átlagot. Az alábbiakban bemutatunk három különböző helyzetet, amikor a SUMPRODUCT függvénnyel kiszámíthatja az Excel súlyozott átlagát
Az alábbiakban három különböző helyzetet mutatunk be, amikor a SUMPRODUCT függvénnyel kiszámíthatjuk az Excel súlyozott átlagát
1. példa - Amikor a súlyok elérik a 100% -ot
Tegyük fel, hogy rendelkezik egy adatkészlettel, amelyben a diákok különböző vizsgákon elért pontszámait és a százalékos súlyokat (az alábbiak szerint) mutatjuk be:
A fenti adatok szerint a tanuló különböző értékelésekben érdemjegyeket kap, de végül végeredményt vagy osztályzatot kell adni. Egy egyszerű átlagot itt nem lehet kiszámítani, mivel a különböző értékelések fontossága eltérő.
Például egy kvíz, amelynek súlya 10%, kétszerese a súlynak, mint egy feladatnak, de egynegyede a súlynak a vizsgához képest.
Ebben az esetben a SUMPRODUCT függvény segítségével lekérheti a pontszám súlyozott átlagát.
Íme a képlet, amely megadja az Excel súlyozott átlagát:
= SUMPRODUCT (B2: B8, C2: C8)
Ez a képlet így működik: Az Excel SUMPRODUCT függvény megszorozza az első tömb első elemét a második tömb első elemével. Ezután megszorozza az első tömb második elemét a második tömb második elemével. Stb…
És végül hozzáadja ezeket az értékeket.
Íme egy illusztráció, hogy világos legyen.
2. példa - Amikor a súlyok nem érnek el 100% -ot
A fenti esetben a súlyokat úgy határozták meg, hogy az összesített összeg 100%legyen. De a valós élet forgatókönyveiben nem mindig lehet így.
Nézzük meg ugyanazt a példát különböző súlyokkal.
A fenti esetben a súlyok 200%-ot tesznek ki.
Ha ugyanazt a SUMPRODUCT képletet használom, az rossz eredményt ad.
A fenti eredményben megdupláztam az összes súlyt, és a súlyozott átlagértéket 153,2 -ként adja vissza. Most már tudjuk, hogy egy diák nem tud 100 -nál többet elérni 100 -ból, bármennyire is zseniális.
Ennek az az oka, hogy a súlyok nem érik el a 100%-ot.
Itt van a képlet, amely ezt rendezi:
= SUMPRODUCT (B2: B8, C2: C8)/SUM (C2: C8)
A fenti képletben a SUMPRODUCT eredményt elosztjuk az összes súly összegével. Ezért bármi is legyen, a súlyok mindig 100%-ot tesznek ki.
3. példa - Mikor kell kiszámítani a súlyokat?
Az eddig tárgyalt példában a súlyokat határozták meg. Előfordulhatnak azonban olyan esetek, amikor a súlyok közvetlenül nem állnak rendelkezésre, és először ki kell számítani a súlyokat, majd a súlyozott átlagot.
Tegyük fel, hogy három különböző típusú terméket értékesít, az alábbiak szerint:
A SUMPRODUCT funkció segítségével kiszámíthatja a termékek súlyozott átlagárát. Itt van a képlet, amelyet használhat:
= SUMPRODUCT (B2: B4, C2: C4)/SUM (B2: B4)
Ha a SUMPRODUCT eredményt elosztja a mennyiségek SUM -jával, akkor a súlyok (ebben az esetben a mennyiségek) 100%-ot tesznek ki.
Súlyozott átlag kiszámítása Excelben - SUM függvény
Míg a SUMPRODUCT függvény a legjobb módja a súlyozott átlag kiszámításához Excelben, a SUM funkciót is használhatja.
A SUM függvény segítségével a súlyozott átlag kiszámításához minden elemet meg kell szorozni, a hozzárendelt fontossággal százalékban.
Ugyanazon adatkészlet használata:
Íme a képlet, amely megadja a megfelelő eredményt:
= SUM (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)
Ez a módszer akkor használható, ha van pár tárgya. De ha sok tárgya és súlya van, ez a módszer nehézkes és hibára hajlamos lehet. Ennek rövidebb és jobb módja van a SUM funkció használatával.
Folytatva ugyanazzal az adathalmazzal, itt van a rövid képlet, amely a SUM függvény segítségével megadja a súlyozott átlagot:
= SUM (B2: B8*C2: C8)
Ennek a képletnek a használata során a trükk az, hogy a Control + Shift + Enter billentyűkombinációt használja az Enter helyett. Mivel a SUM függvény nem tudja kezelni a tömböket, a Control + Shift + Enter billentyűkombinációt kell használnia.
Amikor megnyomja a Control + Shift + Enter billentyűket, a göndör zárójelek automatikusan megjelennek a képlet elején és végén (lásd a képlet sávját a fenti képen).
Ismét győződjön meg arról, hogy a súlyok 100%-ot tesznek ki. Ha nem, akkor az eredményt el kell osztani a súlyok összegével (az alábbiak szerint, a termék példája alapján):
