HANDLEIDING bij het gebruik van EXCEL

1

HANDLEIDING bij het gebruik van EXCEL Deze korte handleiding is een hulpmiddel om de kennismaking met EXCEL vlot te laten verlopen. Behalve een beetje handigheid met de muis is geen verdere voorkennis vereist. Wie reeds vertrouwd is met het pakket EXCEL zal wellicht weinig nieuws ontdekken in de komende pagina’s. Deze handleiding geeft dan ook enkel basisinformatie en wil u zelf op weg zetten. Voor meer informatie kan u steeds de HELP van EXCEL raadplegen of meer gespecialiseerde literatuur. De handleiding werd geschreven op basis van de Engelstalige versie van EXCEL 2000 alhoewel deze versie voorlopig nog niet beschikbaar is in de computerklassen van de Dekenstraat en de bibliotheek van de faculteit ETEW. Ze verschilt bijna niet van de versie geschreven voor de Engelstalige versie van EXCEL 97. Het is mogelijk dat in oudere (of nieuwere) versies niet alle functies op de hieronder beschreven manier kunnen worden uitgevoerd. Vaak zijn er ook alternatieve werkwijzen om een zelfde resultaat te bekomen. Bij het samenstellen van de onderstaande tekst werden de volgende conventies gemaakt. • Zoals u weet, kan u zowel links als rechts klikken op de muis. Om de tekst niet te overladen, wordt met de term “klikken” steeds links klikken bedoeld. Wanneer u rechts moet klikken, wordt dat er steeds expliciet bij vermeld. • De enkelvoudige aanhalingstekens (‘’) worden gebruikt om opties uit een menu aan te duiden, de naam van een knop of een vaste aanduiding in een venster. De tweevoudige aanhalingstekens (“”) daarentegen dienen om de inhoud van een cel of de formulebalk weer te geven zoals u die moet ingeven of af te lezen is op het scherm. • Toetsen of toetsencombinaties worden weergegeven in drukletters. Een plusteken duidt erop dat de toetsen terzelfdertijd moeten worden ingedrukt. Met vragen of problemen kan u terecht bij Jan Adem in lokaal 05.109 HOG, telefonisch op nummer 016/32.69.58 of per email op adres [email protected].

2

Inhoudstafel Inhoudstafel

p. 2-3

A. De bouwstenen van EXCEL

p. 4-20

1. De schermopbouw menubalk – werkbalk opmaak – werkbalk standaard – snelmenu - wizards

p. 4

2. De opbouw van een werkmap werkmap – werkblad – naam van een werkblad veranderen – werkbladen toevoegen of weglaten – rijen – kolommen – cel – cel selecteren – bereik van cellen selecteren – naamvak – cel een naam geven – bereik een naam geven – formulebalk

p. 6

3. Het gebruik van cellen p. 8 tekst – getallen – formules – de inhoud van een cel aanpassen – annuleerknop – invoerknop – naar de inhoud van andere cellen verwijzen – cellen en bereiken van cellen kopiëren – celverwijzingen relatief – absolute celverwijzingen kopiëren door slepen – cellen sorteren 4. Het gebruik van functies functiewizard – functie gebruiken – uitvoer een reeks van waarden

p.11

5. De opmaak van het werkblad cel te klein – getallen vaste opmaak – eigenschappen van cel aanpassen – eigenschappen van kolom of rij aanpassen

p.16

6. Het opslaan, sluiten en oproepen van een werkmap opslaan – sluiten – openen

p.18

7. Het afdrukken van een werkmap afdrukken – afdrukopties - afdrukvoorbeeld

p.19

B. Statistische taken in EXCEL 1. Willekeurige getallen genereren functie RAND() – Analysis ToolPak – functie RANDBETWEEN(bottom,top) – Random Number Generation

p.21-48 p.21

3

2. Een grafiek maken grafieken – gegevenspunt – gegevensreeks – grafiekenwizard – puntenwolk – grafiekblad – histogram – grafiek uitbreiden

p.24

3. Verdelingen en dichtheden uitzetten discrete kansvariabelen – continue kansvariabele

p.31

4. Werken met een draaitabel kwalitatieve variabelen – draaitabelwizard – kruistabel

p.36

5. Werken met een gegevenstabel p.42 gegevenstabel - gevoeligheidsanalyses – simuleren 6. Een boxplot maken boxplot

p.48

4

A. De bouwstenen van EXCEL

1. De schermopbouw Wanneer u EXCEL opstart, krijgt u het volgende scherm te zien (zie Figuur 1). Menubalk Werkbalk opmaak

Werkbalk standaard

Figuur 1 De schermopbouw is vrij gelijkend aan die van WORD. Bovenaan ziet u de menubalk en enkele werkbalken; de werkbalk opmaak en de werkbalk standaard. Net zoals in WORD neemt de muisaanwijzer verschillende vormen aan naargelang de positie op het scherm. Om een optie in de menubalk te gebruiken, moet u links klikken op de gewenste titel in de menubalk. Hierdoor verschijnt een submenu waar u doorheen kan gaan door de muis op en neer te bewegen. Een optie selecteren kan door links te klikken op de betreffende optie. Voorbeeld Stel dat u een werkblad wenst af te printen. Over werkbladen en afdrukken volgt later meer. De bedoeling van dit voorbeeld is enkel het gebruik van de menubalk toe te lichten. U kan dit bv. doen door in de menubalk de optie ‘File’ aan te klikken, waardoor een submenu zich opent (zie Figuur 2).

5

Figuur 2 In dit submenu ziet u opnieuw verschillende opties die u kan aanklikken. Wanneer u de printoptie bekijkt, zal u merken dat er 3 puntjes achter het woord ‘Print’ staan. Dergelijke aanduiding wijst erop dat als u voor deze optie kiest, er zich een nieuw venster zal openen. Klikken op ‘Print…’ zal dus nog niet meteen de printopdracht naar de printer doorsturen. Het icoontje links van ‘Print…’ duidt erop dat er een knop, herkenbaar aan het icoontje, bestaat om de opdracht direct uit te voeren. De toetsencombinatie rechts is de zogenaamde sneltoets om de printopdracht meteen uit te voeren. Dus door een druk op de knop ‘Print’ in de werkbalk standaard of de toetsencombinatie CONTROL + P, geeft u zonder verdere invoer een printopdracht door naar de printer en begint die te printen. Rechts van sommige opties, bv. bij ‘Print Area’ ziet u een zwart driehoekje. Wanneer u dergelijke optie oplicht, zal er nog een submenu verschijnen waarin u opnieuw een optie kan kiezen. In elke optie is ook een teken onderlijnd. U kan ook een optie selecteren door te drukken op het onderlijnde teken. Voor de optie ‘Print…’ is dit dus op de P. < Wanneer u de naam wil kennen van een knop op een werkbalk, hoeft u enkel de betreffende knop een tweetal seconden aan te wijzen met de muisaanwijzer. Naast de zichtbare menu’s kunnen, alnaargelang de positie van de muisaanwijzer op het scherm, allerhande extra menu’s, de

6 zogenaamde snelmenu’s, worden opgeroepen door te klikken op de rechtermuisknop. Evenals WORD beschikt EXCEL over een groot aantal wizards, een opeenvolging van invulvensters die u op een gebruiksvriendelijke manier doorheen de meer ingewikkelde operaties loodsen.

2. De opbouw van een werkmap EXCEL werkt aan de hand van werkmappen. Een werkmap (book) draagt de extensie XLS, zoals een WORD document de extensie DOC krijgt. Een werkmap is samengesteld uit één of meer werkbladen (sheet). Elk werkblad heeft een naam die wordt aangegeven in de tabs onderaan het werkblad.

Naam van het actieve werkblad

Figuur 3

Standaard zitten er drie werkbladen in een werkmap met de naam Sheet1, Sheet2 en Sheet3 (zie Figuur 3). U kan de naam van een werkblad veranderen door te dubbelklikken op de naam of door de muisaanwijzer te brengen naar de naam, op de rechtermuisknop te drukken en de optie ‘Rename’ in het snelmenu te kiezen (zie Figuur 4). U kan, indien u dat wil, werkbladen toevoegen of weglaten. Dat is bv. mogelijk via de opties ‘Insert…’ en ‘Delete’ in het snelmenu dat verschijnt wanneer u rechts klikt met de muisaanwijzer op de naam van een werkblad (zie Figuur 4).

Figuur 4

7 Een werkblad is op zijn beurt ingedeeld in rijen (aangeduid door getallen 1,2,3,…) en kolommen (aangeduid door lettercombinaties A,B,…,AA,AB,…). U kan rijen en kolommen toevoegen en verwijderen met behulp van het snelmenu dat verschijnt door een druk op de rechtermuisknop op de rij- of de kolomkop. Een cel is een kruispunt van een rij en een kolom. Ze krijgt de coördinaten van de betreffende kolom en rij, bv. cel B7, cel G35 of cel AB105. Om een cel te selecteren, moet u op de cel klikken. Hierdoor maakt u de cel actief. De actieve cel wordt gemarkeerd door een vet kader. U kan naar een andere cel gaan door erop te klikken of door de pijltoetsen te gebruiken. Een snelle manier om naar een (niet-zichtbare) cel te springen is het adres (of de naam) van de cel in het naamvak (zie verder) te typen en op ENTER te drukken. U kan ook een bereik (range) van cellen selecteren door de muisknop in te drukken op de eerste cel van het gewenst bereik en die ingedrukt te houden terwijl u met de muis beweegt tot de laatste cel van het bereik waarna u de muisknop loslaat. Stel dat u een bereik wil selecteren dat uit meerdere niet rakende delen bestaat bv. de cellen A1 tot en met A6 en de cellen C1 tot en met C6. Licht hiervoor het eerste deel van het bereik op, bv. de cellen A1 tot en met A6. Druk nu op de toets CTRL hou de toets ingedrukt. U kan nu de overige delen van het bereik selecteren met de muis, hier de cellen C1 tot en met C6. Een volledige rij kan u selecteren door op het kader met het overeenkomende getal, rijkop genoemd, te drukken. Een kolom door op het vakje met de corresponderende letter(combinatie), de zogenaamde kolomkop, te drukken. Het volledig werkblad, dus alle cellen, kan u selecteren door op de onbenoemde knop linksboven op het werkblad te klikken (zie Figuur 5). Druk hier om het hele werblad op te lichten

