In deze samenvatting worden de meeste oefeningen (NIET ALLE OEFENINGEN) die in de lessen van Data Skills XMM2 worden behandeld, getoond in stappen.
Het zijn screenshots met een korte uitleg wat er wordt getoond. Er wordt wel verwacht dat er kennis is van Excel.
,INHOUDSOPGAVE
Les 1: herhaling Excel .............................................................................................................................. 9
Functieberekeningen deel 1 .......................................................................................................................................... 9
Bereken met een Excel-functie het totaal van de salarissen ....................................................................................................... 9
Bereken met een Excel-functie het gemiddelde van de salarissen .............................................................................................. 9
Bereken met een Excel-functie het hoogste salaris ..................................................................................................................... 9
Bereken met een Excel-functie het laagste salaris .................................................................................................................... 10
Berekeningen en doorvoering ..................................................................................................................................... 10
Bereken hoeveel de onkosten bedragen tegenover de salarissen ............................................................................................ 10
Voer deze berekening door ....................................................................................................................................................... 10
ALS – En – OF functie ................................................................................................................................................... 11
Bereken de premie: als men gehuwd is en 2 of meer kinderen (premie van 5% op het jaarsalaris), als men gehuwd is en 4 of
meer kinderen heeft (premie van 10% op het jaarsalaris) ........................................................................................................ 11
Datum en tijd ............................................................................................................................................................... 11
Voeg een kolom tussen geboortedatum en geslacht. Bereken de leeftijd van de werknemer ................................................. 11
Voeg een kolom tussen jaarsalaris en in dienst. Bereken de van de werknemer ...................................................................... 12
Validatie....................................................................................................................................................................... 12
Maak gebruik van een validatie onder geslacht waarbij men enkel kan kiezen uit man of vrouw........................................... 12
Celopmaak (voorwaardelijke opmaak)........................................................................................................................ 12
Selecteer de hele tabel en zorg voor alle randen ...................................................................................................................... 12
Selecteer de titelrij en zorg voor zwarte achtergrond met witte, vetgedrukte letters .............................................................. 13
Selecteer het bereik A6:R54 en zorg voor volgende voorwaarde en opmaak: indien het onkostenbedrag per rij groter is dan
het gemiddelde van de totale onkosten dan kleur je de hele rij oranje geel............................................................................. 13
Afronden...................................................................................................................................................................... 14
De onkosten rond je naar boven af met 2 decimalen ............................................................................................................... 14
De leeftijd en Anciënniteit rond je naar beneden af op 0 decimalen ........................................................................................ 14
Zoeken ......................................................................................................................................................................... 14
Zoek op basis van het ID telkens naar de naam en onkosten ................................................................................................... 14
Zoek met een functie de gemiddelde kosten ............................................................................................................................. 14
Functieberekening deel 2 ............................................................................................................................................ 15
Bereken met een functie het aantal mannen en vrouwen ........................................................................................................ 15
Bereken met een functie het gemiddelde Jaarsalaris van de mannen en de vrouwen ............................................................. 15
Bereken met een functie de gemiddelde leeftijd van de mannen en de vrouwen .................................................................... 16
Les 2: grafieken .................................................................................................................................... 17
Effectenportefeuille .................................................................................................................................................... 17
Berekeningen ............................................................................................................................................................................ 17
Bereken de totale aankoopprijs .......................................................................................................................... 17
Bereken de totale huidige waarde ...................................................................................................................... 17
1
, Voorwaardelijke opmaak .......................................................................................................................................................... 17
Maak de stijgers groen en de dalers rood ........................................................................................................... 17
Grafieken ................................................................................................................................................................................... 18
Maak een gegroepeerde kolom grafiek voor de stijgers ..................................................................................... 18
Wijzig de labels van de verticale as ..................................................................................................................... 18
Maak een gegroepeerde kolom grafiek voor de dalers ...................................................................................... 19
Voorspelling verkoop hamburgers .............................................................................................................................. 19
Grafiek....................................................................................................................................................................................... 19
Maak een grafiek zonder legende ....................................................................................................................... 19
Zorg ervoor dat de gegevenslabels aan de buitenkant van de grafiek staan ...................................................... 20
Verband tussen temperatuur en waterverbruik ......................................................................................................... 20
Grafiek....................................................................................................................................................................................... 20
Maak een spreidingsdiagram waarbij X = temperatuur en Y = waterverbruik ................................................... 20
Zorg ervoor dat de Y-waardes beginnen bij 80 ................................................................................................... 21
Geef de (lineaire) trendlijn en vergelijking weer................................................................................................. 21
Aantal KdG-medewerkers ........................................................................................................................................... 22
Berekening ................................................................................................................................................................................ 22
Bereken het procent van het totaal voor elk departement ................................................................................ 22
Grafiek....................................................................................................................................................................................... 22
Gebruik een combinatie grafiek (gestapelde kolom en lijn met markeringen)................................................... 22
Zorg ervoor dat de secundaire Y-as minimum -1 en maximum 0,3 is ................................................................. 23
Koers IJslandse kroon .................................................................................................................................................. 24
Grafiek....................................................................................................................................................................................... 24
Maak een lijn met markeringen grafiek van 01-06 t.e.m. 13-06......................................................................... 24
Plaats de gegevenslabels koersen op de grafiek ................................................................................................. 24
Verander de indeling van de Y-as ........................................................................................................................ 25
Zorg ervoor dat zaterdagen en zondagen niet worden weergegeven ................................................................ 25
De waarde-as heeft als minimumwaarde 82 en is niet zichtbaar ....................................................................... 26
BCG matrix ................................................................................................................................................................... 26
Opbouwen van een BCG grafiek ............................................................................................................................................... 26
Selecteer het grafiektype bel .............................................................................................................................. 26
Voeg elke reeks apart in ...................................................................................................................................... 27
Zet de horizontale as op een minimum van 0 en een maximum van 2 en zet de waarden in omgekeerde
volgorde. Zorg er ook voor dat de verticale as op de maximale waarde snijdt. ................................................. 27
Doe hetzelfde bij de verticale as ......................................................................................................................... 27
Pas de grafiek en gegevenslabels aan ................................................................................................................. 28
Drankverbruik .............................................................................................................................................................. 29
Grafiek....................................................................................................................................................................................... 29
2
, Maak een combinatiegrafiek met de gegevens .................................................................................................. 29
Pas de verticale as aan ........................................................................................................................................ 29
Voeg de gegevenstabel toe ................................................................................................................................. 30
Les 3: namen ........................................................................................................................................ 31
Inboedel....................................................................................................................................................................... 31
Selectie aanvullen ..................................................................................................................................................................... 31
Voor getallen in de tabel (maakt niet uit welke getallen) ................................................................................... 31
Bereken het totaal per trimester en per jaar ...................................................................................................... 31
Namen maken ........................................................................................................................................................................... 31
Selecteer de volledige tabel. Ga naar het tabblad ‘formules’ en selecteer ‘maken o.b.v. van selectie .............. 31
Controle ..................................................................................................................................................................................... 32
Zoek met de hulp van namen naar het antwoord op de controlevragen ........................................................... 32
Personeelskosten ........................................................................................................................................................ 32
Namen....................................................................................................................................................................................... 32
Stel namen in voor de volledige tabel ................................................................................................................. 32
Stel het gebruik van namen in voor deze tabel ................................................................................................... 32
Controle ..................................................................................................................................................................................... 33
Zoek met behulp van namen het antwoord op de controlevragen .................................................................... 33
Quotiënt ...................................................................................................................................................................... 34
Namen....................................................................................................................................................................................... 34
Maak een naam aan voor ‘teller’ en ‘noemer’ ................................................................................................... 34
Controle ..................................................................................................................................................................................... 34
Gebruik de namen om de tabel verder aan te vullen ......................................................................................... 34
Som van reeksen ......................................................................................................................................................... 35
Namen....................................................................................................................................................................................... 35
Stel namen in voor de volledige tabel ................................................................................................................. 35
Controle ..................................................................................................................................................................................... 36
Vul de tabel verder aan met namen (niet doortrekken => kan niet met namen) ............................................... 36
Les 4: logisch ........................................................................................................................................ 37
Toets punten ............................................................................................................................................................... 37
Berekeningen ............................................................................................................................................................................ 37
Bereken het totaal van de toets punten ............................................................................................................. 37
Zet het totaal om naar procent ........................................................................................................................... 37
Tel de drie toets punten op en zet het resultaat om naar een getal op 20......................................................... 37
Rond dat getal af op 0 decimalen met een formule ........................................................................................... 38
Stel met een ALS formule in de tabel waardoor er een x staat in een hoekje wanneer er een onvoldoende is 38
Zorg ervoor, met voorwaardelijke opmaak, dat de namen die een minder dan de helft hebben op hun
afgerond getal rood worden ............................................................................................................................... 39
3
, Doe nu hetzelfde bij de testen ............................................................................................................................ 39
Champagne.................................................................................................................................................................. 40
Berekeningen ............................................................................................................................................................................ 40
Stel een ALS formule in om te berekenen hoeveel gratis flessen er verkocht zijn ............................................. 40
Bereken het totaal, de korting en te betalen van elke fles champagne .............................................................. 40
Bereken het totaal van elke kolom ...................................................................................................................... 41
Zorg ervoor, met voorwaardelijke opmaak, dat als het ‘aantal’ leeg is, dat de volledige rij grijs wordt............. 42
Prijs per reeks .............................................................................................................................................................. 42
Berekeningen ............................................................................................................................................................................ 42
Zoek de prijs met reeks met verticaal zoeken ..................................................................................................... 42
Berekenen de leeftijd in 2013 in gehele getallen ................................................................................................ 42
Bepaal het kortingspercentage per persoon (zie voorwaarden) met een ALS formule ...................................... 43
Bereken het totaal te betalen (met korting) ....................................................................................................... 43
Stel, met voorwaardelijke opmaak, in dat ‘aantal reeksen’, ‘leeftijd’, ‘korting’ en ‘te betalen’ rood worden als
er 10% korting wordt gegeven ............................................................................................................................ 43
Les 5: tekst ........................................................................................................................................... 44
Theorie ........................................................................................................................................................................ 44
Formules.................................................................................................................................................................................... 44
, Oefeningen ................................................................................................................................................................................ 47
Postcode .............................................................................................................................................................. 47
Namen ................................................................................................................................................................. 48
E-mailadres.......................................................................................................................................................... 49
Code .................................................................................................................................................................... 49
Tekstfuncties (vul de formules stap per stap in) ......................................................................................................... 50
Oefeningen ................................................................................................................................................................................ 50
Initialen ............................................................................................................................................................... 50
Code .................................................................................................................................................................... 50
E-mailadres.......................................................................................................................................................... 51
Aanspreking......................................................................................................................................................... 51
Les 6: zoeken ........................................................................................................................................ 52
Theorie ........................................................................................................................................................................ 52
Formule ..................................................................................................................................................................................... 52
X.ZOEKEN ............................................................................................................................................................ 52
X.ZOEKEN als matrix ............................................................................................................................................ 53
Oefeningen .................................................................................................................................................................. 53
Premie op basis van anciënniteit .............................................................................................................................................. 53
bereken de anciënniteit van elke werknemer..................................................................................................... 53
Zoek de premie die elke werknemer krijgt aan de hand van het volgende tabblad ........................................... 54
Premie op basis van verkoop .................................................................................................................................................... 54
Maak zelf de tabel voor de premies die een verkoper kan krijgen op het volgende tabblad ............................. 54
Bereken nu de premie op basis van de tabel die je juist hebt gemaakt ............................................................. 54
Provincies .................................................................................................................................................................................. 54
Vervolledig de tabel op het volgende tabblad (pas aan waar nodig).................................................................. 54
Zoek nu welke gemeente in welke provincie hoort ............................................................................................ 55
Sorteer de tabel van A-Z...................................................................................................................................... 55
Prijzen van taxiritten ................................................................................................................................................................. 55
Maak de zoektabel op het volgende tabblad ...................................................................................................... 55
Bereken de prijs van elke taxirit met behulp van deze tabel .............................................................................. 56
Land en landnummer ................................................................................................................................................................ 56
Zoek het land en landnummer die correspondeerden met de code UK ............................................................ 56
Maak een dropdown menu bij de landcode om de formule uit te testen .......................................................... 57
Zoek met behulp van de landnummers de matrix .............................................................................................. 57
Type wagen en hun huurprijs .................................................................................................................................................... 57
Zoek het type wagen en de huurprijs via de tabel op het volgende tabblad (draai de huurprijs en type wagen
om in deze tabel om ze alle twee direct te vinden) ............................................................................................ 57
Bereken het aantal dagen tussen begin -en einddatum (met eerste en laatste dag erbij) (geen berekening als
de einddatum leeg is).......................................................................................................................................... 57
5
, Bereken met alle gegevens de totale huurprijs (terug geen berekening als de einddatum leeg is) ................... 58
Danslessen ................................................................................................................................................................................ 58
Zoek leraar, zaal en prijs per reeks met één formule (draai de kolommen om zodat ze op de goede plaats
staan)................................................................................................................................................................... 58
Bereken de leeftijd in 2013 in gehele getalen ..................................................................................................... 58
Bepaal het kortingspercentage dat ieder lid krijgt .............................................................................................. 58
Bereken hoeveel ieder lid moet betalen ............................................................................................................. 58
Dagprijzen ................................................................................................................................................................................. 58
Zoek de klant en de bijhorende prijs in de tabel op het volgende tabblad (Rechtermuisklik op rechter
onderhoek geselecteerde cel voor volledige tabel in te vullen) ......................................................................... 58
Bereken het aantal dagen tussen begin -en einddatum (met eerste en laatste dag erbij) (geen berekening als
de einddatum leeg is).......................................................................................................................................... 59
Bereken de prijs die andere klant moet betalen. Hou opnieuw rekening met lege cellen ................................. 59
Les 7: titelblokkering ............................................................................................................................ 60
Oefeningen .................................................................................................................................................................. 60
Drankbedrijf .............................................................................................................................................................................. 60
Bereken het totaal van de drankverkoop inclusief BTW ..................................................................................... 60
Bereken de totaalsom per BTW-tarief maar zonder de BTW er bij geteld .......................................................... 60
Creëer een titelblokkering op de eerste kolom en rij (selecteer de cel dichtstbij de splitsing tussen kolom en
rij) ........................................................................................................................................................................ 60
Categorieën ............................................................................................................................................................................... 61
Bereken de bedragen inclusief BTW ................................................................................................................... 61
Stel subtotalen in per categorie .......................................................................................................................... 61
Titelblokkeer de eerste rij ................................................................................................................................... 62
Productentabel 1 ....................................................................................................................................................................... 62
Geef kolom 1 en rij 1-3 een kleur en zet deze vast ............................................................................................. 62
Zet deze nu vast................................................................................................................................................... 62
Productentabel 2 ....................................................................................................................................................................... 63
Stel een auto-overzicht in waarbij enkel de totalen van de productgroepen worden getoond ......................... 63
Doe hetzelfde bij deze tabel als bij ‘productentabel 1’ ....................................................................................... 63
Les 8: draaitabellen .............................................................................................................................. 64
Iglo ............................................................................................................................................................................... 64
Tabel ......................................................................................................................................................................................... 64
Maak een dynamische tabel aan op basis van de gegevens ............................................................................... 64
Voeg een nieuwe kolom toe aan de tabel en bereken het totaal ....................................................................... 64
Geef de tabel de naam ‘Iglo’ ............................................................................................................................... 65
Draaitabel 1 .............................................................................................................................................................................. 65
Maak een draaitabel aan en zet deze op een nieuw tabblad. Duid de benodigde gegevens aan ...................... 65
Maak nu een draaitabel aan waar je kan welke productgroep voor de meeste opbrengst zorgt ...................... 66
6
, Stel de gegevens in deze tabel juist in................................................................................................................. 67
Draaitabel ................................................................................................................................................................................. 68
Maak de eerste draaitabel opnieuw aan op het tabblad (1)2............................................................................. 68
Voeg een slicer toe aan de draaitabel ................................................................................................................. 69
Vul nieuwe gegevens in de originele tabel om te zien of de draaitabel werkt ................................................... 70
Draaitabel 3 .............................................................................................................................................................................. 71
Maak een draaitabel op basis van de verkopen per maand ............................................................................... 71
Maak een draaigrafiek aan op basis van deze draaitabel ................................................................................... 71
Pas de tabel aan naar het voorbeeld .................................................................................................................. 73
Aspen ........................................................................................................................................................................... 73
Tabel ......................................................................................................................................................................................... 73
Bereken het totaalbedrag per factuur. Maak daarna een dynamische tabel aan en geef deze tabel de naam
‘Aspen’ ................................................................................................................................................................. 73
Draaitabel 1 .............................................................................................................................................................................. 74
Maak een draaitabel aan op het volgende tabblad waarbij er wordt weergeven wat voor de meeste opbrengst
zorgde .................................................................................................................................................................. 74
Vul de juiste gegevens in de draaitabel............................................................................................................... 74
Zorg ervoor dat er geen eindtotalen meer zijn en dat de cijfers in duizendtalen worden weergegeven ........... 74
Maak een gegroepeerde grafiek volgens het voorbeeld aan waarbij de veldknoppen niet zichtbaar zijn ........ 75
Draaitabel 2 .............................................................................................................................................................................. 76
Maak een draaitabel aan waarbij de evolutie van de omzet wordt weergegeven ............................................. 76
Draaitabel 3 .............................................................................................................................................................................. 77
Maak een draaitabel aan waarbij het aantal verkopen per verkoper wordt weergegeven ................................ 77
Les 9: dashboard................................................................................................................................... 79
Verkoop ....................................................................................................................................................................... 79
Tabel ......................................................................................................................................................................................... 79
Maak een dynamische tabel van de tabel op het tabblad ‘VERKOOP’ en geef de tabel de naam ‘Verkoop’ ..... 79
Draaitabel 1 .............................................................................................................................................................................. 80
Maak een draaitabel aan waarbij de omzet per product wordt weergegeven op het tabblad ‘DASHBOARD’ .. 80
Stel de waarden juist in ....................................................................................................................................... 81
Sorteer de omzet van groot naar klein en zorg ervoor dat enkel de 10 beste producten te zien zijn in de
draaitabel ............................................................................................................................................................ 82
Maak van deze gegevens een gecombineerde draaigrafiek ............................................................................... 84
Stel de grafiek in zodat het ongeveer op het voorbeeld lijkt .............................................................................. 84
Draaitabel 2 .............................................................................................................................................................................. 85
Vul de dynamische tabel aan met productgroep die zich op het tabblad ‘product’ bevindt ............................. 85
Kopieer de eerste draaitabel en zorg ervoor dat de omzet nu per productgroep gesorteerd staat ................... 85
Stel dezelfde filters in als bij de eerste draaitabel............................................................................................... 86
Draaitabel 3 .............................................................................................................................................................................. 87
7
, Doe hetzelfde als bij draaitabel 2 maar stel nu in dat de omzet per staat wordt weergegeven (voeg deze eerst
toe aan de tabel) ................................................................................................................................................. 87
Filteren via dashboard .............................................................................................................................................................. 88
Stel een slicer in voor de productgroepen .......................................................................................................... 88
Stel een timeline in voor de maanden ................................................................................................................ 89
Les 10: Power Pivot .............................................................................................................................. 90
Koffie ........................................................................................................................................................................... 90
Oefening .................................................................................................................................................................................... 90
Haal de gegevens op uit de ‘Koffie DB’ file ......................................................................................................... 90
Bekijk de tabellen in gegevensmodel en trek de nodige verbindingen .............................................................. 92
Voeg een datumtabel toe en maak de verbinding met de ‘Verkoop tabel’ ........................................................ 94
Verberg de namen in de tabel ‘Verkoop’ waarmee een verbinding is gemaakt ................................................. 95
Bereken de omzet in de ‘Verkoop’ tabel ............................................................................................................. 95
Maak een draaitabel aan waarbij de omzet per categorie wordt weergegeven; de omzet per verkoper wordt
weergegeven; en de omzet per maand wordt weergeven ................................................................................. 96
Maak de gegeven grafieken na ........................................................................................................................... 98
8
, LES 1: HERHALING EXCEL
FUNCTIEBEREKENINGEN DEEL 1
BEREKEN MET EEN EXCEL-FUNCTIE HET TOTAAL VAN DE SALARISSEN
=SOM (telt de getallen in een celbereik op)
BEREKEN MET EEN EXCEL-FUNCTIE HET GEMIDDELDE VAN DE SALARISSEN
=GEMIDDELDE (berekent het (rekenkundig) gemiddelde van de argumenten)
BEREKEN MET EEN EXCEL-FUNCTIE HET HOOGSTE SALARIS
=MAX (geeft als resultaat het de grootste waarde in een lijst met argumenten)
9
Voordelen van het kopen van samenvattingen bij Stuvia op een rij:
√ Verzekerd van kwaliteit door reviews
Stuvia-klanten hebben meer dan 700.000 samenvattingen beoordeeld. Zo weet je zeker dat je de beste documenten koopt!
Snel en makkelijk kopen
Je betaalt supersnel en eenmalig met iDeal, Bancontact of creditcard voor de samenvatting. Zonder lidmaatschap.
Focus op de essentie
Samenvattingen worden geschreven voor en door anderen. Daarom zijn de samenvattingen altijd betrouwbaar en actueel. Zo kom je snel tot de kern!
Veelgestelde vragen
Wat krijg ik als ik dit document koop?
Je krijgt een PDF, die direct beschikbaar is na je aankoop. Het gekochte document is altijd, overal en oneindig toegankelijk via je profiel.
Tevredenheidsgarantie: hoe werkt dat?
Onze tevredenheidsgarantie zorgt ervoor dat je altijd een studiedocument vindt dat goed bij je past. Je vult een formulier in en onze klantenservice regelt de rest.
Van wie koop ik deze samenvatting?
Stuvia is een marktplaats, je koop dit document dus niet van ons, maar van verkoper fleurvanderjeught. Stuvia faciliteert de betaling aan de verkoper.
Zit ik meteen vast aan een abonnement?
Nee, je koopt alleen deze samenvatting voor €29,99. Je zit daarna nergens aan vast.
