1: Basis Excel vaardigheden

Lesinhoud en leerdoelen

In deze les maken we een begin met Excel, en we bespreken de basis van dataverwerking.

De vaardigheden die je zal verwerven tijdens deze les:

• Excel installeren
• Datafiles openen in Excel
• Data kopieren en verplaatsen in Excel
• Data organiseren in Excel: transpose
• Formules in excel invoeren zoals gemiddelde, st. dev., etc.
• Cellen bewerken zoals kopiëren, uittrekken, etc
• Data organiseren zoals filteren, sorteren, etc.
• Maten voor centrum (gemiddelde, mediaan)
• Basis data organisatie
• Basis Data veiligheid
• Het gebruik van als, countif en vlookup functies

Voorbereiding

Excel installeren

Zorg dat je de volgende dingen gedaan hebt voor de eerste les:

• Op canvas de datasets voor dit vak gedownload:
• gecheckt hebt of je ingeschreven in Osiris voor de tentamens en hertentamens.
• Excel geinstalleerd (klik hier voor een ietwat oude maar nog bruikbare handleiding). Je kan Excel (heel office) gratis downloaden met je HU-account. Je moet Excel gebruiken op een laptop, niet online (in een internet-browser).
• Excel zo ingesteld dat formules in het Engels moeten en een komma als decimaalteken klik hier voor handleiding. Dan staat je laptop net zo ingesteld als de toetscomputers van de HU. Microsoft heeft bedacht dat als je Excel in het Engels wilt, dat je dan meteen alles in het Engels wilt! Goed bedacht zeg. Maar zie het als een voordeel: je oefent meteen meer met Engels, en dat heb je in het werkveld zeker nodig.
• installeer MSteams als je dat nog niet gedaan hebt.
• De volgende tips uitproberen:
  • Open links in de tekst in een nieuw venster door ctrl (pc) of command (mac) ingedrukt te houden als je er op klikt.
  • op menu-onderdelen klikken in het menu links klapt ze verder uit. Ctrl-klik op voorbeeld pagina links in het menu. Ga naar je nieuwe tab en klik (weer links in het menu) op voorbeeld pagina
• Deze les doorgegeken zodat je een globaal idee hebt wat er behandeld gaat worden. Het doornemen van de lessen doe je VOOR elk werkcollege. Zorg dat je voorbereid naar de lessen komt!

Excel instellen

Voor het onderdeel Statistiek en Excel maken we gebruik van deze reader-website. Je hebt verder Excel nodig en een internetverbinding. Gebruik deze handleiding om je HU account te gebruiken om Office 365 te installeren.

Standaard staat Excel ingesteld op Engels. Wij zullen ook de Engelse versie gebruiken. Staat op je eigen laptop de Nederlandse versie, zet deze dan om naar een Engelse versie (of vertaal de rest van de cursus en op je tentamen alles, dat valt af te raden). Om de datasets te kunnen gebruiken die bij de vragen horen, is het ook belangrijk dat je laptop een komma (en dus geen punt!) gebruikt als decimaalteken. Als je niet weet hoe je dit in moet stellen op je computer, bekijk dan een van de filmpjes hieronder.

Taalinstellingen aanpassen in Windows

Taalinstellingen aanpassen in MacOS

Excel instellen lukt niet

Excel heeft twee language settings: 1 voor alle knoppen en menuutjes (weergave), en 1 voor al je formules en tekst die je typt (ontwerpen). Dus het kan zijn dat al je menuutjes in het Engels zijn, maar je nog steeds Nederlandse formules moet typen. Ga naar bestand > opties > taal. De talen voor weergave en ontwerpen kunnen onafhankelijk van elkaar worden ingesteld. Verander die voor ontwerpen naar Engels.

Excel staat op Engels, maar wil nog steeds punten ipv komma’s

(windows 10: doe het volgende)

• Start > Control Panel / configuratiescherm > Regional and Language Options / land en taal
• klik Additional Settings / meer instellingen
• Bij Decimal Symbol (Decimaalteken), kies een komma : ,
• Bij List Separator, kies een puntkomma: ;

Als je de Windows-instelling niet wilt veranderen, maar alleen voor Excel:

• Druk op de Office-knop
• klik Opties voor Excel
• Maak het keuzevakje leeg bij “Scheidingstekens van systeem gebruiken”
• Bij Decimaalteken, kies een komma : ,
• Bij Scheidingsteken voor duizendtallen, kies een punt: . of niets

Data invoeren

Data is een kostbaar goedje in onderzoeksland. Het is dus belangrijk om je data goed op te slaan, en netjes over te dragen als je over data communiceert!

Een aantal regels voor het delen van excelsheets en andere bestanden met data:

• Als je een verslag inlevert, lever dan ook een excelbestand met de ruwe data in, en een ander excelbestand met je berekeningen / bewerkingen van die data en grafiekjes.
• Zorg dat je data kopieert en niet overtypt waar ook maar enigszins mogelijk. Overtypen zorgt voor fouten.
• Pas nooit je ruwe data aan. Bewaar die in een aparte excelfile, zodat je altijd terug kunt vinden wat je precies op het lab opgeschreven hebt.
• Zorg dat elke kolom in excel waar gegevens in staat, voorzien is van een kolomheader die aangeeft wat er in die kolom staat.
• Herhaal kolomheaders niet. Twee kolommen mogen niet dezelfde header hebben.

Formules in excel

In deze les maken we een start met dataverwerking en met het gebruik van Excel. Excel is een programma dat gebruikt maakt van spreadsheets (zie figuur 1). Zo’n spreadsheet is een digitaal rekenblad, dat bestaat uit 1 of meerdere tabellen. In die tabellen kunnen getallen staan, maar ook berekeningen om op die getallen los te laten.