Rijkop van rij 2

Figuur 5

Kolomkop van kolom C

8 Boven de kolommen bevindt zich het naamvak en de formulebalk (zie Figuur 6). In het naamvak ziet u de naam van de actieve cel. Indien u de actieve cel geen naam hebt gegeven, en meestal is dit zo, verschijnen de coördinaten van de cel. U kan een cel een naam geven door de cel actief te maken, op het naamvak te klikken, de naam in te typen en op ENTER te drukken. Op precies dezelfde manier kan u ook een bereik een naam geven : licht de cellen van het bereik op, klik op het naamvak, typ de naam en druk op ENTER. Indien het bereik de volgende keer opgelicht wordt, zal de naam verschijnen. Indien u het geselecteerde bereik geen naam hebt gegeven, verschijnen gewoon de coördinaten van de eerste cel in het bereik. Als u enkel een deel van het bereik selecteert, verschijnt eveneens enkel de coördinaat van de eerste cel. Een bereik met een naam kan worden aangesproken met die naam. Het is goed mogelijk om met EXCEL te werken zonder ooit cellen te benoemen. Het gemak van een naam komt vooral tot uiting bij grote werkbladen (met duizenden ingevulde cellen). Een naam maakt dan het navigeren door het werkblad en het construeren van formules eenvoudiger. Zo hoef je niet steeds het gewenste bereik op te lichten of de celcoördinaten in te geven. De formulebalk geeft de inhoud van een cel weer. Naamvak

Formulebalk

Figuur 6

3. Het gebruik van cellen De inhoud van een cel kan bestaan uit tekst, getallen of formules. EXCEL gaat zelf na waarover het gaat. Als u zowel tekst als getallen ingeeft, bv. “observatie 4”, dan aanziet EXCEL dit als tekst. Dit is logisch omdat je met dit soort informatie niet kan rekenen. Wanneer u een formule ingeeft, moet u er een gelijkheidsteken voor zetten. Doet u dit niet dan aanziet EXCEL de formule als tekst. Als u bv. “5+7” ingeeft in de formulebalk, zal in de betreffende cel eveneens “5+7” verschijnen. Wanneer u echter “= 5+7” ingeeft, zal in de cel “12” verschijnen, het resultaat van de formule. Een komma (in een kommagetal) moet in EXCEL ook effectief een komma zijn. Wanneer u een punt zou ingeven, zou EXCEL de

9 invoer als tekst aanzien. In formules gelden de normale voorrangregels van de bewerkingen. U kan op meerdere manieren de inhoud van een cel aanpassen. Een eerste manier is gewoon de cel actief te maken door er op te klikken waardoor er een vet kader rond de cel verschijnt en te typen. Terwijl u de gegevens invoert, verschijnen deze ook in de formulebalk. Ook mogelijk - maar iets omslachtiger - is de cel aan te klikken, de muisaanwijzer naar de formulebalk te brengen en te klikken op de formulebalk. Wat u in de formulebalk tikt, zal ook in de cel verschijnen. Bovendien verschijnen naast de formulebalk ook de annuleerknop (rood kruisje) en de invoerknop (groen v’tje). U bevestigt de invoer met een klik op de invoerknop, door op ENTER te drukken of door gewoon een andere cel te selecteren waardoor de invoer automatisch wordt geaccepteerd. Om de foutieve invoer te negeren of de vorige te behouden, kan u op de annuleerknop drukken. Cellen kunnen formules bevatten die naar de inhoud van andere cellen verwijzen, bv. cel A5 kan als inhoud hebben “= A2+7*A4-A3/2”. Aan de hand van de waarde van de genoemde cellen in de formule wordt dan de inhoud van cel A5 berekend. Verandert u de waarde van bv. cel A3, dan wordt de waarde van de cel A5 meteen aangepast. Om naar cellen op een ander werkblad te verwijzen, dient u voor de cel nog de naam van het werkblad te tikken gevolgd door een uitroepteken. Stel dat u op het werkblad met de naam Sheet1 werkt en u wil in cel A3 de som maken van cel C3 en cel C3 op het werkblad genaamd Sheet2. Daartoe typt u het volgende commando in cel A3 : “= C3+Sheet2!C3”. Cellen en bereiken van cellen kopiëren kan heel handig in EXCEL. De basismanier verloopt als volgt : licht de cel of het bereik op, plaatst de muisaanwijzer op de rand van het kader, zodat de vorm van de muisaanwijzer verandert in een pijl. Daarna drukt u op de linkermuisknop en terwijl u de linkermuisknop ingedrukt houdt, kan u de selectie slepen naar de gewenste positie. Kopiëren en plakken kan ook door de respectievelijke sneltoetsen CONTROL + C en CONTROL + V, of door de knoppen ‘Copy’ en ‘Paste’ in de werkbalk standaard. Als er formules in de selectie aanwezig zijn veranderen die mee. Als u bv. cel B11 met de formule “= SUM(B2:B10)” verplaatst naar cel F51, luidt de verplaatste formule “= SUM(F42:F50)”. De formule verandert omdat de celverwijzingen relatief zijn ten opzichte van de positie van de cel met de formule. Soms is dit echter niet gewenst. In dat geval moet u gebruik maken van absolute celverwijzingen. Dit kan u doen door in de oorspronkelijk formule dollartekens ($) voor sommige van de coördinaten te zetten. Daarmee geeft u aan dat de betreffende coördinaten vast zijn. Wanneer u bv. de cel

10 B11 met de formule “= SUM($B$2:$B$10)” opnieuw kopieert naar cel F51 zal de formule niet veranderen. Stel dat de formule “= SUM(B$2:B$10)” is dan zal de formule, indien u de cel B11 naar cel F51 kopieert er als volgt uitzien : “= SUM(F$2:F$10)”. Enkel de coördinaat met het dollarteken vóór blijft vast. Voor wat betreft het kopiëren van cellen biedt EXCEL nog een interessante optie aan : kopiëren door slepen. Stel dat u een reeks van getallen van 0 tot 1 met een tussenstap van 0,01 in de cellen A1:A101 wil ingeven. De 100 waardes manueel ingeven is vrij tijdrovend en het kan daarenboven eenvoudiger. Geef in de cel A1 de waarde “0” in. Vervolgens geeft u in de cel A2 de waarde “0,01” in. In cel A3 typt u nu de waarde “0,02”. Licht hierna de cellen A1 tot en met A3 op. In de rechterbenedenhoek van het opgelichte kader zal u een klein vierkantje zien (zie Figuur 7).

Het kleine vierkantje waarvan sprake

Figuur 7 Wanneer u de muisaanwijzer naar dit vierkantje beweegt, zal de muisaanwijzer veranderen in een fijn kruis. Wanneer u nu links klikt, de muisknop ingedrukt houdt en de muisaanwijzer naar beneden sleept, zal u zien dat EXCEL de reeks zelf verder aanvult. U kan zo de selectie doortrekken tot cel A101. EXCEL zoekt zelf een patroon in de opgelichte selectie (hier cellen A1:A3) en trekt dit door. Hetzelfde is mogelijk met de dagen van de week, maanden, data of eenvoudige opsommingen zoals observatie 1, observatie 2, observatie 3 enz. Hoeveel cellen u manueel moet ingeven tot EXCEL in staat is de reeks door te trekken staat niet vast. Meetkundige reeksen kan EXCEL echter (nog) niet herkennen, rekenkundige wel. Het gemak van het kopiëren door slepen komt vooral tot uiting bij het gebruik van functies. Over het gebruik van

11 functies volgt later meer. Hier wordt enkel een eenvoudig voorbeeld behandeld dat u moet duidelijk maken waar het kopiëren door slepen van pas kan komen. Voorbeeld Stel dat in de cellen A1 tot en met A101 de waarden van 0 tot 1, met een tussenstap van 0,01, staan ingevuld en u van elk van deze waarden de sinuswaarde wil weergeven bv. in de cellen B1 tot B101. Daartoe geeft u in de cel B1 het volgende commando in “= SIN(A1)”. De waarde “0” verschijnt in cel B1. U kan nu op precies dezelfde manier als hierboven beschreven de reeks doortrekken tot cel B101. Licht cel B1 op, breng de muisaanwijzer naar het kleine vierkantje in de rechterbenedenhoek en trek de reeks door. Aangezien de celverwijzing relatief is, verandert die steeds mee (en dit is precies wat u wil). Het is logisch dat EXCEL de cel B1 gewoon kopieert. Een patroon valt er immers niet in het geselecteerde bereik (hier één cel) te ontdekken. Hetzelfde zou gebeuren indien u bv. in cel D1 de waarde “1” invult en die doortrekt tot D101. EXCEL kan geen patroon ontdekken en kopieert gewoon de inhoud van cel D1. < EXCEL biedt de mogelijkheid om op een snelle manier cellen te sorteren, wat bv. handig kan zijn als u kansen van groot naar klein wil ordenen. Licht het bereik op van cellen die u wil sorteren en druk op de knop ‘Sort Ascending’ om de waardes van klein naar groot te sorteren of op de knop ‘Sort Descending’ om van groot naar klein te gaan. Lukrake getallen die u hebt aangemaakt (zie verder), kunnen alleen gesorteerd worden indien ze vast zijn.

