Nu de matrix van het puntenblad is opgesteld, kunnen we de eerder ingevoerde basisfunctionaliteiten uitbreiden met wat meer geavanceerd functies:
- Eerst berekenen we subtotalen voor de beoordelingscriteria die behoren tot dezelfde vraag
- Vervolgens berekenen we voor elke student een totaalscore
- In functie van het gebruiksgemak geven we de kolommen van de beoordelingscriteria en de totalen een herkenbaar kleurtje
- Ten slotte zorgen we er voor dat de quoteringen zoveel mogelijk foutloos worden ingevoerd, eventueel met behulp van een keuzelijst
Vermits we met een 'rekenblad' aan het werk zijn, ligt het voor de hand dat we de optelling van de quoteringen per beoordelingscriterium niet handmatig uitvoeren, noch met een rekenmachine, maar daarentegen aan MS Excel overlaten.
We maken daartoe gebruik van eenvoudige formules en functies.
Als je dat wenst kan je nog andere interessante statistische gegevens inbouwen in je puntenblad: gemiddelde quotering, minimum en maximumquotering, standaarddeviatie en aantallen ; maar deze spreidingsmaten komen in de volgende webpagina aan bod.
Indien je de moeilijkheidsgraad van een (examen)vraag wil inschatten, dan kan je voor elke vraag in je puntenblad een subtotaal berekenen, dat de quoteringen van de verschillende vraagonderdelen totaliseert.
Om daartoe een 'totaalkolom' in te voegen, achter het laatste beoordelingscriterium, ga je als volgt te werk.
- Plaats de muiswijzer op de kolom, rechts van de kolom waarin het laatste beoordelingscriterium van een vraag wordt weergegeven, waardoor je muiswijzer verandert in een zwarte, neerwaarts gericht pijl
- Klik op dat moment met de rechtermuisknop op de kolomkop om een vervolgmenu te openen
- Klik in het vervolgmenu op de optie 'Invoegen', waardoor onmiddellijk een nieuwe kolom wordt ingevoegd, links van de geselecteerde kolom
- Tik als label voor deze nieuwe kolom de tekst 'Totaal' in (of zo je wil 'Subtotaal')
- Voer in de rij waarin voor de beoordelingscriteria de puntwaarde werd ingevoerd een formule in die de puntwaarden optelt. Dat gaat als volgt:
- Klik in de cel waarin het subtotaal moet worden weergegeven (in ons voorbeeld is dat de nog lege cel F4)
- Tik in de formulebalk een isgelijkaanteken ' = '
- Tik de naam van de functie ' Som '
- Open de ronde haakjes ' ( '
- Klik op de cel waarin de puntwaarde van het eerste beoordelingscriterium is ingevoerd (in ons voorbeeld is dat cel C4)
- Sleep met ingedrukte linkermuisknop naar de cel waarin de puntwaarde van het laatste in het subtotaal mee te rekenen beoordelingscriteria is ingetikt (in ons voorbeeld sleep je van C4 naar E4)
- Sluit de ronde haakjes af ' ) ' om de formule te vervolledigen
- Druk op de 'Enter-toets'
om het resultaat van de formule in de cel waarin het subtotaal moest verschijnen weer te geven (in ons voorbeeld is dat weer de cel F4): =SOM(C4:E4)
- Je kan bovenstaande procedure volgen om aan elke te beoordelen vraag, die uit verschillende beoordelingscriteria is opgebouwd, een subtotaal toe te voegen
Meer informatie aangaande het intikken van een formule kan je hier vinden en aangaande het invoegen van formules met behulp van de muis, kan je hier vinden.
Vermits we niet enkel een subtotaal willen berekenen voor de puntwaarden in ons werkblad (in ons voorbeeld in rij 4 van het puntenblad), maar ook voor elke student afzonderlijk, moeten we de zojuist opgestelde formule voor elke student in de kolom F invoeren.
Je hoeft daartoe de eerdere formule echter niet te tikken in de cellen F5, F6, F7 enzovoort. MS Excel kent namelijk een techniek die 'doorvoeren' wordt genoemd en waarmee je de inhoud van een cel op eenvoudige wijze kan kopiëren naar andere cellen.
- Klik in de cel waarin je de te kopiëren formule =SOM(C4:E4) hebt ingetikt (in ons voorbeeld cel F4)
- Bemerk dat rechtsonderaan in de geselecteerde cel een zwart blokje wordt getoond, de zogenaamde 'vulgreep'
- Wanneer je de muiswijzer op deze vulgreep plaatst, verandert je muiswijzer in een plusteken
- Om de formule in deze cel naar de onderliggende cellen te kopiëren, moet je de vulgreep met ingedrukte linkermuisknop naar beneden verslepen, totdat je in de kolom (in ons voorbeeld, kolom F) de laatste cel hebt bereikt waarin een kopie van de formule moet worden gezet (in ons voorbeeld, de cel in kolom F waarin het subtotaal van de laatste student moet worden berekend.
- Bemerk dat, tijdens het slepen met ingedrukte linkermuisknop, door een grijs kader en plusteken wordt aangegeven naar welke cel op elk moment een kopie van de formule zal worden gekopieerd.
- Laat de linkermuisknop pas los, wanneer je (in de kolom) de rij waarin de gegevens van de laatste student hebt bereikt.
- Het resultaat van het doorvoeren is dat in elke cel van kolom F het subtotaal van de beoordelingscriteria van vraag 1 voor elke student verschijnt. Momenteel is dit subtotaal 0 (nul), maar dat zal uiteraard veranderen wanneer 'echte' quoteringen worden ingevoer voor een student.
- Wanneer je bijvoorbeeld op de cel F5 klikt, dan wordt in de formulebalk, net onder het lint, de gekopieerde formule getoond: Excel heeft daartoe het door jou ingetikte celbereik 'C4:E4' in de formule, automatisch gewijzigd in 'C5:E5'.
- Je kan bovenstaande procedure 'doorvoeren' gebruiken om de formule, die je voor elk subtotaal berekende, door te voeren naar de onderliggende cellen waarin de studentenquoteringen zullen komen.
Meer info aangaande het doorvoeren van celinhouden kan je hier vinden.
Uiteraard moeten de verschillende puntwaarden, die je aan de beoordelingscriteria toekende, getotaliseerd worden zodat je voor elke student een examenscore bekomt.
Dat gaat als volgt:
- Tik in de cel achter het laatste subtotaal het label 'Eindtotaal' (in ons voorbeeld is dat in de cel O3)
- Voer in de cel daaronder (O4) een formule die de som maakt van alle subtotalen:
- Tik in de formulebalk: =Som(
- Klik vervolgens in de eerste cel waarin een subtotaal van de puntwaarden wordt berekend (in ons voorbeeld klik je op de cel: F4)
- Vervolgens tik je achter het ingevoerde celadres een puntkomma
- Doe hetzelfde met elke andere cel waarin je een formule voor een subtotaal hebt ingevoerd (in ons voorbeeld: J4;N4), maar achter de celverwijzing naar het laatst subtotaal hoef je geen puntkomma te tikken
- Sluit de formule af met een rond haakje, zodat de formule lijkt op: =SOM(F4;J4;N4)
- Drukken op de Enter-toets of klikken op de knop 'Invoeren'
zorgt ervoor dat het eindtotaal in de cel O4 wordt berekend
Daarna kan je - zoals hoger uitgelegd - de formule, die het eindtotaal van alle subtotalen berekent, naar de onderliggende cellen doorvoeren, zodat het eindetotaal voor elke student wordt berekend.
Wanneer het zojuist berekende eindtotaal van de puntwaarden of subtotalen niet totaliseert op 20, zoals in ons voorbeeld - waarin het eindtotaal totaliseert op 13,5 -, dan moet het berekende eindtotaal nog worden omgezet naar 20 punten.
Dat gaat als volgt:
- Klik in de cel achter het label 'Eindtotaal' bijvoorbeeld het label 'Eindscore'
- Tik in de cel achter de formule voor berekening van het eindtotaal een nieuwe formule: =AFRONDEN(O4*(20/$O$4);0)
Deze formule betekent:
- AFRONDEN( om de uitkomst van de formule af te ronden
- O4 is het celadres van de cel waarin je het eindtotaal hebt berekend
- $O$4 is een absolute celverwijzing naar het eindtotaal van alle puntwaarden ; klik hier voor meer info
- ;0) werd toegevoegd om de uitkomst van de vermenigvuldiging 04*(20/$O$4) af te ronden op 0 cijfers na de komma (omdat een examenquotering steeds als een geheel getal moet worden ingediend)
- Voer deze formule door - zoals hoger uitgelegd - in alle cellen waarin deze gecorrigeerde eindscore moet worden berekend
Wanneer je daarstraks een 'Eindtotaal' berekende dat wél totaliseert tot 20, dan hoefde je de omzetting naar 20, besproken in de voorgaande paragraaf, niet uit te voeren.
Om het berekende eindtotaal voor al je studenten in dat geval toch af te ronden op een geheel getal, omdat je enkel gehele getallen als examenquoteringen kan indienen, kan je al volgt te werk gaan:
- Klik in de cel achter het label 'Eindtotaal' bijvoorbeeld het label 'Eindscore'
- Tik in de cel achter de formule voor berekening van het eindtotaal een nieuwe formule: =AFRONDEN(O4;0)
Deze formule betekent:
- AFRONDEN( is de naam van een functie om de uitkomst van de formule in de cel met het eindtotaal af te ronden
- O4 is de verwijzing naar de cel met het eindtotaal
- ;0) betekent dat het eindtotaal in cel O4 moet worden afgerond met 0 (nul) cijfers na de komma (een geheel getal dus)
- Vervolgens kan je de afgeronde eindscore doorvoeren - zoals hoger uitgelegd - naar de cellen waarin voor elke student een afgeronde eindscore moet worden weergegeven
Het invoeren van het juiste cijfer in de juiste kolom verloopt wat makkelijk wanneer je de criteria die tot eenzelfde vraag behoren ook eenzelfde achtergrondkleur meegeeft.
Tevens kan je totalen wat meer laten opvallen door ze in een kleurtje te zetten en de eindscores - afhankelijk van de hoogte van de totaalscore - in te kleuren.
Om de quoteringen van de beoordelingscriteria, die tot eenzelfde vraag behoren, makkelijk in te voeren door ze tegen eenzelfde achtergrondkleur weer te geven, kan je als volgt te werk gaan:
- Selecteer de cellen waaraan je eenzelfde achtergrondkleur wil toekennen, door ze met ingedrukte linkermuisknop te overslepen
- Klik in het tabblad 'Start' op de knop 'Opvulkleur'
om een kleurengallerij te openen
- Klik op één van de 'Themakleuren' of 'Standaardkleuren' om deze op de geselecteerde cellen toe te passen
- Je kan ook op de knop 'Meer kleuren' klikken indien de voornoemde kleurknoppen niet voldoen.
Klik hier voor meer info aangaande het opmaken van tekst- en achtergrondkleuren
Om de totaalcijfers een kleurtje mee te geven:
- Selecteer de cellen met totaalcijfers, door deze te overslepen met ingedrukte linkermuisknop
- Klik in het tabblad 'Start' op de knop 'Tekstkleur'
- Klik in de daardoor geopende kleurengallerij op een gepaste 'Themakleur' of 'Standaardkleur' of klik op de knop 'Meer kleuren'
De kolom met de 'Eindtotaal' of de 'Eindscore' kan je een stuk informatieve maken door er een 'automatische' kleurencode op toe te passen.
Zo kan je bijvoorbeeld alle eindscores:
- kleiner dan 8 automatisch met een rode kleur weergeven,
- 7 en 8 met een oranje kleur weergeven
- 10 of hoger in het groen weergeven
De functie 'Voorwaardelijke opmaak' van Excel biedt daartoe tal van mogelijkheden:
- Selecteer de cellen waarin de totaalscore wordt berekend door ze te overslepen met ingedrukte linkermuisknop
- Klik in het tabblad 'Start' op de knop 'Voorwaardelijke opmaak'
- In het daardoor geopende vervolgmenu worden tal van mogelijkheden voor voorwaardelijk opmaken aangeboden, waarvan we hier enkel de optie 'Markeringsregels voor cellen' bespreken
- Klikken op voornoemde optie opent een submenu waarin we eerst de optie 'Groter dan' aanklikken
- In het dialoogvenster 'Groter dan' tik je in het invoerveld 'Cellen opmaken die groter zijn dan:' de waarde 9
- In de keuzelijst kies je de optie 'Groene opvulling met donkergroene tekst', waardoor alle eindscores in de geselecteerde kolom groen zullen kleuren wanneer de eindscore 10 of groter is.
- Klik op de knop 'OK' om het dialoogvenster 'Groter dan' te sluiten
- Klik opnieuw op de knop 'Voorwaardelijke opmaak' en kies thans in het submenu van het vervolgmenu 'Markeringsregels voor cellen' de optie 'Kleiner dan'
- In het gelijknamige dialoogvenster tik je nu de waarde 8 in en stel je de opmaak in als 'Lichtrode opvulling met donkerrode tekst'
- Bemerk dat door het invullen van deze settings de cijfers in de geselecteerde cellen meteen rood kleuren tegen een rode achtergrond, ten minste, wanneer in deze cellen momenteel de waarde 0 (nul) wordt weergegeven
- Klik op de knop 'OK' om het dialoogvenster te sluiten en herhaal deze procedure nog een derde keer, waarbij je thans kiest voor de optie 'Tussen'
- In dit dialoogvenster 'Tussen' tik je in het invoerveld voor de minimumwaarde het getal 8 en in het invoerveld voor de maximumwaarde 9.
- Kies in de keuzelijst thans voor de optie 'Aangepaste indeling'
- Daardoor wordt het dialoogvenster 'Celeigenschappen' geopend waarin je klikt op het tabblad 'Opvulling' om de kleurcode te bepalen.
- Klik op de gewenste achtergrondkleur ('Themakleur' of 'Standaardkleur') of klik op de knoppen 'Opvuleffecten' of 'Meer kleuren' om een gewenste achtergrondkleur in te stellen
- Klik op de knop 'OK' om het dialoogvenster 'Celeigenschappen' weer te sluiten
- Klik andermaal op de knop 'OK' om het dialoogvenster 'Tussen' te sluiten
- Het resultaat zal, wanneer je voor een aantal studenten quoteringen hebt ingevuld en de 'Eindscore' hebt laten berekenen, er als volgt uitzien:
Klik hier voor meer info aangaande de mogelijkheden van de 'Voorwaardelijke opmaak'.
Voor het invoeren van de quoteringen van studenten per beoordelingsscriterium kan je grofweg twee strategieën gebruiken:
- ofwel tik je de gepaste quotering als een cijfer in de betreffende cel
- ofwel kies je de quotering uit een keuzelijst, die alle mogelijk quoteringen voor het criterium bevat, en hoef je bijgvolg niets in te tikken
Wanneer je voor de eerste strategie kiest, dan lijkt het aangewezen dat je voorkomt dat er in een cel een hoger cijfer wordt ingevoerd, dan de puntwaarde die je eerder aan het beoordelingscriterium hebt toegekend.
Als je voor de tweede strategie kiest, dan moet aan elke quoteringscel in het werkblad een gepaste keuzelijst worden toegevoegd.
Beide beperkingen van de waarden die in een cel kunnen worden ingevoerd, kan je waarborgen door gebruik te maken van zogenaamde 'geldigheidsregels'.
- Selecteer alle cellen waarvoor je een zelfde maximum in te voeren waarde wil instellen
- Klik daarna op het tabblad 'Gegevens'
- Klik in de groep 'Hulpmiddelen voor gegevens' op de knop 'Gegevensvalidatie'
- Daardoor wordt het dialoogvenster 'Gegevensvalidatie' geopend
- Klik de keuzelijst 'Toestaan' open en kies daarin de optie 'Geheel getal' of 'Decimaal getal', afhankelijk van de (al dan niet decimale) quoteringen die je in de quoteringscellen wil invoeren
- Daardoor wordt de keuzelijst 'Gegeven' getoond, waarin je kan kiezen welke 'operator' voor deze cellen gewenst is: kleiner dan, groter dan, tussen,...
Wellicht is de standaardoptie 'Tussen' voor deze keuzelijst een gepaste optie
- Tik in het invoerveld 'Minimum' de waarde 0 (nul), omdat je wellicht geen negatieve quoteringen wil invoeren
- Tik in het invoerveld 'Maximum' de puntwaarde die je eerder aan het beoordelingscriterium hebt toegekend (in ons voorbeeld is dat de puntwaarde 1)
- Klik op de knop 'OK' om het dialoogvenster 'Gegevensvalidatie' te sluiten
- Wanneer je nu per vergissing een hogere of lagere waarde in de geselecteerde cellen invoert, dan verschijnt een foutmelding, die je er op wijzen dat je de ingestelde maximumwaarde hebt overschreden
Klik hier voor meer info aangaande het aanpassen van deze foutmelding en aangaande het invoeren van een 'invoerbericht'
Als je jezelf een hoop tikwerk wil besparen, kan je er tevens voor kiezen om aan elke quoteringscel een keuzelijst te koppelen waarin alle mogelijke quoteringen als aanklikbare opties worden aangeboden. Voordeel daarvan is dat je vooraf kan bepalen welk quoteringen in een cel zijn toegestaan én dat je de hand aan de computermuis kan houden omdat je niet meer moet tikken.
- Tik rechts van de kolom, waarin je de eindscore berekende, in een willkeurige, lege kolom de mogelijke quoteringen, die in de keuzelijst moeten voorkomen, onder elkaar in (in ons voorbeeld kiezen we daarvoor de kolom S).
Noot: Je mag de cellen, waarin je deze keuzelijstopties intikte nooit verwijderen, noch verplaatsen, omdat dan de koppeling van de quoteringscellen met de keuzelijsten wordt verbroken
Noot: Plaats deze mogelijke quoteringen daarom beter niet onder de
lijst met studentennamen, omdat die dan in de weg komt te zitten wanneer je nadien nog studenten moet toevoegen
- Selecteer alle ingevoerde mogelijke quoteringen, zodat je er de celeigenschappen van kan veranderen
- Klik met de rechtermuis op de geselecteerde cellen om een contextmenu te openen
- Kies in het contextmenu voor de optie 'Celeigenschappen'
- In het daardoor geopende gelijknamige dialoogvenster, wijzig je in de keuzelijst 'Categorie' de optie 'Standaard' in de optie 'Tekst', omdat anders straks de opties van de keuzelijst niet correct worden weergegeven, wanneer de mogelijke quoteringen decimale cijfers bevatten.
- Klik vervolgens op de knop 'OK' om het dialoogvenster te sluiten
- Daardoor worden de ingevoerde keuzelijstopties links uitgelijnd in de cellen in de plaats van rechts
- Selecteer de quoteringscellen waaraan je een keuzelijst wil koppelen
- Klik in het lint op het tabblad 'Gegevens'
- Klik in de groep 'Hulpmiddelen voor gegevens' op de knop 'Gegevensvalidatie'
- Daardoor wordt het dialoogvenster 'Gegevensvalidatie' geopend
- Klik in de keuzelijst 'Toestaan' op de optie 'Lijst'
- Daardoor wordt het op dat moment nog lege invoerveld 'Bron' getoond, waarin je moet klikken om het te activeren
- Selecteer vervolgens de menuopties voor de keuzelijst, die je eerder hebt ingetikt, door er met ingedrukte linkermuisknop over te slepen
- Zodra je de laatse keuzelijstoptie hebt geselecteerd, laat je de ingedrukte linkermuisknop los, waardoor de verwijzing naar de geselecteerde cellen wordt weergegeven in het invoerveld 'Bron'
- Klik op de knop 'OK' om het dialoogvenster te sluiten
- Wanneer je nu op één van de quoteringscellen klikt, waaraan je zojuist de keuzelijst hebt gekoppeld, verschijnt een vervolgknop
- Bij het aanklikken van deze vervolgknop wordt de keuzelijst geopend, waarin je de gepaste quotering kan selecteren
Wanneer de studentennamenlijst in kolom A zeer lang is en je naar de rijen navigeert waarin de laatste studentnamen zich bevinden, dan is het mogelijk dat de kop van je werkblad verdwijnt van je beeldscherm. Het is dan niet meer mogelijk om de labels van de beoordelingscriteria, noch de daaraan toegekende puntwaarde te consulteren.
Evenzo zullen de studentnamen uit het beeldscherm verdwijnen wanneer je erg veel beoordelingscriteria hebt ingevoerd en je de laatste criteria wil quoteren. Je kan dan niet met zekerheid weten voor welke student je een quotering invoert.
Je kan het verdwijnen van belangrijke informatie in de kop of uiterst links op je beeldscherm voorkomen door de betreffende kolommen en rijen te blokkeren.
- Klik op de cel - net onder de rij met puntwaarden en net rechts van de studentvoornamen - om deze te selecteren
- Klik in het lint op het tabblad 'Beeld'
- Klik in de groep 'Venster' op de knop 'Blokkeren'
- Klik in het daardoor geopende vervolgmenu voor de optie 'Titels blokkeren'
- Bemerk dat vanaf nu de geblokkeerde rijen en kolommen steeds zichtbaar blijven wanneer je in het werkblad naar onder of rechts scrollt, waarbij de geblokkeerde rijen en kolommen worden aangegeven met een zwarte lijn.
