Met excel kun je pijlsnel grote berekeningen maken met heel veel getallen. Daarmee boek je vele uren tijdwinst ten opzichte van het maken van berekeningen op een zakjapanner. Met Excel kun je bijvoorbeeld de ontwikkeling van kengetallen als omzet, winst, kosten heel goed zichtbaar maken. Verder kun je met Excel ook hele goede grafieken maken om bepaalde ontwikkelingen of verdelingen grafisch aantrekkelijk te kunnen laten zien in een presentatie. En tenslotte kun je Excel ook nog eens goed gebruiken als een eenvoudige database, zoals een adressenbestand.
Kortom: snel rekenen met slimme formules, goed inzicht in ontwikkelingen die niet zo snel uit een cijferbrij naar voren komen, grafisch aantrekkelijke presentaties om je publiek goed te kunnen voorlichten en overtuigen en ook nog eens een database voor je gegevens.
Uitleg van de opdracht
In dit leerarrangement vind je 12 lessen. Deze lessen moet je maken in deze volgorde. Het begin is eenvoudig en gaandeweg wordt het ingewikkelder. Bij een aantal lessen zijn lesfilmpjes geplaatst om de stof niet alleen via een handleiding op papier uit te leggen, maar ook nog eens met een video-uitleg.
Dit leerarrangement wordt afgesloten met een eindopdracht.
Les 1 Kennismaking met het programma
Eerste kennismaking
"Aangenaam, de naam is excel".
Opstarten: start (windowsknop) – alle apps – excel 2016 (of via Office - Excel)
Opdracht 1. Begrippen. Lees de informatie hieronder. Als je denkt dat je het weet, ga dan naar opdracht 2
Kolommen worden aangeduid met een letter, bijvoorbeeld kolom A
Rijen worden aangeduid met een cijfer, bijvoorbeeld rij 3
Cellen worden aangeduid met de combinatie van een letter en een cijfer, bijvoorbeeld cel A1
De formulebalk is het witte vak achter fx
Tabbladen worden aangeduid met blad 1, blad 2 enzovoort. Namen van tabbladen kun je wijzigen door er met de rechtermuisknop op te klikken en te kiezen voor "Naam wijzigen".
Opdracht 2
Klik in je excel document in cel A2 en typ daarin: Cel A2
Verander de naam van tabblad 1 in les 1 (ga met muis op tabblad 1 staan, dan via rechtermuisknop, naam wijzigen)
Voeg een tabblad toe door op het plusteken te klikken. Geef dit de naam "pagina 4".
Opdracht 3
Tekst en getallen invoeren in cellen en een eenvoudige berekening
Type de volgende teksten in de juiste cellen:
Cel A1 Boodschappen voor mijn feest
Cel A3 Taart
Cel B3 7,25
Cel A4 Bier
Cel B4 38,70
Cel A5 Chips
Cel B5 5,90
Cel A6 Frisdrank
Cel B6 12,80
Cel A7 Wijn
Cel B7 12,00
Cel A8 Totaal
Het valt op dat de getallen niet netjes onder elkaar komen te staan!!!
Voor de oplossing, doe het volgende Zie ook het filmpje: SOMFORMULE
Selecteer de getallen door in cel B3 te klikken en vervolgens naar beneden te slepen tot en met cel B8. Er ontstaat een zwarte rechthoek. Laat los in B8.
In de werkbalken boven aan het excel werkblad zie je allerlei knopjes.
Klik op het knopje meer decimalen en daarna op het knopje minder decimalen. Alles heeft nu mooi twee cijfers achter de komma. Als je deze knoppen niet kunt vinden, ga dan eerst even naar de uitleg van het LINT
Opdracht 4 Optellen in excel: de somformule
We gaan nu uitrekenen hoeveel de uitgaven voor het feestje in totaal zijn.
Dit kan natuurlijk met een rekenmachine, maar dat is niet handig als dit een hele lange lijst zou zijn.
Hoe doe je dit snel met excel?
Alle prijzen (kosten) staan in de cellen B3 tot en met B7 onder elkaar. Controleer dit bij jezelf!
Bedenk nu in welke cel je het totaal wilt hebben staan. Cel B8 is een logische plek.
Klik in cel B8 (de cel wordt actief, want de rand wordt dikker)
Ga naar de formulebalk en typ daar de volgende formule:
=som(b3:b7) Dit heet de somformule. Bekijk eventueel het filmpje SOMFORMULE
Klik op de groene v links naast de formulebalk.
Uitleg:
Om excel te laten weten dat je een formule in een cel wilt zetten is het altijd nodig om te beginnen met een = (het is-teken). Het begin van een berekening of formule in excel is altijd het = teken.
Som betekent: tel voor mij op. Je geeft een opdracht aan excel om voor jou te doen.
(b3:b7) betekent: tel voor mij op de inhoud van cel b3 tot en met de inhoud van de cel b7
De dubbele punt betekent in excel dus wat anders dan in word: Het betekent “Tot en met”.
Het klikken op de V wil zeggen dat je klaar bent met het maken van de formule.
Hoeveel komt er te staan naast Totaal? Controleer je antwoord (76,65)
Opdracht 5
Zet in de cellen D1 tot en met D9 de volgende getallen onder elkaar:
Bereken het totaal op de de manier zoals van hierboven. Controleer je antwoord. (er moet 33530 uitkomen)
Opdracht 6
Zet in de cellen H1 tot en met H6 de volgende getallen onder elkaar:
3,69 5,12 2,30 9,80 8,34 3,40
Zorg dat er overal twee cijfers achter de komma staan. Ga terug naar opdracht 3 als je niet meer weet hoe dit moet.
Tel deze serie getallen op, het antwoord moet komen te staan in cel H7.
Opdracht 7 Tabbladen bewerken
Onder aan het excel werkblad zie je tabbladen, met de namen blad 1, blad 2 en blad 3.
Verander de naam van blad 1 in: Les 1
Verander de naam van blad 2 in Les 2 (Bij de volgende les gebruik je dit werkblad om de opdrachten van les 2 te maken.
Maak nu zoveel tabbladen nieuw aan dat je er 10 lessen in kwijt kunt. Geef ze allemaal de naam les 1, les 2 enzovoort.
Opdracht 8 Een werkblad opslaan
Je kunt een exceldocument opslaan zoals je ook een word-document opslaat. Via Opslaan en naam geven.
Sla je document op onder de naam Excellessen eigenvoornaam
Opdracht 9 Verplaatsen van cellen.
- Selecteer alle cellen van opdracht 3.
- Klik met je rechtermuisknop en kies knippen )(of gebruik Ctrl X)
- Ga naar cel G2
- Kies voor rechtermuisknop plakken (of gebruik Ctrl V).
- Je hebt nu alle cellen verplaatst naar een andere plek
Opdracht 10 Kopiëren van cellen.
Soms is het handig om een bepaalde opzet die je vaak gaat gebruiken (bijvoorbeeld elke maand hetzelfde overzicht maken) vooraf te maken, om te voorkomen dat je de opzet steeds opnieuw moet maken.
Bijvoorbeeld een overzicht van hoeveel iedere medewerker elke maand heeft verkocht.
- Je maakt eerst het overzicht met de gegevens zoals je kunt vinden het in bestand Omzet per Medewerker (te vinden onder aan de opdracht, direct onder het flmpje met de uitleg van de somformule)
- Het overzicht is nu nog leeg.
- Het is natuurlijk het meest handig en tijdbesparend als je op het eerste tabblad niet alleen de gegevens hebt staan, maar ook alle formules die nodig zijn voor een snel overzicht van de resultaten.
1. Vul in de kolom achter december het woord totaal in.
2. Vul in de cel daaronder voor de eerste medewerker de somformule in, waarmee je berekent wat die medewerker in totaal dat jaar heeft omgezet.
3. Kopieer die formule met de automatische vulgreep naar beneden, zodat bij iedere medewerker het totaal komt te staan.
4. Je wilt ook een overzicht van de omzet per maand. Dus vul je onder de laatste medewerker het woord Totaal in. In de cel daarnaast, onder januari, vul je de somformule in voor de omzet in januari. Sleep die formule naar rechts met de automatische vulgreep, zodat de formule voor alle maanden wordt gemaakt.
5. Selecteer nu het hele overzicht, inclusief de titel Omzet per medewerker van Reisbureau De ZON
6, Kopieer het overzicht via rechtermuisknop Kopiëren (of gebruik Ctrl C)
7. Ga nu naar tabblad 2 en klik in cel A1 van tabblad 2 Kies via rechtermuisknop voor Plakken (of gebruik Vtrl V) 8. Verander het jaartal in 2016
9. Doe nu hetzelfde op tabblad 3 het jaartal wordt nu 2017
10. Verander de naam van tabblad 1 in 2015, tabblad 2 in 2016 en tabblad 3 in 2017.
Je hebt nu voor de komende 3 jaar een leeg overzicht dat je alleen nog maar hoeft in te vullen.
Bewaar het document goed, maak ergens een backup. (geef dit dezelfde naam maar zet er kopie achter)
Het lint van excel is een serie knoppen om snel handelingen in excel uit te voeren.
Bestudeer onderstaand filmpje.
Het lint in Excel Bron: VDABwebleren via Youtube
Sjablonen, een snel begin
Excel heeft een groot aantal standaard sjablonen tot zijn beschikking zodat je snel aan het werk kunt gaan. Sjablonen zijn standaardformulieren die je alleen nog maar hoeft in te vullen.
Je hoeft dat dus niet allemaal zelf in elkaar te knutsen. Gemak dient de mens nietwaar?
Volg onderstaande stappen om de sjablonen te ontdekken:
Open het programma excel
Kies Bestand - Nieuw
Je ziet nu al een aantal sjablonen. Bovenaan zie je bijvoorbeeld: Lege werkmap (dit is een nieuw en leeg excelbestand), daarnaast zie je onder andere Voorbeeldsjablonen en Recente sjablonen (Excel onthoudt je voorkeur)
Daar onder zie je Office.com sjablonen. Het zijn er heel veel, zoals agenda, roosters, facturen enzovoort. Kijk hier rustig rond. Er is ook een zoekfunctie, zodat je kunt zoeken naar sjablonen.
Opdracht Sjablonen
Een accountmanager heeft een rondreis gemaakt langs een heleboel klanten en mag zijn kosten declareren.
De volgende kosten zijn gemaakt:
12 december Hotel € 240, benzine € 100, Lunch en diner € 75, Amusement € 45, Overig (koffie onderweg) € 10
14 december Hotel € 350, benzine € 76, Lunch en diner € 86, Overig € 34
18 december Hotel € 475, benzine € 120, Lunch en diner € 120, Amusement € 240, Overige € 76
21 december Hotel € 80, benzine € 100 Lunch en diner € 50 Amusement € 40, Overige € 65
Vul voor deze accountmanager een declaratieformulier in. Het sjabloon heeft de naam Onkostenformulier zakenreizen.
Lever het ingevulde bestand in in de hiervoor bestemde inlevermap. Vul bij de naam van de accountmanager je eigen naam in.
Les 2 De automatische vulgreep
Les 2 gebruik voor deze les tabblad 2 van je excel document
De automatische vulgreep
Met excel kun je heel gemakkelijk herhalend teksten en reeksen van getallen invoeren, zonder dat je dit cel voor cel moet intypen. Bijvoorbeeld dagen van de week, of jaartallen, of een reeks van getallen.
Opdracht 1
Type in cel A10 Maandag
Type in cel A11 Dinsdag
Selecteer beide cellen. Rechtsonder ontstaat een zwart vierkantje.Ga daar met de cursor zo opstaan dat de cursor verandert in een zwart plusteken. Trek nu de rechterbenedenhoek naar beneden tot cel A16. De dagen van de week staan nu onder elkaar
Opdracht 2 Cellen leeg maken:
Selecteer de cellen A10 tot en met A16. Doe de toetscombinatie CTRL X en klik op de deleteknop. De cellen zijn nu leeg en de ‘formule’is ook weg.
Opdracht 3: een kalender (te gebruiken als weekrooster, maandrooster, jaarrooster)
Type in cel A10 maandag en in cel B10 18 mei
Type in cel A11 dinsdag en in cel B11 19 mei
Selecteer deze vier cellen A10 t/m B11
Sleep het zwarte vierkantje rechtsonder naar beneden tot rij 22.
Je krijgt nu een soort kalender.
Opdracht 4
Het invoeren van jaartallen.
Excel herkent niet automatisch een jaartal. Type je in een cel het getal 2000, dan beschouwt excel dit niet als een jaartal, maar als een gewoon getal. Dus moet je een trucje uithalen.
Een jaartal voer je in door eerst een accent te typen en daarna pas het getal. Als je dit doet dan verschijnt een groen driehoekje rechtsboven in de cel.
Zie eventueel het filmpje AUTOMATISCHE VULGREEP
Zet in cel E2 het jaartal ‘2000
Zet in cel E3 het jaartal ‘2001
Maak een reeks tot en met het jaar ‘2020 met behulp van de vulgreep
LET OP: EEN JAARTAL VOER JE ALS VOLGT IN:
Type in de cel eerst het accentteken. Dit zit onder het aanhalingsteken. Type dan het jaartal er aan vast.
Opdracht 5
Zet in cel F1 de tekst rentepercentage
Zet in cel F2 het getal 4
Selecteer cel F2 en gebruik de vulgreep en vul aan tot en met het jaar 2020 (kolom E)
Opdracht 6
Zet in cel H1 het getal 1
Zet in cel H2 het getal 2
Vul aan tot en met het getal 35 met de vulgreep
Automatische vulgreep
Les 3 Berekeningen
In excel kun je getallen optellen, aftrekken, vermenigvuldigen en delen. Verder kun je worteltrekken en machtsverheffen. Deze laatste twee worden niet behandeld/
Om te kunnen rekenen in excel heb je formules nodig. Deze formules vertellen met welke cellen (de inhoud ervan) je wilt rekenen.
Optellen: = a1+b1 Excel telt de inhoud van cel a1 op bij de inhoud van B1
Aftrekken: = A1-B1 Excel trekt de inhoud van cel A1 af met de inhoud van cel A2
Vermenigvuldigen = A1*B1 Excel vermenigvuldigt de inhoud van de cellen a1 en A2
Delen = A1/B1 Excel deelt de inhoud van A1 door de inhoud van A2
LET OP: het teken : betekent in het excels tot en met. Voor delen gebruiken we in Excel het / teken
FILMTIP bij deze les: BEREKENINGEN
Opdracht 1 optellen
Zet in cel A1 het getal 25
Zet in cel B1 het getal 14
Zet in cel C1de formule = a1+b1 (en activeer de formule natuurlijk met de groene V)
Opdracht 2 aftrekken
Zet in cel A3 het getal 25
Zet in cel B3 het getal 14
Zet in cel C3 de formule = a3-b3
Opdracht 3 vermenigvuldigen
Zet in cel A5 het getal 25
Zet in cel B5 het getal 14
Zet in cel C5 de formule = a5*b5 (de * is het keerteken in excel, dus niet de x)
Opdracht 4 delen
Zet in cel A7 het getal 25
Zet in cel B7 het getal 14
Zet in cel C7 de formule = a7/b7 (De slash / is in excel het deelteken)
Samengestelde formules
Je kunt ook ingewikkelde formules maken in excel. Die zijn bijvoorbeeld nodig als je de rente wilt berekenen, of een indexcijfer, of een percentage van een totaal. Dit wordt in een van de volgende lessen behandeld.
Een voorbeeld van een samengestelde formule is:
= 2*(350-20)
Excel leest deze formule als volgt:
Excel doet altijd eerst wat tussen haakjes staat, dus rekent eerst uit hoeveel 350 min 20 is (330)
Daarna vermenigvuldigt excel de uitkomst van de berekening tussen haakjes met 2, want 2 staat voor het haakje. (uitkomst is 660)
Vraag:
Wat is de uitkomst als je geen haakjes zet? (controleer of je ook als uitkomst 680 hebt)
Opdracht 5
Zet in een lege cel de formule = 2*(750-200)
(er moet 1100 uitkomen, is dit bij jou ook zo?)
Zet in de cel eronder de formule =2*750-20 de uitkomst is (1480)
Dit komt omdat excel nu eerst 2 keer 750 doet en daarna pas - 20
De volgorde waarin excel opdrachten belangrijk vindt is:
Machtsverheffen
Vermenigvuldigen
Delen
Worteltrekken
Optellen
Aftrekken
Het beroemde ezelsbruggetje hiervoor is Meneer Van Dale Wacht Op Antwoord
Daarom is het handig om met haakjes te werken in de berekeningen, zodat excel goed weet wat hij eerst moet doen en wat pas later. Dus: alles binnen haakjes krijgt voorrang
Opdracht 6
Wat zijn de uitkomsten van de volgende formules:
= 3*500-200 controle; antwoord is 1300
= 3*(500-200) controle; antwoord is 900
Opdracht 7
Wat zijn de uitkomsten van de volgende formules:
= 200-120/30*3 (188)
= (200-120)/30*3 (8)
= 200-120/(30*3) (198,6667)
BEREKENINGEN
Les 4 Formules in Excel
Formules kopiëren naar onderliggende cellen
Grote bestanden in excel en het kopiëren van een formule
Je hebt pas echt plezier van excel bij grote bestanden met veel getallen, waarbij je de formule van een berekening kunt kopieren zodat je voor alle getallen in een keer de berekening kunt maken. Hier kun je heel veel tijd winnen.
Opdracht 1
Je hebt voor een bepaald aantal weken gegevens opgezocht over de omzet van een ijscokraam. Het aantal verkochte ijsjes is het resultaat van de volgende formule:
4 keer de gemiddelde temperatuur plus het aantal uren zon gedeeld door 2 min 2 keer het aantal millimeters regen
De excelformule hiervoor is =4*(temperatuur+100) + (zonneuren/2) – (2*millimeters regen). Deze formule is een gegeven (hoef je niet te verklaren).
Je ziet hieronder deze gegevens. Donwload het bestand Oefenbestanden Excel aan het eind van de lessenserie naar je laptop en open het bestand
Op het tabblad ijsjes vind je deze gegevens. Vul de weeknummers aan met de vulgreep.
Cel A1
Weeknummer
Cel B 1
Gemiddelde temperatuur
Cel C1
Aantal uren zon
Cel D 1
Millimeters regen
Cel E1
Aantal verkochte ijsjes
26
20
10
30
27
24
12
10
28
19
6
40
29
17
4
50
30
25
12
0
31
22
10
10
In de cellen A1 tot en met E1 staan de kopjes (de namen) van de kolommen. De eerste gegevens komen dus in de rij 2 te staan. Weeknummer 26 staat dus in cel A2, de temperatuur 20 graden staat in B2, enzovoort.
We gaan de formule zetten in cel E2
In excel taal is deze formule: =4*(B2+100)+(C2/2)-2*(D2)
a. Hoeveel ijsjes zijn er verkocht in week 26? (425)
Dit is dus een vrij moeilijke formule die je niet voor elke week opnieuw wilt maken. Dat hoeft ook niet, want je kunt de formule simpel naar beneden kopiëren.
Klik in het zwarte rechthoekje in de cel E2 en sleep de cursor naar beneden, tot en met cel E7.
b. Hoeveel ijsjes zijn er verkocht in week 29? (370)
c. Welke formule staat nu in cel E6? =4*(B6+100)+(C6/2)-2*(D6)
Opdracht 2
Je hebt de volgende gegevens: (zie oefenbestand excel tabblad nettowinst)
jaar
omzet
inkoopkosten
vaste kosten
nettowinst
1999
12000
3000
2000
14000
3000
2001
23000
3000
2002
16000
3000
2003
17000
3000
2004
19000
3000
2005
14000
3000
2006
18000
3000
2007
20000
3000
2008
15000
3000
Toelichting: Een bedrijf weet uit de afgelopen jaren (1999 tot en met 2008) welke omzet ze heeft gehad. Deze omzet staat in de tabel in excel.
De inkoopkosten zijn telkens 80% van de omzet. 80% van de omzet kun je ook schrijven als 0,8 * iets. Dus voor 1999 zou de formule zijn 0,8*cel waar de omzet van 1999 in staat.
De vaste kosten zijn elk jaar 3000.
De nettowinst is omzet – (inkoopkosten + vaste kosten)
Vul de jaren aan tot en met 2008 met behulp van de vulgreep.
Vul de vaste kosten aan voor alle jaren met behulp van de vulgreep.
Bedenk welke formule je moet plaatsen in cel C3 om excel te omzet te laten uitrekenen. Laat je oplossing controleren door de docent.
Kopieer deze formule naar beneden. Wat zijn de inkoopkosten voor 2006?
Bedenk welke formule je moet zetten in cel E3 om de nettowinst te berekenen. Laat je oplossing controleren door de docent
FILMTIP: Berekeningen met formules
Les 5 Grafieken
Excel is een geweldig hulpmiddel om ontwikkelingen door de tijd heen te laten zien in een grafiek. Hiervoor moet je wel eerst altijd een excelwerkblad met gegevens hebben gemaakt.
Herhaling: invoeren jaartallen als je grafieken wilt maken Dit staat niet voor niks dik gedrukt, dus wel even lezen wat hieronder staat
Extra informatie: voor een grafiek is het handig als je jaren in excel invoert als “tekst”
Dit kan eenvoudig door in een cel een jaartal op de volgende manier in te voeren:
‘2000
‘2001 enzovoort. Je hebt dit al geleerd in les 2.
Het teken ‘zit op je toetsenbord links naast de enterknop van het lettertoetsenbord (onder het aanhalingsteken)
Dit teken heet het accentteken.
Opdracht 1
We hebben de volgende gegevens. Zet deze op tabblad 5 van je excel document
Gebruik voor de jaartallen natuurlijk de vulgreep om de reeks van de eerste twee jaartallen snel te kunnen aanvullen. De overige gegevens moet je simpelweg zelf invullen. En vergeet niet om het accent voor het getal te zetten om er een jaartal van te maken.
jaar
omzet in euros (x 1000)
2002
2300
2003
2200
2004
2450
2005
2800
2006
2680
2007
2780
2008
3400
2009
3320
2010
2400
Nu gaan we een grafiek maken.
Selecteer de gegevens uit kolom A (de jaren) en B door te selecteren
Klik op invoegen en kies een grafiektype (kies de eenvoudige maar mooie lijngrafiek 2D). Opmerking: een kolomgrafiek mag ook.
De grafiek is klaar, maar niet mooi.
De grafiek een titel geven
Zorg dat de grafiek actief is door er rechtsboven in te klikken
Klik ONTWERPEN en dan op GRAFIEKONDERDEEL TOEVOEGEN en dan gecentreerde overlay titel. (zie voorbeeld)
Opdracht 2
De assen van de grafiek een naam geven.
De horizontale as (de liggende lijn) noemen we de x-as, de verticale (staande) lijn de y-as
Klik op Ontwerpen en op Grafiekonderdeel toevoegen en dan op astitels
Kies Titel primaire horizontale as
Type in het vak van de formulebalk (dus achter fx) nu de gewenste naam van de liggende as: Jaar
Klik weer Ontwerpen en op Grafiekonderdeel toevoegen en dan op astitels
Kies titel primaire verticale as
Klik in het formulevak de gewenste naam van deze as: Aantal
Opdracht 3 Het invoegen van een trendlijn
Een trendlijn is een lijn die in een grafiek de gemiddelde ontwikkeling weergeeft. Gaat de lijn omhoog dan is de trend positief, gaat de lijn naar beneden dan gaan de zaken beroerd.
Lees hieronder hoe je een trendlijn in de grafiek kan maken.
Klik op de lijn met de gegevens zodat deze actief wordt
Rechtermuisknop en daarna trendlijn toevoegen (er ontstaat een stippellijn) en rechts op je scherm ontstaat een vak met mogelijkheden (Opties voor trendlijn)
Geef de trendlijn een naam door bij Trendlijnnaam het vakje voor AANGEPAST aan te vinken. Noem je trendlijn TRENDLIJN A
Klik nu op de scheve verfpot en selecteer ONONDERBROKEN LIJN
Selecteer bij kleur Rood
Selecteer bij Breedte 2,25 pnt met het pijltje
Selecteer bij type streepje de ononderbroken lijn
Opdracht 4
Het mooi maken van de grafiek (opmaak)
Klik met de rechtermuisknop in het witte deel van de grafiek
Kies Grafiekgebied opmaken
Kies opvulling met kleurovergang (kies zelf een leuke opmaak)
Zoals je ziet zijn er meer mogelijkheden, zoals effen opmaak, bitpatroon enzovoort.
Ga nu in het middelste deel van de grafiek staan en doe rechtermuisknop
Kies tekengebied opmaken en geef het middendeel van de grafiek een leuke opmaak naar keuze.
Klik nu op een van de balken, rechtermuisknop en kies gegevensreeks opmaken.
Geef de balken een groene kleur.
Experimenteer nu met het opmaken van de grafiek en maak er wat moois van. Er zijn heel veel mogelijkheden.
Het toevoegen van gegevenslabels
Soms is het handig dat de gegevens uit de tabel (de cijfertjes) ook in de grafiek te zien zijn.
Klik op ONTWERPEN en op Grafiekonderdeel toevoegen
Kies Gegevenslabels en kies een mogelijkheid, bijvoorbeeld EINDE BUITENKANT
Een andere grafiekvorm
Klik in de grafiek met de rechtermuisknop en kies Ander grafiektype
Maak er een lijngrafiek van
Maak er daarna een driedimensionale kolomgrafiek van
En nu een staafgrafiek
Maak er wat moois van naar keuze
Houd bij het maken van de grafiek er rekening mee dat de gegevens in een oogopslag voor iedereen duidelijk moeten zijn.
Filmtips
Grafiek maken en trendlijn
Opmaak van een grafiek
Opdracht 2
Maak voor je zelf een overzicht van waar jij je geld elke maand aan uitgeeft.
Je hebt de volgende mogelijkheden.
Uitgaan
Reiskosten
Kleding
Uit eten
Cadeau’s
Overige
Drank
Zet dit in een werkblad van excel en maak er een cirkelgrafiek bij. Geef de grafiek je eigen naam.
Opdracht 3
Je ziet hieronder wat gegevens over toeristisch bezoek aan Nederland in 2013. Je gaat hiervan een staafdiagram maken.
Zet eerst de gegevens in excel in een tabel en maak er daarna een staafdiagram van. Geef elk land een eigen kleur. Dit doe je door in de grafiek 2 keer te klikken op een staaf, zodat je die apart een kleur kunt geven via GEGEVENSPUNT OPMAKEN
Duitsland 12000
Frankrijk 8000
Japan 1000
Spanje 2000
Engeland 10000
Zie eventueel het filmpje OPMAAK van een Grafiek
Les 6 Relatieve celverwijzing
Opdracht 1
Iemand ontvangt in het jaar 2000 op zijn 18e verjaardag, toevallig 1 januari, een bedrag van
€ 1000. Hij besluit dit op een spaarrekening te zetten voor als hij 67 jaar wordt en met pensioen gaat. De rente is 4 % en wordt aan het eind van het jaar uitgekeerd.
Maak in Excel een tabel waarbij met behulp van de vulgreep en de juiste formule in een paar seconden duidelijk wordt tot welk bedrag deze € 1000 is aangegroeid op het moment dat hij 67 jaar wordt.
Oplossing:
Zet in cel A1 de tekst leeftijd, in cel B1 de tekst jaar, in cel C1 de tekst bedrag.
In cel A2 komt te staan 18, in cel B2 2000, in cel C2 het bedrag 1000.
Vul de kolommen aan tot 67 jaar met behulp van de vulgreep. In welk jaar gaat hij met pensioen? (2049)
Zet de juiste formule in cel C3 en vul deze kolom aan met behulp van de vulgreep.
Welk bedrag ontstaat er bij 67 jaar? (6833,35)
Zorg ervoor dat in heel kolom C de bedragen steeds met twee cijfers achter de komma staan genoteerd.
Opdracht 2
Iemand koopt 1 januari 2000 een huis van € 240.000. De hypotheek duurt 30 jaar en het laatste jaar van de looptijd is 2029, omdat de hypotheekperiode begint 1 januari 2000.
De rente is per jaar 7%, er wordt gedurende 30 jaar steeds elk jaar 8000 afgelost.
Maak in Excel een tabel voor de gehele looptijd van deze hypotheekschuld.
Je hebt de volgende kolomnamen staan in de cellen A1 tot en met E1:
Jaar Hypotheekbedrag Aflossing Rente Jaarlijkse betaling
Als je er niet uitkomt, dan kun je eerst de filmtips bekijken!!
Filmtip: Hypotheek
Filmtip: Rente op rente
Les 7 Indexcijfer
Opdracht 1
We hebben de volgende gegevens:
Jaar
Toeristen in Groningen
indexcijfer
2000
12000
2001
11500
2002
15000
2003
15600
2004
14900
2005
11900
2006
12870
2007
14567
a. Je kunt deze gegevens vinden in het document Oefenbestand excel, tabblad toeristen
In cel A2 komt het jaar 2000 te staan. Let op de manier waarop je het jaartal invoert!
Vul de jaren met de vulgreep aan.
EERST LEZEN (groot genoeg om niet te vergeten?)
Uitleg indexcijfer: een indexcijfer is een getal waarmee je in procenten alle jaren vergelijkt met een basisjaar. Het basisjaar in bovenstaande reeks is 2000. We willen dus alle jaren vergelijken met het jaar 2000. Als je naar de getallen kijkt is het niet eenvoudig om snel te zien hoe groot de verandering was in procenten. Daarvoor heb je indexcijfers nodig. Het basisjaar van een serie indexcijfers is altijd 100. Je kunt in de tabel in excel dus in de cel naast 12000 het getal 100 typen in cel B2.
Je berekent een indexcijfer als volgt: =(B3/$B$2)*100
Uitleg: Je wilt eerst het jaar 2003 vergelijken met het jaar 2002. De waarde van het jaar 2003 staat in cel B3 en de waarde van het jaar 2002 staat in cel B2. Dus deel je B3 door B2. Maar omdat je ook alle volgende jaren straks wilt delen door B2 moet je in de formule de cel B2 als het ware vastzetten. Dit doe je door het $ teken om de B van B2 heen te zetten. Dit valt verder niet te begrijpen, want het is gewoon een stukje excel-taal (dus gewoon doen).
Je doet alles keer 100, omdat je een percentage wilt hebben.
Zie eventueel het filmpje indexcijfer en grafiek
Opmerking: een indexcijfer is altijd een heel getal. Je kunt de decimalen weghalen door een paar keer te klikken op de knop Minder decimalen, net zolang tot er een heel getal staat.
b. Sleep nu de formule naar beneden voor de rest van de jaren.
Als het goed is heb je onderstaand resultaat
Jaar
Toeristen in Groningen
indexcijfer
2000
12000
100
2001
11500
96
2002
15000
125
2003
15600
130
2004
14900
124
2005
11900
99
2006
12870
107
2007
14567
121
Maak hier nu een grafiek van
In de grafiek willen we de jaren en de indexcijfers hebben, maar niet de absolute aantallen (de getallen per jaar; bijvoorbeeld 12000 is een absoluut aantal, een precies aantal).
Dit doe je als volgt:
Selecteer de jaartallen en ook het woord jaar.
Druk nu de CTRL knop in
Selecteer nu de indexcijfers, inclusief het woord indexcijfer.
Klik nu op invoegen en begin de grafiek te maken.
Zie eventueel nog eens het filmpje: maken van indexcijfer en grafiek
Houd bij het maken van de grafiek er rekening mee dat de gegevens in een oogopslag voor iedereen die het moet zijn ook duidelijk moeten zijn.
TIP: voor het aangeven van een ontwikkeling door de jaren heen is een lijngrafiek of een kolomgrafiek het meest duidelijk
Opdracht 2 Een grafiek maken en een trendlijn toevoegen
Een trendlijn geeft de gemiddelde ontwikkeling door van gegevens over een aantal jaren.
Voorbeeld:
De vraag is nu of dit bedrijf het nu wel of niet goed doet. Immers, de winsten verschillen nogal van jaar tot jaar.
Doen: Zet bovenstaande gegevens handmatig in een excel werkblad. Gewoon overtypen
Bekijk hiervoor eerst even het filmpje: trendlijn toevoegen
Aandachtspunten: voer de jaren in als jaartal (denk aan het accent) en doe dit met behulp van de automatische vulgreep.
De getallen moet je overtypen, het euroteken doe je zoals je in het filmpje trendlijn hebt gezien. Een € kun je ook makem met Control Alt 5
Maak van deze gegevens een grafiek en voeg een trendlijn toe. De naam van de trendlijn is Trend winst bedrijf X
Maak de grafiek op volgens de volgende aanwijzingen:
- Geef de trendlijn een rode kleur en geef deze een breedte van 2,25 pt.
- geef de gegevensreeks een zwarte kleur.
- maak het grafiekgebied lichtgrijs
- maak het tekengebied lichtblauw
Geef de grafiek de naam Winst bedrijf X
Geef de x-as de naam Jaar
Geef de y-as de naam Nettowinst in euro’s
Het resultaat ziet er uit zoals hieronder (in de digitale versie in de NELO, de papieren versie is zwartwit)
Opdracht 3
Je hebt de volgende gegevens tot je beschikking.
Jaar Aantal accounts op Facebook in Nederland (x 1000)
2004 1000
2005 1100
2006 1400
2007 2800
2008 4800
2009 5500
2010 5500
2011 6000
2012 8000
Neem deze gegevens over in een nieuw excel werkblad op tabblad 1. Denk eraan dat je de jaartallen als jaar invoert (het accentteken!)
Geef dit tabblad de naam Facebook
Bereken de indexcijfers van deze gegevens met behulp van de juiste formule (laat eventueel de formule controleren, nadat je deze hebt gemaakt). Het basisjaar is 2004.
Maak een lijngrafiek van de jaren en de indexcijfers (dus niet van de echte aantallen!)
Geef de grafiek de naam Aantal accounts op Facebook (x1000)
Geef de x-as (horizontale as) de naam Jaar
Geef de y-as de naam Facebook
Maak het grafiekgebied roze met effen opvulling
Geef het tekengebied een lichte opvulling met kleurovergang
Geef de gegevensreeks de kleur zwart (ononderbroken lijn) met dikte 3,25 pt
Voeg een lineaire trendlijn toe, met de kleur donkerblauw en dikte 1,75 pt en met als naam Trend Facebook
FILMTIP
Indexcijfer
Les 8 Percentages en grafieken
Je kunt met excel ook heel eenvoudig percentages berekenen en de verhoudingen in een grafiek laten zien.
Opdracht 1
We hebben de volgende gegevens over waar Nederlanders naar toe gaan op vakantie. Zet deze gegevens in een excel werkblad. Begin bij A1 (Land in A1, Griekenland in A2 enzovoort)
Land
Toeristen
Griekenland
1000
Frankrijk
1800
Spanje
800
Engeland
400
Stappen:
a)bereken eerst het totaal aantal toeristen in cel B6 met behulp van de sommeringsformul, dit is =som(begincel:eindcel)
b) Zet in cel c2 de volgende formule, om het percentage voor Griekenland te berekenen.
=(B2/$b$6)*100
Je doet dit omdat je eerst Griekenland vergelijkt (= deelt door) het Totaal (want dan krijg je een deel of percentage.
c) Je ziet dat B6 is ‘vastgezet’, omdat elk land natuurlijk met het totaal moet worden vergeleken.
d) Sleep nu de formule naar beneden, zodat je snel voor elk land het percentage krijgt
e) Selecteer de kolommen A en C en maak een grafiek van de landen en de percentages. Maak eerst een kolomgrafiek
Verander nu de grafie in een cirkelgrafiek
Zie ook het filmpje percentage en grafiek
Opdracht 2
Je hebt de volgende gegevens. Zet deze zelf in een nieuw excel-werkblad en geef dit de naam Leerlingen Euroborg.
Aantal leerlingen per opleiding
Reizen 300
Hotelschool 450
Bakkers 90
Koks 220
Recreatie/Leisure 250
Wellness 40
Totaal
Zet de gegevens in excel en bereken met een formule het totaal aantal leerlingen.
Bereken het percentage leerlingen per opleiding met behulp van de juiste excelformule
Maak een kolomgrafiek waarin goed zichtbaar wordt hoe de leerlingen van de Euroborg over de opleidingen zijn verdeeld. Geef iedere opleiding een andere kleur.
Maak nu van dezelfde gegevens een cirkeldiagram.
Opdracht 3
Bij de meest recente verkiezingen was in de gemeente Ukelenstein de uitslag als volgt:
Partij Aantal stemmen
PvdA 2300
VVD 2320
CDA 1300
PVV 1699
SP 1241
D66 450
Groenlinks 1000
PvdDieren 60
SGP 100
Christen Unie 10
Tip: bekijk eerst de filmpjes cirkelgrafiek en percentage en grafiek voor je begint.
Neem deze gegevens over in een excel werkblad
Bereken het totale aantal stemmers met behulp van de juiste formule
Bereken het percentage stemmen per partij met behulp van de juiste formule (laat eventueel controleren!) Het percentage moet twee cijfers achter de komma hebben.
Maak een drie dimensionale cirkelgrafiek van de partijen en het percentage stemmen (het werkelijk aantal stemmen zit dus niet in de grafiek).
Voeg de gegevenslabels toe aan de grafiek zodat in de grafiek de percentages zichtbaar worden.
Geef de gegevenspunt van de PvdA een rode kleur
Geef de gegevenspunt van het CDA een heldergroene kleur
Geef de gegevensreeks van de VVD een azuurblauwe kleur (opvulling met kleurovergang)
Geef de PVV een bruine kleur
Geef Groenlinks de opvulling Horizon
Geef het grafiekgebied een lichtgrijze kleur
Geef de grafiek de naam Uitslag verkiezingen Ukelenstein
Een en ander ziet er na afloop als volgt uit:
Les 9 Gemiddelde en meer
Met excel kun je snel achterhalen wat het gemiddelde, wat de hoogste en wat de laagste waarde is in een groot bestand met allemaal getallen.
Met de formule =gemiddelde(bereik van de cellen) kun je snel het gemiddelde berekenen
Met de formule = max(bereik) en =min(bereik) kun je snel de hoogste en de laagste waarde berekenen.
Voorbeeld: in onderstaande tabel zie je de ingevulde database die wordt bijgehouden van de regenval in Eelde, per maan, voor de jaren 2006 tot en met 2008.
Je kunt deze gegevens vinden in het bestand oefenbestanden excel, tabblad regenval
Opdracht 1
a. Hoeveel is nu de gemiddelde regenval over de jaren 2006 tot en met 2008?
Oplossing: het bereik van de tabel is van cel B3 (regenval in januari 2006) tot en met cel D14 (regenval in december 2008).
Type in cel G4 het woord Gemiddelde en type in cel G5 de formule =gemiddelde(B3:D14) en bevestig met de groene V
De gemiddelde regenval was ……………… (178)
b. Wat was de hoogste regenval ooit?
De formule hiervoor luidt =max(B3:D14) (500)
Wat was de laagste regenval ooit
De formule luidt: =min(B3:D14) 40
d. Voorwaardelijke opmaak:
Met voorwaardelijke opmaak kun je met behulp van kleuren verschil maken in de tabel
Bijvoorbeeld: je maakt alles wat hoger is dan het gemiddelde rood en alles lager dan het gemiddelde groen. Zo kun je snel zien wat natte of droge maanden waren.
Je doet dit als volgt:
Selecteer alle cellen met een getal
Kies voor Voorwaardelijke opmaak en daarna MARKERINGSEGELS VOOR CELLEN.
Kies groter dan
Vul het gemiddelde in (dat had je al laten uitrekenen) en kies RODE TEKST
Ga nu weer naar markeringsregels voor cellen en kies kleiner dan
Vul het gemiddelde in en kies lichtgroene opmaak
De tabel is nu verdeeld in een groen deel en een rood deel.
Probeer nog wat andere effecten uit.
Zie eventueel het filmpje voorwaardelijke opmaak
Opdracht 2
Een docent heeft in excel een cijferlijstje van een klas
Dit lijstje zit er als onderstaand uit.
Jan 3,8
Karin 6,4
Tania 9,0
Dolores 7,9
Tasje 3,1
Toos 5,5
Fabian 5,0
zet deze gegevens in een werkblad van excel
bereken het gemiddelde cijfer van deze klas
maak een voorwaardelijke opmaak zodat de docent in een keer kan zien wie er voldoende staat en wie niet. Hierbij hou je aan: cijfer 5,5 of hoger dan wordt de opmaak geel en cijfer lager dan 5,5 dan wordt de opmaak rood.
Opdracht 3
In het excelbestand oefenbestanden excel, tabblad bezoekers zie je een tabel met daarin het aantal bezoekers aan een website, per maand
Bereken met de sommeringsformule voor elk jaar het aantal bezoekers per jaar.
Bereken het gemiddeld aantal bezoekers over de gehele periode, dus over alle maanden waarvan gegevens beschikbaar zijn.
Bereken het maximaal aantal bezoekers ooit dat in een maand deze website bezocht
Bereken het minimaal aantal bezoekers ooit dat in een maand deze website bezocht
Geef de tabel een voorwaardelijke opmaak voor aantal bezoekers boven of gelijk aan of lager dan het gemiddelde met behulp van de kleuren rood en groen.
Maak een kolomgrafiek waarin alle gegevens zichtbaar worden.
Les 10 Opmaak van cellen
Opdracht 1
Type in Cel A1 de tekst: Aantal konijnenfokkers in Nederland
Type in Cel B1 de tekst Aantal nertsenfokkers in Nederland
Wat gebeurt er met de teksten?
Het past duidelijk niet in de kolom.
- Selecteer de cellen A1 en A2
- Kies voor Opmaak – Celeigenschappen
Kies voor Uitlijning
Kies voor (tekst)terugloop
Probeer ook uit wat er gebeurt als je kiest voor Tekst passend maken
Probeer ook uit wat er gebeurt als je de tekstrichting aanpast
Opdracht 2
Selecteer de cellen A1 en A2 en kies voor Opmaak, kolom. Pas de kolombreedte aan naar 5. Pas de breedte van kolom 3 en 4 aaan naar 20
Selecteer de rij 4 en pas de hoogte van de rij aan op dezelfde manier naar 20.
Selecteer de rijen
Opdracht 3
Neem een nieuw werkblad en geef dit de naam opdracht 3
a. Invoeren van gegevens
Type in cel A1 de tekst Klas 1B
Type in cel A2 de tekst Achternaam en in cel B2 de tekst Voornaam
Type in cel A3 de achternaam Dijk van en cel B3 de voornaam Jurjen
Voer ook de volgende namen op dezelfde wijze in
Wouterse Kees
Kock de Patricia
Neushoorn Karel
Tasjesplant Tokyo
Precies Pietje
Virides Lester
Lepidus Timon
b. Selecteer alle cellen waar namen in staan, inclusief de cellen met de kopjes en zet de kolombreedte op 20
c. Alfabetiseer de namenlijst
d. Maak de tekst in de cellen A2 en B2 dikgedrukt en centreer deze.
e. Noteer in de cellen C2, D2, E2,F2 en G2 de respectievelijke data 1 september, 8 september, 15 september, 22 september, 29 september.
TIP: Doe dit voor de eerste cellen (C2 en D2) en vul aan met de vulgreep naar rechts
f. Selecteer de cellen A2 tot en met G7.
Kies voor Opmaak – Celeigenschappen – Rand. Klik vervolgens op de vierkantjes “omtrek” en “rand”.
Het resultaat is een lijst met leerlingen en keurige hokjes.
g. Kies voor Invoegen – Koptekst en type je eigen naam in witte veld
h. Kies voor Bestand Afdrukvoorbeeld – Instellen - en verander van staand naar liggend. Je kunt nu printen.
OF: Kies voor Afdrukken – Eigenschappen – en verander van staand naar liggend en daarna Ok en printen.
Opdracht 4
Klik in cel A20
Kies voor Invoegen – Opmerking
Haal de tekst weg die er staat en type de tekst HELP MIJ
Verlaat de cel.
Beweeg nu met de cursor over de cel (niet klikken).
Je ziet nu de opmerking weer.
Opdracht 5
Selecteer de cellen A3 tot en met B7.
Klik op de verfpot in de werkbalk en kies de kleur geel
Selecteer de cellen C3 tot en met D7
Klik op de verfpot in de werkbalk en kies de kleur groen
Opdracht 5 Kopregels bevriezen
Bij grote bestanden van excel lukt het niet meer om alles in beeld te houden.
Ga naar het oefenbestand en ga naar het tabblad bevriezen.
Je ziet een ledenbestand van 1 tot en met 63.
Ga naar lid nummer 61.
Zie je nu nog de kopregels bovenaan in het bestand? NEE dus.
Daar is een oplossing voor en die heet Titels blokkeren.
Wat moet je doen?
1. Klik in cel A2
2. Klik op beeld
3. Klik op Blokkeren
4. Klik op Titels blokkeren.
5. Scroll naar beneden, naar lid 61. Je ziet dat nu de titels blijven staan.
Op dezelfde manier kun je ook kolommen blokkeren.
1. Klik in cel A2
2. Klik op blokkeren
3. Klik op 1e kolom blokkeren
Je kunt nu de schuifbalk naar rechts bewegen zonder dat de 1e kolom uit beeld verdwijnt.
Les 11 Excel als database
Opdracht 1 Database
Excel werkt erg goed als database. In een database kun je heel snel gegevens vinden in een hele grote berg gegevens. Het scheelt uren zoekwerk.
Bekijk de video om te kijken hoe je er een maakt.
Daarna ga je er zelf een maken, met behulp van een formulier. Ook dit kun je leren in de video.
Een database maken
Je hebt de volgende gegevens tot je beschikking van de “Vereniging tot nut van het Gemak”. Let op: JE HOEFT DIT NIET OVER TE TYPEN IN EXCEL. Het bestand met de gegevens heet DATABASE. Het staat onder aan deze les.
Startjaar
Geslacht
Voornaam
Achternaam
Woonplaats
Groep
2010
m
Kees
Wouterse
Groningen
1
2009
v
Patricia
Kock de
Amsterdam
2
2005
m
Karel
Neushoorn
Groningen
3
2010
v
Tokyo
Tasjesplant
Amsterdam
3
2009
m
Pietje
Precies
Rotterdam
1
2006
m
Lester
Virides
Rotterdam
2
2005
m
Timon
Lepidus
Assen
3
2009
v
Wanda
Kiloknaller
Assen
1
2005
m
Koos
Werkeloos
Amsterdam
2
2010
v
Tanja
Suikerpot
Groningen
3
2009
v
Foekje
Dillema
Amsterdam
1
2007
v
Iona
Joric
Assen
3
2007
m
Fonseca
Hamman
Rotterdam
2
2008
v
Harma
Binnenband
Groningen
2
2008
m
Toby
Piramidespel
Rotterdam
3
2010
m
Rocco
Raket
Groningen
1
2008
v
Dieuwke
Uvertouw
Assen
2
2010
m
Robert
Uvertouw
Assen
1
2009
v
Rianne
Wijk van
Groningen
3
2006
v
Wanda
Touwslager
Rotterdam
3
2009
m
Jan
Petfles
Groningen
1
Het valt op dat de namenlijst niet op alfabet staat. Open het bestand DATABASE en volg de aanwijzingen:
1. Klik op de kolom achternaam (kolom D, op de letter klikken), zodat die kolom actief wordt. Klik op de knop Alfabetiseren en Filteren en kies AZ van laag naar hoog. Klik bij de melding Selectie uitbreiden JA en daarna OK.
Nu staan de namen op alfabet
2. We gaan nu een tabel maken, om snel gegevens te kunnen filteren. Ik wil bijvoorbeeld alleen de leden zien die in Groningen wonen.
Ga op een willekeurige cel staan in de tabel.
Klik op Invoegen en daarna op tabel. Excel geeft nu het bereik (automatisch aan). Klik op OK.
Je ziet nu een tabel voor je met filtermogelijkheden. Klik op het neerwaarts gerichte pijltje naast woonplaats. Vink alle plaatsnamen uit, behalve Groningen. Dit is het resultaat.
Startjaar
Geslacht
Voornaam
Achternaam
Woonplaats
Groep
2010
m
Kees
Wouterse
Groningen
1
2005
m
Karel
Neushoorn
Groningen
3
2010
v
Tanja
Suikerpot
Groningen
3
2008
v
Harma
Binnenband
Groningen
2
2010
m
Rocco
Raket
Groningen
1
2009
v
Rianne
Wijk van
Groningen
3
2009
m
Jan
Petfles
Groningen
1
Opdracht:
Doe nu hetzelfde voor de selectie van geslacht en beantwoord de vraag: hoeveel vrouwen telt de vereniging?
(je antwoord moet zijn 10)
Beantwoord ook de volgende vragen:
1. Hoeveel leden zijn er in 2010 lid geworden?
2. Wie zitten er allemaal in groep 1? Laat een screenshot met je antwoord zien aan de docent!
3. Hoeveel leden zijn geboren in 1980? Idem
Opdracht:
Maak in excel een tabel van 20 van je Facebookvrienden met de volgende kolommen.
Maak hier vervolgens een tabel van met filtermogelijkheden.
Maak vervolgens een selectie op:
- aantal vrienden (van hoog naar laag)
- achternaam (van A- Z)
Voornaam Achternaam Woonplaats Geboortejaar Geslacht Aantal vrienden
Je bent vrijwilliger bij de Buurtbieb, een kleinschalige organisatie voor mensen in de buurt Kimmenadert, die als doel heeft de bewoners van de buurt gratis in staat te stellen boeken te lezen. Voor dit doel hebben ze – als begin – een paar dozen vol met boeken cadeau gekregen van personen die gingen verhuizen en veel boeken weg hebben gedaan.
Je opdracht is om een digitaal bestand samen te stellen voor de buurtbewoners waarin ze gericht kunnen zoeken naar boeken die hun interesse hebben.
De boeken moeten in een minidatabase worden opgenomen en voorzien van metadata, zodat de bewoners gemakkelijk kunnen zoeken.
Hieronder staat een lange rij met boeken die allemaal in de database moeten worden ingevoerd, voorzien van de volgende metadata:
Achternaam auteur
Voornaam auteur
Nationaliteit auteur
Genre van het boek
Jaar van uitgifte van het boek
Uitgeverij
Titel van het boek
Taal waarin het boek is geschreven
In de boekendoos zitten alle boeken, maar niet de informatie die in de database er bij moet komen. Die moet je dus opzoeken op internet.
Excel is heel goed in het snel vergelijken van grote hoeveelheden cijfers. Zo snel dat geen mens er met een rekenmachine tegen op kan. Het scheelt dus tijd door slim om te gaan met de FUNCTIES in Excel.
De Functie ALS vergelijkt steeds twee getallen met elkaar en geeft dan een soort van beoordeling of conclusie. Bijvoorbeeld of in een bepaalde periode het budget van een project is overschreden of niet. Zie voor een voorbeeld het bestand hieronder.
Bekijk voor je aan de opdracht begint ook eerst de videotutorial van Microsoft!
Om met dit soort functies te kunne werken moet je eerst even iets weten over wat ze bij Excel een "logische test" noemen.
In gewoon Nederlands: is iets wel of niet waar.
Bijvoorbeeld: heeft een verkoper genoeg omzet gemaakt om in aanmerking te komen voor een bonus. Stel dat de norm voor een bonus een omzet van € 100.000 is, dan wil Excel weten of dit wel of niet waar is.
Je hebt verschillende "operatoren" die je kunt gebruiken in logische testen. Die zijn:
> groter dan
>= groter dan of gelijk aan
< kleiner dan
<= kleiner of gelijk aan.
= gelijk aan
In het voorbeeld van hierboven zou je dus de operator >= nodig hebben, want de omzet moet minimaal € 100.000 zijn om de verkoper een bonus te kunnen geven.
Je kunt de kunst afkijken in het bestand functieals hieronder.
Maar bekijk eerst de fllm met de uitleg hoe het moet of open de handleiding van Microsoft.
De functies van Excel moeten heel nauwkeurig worden geschreven, anders werken ze niet. De tekens ; (puntkomma) en "(aanhalingstekens) moeten echt precies op de goede plek staan.
Voorbeeld
Je zag in het excel document als formule staan =ALS(B3>C3;"fouteboel";"prima")
Bouw van een ALS-Formule: (Let op: als je een engelstalige Excel hebt, is het de IF-formula)
Zoals altijd begint de formule met een = (zonder = weet Excel echt niet wat hij moet doen)
Vervolgens typ je ALS om aan te geven dat je zometeen een voorwaarde gaat stellen die tot een conclusie moet leiden. In gewone mensen-taal: ALS de inhoud van Cel B3 groter is dan de inhoud van Cel C3, dan zijn de echte kosten hoger dan de voorspelde kosten (het budget), met als conclusie in bovenstaande formule: "foute boel". Na ALs komt ( (=haakje openen)
Daarna komt het zogenaamde argument: B3 > C3(> betekent groter dan en < betekent kleiner dan)
Let nu op de plaats van de puntkomma ; die komt na het argument.
De mogelijke conclusies komen tussen aanhalingstekens, gescheiden door weer een puntkomma.
Je sluit de formule af met een ) =haakje sluiten
OPDRACHT
Open onderstaand document. Je ziet voor twee jaren een overzicht van gemaakte kosten en voorspelde kosten. Je wilt snel weten in welke maanden het budget is overschreden, zodat je snel kunt zoeken naar oorzaken.
Je maakt dus voor deze getallenreeks een Als Formule. Als het budget is overschreden moet er de conclusie komen "VERLIES" en als de werkelijke kosten lager zijn dan de verwachte kosten moet er de conclusie komen "GELD OVER".
Zorg er verder voor dat de kolombreedte van de cellen waarin de conclusies komen een breedte heeft van 20.
Je kunt de functie ALS prima combineren met extra voorwaarden, zoals bijvoorbeeld een gemiddelde. Een goed voorbeeld hiervan is een hele serie toetscijfers van een klas met studenten. In onderstaand exceldocument zie je een voorbeeld van een klas met studenten en al hun toetscijfers mooi naast elkaar. Een student is geslaagd als het gemiddelde cijfer hoger is dan een vijf en een half (55).
Je ziet dat in de formule terug. =ALS(GEMIDDELDE(B2:D2)>55;"geslaagd";"gezakt")
OPDRACHT
Bestudeer eerst het voorbeeld op blad 1 van het document hieronder.
Ga daarna naar blad 2. Daar zie je een overzicht van een klas met al hun toetscijfers.
Maak de juiste formule, zodat de mentor direct kan zien we er geslaagd dan wel gezakt is.
Lever dit document in in de inlevermap in de ELO en zet er even bij wie er gezakt zijn.
Bron: Sander Kollerie Cursus Excel Geavanceerde functies en formules Deel 3: ALS en gerelateerde functies in Excel
Zullen we het een beetje moeilijker maken?
In het voorbeeld van zojuist zagen we dat om te slagen de student een gemiddeld cijfer van 55 of hoger moest halen. Dat is in de werkelijkheid niet erg realistisch. Veel opleidingen hanteren een extra voorwaarde. Bijvoorbeeld dat geen enkel cijfer lager dan 35 mag zijn.
Ook hier kan Excel ons prima helpen om snel vanuit een grote cijferbrij te bepalen wie er geslaagd of gezakt is.
We gaan aan de slag met het document dat we hiervoor ook al hebben gebruikt, het overzicht van de toetscijfers.
Volg onderstaand stappenplan om de twee voorwaarden slim met elkaar te koppelen.
1. Je ziet de cijfers van de toetsen 1 tot en met 8 in de kolommen C tot en met J, met het eerste cijfer in C2.
Wat je als eerste doet is het gemiddelde bereken per student. Dus zet je in kolom K, in cel K2 de formule (=gemiddelde C2:J2)
Als je dit hebt gedaan kun je die formule weer naar beneden slepen, zodat je nu voor elke student het gemiddelde cijfer ziet. Je mag afronden op 1 decimaal. (1 cijfer achter de komma).
2. De tweede voorwaarde om te slagen is dat alle cijfers hoger of gelijk aan 35 moeten zijn.
3. In kolom L, te beginnen met cel L2 zet je nu de formule =MIN(c2:J2). Ook die formule sleep je naar beneden, zodat je voor elke student zie wat zijn of haar laagste cijfer is.
4. Nu komt het moelijke werk, want nu moet je een formule hebben die alle twee voorwaarden combineert.
Die formule luidt =ALS(EN(K2>55;L2>=35);"geslaagd";"gezakt")
Als je deze formule in gewone mensentaal zou uitspreken, dan staat er: Als zowel het gemiddelde hoger is dan 55 als ook het laagste cijfer hoger of gelijk is aan 35, dan is de student geslaagd. Als aan één van beide voorwaarden niet wordt voldaan is de student gezakt.
Je bent leidinggevende van een verkoopafdeling en jij staat voor de vraag welke verkoper aan het eind van het jaar een kerstbonus krijgt en wie niet.
In onderstaand exceldocument vind je een overzicht van de omzet per verkoper per kwartaal en een overzicht van het aantal ziektedagen per kwartaal.
Je besluit dat er twee criteria zijn om iemand een bonus toe te kennen, namelijk:
1) de gemiddelde omzet per kwartaal moet € 5000 of hoger zijn (dus >= 5000)
2) het aantal ziektedagen per kwartaal mag niet hoger zijn dan 2 (=<2).
Welke verkopers krijgen wel een bonus en welke niet? Motiveer je antwoord door toepassing van en ALS en EN formule.
Je levert je excel bestand in in de ELO, in de daarvoor aangewezen inlevermap.
Een veel gebruikte functie in Excel is om op een aantal tabbladen dagelijkse getalsmatige informatie bij te houden waarbij de totaalcijfers op een andere pagina zichtbaar worden. Hiervoor heb je een verwijzingn naar een ander tabblad nodig.
Een voorbeeld:
Hieronder staat een bestand met de naam VerantwoordingSBUstudentvoorbeeld. Open dit bestand om dit te bestuderen.
Je ziet dat er op de tabbladen 2, 3 en 4 invulstaten zijn gemaakt waarin gewerkte uren kunnen worden ingevuld. En dat op tabblad 1 een overzicht staat waarin automatisch de totalen worden opgeteld van de uren die op de tabblade 2, 3 en 4 zijn ingevuld. Probeer het maar eens.
Opdracht
Onder aan deze bladzijde kun je een bestand vinden met de naam Omzet winkelketen LAMA. Open dit bestand.
Winkelketen Lama heeft filialen in Groningen, Leeuwarden, Assen, Zwolle en Arnhem. De directeur wil dat jij een mooi overzicht maakt waarin op de tabbladen 2 tot en met 6 de namen van de plaatsen staan en de overzichten per maand van de omzet. Maar directeuren hebben weinig tijd en deze directeur wil op tabblad 1 een overzicht hebben van het jaartotaal per plaats.
- Vul eerst overal getallen in, zodat het wat echter lijkt. Neem overal getallen tussen de 500 en de 2000, je mag zelf weten hoeveel omzet in de winkels per maand wordt gemaakt.
- Zorg dat je bij Totaal steeds de omzet van de maanden optelt; je gebruikt (uiteraard) de somformule. Doe dit voor elk filiaal.
- Zet nu in het overzicht op het eerste tabblad de juiste formule, zodat direct zichtbaaar wordt hoe groot de omzetten zijn per winkel.
Je kunt in Excel prachtige overzichten maken voor anderen om in te vullen. Bijvoorbeeld het bijhouden van gewerkte uren en dit dan automatisch laten optellen. Een voorbeeld hiervan is het urenverantwoordingsformulier dat jullie invullen om bij te houden hoeveel tijd je besteedt aan dit keuzedeel.
Wat je dan natuurlijk niet wilt is dat je formulier door iemand anders helemaal wordt veranderd! Daar is een oplossing voor, namelijk het beveiligen van cellen. Maar om te bereiken wat je wilt moet je natuurlijk ook zorgen dat het deel dat door anderen moet worden ingevuld niet beveiligd is. Want anders kunnen ze niets invullen.
OK, daar gaan we dan.
Open om te beginnen het bestand hieronder met de naam Gewerkte UREN.
Je bent meewerkend leidinggevende bij de Jumbo en één van je taken is om de gewerkt uren van de vakkenvullers bij te houden. Je hebt besloten dat dit eenvoudig kan met een exceldocument. Dit document vind je hieronder en heeft de naam Jumbowerkuren.
Het document bevat de data van januari en de namen van de medewerkers. Beveilig dit document zodanig dat:
- de data en de namen van de medewerkers niet kunnen worden gewijzigd.
- de gewerkte uren door de medewerkers wél kunnen worden ingevuld.
Als je klaar bent kun je het document inleveren onder je eigen naam in de inlevermap in de ELO.
Als je een Excel bestand hebt met meerdere werkbladen, wil je als het even kan de opmaak van al die werkbladen in een keer doen. En niet alles per werkblad. Daar is een handige truc voor.
Bekijk eerst de uitlegfilm.
Maak daarna deze opdracht.
Open het oefenbestand dat je hieronder aantreft.
Maak op alle tabbladen - met behulp van groeperen - de opmaak van de cel waar Filiaal staat 14 punten groot en bovendien rood en dik gedrukt
Voeg in een keer, dus weer met behulp van groeperen - de artikelgroep Frisdrank in.
Maak in een keer een formule aan waar de totaalzet van het filiaal komt te staan.
Maak als bewijs dat je het echt zelf kunt en ook echt zelf gedaan hebt een screencastfilm van deze opdracht en lever deze in in de inlevermap Groeperen in Excel
Het arrangement Excel 2013 is gemaakt met
Wikiwijs van
Kennisnet. Wikiwijs is hét onderwijsplatform waar je leermiddelen zoekt,
maakt en deelt.
Auteur
Klaas ter Veen
Je moet eerst inloggen om feedback aan de auteur te kunnen geven.
Laatst gewijzigd
2017-04-14 10:52:44
Licentie
Dit lesmateriaal is gepubliceerd onder de Creative Commons Naamsvermelding 3.0 Nederlands licentie. Dit houdt in dat je onder de voorwaarde van naamsvermelding vrij bent om:
het werk te delen - te kopiëren, te verspreiden en door te geven via elk medium of bestandsformaat
het werk te bewerken - te remixen, te veranderen en afgeleide werken te maken
voor alle doeleinden, inclusief commerciële doeleinden.
Leeromgevingen die gebruik maken van LTI kunnen Wikiwijs arrangementen en toetsen afspelen en resultaten
terugkoppelen. Hiervoor moet de leeromgeving wel bij Wikiwijs aangemeld zijn. Wil je gebruik maken van de LTI
koppeling? Meld je aan via info@wikiwijs.nl met het verzoek om een LTI
koppeling aan te gaan.
Maak je al gebruik van LTI? Gebruik dan de onderstaande Launch URL’s.
Arrangement
IMSCC package
Wil je de Launch URL’s niet los kopiëren, maar in één keer downloaden? Download dan de IMSCC package.
Wikiwijs lesmateriaal kan worden gebruikt in een externe leeromgeving. Er kunnen koppelingen worden gemaakt en
het lesmateriaal kan op verschillende manieren worden geëxporteerd. Meer informatie hierover kun je vinden op
onze Developers Wiki.
Omzet per medewerker
