7. Examenuitslagen beheren

In dit hoofdstuk zal je een aantal werkbladen opstellen waarin je examenresultaten worden verwerkt. Deze worden bewaard in het werkboek Examens.xls.

De volgende zaken worden behandeld:

• De gegevens aanbrengen.
• Formules met voorwaarden aanbrengen.
• De vormgeving afwerken.
• De lijst beheren met database-achtige commando's.
• Totalen en subtotalen berekenen.
• Statistische informatie aanbrengen.
• Een histogram opstellen.
• Een pivot table opstellen, beheren en gebruiken.

Vraag het bestand Examen.xls op in P:\Excel 97 en bewaar het op je I: schijf.

7.1 Het werkblad opstellen en afwerken (*)

Het werkblad Studentenlijst bevat een titel, een lijst met studenten, met vakken en met punten op 20. Elk vak krijgt een aantal studiepunten, waarmee je de punten moet vermenigvuldigen vóór ze worden opgeteld. Het resultaat is het totaal. Ook het percentage wordt berekend. Als aanwijzing voor een eventuele deliberatie zal je het aantal punten onder 10 berekenen. De studenten worden in drie klassen opgedeeld:

figuur 1: Het werkblad 'Studentenlijst 1'

• Geslaagd: De student heeft ofwel minstens 50% en geen tekorten, ofwel minstens 55% en maximaal drie punten tekort.
• Deliberatie: De student heeft minstens 50% maar voldoet niet aan de bovenstaande voorwaarden.
• Niet geslaagd: De student heeft minder dan 50%.

De naam van deze klasse wordt als voorstel tot besluit vermeld. Alle onvoldoendes worden in een speciale kleur (rood) aangeduid. Je bekomt dan een werkblad met de gedaante van het werkblad Studentenlijst 1 (figuur 1). Hierin zijn kolommen Q:AC met tussenresultaten verborgen.

Het is de bedoeling de gegevens (punten) niet rechtstreeks in het werkblad in te vullen, maar via speciale dialoogvensters. Je zal dit uiteindelijk realiseren aan de hand van de commando's uit het Data menu. Bij het opbouwen van het werkblad zal je de gegevens nochtans rechtstreeks invullen om de berekeningen te kunnen verifiëren.

7.1.1 De tekst en gegevens invullen (*)

Voer de gegevens in tot aan de kolom van de percentages. Het is bij tabellen echter vervelend dat je geen overzicht kan bewaren omdat niet alles tezamen op het werkblad gaat. Daarom is het aan te raden reeds in een eerste fase een aantal speciale maatregelen te nemen. Deze zijn:

• Versmal de kolommen die punten bevatten. Om de tekst (vaknamen) volledig leesbaar te houden oriënteer je ze verticaal, zodat je met smalle kolommen kan werken. Doe dit zodra je de hoofden van alle kolommen in rij 2 hebt aangebracht.
• Pas de getalformaten (behalve de kleur) zo snel mogelijk aan.
• Eventueel kan je andere formatteerbewerkingen (o.a. bij de titel en het jaartal) ook reeds onmiddellijk uitvoeren. Het vooraf aanbrengen van de kaders levert echter vaak problemen op bij het kopiëren, doordat lijnen ongewenst wel of niet worden meegekopieerd.
• Deze bedenkingen leiden tot de volgende acties:

A1:	naam, Italic knop
B1:	Examenuitslagen, Bold knop, Font Size knop, 12
B1:N1:	Merge and Center knop
P1:	jaar:
Q1:	1997-1998
P1:Q1:	Italic knop
A3:	vak, Format, Cells…, Border tabblad, diagonaal knop (linksboven-rechtsonder), Alignment tabblad, Orientation: 90°, Horizontal: Right, Vertical: Center
A4:	studiepunten, Align Right knop
A5:A20:	Crasson, … , Wilmots (namen van de studenten)
A:A:	dubbelklik rechterrand kolomhoofd
B3:N3	Sociologie, …, Duits (namen van de vakken)
O3:	Totaal
P3:	Percentage
B3:P3:	Format, Cells…, Alignment tabblad, Orientation: 90°, Horizontal: Center, Vertical: Bottom
B:N:	sleep rechterrand kolomhoofd naar links tot de breedte 3 bedraagt
O:P:	sleep rechterrand kolomhoofd naar links tot de breedte 5 bedraagt
B4:N4:	3, …, 4 (studiepunten), Center knop
O4:	=SUM(B4:N4) (AutoSum knop), Center knop
P4:	100
B5:N20:	vul de punten van de studenten per kolom (per vak) in

7.1.2 De formules voor de totalen en percentages aanbrengen (*)

Het totaal van een student wordt berekend als de som van de producten van de punten en de studiepunten uit dezelfde kolom. Excel heeft hiervoor een speciale functie, SUMPRODUCT, die zich in de Math & Trig categorie van het Paste Function dialoogvenster bevindt. Breng deze aan voor de eerste student en kopieer ze daarna met de fill handle voor de overige studenten. Bereken het percentage als een getal tussen 0 en 100. Je verkrijgt de formule door het totaal te delen door het product van het totale aantal studiepunten (60, in O4) en te vermenigvuldigen met 5. Het maximaal te behalen totaal aantal punten bedraagt immers 20 maal het totale aantal studiepunten.

Bij het gebruik van de SUMPRODUCT functie worden als argumenten opgegeven:

SUMPRODUCT(array1,array2)

De arrays (vectoren) duiden op ranges met één rij of één kolom en allemaal evenveel cellen. Het zijn de rijen met de gewichten en de punten. De range van de studiepunten staat in rij 4 voor elke student; je moet die dus door een absolute referentie voorstellen. De range van de punten staat in dezelfde rij als het totaal voor elke student; je moet die dus door een relatieve referentie voorstellen.

In de formule voor het percentage heb je eveneens een relatieve en een absolute referentie.

De uit te voeren acties zijn:

Opmerkingen:

• Je moet de formules zo algemeen mogelijk houden. Dit betekent dat je in de formule in cel P5, niet 60 mag opgeven (totaal aantal studiepunten), maar de referentie $O$4, omdat dit getal zou kunnen veranderen wanneer de studiepunten veranderen. Daarentegen mag je wel 5 in de formule intypen, omdat 100/20 (max. percentage gedeeld door maximaal aantal te behalen punten) steeds 5 is!
• De percentages in P5:P20 worden als gehele getallen voorgesteld. Het volstaat niet ze afgerond te tonen met de knoppen uit de Formatting toolbar, omdat verdere berekeningen over slagen of niet slagen op basis van de getoonde afgeronde getallen moeten gebeuren. Gebruik daarom de functie ROUND(x,0), die een niet geheel getal x afrondt tot op 0 cijfers na de decimale punt.
• Je had de getallen met het % teken kunnen voorstellen, door de formule in P5:P20 de vorm =ROUND(O5/($O$4*20),2) te geven en het resultaat als een percentage te formatteren. De afronding gebeurt in dit geval tot op twee cijfers na de decimale punt. Pas deze werkwijze niet toe omdat ze bij het opstellen van pivot tables, later in dit hoofdstuk, problemen met de vormgeving kan veroorzaken.

7.1.3 Het venster opsplitsen in panes (*)

De tabel is nu zo groot geworden, dat hij niet meer op het scherm past. Het is meestal aangewezen dat de hoofden van de rijen (kolom A) en kolommen (rijen 3 en 4) steeds in beeld blijven. Daarom zal je het venster in deelvensters of panes verdelen. Hiervoor gebruik je de commando's van het Window menu.

Het Window menu bevat de volgende commando's:

Je hebt reeds gezien dat je meerdere vensters van een zelfde werkboek kan openen en schikken met de commando's

Window, New Window en Window, Arrange....

De combinatie van deze commando's vormt de enige oplossing wanneer je dezelfde cellen door twee vensters wilt bekijken.

Wanneer je echter ver uit elkaar gelegen cellen van een zelfde werkblad samen in beeld wil hebben op een gesynchroniseerde wijze, heb je hiervoor twee andere commando's.

Wanneer je een cel in een venster activeert, kan je met het commando

Window, Split

het werkblad in vier deelvensters splitsen, panes genaamd, links van en boven de geactiveerde cel. De scheiding wordt aangegeven door de horizontale en/of verticale split bar in de vorm van een dubbele lijn. Indien de geactiveerde cel zich vóór het opsplitsen in de bovenste zichtbare rij of de meest linkse zichtbare kolom bevindt wordt het venster enkel in twee verticale of horizontale panes gesplitst. De cell pointer bevindt zich in de pane rechts onderaan. Wanneer je de cell pointer verticaal beweegt zullen op een zeker ogenblik de rijen in zijn pane en in de pane links ervan beginnen te scrollen, terwijl de rijen in de andere twee panes niet scrollen. Een analoge zaak doet zich voor wanneer je horizontaal scrolt en wanneer je de cell pointer in één van de andere panes beweegt. Op deze wijze kan je ver uit elkaar liggende cellen samen in beeld brengen, maar horizontaal naast elkaar liggende cellen zullen in alle panes steeds hetzelfde rijnummer hebben, en verticaal naast elkaar liggende cellen zullen in alle panes steeds hetzelfde kolomnummer hebben. De panes zullen steeds synchroon scrollen.

Opmerkingen:

• Boven de verticale schuifbalk en rechts van de horizontale schuifbalk bevindt zich een dik grijs streepje. Door dit naar beneden of naar rechts te slepen verplaats je de split bar en splits je het venster dus eveneens in twee of in vier delen.
• Op een analoge wijze kan je de ruimte van de sheet tabs vergroten of verkleinen ten koste van de ruimte van de horizontale schuifbalk door het streepje dat beide scheidt te verslepen.
• Bij het scrollen kan het voorkomen dat sommige cellen en ook de cell pointer in meerdere panes zichtbaar zijn. In dit geval zal de cell pointer een witte achtergrond vertonen in de geactiveerde pane en een donkere achtergrond in de overige panes (deze cel lijkt en is ook geselecteerd).
• Je kan het opsplitsen in deelvensters opheffen met het commando

Window, Remove Split

of door de split bar(s) te dubbelklikken.

Met het commando

kan je het werkblad eveneens links van en boven de geactiveerde cel in twee of vier panes opsplitsen, waarbij het echter niet mogelijk is verticaal te scrollen in de bovenste panes of horizontaal te scrollen in de linkse panes. Enkel in de pane rechtsonderaan kan je nog volledig vrijuit scrollen. Nu is de scheidingslijn tussen de panes een horizontale en/of verticale lijn. Het is nu niet mogelijk dat cellen of de cell pointer in meer dan één pane voorkomen. Op deze wijze kan je ervoor zorgen dat de bovenste rijen en de meest linkse kolommen vast in beeld blijven. Dit is vaak handig, omdat ze meestal titels voor kolommen of rijen bevatten, die de betekenis verklaren.

Opmerkingen:

• Je kan het bevriezen van deelvensters opheffen met het commando

  Window, Unfreeze Panes

• Wanneer panes bevroren zijn zal het commando Window, Split de panes eerst ontvriezen en dan splitsen.
• Wanneer panes gesplitst zijn zal het commando Window, Freeze Panes de panes op de plaats van de splitsing bevriezen, maar de gesplitste toestand blijft behouden bij het commando Window, Unfreeze Panes.

Activeer de eerste cel met punten en bevries de rijen er boven en de kolom links ervan:

7.1.4 De logische functies (*)

Je moet nog twee kolommen opstellen met daarin de som van de tekorten en een besluit: geslaagd, deliberatie of niet geslaagd. Deze kolommen hebben echter de referenties AD en AE. In de 13 tussenliggende verborgen kolommen wordt voor elk van de 13 vakken het aantal punten onder 10 berekend. Zo zal voor de eerste student in cel Q5 het tekort voor Sociologie staan, in cel R5 het tekort voor Financiële Algebra enz. Je zal de formule voor elke student en elk vak kunnen kopiëren, wanneer je haar met relatieve referenties opstelt. In kolom AD worden dan de 13 tekorten opgeteld. In de AE kolom wordt aan de hand van het percentage en het totale tekort beslist of de student wordt voorgedragen om te slagen.

Je mag hier echter niet zomaar de formule =10-punten invullen, omdat dan quotaties boven 10 tot een negatief resultaat leiden. Je moet hier twee verschillende formules gebruiken:

• Wanneer de punten lager zijn dan 10 (zoals de 9 voor Management in G5): 10-punten (10-9 = 1).
• Wanneer de punten hoger zijn dan 10 (zoals de 13 voor Sociologie in B5): 0 (er is geen tekort).

Hiervoor heb je de IF functie. Deze bevindt zich in de Logical Function categorie van het Paste Function dialoogvenster en heeft drie argumenten:

• Het eerste argument logical_test geeft een voorwaarde aan, waarvan het antwoord een logische waarde moet hebben: TRUE (waar, ja) of FALSE (niet waar, neen).
• Het tweede argument value_if_true is de in te vullen waarde of formule indien de voorwaarde is vervuld (TRUE).
• Het derde argument value_if_false is de in te vullen waarde of formule indien de voorwaarde niet is vervuld (FALSE).

Twee van de vijf overige logische functies vullen een logische waarde in en hebben geen argumenten:

De overige drie maken het mogelijk de logische bewerkingen uit de Booleaanse Algebra uit te voeren:

Je kan logische functies als argument van een andere logische functie gebruiken. Op deze wijze kan je complexe voorwaarden opstellen.

7.1.5 De formules opstellen (*)

Gebruik de logische functies bij het opstellen van de formules voor de tekorten en het voorstel tot slagen. Geef ook aan elke kolom een kolomhoofd in rij 3:

De formules met logische functies vragen enige verklaring:

