De student moet gegevens in een MS Excel-tabel kunnen analyseren door
totalen en subtotalen te berekenen: som, gemiddelde, minimum, maximum, standaardafwijking, berekende velden.....
In een 'normale' tabel (celbereik) moet de student subtotalen kunnen berekenen.
In de voorgaande webpagina's werd toegelicht hoe je een MS Excel-tabel kan definiëren, de gegevens in dergelijke tabel kan filteren en sorteren.
Excel-tabellen bieden evenwel nog heel wat meer mogelijkheden waarvan er enkele op deze pagina worden toegelicht: een berekend veld toevoegen, een totaalrij toevoegen, totalen berekenen, nieuwe rijen en kolommen toevoegen.
Bij het opstellen van een tabel kwam het begrip 'berekend veld' reeds ter sprake: aan een tabel met studentgegevens werd een kolom (veld) toegevoegd waarin een gemiddelde stagequotering werd berekend op basis van de gegevens in twee andere kolommen (stagequotering voor een tweedejaarsstage en voor een derdejaarsstage).
Na de definiëring van een tabel als MS Excel-tabel kan het toevoegen van een dergelijk berekend veld echter veel sneller gebeuren. Open daartoe andermaal het bestand 'Oefening_tabel.xlsx' dat je in de loop van de voorgaande webpagina's zelf hebt opgebouwd:
- in rij3 van het werkblad werd een hele reeks kopteksten (kolom- of veldnamen, of nog: variabelen) ingevoerd
- daarna werd in elke kolom een overeenkomstig studentkenmerk (gegeven) ingevoerd: indien je de instructies exact volgde werden in totaal van 12 fictieve studenten de gegevens ingevoerd
Indien je niet over een tabel beschikt zoals hieronder weergegeven:
- Klik hier om de tabelgegevens te kopiëren en te plakken in een nieuw werkblad (dat gaat het makkelijkst wanneer je het zelfstudiepakket opent met Internet Explorer)
- Definieer de tabel als een MS Excel-tabel
- Sla het werkblad op onder de naam: Oefening_tabel.xlsx
Onderstel dat we een nieuwe kolom aan de bovenstaande tabel in het werkblad 'Oefening_tabel.xlsx' willen toevoegen waarin we de optelsom van de quoteringen voor de tweedejaarsstage en de derdejaarsstage samentellen. Dan kan als volgt:
- Navigeer naar de eerste lege cel rechts van de tabel in de rij met kopteksten. In ons voorbeeld is dat cel L3. Voeg daarin de nieuwe koptekst 'Totaalscore' toe en klik op de 'Enter'-toets.
Daardoor wordt automatisch een nieuwe tabelkolom toegevoegd, mét een filterknop en met in ons voorbeeld afwisselend lichtblauwe en blauwe rijen, evenveel als er cases (studenten) werden ingevoerd.
- Zoals je je wellicht herinnert, is de opdrachtknop 'Autosom' bedoeld om snel waarden in cellen bij elkaar op te tellen: in dit voorbeeld de waarden in kolom 'I' ('Stagequotering 2') en kolom 'J' ('Stagequotering 3').
Klik op cel L4 (indien deze nog niet geselecteerd zou zijn) en vervolgens in het lint op de knop 'Autosom' 
op het tabblad 'Start' in de groep 'Bewerken'.
- Daardoor wordt in cel L4 de functie 'Som()' ingevoerd.
Selecteer de cellen I4 en J4 door erover te slepen met ingedrukte linkermuisknop
- Druk op de 'Enter'-toets en bemerk dat automatisch voor alle rijen (alle studenten) meteen de totaalscore wordt berekend, waarbij de celverwijzingen (argumenten) van de functie 'Som' automatisch wordt aangepast aan de celnamen in de rij
Deze werkwijze levert dus veel sneller het gewenste resultaat op dan wanneer de functie 'Som()' eerst moet worden ingevoerd in één cel, om ze vervolgens manueel door te voeren naar alle andere cellen.
Eveneens handig is de mogelijkheid om onder een Excel-tabel een totaalrij toe te voegen. Hierin kan vervolgens voor de kolommen waarvan de cellen een getal bevatten op eenvoudige wijze een totaal worden ingevoerd en voor kolommen met tekst een totaalaantal.
- Klik op een willekeurige plaats in de tabel, om het contextuele tabblad 'Hulpmiddelen voor tabellen' te openen
- Klik op het tabblad 'Ontwerpen' en bemerk dat dit tabblad tal van mogelijkheden bevat om de tabel te bewerken
- Vink het selectievakje 'Totaalrij' aan, waardoor onmiddellijk onder de laatste tabelrij een rij met als koptekst 'Totaal' wordt toegevoegd.
- Klik op de lege cel in de totaalrij onder de kolom 'Stagequotering 2' om een vervolgknop
zichtbaar te maken, die bij het aanklikken een keuzemenu opent met allerlei functies om totalen te berekenen voor de gegevens in de betreffende kolom.
- Klik in het menu bij wijze van voorbeeld op de optie 'Gemiddelde' om het gemiddelde voor alle stagequoteringen voor een tweedejaarsstage te berekenen.
- Bemerk dat, wanneer je de cel met het gemiddelde selecteert, in de formulebalk een speciale formule, een zogenaamde 'Gestructureerde verwijzing' wordt weergegeven, waarin de koptekst (naam) van de kolom is opgenomen.
Bereken op gelijkaardige wijze:
- het totaal aantal studenten in de kolom 'Naam': gebruik daartoe de functie 'Aantal'
- het aantal studenten dat een quotering voor een derdejaarsstage heeft gekregen: gebruik daartoe de functie 'Aantal' in het keuzemenu
- het maximum voor de kolom 'Stage-gemiddelde': functie 'Maximum'
- het aantal studenten dat momenteel in het tweede jaar zit in de kolom 'Jaar'
- kies voor de optie 'Meer functies' (meer info)
- zoek de functie 'Aantal.als' op
- selecteer het gepaste bereik: G4:G15
- voer het criterium in '=2'
- de standaarddeviatie voor de kolom 'Totaalscore', afgerond op 2 cijfers na de komma:
- Klik in cel L16
- Tik in de formulebalk: =a
- Dubbelklik op de optie 'Afronden'
- Tik in de formulebalk de beginletters van de functie standaarddeviatie: st
- Dubbelklik op de menuoptie 'STDEV'
- Selecteer het celbereik L4:L15
- Vervolledig de geneste formule met: );2)
- Druk op de 'Enter'-toets of klik op de knop 'Invoeren'
Het resultaat:
Wanneer een totaalrij wordt weergegeven lijkt het niet vanzelfsprekend om onderaan in de tabel een nieuwe tabelrij toe te voegen waarin de gegevens betreffende een nieuwe case (in ons voorbeeld een student) kunnen worden ingevoerd. De totaalrij 'zit dan in de weg'.
In dat geval een rij toevoegen gaat echter als volgt:
- Klik op de cel waarin het allerlaatste gegevens is ingevoerd nét boven de totaalrij. In ons voorbeeld is dat cel L15.
- Druk vervolgens op de 'Tab'-toets op je toetsenbord om een nieuwe rij onderaan in de tabel toe te voegen
Om een nieuwe rij tussen te voegen op een willekeurige plaats in de tabel, ga je als volgt te werk:
- Klik op de rij waarboven de nieuwe rij moet worden tussengevoegd
- Klik met de rechtermuisknop en kies in het snelmenu de optie 'Invoegen'
- Kies in het vervolgmenu 'Tabelrijen boven'
Noot: een nieuwe kolom op een willekeurige plaats in de MS Excel-tabel tussenvoegen kan uiteraard ook. Volg daartoe bovenstaande procedure maar kies voor 'Tabelkolommen links' in het vervolgmenu (nummer 3 in de bovenstaande figuur).
Wanneer je de specifieke gebruiksmogelijkheden van een MS Excel-tabel (zoals we die hoger op deze pagina en in de voorgaande pagina's besproken hebben) niet langer nodig hebt, dan kan je de tabel terug omzetten in een 'normaal' celbereik.
Wanneer je naar deze 'oorspronkelijke' situatie terugkeert dan:
- blijven de opmaakstijl van de tabel, de totaalrij, de toegevoegde rijen en (berekende) kolommen behouden
- verdwijnen echter de filterknoppen
- alsook de vervolgknoppen in de totaalrij
- en worden de 'gestructureerde verwijzingen' omgezet naar normale celverwijzingen
Om de definiëring als MS Excel-tabel op te heffen, volg je onderstaande procedure:
- Klik op een willekeurige plaats in de tabel
- Klik in het lint op het tabblad 'Ontwerpen'
- Klik in de groep 'Extra' op de opdrachtknop 'Converteren naar bereik'
- Excel komt je eerst om bevestiging vragen
- Klikken op de 'Ja'-knop voltrekt de conversie
Bemerk dat je geen gegevens of formules bent kwijt geraakt, maar dat enkel de typische Excel-tabel-functies niet langer beschikbaar zijn, totdat je de tabel opnieuw als Excel-tabel definieert.
Subtotalen kunnen NIET berekend worden op een MS Excel-tabel!
Onder meer om deze reden werd in de voorgaande paragrafen toegelicht hoe je een MS Excel-tabel terug kan omzetten in een 'normaal' celbereik. Indien je deze convertering nog niet hebt uitgevoerd op de tabel in het bestand 'Oefening_tabel.xlsx', doe dat dan eerst.
Indien je ook de afwisselende blauw en witte achtergrondkleuren wil verwijderen, zodat deze straks het lezen van het subtotalenoverzicht niet onnodig bemoeilijken:
- Selecteer dan de gehele tabel
- Klik op het tabblad 'Start'
- Klik in de groep 'Bewerken' (uiterst rechts op het lint) op de opdrachtknop 'Wissen'
- Selecteer in het vervolgmenu de optie 'Opmaak wissen'
Stel dat bij wijze van voorbeeld in je tabelbestand 'Oefening_tabel.xlsx' moet onderzocht worden of mannelijke
studenten gemiddeld hogere quoteringen voor stages verkrijgen dan
vrouwelijke. Dergelijke analyse is onder meer mogelijk door toepassing van
de functie 'Subtotalen', nadat de gegevens in de tabel eerst gesorteerd werden.
Plaats de muiswijzer in de kolom 'Geslacht' en
sorteer de tabel naar geslacht volgens één van de eerder besproken sorteringstechnieken.
-
Nadat je de tabel hebt gesorteerd op de kolom 'Geslacht', klik je op om het even welke plaats in de tabel
- Klik in het tabblad 'Gegevens' op de opdrachtknop 'Subtotalen'
in de groep 'Overzicht'
- Daardoor wordt het dialoogvenster 'Subtotalen'
geopend.
-
Selecteer in het rolmenu 'Bij iedere wijziging
in:' van het dialoogvenster 'Subtotalen' het veld 'Geslacht' (omdat we de eventuele verschillen tussen mannen en vrouwen willen onderzoeken)
- Selecteer in het rolmenu 'Functie' de optie
'Gemiddelde' (omdat we op zoek zijn naar verschillen in de gemiddelde stagequotering tussen beide geslachten)
- Zorg ervoor dat in het rolmenu 'Subtotaal
toevoegen aan:' enkel de veldnaam 'Stage-gemiddelde' werd aangevinkt (omdat we de gemiddelde quotering voor mannen en vrouwen enkel in de kolom 'Stage-gemiddelde' willen onderzoeken)
- Vink voor alle zekerheid de optie 'Huidige subtotalen vervangen' aan om te voorkomen dat eerdere subtotalen opnieuw worden 'meegenomen'
- Vink de optie 'Overzicht onder de gegevens plaatsen' aan, waardoor ook de gemiddelde stagequotering voor de twee groepen samen zal worden berekend.
- Klik op 'OK'
Excel voegt daardoor aan de tabel drie rijen toe:
-
onder de gesorteerde weergave van de mannelijke
studenten wordt in de kolom 'Geslacht' de melding 'Gemiddelde M' toegevoegd,
terwijl in de kolom 'Stage-gemiddelde' de gemiddelde stagequotering van alle
mannelijke studenten wordt berekend: in ons voorbeeld: 13,9
- in de kolom 'Geslacht' wordt onder de weergave
van alle vrouwelijke studenten een gelijkaardige melding toegevoegd en in de
kolom 'Stage-gemiddelde' de gemiddelde stagequotering voor alle vrouwelijke
studenten: in ons voorbeeld: 12,3
- onder de tabel wordt een rij toegevoegd
waarin het totaalgemiddelde van de stagequoteringen voor alle studenten
samen wordt weergegeven: in ons voorbeeld: 12,8
- Bemerk dat links op het beeldscherm aan de hand van accolades een aantal celbereiken
wordt aangegeven.
Door op een min-teken in deze weergave te klikken wordt de tabel
samengevouwen en worden alleen de subtotalen getoond.
Door op een plusteken te klikken wordt de tabel opnieuw opengevouwen.
- Links boven op het beeldscherm bevinden zich drie overzichtsknoppen
. Door op één van de knoppen te klikken wordt telkens een ander detailniveau van de subtotalen weergegeven.
Hierdoor kan de analyse van de subtotalen makkelijker worden uitgevoerd.
Door in stap 2 van de hoger beschreven procedure niet enkel de veldnaam 'Stage-gemiddelde' aan te vinken in het rolmenu 'Subtotaal toevoegen aan', maar tevens
de veldnamen 'Stagequotering 2' en 'Stagequotering 3', kan een meer
gedetailleerde vergelijking worden opgezet.
Om alle ingevoerde subtotalen opnieuw te verwijderen uit de tabel:
- Klik in het lint op het tabblad 'Gegevens'
- Klik in de groep 'Overzicht' op de opdrachtknop 'Subtotalen'
- Klik in het dialoogvenster 'Subtotalen' op de knop 'Alles verwijderen'
Klik hier om de oefening 'Tabellen' te
maken.
Helpprogramma bij Microsoft Excel 2016: zoek op 'totaalrij', 'tabel converteren naar bereik', 'subtotalen'.
