beschrijvende statistiek

en excel

Het rekenblad Excel biedt heel wat mogelijkheden voor de verwerking van gegevens in de beschrijvende statistiek. We werken hieronder enkele typische voorbeelden uit.

De meeste functies die hierbij gebruikt worden, zijn ook in andere rekenbladen mogelijk, zij het in een ietwat aangepaste vorm. Zelfs in een eenvoudig rekenblad zoals dat van het pakket MS-Works zitten voldoende mogelijkheden om uit te werken wat hieronder in Excel gebeurt.

Omdat de leerlingen in de tweede graad normaal gezien in de lessen informatica allemaal wat kennis van een rekenblad hebben opgedaan, is het in principe mogelijk om de leerlingen de oefeningen te laten maken op de computer (in plaats van met een rekentoestel). De ervaring leert mij dat zoiets wel de nodige tijd (1 à 2 lessen) vraagt om aan te leren. Positief is dan wel dat de leerlingen gewoonlijk enthousiast zijn om op die manier te werken. Een praktisch probleem is natuurlijk dat de computerklas moet kunnen gebruikt worden. Is dat niet het geval, maar is er wel één computer beschikbaar (met beeldprojectie) in bijvoorbeeld een wiskundelokaal, dan kan Excel natuurlijk gebruikt worden ter illustratie van de gemaakte oefeningen. Vooral de grafieken uit de beschrijvende statistiek geven dan veel tijdwinst.

Het kan in Excel nuttig zijn om vooraf een aanvulling in de menu's aan te brengen. Kies in het menu extra voor invoegtoepassingen. Bij de mogelijkheden die nu verschijnen, vink je het vakje analysis toolpak aan. Door deze keuze wordt in het vervolg in datzelfde menu extra onderaan een keuze bij geplaatst: gegevensanalyse.

1 Eerste voorbeeld: statistische verwerking van een steekproef

met een beperkt aantal verschillende waarnemingsgetallen

Van 1000 gezinnen werd genoteerd hoeveel kinderen in het gezin voorkomen. Het resultaat van die waarnemingen wordt in een frequentietabel weergegeven. Voor het opstellen van die tabel zijn 5 kolommen opgenomen:

xi : waarnemingsgetallen

ni : frequentie van de waarnemingsgetallen

fi : relatieve frequentie (procentueel t.o.v. het totaal)

cni : cumulatieve frequentie

cfi : relatieve cumulatieve frequentie

Van deze grootheden worden achteraf grafieken gemaakt.

De titels boven de kolommen en de opmaak moeten zelf ingetypt en ingesteld worden.

Ook de getallen uit de eerste twee kolommen moeten natuurlijk ingetypt worden. De laatste drie kunnen we laten berekenen door Excel:

in cel C9 =B9/B$18 en omlaag doorvoeren

D9 =B9 en in D10 =D9+B10 en omlaag doorvoeren

E9 =D9/B$18 en omlaag doorvoeren

B18 =som(B9:B17) en in C18 =som(C9:C17)

De cellen van relatieve frequentie en cumulatieve relatieve frequentie hebben getalopmaak percentage (in te stellen via opmaak - celeigenschappen - getal).

Voor de berekening van het gemiddelde en de standaardafwijking worden nog twee kolommen bij gemaakt: een kolom voor en voor :

in cel G9 =A9*B9 en omlaag doorvoeren

H9 =A9^2*B9 en omlaag doorvoeren

G18 =som(G9:G17) en in H18 =som(H9:H17)

Onder de frequentietabel (maar de plaats is natuurlijk vrij te kiezen), zijn de centrummaten en de spreidingsmaten berekend. Eerste en derde kwartiel, mediaan modus en variatiebreedte, zijn niet door Excel berekend (dat kan wel als alle waarnemingsgetallen ingetypt worden vóór de frequentietabel: zie tweede voorbeeld). Voor de andere zijn de volgende formules ingegeven:

in cel E24 =G18/B18 E31 =E26-E25

E32 =wortel(H18/B18-E24^2) E33 =E32/E24

Gemiddelde en standaardafwijking zijn hier dus berekend met de volgende formules:

Bemerk dat Excel ook rechtstreeks het gemiddelde en de standaardafwijking kan berekenen, weer op voorwaarde dat alle waarnemingsgetallen afzonderlijk ingegeven zijn.

Grafieken van deze verschillende frequenties

Van zowel de frequentie, de relatieve frequentie, de cumulatieve frequentie en de relatieve cumulatieve frequentie is een grafiek gemaakt. Voor de 4 grootheden is dat op precies dezelfde manier gebeurd. Bij de uitleg beperken we ons dan ook tot één grafiek, bijvoorbeeld die van de cumulatieve frequentie.

- eerste stap: kies het grafiektype; voor onze oefening: kies het eerste (kolom):

- tweede stap: (je ziet nu overigens al een voorlopig voorbeeld van de grafiek)

In het vakje gegevensbereik moet normaal niets gewijzigd worden (de selectie vooraf van de cellen D9-D17 zorgt ervoor dat dit vakje al ingevuld is); is het vakje toch leeg of verkeerd, dan klik je in dat vakje, maak het eventueel leeg, en selecteer nu in het rekenblad de cellen D9-D17;

in het tabblad reeks staat alles meteen ook goed ingevuld, behalve het vakje labels categorieas (X); dat vakje is bedoeld om de cellen aan te geven van de waarden die bij de X-as horen; klik dus in het vakje en selecteer de cellen A9-A17. In sommige gevallen is dit invullen niet nodig, namelijk als Excel zelf ontdekt waar die gegevens staan.