• In cel L5 (tekort Crasson voor Sociologie) is de voorwaarde "Zijn de punten voor Sociologie (B5) lager dan 10 (<10)?". Indien de voorwaarde is vervuld worden de punten afgetrokken van 10 (10-B4). Indien de voorwaarde niet is vervuld wordt 0 ingevuld. Door de relatieve referentie en de lay-out worden na het kopiëren steeds de punten voor het juiste vak en de juiste student getest. Je had hier ook de formule =MAX(10-B4,0) kunnen gebruiken. Deze is eenvoudiger, maar minder duidelijk.
• In cel AE5 (In welke klasse valt Crasson?) is de voorwaarde samengesteld. Je hebt drie mogelijke resultaten, geslaagd, deliberatie en niet geslaagd. In een IF functie kan je echter slechts twee resultaten vermelden. De oplossing voor dit probleem bestaat in het nesten van een tweede IF functie binnen de eerste. Dit kan op meerdere wijzen gebeuren. In het voorbeeld wordt eerst getest of de student minstens 50 percent haalde of niet (P5>=50 in de eerste IF functie). Indien dit niet het geval is, valt de student in de klasse niet geslaagd, aangeduid door het derde argument van de IF functie.

Wanneer de student minstens 50 percent behaalde zijn er twee mogelijkheden, die leiden tot de klasse geslaagd of deliberatie. In de IF functie (IF(…, "geslaagd", "deliberatie") ), die het tweede argument vormt van de eerste IF functie wordt het onderscheid gemaakt. Het criterium (dat nog steeds complex is en OR en AND functies bevat) vormt het eerste argument, de twee klassen vormen het tweede en derde argument.

Een student met minstens 50% is geslaagd (2de argument van de tweede IF functie) wanneer hij aan minstens één van beide volgende voorwaarden voldoet (aangeduid door OR(…, …) ): hij heeft geen tekorten (AD5=0) of hij heeft minstens 55% en hoogstens drie tekorten ( AND(P5>=55, AD5<=3) ). In het andere geval valt hij in de klasse deliberatie (3de argument van de tweede IF functie).

figuur 2: De afgewerkte IF function - dialoogvenster en formulebalk

Opmerkingen:

• De achtereenvolgens uit te voeren acties bij het opstellen van de formule in AE5 zijn:
  1. Klik de Paste Function knop.
  2. Kies de IF functie in de Logical categorie (en klik OK). Het dialoogvenster van de IF functie wordt geopend.
  3. Selecteer het eerste argument Logical_test, klik in cel P5 (percentage) en typ in >=50.
  4. Selecteer het tweede argument Value_if_true en klik IF in de name list. In de formulebalk verschijnt een tweede IF met groene haakjes. Het dialoogvenster van deze tweede IF functie wordt geopend.
  5. Selecteer Logical_test (bij de 2de IF functie), klik de name list open, klik More Functions… en kies de OR functie in de Logical categorie (en klik OK). In de formulebalk verschijnt een OR met purperen haakjes. Het dialoogvenster van deze OR functie wordt geopend.
  6. Selecteer het eerste argument Logical1, klik in cel AD5 (som tekorten) en typ in =0.
  7. Selecteer het tweede argument Logical2, klik de name list open, klik More Functions… en kies de AND functie in de Logical categorie (en klik OK). In de formulebalk verschijnt een OR met bruine haakjes. Het dialoogvenster van deze AND functie wordt geopend.
  8. Selecteer het eerste argument Logical1, klik in cel P5 (percentage) en typ in >=55.
  9. Selecteer het tweede argument Logical2, klik in cel AD5 (som tekorten) en typ in <=0.
  10. Klik in de formulebalk op de OR functie. Het dialoogvenster van de OR functie wordt opnieuw getoond. Verifieer dat beide argumenten zijn ingevuld.
  11. Klik in de formulebalk op de tweede IF functie. Het dialoogvenster van de tweede IF functie wordt opnieuw getoond. Verifieer dat het eerste argument is ingevuld.
  12. Selecteer het tweede argument Value_if_true en typ "geslaagd" in.
  13. Selecteer het derde argument Value_if_false en typ "deliberatie" in.
  14. Klik in de formulebalk op de eerste IF functie. Het dialoogvenster van de eerste IF functie wordt opnieuw getoond. Verifieer dat de eerste twee argumenten zijn ingevuld.
  15. Selecteer het derde argument Value_if_false en typ "niet geslaagd" in (figuur 2).
  16. Beëindig de formule met OK.
