orpractwk1

Hogeschool van Amsterdam
Instituut Informatica en Elektrotechniek
Opleiding Informatica
locatie: De Leeuwenburg
Cursus 2001 / 2002
OR - practicumopgaven week 1
Opmerkingen vooraf:
In de opgaven zal soms gebruik gemaakt worden van de Engelse benamingen van
Excel-werkbladfuncties, dit ondanks het feit dat op school de Nederlandse versie
aanwezig is op de computers in het practicum. Sommige studenten zullen wellicht op
hun laptop of thuis een Engelse versie van Excel gebruiken. Op de volgende website is
een vertaaltabel te vinden van Engels naar Nederlands en omgekeerd:
http://www.gil.be/technique/Excel/Xlfonctr.htm.
Overigens is het zo dat als je een spreadsheet-werkblad aanmaakt in een Engelse
versie, dit opslaat op schijf en vervolgens inlaadt in een Nederlandse versie, of
omgekeerd als je van Nederlands naar Engels gaat, de vertaling van de
werkbladfunctienamen automatisch plaatsvindt.
Maak de opgaven in de aangegeven volgorde. Aangenomen wordt dat je de opgaven 1
t.e.m. 24 gemaakt hebt vóór het volgen van het hoorcollege in week 2.
Als je in Excel97 via het menu-onderdeel Help en de index zoekt naar gemiddelde, dan krijg
je onder andere informatie aangeboden over de volgende werkbladfuncties:
GEMIDDELDE
MEETK.GEM
HARM.GEM
GETRIMD.GEM
1. Lees zorgvuldig de beschrijvingen van deze vier functies en maak jezelf duidelijk wat
deze functies doen en hoe hun syntax eruit ziet. Maak aantekeningen. Ga daarna verder
met de volgende vragen.
2. Er bestaat een verband tussen het rekenkundig gemiddelde en het meetkundig gemiddelde
van een verzameling getallen. Maak in Excel een rijtje getallen en bepaal deze twee
gemiddelden. Doe hetzelfde met een ander rijtje getallen. Zie je een verband tussen het
rekenkundig en het meetkundig gemiddelde?
3. Een kapitaal van f 1000,- wordt uitgezet tegen rente op rente. In vijf achtereenvolgende
jaren bedraagt de jaarlijkse rente: 4%, 4,25%, 5%, 4,8%, 4,75%.
a Bereken de grootte van het kapitaal na vijf jaar.
b Tegen welke constante jaarrente zou het beginkapitaal moeten zijn uitgezet om
hetzelfde eindkapitaal te bereiken als in vraag a?
c. De rente die je in onderdeel b hebt berekend, is een bepaald gemiddelde van de vijf
rentepercentages die in onderdeel a zijn gegeven. Is dat het rekenkundig gemiddelde, het
meetkundig gemiddelde of het harmonisch gemiddelde?
4. Twee positieve gehele getallen definiëren een rechthoek met een zekere oppervlakte. Wat
is het verband tussen het meetkundig gemiddelde van die twee positieve getallen en de
zijde van een vierkant met dezelfde oppervlakte als de rechthoek met zijden gelijk aan de
twee getallen? Formuleer het verband tussen het meetkundig gemiddelde van drie
positieve getallen en de ribbelengte van een kubus met dezelfde inhoud als het
rechthoekig blok met die getallen als ribben.
5. In welke situatie zijn het rekenkundig gemiddelde en het meetkundig gemiddelde van één
en dezelfde verzameling reële getallen altijd aan elkaar gelijk? Voor twee getallen kun je
het vast wel beredeneren of afleiden uit de formules.
6. Iemand rijdt van A naar B (afstand 100 km) met een gemiddelde snelheid van 80 km/uur.
Terug van B naar A langs dezelfde weg rijdt hij gemiddeld 90 km/uur. Hoe groot was de
gemiddelde snelheid over de heen en terugweg? N.B.: Het antwoord is niet (80+90)/2 =
85 km/uur. Reken het maar na! Welk gemiddelde is hier nu van toepassing?
7. In een anoniem artikel met de naam “Dissertation sur la recherche du milieu le plus
probable” uit de Annales de Mathématique van 1821 lazen wij:
... Il existe certaines provinces de France où, pour déterminer le rendement moyen d’un
terrain, on observe ce rendement durant 20 années consécutives, on enlève la plus grande
et la plus petite valeur, et on prend la dix-huitième partie de la somme des valeurs
restantes...
a
Welk gemiddelde wordt hier bedoeld?
b
Bij welke sport wordt hetzelfde gemiddelde gebruikt. Als je het niet weet, vraag het
dan eens aan anderen, bijvoorbeeld thuis.
8. Als je in Help bijvoorbeeld GEMIDDELDE opzoekt, dan kun je via een hyperlink met de
naam Zie ook, naar een overzicht van hiermee samenhangende werkbladfuncties en
onderwerpen. Volg deze hyperlink. Er wordt dan onder andere verwezen naar statistische
functies. Ga daarheen. Loop de lijst langs en zoek naar andere functies die op een of
andere manier te maken hebben met gemiddelden.
9. In deze opgave gaan we er vanuit dat er geen werkbladfunctie GEMIDDELDE( ) bestaat.
Hoe zou je deze functie zelf kunnen maken met andere eenvoudige werkbladfuncties?
a Schrijf de gevraagde samengestelde functie helemaal voor jezelf uit als we moeten
uitgaan van gegeven getallen in de cellen A1 t.e.m. A10. (Aanwijzing gebruik o.a.
AANTAL( ). )
b Probeer het gevonden resultaat uit op de berekening van het rekenkundig gemiddelde
van 100 random-getallen tussen 0 en 1, die je maakt met de functie ASELECT( ). [N.B.:
Als de functie ASELECT( ) bij jou niet werkt, dan moet je hem installeren: menu:
Toevoegen: Analysis ToolPak en Analysis VBA.]
b i Wat verwacht je dat de uitkomst zal (moeten) zijn?
b ii Controleer jezelf door het ook te (laten) berekenen met GEMIDDELDE( ).
Opmerking: De som van de tien getallen A1 tot en met A10 kan worden weergegeven met
10
de compacte wiskundige notatie:
A
i 1
i
. Dit is de symbolische notatie voor
A1+A2+A3+…+A10 . De oefeningen 10 en 11 maken gebruik van deze en een verwante
N
notatie
A
i
voor producten van factoren. Deze opgaven zijn bedoeld om je vaardigheid
i 1
in Excel te vergroten en zijn ook een intellectuele uitdaging. Je kunt ze eventueel
overslaan.
10. Verifieer (met een eigen verzonnen getallenverzameling) de juistheid van de zogeheten
ongelijkheid van Cauchy:
2
 n

