Gemiddelde & variantie in een samengevatte layout
- 20/07/2008
 | Voorbeeldbestanden bij dit artikel: |
Waarover gaat het?
Op deze pagina: Gemiddelde & variantie hebben jullie al kunnen lezen hoe je het gemiddelde en de variantie van een reeks getallen berekent in Excel. Lees misschien eerst die pagina eens door mocht je niet helemaal vertrouwd zijn daarmee.
Nu, de aanleiding voor beide pagina’s op mijn website, is een topic in de Excel nieuwsgroepen. Een zekere Monte Milanuk vroeg naar het gemiddelde en de standaardafwijking van een reeks getallen. Dat is voor ons ondertussen wel kinderspel. Maar in plaats van een bereik met ALLE observaties:
is er een soort samenvatting van de observaties:
Bekijk nu het 2de tabblad van het voorbeeldbestand bovenaan de pagina.
Om één of andere reden heb je dus NIET de beschikking over alle data, maar een beknopte tabel: telkens de observatie en hoe vaak die observatie voorkomt in de steekproef. De gegevens in het bereik B2:C5 zijn dus identiek aan die in het bereik I2:I69 (tabblad 2). Het getal 172,5 komt 16 keer voor, het getal 172,6 ook, het getal 172,7 komt 36 keer voor.
Uitleg en details
Het gemiddelde
Het gemiddelde is dus NIET 172,6! Als de 3 getallen elk evenveel keer voorkomen, dan zou dat wel het geval zijn. Nu is het gemiddelde iets meer dan 172,6 aangezien 172,7 een groter gewicht heeft (komt meer voor). In cellen F5 en G5 zie je het gemiddelde en de variantie als je geen rekening houdt met de gewichten.
Dat is niet correct natuurlijk. Maar hoe berekenen we dan wel het gemiddelde en de variantie in de steekproef als we niet de uitgebreide gegevenstabel krijgen of maken? We passen dan de formules voor gemiddelde en variantie aan voor verschillende gewichten (kolom C).
Het gemiddelde wordt in cel F3:
=SOMPRODUCT(B3:B5;C3:C5)/SOM(C3:C5)
Bij het gemiddelde deel je het totaal door het aantal observaties. We doen: 172,5*16+172,6*16+172,7*36 om het totaal te berekenen. Je vermenigvuldigt overeenkomstige waarden uit kolom B en C, en telt die op: B3*C3+B4*C4+B5*C5. Dat is exact wat nu de SOMPRODUCT functie in Excel doet. Het totaal deel je door het aantal observaties: 16+16+36=68.
Ter controle staat in de andere gele cel (F4) het gemiddelde berekend op basis van alle 68 observaties.
De variantie
Bij de variantie neem je de som van de kwadratische afwijkingen ten opzichte van het gemiddelde, en dat deel je door het aantal observaties min 1. Dit soort opzet is niet echt gekend onder Excel gebruikers, daarom spendeer ik er wat meer aandacht aan hier. De variantie wordt in cel G3:
=SOMPRODUCT((B3:B5-F3)^2;C3:C5)/(SOM(C3:C5)-1)
Werk van binnen naar buiten.
=SOMPRODUCT((B3:B5-F3)^2;C3:C5)/(SOM(C3:C5)-1)
Het blauwe deel B3:B5 geeft jou een Array van 3 waarden, met telkens de observatie in:
{172,5; 172,6; 172,7}
Dus eerst B3, dan B4, en dan B5.
=SOMPRODUCT((B3:B5-F3)^2;C3:C5)/(SOM(C3:C5)-1)
In dit blauwe deel B3:B5-F3 gaan we nu van deze 3 waarden telkens het gemiddelde (cel F3) aftrekken:
{-0,1294…; -0,0294…; 0,0706…}
En het is zo dat je dit soort formule moet begrijpen. Dan kijk je verder in de formule en zie je dat het kwadraat genomen wordt:
=SOMPRODUCT((B3:B5-F3)^2;C3:C5)/(SOM(C3:C5)-1)
Je krijgt dan de volgende Array van 3 waarden voor het blauwe deel:
{0,0167…; 0,0009…; 0,0050…}
Daarna voert Excel de SOMPRODUCT uit:
=SOMPRODUCT((B3:B5-F3)^2;C3:C5)/(SOM(C3:C5)-1)
Je neemt dan eigenlijk de waarden uit de vorige Array, en vermenigvuldigt telkens met het gewicht en telt alles op: 0,0167*16+0,0009*16+0,0050*36=0,4612. Dit totaal deel je door het aantal observaties min 1 (dat is 67):
=SOMPRODUCT((B3:B5-F3)^2;C3:C5)/(SOM(C3:C5)-1)
Dit resulteert in 0,4612/67=0,00688, de variantie.
Ter controle staat in de andere groene cel (G4) de variantie berekend op basis van alle 68 observaties.
Op de werkbalk Formules controleren, staat er een vergrootglas. Als je op een cel met een formule gaat staan en dan op het vergrootglas klikt, krijg je achtereenvolgens de stappen van de berekening te zien.
De variantie met een matrixformule
Eentje om het af te leren. De formule in G9 gaat een stapje verder:
{=KWADRATENSOM(I2:I69-F4)/(SOM(C3:C5)-1)}
Dit is de variantie, maar dan berekend als matrixformule op basis van alle observaties. Dat het een matrixformule is, zie je aan het feit dat er rondom de formule accolades komen. Je typt die NIET zelf! Maar hoe komen ze er dan wel? Typ of plak de formule, en in plaats van af te sluiten via de gewone Enter, druk je Ctrl + Shift + Enter. Deze werkwijze geldt voor ALLE matrixformules! Op Engelstalige Excel forums en websites zie je soms CSE staan; dat is de afkorting van Ctrl + Shift + Enter.
In deze formule wordt weer voor elke observatie het gemiddelde afgetrokken van de observatie zelf. De functie KWADRATENSOM neemt het kwadraat van elk argument, en telt alles netjes op. Als laatste stap deel je door het aantal observaties min 1 en je krijgt alweer dezelfde variantie. Zo heb je de KWADRATENSOM functie ook eens gebruikt!
Klaar!
Voilà, weer wat leuke technieken met formules bijgeleerd!