• De teksten geslaagd, deliberatie en niet geslaagd moeten tussen " " staan, omdat ze anders als niet-bestaande namen worden opgevat en in #NAME? foutmelding resulteren.
• Het is interessant voor de leesbaarheid van de IF functie om ze in een aantal regels op te splitsen. Dit doe je door achteraf de functie in de formulebalk te editeren en op de gewenste positie op alt+enter te drukken.
• Een alternatieve formule voor cel AE5, met hetzelfde resultaat, is:

  AE5:	=IF(OR(AND(P5>=50,AD5=0),AND(P5>=55,AD5<=3)), "geslaagd" , IF(P5>=50,"deliberatie","niet geslaagd")

  Ga dit na !

7.1.6 De kolommen met tekorten verbergen (*)

De verdere afwerking bestaat uit drie delen: Verberg de overbodige kolommen, beklemtoon de onvoldoendes door ze in een rode kleur te plaatsen, brengen kaders en achtergrondkleuren aan en herschik de titels.

De kolommen met de tekorten voor een vak worden beter niet getoond. Verberg ze dus:

Opmerking:

• Je kan eventueel ook de hoogte van rij 3 verminderen. Deze wordt bepaald door haar cel met de langste tekst (tekort Finaciële Algebra), maar deze is nu verborgen:

  3:3:	sleep de onderrand van het rijhoofd omhoog tot de langste zichtbare tekst (in C3) nog juist past

7.1.7 Tekorten in rood aangeven (*)

Je kan nu onvoldoendes in het rood aangeven op drie plaatsen: individuele punten onder 10, percentages onder 50 en een som van tekorten groter dan 3. Plaats daarenboven de klasse in een kleur: groen voor geslaagd, oranje voor deliberatie en rood voor niet geslaagd.

Excel heeft twee mogelijkheden om dit te realiseren:

• Door een nieuw numeriek formaat te maken met daarin een voorwaarde en de naam van een kleur, die je beide tussen [ ] zet. Om de punten <10 in het rood te zetten gebruik je het commando

  Format, Cells…, Number tabblad, Category: Custom, [<10] [Red] 0

  In de twee andere gevallen ga je op een analoge wijze te werk. Meerdere voorwaardelijk formaten scheid je door een ;. Je hebt wel slechts zestien kleuren (geen oranje!).

• Een meer algemene en meer gebruiksvriendelijke methode werkt met de voorwaardelijke formaten. Je kan daar afhankelijk van een voorwaarde niet alleen een kleur geven aan een tekst, maar ook kaders en achtergrondpatronen aanbrengen. Je maakt hierbij gebruik van het commando

  Format, Conditional Formatting…

Breng met deze tweede methode de gepaste kleuren aan:

7.1.8 Kaders en achtergrondkleuren aanbrengen (*)

Houd dezelfde stijl aan als bij de vorige werkbladen: breng een halfdikke lijn rond het werkblad en scheid de onderdelen die van betekenis verschillen door dunne lijnen en verwijder daarna de rasters:

A1:AE21:	Borders knop: knop 12 (halfdikke lijn rond de buitenkant)
A3:AE21:	Format, Cells…, Border tabblad, halfdikke lijn, Outline knop, dunne lijn, klik verticale lijn in het midden
A4:AE4:	Borders knop: knop 7 (dunne lijn boven en onder de range)
	Tools, Options..., View tabblad, verwijder de aankruising van Gridlines

Opmerking:

• Wanneer je later rijen (studenten) of kolommen (vakken) zal toevoegen kunnen er problemen optreden met het ten onrechte aanbrengen of weglaten van lijnen. Wanneer je de lijnen plaatst in de rijen en kolommen die geen studenten of vakken bevatten zijn de te verwachten problemen meestal het geringst.

Vervolgens maak je het tabelhoofd licht blauw en de gegevens licht geel. Je kan dit doen met de Color lijst:

De gele kleur is echter nog aan de donkere kant. Het Patterns tabblad van het Format dialoogvenster bevat een lichter geel. Breng dit als volgt aan:

Op deze wijze zal de leesbaarheid van de tekst niet worden gehinderd door de achtergrondkleur.

Bekijk vervolgens het werkblad in Full Screen mode en bewaar het werkboek Examens.xls.

Opmerkingen:

• Wanneer je de Fill Color knop gebruikt heb je de keuze uit 40 kleuren, wanneer je het Patterns tabblad uit het Format Cells dialoogvenster gebruikt heb je de keuze uit 56 kleuren. Aan de oorspronkelijke 40 zijn dan de 16 kleuren voor charts toegevoegd. Sommige kleuren komen echter dubbel voor, zodat je in werkelijkheid slechts over 46 verschillende kleuren beschikt.
• Je kan extra kleuren bekomen door in het Patterns tabblad van het Format Cells dialoogvenster voor een Pattern te kiezen. Je kiest dan een primaire kleur uit de 56 Colors. Daarna klik je op Patterns. Nu klapt een lijst open waarin je een arceringspatroon kan kiezen uit 18 mogelijkheden en een secundaire kleur uit opnieuw 56 mogelijkheden. Je moet deze handeling wel tweemaal uitvoeren, een eerste maal voor de arcering, een tweede maal voor de secundiare kleur. Je kan een primaire kleur "lichter" maken, door een fijn arceringspatroon met als secundaire kleur wit te kiezen.
• Je kan ook kleuren wijzigen in het Color tabblad van het Options dialoogvenster (zie supra).

7.2 De studentenlijst beheren (*)

Je kan studenten en punten toevoegen, wijzigen en verwijderen met de gewone spreadsheet commando's. Excel heeft echter de mogelijkheid om dit op een meer gebruiksvriendelijke manier te doen met de zogenaamde database commando's uit het Data menu.

Het Data menu bevat de volgende commando's:

Het is belangrijk vooraf enkele begrippen te definiëren:

• Een region is een rechthoekig gebied dat afgebakend wordt door lege rijen en kolommen. Wanneer je een cel hebt geactiveerd, kan je de region waartoe de cel behoort op eenvoudige wijze selecteren met het commando

  Edit, Go To..., Special..., Current region

• Een list (lijst) is een rechthoekige tabel bestaande uit een bovenste rij met unieke tekstwaarden en daaronder rijen met gegevens. De rijen (behalve de eerste rij) worden records genoemd. De kolommen worden meestal velden genoemd. De kolomhoofden in de eerste rij zijn de veldnamen. De veldnamen moeten allemaal verschillen. Velden in records kunnen zowel constante als via formules berekende waarden bevatten. Je kan de volgende lijsten onderscheiden:

  Een data list is een lijst met de oorspronkelijke gegevens. Vóór Excel 5 kreeg hij standaard de naam Database.

  Een criteria list is een lijst met, in de plaats van de gegevensrecords, voorwaarden (criteria) waaraan records moeten voldoen om geselecteerd te worden. Vóór Excel 5 kreeg hij standaard de naam Criteria.

  Een extract list is een lijst met de aan de hand van de voorwaarden uit de criteria list geselecteerde records uit de data list. Vóór Excel 5 kreeg hij standaard de naam Extract.

Opmerkingen:

• Spreadsheets kunnen in het algemeen enkel werken met zogenaamde flat file databases. Dit zijn databases die slechts uit één tabel bestaan. Omdat de in vroegere versies van Excel gebruikte benaming Database hierbij niet echt gerechtvaardigd is, werd zulk een tabel in Excel 5 een list (lijst) genoemd, wat inderdaad een meer correcte benaming is. In deze tekst wordt in het vervolg de term lijst gebruikt.
• Excel 97 biedt nochtans zeer uitgebreide mogelijkheden om lijsten te raadplegen en te manipuleren. Het maakt daarbij gebruik van een variant van de query taal (opvraagtaal) Query By Example (QBE), die ook populair is bij echte relationele database management systemen (RDBMS). Daarnaast biedt Excel de mogelijkheid om gegevens uit een meer-tabellen-database op te vragen via de add-in MS Query, waarop in deze tekst echter niet verder wordt ingegaan.
• Vanaf Excel 5 zijn de namen Database, Criteria en Extract niet langer verplicht, maar het gebruik ervan biedt een beperkt aantal voordelen.

figuur 3: Het Go To Special dialoogvenster
• In het Go To Special dialoogvenster (figuur 3), dat verschijnt wanneer je in het Go To dialoogvenster de Special... knop indrukt, kan je de volgende dingen selecteren:

  Alle cellen met commentaar (Comments).

  Alle cellen met waarden maar zonder formules (Constants).

  Alle cellen met formules (Formulas), waarbij je het type van de resultaten kan aangeven (Numbers, Text, Logicals, Errors).

  Alle lege cellen (Blanks).

  De range waarin de geactiveerde cel staat (Current region). Hierbij wordt de selectie vanaf de geactiveerde cel in de vier richtingen uitgebreid tot vóór de eerste lege rij of kolom.

  De array waarin de geactiveerde cel staat (Current array).

  Alle objecten (Objects), zoals charts, figuren, tekstkaders, knoppen enz...

  De cellen binnen de geselecteerde range waar de formule verschilt van die in de geactiveerde cel in de rij (Row differences) of in de kolom (Column differences).

  De cellen waarheen in de geactiveerde cel gerefereerd wordt (Precedents) of waaruit naar de geactiveerde cel gerefereerd wordt (Dependents). Je hebt de mogelijkheid dit voor één niveau te doen (Direct only) of voor alle niveaus (All levels).

  De cel in de rechterbenedenhoek van het gebruikte deel van het werkblad (Last cell). Dit komt overeen met het indrukken van de toetsen CTRL+END.

  Enkel de zichtbare cellen (Visisble cells only). Verborgen cellen worden niet geselecteerd.

  Alle cellen met voorwaardelijke formaten (Conditional formats) op twee niveaus (All, Same).

  Cellen met regels voor gegevensinvoer (Data validation) op twee niveaus (All, Same).

Het selecteren van alle formules of alle constanten is vooral interessant wanneer je deze wilt beveiligen tegen wijzigen of bekijken. Het selecteren van precedent en dependent cellen is interessant om visueel de afhankelijkheden binnen het werkblad te zien en fouten op te sporen.

7.2.1 De data list bepalen (*)

Vóór Excel 5 kon je per werkblad één data list, criteria list en extract list gebruiken en moesten deze de namen Database, Criteria en Extract krijgen om er de database commando's op te kunnen toepassen. Vanaf Excel 5 heb je drie mogelijkheden:

• Wanneer je de lijst als een region afbakent, kan je er de database commando's op toepassen wanneer je een willekeurige cel erin geactiveerd hebt. Het is dan niet nodig de lijst een naam te geven.
• Je kan de lijst een naam geven. Wanneer je hem dan selecteert kan je de database commando's op de lijst toepassen. Het geven van de naam en het selecteren verloopt het vlotst via de name list links in de formulebalk. Het voordeel van deze methode is dat je de lijst niet door lege rijen en kolommen moet begrenzen. Wanneer je met het commando Data, Form... records onderaan de data list toevoegt, wordt de range met de door jou gegeven naam echter niet mee aangepast. Je zal daarom, telkens je records toevoegt, de data list moeten herdefiniëren (opnieuw zijn naam geven).
• Je kan de data list de naam Database geven, zoals in Excel 4. Wanneer je hem dan selecteert kan je de database commando's op de lijst toepassen. Wanneer je met het commando Data, Form... records onderaan de lijst toevoegt wordt de range met naam Database automatisch aangepast, zodat de nieuwe records erin opgenomen zijn.

Uit het voorgaande blijkt dat je een lijst die je wilt uitbreiden best geen naam geeft of enkel de naam Database. Een lijst waaraan je geen records wilt toevoegen kan je gerust een zelfgekozen naam geven.

De tabel die je tot nu hebt opgesteld heeft wel min of meer de gewenste gedaante van een data list, maar kan niet als zodanig worden gebruikt omdat tussen de veldnamen in rij 3 en de records vanaf rij 5 nog rij 4 met studiepunten staat.

Je kan dit probleem oplossen door twee extra rijen toe te voegen tussen de rijen met vaknamen en studiepunten (3:4) enerzijds en de rijen met records (5:20) anderzijds. Rijen 5:20 zullen dan naar onder opschuiven.

• Laat rij 5 leeg. Plaats in rij 6 formules die naar de corresponderende cellen in rij 3 verwijzen. Enkel in A6 plaats je student i.p.v. vak. De nieuwe rij 6 zal hierdoor de veldnamen bevatten, terwijl de records beginnen vanaf de nieuwe rij 7. Rijen 3 en 4 behoren technisch gezien niet tot de data list.
• Een probleem is dat de namen van de vakken tweemaal vermeld worden, in rij 3 en rij 6. Je kan nu rij 6 verbergen, maar dit zal voor problemen zorgen bij het AutoFilter commando (zie infra). Geef daarom aan deze cellen dezelfde Font Color als hun Fill Color, zodat ze onzichtbaar worden. Daarmee los je ook het probleem van het lelijke formaat van deze cellen op.
• Rij 5 zorgt ervoor dat de data list los staat van de twee bovenste rijen met vaknamen en gewichten, waardoor hij een region is.

De hierbij uitgevoerde acties zijn:

5:6:	Insert, Rows
A6:	vak, Align Right knop
B6:	=B3, sleep fill handle naar B6:AE6
B6:AE6:	Font Color knop, Light Turquoise
(bv.) G12:	Edit, Go To..., Special..., Current region

Opmerking:

• Je kan de hierboven beschreven methode kan steeds toepassen wanneer er meerdere rijen vooraan in de tabel staan die geen records bevatten.

7.2.2 Records toevoegen, invullen, wijzigen en verwijderen (*)

Je kan nu allerlei bewerkingen op records uitvoeren. De eenvoudigste methode bestaat er in een cel in de lijst te selecteren en daarna het commando

te kiezen. Nu verschijnt het Data Form dialoogvenster (figuur 4), dat als titel de naam van het werkblad Studentenlijst heeft.

figuur 4: Het Data Form dialoogvenster als invulscherm en als criteria scherm

Het eerste record (Crasson) wordt getoond. Links in het dialoogvenster staan onder elkaar de veldnamen en daarachter de waarden. Onafhankelijke gegevens staan in een invulvak. Je kan ze wijzigen. Afhankelijke gegevens (formules) worden als gewone tekst getoond. Je kan ze niet veranderen (tenzij je de gegevens waarvan ze afhangen verandert). Velden in verborgen kolommen worden niet getoond. In het midden staat een schuifbalk, waarmee je door de records kan bladeren. Rechts bovenaan staat het nummer van het getoonde record en het totale aantal records (1 of 16). Daaronder staan zeven knoppen waarmee je de belangrijkste manipulaties met de gegevens kan uitvoeren:

• Ga naar een ander record met de schuifbalk. Je kan met de schuifpijlen, de enter toets of de pijl omlaag toets naar het volgende record gaan en met de shift+enter toets of de pijl omhoog toets naar het vorige record gaan. Wanneer je de schuifbalk of een pijl toets gebruikt blijf je in hetzelfde veld. Zo kan je zeer vlot een kolom invullen. Met de enter en shift+enter toetsen wordt echter steeds het eerste veld van een ander record geselecteerd. Met de pgupen pgdn toetsen kan je tien records tegelijk verspringen.

• Ga naar een ander veld in het getoonde record door dat veld te klikken. Met de tab en shift+tab toetsen ga je naar het volgende of vorige veld binnen het getoonde record. Op deze wijze kan je de data list record per record (rij per rij) invullen. Hierbij worden afhankelijke velden niet geselecteerd maar de verschillende knoppen wel.

• Voeg achteraan een nieuw record toe met de New knop. Bovenaan rechts verschijnt het bericht New Record in plaats van het recordnummer. Het record wordt pas toegevoegd wanneer minstens één veld is ingevuld.

• Verwijder het getoonde record met de Delete knop. De Office Assistant toont de waarschuwing Displayed record will be permanently deleted, waarna je de actie kan bevestigen of annuleren.

• Annuleer wijzigingen in het getoonde record met de Restore knop. Je kan wijzigingen ongedaan maken zolang je geen ander record selecteert.

• Ga naar de Criteria vorm van het Data Form dialoogvenster (figuur 4). Die wordt gekenmerkt doordat op de plaats van het recordnummer het woord Criteria staat. Geef voorwaarden op in de verschillende velden (onafhankelijke en afhankelijke) en selecteer daarna met de Find Prev of Find Next knop het eerstvolgende record in achterwaartse of voorwaartse dat aan alle ingevulde criteria tegelijk voldoet. De ingevulde waarden kunnen ongelijkheden zijn zoals >=69, waarmee alle records met 69 procent of meer voor het betreffende vak aan de criteria voldoen. Met de Clear knop maak je alle ingevulde Criteria leeg. Met de Form knop keer je terug naar de normale vorm van het Data Form dialoogvenster zonder een ander record te selecteren.

• Zoek het eerstvolgende record dat aan de laatst gedefinieerde Criteria voldoet in achterwaartse of voorwaartse zin met de Find Prev of Find Next knop. Indien geen Criteria zijn gedefinieerd voldoen alle records aan de Criteria en wordt gewoon naar het vorige of volgende record gegaan. Indien er verder geen records meer zijn die aan alle criteria voldoen wordt dit via een geluidssignaal gemeld.

• Sluit het Data Form dialoogvenster en keer terug naar het werkblad met de Close knop.

Opmerkingen:

• Om aan de hand van meer complexe criteria te zoeken worden de commando's uit het Data, Filter submenu gebruikt (zie infra).
• Bij het toevoegen van records schuiven de onderstaande cellen niet op. Het formaat van het laatste record wordt aan het toegevoegde record gegeven. Dit heeft als gevolg dat de dikke lijn onderaan de lijst verdwijnt van zodra één record wordt toegevoegd.
• Je kan records aan de lijst toevoegen zolang eronder nog lege ruimte is. Wanneer echter één van de cellen waar het volgende record moet komen reeds een waarde bevat, toont de Office Assistant bij het invullen van het nieuwe record de boodschap Cannot extend list or database, en moet je teruggaan naar een vorig record. De oplossing bestaat er in extra tussenliggende rijen toe te voegen of geen gegevens onder de lijst aan te brengen tenzij je zeker bent dat hij niet zover zal worden uitgebreid.
• Indien een lijst met naam Database is gedefinieerd, zal het commando Data, Form... steeds worden toegepast op deze range, ook indien ze niet geselecteerd is! Indien records worden toegevoegd, zal de range met naam Database automatisch worden aangepast.
• Indien geen lijst met naam Database is gedefinieerd, maar een lijst range met een andere naam is geselecteerd, zal het commando Data, Form... worden toegepast op deze geselecteerde range. De toegevoegde records zullen echter niet tot de range met deze naam behoren. Bij een volgende gelegenheid zullen ze dus niet door het commando Data, Form... worden behandeld en in de weg staan om de data list nog verder uit te breiden. Dit zijn ongewenste resultaten. Daarom verdient het geen aanbeveling lijsten een andere naam dan Database te geven!
• Indien geen lijst met naam Database is gedefinieerd, en een enkele cel geselecteerd is, zal het commando Data, Form... worden toegepast op de door lege rijen en kolommen afgebakende current region, die de geselecteerde cel bevat. Dit garandeert dat minstens één record kan worden toegevoegd, omdat de rij onder de current region per definitie leeg is.

7.2.3 Records selecteren met het AutoFilter (*)

Met het commando Data, Form... kan je telkens op zoek gaan naar één record dat aan zekere criteria voldoet. Het is echter niet mogelijk met dit commando alle records die aan zekere criteria voldoen samen in beeld te brengen. Om dit te realiseren gebruik je de commando's uit het Data, Filter submenu.

Er zijn twee methodes, die beiden een variant op de opvraagtaal QBE (Query By Example) gebruiken:

• De AutoFilter methode maakt gebruik van de rij met veldnamen in de data list. In de cellen van de veldnamen kan je waarden kiezen in uitklapbare lijsten. De rijen van de records die met deze waarden corresponderen zullen worden getoond en de overige rijen van de data list zullen worden verborgen.
• De Advanced Filter methode biedt meer uitgebreide mogelijkheden maar vraagt dan ook meer werk. Er is een afzonderlijke criteria list. Ofwel worden de records die aan deze criteria voldoen getoond in de data list en worden de andere records verborgen, ofwel worden de records die aan deze criteria voldoen naar een derde range, de extract list, gekopieerd. Indien deze extract list veldnamen bevat worden enkel de kolommen met de in de extract list voorkomende veldnamen gekopieerd.

Eerst volgt een illustratie van de AutoFilter methode.

Bij de AutoFilter methode werk je met rij 6 met de veldnamen. Deze mag niet verborgen zijn, maar de Font Color en de Fill Color mogen wel gelijk zijn (zoals hier).

Toon de AutoFilter lijsten met het commando

Nu verschijnt rechts in elke cel van de eerste rij van de lijst een pijl waarmee je een lijst kan laten openklappen. In deze lijst kan je waarden kiezen. Enkel de records met deze waarden worden daarna nog getoond. Kies je bijvoorbeeld in de meest linkse AutoFilter lijst met de studentennamen de naam Van der Elst, dan zal daarna enkel nog het record van Van der Elst getoond worden. Het rijnummer van dit getoonde record en de pijl van de lijst waarin de waarde werd gekozen, worden in een blauwe kleur getoond (figuur 5).

figuur 5: De data list met Autofilter - één voorwaarde

Toon opnieuw alle records met het commando

Je kan ook een meer ingewikkelde selectie maken. Selecteer eerst de studenten met 55 procent of meer. Klap daartoe de AutoFilter lijst van het percentage in cel P5 open. Hierin heb je de volgende mogelijkheden:

figuur 6: Het Custom Autofilter dialoogvenster

Om een voorwaarde van de vorm >=55 op te stellen kies je (Custom...). Je kan in het Custom AutoFilter dialoogvenster (figuur 6) twee voorwaarden opgeven en deze combineren met And (ze moeten beide voldaan zijn) of Or (minstens één van de voorwaarden moet voldaan zijn). Elke voorwaarde begint met een tekst, die het vergelijkingscriterium aanduidt. De lijst met waarden bevat de in dit veld voorkomende waarden, maar je kan ook zelf een waarde opgeven. Hierbij kan je (vooral bij tekstvelden) de klassieke wildcards ? en * gebruiken om één enkel willekeurig teken of een willekeurig aantal willekeurige tekens aan te duiden.

Vul de voorwaarde is greater then or equal to 55 in. Er worden nu elf records getoond.

Je kan nu voor andere velden extra voorwaarden opgeven. Aan alle voorwaarden van de verschillende AutoFilter lijsten zal tegelijk voldaan moeten zijn.

Geef als tweede voorwaarde op dat de student geslaagd moet zijn (veld klasse). Er worden nu nog slechts negen records getoond (figuur 7), van de geslaagde studenten met 55 procent of meer.

figuur 7: De data list met Autofilter - meerdere voorwaarden

Verwijder het AutoFilter met

Data, Filter, AutoFilter

De markering naast het AutoFilter commando verdwijnt nu in het Data, Filter submenu en alle records worden opnieuw getoond.

Merk op dat de buitenrand rond de data list onderaan is verdwenen. Corrigeer dit door de rand opnieuw aan te brengen:

Opmerkingen:

• Het AutoFilter commando is een schakelcommando. Wanneer het geactiveerd is wordt dit aangeduid door een aankruising naast het commando.
• Wanneer een AutoFilter in gebruik is, lijken de commando's Data, Filter, AutoFilter en Data, Filter, Show All hetzelfde effect te hebben: ze tonen opnieuw alle record. Met Show All, blijven de pijlen echter bestaan, en kan je nog steeds nieuwe AutoFilter criteria opgeven. Met AutoFilter, verwijder je het AutoFilter en verdwijnen ook de pijlen. Je kan pas opnieuw AutoFilter criteria opgeven nadat je het AutoFilter commando opnieuw hebt uitgevoerd.
• Er is slechts één AutoFilter per werkblad mogelijk.
• Het is van belang welke region geselecteerd is wanneer je het AutoFilter activeert. Wanneer je bijvoorbeeld cel B3 geactiveerd hebt, zullen de AutoFilter lijsten in rij 3 staan en zal je één record met de studiepunten hebben.
• Door het verticaal plaatsen van de vaknamen in smalle kolommen, zullen deze steeds misvormd worden wanneer je de AutoFilter lijsten toont. De hier toegepaste methode om de vaknamen in een extra rij te kopiëren en daar te verbergen is de aangewezen oplossing. Daarenboven kan je nu de veldnaam student juist boven de tabel tonen in A6 en het woord vak in A3.
• Omdat het AutoFilter voortdurend andere rijen toont en verbergt, is het aan te bevelen de rijen naast een data list waarop een AutoFilter wordt uitgevoerd leeg te laten.
• Bij het afdrukken op papier worden de pijltjes van het AutoFilter niet mee afgedrukt.

7.2.4 Records selecteren met het Advanced Filter

Met de AutoFilter methode kan je records selecteren waarvan verschillende velden tegelijk aan één of meerdere voorwaarden moeten voldoen. De voorwaarden worden dus aan een And combinatie onderworpen. Het is echter niet mogelijk records te selecteren waarvan verschillende velden aan voorwaarden moeten voldoen, wanneer deze voorwaarden aan een Or combinatie worden onderworpen. Zo kan je niet vragen de studenten te selecteren die onvoldoende halen op Wiskunde of Economie.

Voor een dergelijke vraag wordt de Advanced Filter methode gebruikt. Hierbij heb je één of twee extra lijsten nodig.

In een eerste lijst, die in vroegere Excel versies de naam Criteria kreeg, kan je de voorwaarden aanbrengen. Deze criteria list bevat een eerste rij met veldnamen. Deze veldnamen moeten exact gelijk zijn aan die in de data list. Daaronder bevinden zich een aantal "records" waarin de voorwaarden voor de verschillende velden onder hun veldnaam worden vermeld. Voorwaarden die tegelijk vervuld moeten zijn worden in dezelfde rij vermeld (And combinatie), voorwaarden die als alternatieven vervuld moeten zijn worden in onder elkaar staande rijen vermeld (Or combinatie). Deze wijze van het aangeven van selectie criteria in een lijst structuur wordt Query By Example genoemd.

Je zou kunnen stellen dat je bij de AutoFilter methode voor elk veld één criteria in de AutoFilter lijst kan vastleggen en dat je bij de Advanced Filter methode voor elk veld meerdere criteria in de criteria list kan vastleggen.

Breng de criteria list rechts van de tabel aan. Om te vermijden dat rijen bij het scrollen verborgen geraken moet je het bevriezen van de vensters vooraf uitzetten. Kopieer daarna de lijst met veldnamen in de tweede rij naar rechts. Kopieer enkel de getoonde velden. De vormgeving van deze cellen is grotendeels reeds zoals gewenst. Je moet enkel de eerste veldnaam student i.p.v. vak aanbrengen en de kolombreedtes aanpassen:

Plaats daarna de voorwaarden, die aan de Or combinatie moeten voldoen, bij de juiste veldnamen in opeenvolgende records van de criteria list:

figuur 8: De criteria list

Om de criteria list beter aan te duiden kan je zijn records op een licht groene achtergrond plaatsen (de hoofden staan al op een licht blauwe achtergrond) en de nodige kaders aanbrengen (figuur 8):

Klik vervolgens in de tabel en kies het commando

figuur 9: Het Advanced Filter dialoogvenster - Filter the list, in-place

In het Advanced Filter dialoogvenster (figuur 9) kan je aangeven welke de List range en Criteria range zijn. Wanneer in het werkblad reeds lijsten met de namen Database en Criteria gedefinieerd zijn, worden de ranges die hiermee overeenstemmen voorgesteld. In het andere geval wordt de region van de geactiveerde cel als List range voorgesteld en moet je de Criteria range zelf aanduiden. Je kan ervoor kiezen de records die aan de voorwaarden voldoen, in de oorspronkelijke tabel te tonen en de rijen van de overige records te verbergen (Filter the list, in-place; zoals bij de AutoFilter methode), of naar een andere plaats de kopiëren (Copy to another location), die je dan eveneens moet opgeven (Copy to). Verder kan je ervoor kiezen "dubbele" records slechts eenmaal te tonen (Unique records only).

Geef als Criteria range: $AG$3:$AX$5 op. Het resultaat (twee studenten) wordt getoond in de List range (figuur 10).

figuur 10: De data list met Advanced Filter, in-place

Opmerkingen:

• Je moet er op toezien dat de criteria list geen lege records bevat. Een leeg record zou immers betekenen dat records waaraan geen enkele voorwaarde is opgelegd eveneens geselecteerd moeten worden. Zodoende zou dus elk record automatisch geselecteerd worden.
• Het is niet nodig dat de criteria list alle velden bevat. In feite zijn enkel de velden met voorwaarden nodig (Wiskunde, Economie). De andere velden werden hier toegevoegd, omdat je dan de voorwaarden kan wijzigen zonder de lay-out van de criteria list te moeten wijzigen.
• De optie Filter the list, in-place zal juist zoals de AutoFilter methode rijen verbergen. Je ziet er best op toe dat alle rijen uit de criteria list getoond blijven. Zolang die slechts twee of drie records bevat is hier geen gevaar. Indien die meer records bevat is het wellicht aangewezen de criteria list niet rechts van maar onder de data list of in een ander werkblad aan te brengen !

Wanneer je enkel geïnteresseerd bent in de studenten uit de klasse deliberatie met een onvoldoende voor Kostencalculatie of Boekhouden moet je de vorige criteria verwijderen, <10 invullen in opeenvolgende records voor deze Kostencalculatie en Boekhouden, en daarenboven bij beide records deliberatie in het veld klasse invullen:

Dit betekent dat records geselecteerd moeten worden van studenten die

• voor deliberatie in aanmerking komen en minder dan 10 hebben voor Kostencalculatie
• of voor deliberatie in aanmerking komen en minder dan 10 hebben voor Boekhouden (figuur 11).

Er worden nu drie records getoond.

Opmerkingen:

• Wanneer je de drie voorwaarden in dezelfde rij had geplaatst zou de voorwaarde luiden: de voor deliberatie in aanmerking komende studenten met een onvoldoende voor zowel Kostencalculatie als Boekhouden. Hier heb je daarentegen de voor deliberatie in aanmerking komende studenten met een onvoldoende voor Kostencalculatie of Boekhouden (of allebei).

figuur 11: De criteria list met de extra voorwaarden
• Je kan in de criteria list extra kolommen toevoegen met een nieuwe naam, waarbij je de definitie in een formule in het record vermeldt. Op deze wijze had je de criteria list tot één record kunnen beperken door een nieuwe veldnaam in AY3 te vermelden en in cel AY4 als voorwaarde in te vullen: =OR(Kostencalculatie<10,Boekhouden<10) naast de enige resterende voorwaarde deliberatie in AX4. De criteria list zou dan beperkt worden tot AG3:AY4. Nochtans leiden dergelijke methodes bij meer complexe voorwaarden gemakkelijk tot erg onoverzichtelijke resultaten.
• De volgorde van de velden in de criteria list is totaal onbelangrijk.
• Het is niet nodig tussen twee opeenvolgende filteringen het commando Data, Filter, Show All uit te voeren. Elk nieuw Advanced Filter wordt op alle records uitgevoerd, niet enkel op de op dat ogenblik getoonde records.
• Het wijzigen van de voorwaarde in de criteria list zal niet als gevolg hebben dat er andere records worden geselecteerd. Daarvoor moet je het commando Data, Filter, Advanced Filter... opnieuw uitvoeren.

Je zal bij een volgend Advanced Filter de oorspronkelijke tabel onaangeroerd laten en de resultaten op een andere plaats in het werkblad plaatsen. Daarvoor moet je de gewenste veldnamen uit de data list of criteria list nogmaals kopiëren naar een extract list. Je kan vooraf een aantal records voor de bestemming vastleggen. In dit geval zal maximaal dit aantal records worden gekopieerd. Wanneer je echter vooraf geen records vastlegt zullen juist voldoende records worden gekopieerd. Voer dus de volgende acties uit:

figuur 12: Het Advanced filter dialoogvenster - Copy to another location

Kies nu in het Advanced Filter dialoogvenster (figuur 12) om de resultaten te kopiëren (Copy to another location) en geef de extract list op (Copy to: $AZ$3:$BQ$3)

Uiteraard bevat de gegenereerde extract list in AZ3:BQ6 drie records, evenveel als er bij list in-place zouden zijn (figuur 13).

Opmerkingen:

• Bij het Advanced Filter zullen de records steeds geselecteerd worden uit de hele data list, of er nu records verborgen zijn of niet. Nochtans is het voor de duidelijkheid aan te raden vooraf het commando Data, Filter, Show All uit te voeren, vooral omdat sommige gekopieerde records eventueel in verborgen rijen kunnen terecht komen. Hier is dit juist niet het geval.

figuur 13: De extract list met gekopieerde resultaten
• Bij het kopiëren naar de extract list worden formules door hun getalwaarden vervangen (Paste Values), zodat wijzigingen in de data list geen invloed hebben op de extract list.
• Door de resultaten te kopiëren kan je het aantal velden in de extract list beperken. Je bent namelijk volledig vrij in het kiezen van de velden in de extract list.
• De volgorde van de velden in de extract list mag totaal verschillen van de volgorde in de data list, maar bepaalt uiteraard wel de volgorde waarin de kolommen getoond zullen worden.
• Wanneer je slechts een klein aantal velden in de extract list wilt opnemen wordt de kans groter dat het resultaat records bevat waarvan alle getoonde velden dezelfde waarde hebben. Door Unique records only aan te kruisen in het Advanced Filter dialoogvenster kan je de overtollige records elimineren.
• Je kan als extract list een lijst met veldnamen en plaats voor een beperkt aantal records opgeven. In dit geval zullen hoogstens evenveel records worden gekopieerd als er plaats is voorzien.
• Je kan eveneens als extract list enkel een lijst met veldnamen opgeven (zoals hier). In dit geval zal de extract list bij het kopiëren zover als nodig worden uitgebreid. De uitbreiding wordt eventueel gestopt wanneer niet-lege cellen worden tegengekomen.
• Je kan tenslotte als extract list één enkele (lege) cel opgeven. In dit geval zal deze cel de linkerbovenhoek van de extract list vormen en zullen alle veldnamen uit de data list in de extract list worden gekopieerd. Hij zal zo ver als nodig worden uitgebreid. De uitbreiding wordt eventueel gestopt wanneer niet-lege cellen worden tegengekomen.

7.2.5 Records sorteren (*)

Heel vaak wens je records in een tabel of data list in een bepaalde volgorde te zien. De records in de data list staan in volgorde van hun naam. Je zal de records op het percentage of op de klasse (geslaagd / deliberatie / niet geslaagd) ordenen.

Vóór het sorteren kan je de te sorteren range selecteren. Indien je echter slechts één cel selecteert wordt de selectie uitgebreid tot de region rond de cel. Excel zal zelf trachten te bepalen of de eerste rij in de range als veldnamen kan worden geïnterpreteerd of niet. Meestal hangt dit af van het wel of niet numeriek zijn van de gegevens in deze rij.

• Wanneer je slechts één sorteercriterium wenst te hanteren, klik je een cel in de betreffende kolom, gevolgd door de Sort Ascending of Sort Descending knop. De region wordt vervolgens gesorteerd volgens de waarden in deze kolom.
• Wanneer je echter op meer kolommen wilt sorteren gebruik je het commando

Data, Sort...

figuur 14: Het Sort dialoogvenster

In het Sort dialoogvenster (figuur 14) kan je aangeven op welk veld of welke kolom eerst moet worden gesorteerd (Sort by). Indien gewenst kan je één of twee extra velden of kolommen kiezen waarop moet worden gesorteerd (Then by), wanneer de vorige waarden gelijk zijn. Indien de cellen in de eerste rij als veldnamen worden herkend, worden zij in de Sort by of Then by lijsten opgenomen, anders wordt de kolomletter aangegeven. Je kan voor elk sorteerveld de sorteervolgorde opgeven (Ascending: A-Z en 0-9 of Descending: Z-A en 9-0). Je kan het voorstel van Excel om de eerste rij als veldnamen te beschouwen aanvaarden (My list has header row) of verwerpen (My list has no header row).

Sorteer als voorbeeld op klasse en vervolgens op dalend percentage en naam van de student.

Verklein daarna met de Zoom knop de weergave tot 75% om alle gesorteerde records samen in beeld te krijgen.

figuur 15: Het Sort Options dialoogvenster

Vervelend is dat de drie klassen alfabetisch staan (deliberatie, geslaagd, niet geslaagd), waar de volgorde geslaagd, deliberatie, niet geslaagd beter is. Je kan dit realiseren door in het Custom Lists tabblad van het Options dialoogvenster deze lijst toe te voegen, en bij het sorteren in het Sort dialoogvenster de Options… knop te kiezen. Je kan dan in het Sort Options dialoogvenster (figuur 15) een alternatieve sorteervolgorde voor de eerste sleutel uit de Custom lists kiezen:

Tools, Options…, Custom Lists tabblad, Custom lists: NEW LIST, List entries: geslaagd, deliberatie, niet geslaagd, Add Data, Sort…, (laat vorige instellingen staan), Options…, First key sort order: geslaagd, deliberatie, niet geslaagd.

Je krijgt dan de studenten gesorteerd in de gewenste volgorde (figuur 16).

figuur 16: De gesorteerde data list

Opmerkingen:

• Wanneer het Sort dialoogvenster wordt getoond zijn alleen de te sorteren rijen geselecteerd. Omdat rij 6 als Header Row wordt beschouwd en dus niet mee moet worden gesorteerd, wordt deze ook niet mee geselecteerd.
• Na het sorteren staat Mpenza L. (61%, 729 punten) vóór Nilis (61%, 731 punten). Dit komt omdat effectief werd afgerond met de ROUND functie. Wanneer deze functie niet gebruikt was in de formules voor de percentages, zou de volgorde anders zijn, omdat dan bij het sorteren zou worden uitgegaan van de niet-afgeronde percentages.
• Ook de velden in verborgen kolommen komen in aanmerking om op te sorteren in het Sort dialoogvenster.
• Wanneer je de veldnamen in rij 6 met het ;;; formaat had verborgen, in plaats van Font Color en Fill Color gelijk te maken, zouden kolomaanduidingen (Column AE), (Column P) in plaats van veldnamen worden getoond in het Sort dialoogvenster.
• Indien de eerste rij niet als veldnamen zou zijn beschouwd, zouden eveneens kolomnamen worden getoond en zou deze dus mee zijn gesorteerd.
• Bij het sorteren staan getallen vóór tekst en lege cellen achteraan. Voor het sorteren van tekstwaarden gebruikt Windows niet het ASCII alfabet, maar het sterk gelijkende ANSI alfabet.
• In het Sort Options dialoogvenster kan je verder nog aangeven of je een onderscheid wenst te maken tussen hoofd- en kleine letters (Case sensitive – standaard: geen onderscheid) en of je rijen (Sort top to bottom – standaard) of kolommen willen sorteren (Sort left to right).
• Je kan enkel een alternatieve sorteerwijze opleggen voor het eerste veld waarop je wil sorteren (First key sort order).
• Standaard worden rijen gesorteerd. In databases is dit ook steeds de bedoeling. Je kan echter ook kolommen (bijvoorbeeld de vakken) alfabetisch sorteren. Dit levert geen problemen op met de hoofden en resultaten in de verborgen kolommen van de tekorten. Deze worden namelijk niet mee van plaats verwisseld. Omdat de formules in deze kolommen gelijk zijn treden er geen fouten op in de formules of de resultaten.
• Het heeft geen zin om sorteervelden of -kolommen op te geven die formules bevatten. Na het sorteren wordt immers de formule opnieuw geëvalueerd en de oorspronkelijke volgorde hersteld. Een oplossing bestaat er in formules door hun waarden te vervangen met de Copy knop en het commando

  Edit, Paste Special..., Values

  en daarna op de waarden te sorteren.

7.3 Subtotalen aanbrengen

In de gesorteerde lijst zijn de studenten nu gegroepeerd: de geslaagde studenten staan bovenaan, de voor deliberatie in aanmerking komende in het midden, en de niet geslaagde onderaan. Je zal nu voor elke van deze drie groepen en voor alle studenten samen totalen berekenen. De totalen voor de groepen worden subtotals genoemd, de algemene totalen worden grand totals genoemd. Je zal het gemiddelde en de standaardafwijking voor elk vak en voor het percentage berekenen, en het aantal studenten tellen.

Je zal de bewerkingen met subtotalen uitvoeren in het werkblad Studentenlijst 2, dat een kopie is van het op klasse, dalend percentage en naam gesorteerde werkblad Studentenlijst. Je kan het werkblad kopiëren door zijn tab te ctrl+slepen en het gekopieerde werkblad Studentenlijst (2) te dubbelklikken en hernoemen in Studentenlijst 2.

Selecteer een cel in de lijst en kies daarna het commando

figuur 17: Het Subtotal dialoogvenster

In het Subtotal dialoogvenster (figuur 17) kan je de volgende zaken invullen:

• Op welk veld worden records gegroepeerd (At each change in – standaard het eerste veld).
• Welk totaal wordt berekend (Use function – standaard: het vorige berekende subtotaal; bij een eerste uitvoering Sum voor numerieke en Count voor niet-numerieke velden).
• Voor welke velden worden totalen berekend (Add subtotal to – standaard het groeperingsveld).
• Vervang de reeds berekende totalen (Replace current subtotals – standaard) of voeg de nieuwe totalen toe aan de reeds bestaande.
• Neem voor elke groep een nieuw blad (Page break between groups).
• Plaats de totalen onder of boven de gegevensrijen (Summary below data – standaard: onder).
• Verwijder alle totalen (Remove All).

Juist zoals bij het sorteren worden de veldnamen of (indien deze verborgen zijn) de kolomletters in de lijsten vermeld.

Je kan per keer slechts één soort totaal berekenen. Vul de eerste maal in::

Opmerking:

• Je moet er op toezien in de Add subtotal to lijst de standaard aankruising van het groeperingsveld klasse te verwijderen!

figuur 18: Het werkblad 'Studentenlijst 2' met de data list met subtotalen voor het gemiddelde>

Het resultaat (figuur 18) vergt enige verklaring:

• De toegevoegde rijen 16, 22 en 25 bevatten het aantal (Count) als subtotalen voor de groep van de geslaagde studenten (rij 16), de deliberatie studenten (rij 22) en de niet geslaagde studenten (rij 25).
• De toegevoegde rij 26 bevat het totaal aantal (Grand Count) studenten.
• De aantallen zijn berekend voor de opgegeven kolom (P).
• In kolom AE (klasse), waarop gegroepeerd werd, staan in de toegevoegde rijen de gecentreerde teksten geslaagd Count (AE16), deliberatie Count (AE22), niet geslaagd Count (AE25) en Grand Count (AE26).
• In de linkermarge zijn outline symbolen aangebracht. Er zijn drie outline niveaus:

Breng vervolgens ook de standaardafwijkingen en aantallen aan door nog tweemaal het commando

uit te voeren.

Om de standaardafwijkingen te laten berekenen vul je in:

Om de gemiddelden te laten berekenen vul je in:

Telkens werden er drie rijen toegevoegd, twee voor de subtotals en een voor het Grand Total (figuur 19). In de corresponderende velden staan de berekende totalen. In kolom AE (klasse) staat de verklaring voor de toegevoegde rij.

figuur 19: Het werkblad 'Studentenlijst 2' met de data list met alle subtotalen

Opmerkingen:

• Nieuwe subtotalen worden boven de oude aangebracht!. De gemiddelden staan dus vóór de standaardafwijkingen en de standaardafwijkingen vóór de aantallen!
• De subtotalen worden in hun cel aangebracht met de functie SUBTOTAL(number,range), waarbij number aangeeft welk totaal wordt berekend (volgnummer) en range over welke cellen dit gebeurt. Lege cellen en cellen in deze range die zelf reeds subtotalen bevatten worden niet in rekening gebracht.
• Er werd gebruik gemaakt van de standaardafwijking van de populatie Stdevp en niet de standaardafwijking van de steekproef Stdev, omdat je gegevens hebt over alle studenten. Zie voor meer uitleg de cursus Statistiek.
• Voor het tellen van het aantal studenten kan je in principe elk veld aangeven. Hier werd niet voor het veld student gekozen, omdat het de bedoeling is de verklaring daarheen te verplaatsen en deze cellen dus leeg moeten blijven. Het veld klasse werd ook niet genomen omdat het Grand Total voor het aantal dan een fout oplevert (22 i.p.v. 16, bepaalde toegevoegde records met tekst in deze kolom worden meegeteld!).
• Je kan voor het tonen en verbergen van niveaus in de outline gebruik maken van knoppen uit de PivotTable toolbar (zie infra) en vier commando's uit het Data, Group and Outline submenu:

Hide Detail	verberg details die bij de geselecteerde cellen horen.
Show Detail	Toon alle details die bij de geselecteerde cellen horen.
Group	Groepeer de geselecteerde cellen in een outline.
Ungroup	Verwijder de groepering van de geselecteerde cellen in een outline.

• Het is aan te bevelen de data list te sorteren op het veld waarop wordt gegroepeerd vóór subtotalen worden aangebracht. Wanneer je later wil sorteren op de grootte van de subtotalen, moet je alle details verbergen (klik op de – knoppen) en vervolgens op de nog steeds getoonde subtotalen sorteren. De verborgen rijen worden dan mee verplaatst met het bijbehorende subtotaal.
• Je kan alle rijen van een bepaald niveau tonen en verbergen door op het knopje met het niveaunummer te klikken, dat zich boven de outline symbolen bevindt.
• De betekenis van de + en – knoppen is vergelijkbaar met de + en – tekens in de boomstructuur van Explorer bij Windows 95.

De vormgeving is uiteraard niet zoals gewenst. Wijzig die daarom als volgt:

Je verkrijgt uiteindelijk een afgewerkte lay-out (figuur 20), waarbij je de records van de geslaagde studenten hebt verborgen door de corresponderende – knop te klikken. Deze is daardoor door een + knop vervangen, die je moet klikken om deze records opnieuw te tonen.

figuur 20: Het werkblad 'Studentenlijst 2' met de afgewerkte data list met subtotalen

Opmerkingen:

• Wanneer je nieuwe formaten aan cellen geeft zullen die normaal de oudere automatisch vervangen. Dit is blijkbaar niet het geval bij voorwaardelijke formaten, die je expliciet moet verwijderen in de toegevoegde rijen om de nieuwe kleuren overal te kunnen opleggen!
• Lijsten met totalen en subtotalen bevatten vaak zeer veel gegevens. Om toch een overzicht te bewaren kan je de weergave verkleinen tot bijvoorbeeld 75% met de Zoom knop of sommige details verbergen met de knoppen in het outline gebied.
• Je kan alle rijen met totalen en subtotalen verwijderen met het commando

  Data, Subtotals..., Remove All

7.4 Statistische informatie aanbrengen (*)

Je kan subtotalen gebruiken om statistische informatie aan te brengen. Wanneer je echter alleen algemene totalen wenst, kan je deze methode niet toepassen.

Er zijn echter wel twee andere methodes:

• Met statistische functies.
• Met de Descriptive Statistics add-in.

Hierna worden beide methoden achtereenvolgens geïllustreerd. Dit doe je in het werkblad Studentenlijst 3, dat je creëert als een kopie van Studentenlijst.

7.4.1 Statistische functies gebruiken (*)

Je zal onder de tabel, waarvan het laatste record zich bevindt in rij 22, de volgende gegevens berekenen voor elk vak en voor het percentage. De gebruikte functies bevinden zich (op één na) in de Statistical categorie van het Paste Function dialoogvenster.

• Het gemiddelde voor een vak van de resultaten van alle studenten. Hiertoe gebruik je de reeds vroeger geziene functie AVERAGE(range).
• De mediaan, dit is het resultaat van de middelste student wanneer de punten geordend zijn. Je gebruikt de functie MEDIAN(range). Excel vereist niet dat de punten geordend zijn om deze mediaan te berekenen.
• De standaardafwijking ten opzichte van het gemiddelde. Bij normaal verdeelde punten moet ongeveer 68% van de punten binnen deze standaardafwijking van het gemiddelde liggen. Er zijn twee functies, STDEV(range) en STDEVP(range). STDEV berekent de standaardafwijking voor een steekproef; STDEVP berekent de standaardafwijking voor de ganse populatie. Het resultaat van STDEVP is steeds kleiner dan bij STDEV maar bij een groot aantal gegevens vallen beide standaardafwijkingen nagenoeg samen. Gebruik hier de functie STDEVP.
• Het hoogste aantal punten voor het vak. Dit verkrijg je met de functie MAX(range).
• Het laagste aantal punten voor het vak. Dit verkrijg je met de functie MIN(range).
• Het aantal studenten. Dit verkrijg je met de functie COUNT(range).
• Het aantal studenten met minder dan 10 punten. Hierbij gebruik je de functie COUNTIF(range,criteria). Deze functie bevindt zich in, in tegenstelling tot de overige, in de Math & Trig categorie. Ze heeft twee argumenten: de range waarvan de waarden worden geteld en de criteria die een voorwaarde bevat. Deze voorwaarde moet tussen " " staan.
• Het aantal studenten met minder dan 12 punten. Ook hiervoor gebruik je de COUNTIF(range,criteria) functie.

Ga als volgt te werk:

1. Breng in A24:A31 het rijhoofd van de statistische gegevens aan.
2. Breng in B24:B31 de formules aan en kopieer ze naar B24:AE31.
3. Pas de formaten aan en veeg zinloze resultaten uit.

Dit leidt tot de volgende formules (figuur 21):

figuur 21: Het werkblad 'Studentenlijst 3' met statistische informatie

Opmerkingen:

• De commando's om de vormgeving aan te passen werden niet vermeld. Experimenteer hiermee zelf.
• Kopieer de minder zinvolle resultaten niet (AD24:AE31) of schrap ze (O30:O31).
• Doordat je de statistische informatie vlak onder de data list hebt geplaatst, kan je deze laatste slechts uitbreiden zolang er lege rijen tussen de data list en de statistische gegevens staan. Excel weigert namelijk cellen met gegevens te overschrijven wanneer je het commando Data, Form... gebruikt. Indien nodig zal je met Insert, Rows rijen moeten toevoegen of de rijen met statistische informatie verder naar onder moeten verslepen.
• Wanneer je onderaan de data list records (rijen) toevoegt worden deze niet meegerekend in de statistische functies omdat de ranges van deze functies beperkt blijven tot rij 20. Indien nodig moet je deze ranges manueel wijzigen. Het is daarom aangewezen de statistische formules pas aan te brengen op het ogenblik dat je geen studenten meer moet toevoegen. Deze problemen treden niet op wanneer je nieuwe records in het midden van de data list toevoegt, maar dan kan je het commando Data, Form... hiervoor niet gebruiken.
• De functies COUNT, AVERAGE, STDEV, STDEVP, VAR, VARP, MIN en MAX hebben een variant COUNTA, AVERAGEA, STDEVA, STDEVPA, VARA, VARPA, MINA en MAXA, waarbij de bewerking uitgevoerd wordt over de niet-lege cellen van de range. Er is echter geen functie MEDIANA.
• Naast de functie COUNTIF(range,criteria), die het aantal cellen in de range telt, waarvan de waarden aan de opgegeven criteria voldoen, is er ook een gelijkaardige functie SUMIF(range,criteria,sumrange), die de som van de waarden in de sumrange berekent waarvan de waarden van de corresponderende cellen in de range aan de opgegeven criteria voldoen. Range en sumrange moeten evenveel cellen bevatten.
• Er zijn ook statistische database functies. Deze bevinden zich in de Database categorie. Hun naam is die van een gewone statistische functie voorafgegaan door de letter D. De statistische database functies zijn: DAVERAGE, DCOUNT, DCOUNTA, DGET (selecteer één record), DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR en DVARP. Ze hebben drie argumenten (database, field en criteria) met dezelfde betekenis. Database geeft een data list aan (met als eerste rij veldnamen) waarin de gegevens staan die verrekend moeten worden. Field geeft aan op welk veld de berekeningen moeten geschieden. Als het ontbreekt (vooral bij de DCOUNT functies) worden volledige records verrekend. Criteria geeft een criteria list aan, waarin zich voorwaarden bevinden. Deze functies maken het mogelijk meer ingewikkelde voorwaarden op te stellen dan de COUNTIF en SUMIF functies.

7.4.2 De Descriptive Analysis Tool (*)

figuur 22: Het Data Analysis dialoogvenster

Je kan eveneens statistische informatie aanbrengen met één van de add-in macro's uit de Analysis Tools.

Gebruik de gegevens uit het werkblad Studentenlijst 3 en plaats de resultaten in een nieuw werkblad Statistieken.

Gebruik het commando

Tools, Data Analysis...

en kies in het Data Analysis dialoogvenster (figuur 22) de Descriptive Statistics.

figuur 23: Het Descriptive Statistics dialoogvenster

Vervolgens verschijnt het Descriptive Statistics dialoogvenster (figuur 23). Selecteer de percentages in P6:P22 als Input Range (te onderzoeken gegevens). Omdat je slechts statistieken van één kolom wenst worden de gegevens uiteraard per kolom gegroepeerd (Grouped By Columns). Duid verder aan dat je de tekst Percentage in de eerste rij van de input range (P6, met een onzichtbare titel percentage!) als kolomhoofd wenst (Labels in First Row).

Geef aan dat je de resultaten in een nieuw werkblad met naam Statistieken wilt plaatsen (Output Options: New Worksheet Ply), dat je overzichtstatistieken wenst (Summary Statistics), dat je de 3de grootste en kleinste waarde (Kth Largest, Kth Smallest) wél en een betrouwbaarheidsinterval (Confidence Level for Mean) niet in het overzicht op wil nemen.

Het resultaat zal links bovenaan in het nieuwe werkblad Statistieken worden geplaatst. Dit werkblad zelf wordt links van het huidige (geactiveerde) werkblad Studentenlijst 3 in het werkboek tussengevoegd. Pas de kolombreedte en de getalformaten aan en vergelijk de resultaten (figuur 24) met die van de statistische functies in P24:P31.

figuur 24: Het werkblad 'Statistieken' met de Descriptive Statistics

Opmerkingen:

• Het berekenen van de resultaten gebeurt met een macro en neemt enige tijd in beslag. In de statusbalk verschijnt zolang het bericht: Calculating Descriptive Statistics....
• In cel P6 staat de veldnaam Percentage. Deze is onzichtbaar omdat de Font Color en Fill Color gelijk zijn. Wanneer je hier geen gebruik van wilt maken, selecteer je als Input Range slechts P7:P22 en kruis je Labels in First Row niet aan. De tekst in A1 zal dan in het resultaat niet verschijnen.
• De resultaten worden als getallen en niet als formules voorgesteld. Dit heeft een aantal gevolgen. Zo zal je, na elke wijziging aan een gegeven dat een percentage beïnvloedt, de Descriptive Analysis Tool opnieuw moeten uitvoeren om de output up to date te houden. Hierbij wordt gevraagd of de bestaande Output Range mag worden overschreven.
• Wanneer Precision as displayed in het Calculation tabblad van het Options dialoogvenster is aangekruist, zal het gebruik van de formatteringsknoppen als gevolg hebben dat de getallen gewijzigd worden en er heel gemakkelijk nauwkeurigheid verloren gaat. Daarom is het aangewezen Precision as displayed niet aan te kruisen en de percentages in de P kolom af te ronden met de functie ROUND(x,0).
• De formules en functies waarmee deze resultaten kunnen worden bekomen zijn:

  A kolom	B kolom	gelijkwaardige formule
  Mean (gemiddelde waarde)	61.94	=AVERAGE(P7:P22)
  Standard Error (standaard fout op gemiddelde waarde)	2.37	=STDEV(P7:P22)/SQRT(COUNT(P7:P22))
  Median (mediaan)	61.00	=MEDIAN(P7:P22)
  Mode (modus)	70.00	=MODE(P7:P22)
  Standard Deviation (standaardafwijking van de steekproef)	9.48	=STDEV(P7:P22)
  Sample Variance (variantie van de steekproef)	89.93	=VAR(P7:P22)
  Kurtosis (kurtosis)	0.51	=KURT(P7:P22)
  Skewness (scheefheid)	0.47	=SKEW(P7:P22)
  Range (interval)	37	=MAX(P7:P22)–MIN(P7:P22)
  Minimum (kleinste waarde)	47	=MIN(P7:P22)
  Maximum (grootste waarde)	84	=MAX(P7:P22)
  Sum (som)	991	=SUM(P7:P22)
  Count (aantal waarden)	16	=COUNT(P7:P22)
  Largest(3) (3de grootste)	70	=LARGE(P7:P22,3)
  Smallest(3) (3de kleinste)	53	=SMALL(P7:P22,3)

• De scheefheid (Skewness) en Kurtosis zijn maten voor het derde en vierde moment en geven aan in welke mate de distributie asymmetrisch (scheef) is en een scherpe piek vertoont t.o.v. de normale distributie. Ze worden door Excel als volgt gedefinieerd:

  scheefheid:
  kurtosis:

  Voor een verdere uitleg over de betekenis en interpretatie wordt verwezen naar de cursus Statistiek.

7.4.3 Een staafdiagram opstellen (*)

Je kan eveneens een visueel beeld van de resultaten opstellen met behulp van een histogram of staafdiagram.

Vooraf moet je een zogenaamde bin range met benedengrenswaarden aanbrengen voor de klassen die je horizontaal wil voorstellen. Je kan dit doen in het werkblad met de gegevens, maar het is wellicht beter dit te doen in het werkblad met de resultaten, zoals het reeds gemaakte werkblad Statistieken.

Kies voor percentageklassen met een breedte van 10 procent en plaatsen de waarden 0, 10, ..., 100 in de bin range D3:D13 van het werkblad Statistieken:

Activeer daarna opnieuw het werkblad Studentenlijst 3, gebruik het commando

en kies Histogram.

Vervolgens verschijnt het Histogram dialoogvenster (figuur 25). Neem als Input Range de percentages met kolomhoofd (P6:P22; kruis Labels aan). De Bin Range bevindt zich in een ander werkblad. Klik in het Bin Range vak, klik vervolgens op de sheet tab van het werkblad Statistieken, en selecteer in dit werkblad de Bin Range Statistieken!D2:D13 .

Stip Output Range aan en klik in het Output Range vak. Duid vervolgens op een analoge wijze de eerste cel van de Output Range in het werkblad Statistieken aan: Statistieken!E2 .

Duid aan dat de klassen niet van grootste naar kleinste frequentie moeten worden geordend (kruis Pareto niet aan). Kruis wel aan dat je een gecombineerde tabel met het histogram en de procentuele cumulatieve verdeling (Cumulative Percentage) wenst. Hierdoor zal je kunnen aflezen hoeveel studenten meer of minder dan een bepaald percentage (veelvoud van 10) hebben. Kruis ook aan dat je een chart (Chart Output) wilt, die rechts van de output tabel zal worden geplaatst.

figuur 25: Het Histogram dialoogvenster

De resulterende tabel en chart worden in het werkblad Statistieken geplaatst, met als linkerbovencel E2 (figuur 26).

Opmerkingen:

• Ook hier verschijnt tijdens het opstellen van het histogram in de statusbalk de boodschap Calculating Histogram…
• De bin range in kolom D en de eerste kolom van de output range E, bevatten dezelfde informatie. Je kan echter niet aangeven dat je de output range in D2 wilt beginnen. De bin range wordt dan leeg gemaakt vóór de berekeningen van het histogram worden uitgevoerd. Als gevolg daarvan is je histogram betekenisloos. Je kan wel achteraf kolom D verwijderen of verbergen.
  
  figuur 26: Het bin range en output van de Histogram tool
• Zowel bij een histogram als bij een staafdiagram worden de punten in klassen verdeeld en wordt een frequentieverdeling van elke klasse door kolommen weergegeven. Bij een histogram moeten deze kolommen tegen elkaar plakken, bij een staafdiagram niet. Excel beweert een histogram te maken, maar plakt de kolommen niet tegen elkaar. Je bekomt dus in werkelijkheid een staafdiagram. Uiteraard kan je in het Options tabblad van het Format Data Series dialoogvenster van de staven de Gap Width gelijk aan 0 maken, zodat je een echt histogram verkrijgt !
• Er kan verwarring zijn over de grenzen van de tabel. Bekijk de klasse met in kolom E de waarde 50. Deze bevat 2 studenten. Dit zijn de twee niet geslaagde studenten met minder dan 50%! Het blijkt hier dus te gaan om de waarden tussen 40 en 50 procent, waarbij de benedengrens (40) niet en de bovengrens (50) wel in deze klasse is opgenomen. In de chart output wordt deze staaf nochtans bij de waarde 60 geplaatst!
• Het standaard in een hoek van 45° plaatsen van de X-waarden op de chart zorgt voor een nog grotere verwarring. Op het eerste zicht staat bij de staaf met hoogte 2 de X-waarde 60. In werkelijkheid is dit 50, en de bovengrens van de klasse (40,50), waarbij de benedengrens niet en de bovengrens wél in de klasse is opgenomen. Wiskundig druk je dit uit als het interval ]40,50].