Excel is een van de meest gebruikte programma’s voor het invoeren en verwerken van data. En Excel kan ook wat statistiek, en grafieken maken.

Een spreadsheet in Excel ziet er zo uit:

Figure 1: Excel spreadsheet met bovenin de menubalk. De standaard instelling is de Home tab. Andere veelgebruikte tabbladen van de menubalk zijn File, Insert, Formulas en Data.

Een spreadsheet in Excel heet een “werkblad” of “sheet”, en bestaat uit “cellen”: allemaal (nu nog lege) hokjes. Die cellen staan in kolommen (vertikaal) en rijen (horizontaal), en elke cel heeft een coördinaat. Een cel in de eerste rij, kolom B, heeft coördinaat B1. In iedere cel kan iets ingevuld worden.

Je kan ook een aantal cellen tegelijk beschrijven. De range D11:D15 omvat alle cellen D11, D12, D13, D14, en D15.

Figure 2: Rijen en Kolommen. Cellen D11 t/m D15 zijn geselecteerd.

Binnen een bestand is het mogelijk om meerdere werkbladen te hebben.

Bekijk de instructievideo: “werkbladen in Excel” als je Excel niet eerder gebruikt hebt:

Opdracht 1

1. Open Excel en maak een leeg werkblad aan met de naam “les1”.
2. Type een getal in cel D5
3. Type 5,2 in cel D10
4. Type =D10*2 in cel D11. Krijg je een foutmelding? klik hieronder

Klik hier als stap 4 een foutmelding gaf

• Controleer eerst of je echt =D10*2 hebt ingetypt.
• Klopt dat, kijk dan naar cel D10. Heb je 5-komma-2 getypt en niet 5-punt-2? Dit is een dingetje in Excel. Excel gaat uit van een bepaalde “decimal separator”, in ons geval als je Excel goed ingesteld hebt: een komma. Ziet Excel dan ergens een punt, dan gaat het er vanuit dat die gegevens tekst zijn (en aligned de tekst aan de linker kant van de cel)..

• Heb je 5-komma-2 getypt en doet hij het alsnog niet? Dan staat Excel misschien niet goed ingesteld. Ga terug naar de startpagina van deze cursus (klik op het logo helemaal links bovenin) en volg de instructies. Werkt dat niet, waarschuw dan je docent.

In de bovenste balk staan de verschillende tabbladen van Excel. Ieder tabblad bevat weer andere opties om de data in Excel te bewerken. Bekijk de instructievideo “navigeren in Excel” voor verdere uitleg als je Excel nog niet eerder gebruikt hebt:

Net als in de andere Microsoft Office programma’s werken shortcut keys. Dit zijn veelgebruikte:

Table 1: Shortkeys in Excel.

functie	toetscombinatie
nieuw werkblad	ctrl+n
alles selecteren	ctrl+a
kopieren van data	ctrl+c
knippen van data	ctrl+x
plakken van data	ctrl+v
laatste actie ongedaan maken	ctrl+z
opslaan	ctrl+s

Type iedere 5 minuten ctrl + s zodat je werk opgeslagen wordt !!

Een werkblad Kopiëren

Opdracht 1

Doe mee met de volgende stappen tenzij je al een doorgewinterd werkblad-kopieerder bent.

• Ga onderin beeld met de cursor op de naam van het werkblad staan (die je wilt kopiëren)
• klik op de rechtermuisknop
• Select “Move or Copy”

Figure 3: Een werkblad kopiëren binnen een Excelbestand

- Vink onderaan het venster de optie “Create a copy” aan - Selecteer bij “To book:” het bestand waarin je werkt (of een ander bestand als je je werkblad naar een andere Excelfile wilt kopiëren.) - Selecteer waar het werkblad geplaatst moet worden in je bestand, bijvoorbeeld “move to end” (zie figuur 5). Klik op “OK” - Er verschijnt nu een extra werkblad met de naam van het originele werkblad met (2) erachter

Excel heeft honderden verschillende formules die op zowel numerieke als tekst data toegepast kunnen worden. Voor gedetailleerde informatie / instructies over het gebruik van formules in Excel, raadpleeg je deze website.

De formules kan je vinden in de menu balk onder Formulas. Onder iedere categorie staan een lijst met formules. Bij de optie More Functions staat nog een lijst met categorieën waaronder Statistical. Hieronder staan veelgebruikte formules zoals het gemiddelde (average).

In de les maken we vaak gebruik van de Engelse namen van de formules. Als je de Nederlandse naam van de formule wilt weten, kun je deze website raadplegen of kijken op het formuleblad.

Er zijn twee manieren om een functie te gebruiken in Excel. In beide gevallen is de eerste stap het selecteren van een lege cel in je Excel werkblad.

Manier 1

Als je de naam van een functie weet, kan je de formule direct invullen in de cel. Type in de cel het = teken gevolgd door de naam van de formule. Na de naam van een formule volgt een linker haakje, selectie van de data en als laatste haakje sluiten.

Manier 2

Je kunt ook de Insert Function van de Formulas balk gebruiken.

• Selecteer een lege cel in Excel.
• Klik op de Insert Function van de Formulas balk.

• Selecteer een functie uit het keuze menu (bijvoorbeeld AVERAGE).

Er verschijnt een venster voor de Function Arguments (zie figuur 1e). Function Arguments zijn alle gegevens die een functie nodig heeft om de functie uit te voeren. Voor de functie AVERAGE zijn dat de meetwaarden waarvan het gemiddelde uitgerekend moet worden.