x  y    xr y r 

r 1
r 1
 r 1

(Kies zelf een waarde voor het aantal termen n, maar wel groter dan 10.)
11. Verifieer de juistheid van de zogeheten ongelijkheid van Weierstrass:
n
   
n
2
r
2
r
n
n
r 1
r 1
 (1  xr )  1   xr
(N.B.: Er moet wel gelden dat de xr allemaal positief of allemaal negatief zijn, en ook dat
de factoren (1+xr) allemaal positief zijn.)
12. In deze opgave gaan we er vanuit dat er geen werkbladfunctie MEETK.GEM( ) bestaat.
Hoe zou je deze functie zelf kunnen maken met andere eenvoudiger functies? Probeer het
gevonden resultaat uit op 100 random-getallen tussen 0 en 1, die je maakt met de functie
ASELECT( ). Controleer jezelf door het ook te laten berekenen met MEETK.GEM( ).
13. Welke voor jouw nieuwe Excel-werkbladfuncties heb je in de voorgaande opgaven leren
kennen? (Zo mogelijk, afgaan op je herinnering, dus niet de opgaven nog eens langslopen)
14. Welke Excel-werkbladfuncties ken je nog meer, bijv. omdat je ze ooit gebruikt hebt?
Schrijf ze compleet op. Als je de syntax niet meer precies weet, zoek het dan op in Help.
15. Maak 10 rijen van 10 randomgetallen uit de verzameling {1, 2, 3, 4, 5, 6} m.bv.
ASELECTTUSSEN(). Bepaal van de 100 getallen met behulp van Excelwerkbladfuncties:
a het rekenkundig gemiddelde
b de modus (zie MODUS( ) )
c de mediaan (zie MEDIAAN( ) )
Ververs een aantal keren de random-getallen door op F9 te drukken en bekijk telkens de
resultaten van de drie centrale liggingsmaten uit a, b en c. Verklaar (probeer te begrijpen)
dat het rekenkundig gemiddelde nauwelijks varieert en dat de mediaan veel minder dan de
modus verandert, die juist heel sterk blijkt te variëren.
16. Maak een kolom (A1:A20) van 20 random-getallen tussen 0 en 1 met behulp van
ASELECT(). Kopieer alleen de waarden naar B1:B20 met 'Plakken speciaal ...'
Maak nu berekeningen van
1 het gemiddelde van de getallen in B1 t.e.m. B20, plaats dit in B22;
2 de afwijkingen van B1:B20 met het gemiddelde uit B22, plaats deze in C1:C20;
3 het gemiddelde van de afwijkingen van het gemiddelde, plaats dit in C22;
4 het kwadraat van de afwijkingen van B1 tot en met B20 met het gemiddelde uit
B22, plaats deze in D1:D20.
5 het gemiddelde van de kwadraten van de afwijkingen van het gemiddelde, plaats
dit in D22.
De grootheid die je berekend hebt in 3, de gemiddelde afwijking, moet gelijk zijn aan nul!
Als dit niet zo is dan is dit een gevolg van afrondingsfouten of je hebt zelf een fout
gemaakt.
De grootheid die je uitgaande van B1 tot en met B20 berekend hebt in 5 heet de variantie.
In formulevorm:
1 N
1 N
2