7.4.4 De vormgeving van het staafdiagram afwerken (*)

Om de onduidelijkheden weg te werken moet je de tabel aanpassen:

Opmerkingen:

• De formule in E4 bestaat uit vijf delen, geschieden door de samenvoegingsoperator &:

  de tekst "]"

  de benedengrens van de klasse (0 in D3)

  de tekst ","

  de bovengrens van de klasse (10 in D4)

  de tekst "]"

  Bij het samenvoegen van tekst en getallen worden de getallen tot tekst omgezet en gaan de formaten verloren.

• Door de waarden in kolom B en het formaat in kolom D te wijzigen zijn de waarden op de X-as en Y-as in het histogram mee aangepast !
• Wanneer je geen bin range opgeeft zal Excel zelf de grenzen van de klassen bepalen. Het aantal klassen wordt dan gekozen in functie van het aantal waarnemingen. Door het geringe aantal waarnemingen zouden er hier slechts vier klassen zijn.

De tabel is nu meer leesbaar geworden. De lay-out van de chart moet echter nog worden aangepast. Om de waarden op de as meer leesbaar te maken moet er meer plaats voor zijn. Vergroot daarom de hoogte van de tabel, verplaats de legende, doe de kolommen tegen elkaar aan plakken, breng rasterlijnen aan voor de linkse Y-as, pas de schalen van de Y-assen aan, en wijzig de hoek van de tekst bij de X-as tot 90°. Wijzig eveneens de titels van de chart en de teksten in de legende:

1. Klik de chart.
2. Sleep de handle onderaan verder omlaag om de chart te vergroten.
3. Dubbelklik de legende, en kies in het Placement tabblad van het Format Legend dialoogvenster Bottom.
4. Dubbelklik de kolom datareeks, en kies in het Options tabblad van het Format Data Series dialoogvenster een Gap Width 0.
5. Kies het commando Chart, Chart Options… en kruis in het Gridlines tabblad bij de Value (Y) axis Major gridlines aan.
6. Dubbelklik de linkse Y-as en, en kies in het Scale tabblad van het Format Axis dialoogvenster een Major unit 1 en een Maximum 10.
7. Dubbelklik de rechtse Y-as en, en kies in het Scale tabblad van het Format Axis dialoogvenster een Minimum 0 en een Maximum 1.
8. Dubbelklik de X-as en, en kies in het Alignment tabblad van het Format Axis dialoogvenster een Orientation van 90°.
9. Klik de titel (Histogram) en wijzig hem in: Histogram van de percentages.
10. Klik de tekst bij de linkse Value Axis (Frequency) en wijzig hem in: Aantal.
11. Kies het commando Chart, Data Source…, Kies in het Series tabblad de eerste datareeks Frequency, klik in het Name vak. Hierin staat de formule ="Frequency". Wanneer je deze vervangt door =F2, zal de naam van deze datareeks in de legende gekoppeld zijn aan de titel van de tabel (Aantal). Doe daarna hetzelfde voor de andere datareeks. Vervang ="Cumulative %" door =G2.
12. Wijzig indien nodig nog de grootte van de chart, de plot area en de teksten.