• Selecteer met de cursor de cellen waarvan het gemiddelde moet worden uitgerekend.

In plaats van een formule te zoeken met de Insert Function van de Formulas balk, kun je de functie die je wilt gebruiken ook direct aanklikken in de Formulas balk.

Het gemiddelde berekenen

Hierboven hebben we laten zien hoe je een formule invoert in Excel met als voorbeeld de functie van het gemiddelde AVERAGE(). Tussen de haakjes worden de cellen genoteerd waarvan het gemiddelde uitgerekend moet worden. De functie AVERAGE() negeert lege cellen en tekst maar niet het nummer 0. Indien aanwezig wordt de 0 waarde ook gebruikt voor het uit uitrekenen van het gemiddelde.

Je kunt het gemiddelde van een aantal cellen als volgt berekenen:

• Selecteer de cel waarin het gemiddelde moet komen te staan.
• Typ in deze cel =AVERAGE(
• Selecteer met de cursor de cellen met data waarvan je het gemiddelde wilt berekenen.
• Sluit af met een ) en druk op Enter.

Als alternatief kan je de invoer cellen ook individueel selecteren:

• Selecteer de cel waarin het gemiddelde moet komen te staan.
• Typ in deze cel =AVERAGE(
• Selecteer met de cursor de eerste cel.
• Typ ;
• Herhaal de vorige twee stappen totdat je alle data hebt geselecteerd.
• Sluit af met een ) en druk op Enter.

Opdracht 1

Importeer de data uit staartlengtes.txt in Excel en bereken de gemiddelde staartlengte van de katten in deze steekproef met een formule.

Klik hier voor het antwoord

31.36 cm (correct afgerond: 31 cm. merk op dat Excel niet automatisch voor je afrondt)

Krijg je 30.5? dan heb je waarschijnlijk vergeten om de decimal separator aan te geven bij het importeren. Importeer de data uit de .txt file nog een keer. Check hier bij figuur 14 hoe je de decimal separator ook al weer instelt bij het importeren.

Data in Excel krijgen

De eerste stap in data analyse is het invoeren van je data. Je kunt dit op verschillende manieren doen:

• data intypen (als het niet zoveel is, of tijdens het labwerk)
• data kopieren en plakken
• data importeren

Cellen kopiëren, uittrekken, etc

Als je een kleine dataset hebt die je wilt analyseren kan je de getallen handmatig invullen. Vaak is het ook mogelijk om de data van een bestand te kopiëren en direct te plakken in Excel.

Stel, je leerteam verzamelt de volgende data (waarschijnlijk voor een beschrijvende onderzoeksvraag. welke onderzoeksvraag denk je?):

Table 2: Bepaling van het alcohol % van een fles rode wijn door 5 studenten

Student	perc_alcohol
Boris	11.2
Evelien	13.9
Iris	12.5
John	12.3
Marcel	11.9

Opdracht 1

Data intypen

• Open een nieuw werkblad.
• Plaats de cursor in “cell” A1 en type student.

Figure 4: Selectie van de cell met de cursor. Vul de cel met tekst of getallen

• Met de pijlen op je toetsenbord of met de muis selecteer je cell B1 en type perc_alcohol
• Selecteer de cellen A1:B1 (klik op A1, hou de muis ingedrukt, sleep naar B1, laat de muis los) en maak de tekst dikgedrukt (bovenin beeld of ctrl+b)
• vul nu de tabel aan met de data uit tabel 2 hierboven.

Figure 5: Handmatig invullen van de gegevens van Tabel I

Opdracht 1

Data kopieren

• Selecteer alle data in de tabel in Excel (klik A1, hou de muis ingedrukt, sleep naar B6, laat los). kopieer deze data (ctrl+c) en plak hem op cel D1 (ctrl+v). Als het goed is vult Excel nu automatisch cel D1:E6
• Maak nu een nieuw werkblad aan. Selecteer weer alle data en kopieer en plak de data in het nieuwe werkblad.

Figure 6: Een nieuw werkblad maken binnen een Excelbestand

• Selecteer weer alle data en kopieer (ctrl+c). Klik wat rond in een leeg werkblad, type op een random plaats je naam en probeer dan de data te plakken (ctrl+v). Je zult merken dat dit niet werkt. Excel onthoudt niet wat je gekopieerd hebt als je tussendoor andere dingen gaat doen.
• Probeer nu de informatie uit de tabel hier op de website te kopieren en plakken naar cel G1. Lukt dat? Soms lukt het wel, soms lukt het niet. Opletten dus als je dit doet! Check altijd of Excel de cijfers herkent als cijfers en niet denkt dat het tekst is (aligned rechts in de cel = cijfer).

Opdracht 1

Data verplaatsen

Selecteer weer je cellen met data en probeer ze naar een andere plek in het werkblad te verplaatsen:

• selecteer de data
• ga met de cursor op de rand staan van de geselecteerde data totdat de cursor verandert van een wit kruis naar een zwart kruis in de vorm van pijlen.
• Als de cursor de vorm heeft van een kruis met pijlen, Hou je de linkermuisknop continue ingedrukt en sleep met de muis of touchpad de data naar de gewenste “cell” in het werkblad.

Data wordt niet altijd op een overzichtelijke manier aangeleverd. Een veelvoorkomende handeling is het wisselen van de kolommen en de rijen (transpose). Een goede organisatie van de data betekent dat iedere observatie (hier een meting van een student ) in rijen staat en de meetwaarde in kolommen. In figuur 7 staat het juist andersom en dat maakt verdere bewerking van de data lastiger (maar niet onmogelijk).