- derde stap: de meeste zaken die hier kunnen ingevuld worden, spreken voor zich: titel bovenaan de grafiek, bij de assen, weergeven van assen en rasterlijnen enz. We kiezen hier om géén labels af te drukken, ook geen gegevenslabels (getallen bovenaan de staven van de grafiek, de eigenlijke waarden van de grafiek dus).

 

- vierde stap: kies de plaats waar de grafiek moet komen: in een nieuw (reken)blad, of als een object in het actuele rekenblad; kies voor de tweede mogelijkheid:

 

 

2 Tweede voorbeeld: statistische verwerking van een steekproef met veel

verschillende waarnemingsgetallen (indeling in klassen)

We werken het voorbeeld uit waarbij van 120 leerlingen het resultaat van een proefwerk wiskunde opgetekend is (in procenten uitgedrukt, afgerond op de eenheid).

Zoals gebruikelijk bij veel verschillende waarnemingsgetallen, worden de getallen in klassen verdeeld. Alle waarnemingsgetallen binnen één klasse krijgen dan dezelfde waarde: die van het klassemidden. Daardoor krijgen we in de berekeningen (overigens aanvaardbare) kleine afwijkingen tussen bijvoorbeeld het precieze gemiddelde en het gemiddelde berekend volgens de klassen. (In Excel kunnen we nu overigens gemakkelijk deze afwijking nagaan.)

Er is natuurlijk veel gelijkenis tussen het statistisch verwerken van deze gegevens in klassen en het vorige voorbeeld, maar ook wat verschil. Met name het indelen in klassen is een bijkomende berekening die moet gebeuren. Maar ook hier kan Excel ons helpen.

We typen eerst alle 120 waarnemingsgetallen in, bijvoorbeeld in de cellen A3-J14. Om de klassen te definiëren zoeken we eerst het grootste en het kleinste waarnemingsgetal (zie K21 (=MAX(A3:J14)) en L21 (=MIN(A3:J14))). We kunnen er uit afleiden dat 9 klassen een goede verdeling zullen geven: [45;49] [50;54] [55;59] … [80;84] [85;89].

Om Excel nu de frequentie te laten zoeken die in elke klasse voorkomt, voeren we de volgende stappen uit:

(invoerbereik = plaats waar de te verwerken gegevens staan, dus A3-J14; verzamelbereik = grenzen waarmee de klassen gemaakt worden, dus A17-A25; uitvoerbereik = plaats vanaf waar de frequentietabel (indeling in klassen) zal ingevuld worden)

Verzamelbereik

Frequentie

49

1

54

2

59

4

64

28

69

32

74

29

79

12

84

9

89

3

Meer

0

Deze tabel geeft ons dus de frequentie waarmee de waarnemingsgetallen in elke klasse voorkomen. We passen nu zelf deze tabel aan, zodat die zo goed mogelijk gelijkt op de frequentietabel uit het eerste voorbeeld. Verzamelbereik vervangen we door klassen, we voorzien ook een kolom voor het klassemidden.

We brengen ook weer een kolom aan met de waarden    en voor    (met xi is nu telkens het klassemidden bedoeld).

We kunnen nu de statistische waarden voor centrum en spreiding zowel voor de oorspronkelijke 120 getallen laten berekenen (is dus exact), als voor de gegevens in klassen ingedeeld (bevat kleine afwijkingen ten opzichte van de exacte waarden). In het rekenblad staan de eerste onder de oorspronkelijke tabel van 120 getallen (kolom K), de tweede onder de frequentietabel. Voor de berekening van deze laatste kan precies dezelfde werkwijze gevolgd worden als bij het eerste voorbeeld.

Voor de berekening van de juiste statistische grootheden zijn de volgende formules gebruikt:

in cel K24 =gemiddelde(A3:J14) K25 =mediaan(A3:J14)

K26 =kwartiel(A3:J14;1) K27 =kwartiel(A3:J14;3)

K28 =modus(A3:J14) K30 =K21-K22

K31 =K27-K26 K32 =STDEVP(A3:J14) K33 =K32/K24 (standaarddeviatie van een steekproef met de volledige populatie)

Grafieken van deze verschillende frequenties

Ook nu is het maken van de grafieken vrij gelijklopend met het eerste voorbeeld. Toch zijn er ook een paar belangrijke verschillen:

3 Correlatie en lineaire regressie

Van 8 personen wordt de lengte en het gewicht genoteerd:

persoon

lengte
(cm)

gewicht
(kg)

A
B
C
D
E
F
G
H

165
182
172
175
167
174
178
168

67
79
68
79
65
74
76
66

We zetten de punten met coördinaten bestaande uit de lengte en gewicht van de personen uit in een grafiek. Bij sterkere (lineaire) correlatie tussen de twee reeksen waarnemingsgetallen, liggen de punten sterker geconcentreerd rond een rechte: de regressielijn.

De correlatie tussen lengte en gewicht van een persoon wordt dan uitgedrukt door de correlatiecoëfficiënt r (waarde tussen -1 en 1; 0 = geen verband, dicht bij +1 is sterke positieve correlatie, dicht bij -1 is sterke negatieve correlatie).

Zowel de regressielijn als de correlatiecoëfficiënt zijn met Excel eenvoudig te vinden:

(bvb in C5-C12 en D5-D12);

selecteer eventueel vooraf die 2 reeksen getallen;

kies als grafiektype spreiding (de gegevens worden dan als losse punten op de grafiek geplaatst);

de rest van de stappen om te komen tot een nieuwe grafiek lijken vrij goed op de andere grafieken in de vorige voorbeelden; eventueel kan de schaal op de Y-as aangepast worden (niet beginnen bij 0, maar bijvoorbeeld beginnen bij 60);

 

 

 

Terug Homepagina van Wiskunde