De tabel en het bijbehorende histogram (dat nu een echt histogram is) hebben nu een betere gedaante (figuur 27).

figuur 27: Het histogram na verzorging van de vormgeving

Opmerkingen:

• Ook hier zijn alle resultaten als getallen bewaard en zal na een wijziging in de data list het histogram dus niet langer up to date zijn.
• Bij een gering aantal gegevens zal de klokvorm van een normale verdeling van de percentages vaak niet erg opvallen. In het algemeen zal bij een groot aantal gegevens (en "normale" studenten) het histogram wel een klokcurve vertonen. De scheefheid geeft een maat aan voor de asymmetrie t.o.v. het gemiddelde; De kurtosis geeft de scherpte van de piek aan. Volgens de hier gebruikte definities zijn ze allebei 0 bij een normale verdeling. De nooit dalende cumulatieve lijn vertoont dan een S-vorm. De mediaan bevindt zich steeds in de eerste klasse met een datapunt op de Cumulatieve frequentielijn, dat 50% of meer bedraagt (klasse ]60,70]). De modus van de klassen (dit is niet de modus van de percentages!) wordt aangegeven door de staaf met de grootste hoogte (klasse ]60,70]).
• Het is soms gevaarlijk een uitspraak over de mediaan te maken, vooral wanneer de gegevens verdeeld zijn over discrete klassen met elk één enkele waarde. Wanneer je een histogram van de punten voor een vak zou maken met klassen ter breedte van één punt, zou de klasse ]11,12] samenvallen met de waarde 12. Wanneer de mediaan in deze klasse valt, zal de mediaan bijna steeds 12 zijn, en in uitzonderlijke gevallen 11.5 (nl. bij een even populatie, waarbij de punten onder en boven de 50% lijn dicht bij deze lijn en op gelijke afstand ervan liggen).