4. Het gebruik van functies EXCEL kent een groot aantal functies voor allerhande soorten berekeningen, bv. financiële, wiskundige, logische bewerkingen, statistische,… Deze functies zijn standaard ingebouwd. Voorbeeld Stel dat u in cel C11 de inhoud van de cellen C2 tot en met C10 wenst op te tellen. Een formule als “= C2+C3+C4+C5+C6+C7+C8+C9+C10” is behoorlijk omslachtig. De ingebouwde somfunctie biedt hier een handig alternatief. Bij de somfunctie dient u als argument enkel het bereik op te geven waarbinnen de cellen moeten worden opgeteld, en dus niet langer elke cel afzonderlijk. De optelling van de cellen C2 tot en met C10 formuleert u dan als volgt : “= SUM(C2:C10)”. Een bereik wordt steeds aangegeven door de eerste en de laatste cel, gescheiden door een dubbelpunt. Omdat de

12 somfunctie frequent voorkomt, zit er ook een knop ‘Sum’ ingebouwd in de werkbalk standaard (zie Figuur 8). Wanneer u op de knop ‘Sum’ drukt, zal in de actieve cel de somfunctie verschijnen. EXCEL gaat zelf na welk bereik het meest waarschijnlijk is en vult dat in. In het werkblad verschijnt er een bewegend kader rond het gekozen bereik. Als het voorstel van EXCEL correct is, klikt u op de invoerknop of drukt u op ENTER, zoniet verandert u het bereik zelf. Indien u het bereik een naam hebt gegeven, kan u de naam als argument meegeven in een functie. Indien u bv. in de cellen C2:C10 de waarnemingen van een steekproef hebt ingegeven, kan u dat bereik de naam “observaties” geven. U kan dan het volgende commando ingeven “= SUM(observaties)” om de som van alle waarden te bekomen. De somfunctie is bv. erg handig indien u snel wil nakijken of kansen optellen tot 1. < Functies, vooral de complexere, zorgen vaak voor verwarring. Zo is het niet altijd even makkelijk om de nodige argumenten, die bepalen hoe de functie wordt uitgevoerd, in te geven. Daarom is een speciale functiewizard voorzien. De wizard helpt u bij het kiezen van een functie en het invullen van de juiste parameters van een functie, zodat fouten minder snel voorkomen. Er is een speciale knop ‘Paste Function’ voorzien die de functiewizard activeert (zie Figuur 8). Knop voor het oproepen van de functiewizard

Knop voor het oproepen van de somfunctie

Figuur 8 Als u een functie wil gebruiken, selecteer dan eerst de cel waarin u de formule wil plaatsen. Klik vervolgens op de knop ‘Paste Function’ in de werkbalk standaard. Deze knop activeert de functiewizard (zie Figuur 9).

13

De knop om de HELP te activeren

Figuur 9 Klik op de gewenste functiecategorie en vervolgens op de gewenste functie. Indien u niet weet in welke categorie een functie thuishoort, kan u op ‘All’ klikken om alle functies te zien. Als u op de naam van een functie klikt, verschijnt de schrijfwijze van de functie, inclusief argumenten, onder in het dialoogvenster, samen met een korte beschrijving van het doel van de functie. Als dit niet duidelijk genoeg is, klik dan op de knop HELP om de functie te bestuderen (zie Figuur 9). Of licht de functie op en druk op F1, waardoor u eveneens de HELP activeert. Op enkele statistische functies wordt in de cursus nog in detail teruggekomen. Voorbeeld Stel dat u een actuele waarde wil berekenen van een reeks van 5 betalingen van 18000 €. Geef eerst, bv. in cel A1, het bedrag in dat u periodisch moet betalen, dus “18000”. De actuele waarde zetten we bv. in cel C1. De actuele waarde berekenen kan in EXCEL met behulp van de functie ‘PV’ die terug te vinden is in de categorie van de financiële functies. Klik op de knop ‘OK’ of druk op ENTER om de keuze van de functie te bevestigen zodat het volgende scherm verschijnt (zie Figuur 10). In de actieve cel en de formulebalk kan u “= PV()” aflezen.

14

Figuur 10 Vervolgens moet u de argumenten ingeven. Dit kan u doen aan de hand van het zopas verschenen venster. U ziet dat de PV-functie 5 argumenten heeft. De vetgedrukte argumenten (de eerste drie) zijn verplicht in te geven, de overige zijn optioneel. Het eerste argument is de interest per periode (Rate). Onderaan het venster ziet u telkens wat uitleg over het in te vullen argument. Geef als interest bv. “0,055” in en druk op de tabtoets. Hierdoor verspringt de cursor naar het volgende invulvak en verschijnt onderaan het venster de uitleg van het volgende argument, het aantal periodes (Nper). In ons geval is dit 5. Druk terug op de tabtoets, zodat de cursor springt naar het invulvak naast het derde argument (Pmt). Merk op dat onderaan het kader, naast de woorden ‘Formula result =’ nog niets staat ingevuld. Dit is logisch omdat minstens de eerste drie argumenten moeten worden ingevuld. Het derde argument vraagt het bedrag dat u elke periode betaalt. Dit bedrag staat in cel A1. U kan in het invulvak nu gewoon “A1” typen of op het werkblad de cel A1 aanklikken. Indien de cel A1 niet zichtbaar is, kan u klikken op de knop rechts van het invulvak. Hierdoor verschijnt de volgende balk op het scherm (zie Figuur 11).

Figuur 11 Nu kan u gemakkelijk cel A1 aanklikken waardoor de waarde “A1” in de balk verschijnt en rond de cel A1 een streepjeskader beweegt. Klik terug op de knop rechts in de balk om terug te keren naar het venster met de argumenten van de functie. Let erop dat nu naast de woorden ‘Formula result =’ de waarde -76865,12056 verschijnt. Tot slot nog de laatste 2 argumenten. Het argument ‘FV’ is het bedrag dat u wil overhouden na de laatste betaling. Veronderstel dat dit 0 is. U kan “0” ingeven of het invulvak leeg laten, EXCEL

15 veronderstelt namelijk standaard de waarde 0. Het laatste invulvak betreft een 0-1 variabele. Neem aan dat onze betalingen geschieden aan het begin van elke periode. Uit de uitleg naast ‘Type’ onderaan het venster kan u opmaken dat we dan de waarde 1 moeten meegeven in het invulvak naast ‘Type’. Standaard wordt immers 0 verondersteld. U zal zien dat ‘Formula result =’ nu verandert naar -81092,70219. Klik tot slot op de knop ‘OK’ of druk op ENTER om de invoer van de argumenten te bevestigen. In cel C1 verschijnt nu de geformateerde rode getalwaarde “-81.092,70 BEF”. Om de aanduiding “BEF” om te zetten in “€” kan u rechts klikken op de cel, voor ‘Format Cells...’ kiezen en op het tabblad ‘Number’ ‘Currency’ aanklikken waarna u de gepaste munteenheid kan aanklikken. Let ook op de manier van noteren van de functie in de formulebalk. Indien u vertrouwd bent met de functies, kan u ze ook rechtstreeks ingeven in de formulebalk, op voorwaarde dat u de notatie respecteert. < Sommige functies hebben als uitvoer een reeks van waarden i.p.v. één waarde. Bijvoorbeeld de functie ‘= MINVERSE()’ die de inverse van een matrix berekent. Om de invoer van een dergelijke functie te bevestigen, mag u niet op de klassieke manier bevestigen, nl. door op de knop ‘OK’ of op ENTER te drukken. Een correct resultaat krijgt u enkel door te bevestigen via de toetsencombinatie CONTROL + SHIFT + ENTER. De SHIFT-toets is de toets met het pijltje ⇑. Dergelijke functies vragen een beetje wenning. Mocht u bij het gebruik ervan ooit vast komen te zitten, druk dan op de toets “esc” en probeer alles opnieuw. Voorbeeld Veronderstel dat u een 3 op 3 matrix ingeeft in de cellen A1:C3 en die wenst te inverteren. De inverse matrix, eveneens een 3 op 3 matrix, zal dan eveneens 9 cellen beslaan. Om de matrix te inverteren moet u als volgt te werk gaan. Klik op de cel E1 en licht een bereik van 3 bij 3 cellen op, bv. E1:G3. Druk hierna op de knop ‘Paste Function’ die de functiewizard opent. Zoek de functie MINVERSE en klik op de knop ‘OK’. Het volgende scherm zal verschijnen (zie Figuur 12).

Figuur 12

