EXCEL
Inhoudsopgave
1. Gegevensreeksen en celadressering ................................................................................. 3
1.1 Gegevensreeksen ................................................................................................. 3
1.2 Snel aanvullen aka flashfill ..................................................................................... 5
1.3 Relatieve adressering ............................................................................................ 6
1.4 Absolute adressering ............................................................................................. 6
1.5 Gemengde adressering ........................................................................................... 6
1. Voorbereiding Quiz................................................................................................... 7
1. Startbestand les 1 .................................................................................................... 7
2. Als en Datumfuncties ................................................................................................... 8
2.1 Eenvoudige ALS.......................................................................................................... 8
2.2 ALS – EN - OF ............................................................................................................ 9
2.3 ALS – EN ........................................................................................................... 10
2.4 Geneste ALS ............................................................................................................ 11
2.5 Rekenen met datums .................................................................................................. 12
2.6 Eenvoudige datumfuncties ........................................................................................... 13
2.7 Complexere datumfuncties ........................................................................................... 14
2.8 Gegevensvalidatie ..................................................................................................... 16
2. Voorbereiding Quiz.................................................................................................. 18
2. Test les 2 ................................................................................................................. 20
Startbestand Les 2 ......................................................................................................... 23
3. Draaitabellen en grafieken .............................................................................................. 23
3.1 Draaitabel de basis .................................................................................................... 23
3.2 Draaitabel verder aanpassen ......................................................................................... 24
3.3 Draaitabel waardeveldinstellingen .................................................................................. 24
3.4 Draaitabel tab analyseren ............................................................................................ 25
3.5 Draaitabel tab ontwerpen ............................................................................................ 26
3.6 Draaigrafiek maken .................................................................................................... 28
3. TESTVRAGEN ............................................................................................................. 30
Test les 3 .................................................................................................................... 32
Uitleg startbestand 3 ........................................................................................................ 34
Toetsinstructies: herhalingstoest les 3................................................................................... 35
4. Verticaal zoeken – Voorwaardelijke opmaak ........................................................................ 43
LinkedIn Learning: video over aangepaste getalnotaties ............................................................. 43
4.1 Verticaal zoeken – onwaar ............................................................................................ 44
4.2 Verticaal zoeken – eerste kolom ..................................................................................... 45
4.3 Herhaling – geneste ALS............................................................................................... 46
1
, 4.4 Verticaal zoeken – waar - sorteren .................................................................................. 46
4.5 Voorwaardelijke opmaak – basis ..................................................................................... 48
4.6 Voorwaardelijke opmaak – verwijzen – formules.................................................................. 48
4. Voorbereiding Quiz ..................................................................................................... 50
4. Test Les 4................................................................................................................. 51
Uitleg startbestand ........................................................................................................ 54
5. Tekstfuncties - x.als - xxx.als ................................................................................ 54
5.1 tekstfuncties: .......................................................................................................... 54
HOOFDLETTERS, kleine letters en beginletters ........................................................................ 54
Deel .......................................................................................................................... 54
Links .......................................................................................................................... 55
Rechts ........................................................................................................................ 55
Tekst samenvoegen 1 ...................................................................................................... 55
Tekst samenvoegen 2 ...................................................................................................... 55
5.2 tekstfuncties: .......................................................................................................... 56
Substitueren ................................................................................................................ 56
VIND.ALLES .................................................................................................................. 56
Lengte ........................................................................................................................ 57
5.3 tekstfuncties: .......................................................................................................... 59
Som ........................................................................................................................... 59
GEMIDDELDE.ALS ........................................................................................................... 60
AANTAL.ALS ................................................................................................................. 60
5.4 tekstfuncties: .......................................................................................................... 61
SOMMEN.ALS ................................................................................................................ 61
5.5 tekstfuncties: .......................................................................................................... 61
GEMIDDELDEN.ALS .......................................................................................................... 61
ALS.FOUT .................................................................................................................... 61
5.6 tekstfuncties: .......................................................................................................... 62
AANTALLEN.ALS............................................................................................................. 62
5.Voorbereiding Quiz ...................................................................................................... 63
5.Test Les 5 ................................................................................................................. 64
5.Startbestand les 5........................................................................................................ 66
6. Sorteren en filteren ...................................................................................................... 69
6.1 SORTEREN .............................................................................................................. 69
6.2 FILTEREN ................................................................................................................ 70
6.3 GEAVANCEERD FILTEREN .............................................................................................. 71
6. Voorbereiding quiz ...................................................................................................... 74
6. Test Les 6................................................................................................................. 75
6. Startbestand les 6 ....................................................................................................... 78
Integratieoefeningen ........................................................................................................ 78
Proefexamen ICT MC......................................................................................................... 79
2
, 1. Gegevensreeksen en celadressering
1.1 Gegevensreeksen
1.1.1 REEKS KOPIEREN
Cellen kopieren: Slepen en doortrekken naar beneden aan het vulblokje
1.1.2 REEKS DOORVOEREN
Reeks doorvoeren
→ werkdagen doorvoeren (enkel werkdagen worden doorgevoerd)
3
,→ datums doorvoeren
➔ datums van ‘werkdagen’ doorvoeren
→ maanden doorvoeren
o Zelfde principe bij uren,...
o Bvb lokaal 1, lokaal 2,...lokaal 12,......lokaal 102,...
Kwartaal 1, kwartaal 2, kwartaal 3, kwartaal 4, kwartaal 1, kwartaal 2,... (excel weet dus
dat er maar 4 kwartalen zijn)
1.1.3 GROEIREEKS
Zelfde met datums, uren, ...
4
, 1.1.4 GROEITREND – LINEAIRE TREND - SNEL AANVULLEN
Slepen met RMK
1.2 Snel aanvullen aka flashfill
Suggesties om snel aan te vullen
1.2.1 Naamlijst aanvullen
-> slepen aan vulblok , trek door
->kies snel aanvullen
1.2.2 Achternaam aanvullen
-> trek door
-> kies snel aanvullen
5
,OF OP DEZE WIJZE:
doortrekken
1.3 Relatieve adressering
Formules doortrekken aangepast aan de cellen (verschuifd mee)
1.4 Absolute adressering
Bepaalde delen in formules vastzetten zodat, ondanks de formule doorgetrokken wordt, de
verwijzing hetzelfde blijft. Hievoor gebruiken we $-teken (via F4)
VB btw berekenen.
o De btw nl 21% staat in B1
o Het bedrag excl btw staat in kolom A (A4, A5, A6, A7,A8 en A9)
We typen voor bedrag incl btw =A4*(1+$B$1)
1.5 Gemengde adressering
Een verwijzing vastzetten (maw absoluut adresseren) in combinatie met relatieve gegevens
6
, 1. Voorbereiding Quiz
➔Werkboek (workbook), een workbook bestaat uit 1 of meerdere werkbladen (worksheets)
➔ =A2-(A2*$E$1)
1. Startbestand les 1
Startbestand Doornemen + maken
Filmpje Uitleg startbestand bekijken
Startbestand oplossing doornemen
7
, 2. Als en Datumfuncties
2.1 Eenvoudige ALS
Met een ALS-functie kun je berekeningen in excel laten afhangen van een voorwaarde.
VB:
o Kolom A: het product
o Kolom C: prijs
o Kolom D: aantal stuks
o Kolom E: aantal stuks*prijs
In E6 wordt met de SOMfunctie het totaal berekend
=SOM(E2:E5)
In E7 gaan we berekenen met een ALSfunctie of er korting wordt gegeven
Er wordt korting gegeven wanneer het aankoopbedrag groter of gelijk is aan 1500 (deze
grenswaarde staat in B8) . De korting is 2% (staat in B7)
Meerdere manieren om een functie in te voeren. Nl door gewoon te typen in de formulebalk/ tab
“formules/ icoontje formule aanklikken
=ALS()
Werkwijze 1:
We gaan de verschillende functieargumenten invullen
o Logischte test: is een voorwaarde nl in dit vb “als het bedrag groter of gelijk is aan 1500”
o DUS E6>=B8
o Waarde als waar: er wordt dan korting gegeven nl 2%
o DUS E6*B7
o Waarde als onwaar: 0
Werkwijze 2:
We beginnen met typen
=ALS(E6>=B8;E6*B7;0)
8
, 2.2 ALS – EN - OF
Bij de ALS-functie kun je de berekening ook laten afhangen van meerdere voorwaarden. Je dient
hiervoor dan een extra functie te nesten nl de OF-functie of de EN-functie.
We hebben het aantal producten, het bedrag en de grenswaarden voor eventuele korting.
OF dusss als aan 1 van beide voorwaarden voldaan wordt => korting
We gaan naar de tab formules → rubriek logisch → functieagrumenten voor de OF →
1ste logische test (1ste voorwaaarde) is het aantal is groter dan de 1ste grenswaarde
2de logische test is het aankoopbedrag is groter dan de 2de grenswaarde
...
Je kunt nog meer voorwaarden instellen!
We krijgen dus de waarde WAAR, in deze test is de waarde dus groter dan 10 (1 van beide
voorwaarden is voldaan)
We kunnen bij Korting het volgende doen:
=ALS(B13;E13;E14)
Als aan B13 wordt voldaan dan wordt er korting gegeven van 0,1. Anders wordt er geen korting
gegeven.
! ➔ we kunnen ook die OF in die ALS nestelen dan heb je geen tussenstap nodig
=ALS(OF(B11>E11;B12>E12);E13;E14)
9
, De EN-functie is hetzelfde met het enige verschil dat aan beide voorwaarden moeten voldaan
worden.
Met cursos in B13 staan → tab “formules” → Logisch → EN → bij “functieargumenten”
- de 1ste logische test B11>E11
- de 2de logische test B12>E12
We krijgen als resultaat in B13 ONWAAR te zien. Aan beide voorwaarden moet voldaan worden.
! ➔ we kunnen opnieuw die EN in die ALS nestelen dan heb je geen tussenstap nodig
=ALS(EN(B11>E11;B12>E12);E13;E14)
2.3 ALS – EN
In dit vb zie je de naam van de verkopers, de leeftijd die ze hebben ennnn de omzet die ze hebben
gerealiseerd.
We gaan nu bepalen of ze een bonus van €250 krijgen of een fles wijn.
Bonus van €250 indien:
1ste voorwaarde is jonger zijn van 25
2de voorwaarde is een van omzet gerealiseerd van 10000
In het ander geval: fles wijn
We moeten dus een AlS-functie gebruiken met daarin een EN-functie
Met cursor in D18 → =ALS(EN(B18<H17;C18>H18);H19;H20)
“Als,( en de leeftijd kleinder is dan 25 , en de omzet groter is dan 10000) dan krijgen ze een bonus
van 250 euro, is dit niet het geval dan krijgen ze nog een fles wijn.”
→ formule doorkopieren
10
Inhoudsopgave
1. Gegevensreeksen en celadressering ................................................................................. 3
1.1 Gegevensreeksen ................................................................................................. 3
1.2 Snel aanvullen aka flashfill ..................................................................................... 5
1.3 Relatieve adressering ............................................................................................ 6
1.4 Absolute adressering ............................................................................................. 6
1.5 Gemengde adressering ........................................................................................... 6
1. Voorbereiding Quiz................................................................................................... 7
1. Startbestand les 1 .................................................................................................... 7
2. Als en Datumfuncties ................................................................................................... 8
2.1 Eenvoudige ALS.......................................................................................................... 8
2.2 ALS – EN - OF ............................................................................................................ 9
2.3 ALS – EN ........................................................................................................... 10
2.4 Geneste ALS ............................................................................................................ 11
2.5 Rekenen met datums .................................................................................................. 12
2.6 Eenvoudige datumfuncties ........................................................................................... 13
2.7 Complexere datumfuncties ........................................................................................... 14
2.8 Gegevensvalidatie ..................................................................................................... 16
2. Voorbereiding Quiz.................................................................................................. 18
2. Test les 2 ................................................................................................................. 20
Startbestand Les 2 ......................................................................................................... 23
3. Draaitabellen en grafieken .............................................................................................. 23
3.1 Draaitabel de basis .................................................................................................... 23
3.2 Draaitabel verder aanpassen ......................................................................................... 24
3.3 Draaitabel waardeveldinstellingen .................................................................................. 24
3.4 Draaitabel tab analyseren ............................................................................................ 25
3.5 Draaitabel tab ontwerpen ............................................................................................ 26
3.6 Draaigrafiek maken .................................................................................................... 28
3. TESTVRAGEN ............................................................................................................. 30
Test les 3 .................................................................................................................... 32
Uitleg startbestand 3 ........................................................................................................ 34
Toetsinstructies: herhalingstoest les 3................................................................................... 35
4. Verticaal zoeken – Voorwaardelijke opmaak ........................................................................ 43
LinkedIn Learning: video over aangepaste getalnotaties ............................................................. 43
4.1 Verticaal zoeken – onwaar ............................................................................................ 44
4.2 Verticaal zoeken – eerste kolom ..................................................................................... 45
4.3 Herhaling – geneste ALS............................................................................................... 46
1
, 4.4 Verticaal zoeken – waar - sorteren .................................................................................. 46
4.5 Voorwaardelijke opmaak – basis ..................................................................................... 48
4.6 Voorwaardelijke opmaak – verwijzen – formules.................................................................. 48
4. Voorbereiding Quiz ..................................................................................................... 50
4. Test Les 4................................................................................................................. 51
Uitleg startbestand ........................................................................................................ 54
5. Tekstfuncties - x.als - xxx.als ................................................................................ 54
5.1 tekstfuncties: .......................................................................................................... 54
HOOFDLETTERS, kleine letters en beginletters ........................................................................ 54
Deel .......................................................................................................................... 54
Links .......................................................................................................................... 55
Rechts ........................................................................................................................ 55
Tekst samenvoegen 1 ...................................................................................................... 55
Tekst samenvoegen 2 ...................................................................................................... 55
5.2 tekstfuncties: .......................................................................................................... 56
Substitueren ................................................................................................................ 56
VIND.ALLES .................................................................................................................. 56
Lengte ........................................................................................................................ 57
5.3 tekstfuncties: .......................................................................................................... 59
Som ........................................................................................................................... 59
GEMIDDELDE.ALS ........................................................................................................... 60
AANTAL.ALS ................................................................................................................. 60
5.4 tekstfuncties: .......................................................................................................... 61
SOMMEN.ALS ................................................................................................................ 61
5.5 tekstfuncties: .......................................................................................................... 61
GEMIDDELDEN.ALS .......................................................................................................... 61
ALS.FOUT .................................................................................................................... 61
5.6 tekstfuncties: .......................................................................................................... 62
AANTALLEN.ALS............................................................................................................. 62
5.Voorbereiding Quiz ...................................................................................................... 63
5.Test Les 5 ................................................................................................................. 64
5.Startbestand les 5........................................................................................................ 66
6. Sorteren en filteren ...................................................................................................... 69
6.1 SORTEREN .............................................................................................................. 69
6.2 FILTEREN ................................................................................................................ 70
6.3 GEAVANCEERD FILTEREN .............................................................................................. 71
6. Voorbereiding quiz ...................................................................................................... 74
6. Test Les 6................................................................................................................. 75
6. Startbestand les 6 ....................................................................................................... 78
Integratieoefeningen ........................................................................................................ 78
Proefexamen ICT MC......................................................................................................... 79
2
, 1. Gegevensreeksen en celadressering
1.1 Gegevensreeksen
1.1.1 REEKS KOPIEREN
Cellen kopieren: Slepen en doortrekken naar beneden aan het vulblokje
1.1.2 REEKS DOORVOEREN
Reeks doorvoeren
→ werkdagen doorvoeren (enkel werkdagen worden doorgevoerd)
3
,→ datums doorvoeren
➔ datums van ‘werkdagen’ doorvoeren
→ maanden doorvoeren
o Zelfde principe bij uren,...
o Bvb lokaal 1, lokaal 2,...lokaal 12,......lokaal 102,...
Kwartaal 1, kwartaal 2, kwartaal 3, kwartaal 4, kwartaal 1, kwartaal 2,... (excel weet dus
dat er maar 4 kwartalen zijn)
1.1.3 GROEIREEKS
Zelfde met datums, uren, ...
4
, 1.1.4 GROEITREND – LINEAIRE TREND - SNEL AANVULLEN
Slepen met RMK
1.2 Snel aanvullen aka flashfill
Suggesties om snel aan te vullen
1.2.1 Naamlijst aanvullen
-> slepen aan vulblok , trek door
->kies snel aanvullen
1.2.2 Achternaam aanvullen
-> trek door
-> kies snel aanvullen
5
,OF OP DEZE WIJZE:
doortrekken
1.3 Relatieve adressering
Formules doortrekken aangepast aan de cellen (verschuifd mee)
1.4 Absolute adressering
Bepaalde delen in formules vastzetten zodat, ondanks de formule doorgetrokken wordt, de
verwijzing hetzelfde blijft. Hievoor gebruiken we $-teken (via F4)
VB btw berekenen.
o De btw nl 21% staat in B1
o Het bedrag excl btw staat in kolom A (A4, A5, A6, A7,A8 en A9)
We typen voor bedrag incl btw =A4*(1+$B$1)
1.5 Gemengde adressering
Een verwijzing vastzetten (maw absoluut adresseren) in combinatie met relatieve gegevens
6
, 1. Voorbereiding Quiz
➔Werkboek (workbook), een workbook bestaat uit 1 of meerdere werkbladen (worksheets)
➔ =A2-(A2*$E$1)
1. Startbestand les 1
Startbestand Doornemen + maken
Filmpje Uitleg startbestand bekijken
Startbestand oplossing doornemen
7
, 2. Als en Datumfuncties
2.1 Eenvoudige ALS
Met een ALS-functie kun je berekeningen in excel laten afhangen van een voorwaarde.
VB:
o Kolom A: het product
o Kolom C: prijs
o Kolom D: aantal stuks
o Kolom E: aantal stuks*prijs
In E6 wordt met de SOMfunctie het totaal berekend
=SOM(E2:E5)
In E7 gaan we berekenen met een ALSfunctie of er korting wordt gegeven
Er wordt korting gegeven wanneer het aankoopbedrag groter of gelijk is aan 1500 (deze
grenswaarde staat in B8) . De korting is 2% (staat in B7)
Meerdere manieren om een functie in te voeren. Nl door gewoon te typen in de formulebalk/ tab
“formules/ icoontje formule aanklikken
=ALS()
Werkwijze 1:
We gaan de verschillende functieargumenten invullen
o Logischte test: is een voorwaarde nl in dit vb “als het bedrag groter of gelijk is aan 1500”
o DUS E6>=B8
o Waarde als waar: er wordt dan korting gegeven nl 2%
o DUS E6*B7
o Waarde als onwaar: 0
Werkwijze 2:
We beginnen met typen
=ALS(E6>=B8;E6*B7;0)
8
, 2.2 ALS – EN - OF
Bij de ALS-functie kun je de berekening ook laten afhangen van meerdere voorwaarden. Je dient
hiervoor dan een extra functie te nesten nl de OF-functie of de EN-functie.
We hebben het aantal producten, het bedrag en de grenswaarden voor eventuele korting.
OF dusss als aan 1 van beide voorwaarden voldaan wordt => korting
We gaan naar de tab formules → rubriek logisch → functieagrumenten voor de OF →
1ste logische test (1ste voorwaaarde) is het aantal is groter dan de 1ste grenswaarde
2de logische test is het aankoopbedrag is groter dan de 2de grenswaarde
...
Je kunt nog meer voorwaarden instellen!
We krijgen dus de waarde WAAR, in deze test is de waarde dus groter dan 10 (1 van beide
voorwaarden is voldaan)
We kunnen bij Korting het volgende doen:
=ALS(B13;E13;E14)
Als aan B13 wordt voldaan dan wordt er korting gegeven van 0,1. Anders wordt er geen korting
gegeven.
! ➔ we kunnen ook die OF in die ALS nestelen dan heb je geen tussenstap nodig
=ALS(OF(B11>E11;B12>E12);E13;E14)
9
, De EN-functie is hetzelfde met het enige verschil dat aan beide voorwaarden moeten voldaan
worden.
Met cursos in B13 staan → tab “formules” → Logisch → EN → bij “functieargumenten”
- de 1ste logische test B11>E11
- de 2de logische test B12>E12
We krijgen als resultaat in B13 ONWAAR te zien. Aan beide voorwaarden moet voldaan worden.
! ➔ we kunnen opnieuw die EN in die ALS nestelen dan heb je geen tussenstap nodig
=ALS(EN(B11>E11;B12>E12);E13;E14)
2.3 ALS – EN
In dit vb zie je de naam van de verkopers, de leeftijd die ze hebben ennnn de omzet die ze hebben
gerealiseerd.
We gaan nu bepalen of ze een bonus van €250 krijgen of een fles wijn.
Bonus van €250 indien:
1ste voorwaarde is jonger zijn van 25
2de voorwaarde is een van omzet gerealiseerd van 10000
In het ander geval: fles wijn
We moeten dus een AlS-functie gebruiken met daarin een EN-functie
Met cursor in D18 → =ALS(EN(B18<H17;C18>H18);H19;H20)
“Als,( en de leeftijd kleinder is dan 25 , en de omzet groter is dan 10000) dan krijgen ze een bonus
van 250 euro, is dit niet het geval dan krijgen ze nog een fles wijn.”
→ formule doorkopieren
10