Figure 7: Voorbeeld van data die je zou willen transponeren (transpose). De meetwaarden staat in een rij en de observaties in een kolom weergegeven.

Opdracht 1

Transpose

• open excelbestand verkeerdom.xlsx
• Selecteer en kopieer de data (A1:F2)
• klik op cel A4
• klik op het pijltje v onder Paste linksboven in beeld

Figure 8: Verschillende Paste Options. Met rood gemarkeerde icoontje is Transpose

• De data is nu gekopieerd waarbij de kolommen en rijen zijn verwisseld

Figure 9: Resultaat van de Transpose optie: kolommen en rijen zijn verwisseld.

Kopieren-plakken naar excel is een snelle manier om data in te voeren, maar het gaat ook makkelijk mis! Controleer dus altijd of het goed gegaan is als je dit doet.

Openen en importeren

Data handmatig invoeren is foutgevoelig: je maakt makkelijk een typefoutje. Je kan data ook laten importeren door Excel, als je die al in een ander bestand hebt. Oefen hier goed mee!

Excel kan verschillende type bestanden openen. De meest voorkomende data formats zijn

• .xlsx / .xls bestanden: Excel bestanden (ook van eerdere versies)
• .txt bestanden: De kolommen zijn meestal gescheiden door een Tab
• .csv bestanden (Comma Separated Values - De kolommen zijn gescheiden door een comma (,) of een punt-comma (;)

Als je een Excel bestand wilt openen:

• Klik op de File tab (links bovenin)
• klik op open en dan op browse

Figure 10: Klik op Browse om Excel bestanden te zoeken en te openen

• Navigeer door je folders en selecteer je Excel bestand. Klik op “Open”

Als je een .txt bestand wilt openen:

Bij tekstbestanden is het even goed opletten: welk tekentje wordt gebruikt als decimal separator? En met welk teken zijn de kolommen gescheiden? De volgende kolommen zijn bijvoorbeeld gescheiden met een #

persoon1#5

persoon2#7

persoon3#8.5

Gebruikelijker zijn een Tab, een comma (,) of een punt-comma (;) maar open dus altijd eerst je tekstbestand in bijvoorbeeld Notepad (programma op de PC) of TextEdit (Mac) om het te bekijken.

Opdracht 1

Zoek in de gedownloade datamap het textbestand /les1/alcoholpercentages.txt en doe mee met de volgende stappen:

Stappen om een textbestand te openen:

• Open het textbestand in Notepad (Windows) of TextEdit (Mac) en kijk welk teken er tussen de kolommen staat en wat de decimal separator is.
• In Excel: klik File tab –> open –> browse
• Selecteer “Text Files” in het drop down menu rechtsonder

Figure 11: Standaard zoekt Excel alleen naar Excel-bestanden

• Zoek en selecteer het txt bestand dat je wilt openen, klik op open.

• Na het openen van het tekstbestand verschijnt de “Text Import Wizard”. Kies in het eerste scherm of de kolommen in je textbestand een delimiter gebruiken of altijd even breed zijn (meestal dat eerste) en of er “headers” (kolomnamen in de eerst rij) in staan. Kies en klik op next.

Figure 12: De Text Import Wizard

• kies voor de juiste “delimiter” (wat staat er tussen de kolommen). klik next (nog niet finish).

Figure 13: Kies de juiste delimiter, in dit geval een ;

• Nu staan de “decimal separators” nog verkeerd (er staat 11,2 en niet 11.2). Klik op Advanced:

Figure 14: Text import Wizard scherm 3/3. We zijn er bijna.

• kies bij decimal separator het teken dat je textdocument gebruikt. Excel zet die dan om in het teken dat Excel gebruikt.

Figure 15: Kies de juiste decimal separator, in dit geval een komma

• Als het goed is heb je nu een werkblad dat er precies zo uit ziet als het werkblad waarin je de data handmatig getypt had (minus de dikgedrukte letters in rij 1).

• Heb je de verkeerde delimiter gekozen, dan is alles in 1 kolom gepropt:

Figure 16: Verkeerd geimporteerde data

decimaal symbool en het duizend scheidingsteken

In Nederland gebruiken standaard de komma als decimaal symbool en de punt als duizend scheidingsteken. (Dus wij zouden typen: 10.000,5 voor tienduizend-en-een-half) In andere landen waaronder de Verenigde Staten is dit precies andersom: de punt als decimaal symbool en de komma als duizend scheidingsteken (10,000.5). Op sommige computers staat de Amerikaanse versie ingesteld. Dit geeft problemen als we data met decimalen willen invoeren (Excel denkt namelijk dat het duizendtallen zijn).

Controleer dus bij het invoegen van data goed of je getallen niet opeens 1000x zo groot zijn geworden! Is dat gebeurt, gebruik dan de optie om de thousands separator in te vullen (te zien in figuur 15)

Help, ik heb het verkeerd geimporteerd en nu heb ik toch ergens een kolom met tekst ipv nummers:

Als je data hebt geimporteerd maar je krijgt alle waardes in een kolom dan kan je excel deze waardes laten opsplitsen op basis van een scheidingsteken. Gebruik hiervoor ‘text to column’ in het tabblad ‘Data’.

Excel als rekenmachine

Nu we data in excel kunnen krijgen, kunnen we eens wat beschrijvende statistiek gaan doen! Dan moeten we wat gaan rekenen.

Met Excel kun je eenvoudig berekeningen uitvoeren. Hieronder leggen we stap voor stap uit hoe je dat doet.

• Typ het = teken in een cel.
• Typ de berekening.
• Druk op Enter

De uitkomst van de berekening verschijnt in de cel A1. De onderliggende berekening wordt weergeven in de formule balk (zie rode vierkant in het figuur hieronder).

Als alternatief kan je de cijfers eerst in Excel typen en de berekening in een aparte cel doen. Dit is overzichtelijker omdat je direct ziet welke getallen er gebruikt zijn voor de berekening. In kolom A en B staan de data. In kolom C is de berekening ingevoerd zoals rechtsboven in de formule balk is weergegeven.

Je kan altijd dubbel klikken op een cel om te zien wat er daadwerkelijk in een cel zit: data of een formule. Dubbel klikken op C1 laat in dit geval de formule zien en geeft met welke data er gerekend wordt (cel A1 en B1).

We kunnen ook de getallen van elkaar aftrekken, zoals gebeurt in C2:

In C3 worden de getallen in A3 en B3 met elkaar vermenigvuldigd:

In C4 worden de getallen door elkaar gedeeld:

Ten slotte wordt in C5 gedemonstreerd hoe je een machtsverheffing kunt uitvoeren in Excel:

Worteltrekken kan op twee manieren worden uitgevoerd. Ten eerste kan je worteltrekken door middel van machtsverheffen. Als je een grondgetal heft tot de macht 0,5 is dat hetzelfde als worteltrekken van het grondgetal:

Als alternatief kan je ook de Excel formule gebruiken voor het uitrekenen van een wortel, sqrt():

Met de uitkomsten van een berekening kunnen nieuwe berekeningen worden uitgevoerd:

Door dubbel te klikken op een cel kun je de inhoud zien (data of een formule):

Als we de andere getallen in kolom C ook willen vermenigvuldigen met 10 dan kunnen we de formule in cel D1 kopiëren:

Vervolgens selecteer je de cellen waar je de formule naar toe wilt kopiëren:

En plak je de formule:

Volgorde van berekeningen in Excel

Excel volgt BEDMAS om de volgorde van de berekeningen te bepalen:

• Brackets (= haakjes)
• Exponents (= machten)
• Division (= delen)
• Multiplication (= vermenigvuldigen)
• Addition (= optellen)
• Subtraction (= aftrekken)

Bij twijfel is het dus belangrijk om haakjes te gebruiken!

Voorbeeld 1

• 3^2 *2 / 6 -2 + 9 =
• 9 * 2 / 6 -2 +9 =
• 18 / 6 -2 + 9 =
• 3 - 2 + 9 =
• 1 + 9 = 10

Voorbeeld 2

• 3^(2*2) / 6 -2 + 9 =
• 3^4 / 6 -2 +9 =
• 81 / 6 -2 + 9 =
• 13,5 - 2 + 9 =
• 11,5 + 9 = 20,5

Voorbeeld 3

• 3^ 2 *2 / (6 -2) + 9 =
• 3 ^ 2 * 2 / 4 +9 =
• 9 * 2 / 4 + 9 =
• 18 / 4 + 9 =
• 4,5 + 9 = 13,5

Opdracht 1

Type

= 3^ 2 *2 / (6 -2) + 9

in Excel en kijk of het antwoord klopt met de berekening hierboven.

Data rapporteren

Hieronder een voorbeeld van een tabel die niet goed is opgemaakt

Opdracht 1

Waarom is dit geen nette excelsheet?

Klik hier voor het antwoord

• twee kolommen hebben geen kolomheader. Is dat ook teenlengte? of misschien wat anders? Joost mag het weten.
• kolomheaders worden herhaald (persoon)
• het is onduidelijk wat de eenheid is
• er staat een notitie in dezelfde cel als een datapunt. Dat zorgt ervoor dat je niet meer automatisch met dat datapunt kunt rekenen.

Dit zou beter zijn:

Dit is in ieder geval duidelijk. Je kan niet per ongeluk denken dat er wat anders in de kolommen staat.

Het worden wel een beetje lange kolomnamen, maar dat is beter dan onduidelijkheid.

Een nettere oplossing ( voor nu nog niet verplicht, maar wel sterk aangeraden) is om je data “tidy” op te slaan: 1 variabele per kolom, 1 observatie per rij, 1 waarde per cel:

Filteren en sorteren

Filteren

Table 3: Excel sorteer-functie voorbeeld tabel. Personen werken op een afdeling, je kunt deze data filteren op bijvoorbeeld de afdeling ‘ICT’.

A	B
Naam	Afdeling
Karel	ICT
Maria	HR
Lisa	ICT
Tom	Marketing

Selecteer de tabel (van A1 tot B6), daarna ga je naar het tabblad “Gegevens” en gebruik de filter functie. Als je op de filter functie klikt dan krijg je een dropdown functie per colum in je geselecteerde data. Hier kan je specificeren op welke variabele je wilt filteren, hierdoor zal alleen de specificeerde variabele zichtbaar worden.

Sorteren

Je kan je data in excel sorteren en/ of filteren. Soms is het bijvoorbeeld handig om je data serie te sorteren van klein naar groot, denk maar aan leeftijden, dan is het handiger om de data serie te sortern van jong naar oud (of oud naar jong) in tegenstelling tot een leeftijden lijst die willekeurig is gesorteerd. In excel kan je data sorteren door de data serie te selecteren en via het tabblad “Gegevens” de “sorteren” functie te gebruiken. Met deze functie kan je data op verschillende manieren sorteren, probeer maar eens een aantal verschillende sorteer methodes toe te passen op wat oefen data.

Verschillende soorten data

Statistiek is reken met een grote set van getallen, denk aan het aantal personen in een provincie of land waar je iets over wilt zeggen. In een notendop zijn er twee verschillende vorm van statistiek, inductieve statistiek en beschrijvende statistiek, in het engels inferential statistics en descriptive statistics genoemd. Tijdens het eerste jaar van de opleiding zullen we ons vooral richten op die laatste: beschrijvende statistiek.

Om toch alvast een beetje en beeld te krijgen tussen de twee verschillende methodes hebben we hieronder een korte beschrijving geven.

Inductieve statistiek

Als je een steekproef doet, heb je niet de hele populatie gemeten. En elke steekproef gaat dus andere data opleveren. Maar je vraag ging over de populatie! Hoe kun je dan toch iets zeggen over die populatie? Dus in het plaatje hierboven: kun je op basis van die 4 kangaroes iets zeggen over de kleuren in de hele populatie? En hoe betrouwbaar is die inschatting? Hiervoor gebruik je inductieve statistiek: de bewerking van data uit een steekproef om een uitspraak te doen over een eigenschap van een populatie. Dit is waar mensen aan denken bij het woord “statistiek”.

Beschrijvende statistiek

Beschrijvende statistiek is de term die wordt gegeven aan de analyse van gegevens waarmee gegevens op een zinvolle manier kunnen worden beschreven, weergegeven of samengevat. Denk bijvoorbeeld aan het beschrijven van de staartlengte van 20 Utrechtse katten met een gemiddelde. Het CBS is bekend om de grote hoeveelheid beschrijvende statistiek.

Beschrijvende statistiek heeft altijd betrekking op de werkelijk gemeten waardes en kan niet gebruikt worden om hypotheses te testen of om een uitspraak te doen over de populatie aan de hand van een steekproef. Beschrijvende statistiek laat het niet toe om conclusies te trekken die verder gaan dan de gegevens die we hebben geanalyseerd. Dan zou je namelijk inductieve statistiek aan het doen zijn. Ze zijn gewoon een manier om gegevens te beschrijven over de populatie (alleen als je de hele populatie gemeten hebt) of de steekproef.

Kwalitatieve data beschrijven

Ordinale data wordt vaak foutief behandeld alsof het continue data is. Een veel gebruikt voorbeeld zijn waarde-oordelen, zoals “geef deze hogeschool een beoordeling op een schaal van 1 tot 5”. De neiging is dan snel om vervolgens te kijken wat de hogeschool gemiddeld voor cijfer haalt. Maar zo werkt een ordinale schaal niet. Want is het verschil tussen 2 en 3 sterren net zo groot als het verschil tussen 4 en 5 sterren? Geen idee. Een ster is geen meetbare eenheid.

Is zo’n gemiddelde reviewscore daarom onbruikbaar? Dat is een andere vraag. Het geeft wel een indicatie van hoe “hoog iets scoort”, maar hoe hoog dat dan precies is, blijft onduidelijk. Met onderzoeksdata is het wel belangrijk om het preciezer aan te pakken.

Over het algemeen beschrijf je kwalitatieve data met percentages. Bijvoorbeeld: 60% van de klas heeft bruine ogen, 30 procent heeft blauwe ogen.

Ook ordinale data kun je zo beschrijven:

Figure 17: Descriptive statistics van ordinale data

Kwantitatieve data beschrijven

Kwantitatieve data beschrijven hebben we al gedaan in eerdere lessen: bijvoorbeeld door een centrum-maat te berekenen (zoals gemiddelde, of mediaan) of een maat voor de spreiding te berekenen (zoals standaarddeviatie). Die gebruik je vooral voor continue data, maar het kan ook voor discrete data. Hoewel je altijd een aantal huisdieren in huis hebt uitgedrukt in gehele getallen (bijv 0, 1, 2 of 3), kunnen mensen in Utrecht best gemiddeld 1,2 huisdier hebben.

Maten voor centrum

Gemiddelde

Het is belangrijk te begrijpen waarom je een centrum-maat wilt gebruiken. De centrum-maat wil je gebruiken omdat je over de waardes van je data wilt praten, het is onmogelijk om de hele lijst van getallen op te noemen als je wilt aangeven hoeveel huisdieren mensen in Utrecht hebben. Dus willen we een getal gebruiken dat een indicatie geeft over de serie van getallen. Een gemiddelde is een centrum-maat die we goed kunnen gebruiken voor een serie van getallen waarvan de waardes redelijk dicht bij elkaar zitten. Als je een gemiddelde gebruikt voor een serie getallen met hele grote verschillen dan resulteerd dat in een gemiddeld getal wat eigenlijk geen reflectie is van de serie getallen, daar heb je dus niet zo veel aan. Om een extreem voorbeeld te geven; we onderzoeken het salaris van een selectie van 1.000 Nederlandsers. We vragen hoe hoog hun salaris is, hieruit blijkt dat 999 Nederlanders ongeveer €1.000,- per maand verdienen, bij de een is het een euro minder en bij de ander is het een euro meer, maar over het algemeen ligt het rond de €1.000,-. Er is ook 1 Nederlander die is super rijk en verdient 1 miljoen euro per maand. Als we het gemiddelde uitrekenen van de 999 Nederlanders zal dat gemiddeld uitkomen op €1.000,-, en dat klopt (+/- €1.000,- * 999 personen = €999.000 -> €999.000/ 999 personen = €1.000,-). Als we dan die ene hele rijke persoon eraan toevoegen gebreurt er dit; +/- €1.000,- * 999 personen + €1.000.000 = €1.999.000, als we dat delen door 1.000 personen dan krijgen we een gemiddelde van ongeveer €2.000. Hmmmm… als we naar de set van getallen kijken dan zien we dat eigenlijk iedereen €1.000 verdiend, €2.000 is daar best ver vandaan. Behalve die ene hele rijke persoon, die verdiend €1.000.000 maar dat is ook ver weg van €2.000. Nu hebben we dus een gemiddeld getal berekend dat niks zegt over de meeste van onze getallen EN ook niet over dat ene bijzonder getal. Dit gemiddelde zegt dus eigenlijk helemaal niks. In een geval zoals hierboven kunnen we dus beter de mediaan gebruiken.

Mediaan

Sorteer de sersie van getallen van groot naar klein en pak het getal op de middelste positie en gebruik dat als reflectie voor de serie van getallen. In ons voorbeeld hierboven hebben we dus 1.000 personen ondervraagd en dus hebben we 1.000 getallen. Als ik die sorteer en het middelste getal wil pakken hebben we een probleem. Er is namelijk geen getal dat exact in het midden zit; 1, 2, 3, 4, welk getal zit in het midden? Die is er niet want we hebben een even aantal, het middelste getal zit een soort tussen getal 2 en getal 3. In dit geval kijken we naar de twee getallen die rondom de middenpositie zitten en we nemen het gemiddelde van deze twee getallen. In het salaris voorbeeld zijn de twee getallen die rondom de midden positie liggen beide €1.000, en hier nemen we dan het gemiddelde van; (€1.000 + €1.000)/2 = €1.000, de mediaan is dan dus €1.000. Als we een oneven aantal getallen hebben dan kunnen we wel het getal pakken dat op de middelste positie zit; 1, 2, 3, welk getal ligt op de middelste positie? het getal 2, de mediaan zou dan dus 2 zijn. Terug naar het salaris voorbeeld; als de mediaan €1.000 is dan is dat een betere reflectie voor de serie van getallen dan de €2.000 die we kregen vanuit het gemiddelde. Inderdaad, dat ene speciale geval van €1.000.000 per maand valt dan wel buiten beschouwing, dat is niet erg, want we zijn opzoek naar een getal dat een reflectie is voor de totale serie van getallen.

Basis data organisatie

Labeling

Zorg ervoor dat je alle files en folders een logische unieke naam geeft. Labeling lijkt misschien niet zo belangrijk maar als je een gigantische verzameling aan files en folders hebt dan is het de bedoeling dat je over 3 maanden nog steeds je files en folder kunt teryg vinden. Dat lukt alleen als je logische unieke labels hebt gebruikt. Als voorbereiding van “machine readable” data opslag is het “good practise” als je er voor zorgt dat je geen spaties gebruikt in voor de labeling van files en folders. De spaties kun je vervangen door underscore. Zo is het beter om een folder “old_archive” te noemen i.p.v. “old archive”.

Folder structuur

In essentie gebruiken we heel vaak dezelfde kapstok voor het opzetten van een goede folderstructuur. Er zijn namelijk een aantal folders die we eigenlijk altijd nodig hebben. Je kunt dus de volgende folder structuur overnemen en uitbreiden zoals je dat zelf handig vind.

1. ruwe_data
2. analyse
3. old_archive

ruwe_data - Hier bewaar je experiment data, deze data is vanaf je meetinstrument gekomen en is ongewijzigd. Je kan deze data bijvoorbeeld een label geven met de suffix “ori”. Zo is het beter om je originele data het label “meet_data_ori.csv” te geven i.p.v. “meet_data.csv”. Zonder de label “ori” is het niet duidelijk dat het de originele data is.

analyse - In deze folder kopieer je de data en organiseer je de analyse resultaten, zorg voor duidelijke labeling zodat je weet welke analyse je hebt gedaan op je data.

old_archive - Dit is een soort prullenbak folder, als je een oude versie van een analyse niet meer gebruikt maar ook niet definitief wilt weggooien dan kan je die hier bewaren.

Basis Data veiligheid

Backup

bedenk je goed waar je de data bewaard en of het daar veilig staat. met veiligheid bedoelen we vooral; je laptop kan stuk gaan en dan ben je al je data en je werk kwijt. Zorg er dus voor dat je een kopie van je data en je werk op een extra plek bewaard. bvb. bewaar je data en je analyse op je laptop en een kopie van al je data en analyse op een andere machine (denk aan onedrive, dropbox of een andere onlinestorage). Maar ook het gebruik van de ruwe_data folder is een vorm van backup, je werkt in een kopie van het originele bestand. Zo kan je altijd terug naar je originele data.

Privacy

Maar je kan ook denken aan privicy gevoelige data, waar kan je die het beste opslaan, je wilt natuurlijk niet iemand zijn naam, adres gegevens en gezondheidsgegevens zomaar ergens online zetten. Probeer te bedenken welke acties je kan ondernemen om met dit issue om te gaan.

IF-functies

Excel kan veel verschillende analyses uitvoeren op data die je aanreikt. Dat doen we bvb door het gebruiken van een formule waarmee we een rekensom op de data kunnen uitrekenen. Maar je kan ook een conditie meegeven waarop je een dataset wilt filteren, denk bvb aan “als”; alleen als de waarde hoger is dan 3 dan wil je iets doen met je data. Dit kan Excel ook.

Table 4: Excel IF-functie voorbeeld tabel. Personen hebben scores en op basis van de score kunnen we aangeven of de persoon geslaagd is of niet (score >= 60 -> geslaagd, anders niet geslaagd).

A	B	C
Naam	Score	Resultaat
Karel	58
Maria	75
Lisa	62
Tom	47

Als dit de data is waar we een conditie op toe willen passen zoals een IF-functie, dan gebruiken we die op de volgende manier;

=IF(B2>=60, “Geslaagd”, “Niet Geslaagd”)

Deze formule kunnen we invullen in de formulebalk van excel voor de cel C2 en we kunnen de formule toepassen op de hele serie data door C2 te selecteren en dan het dikke puntje rechtsonderin naar beneden te slepen of er dubbel op te klikken. Probeer met deze functie alle personen in de tabel te labelen met “Geslaagd” of “Niet geslaagd”

COUNTIF-functie

De COUNTIF functie telt het aantal cellen in een bereik die voldoen aan een bepaalde voorwaarde.

COUNTIF(bereik, criterium)

bereik: Het bereik van cellen waarbinnen je wilt tellen. criterium: De voorwaarde waaraan de cellen moeten voldoen om geteld te worden.

Voorbeeld

Stel je hebt een lijst met bestellingen in kolom A (A2:A10) en je wilt weten hoeveel keer het product “Appel” is besteld.

Table 5: productenlijst.

A
product
Appel
Banaan
Appel
Sinaasappel
Banaan
Appel
Druif
Appel
Banaan

=COUNTIF(A2:A10, “Appel”)

Deze formule telt het aantal keren dat “Appel” voorkomt in het bereik A2. Het resultaat is 4.

In Excel zijn er meerdere COUNT-functie varianten:

• = count(range) : telt het aantal cellen met een cijfer. Negeert lege cellen
• = counta(range) : telt het aantal cellen met data (zowel tekst als cijfers). Negeert lege cellen
• = countif(range;voorwaarde) : telt het aantal cellen dat voldoet aan een bepaalde voorwaarde

VLOOKUP-functie

De VLOOKUP (Vertical Lookup) functie zoekt een waarde in de eerste kolom van een bereik en retourneert een waarde in dezelfde rij uit een andere kolom van dat bereik.

VLOOKUP(zoekwaarde, tabelmatrix, kolomindex_getal, [exact_zoeken])

zoekwaarde: De waarde die je zoekt. tabelmatrix: de totale tabel waarbinnen je wilt zoeken. kolomindex_getal: de kolom waarvan je een waarde wilt selecteren. [exact_zoeken] (optioneel): TRUE of FALSE waarde. TRUE zoekt naar een benaderde overeenkomst. FALSE zoekt naar een exacte overeenkomst.

Voorbeeld

Stel je hebt een tabel met productgegevens waarbij de product-ID’s in kolom A staan en de bijbehorende prijzen in kolom B. Je wilt de prijs opzoeken van een product-ID 1003 die in cel D1 staat.

Table 6: Gegeven data.

A	B
productID	prijs
1001	5.99
1002	3.49
1003	7.89
1004	6.75
1005	2.99

=VLOOKUP(D1, A2:B6, 2, FALSE)

De cel D1 bevat het product-ID, we evalueren de tabel van A2 tot B6, als we een hit hebben op ID 1003 laat dan de prijs uit kolom 2 zien, FALSE want we willen een exact vergelijk. In de cel waar je de formule gebruikt krijg je dan 7.89 want dat is de ID hit.

Hieronder een aantal oefenopdrachten om bekent te worden met het gebruik van Excel formules. Niet alle formules die Excel heeft kunnen we hier bespreken, zorg er dus voor dat als je een formule tegenkomt die je niet kent deze opzoekt op internet.

Opdracht 1

Het Excel-bestand leeftijd_geslacht.xlsx bij deze opdracht bevat informatie over de leeftijd en geslacht van eerstejaars studenten van het Instituut Life Science en Chemistry.

1. Bereken de gemiddelde leeftijd van de eerstejaars studenten van de opleidingen Life Science en Chemie/Chemische Technologie.

2. Heeft het zin om het gemiddelde geslacht van de eerstejaars studenten te berekenen? Denk hierbij aan de type data.

Klik hier voor het antwoord

1. beiden 18 jaar

2. je kan geen gemiddelde berekenen van nominale data

Opdracht 1

Hoeveel eerstejaars studenten van de opleidingen Life Science en Chemie/Chemische Technologie bij elkaar zijn ouder dan 18?

tip: gebruik de volgende formule: =COUNTIF(C2:C297;“>18”)

Klik hier voor het antwoord

37

Opdracht 1

Welke percentage van de eerstejaars studenten van de opleidingen Life Science en Chemie/Chemische Technologie bij elkaar zijn ouder dan 18?

Klik hier voor het antwoord

0,125 dus 12,5 %

Opdracht 1

Welke percentage van de eerstejaars studenten Life Science is vrouw?

tip: De sum() functie telt de waarde van geselecteerde cellen bij elkaar op. Het negeert tekst en lege cellen. Je kunt sum() hier gebruiken om het aantal vrouwen te tellen.

Klik hier voor het antwoord

60 %

Opdracht 1

Welke percentage van de eerstejaars studenten Chemie/Chemische Technologie is man?

Klik hier voor het antwoord

69%

Werkcollege casus iris

1. Download de iris dataset van internet in .csv format en lees de file via Excel
2. Bereid de data voor om er een analyse op uit te voeren als dat nodig is
3. Bereken het gemiddelde en de mediaan voor alle 3 de iris soorten met een Excel formule
4. Laat zien wat het effect is van een uitschieter op het gemiddelde en de mediaan met een grafiek
5. Zet een degelijke folder structuur op waarin je deze analyse bewaard. Geef daarbij aan welke punten van data veiligheid van belang zijn in deze setting.