16 In het invulvak moet u de plaats aangeven waar de te inverteren matrix zich bevindt. U kan dit doen door “A1:C3” te typen of door het bereik van cellen op te lichten op het werkblad. U zal naast ‘Formula result =’ maar één cijfer zien, terwijl het resultaat eigenlijk een 3 op 3 matrix zou moeten zijn. Dat komt omdat EXCEL er nog van uit gaat dat de formule enkel geldt voor de cel waarin u de formule intikt, nl. cel E1. Indien u op ‘OK’ klikt (of op ENTER drukt), zal u zien dat enkel de cel E1 een waarde zal krijgen en de overige cellen van E1:G3 niet. Om de correcte matrix te verkrijgen, mag u dus niet op de klassieke manier bevestigen. De juiste manier is op de toetsencombinatie CONTROL + SHIFT + ENTER te drukken. Zo geeft u aan EXCEL mee dat de formule geldt voor alle opgelichte cellen en zal de inverse matrix terechtkomen in de cellen E1:G3. Wanneer u bv. op de cel G2 klikt, zal u zien dat de formulebalk de volgende formule toont : “{= MINVERSE(A1:C3)}”. De verzamelingtekens geven aan dat de formule geldt voor een bereik van cellen. < Telkens wanneer u een functie gebruikt waarvan het resultaat meerdere cellen beslaat, moet u op de hierboven beschreven manier te werk gaan. Dus, het bereik oplichten, de formule ingeven en bevestigen met CONTROL + SHIFT + ENTER.

5. De opmaak van het werkblad Het kan zijn dat een cel te klein is om de volledige celinhoud weer te geven. Als u meer tekst invoert dan in de breedte van de kolom past, loopt de tekst over de celgrens heen, tot in de volgende kolom, tenzij de buurcel zelf gegevens bevat. In dat geval is slechts een gedeelte van de tekst zichtbaar. Getallen die te breed zijn voor de kolom worden soms in de wetenschappelijke notatie weergegeven. In andere gevallen past de kolombreedte zich automatisch aan. Zo kan 1 000 000 000 bv. worden weergegeven als 1E+09. Als u de cel activeert, ziet u in de formulebalk echter het getal zoals u het hebt ingevoerd. U kan getallen ook een vaste opmaak geven. Dit gebeurt door op de cel te staan en rechts te klikken zodat een snelmenu verschijnt. Hier kan u de optie ‘Format Cells…’ kiezen zodat het venster van Figuur 13 verschijnt.

17