figuur 28: Het Histogram dialoogvenster zonder chart output

Bij examenuitslagen is het beter klassen van de vorm [50,60[ te maken omdat een student met 50 procent mogelijk tot de geslaagden behoort. Eventueel kan je dan ook de gebruikelijke lettercodes in plaats van de klassegrenzen in de chart gebruiken. Om dit te realiseren kan je in een nieuwe Bin Range Statistieken!D15:D26 de grenzen niet op 10, 20 enz. leggen maar op 9.5, 19.5 enz. Vervolgens zou je het histogram opnieuw moeten opstellen en formatteren. Je kan dit vermijden, door als Output Range Statistieken! E15 te kiezen en geen Chart Output aan te vragen (figuur 28). De resultaten worden in cellen E15:E27 van het werkblad Statistieken geplaatst. Kopieer daarna de resultaten uit F17:G27 naar F3:G13 en voer nog enkele kleine wijzigingen aan de vormgeving aan:

Je kopieert de waarden uit F17:G27 best zonder hun formaat:

De chart wordt bij het plakken aangepast.

De resultaten (figuur 29) geven nu een meer informatief beeld.

figuur 29: Het afgewerkte histogram na aanpassing van de grenzen

7.5 Een pivot table opstellen (*)

figuur 30: Stap 1 van de Pivot Table Wizard

Je kan ook bestuderen hoe de punten van de geslaagde en niet geslaagde studenten voor een vak verdeeld zijn. In Excel 4 werd daarvoor een cross table (kruistabel) opgesteld, waarin in essentie gegevens uit een ééndimensionale lijst worden herschikt in een tweedimensionale lijst, waarbij allerlei totalen kunnen worden gemaakt. In Excel 5 is deze methode uitgebreid en van naam veranderd. Ze heet daar pivot table (draaitabel). In Excel 97 is de flexibiliteit verder toegenomen, vooral op het gebied van de vormgeving.

Het opstellen van een pivot table verloopt sterk geautomatiseerd met behulp van de Pivot Table Wizard.

Je zal een pivot table opstellen die aangeeft hoeveel studenten geslaagd of niet geslaagd zijn in functie van hun resultaat voor Boekhouden.

7.5.1 De pivot table creëren met de Pivot Table Wizard (*)

Vooraf selecteer je een cel binnen de data list A6:AE22. Roep daarna de Pivot Table Wizard op met het commando

De Assistant vraagt dan of je hierover enige uitleg wenst. Wanneer je hierop bevestigend antwoordt, krijg je extra informatie en uitleg bij de stappen van de Pivot Table Wizard.

De Pivot Table Wizard omvat vier stappen.

1. In stap 1 (figuur 30) kan je aanduiden waar de gegevens voor de pivot table zich bevinden. heel vaak is dit een externe database. Je hebt de volgende mogelijkheden:

   Microsoft Excel list or database	De gegevens bevinden zich in een Excel tabel, lijst of database. Dit is de standaard keuze.
   External data source	De gegevens zijn afkomstig van zich buiten Excel, meestal van een DBMS.
   Multiple consolidation ranges	De gegevens uit meerdere tabellen of databases moeten worden samengevoegd in één pivot table.
   Another Pivot Table	De gegevens bevinden zich reeds in een pivot table.

   In deze tekst wordt enkel de eerste mogelijkheid behandelden. Klik dus onmiddellijk de Next knop. Het is nochtans interessant een pivot table op te stellen op basis van bv. een Access tabel.

   
   figuur 31: Stap 2 van de Pivot Table Wizard
2. In stap 2 (figuur 31) selecteer je de data list waarvan je een pivot table wilt maken (Range). Wanneer er een data list met de naam Database is wordt deze standaard voorgesteld. Indien er geen data list met de naam Database is wordt de geactiveerde cel uitgebreid tot een region en wordt deze region voorgesteld. Met de Browse... knop kan je een ander werkboek (bestand) openen om daarin een lijst te kiezen.

   Aanvaard het voorstel A6:AE22 en klik de Next knop.

3. In stap 3 (figuur 32) geef je op hoe je pivot table eruit moet zien. Je kan de veldnamen uit de eerste rij van de data list naar één van de gebieden van de pivot table slepen en zo op een erg visuele wijze aangeven hoe de pivot table er moet uitzien. Er zijn vier gebieden:

   Row	Plaats hier het veld (of de velden) waarvan de waarden het rijhoofd zullen vormen. Records met gemeenschappelijke waarden zullen worden gegroepeerd. De rij vormt een eerste dimensie van de pivot table.
   Column	Plaats hier het veld (of de velden) waarvan de waarden het kolomhoofd zullen vormen. Records met gemeenschappelijke waarden zullen worden gegroepeerd. De kolom vormt een tweede dimensie van de pivot table.
   Data	Plaats hier het veld (of de velden) waarvan de waarden moeten worden getotaliseerd. Bij numerieke waarden wordt standaard de som (Sum) gemaakt voor alle records met gemeenschappelijke waarden voor de rij- en kolomhoofden. Bij niet-numerieke waarden wordt standaard het aantal (Count) records geteld. Je hebt later de mogelijkheid om andere totalen aan te geven.
   Page	Indien je een derde dimensie voor de pivot table wenst kan je hier een veld (of velden) plaatsen, zodat enkel records met dezelfde waarde voor dit veld in de pivot table worden getoond. Je kan dan de waarden van dit veld uit een lijst kiezen.

   
   figuur 32: Stap 3 van de Pivot Table Wizard
   
   figuur 33: Stap 4 van de Pivot Table Wizard

   Je kan velden naar het gewenste gebied slepen. Indien ze in een verkeerd gebied staan kan je ze naar een ander gebied slepen of uit het gebied weg slepen, waardoor ze worden verwijderd. Aangezien de veldlijst uitgebreid is en ook verborgen velden (de tekorten) erin worden opgenomen, kan je via een schuifbalk de meer rechts gelegen velden in beeld brengen.

   Kies het veld Boekhouden als rijhoofd, klasse als kolomhoofd en student als te totaliseren gegeven. In het Data gebied wordt aangegeven dat het aantal studenten zal worden geteld (Count of Student). Klik daarna op de Next knop.

4. In stap 4 (figuur 33) kan je kiezen of je de Pivot table wilt plaatsen in een nieuw (New worksheet) of in hetzelfde werkboek (Existing worksheet) als de gegevens waarop hij is gebaseerd. In het laatste geval kan je de linkerbovencel van de pivot table opgeven. Met de Options… knop heb je daarenboven nog de volgende mogelijkheden in het Pivot Table Options dialoogvenster (figuur 34):
   
   figuur 34: Het Pivot Table Options dialoogvenster
   • Kies de naam van de pivot table (standaard PivotTablen).
   • Leg afspraken voor het formatteren vast:

     Toon algemene totalen voor rijen en kolommen (Grand totals for rows/ columns).

     Geef de Pivot table een AutoFormat (AutoFormat Table).

     Hou rekening met verborgen items op de pagina bij het maken van subtotalen (Subtotal hidden page items).

     Voeg cellen met gemeenschappelijke hoofden van rijen en kolommen samen (Merge labels).

     Behoud de formattering bij het wijzigen of up to date brengen van de gegevens (Preserve Formatting). Dit is een zeer belangrijke optie, die er voor zorgt dat de vormgeving niet wordt te niet gedaan bij het bijwerken van de inhoud.

     Geef de volgorde van pagina's aan (Page layout).

     Geef het aantal onder elkaar staande pagina velden aan (Fields per column). Volgende paginavelden staan in de kolom ernaast.

     Geef aan wat i.p.v. een foutwaarde moet worden getoond (For error values, show).

     Geef aan wat in een lege cel moet worden getoond (For empty cells, show).

   • Leg afspraken voor de beschikbaarheid van de originele gegevens vast (Data source options):

     Bewaar de gegevens (verborgen) bij de structuur van de tabel (Save data with tabel layout). Dit versnelt het bijwerken maar vraagt (soms veel) extra geheugen.

     Verhinder het tonen van detail informatie wanneer je een samenvattende rij of kolom dubbelklikt (Enable drilldown).

     Werk de gegevens bij bij het openen van het werkboek (Refresh on open).

   • Leg afspraken voor externe gegevens vast (External data options):

     Bewaar het paswoord van de externe database, wanneer de externe database een paswoord bevat, zodat je het niet telkens opnieuw moet opgeven (Save password).

     Voer de (vaak veel tijd vergende) query om de externe gegevens op te vragen uit in de achtergrond, zodat je ondertussen verder kan werken (Background query).

     Bezuinig op het geheugengebruik bij het bijwerken van de Pivot table (Optimize memory).

   In de meeste gevallen kan je deze opties ongewijzigd laten.

   Kies ervoor de pivot table in een nieuw werkblad te plaatsen en klik de Finish knop. De pivot table wordt in een nieuw werkblad Sheet1 (omdat deze naam niet meer bestaat) geplaatst, links van het geactiveerde werkblad Studentenlijst 3 (figuur 35). Dit werkblad wordt geactiveerd en de Pivot Table toolbar verschijnt.

figuur 35: Het nieuwe werkblad 'Sheet1' met de Pivot table

Hierna volgt een samenvatting van de keuzes in de verschillende stappen van de Pivot Table Wizard:

1. Stap 1: Kies Microsoft Excel list or database.
2. Stap 2: Aanvaard de range A6:AE22. Het referentietype is van geen belang.
3. Stap 3: Sleep Boekhouden naar het ROW vak, klasse naar het COLUMN vak en student naar het DATA vak.
4. Stap 4: Kies New worksheet.

Je kan in de gecreëerde pivot table de volgende delen onderscheiden:

• A3:A11 bevat de voorkomende waarden van het rijveld Boekhouden. De veldnaam staat erboven (A2).
• B2:D2 bevat de voorkomende waarden van het kolomveld klasse. De veldnaam staat boven de eerste cel (B1).
• B3:D11 bevat de corresponderende waarden van het dataveld student. De veldnaam en bewerking (Count of Student) staan schuin links boven de eerste cel (A1).
• Rij 12 (B12:D12) en kolom E (E3:E11) bevatten kolom- en rijtotalen. De verklarende teksten Grand Total staan in A12 en E2. Het algemeen totaal (totaal aantal studenten) bevindt zich in E12.

Opmerkingen:

• De naam van de tabel (standaard PivotTable1) wordt nergens vermeld.
• Tijdens het opstellen van de pivot table verschijnt in de statusregel het bericht Calculating PivotTable.
• Punten die niet werden gegeven komen niet voor in het rijhoofd.
• De oorspronkelijke data list is in feite ook een tweedimensionale tabel met als rijhoofden de studentennamen en als kolomhoofden de vaknamen. Je zou hem kunnen creëren als een pivot table uit een eenvoudigere tabel met de velden student, vak en punt. Het aanbrengen van de totalen en het berekenen van de tekorten zou dan achteraf kunnen gebeuren.
• De benamingen rijveld en kolomveld kunnen verwarrend overkomen. De waarden van het rijveld Boekhouden staan onder elkaar (in het ROW gebied) en zijn rijhoofden van de rijen met corresponderende aantallen. De waarden van het kolomveld klasse staan naast elkaar (in het COLUMN gebied) en zijn kolomhoofden van de kolommen met corresponderende aantallen.

Je kan de Pivot table op drie manieren wijzigen:

• Wanneer je een cel in de Pivot table selecteert kan je hem met de Pivot Table Wizard knop in de Pivot Table Toolbar of met het commando

  Data, PivotTable Report...

  bewerken. Je komt dan onmiddellijk in stap 3 van de Pivot Table Wizard terecht en kan van daaruit naar de vorige of volgende stappen gaan. Deze werkwijze wordt vooral gebruikt om velden toe te voegen aan de pivot table en om de opties in stap 4 te wijzigen.

• Wanneer je in de pivot table een veldnaam of veldwaarden hebben geselecteerd, kan je met de Pivot Table Field knop eigenschappen van dit veld in de pivot table wijzigen via het PivotTable Field dialoogvenster.
• Je kan in de pivot table zelf velden verplaatsen en andere bewerkingen uitvoeren, waardoor je de lay-out op verregaande maar zeer eenvoudige wijze kunnen wijzigen. Op deze manier draai of pivoteer je de tabel.

7.5.2 De Pivot Table toolbar

De Pivot Table toolbar bevat de volgende knoppen, die gedeeltelijk overeenkomen met commando's uit het Data, Group and Outline submenu:

7.5.3 Punten in de pivot table groeperen in klassen (*)

Groepeer nu de punten van Boekhouden in de pivot table in klassen.

figuur 36: Het Grouping dialoogvenster

1. Selecteer vooraf één van de cellen A3:A11 met een veldwaarde voor Boekhouden.
2. Klik de Group knop. Geef in het Grouping dialoogvenster (figuur 36) op dat je de waarden willen groeperen per twee (By 2). Je had ook andere start- (Starting at) en eindwaarden (Ending at) kunnen opgeven en op deze wijze sommige resultaten niet tonen.

Je verkrijgt een pivot table, waarbij de punten voor Wiskunde gegroepeerd zijn in klassen met breedte 2 (figuur 37).

figuur 37: De pivot table na het groeperen in puntenklassen

Enkel de klassen die waarden bevatten 2-3, 4-5, 8-9, 10-11, 12-13 en 14-16 (!) worden getoond. De lege klasse 6-7 wordt niet getoond.

De grootste voorkomende waarde is 16. Dit is ook de automatisch berekende bovengrens van de hoogste klasse (Auto Ending at 16). Daarom wordt deze waarde in de klasse eronder (14-15) opgenomen, die daardoor wordt uitgebreid tot 14-16. Hier is het beter een extra klasse 16-17 te hebben.

Groepeer daarom nogmaals en wijzig de beneden- en bovengrenzen in 0 en 20, de uiterste punten die kunnen voorkomen. Nu wordt gegroepeerd in de klassen 0-1, 2-3, 4-5…, 16-17 en 18-20, waarbij lege klassen (0-1, 6-7 en 18-20) niet worden getoond.

Opmerkingen:

• Je kan het groeperen ongedaan maken door eerst een cel met group labels (in kolom A) te klikken en daarna de Ungroup knop te klikken.
• Wanneer je niet één enkele rijwaarde, maar een aantal opeenvolgende rijen selecteert, vóór je groepeert, dan wordt het Grouping dialoogvenster niet getoond, maar van alle geselecteerde rijen één groep gemaakt, met als grenzen de uiterste waarden.
• In bepaalde situaties weigert Excel de data te groeperen omdat ze niet samen met de pivot table werden bewaard. Dan moet je eerst de Refresh Data knop gebruiken en kan je daarna wel groeperen.
• Wanneer je de benedengrens op 10 legt zullen de klassen 2-3, 4-5 en 8-9 niet worden getoond.
• Wanneer je op een datumveld groepeert, zullen de verschillende soorten tijdseenheden (maanden, weken, dagen, uren...) worden voorgesteld als klassen.
• Wanneer je tekstvelden wilt groeperen moet je eerst de te groeperen velden selecteren. Deze worden automatisch tot een groep omgevormd, waarvan je de naam kan wijzigen. Hierbij blijven de oorspronkelijke waarden bewaard, zodat er een extra rij- of kolomveld ontstaat.

7.5.4 Velden aan een pivot table toevoegen (*)

Breid de pivot table nu uit met enkele velden:

1. Klik in de pivot table en klik daarna de PivotTable Wizard knop. Je komt onmiddellijk in stap 3 van de Pivot Table Wizard:
2. Sleep het veld klasse van het Column naar het Page gebied.
3. Sleep het veld Economie naar het Column gebied.
4. Sleep het veld Percentage naar het Row gebied. Klik daarna onmiddellijk op de Finish knop.

figuur 38: De uitgebreide pivot table

Je verkrijgt nu een meer uitgebreide pivot table (figuur 38)

Je kan in deze pivot table de volgende delen onderscheiden:

• A5:A27 bevat de waarden en subtotaal teksten van het rijveld Boekhouden. De veldnaam staat boven de eerste cel (A4). De waarden zijn nog steeds gegroepeerd in klassen.
• B5:B27 bevat de waarden van het rijveld Percentage. De veldnaam staat boven de eerste cel (B4).
• C4:J4 bevat de waarden van het kolomveld Economie. De veldnaam staat eveneens boven de eerste cel (C3).
• C5:J27 bevat de waarden en subtotalen van het dataveld student. De veldnaam en bewerking (Count of Student) staan schuin links boven de eerste cel (A3).
• B1 bevat een lijst met waarden van het paginaveld klasse. De veldnaam staat ernaast (A1). In het begin is de waarde (All) geselecteerd, maar je kan de andere waarden (geslaagd, deliberatie en niet geslaagd) selecteren. Dit zal de resultaten (C5:K28) wijzigen en eventueel rijen en kolommen met uitsluitend 0 aantallen schrappen.
• Rij 28 (C28:J28) en kolom K (K5:K27) bevatten kolom- en rijtotalen. De verklarende teksten Grand Total staan in A28 en K4. Het algemeen totaal (totaal aantal studenten) bevindt zich in K28.

Opmerkingen:

• Het veld Boekhouden is nog steeds gegroepeerd in puntenklassen.
• Ook hier kan je verder puntenklassen creëren voor het rijveld Economie. Je kan zelfs puntenklassen creëren voor het kolomveld Percentage, maar moet dan de grenzen aanpassen (Starting at 0, Ending at 100, By 10).
• Er is een groot aantal merged cellen: A3:B3, A6:B6, A9:A12 enz.
• Bij een complexe pivot table is het aangewezen het scherm in delen te splitsen door de eerste cel van het Data gebied te selecteren en daarna het commando

  C5:	Window, Freeze Panes

  te gebruiken.

7.5.5 De lay-out van de pivot table wijzigen (*)

Je kan de lay-out van de pivot table op een zeer eenvoudige wijze wijzigen door de cellen met veldnamen in het werkblad met de pivot table zelf te verplaatsen, op een analoge wijze als in stap 3 van de Pivot Table Wizard.

Op dit ogenblik bevinden de veldnamen zich in:

Je kan de pagina-, rij- en kolomvelden verplaatsen en rij- en kolomvelden groeperen:

1. Sleep het rijveld Percentage tot op Boekhouden. De volgorde van de rijvelden Boekhouden en Percentage wordt verwisseld.
2. Sleep het kolomveld Percentage tot op Economie. Het wordt nu een tweede kolomveld. De tabel wordt herschikt.
3. Sleep het kolomveld Percentage tot onder besluit. Het wordt nu een tweede paginaveld. De tabel wordt herschikt.
4. Sleep het paginaveld Percentage tot rechts van besluit. Over de naam van het veld Percentage wordt een zwart kruis geplaatst. Bij het loslaten van de muisknop zal het veld worden verwijderd. Maak deze bewerking ongedaan met de Undo knop.
5. Selecteer een waarde uit het kolomveld Economie (in C4:J4) en klik de Group knop. Geef puntenklassen met grootte 2 (By 2) en grenzen (Starting at) 0 en (Ending at) 20 op. Nu worden de waarden voor Economie gegroepeerd in puntenklassen, juist zoals de waarden voor Boekhouden (figuur 39).

figuur 39: De pivot table na het pivoteren en groeperen

Nu bevinden de veldnamen zich in:

Opmerkingen:

• Je kan alle velden op deze wijze verplaatsen, wat kan neerkomen op het verwisselen van rijen, kolommen en pagina's. Dergelijke bewerkingen noemt men pivoteren. Daarom heet deze tabel een pivot table of draaitabel
• Tijdens het pivoteren wordt een pivoteringsicoon getoond. Daarnaast zal een I-vormige grijze lijn aangeven waar het veld zal worden geplaatst (juist zoals bij het kopiëren-met-opschuiven van ranges).
• Het is mogelijk een pivot table op te stellen zonder kolom-, rij- of paginaveld. Uiteraard moet wel minstens één van deze gebieden één veld bevatten.
• Je kan ook gegroepeerde velden verslepen. Op deze wijze kan je paginavelden met puntenklassen maken. Het is immers niet mogelijk paginavelden rechtstreeks te groeperen, maar je kan wel gegroepeerde velden tot paginavelden omvormen door ze te verslepen.
• Wanneer een gegroepeerd veld tot paginaveld wordt omgevormd, zullen ook lege groepen in de lijst met keuzen worden opgenomen.

7.5.6 Datavelden toevoegen en hun eigenschappen wijzigen

Je kan niet alleen meerdere rij-, kolom- en paginavelden, maar ook meerdere datavelden creëren.

figuur 40: Het PivotTable Field dialoogvenster van een data veld

1. Klik in de pivot table en klik daarna de PivotTable Wizard knop.
2. Sleep in stap 3 het paginaveld Percentage naar het Data gebied. Nu wordt de bewerking Sum of Percentages voorgesteld voor het nieuwe dataveld. Dit is niet gewenst.
3. Bereken het gemiddelde percentage door de veldnaam Sum of Percentages te dubbelklikken.

Nu verschijnt het PivotTable Field dialoogvenster voor een dataveld (figuur 40). Je kan hier de uit te voeren bewerking (Summarize by) wijzigen in Average en de naam (Name) in Gemiddeld Percentage. Je kan eveneens het veld verwijderen (Delete), het numerieke formaat wijzigen (Number...) en de wijze van berekening aangeven (Options>> - het dialoogvenster wordt uitgebreid met een Show Data as gedeelte).

4. Dubbelklik daarna ook de veldnaam Count of student en wijzig de Name in Aantal studenten. Klik tot slot de Finish knop.

In de resulterende pivot table (figuur 41) heb je een tweede rijveld met de naam Data en met als waarden de namen van de datavelden Aantal studenten en Gemiddeld Percentage.

figuur 41: De pivot table met twee data velden

Nu bevinden de veldnamen zich in:

figuur 42: De herschikte pivot table met twee datavelden

Opmerkingen:

• De Summarize by lijst bevat de volgende berekeningen: Sum (standaard bij numerieke velden), Count (standaard bij niet-numerieke velden), Average, Max, Min, Product, Count Nums, StDev, StDevp, Var, Varp).
• Met de Options>> knop kan je de waarden in het Data gebied aangeven als

  Normal	De berekende waarde.
  Difference From	Het verschil t.o.v. de Base Item waarde in het Base Field.
  % Of	Het procentuele deel van de Base Item waarde in het Base Field.
  % Difference From	Het procentuele verschil t.o.v. de Base Item waarde in het Base Field.
  Running Total in	Het lopende (gecumuleerde) totaal.
  % of Row	Het procentuele deel t.o.v. het totaal van de rij.
  % of Column	Het procentuele deel t.o.v. het totaal van de kolom.
  % of Total	Het procentuele deel t.o.v. het totaal van alle rijen en kolommen samen.
  Index	de waarde vermenigvuldigd met het algemene totaal en gedeeld door het totaal van alle rijen en het totaal van alle kolommen.

Indien het aantal studenten 0 is worden geen data-waarden vermeld, ook niet voor de gemiddelden.

Deze lay-out met het data veld als tweede rijveld is enigszins verwarrend en daarom niet aangewezen. Het zou beter zijn dat het Data veld als enig kolomveld dienst doet. Voer daarom de volgende acties uit:

1. Sleep daarom het kolomveld Economie tot op Boekhouden. Het wordt nu een rijveld.
2. Sleep het rijveld Data tot juist boven het Data gebied. Het wordt nu een kolomveld. De tabel wordt herschikt (figuur 42).

Nu bevinden de veldnamen zich in:

7.5.7 Eigenschappen van niet-datavelden wijzigen

Er worden subtotalen berekend voor het eerste rijveld Boekhouden, maar niet voor het tweede rijveld Economie. Deze zijn bij het verplaatsen van Economie van het COLUMN naar het ROW gebied verdwenen.

Je kan ook de eigenschappen van een niet-data veld, zoals Boekhouden en Economie wijzigen. Daarvoor selecteer je de naam van of een waarde in dit veld en klikt de PivotTable Field knop.

figuur 43: Het PivotTable Field dialoogvenster van een niet-data veld

In het getoonde PivotTable Field dialoogvenster (figuur 43) kan je dan de volgende zaken doen:

• Wijzig de veldnaam in de pivot table (Name).
• Plaats het veld in het Row, Column of Page gebied (Orientation).
• Voorzie samenvattende rijen of kolommen op het einde van de tabel voor de secundaire velden (Subtotals), wanneer er meer dan één veld in het Row of Column gebied is opgenomen. Je kan het subtotaal van het veld in het Data gebied kiezen (Automatic – standaard), één of meerdere subtotalen uit een lijst kiezen (Custom – per in de lijst geselecteerd totaal wordt een rij of kolom voorzien) of geen subtotalen kiezen (None).
• Verberg de rijen of kolommen van de in de lijst geselecteerde waarden (Hide Items).
• Toon lege rijen of kolommen (Show items with no data – standaard niet aangekruist).
• Verwijder het veld uit de pivot table (Delete).
• Bepaal geavanceerde eigenschappen (Advanced…). In het PivotTable Field Advanced Options dialoogvenster (figuur 44) heb je de volgende mogelijkheden:

  Geef mogelijkheden om Page velden zo efficiënt mogelijk te beheren (Page field options).

  Bepaal in welke volgorde de veldwaarden worden getoond (AutoSort options).

  Bepaal hoeveel en welke veldwaarden worden getoond (AutoShow options).

• Formatteer getallen (Number…) in het Format Cells dialoogvenster, dat enkel een Number tabblad heeft. Deze knop komt enkel voor bij numerieke velden.

figuur 44: Het PivotTable Field Advanced Options dialoogvenster

Toon als toepassing de subtotalen voor Economie:

1. Selecteer een waarde in het veld Economie en klik de PivotTable Field knop.
2. Duid in het PivotTable Field dialoogvenster Count aan als Custom Subtotals.

figuur 45: De pivot table met sub totalen voor beide rijvelden

Nu worden onderaan de tabel de subtotalen voor Economie getoond (figuur 45) in rijen 28:37. Nochtans zijn er enige onvolkomenheden:

• Er worden aantallen getoond voor zowel Aantal studenten (zoals gewenst), als voor Gemiddeld Percentage (overbodig en verwarrend).
• Je had dit gedeeltelijk kunnen oplossen door in het PivotTable Field dialoogvenster twee subtotalen aan te klikken: Count en Average. In dat geval worden de gemiddelde percentage per klasse Economie getoond (zoals gewenst) maar ook extra zinloze gemiddelden voor de kolom Aantal studenten, die trouwens in een foutmelding #DIV/0! resulteren.
• Er worden ook subtotalen getoond voor niet-voorkomende klassen voor Economie, al werd deze optie niet aangekruist !

Je kan de onvolkomenheden wegwerken door de kolom met gemiddelde percentages te verwijderen:

3. Selecteer een waarde in de kolom Gemiddeld Percentage en klik de PivotTable Field knop.
4. Klik de Delete knop in het PivotTable Field venster.

Kolom D is nu leeg, Het kolomveld Data is verdwenen. Er is dus geen kolomveld meer (figuur 46). Nu bevinden de veldnamen zich in:

figuur 45: De pivot table met subtotalen voor beide rijvelden