B

B
B

var(B) =  i
. Hierin is
 Bi en N=20.
N i 1
N i 1
Het kan worden aangetoond dat deze grootheid ook berekend kan worden met de formule:
2
1 N
1 N

var( B)   ( Bi ) 2    Bi  . In woorden: de variantie = het gemiddelde van de
N i 1
 N i 1 
kwadraten minus het kwadraat van het gemiddelde. Verifieer dit door een eigen
spreadsheetberekening in kolom E.
2
1 N
1 N

17. In de praktijk is de formule var( X )   ( X i ) 2    X i  eenvoudiger in het
N i 1
 N i 1 
1 N
1 N
2
gebruik dan de formule var(X) =   X i  X  met X   X i .
N i 1
N i 1
Zie dat in, door beide berekeningen uit te voeren voor de zes mogelijke uitkomsten bij het
werpen van een dobbelsteen, te weten: {1, 2, 3, 4, 5, 6}. Doe dit eerst helemaal op papier
en zo mogelijk zonder rekenmachine, daarna, ter controle, nog eens in een spreadsheet.
18. Als je een dobbelsteen 12 keer gooit en de uitkomsten noteert, wat zal dan de waarde van
de gemiddelde uitkomst (ongeveer) zijn? Wat, als je het honderdentwintig keer doet? Wat
als je het 600 keer doet? Of je het nu weet of niet, laten we het eens simuleren.
Voer de bovenbedoelde experimenten nu uit met een spreadsheet, (gebruik de methode uit
16), maar let op: Tel op een of andere manier (gebruik bijv. de werkbladfunctie:
AANTAL.ALS( )) het aantal keren dat er een 1 komt, een 2, een 3, enz. Bereken dan met
behulp van deze aantallen (frequenties) het gemiddelde van alle uitkomsten. Als het
aantal keren dat we de dobbelsteen gooien groot is, bijv. 600, wat verwacht je dan voor de
aantallen keer 1, 2, 3, enz.? Klopt dit ook?
De gemiddelde uitkomst die we mogen verwachten na vele keren uitvoeren van het
experiment X (hier: het gooien van een dobbelsteen) heet de verwachting. We noteren de
verwachting van X als E (X) (Een scriptletter E, de E van het Engelse expectance, of het
Latijn: expectatio = ik verwacht.)
19. Herhaal vraagstuk 18 maar bereken nu in plaats van de verwachting de variantie var(X).
Dus weer via de frequenties.
20. Excel kent de werkbladfuncties VAR( ), VARP( ), VARA( ) en VARPA( ). Welke van
deze komt overeen met de variantie zoals die in de voorgaande vraagstukken is
behandeld? Bestudeer nauwkeurig het verschil. Bereken de variantie van de getallen uit 19
m.b.v. VAR( ) en VARP( ). Klopt een en ander met de uitkomsten die je al gevonden had.
21. De wortel uit de variantie heet de standaardafwijking aangegeven met het symbool
(kleine Griekse letter sigma). Dus  ( X )  Var ( X ) . In Excel bestaan werkbladfuncties
voor het in één klap berekenen van de standaarddeviatie van een verzameling getallen
(waarnemingen). De standaardafwijking voor getallen die random getrokken worden uit
de populatie van reële getallen tussen 0 en 1 is volgens de theorie gelijk aan 0,285...
Controleer of je dit ook vindt voor een aantal verschillende verzamelingen van random
getallen tussen 0 en 1.
22. Produceer vier rijen van 25 random getallen tussen 0 en 1. Maak hiervan vier box plots.
Als hulpmiddel kun je gebruik maken van Excel's mogelijkheden om te 'ranken' (op
grootte te sorteren en percentielen te bepalen): Extra, Gegevensanalyse, Rang en
Percentiel.
23. Produceer 20 random gehele getallen tussen 1 en 10. Bepaal via: Extra, Gegevensanalyse,
Beschrijvende Statistiek welke centrale liggingsmaat het meest robuust is, d.w.z.
ongevoelig voor een verandering in de data; verander daartoe de grootste waarneming
door een getal dat 5 keer zo groot is. Bestudeer de gevolgen voor de verschillenden
centrale liggingsmaten. Wat zijn de gevolgen voor de diverse spreidingsmaten die je kent?
24. Herhaal de vraagstukken 13 en 14!