Figuur 13 Aan de hand van dit venster is het mogelijk de eigenschappen van de cel aan te passen bv. lettertype, kleur, dikte van de randen, enz. Dit kan van pas komen om het werkblad overzichtelijk te houden. Wanneer u het blad met de aanduiding ‘Number’ kiest, kan u de getallen een opmaak meegeven. De codes zijn soms niet zo duidelijk maar onder ‘Sample’ ziet u hoe de cel er zou uitzien met de gekozen opmaak. Als een opgemaakt getal te breed is voor de kolom, verschijnen er hekjes (###) in plaats van het getal. Op een analoge manier kan u de eigenschappen van alle cellen in een kolom of rij aanpassen door de kolom of de rij op te lichten, rechts te klikken en ‘Format Cells…’ te selecteren in het snelmenu. Ook de kolombreedte en de rijhoogte zijn gemakkelijk aan te passen. De breedte van een kolom kan u veranderen door de rechterrand van de kolomkop te verslepen. Als er meerdere kolommen zijn geselecteerd, worden alle geselecteerde kolommen even breed als de gewijzigde kolom. Een andere manier is de muisaanwijzer op de kolomkop te zetten en rechts te klikken zodat een snelmenu verschijnt waar u de optie ‘Column Width…’ kan selecteren. Een handige optie om de breedte van een kolom automatisch aan te passen aan de inhoud van de breedste cel in de kolom is te dubbelklikken op de rechterrand van de kolomkop. De rijhoogte kan u aanpassen door de onderkant van de rijkop te verslepen. Of u kan ook werken aan de hand van het snelmenu dat verschijnt wanneer u rechts klikt op de rijkop.

18

6. Het opslaan, sluiten en oproepen van een werkmap Het opslaan van een werkmap gebeurt op dezelfde manier als bij een WORD–document. De extensie van het bestand is echter XLS in plaats van DOC. U kan een werkmap opslaan via de werkbalk standaard door op de knop ‘Save’ te drukken, of door in de menubalk achtereenvolgens de opties ‘File’ en ‘Save’ (of ‘Save as…’) te selecteren. Het kan ook via de toetsencombinatie CONTROL + S. Indien u de werkmap voor de eerste maal opslaat, zal u verzocht worden die een naam en locatie te geven. De volgende keer dat u de werkmap opslaat, wordt de vorige versie overschreven door de aangepaste versie en onder dezelfde naam en op dezelfde locatie bewaard. De werkmap kan u sluiten door de opdracht ‘Close’ in het menu ‘File’ te kiezen of door te klikken op de onderste (!) knop met het kruisje in de rechterbovenhoek van het scherm. De bovenste knop met een kruisje sluit EXCEL af (zie Figuur 14). Met deze knop sluit u EXCEL af

Figuur 14

Met deze knop sluit u de werkmap

Indien u nog wijzigingen hebt aangebracht die nog niet werden opgeslagen, zal een venster verschijnen waarin u wordt gevraagd of de veranderingen dienen te worden opgeslagen of niet. Het openen van een werkmap is gelijkaardig aan het openen van een tekstdocument in WORD. Dit kan via de knop ‘Open’ in de werkbalk standaard, of door ‘Open’ onder ‘File’ in de menubalk te kiezen. De werkmappen die het laatst zijn geopend, zijn aan het menu onder ‘File’ toegevoegd, en kunnen worden geopend door de naam te selecteren en te klikken.

19

7. Het afdrukken van een werkmap Als u gebruik maakt van de standaardopties is afdrukken in EXCEL erg eenvoudig. U kan echter ook kiezen uit een groot aantal afdrukopties waarmee u de afdruk volledig naar eigen smaak regelt. Als u het werkblad zo eenvoudig mogelijk wenst af te drukken hoeft u alleen maar op de knop ‘Print’ op de werkbalk standaard te drukken. EXCEL schikt dan zelf het werkblad op, eventueel op verschillende pagina’s. Het resultaat oogt soms een beetje rommelig, zodat de afdrukopties van pas kunnen komen. De afdrukopties kan u regelen via het venster ‘Page Setup…’ onder de optie ‘File’ in de menubalk (zie Figuur 15). Hier kan u bv. de marges instellen, het werkblad verkleinen of vergroten, de rasterlijnen uitvegen, staand of liggend formaat kiezen, kop- of voetteksten toevoegen, enz.

Figuur 15 Een interessante optie is dat u ook delen van een werkblad kan afprinten. Dit gebeurt door op het tabblad ‘Sheet’ (zie Figuur 15) in het invulkader voor ‘Print Area’ het gewenste bereik in te geven bv. A1:G36. U kan ook op het kader naast ‘Print Area’ klikken en op het werkblad het bereik aanduiden met de muis. Nog een andere mogelijkheid om een bereik af te drukken is het bereik te selecteren, vervolgens in de menubalk de optie ‘File’ en ‘Print…’ te kiezen en op het keuzerondje ‘Selection’ in het venster ‘Print’ te klikken.

20

Om te weten hoe het werkblad zal worden afgedrukt kan u altijd op ‘Print Preview’ klikken in de optie ‘File’ in de menubalk of de knop ‘Print Preview’ in de werkbalk standaard aan te klikken. Er zijn nog andere manieren om het afdrukvoorbeeld op te roepen, zoals met de knop ‘Print Preview’ in het venster voor ‘Page Setup’ (zie Figuur 15).

21

B. Statistische taken in EXCEL

1. Willekeurige getallen genereren Willekeurige getallen zijn vaak vereist bij simulaties van kansexperimenten. Ze kunnen binnen EXCEL zonder veel moeite worden aangemaakt. Er zijn 3 manieren om dit te doen. De functie RAND() genereert (pseudo)lukrake getallen groter of gelijk aan 0 en kleiner dan 1. Telkens er een herberekening plaatsvindt in het werkblad (bv. door op F9 te drukken), wordt een nieuw willekeurig getal gegenereerd. Soms is dit hinderlijk. Wanneer u niet wil dat er bij elke herberekening nieuwe willekeurige getallen worden gegenereerd, moet u als volgt te werk gaan. Licht een cel op, geef in de formulebalk “= RAND()” in en druk op F9 (dus niet ENTER). Daarmee verandert de formule “= RAND()” in een vast willekeurig getal. Het nadeel hiervan is dat u niet langer nieuwe lukrake getallen kan aanmaken door de cel te kopiëren door slepen. Om een reeks van vaste lukrake getallen te bekomen, kan u bv. als volgt te werk gaan. Geef in een cel, bv. A1, de formule “= RAND()” in en kopieer de formule door slepen over een bereik ter grootte van het bereik waarin u lukrake getallen wenst te zetten, bv. A1:A10. Licht vervolgens het bereik (A1:A10) op en klik rechts. In het submenu kiest u voor “Copy” waardoor een bewegend kader rond het bereik verschijnt. Hierna selecteert u de eerste cel van het bereik waarin u de vaste lukrake getallen wenst te verkrijgen, bv. cel B1. Klik vervolgens op de optie ‘Paste Special…’ onder ‘Edit’ in de menubalk, zodat het volgende venster verschijnt (zie Figuur 16).

Figuur 16

22

Klik op het selectierondje naast ‘Values’ en bevestig door op de knop ‘OK’ te drukken. Hierdoor worden in het bereik B1:B10 vaste lukrake getallen gezet. Om willekeurige getallen tussen a en b (met b>a) te genereren, kan u de volgende formule gebruiken : “= a + RAND()*(b-a)”. De beide andere manieren steunen op het gebruik van het ‘Analysis ToolPak’. Met behulp van dit pakket kan u in EXCEL gebruik maken van allerhande specifieke statistische functies. Daarvoor moet u het ‘Analysis ToolPak’ eerst installeren in uw versie van EXCEL. Dit verloopt als volgt. Kies in de menubalk de optie ‘Tools’ en vervolgens de optie ‘Add-Ins…’ zodat u het volgende venster te zien krijgt (zie Figuur 17).

Figuur 17 Indien ‘Analysis ToolPak’ in de lijst voorkomt, kruis dan gewoon het vakje naast ‘Analysis ToolPak’ en ‘Analysis Toolpak – VBA’ aan en klik op de knop ‘OK’. Indien het vakje al aangekruist staat, is er geen probleem. Dat betekent dat het pakket al geïnstalleerd was in EXCEL. Het is echter mogelijk dat het ‘Analysis ToolPak’ niet voorkomt in de lijst. In dat geval kan u via de knop ‘Browse…’ het ‘Analysis ToolPak’ (het bestand : analys32.xll) trachten te localiseren en toe te voegen aan de lijst. Lukt dit u niet dan kan u eventueel trachten EXCEL opnieuw te installeren want normaal zit het ‘Analysis ToolPak’ standaard in de lijst van ‘Add-Ins’ die worden toegevoegd bij installatie. Belangrijk om weten bij het gebruik van het ‘Analysis Toolpak’ is dat de berekende resultaten als vaste waarden worden ingevoegd in een werkblad. Dit betekent dat wanneer de brongegevens gewijzigd worden, de overeenkomstige resultaten niet automatisch worden aangepast.

23

Het ‘Analysis ToolPak’ pakket geeft een tweede mogelijkheid om willekeurige getallen te genereren via de functie RANDBETWEEN(bottom,top). Deze functie stelt u in staat gehele lukrake getallen aan te maken. De RANDBETWEEN functie geeft u een willekeurig geheel getal kleiner of gelijk aan de bovengrens (top) en groter of gelijk aan de ondergrens (bottom). Ook hier wordt er telkens een nieuw getal berekend als u een herberekening uitvoert in het werkblad. Om het getal vast te zetten, kan u terug drukken op F9 wanneer u de formule hebt ingegeven of kopiëren en plakken met ‘Paste Special…’. De derde manier om lukrake getallen aan te maken loopt via de optie ‘Data Analysis…’ onder ‘Tools’ in de menubalk. Indien het ‘Analysis ToolPak’ niet is geïnstalleerd, zal deze optie niet beschikbaar zijn. Klik vervolgens op de optie ‘Random Number Generation’ zodat het volgende scherm verschijnt (zie Figuur 18).

Figuur 18 Neem aan dat u voor 1 variabele 10 willekeurige waardes wenst te genereren. In het invulvak naast ‘Number of Variables:’ vult u dan 1 in en naast ‘Number of Random Numbers:’ 10. Merk op dat het vak ‘Distribution:’ u toelaat een bepaalde kansverdeling of kansdichtheid op te geven. De lukrake getallen worden dan volgens de opgegeven kansdichtheid of -verdeling aangemaakt. Later in de cursus zal de betekenis hiervan u wel duidelijk worden. Stel dat u gevraagd wordt lukrake getallen volgens de normale verdeling te genereren. Kies onder ‘Distribution’

24 voor ‘Normal’. Elk van de beschikbare kansverdelingen of – dichtheden vraagt de invoer van een aantal parameters. Bij de normale dichtheid is dit, zoals u kan zien, een gemiddelde en een standaarddeviatie. EXCEL vult standaard de waarden voor de standaardnormale dichtheid in, 0 en 1. Neem aan dat dit in orde is. Vervolgens komt het vak ‘Random Seed:’. Dit vak is optioneel. De willekeurige getallen die u aanmaakt worden bepaald door de parameter ‘Random Seed’. Echt lukraak zijn de getallen dus niet (vandaar de term pseudolukraak). Vult u daar een getal in, dan zal u telkens dezelfde willekeurige getallen genereren. Indien u geen waarde invult, kiest EXCEL zelf een (pseudo)willekeurige waarde voor ‘Random Seed’. Laat het vak bv. leeg. Selecteer tot slot het keuzerondje links van ‘Output Range:’ en geef een bereik van 10 cellen op in het invulvak, bv. B1:B10. Druk op de knop ‘OK’. De getallen die worden gegenereerd zijn vast, d.w.z. er verschijnen geen nieuwe waarden telkens een herberekening in het werkblad plaatsvindt.

2. Een grafiek maken Grafieken zijn grafische weergaves van waarden op het werkblad. Om bv. de functie f(x)= x² weer te geven, moet u er voor zorgen dat er voldoende functiewaarden op het werkblad staan. EXCEL kan niet zomaar een functie tekenen, enkel celwaarden grafisch uitzetten. Elke waarde komt voor als een gegevenspunt in de grafiek. Een gegevenspunt kan weergegeven worden als een taartpunt, een staaf of een punt. Een verzameling gegevenspunten die afkomstig is uit dezelfde kolom of rij heet een gegevensreeks (serie). Een grafiek kan een vrijwel onbeperkt aantal gegevensreeksen bevatten, met uitzondering van de taartgrafiek, die maar één gegevensreeks kan weergeven. Behalve de gegevensreeksen kan een grafiek ook een titel, asaanduidingen, een legende (per gegevensreeks) en vele andere onderdelen bevatten. De grafiek is gekoppeld aan de gegevens; veranderen de gegevens dan verandert ook de grafiek. De grafiekenwizard leidt u bij het maken van een grafiek en laat u toe (bijna) alle gewenste instellingen aan te brengen. Om een grafiek te maken gaat u als volgt te werk. Klik op de knop ‘Chart Wizard’ in de werkbalk standaard of kies ‘Insert’ en ‘Chart…’ in de menubalk zodat het eerste venster van de grafiekenwizard verschijnt (zie Figuur 19).

25

Figuur 19 Loop door de wizard en construeer de grafiek naar wens. De grafiekenwizard is vrij duidelijk op zich en vraagt enkel een beetje oefening. Twee voorbeelden ter verduidelijking. Voorbeeld Stel dat we twee variabelen willen uitzetten in een zogenaamde puntenwolk (scatter plot). Veronderstel dat de waarden van de variabelen zich in kolom A (A2:A11) en kolom B (B2:B11) bevinden (zie Figuur 20).

26

Figuur 20 Open de grafiekenwizard door bv. op de knop ‘Chart Wizard’ te drukken. Kies als grafiektype ‘XY (Scatter)’ met als subtype de ‘Scatter. Compares pairs of values.’ (dit staat standaard ingesteld) en klik op de knop ‘NEXT>’. EXCEL veronderstelt, zoals u kan aflezen uit het ‘Data Range’ tabblad, dat de gegevensreeksen in de kolommen op het werkblad vermeld staan en selecteert meteen het correcte bereik. Indien dit niet zo zou zijn, kan u zelf met de muis het correcte bereik selecteren op het werkblad. Wanneer u de muisknop loslaat, vult EXCEL het door u geselecteerde bereik in in het invulvak ‘Data Range’. Op het tabblad ‘Series’ vindt u o.a. de mogelijkheid om de X- en Y-as om te wisselen als u dit wil. Merk op dat EXCEL de grafiek al de titel ‘breedte’ heeft gegeven. Ook de legende draagt de naam ‘breedte’. De naam van de legende kan u veranderen door inhoud van het invulvak naast ‘Name:’ op te lichten en een andere naam in te geven. Geef als legende bv. “Observaties” mee. Wanneer alles naar uw zin lijkt, klikt u op de knop ‘NEXT>’. Op het tabblad ‘Titles’ kan u de grafiek een titel geven alsook de assen benoemen. Verander de titel van “Observaties” naar “Puntenwolk” en benoem de assen met “Lengte” en “Breedte”. Op de andere tabbladen kan u met behulp van eenvoudige aankruisvakjes opties aanen uitschakelen. Deze spreken voor zich. Veeg bv. de rasterlijnen (gridlines) uit. Klik daarna terug op de knop ‘NEXT>’. Vermeldenswaard is nog de laatste optie die u in staat stelt de grafiek als een object in te voegen op een bestaand werkblad of als een zogenaamd grafiekblad. Indien u voor het grafiekblad opteert, wordt een werkblad aan de werkmap toegevoegd waarop de grafiek wordt uitgezet. Kies bv. voor dit laatste en geef als titel voor het grafiekblad “Puntenwolk” mee. Mocht u nog iets willen veranderen, kan u naar voorgaande vensters terugkeren met behulp van de knop ‘
27 u enkel de instructies van de tekst hebt gevolgd - de volgende grafiek op het scherm (zie Figuur 21).

Figuur 21 De grafiek is ook nu nog makkelijk te wijzigen. Stel bv. dat u de achtergrondkleur van de puntenwolk wil wijzigen van grijs naar wit. Beweeg de muisaanwijzer over de achtergrond van de grafiek. Een kadertje met ‘Plot Area’ zal verschijnen en dubbelklik vervolgens. U krijgt het tabblad ‘Patterns’ te zien waarin u zowel ‘Border’ als ‘Area’ kan wijzigen. Zet ze allebei op wit en klik op de knop ‘OK’. De punten zullen nu uitgezet zijn tegen een witte achtergrond. < Voorbeeld Stel dat we een histogram moeten maken van de volgende gegevens (zie Figuur 22). Deze opgave is een stuk technischer. In de loop van de cursus wordt aan het maken van een histogram nog de nodige aandacht besteed.

28

Figuur 22 Een histogram wordt – zoals in de cursus zal worden uitgelegd – opgebouwd aan de hand van klassen. We moeten dus eerst deze 20 observaties verdelen in klassen. Neem aan dat we de volgende 4 intervallen X<5, 5≤X<10, 10≤X<15 en 15≤X als klassen kiezen. Om de 20 observaties in klassen te verdelen kunnen we gebruik maken van de functie FREQUENCY. Vul eerst in de cellen F1:F3 de waarden 4, 9 en 14 in. Zo meteen zal duidelijk zijn waarom dit nodig is. Selecteer nu het bereik G1:G4 en roep de functie FREQUENCY op, bv. met behulp van de functiewizard. Dan krijgt u het volgende venster te zien (zie Figuur 23).

Figuur 23 U ziet dat de functie de invoer van 2 argumenten vereist (allebei vetgedrukt). Vul in het invulvak van de ‘Data_array’ het bereik van de observaties in, nl. A1:D5. Klik vervolgens op het invulvak van ‘Bins_array’. Wanneer u de uitleg onderaan het venster leest, zal u duidelijk worden waarom de waardes in de cellen F1:F3 nodig waren. Aan de hand van deze waarden leidt EXCEL af dat het gaat om de intervallen 0<X≤4, 5<X≤9, 10<X≤14 en 14<X. Let erop dat EXCEL de bovengrens van elk interval tot het interval rekent. Omdat we met gehele waarden werken komt dit precies neer op de gevraagde intervallen.

29 Geef dit bereik (F1:F3) in in het invulvak van ‘Bins_array’. De functie FREQUENCY heeft (zoals af te leiden is uit de omschrijving) als uitvoer een reeks waarden. U kan dus niet bevestigen via de knop ‘OK’ of door te drukken op ENTER maar via de toetsencombinatie CONTROL + SHIFT + ENTER. Daarmee krijgt u het volgende scherm (zie Figuur 24).

Figuur 24 Nu we de klassen hebben opgebouwd, kan het histogram eenvoudig worden geconstrueerd. Geef eerst nog in de cellen H1:H4 de respectievelijke intervallen in bv. “[0-5[”, “[510[”, “[10-15[” en “[15-20[”. Deze aanduidingen komen later van pas om de X-as te benoemen. Activeer de grafiekenwizard en kies als grafiektype ‘Column’. Als subtype van de grafiek kan u bv. ‘Clustered Column. Compares values across categories’ nemen (dit staat standaard ingesteld). Geef als gegevensbereik de cellen G1:G4 in en klik op het keuzerondje bij ‘Columns’. De gegevensreeks zit immers in kolom G. Ga vervolgens naar het tabblad ‘Series’ en vul in het invulvak naast ‘Category (X) axis labels:’ het bereik H1:H4 in. Vervolledig de grafiek naar smaak en klik op de knop ‘FINISH’ wanneer u klaar bent. U zal zien dat de klassen van de grafiek nog niet op elkaar aansluiten. Het is niet moeilijk om dit aan te passen. Dubbelklik op één van de staven. In het venster dat zich opent, kiest u het tabblad ‘Options’. De instelling ‘Gap width:’ bepaalt de afstand tussen de staven. Geef naast ‘Gap width:’ de waarde “0” in. Wanneer u op de knop ‘OK’ klikt, zal u zien dat de staven nu wel op elkaar aansluiten. De grafiek moet er, afhankelijk van uw eigen ingestelde opties, ongeveer zo uitzien als in Figuur 25. Merk nog op dat als u de waarde van een observatie verandert, bv. zet in cel B2 de waarde 19 i.p.v. 1, de formules en het histogram zich automatisch aanpassen.

30

Figuur 25 Het is ook mogelijk om een histogram aan te maken via het ‘Analysis ToolPak’. Selecteer in de menubalk de optie ‘Tools’ en vervolgens ‘Data Analysis…’. In het venster dat verschijnt, kan u de optie ‘Histogram’ selecteren. De werkwijze is vrij gelijkaardig aan de hoger beschreven methode. We zullen ze hier daarom niet doornemen. Mocht u bijkomende uitleg nodig hebben, kan u steeds de HELP raadplegen. < Interessant is misschien nog dat u een grafiek makkelijk kan uitbreiden met een nieuwe gegevensreeks door de reeks naar de grafiek te slepen. Licht de gegevensreeks op en beweeg de muisaanwijzer naar de rand van het kader totdat hij in een pijltje verandert. Klik links, houd de muisknop ingedrukt, sleep de gegevens naar de grafiek en laat ze los. EXCEL zal ze invoegen in de grafiek. Indien de grafiek zich op een grafiekblad bevindt, kan u de gegevensreeks gewoon kopiëren (bv. met de knop ‘Copy’ in de werkbalk standaard). Vervolgens gaat u naar het grafiekblad en duwt u op de knop ‘Paste’ in de standaard werkbalk. Of u kan gebruik maken van de toetsencombinatie CONTROL + C en CONTROL + V om respectievelijk het bereik te kopiëren en te plakken.

31

3. Verdelingen en dichtheden uitzetten Discrete kansvariabelen kunnen grafisch worden voorgesteld aan de hand van een kansverdeling en een verdelingsfunctie. Voor een continue kansvariabele spreken we respectievelijk van de kansdichtheid en de verdelingsfunctie. Deze begrippen komen nog uitvoerig aan bod in de cursus. Het is mogelijk om met behulp van EXCEL deze functies uit te tekenen. Er volgen twee voorbeelden ter illustratie. Het eerste behandelt een discrete kansvariabele, het tweede een continue. Voorbeeld Veronderstel dat we de kansverdeling en de verdelingsfunctie van een Poisson verdeelde kansvariabele X met λ = 3 willen uittekenen. U hoeft voorlopig nog niet te weten wat een Poisson verdeelde kansvariabele met parameter λ precies beduidt. Het is hier enkel de bedoeling om te illustreren dat de mogelijkheden van EXCEL om grafieken te maken vrij uitgebreid zijn. Om de kansverdeling te tekenen, moeten we eerst functiewaarden genereren. Zet in de cellen A1:A27 getallen van 0 tot 8 waarbij elk getal drie keer voorkomt (zie Figuur 26).

Figuur 26 Deze getallen zijn de argumenten van de functie. Waarom elk getal drie keer moet voorkomen, zal u later duidelijk worden. Selecteer vervolgens de cel B2 en roep de functie POISSON op. Het volgende scherm zal verschijnen (zie Figuur 27).

32

Figuur 27 Geef in het invulvak naast ‘X’ de cel A2 op. In het invulvak naast ‘Mean’ vult u “3” in en naast ‘Cumulative’ tikt u “FALSE”. Hierna klikt u op de knop ‘OK’, zodat de waarde “0,049787” in cel B2 verschijnt. Kopieer vervolgens de inhoud van cel B2 naar de cellen B5, B8, B11, B14, B17, B20, B23 en B26. Let erop dat de relatieve celverwijzing naar cel A2 zich in de formule heeft aangepast aan de overeenkomstige cel uit kolom A. In de nog lege cellen van het bereik B1:B27 voert u de waarde “0” in. Nu maken we, met behulp van de grafiekenwizard de grafiek van de kansverdeling aan. Kies als grafiektype ‘XY (Scatter)’ en als subtype ‘Scatter with data points connected by lines without markers.’ Geef op het tabblad ‘Data Range’ het bereik A1:B27 op. Veeg de rasterlijnen weg en verwijder de legende. Voeg de grafiek in in een bestaand werkblad, bv. Sheet1. Hierdoor krijgt u de volgende figuur te zien (zie Figuur 28).

Figuur 28 Indien u dat wil, kan u de opmaak van de grafiek nog veranderen door te dubbelklikken op het element van de grafiek

33 dat u wil veranderen. Stel dat u de asaanduidingen op de X-as wil aanpassen. Dubbelklik ter hoogte van de asaanduidingen zodat het volgende venster verschijnt (zie Figuur 29). Neem als ‘Major Unit:’ bv. 1 en klik op de knop ‘OK’. De asaanduidingen zullen nu opeenvolgende getallen zijn.

Figuur 29 Vervolgens bouwen we nog een grafiek van de verdelingsfunctie. Selecteer hiervoor een nieuw werkblad, bv. Sheet2 en zet in de cellen A1:A18 opnieuw de waarden van 0 tot 8 waarbij elke waarde twee keer voorkomt. Selecteer de cel B2 en kies terug de functie POISSON. Geef in het invulvak naast ‘X’ de cel A2 op. In het invulvak naast ‘Mean’ vult u “3” in en naast ‘Cumulative’ tikt u “TRUE”. Hierna klikt u op de knop ‘OK’ waardoor de waarde “0,049787” in de cel B2 verschijnt. Kopieer de inhoud van cel B2 naar de cellen B4, B6, B8, B10, B12, B14, B16 en B18. In cel B1 geeft u de waarde “0” in en in de overige cellen de waarde van de ervoor komende cel, dus in cel B3 de waarde van cel B2, in cel B5 de waarde van cel B4 enz. Op basis van deze gegevensreeksen maken we een grafiek aan van de verdelingsfunctie. Activeer de grafiekenwizard en kies als grafiektype terug ‘XY (Scatter)’ met hetzelfde subtype als zonet. Geef als ‘Data Range’ het bereik A1:B18 op en vervolledig de grafiek naar smaak. Het eindresultaat zal een trapfunctie zijn die er ongeveer moet uitzien als in de Figuur 30. <

34

Figuur 30

Voorbeeld Stel dat we de dichtheid en de verdelingsfunctie van een exponentiële kansvariabele met λ = 1/3 willen uitzetten. De werkwijze is analoog en vereenvoudigt zelfs in die zin dat elk argument maar één keer moet opgenomen worden in de kolom van de argumenten. Zet de getallen van 0 tot 8, met een tussenstap van 0.2, in de cellen A1 tot A41. Activeer cel B1 en de functiewizard. Selecteer de functie EXPONDIST en typ in de invulvakjes wat in Figuur 31 te zien is.

Figuur 31 Hierdoor verschijnt de waarde “0,33333” in cel B1. Kopieer door slepen de inhoud van cel B1 over het bereik B2:B41. Maak vervolgens op dezelfde manier als bij een

35 discrete kansvariabele een grafiek aan. Het eindresultaat zal er ongeveer als volgt uitzien (zie Figuur 32).

Figuur 32 Om een grafiek van de verdelingsfunctie aan te maken gaat u op precies dezelfde manier te werk. Het enige verschil is dat u in de functie ‘EXPONDIST’ bij ‘Cumulative’ de optie “TRUE” moet ingeven. De verdelingsfunctie zou er als volgt moeten uitzien (zie Figuur 33). <

36

Figuur 33

4. Werken met een draaitabel De draaitabel (pivot table) is een handig instrument om kwalitatieve variabelen en frequenties ervan voor te stellen. Een draaitabel is daarenboven vaak een goed uitgangspunt om een figuur op te bouwen. EXCEL beschikt over een uitgebreide wizard, de draaitabelwizard, die u doorheen de constructie van een draaitabel moet leiden. Eens u een draaitabel hebt geconstrueerd, past EXCEL de tabel automatisch aan wanneer u de brongegevens zou veranderen. Een kruistabel, die nog aan bod komt in de cursus, is bijvoorbeeld op een eenvoudige manier aan te maken met behulp van de draaitabel. Overigens kan u ook draaitabellen aanmaken van gegevensreeksen afkomstig uit een ander programma dan EXCEL. Het onderstaande voorbeeld verduidelijkt de wijze van constructie. Voorbeeld Veronderstel dat we beschikken over een reeks gegevens als weergegeven in de Figuur 34.

37

Figuur 34 Om de draaitabelwizard te activeren, klikt u op ‘PivotTable and PivotChart Report…’ onder de optie ‘Data’ in de menubalk zodat het volgende venster verschijnt (zie Figuur 35).

Figuur 35

38 U ziet dat de gegevens niet noodzakelijk vanuit een EXCEL werkblad moeten komen. Omdat de gegevens van dit voorbeeld wel uit EXCEL komen, kan u gewoon de knop ‘NEXT>’ aanklikken om verder te gaan. Op het volgende venster moet u het bereik van de gegevens ingeven. Geef A1:D19 op als bereik en klik terug op de knop ‘NEXT>’. In het volgende venster klikt u op de knop ‘Layout…’ waardoor het volgende venster opent (zie Figuur 36) waarin u de draaitabel zelf kan construeren.

Figuur 36 Rechts ziet u de namen van de beschikbare gegevensreeksen. U kan elk van deze reeksen slepen naar elk van de vier plaatsen in de layout van de draaitabel. Sleep bv. de reeks ‘Jaar’ naar ‘Page’, ‘Student’ naar ‘Row’, ‘Vak’ naar ‘Column’ en ‘Punten’ naar ‘Data’. Merk op dat de reeks ‘Punten’ in de layout van de draaitabel de naam ‘Sum of Punten’ heeft gekregen. Dubbelklik vervolgens op het vakje met de naam ‘Sum of Punten’, zodat u het volgende venster te zien krijgt (zie Figuur 37).

39

Figuur 37 Aan de hand van dit venster kan u de weergave bepalen van de gegevens in het stuk ‘Data’ van de layout van de draaitabel. Aangezien er in ons voorbeeld maar één waarneming is van ‘Punten’ per drietal (jaar, student, vak), heeft het venster niet zoveel nut. Maar stel dat er per drietal (jaar, student, vak) 3 punten gegeven zouden zijn. Dan kan u bv. opgeven dat u in de draaitabel het gemiddelde van de drie punten wil zien door onder ‘Summarize by:’ de optie ‘Average’ aan te klikken. Voor ons voorbeeld voldoet echter de weergave ‘Sum’ zodat u op de knop ‘CANCEL’ mag drukken om terug te keren naar de wizard. Klik hierna op de knop ‘OK’. Klik nu op de knop ‘Options…’ waardoor het volgende venster verschijnt (zie Figuur 38).

Figuur 38

40

Aan de hand van de opties in dit venster kan u de opmaak van de draaitabel naar eigen wens schikken. Schakel bv. de optie ‘Grand totals for columns’ uit en geef de draaitabel de naam “Puntenlijst”. Klik op de knop ‘OK’. Klik tot slot op de knop ‘FINISH’. Hierdoor krijgt u de volgende draaitabel te zien (zie Figuur 39).

Figuur 39 Indien u nu klikt op het pijltje naast “(All)” in cel B1 kan u de punten van de vakken van een bepaald jaar opvragen, selecteer bv. “1997”. In de cellen onder “Grand Total” in cel E4, leest u het totaal van de punten van de betreffende student op 60. Merk op dat de vakken en de namen van de studenten alfabetisch werden geordend. Ook nu is de draaitabel nog zeer gemakkelijk aan te passen. Klik bv. op de inhoud van cel A1 en houd de knop ingedrukt terwijl u het blokje “Jaar” tot boven de namen van de studenten sleept. Laat de muisknop los en EXCEL zal de draaitabel als volgt aanpassen (zie Figuur 40).

41

Figuur 40 Sleep nu bv. het blokje “Student” van cel A4 naar cel A1. De draaitabel past zich weerom aan. Door op het pijltje naast “(All)” in cel B1 te drukken kan u bv. de punten van Elke opvragen over de verschillende jaren. Een vernieuwing in EXCEL 2000 is dat op basis van de draaitabel automatisch grafieken kunnen worden opgemaakt. In EXCEL 97 moest u zelf de grafiek construeren. Om zo’n grafiek te maken, kan u bijvoorbeeld rechts klikken ergens in de draaitabel, waardoor een snelmenu verschijnt (zie Figuur 41).

Figuur 41

42 Daar kiest u voor de optie ‘PivotChart’. Merk op dat de grafiek op een grafiekblad wordt gezet en dat de keuzevakjes de grafiek makkelijk aanpasbaar maken. Een voorbeeld is gegeven in Figuur 42. <

Figuur 42

5. Werken met een gegevenstabel Een gegevenstabel (data table) wordt vooral gebruikt om gevoeligheidsanalyses uit te voeren en om te simuleren. De manier van werken, dus de opbouw van de tabel, is in beide gevallen dezelfde. Hierna volgen twee voorbeelden. Het eerste voorbeeld behandelt een gevoeligheidsanalyse, het tweede een simulatie. Alhoewel u in het kader van deze cursus geen gevoeligheidsanalyses zult moeten doen, werd de tekst toch zo opgebouwd omdat het u makkelijker maakt de logica van de opbouw van een gegevenstabel te vatten. Voorbeeld Veronderstel dat we een kansexperiment uitvoeren waarvan de kans op succes 0.7 is en de kans op mislukking 0.3. We herhalen het kansexperiment 20 keer. De uitkomst van elk

43 kansexperiment is onafhankelijk van de uitkomst in de reeds uitgevoerde kansexperimenten. We stellen ons nu de vraag hoe groot de kans is om in die 20 herhalingen exact 10 keer een succes te bekomen. Noem X het aantal successen dat voorkomt in die 20 herhalingen. In de cursus zal u zien dat de X een kansvariabele is die een binomiale kansverdeling heeft met parameters p = 0.7 en n = 20. Voorlopig hoeft u niet precies te weten wat dit inhoudt. Neem gewoon aan dat dit klopt. Geef bijvoorbeeld in cel A1 de waarde voor p in, dus 0.7, en in cel A2 de waarde voor n, 20. De gevraagde kans is makkelijk te berekenen in EXCEL met behulp van de functie “= BINOMDIST()”. Zet de formule “= BINOMDIST(A4;A2;A1;0)” in cel A6 (zie figuur 43). ). De gevraagde kans is, zoals u ziet, 0.0308.

Figuur 43 Veronderstel nu dat we willen weten wat de kans op i successen is waarbij i ∈ {0,1,…,20}. Merk op dat de kansvariabele X enkel deze waarden kan aannemen. Eén manier om dit te doen is in cel A4 een waarde voor i in te geven en op ENTER te drukken. In cel A6 kan u dan telkens de overeenstemmende kans aflezen. Het nadeel van deze methode is dat u altijd maar één kans tegelijk te zien krijgt. U kan ook alle kansen in één keer berekenen. Dit gebeurt met een gegevenstabel. In de eerste stap moeten we de structuur van de gegevenstabel opzetten. De structuur bestaat uit twee delen: een rekenvoorbeeld en een reeks waarden van de parameter die u wil laten variëren. De parameter die wij willen laten variëren is de mogelijke waarde van de kansvariabele X. De mogelijke waarden lopen van 0 tot 20. Geef deze waarden in in de cellen C2:C22. Het rekenvoorbeeld is een verwijzing naar de inhoud van een andere cel, in ons geval cel A6. In cel A6 staat de formule die de gevraagde kans berekent op basis van de inhoud van andere cellen (A1, A2 en A4). Geef cel D1 de

44 inhoud “= A6”. De structuur van de gegevenstabel is nu klaar. Licht nu de structuur op zoals in de onderstaande figuur.

Figuur 44 Selecteer in de menubalk de optie ‘Data’ en vervolgens ‘Table…’. Het volgende venster zal op uw scherm verschijnen (zie figuur 45).

Figuur 45 Hier komt het principe van een gegevenstabel naar voor. EXCEL gaat het rekenvoorbeeld, dus de formule =BINOMDIST(A4;A2;A1;0), herberekenen voor de verschillende parameterwaarden 0,1,…,20. Voorlopig weet EXCEL echter nog niet over welke parameter het gaat: die in cel A4, die in cel A2 of die in cel A1. Dit venster dient precies om de coördinaten van de parameter aan te duiden. Onze waarden {0,1,…,20} staan in een kolom en de parameter staat in cel A4. Vandaar dat u in het invulvak naast ‘Column input cell:’ “A4” moet invullen. U zal zien dat in het invulvak “$A$4” verschijnt. EXCEL eist blijkbaar dat de celverwijzing

45 absoluut is (wat vrij logisch is maar verder geen belang heeft). Klik op de knop ‘OK’. Uw werkblad moet er nu als volgt uitzien (zie figuur 46).

Figuur 46 EXCEL heeft de formule “= BINOMDIST(A4;A2;A1;0)” dus 21 keer herberekend telkens met een andere waarde voor cel A4. Merk op dat de waarden sommeren tot 1, wat logisch is. Stel nu dat u wil zien hoe de kans op exact 10 successen afhangt van de parameters n en p. Dit komt neer op het uitvoeren van een gevoeligheidsanalyse, namelijk nagaan hoe “gevoelig” een uitkomst is voor veranderingen in de waarde van bepaalde parameters die de uitkomst bepalen. Hiertoe gaan we opnieuw een gegevenstabel opbouwen. Geef in het bereik F2:F10 verschillende waarden voor n in, bv. 10,15,…,50. In het bereik G1:K1 vult u enkele waarden voor p in, bv. 0.5,0.6,…,0.9. Vervolgens voegen we het rekenvoorbeeld toe aan de gegevenstabel. Dat dient u te doen in cel F1. Daar komt dus “= A6” in te staan. Licht vervolgens weer de gegevenstabel op zoals in de onderstaande figuur.

46

Figuur 47 Selecteer in de menubalk terug ‘Data’ en ‘Table…’ waardoor het venster van figuur 45 weer verschijnt. Deze keer hebben we twee parameters, één met waarden in de linkse kolom en één met waarden in de bovenste rij van de gegevenstabel. Vul in het invulvak naast ‘Row input cell:’ “A1” in omdat de bovenste rij van de gegevenstabel de waarden voor p bevat. Vul in het invulvak naast ‘Column input cell:’ “A2” in omdat de linkse kolom van de gegevenstabel de waarden voor n bevat. Klik op de knop ‘OK’. Daardoor herberekent EXCEL de formule in het rekenvoorbeeld met de opgegeven parameterwaarden. Het is niet mogelijk om een gegevenstabel met drie parameters op te maken. Het venster van figuur 45 had ook maar twee invulvakjes. Merk nog op dat de gegevenstabellen zich automatisch aanpassen als u iets verandert in het werkblad. Verander bijvoorbeeld de inhoud van cel A4 naar 5. Dan zal EXCEL de tweede gegevenstabel als volgt aanpassen (zie figuur 48). De eerste gegevenstabel verandert uiteraard niet. <

47

Figuur 48

Voorbeeld In dit voorbeeld gaan we illustreren hoe u met behulp van een gegevenstabel kan simuleren. Zet in de cellen A1:A20 willekeurige getallen uit het interval [0 1[. Gebruik hiervoor bv. de functie “= rand()”. Zet het gemiddelde van deze 20 willekeurige getallen in cel A22 d.m.v. de functie “= average()” en de standaarddeviatie in cel A23 d.m.v. de functie “= stdev()”. Stel nu dat u dit 20 keer wil doen, telkens met nieuw gegenereerde willekeurige getallen. U zou dit kunnen doen door nog 19 keer op F9 te duwen (waardoor het werkblad wordt herberekend). Maar dan zou u telkens de waarde voor het gemiddelde en de standaarddeviatie moeten opschrijven, wat nogal vervelend is. Bovendien kan het veel makkelijker met behulp van een gegevenstabel. Zet in de cellen D1:E1 de verwijzingen naar het rekenvoorbeeld, dus in cel D1 komt “= A22” en in cel E1 “= A23”. Het invullen van waarden voor een parameter is in dit geval niet nodig. Later zal wel duidelijk worden waarom. Licht nu de gegevenstabel op. Deze loopt over het bereik C1:E20. Ook al vult u dus geen waarden voor een parameter in, toch moet u deze cellen oplichten. Selecteer vervolgens terug de optie ‘Data’ in de menubalk en de optie ‘Table…’ waardoor het venstertje van figuur 45 terug verschijnt. Onze

48 (denkbeeldige) parameter staat in de kolom, dus we moeten iets invullen in het invulvak naast ‘Column input cell:’. U mag daar om het even welke cel invullen die nog geen inhoud heeft, bv. cel G2. Druk vervolgens op de knop ‘OK’. U zal zien dat EXCEL nieuwe waarden voor het gemiddelde en de standaarddeviatie in de gegevenstabel heeft gezet, zoals bv. in figuur 49.

Figuur 49 Wat is er gebeurd? Zoals altijd heeft EXCEL het rekenvoorbeeld herberekend met de opgegeven waarden voor de parameter. De parameter was de inhoud van cel G2. Deze had echter niks met de formule in het rekenvoorbeeld te maken en bijgevolg heeft EXCEL gewoon het rekenvoorbeeld 19 keer herberekend. Bij elke herberekening werden ander willekeurige getallen gegenereerd (alhoewel u die niet ziet afgebeeld op het scherm). Dit is precies wat u wou. Dit verklaart ook waarom in de linkse kolom van de gegevenstabel geen parameterwaarden moesten worden opgegeven. <

6. Een boxplot maken Er bestaat in EXCEL geen directe methode om een boxplot te tekenen. Via een omweg is het echter toch mogelijk om een (eenvoudige) boxplot te maken met EXCEL. De methode van

49 constructie is afkomstig van Neville toegelicht aan de hand van een voorbeeld.

Hunt.

Ze

wordt

Voorbeeld Veronderstel dat we beschikken over de volgende data (zie figuur 50).

Figuur 50 Bereken in de opgegeven volgorde voor elk van de drie gegevensreeksen het eerste kwartiel, het minimum, de mediaan, het maximum en het derde kwartiel. Hiervoor kan u de functie “=QUARTILE(;)” gebruiken waarbij het tweede argument respectievelijk 1, 0, 2, 4 en 3 moet zijn. Het eerste argument is telkens de gegevensreeks. Zet deze getallen op het werkblad als in figuur 51.

Figuur 51

50

Licht nu de cellen E1:H6 op en activeer de grafiekenwizard. Kies als grafiektype ‘Line’ met subtype ‘Line with markers displayed at each data value.’ Klik op de knop ‘Next’. Selecteer het rondje naast ‘Rows’ (standaard staat het rondje naast ‘Columns’ geselecteerd). Klik op de knop ‘Next’. Verwijder de rasterlijnen en druk op de knop ‘Finish’. Daardoor krijgt u de volgende grafiek (zie figuur 52).

Figuur 52 Selecteer nu om beurten elke reeks van gegevenspunten, klik rechts en kies voor ‘Format Data Series…’ in het snelmenu dat verschijnt. Duid op het tabblad ‘Patterns’ onder ‘Line’ het rondje naast ‘None’ aan. Als u dit voor elke reeks van gegevenspunten hebt gedaan, selecteert u terug een gegevensreeks (om het even welke). Klik terug rechts en kies voor ‘Format Data Series…’ in het snelmenu. Kruis op het tabblad ‘Options’ de vakjes naast ‘High-low lines’ en ‘Up-down bars’ aan. En druk op de knop ‘OK’. Kleur de achtergrond van de figuur nog wit (rechts klikken op de achtergrond, ‘Format Plot Area…’ selecteren en onder ‘Area’ voor ‘None’ kiezen) zodat het eindresultaat eruit ziet als in de volgende figuur. <

51

Figuur 53

Augustus 2001