Excel heeft het berekenen van het gemiddelde van meerdere cellen een zeer gemakkelijke taak gemaakt - gebruik gewoon de functie GEMIDDELDE (GEMIDDELD). Maar wat als sommige waarden meer gewicht in de schaal leggen dan andere? In veel cursussen wegen bijvoorbeeld toetsen zwaarder dan opdrachten. Voor dergelijke gevallen is het noodzakelijk om te berekenen gewogen gemiddelde.
Excel heeft geen functie voor het berekenen van het gewogen gemiddelde, maar er is een functie die het meeste werk voor je doet: SUMPRODUCT (SOMPRODUCT). En zelfs als je deze functie nog nooit eerder hebt gebruikt, zul je hem aan het einde van dit artikel als een professional gebruiken. De methode die we gebruiken werkt in elke versie van Excel en ook in andere spreadsheets zoals Google Spreadsheets.
Wij maken de tafel klaar
Als u een gewogen gemiddelde gaat berekenen, heeft u minimaal twee kolommen nodig. De eerste kolom (kolom B in ons voorbeeld) bevat de scores voor elke opdracht of toets. De tweede kolom (kolom C) bevat de gewichten. Meer gewicht betekent meer invloed van de taak of toets op het eindcijfer.
Om te begrijpen wat gewicht is, kun je het zien als een percentage van je eindcijfer. In feite is dit niet het geval, aangezien in dit geval de gewichten optellen tot 100%. De formule die we in deze les gaan analyseren, berekent alles correct en is niet afhankelijk van het bedrag dat de gewichten optellen.
We voeren de formule in
Nu onze tabel klaar is, voegen we de formule toe aan de cel B10 (elke lege cel is voldoende). Zoals bij elke andere formule in Excel, beginnen we met een gelijkteken (=).
Het eerste deel van onze formule is de functie SUMPRODUCT (SOMPRODUCT). Argumenten moeten tussen haakjes staan, dus we openen ze:
=СУММПРОИЗВ(
=SUMPRODUCT(
Voeg vervolgens de functieargumenten toe. SUMPRODUCT (SOMPRODUCT) kan meerdere argumenten hebben, maar meestal worden er twee gebruikt. In ons voorbeeld is het eerste argument een celbereik. B2:B9A die de scores bevat.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Het tweede argument is een celbereik C2:C9, die de gewichten bevat. Deze argumenten moeten worden gescheiden door een puntkomma (komma). Als alles klaar is, sluit je de haakjes:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Laten we nu het tweede deel van onze formule toevoegen, waarmee het resultaat wordt gedeeld door de functie SUMPRODUCT (SOMPRODUCT) door de som van de gewichten. We zullen later bespreken waarom dit belangrijk is.
Om de delingsbewerking uit te voeren, gaan we verder met de reeds ingevoerde formule met het symbool / (rechte schuine streep), en schrijf dan de functie SOM (SOM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
voor functie: SOM (SUM) we zullen slechts één argument specificeren - een reeks cellen C2:C9. Vergeet niet de haakjes te sluiten na het invoeren van het argument:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Klaar! Na het indrukken van de toets Enter, berekent Excel het gewogen gemiddelde. In ons voorbeeld is het eindresultaat: 83,6.
Hoe het werkt
Laten we elk deel van de formule opsplitsen, te beginnen met de functie SUMPRODUCT (SOMPRODUCT) om te begrijpen hoe het werkt. Functie SUMPRODUCT (SOMPRODUCT) berekent het product van de score en het gewicht van elk item en telt vervolgens alle resulterende producten op. Met andere woorden, de functie vindt de som van de producten, vandaar de naam. Dus voor Opdrachten 1 vermenigvuldig 85 met 5, en voor De test vermenigvuldig 83 met 25.
Als je je afvraagt waarom we de waarden in het eerste deel moeten vermenigvuldigen, stel je dan voor dat hoe groter het gewicht van de taak, hoe vaker we het cijfer ervoor moeten overwegen. Bijvoorbeeld, Taak 2 5 keer geteld en Eindexamen – 45 keer. Dat is waarom Eindexamen heeft meer invloed op het eindcijfer.
Ter vergelijking: bij het berekenen van het gebruikelijke rekenkundige gemiddelde wordt elke waarde slechts één keer in aanmerking genomen, dat wil zeggen dat alle waarden hetzelfde gewicht hebben.
Als je onder de motorkap van een functie zou kunnen kijken SUMPRODUCT (SOMPRODUCT), zagen we dat ze dit in feite gelooft:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Gelukkig hoeven we niet zo'n lange formule te schrijven, want SUMPRODUCT (SOMPRODUCT) doet dit allemaal automatisch.
Een functie op zich SUMPRODUCT (SOMPRODUCT) geeft ons een enorm aantal terug − 10450. Op dit punt komt het tweede deel van de formule om de hoek kijken: /SOM(C2:C9) or /SOM(C2:C9), die het resultaat terugbrengt naar het normale bereik van scores, waardoor het antwoord wordt gegeven 83,6.
Het tweede deel van de formule is erg belangrijk omdat u berekeningen automatisch kunt corrigeren. Weet je nog dat gewichten niet optellen tot 100%? Dit alles dankzij het tweede deel van de formule. Als we bijvoorbeeld een of meer gewichtswaarden verhogen, wordt het tweede deel van de formule gewoon gedeeld door de grotere waarde, wat weer resulteert in het juiste antwoord. Of we kunnen de gewichten veel kleiner maken, bijvoorbeeld door waarden op te geven zoals 0,5, 2,5, 3 or 4,5, en de formule zal nog steeds correct werken. Het is geweldig, toch?