M&S
Table of Contents
Week 1....................................................................................................................................1
Week 2....................................................................................................................................4
Message box -- Say hello....................................................................................................................5
Record a macro – Make me red..........................................................................................................5
2 phases of programming...................................................................................................................5
Steps to coding - Is a student passing or failing?................................................................................5
Histogram...........................................................................................................................................6
Week 3....................................................................................................................................6
Different types of data.......................................................................................................................7
Cube volume......................................................................................................................................7
Week 4 – Arrays and loops......................................................................................................7
Array maken:......................................................................................................................................7
Looping statements:...........................................................................................................................8
Week 5....................................................................................................................................8
Do until:.............................................................................................................................................8
Do while.............................................................................................................................................8
Week 6....................................................................................................................................9
Week 7 – Userform................................................................................................................10
Week 1
Index Om een waarde in een tabel te vinden.
=INDEX (range, column, row)
=INDEX (A1:C3, 3, 2)
Match Vinden waar een bepaalde waarde in de tabel staat.
=MATCH (value, range, 0)
The 0 means exact value
, Index + Match vinden hoeveel een bepaalde rij een paar columns verderop is.
=INDEX (range, MATCH (value, range, 0), column)
=INDEX(B5:D7, MATCH("Petra", A5:A7, 0), 2)
(Index range is alleen de waardes en match range is met maanden en
namen erbij)
Vlookup Om dingen in verticale richting te vinden
=VLOOKUP (opzoekwaarde, range, column, FALSE)
=VLOOKUP (G12, F5:H9, 3, FALSE)
If Als … geldt, dan pass, anders fail
=IF(Cell > < = waarde, “pass”, “fail”)
=IF(B20>=100.000, "aangenomen", "nee")
And Als … geldt EN … geldt, dan TRUE anders FALSE
=AND(Cell <>= waarde, Cell <>= waarde)
Count if Tel op als …
=COUNTIF (range, “Eis”)
=COUNTIF(K5:K12, ">18")
Sum if waardes optellen als …
=SUMIF(range, “eis”)
Average if gemiddelde uitrekenen als …
=AVERAGEIF(range, “eis”)
Trim verwijderd extra spaties
=TRIM(Cell)
=TRIM(A1)
Len Lengte van het woord
=LEN(Cell)
=LEN(A1)
Concatenate twee cellen samenvoegen
= Cell1 & “ “ & Cell2
= A1 & “ “ & B1
Left, Mid, Right Geeft woorden in 1 cel apart vanaf links rechts of
midden
=LEFT(cell, aantal letters)
=LEFT(A1, 3)
Table of Contents
Week 1....................................................................................................................................1
Week 2....................................................................................................................................4
Message box -- Say hello....................................................................................................................5
Record a macro – Make me red..........................................................................................................5
2 phases of programming...................................................................................................................5
Steps to coding - Is a student passing or failing?................................................................................5
Histogram...........................................................................................................................................6
Week 3....................................................................................................................................6
Different types of data.......................................................................................................................7
Cube volume......................................................................................................................................7
Week 4 – Arrays and loops......................................................................................................7
Array maken:......................................................................................................................................7
Looping statements:...........................................................................................................................8
Week 5....................................................................................................................................8
Do until:.............................................................................................................................................8
Do while.............................................................................................................................................8
Week 6....................................................................................................................................9
Week 7 – Userform................................................................................................................10
Week 1
Index Om een waarde in een tabel te vinden.
=INDEX (range, column, row)
=INDEX (A1:C3, 3, 2)
Match Vinden waar een bepaalde waarde in de tabel staat.
=MATCH (value, range, 0)
The 0 means exact value
, Index + Match vinden hoeveel een bepaalde rij een paar columns verderop is.
=INDEX (range, MATCH (value, range, 0), column)
=INDEX(B5:D7, MATCH("Petra", A5:A7, 0), 2)
(Index range is alleen de waardes en match range is met maanden en
namen erbij)
Vlookup Om dingen in verticale richting te vinden
=VLOOKUP (opzoekwaarde, range, column, FALSE)
=VLOOKUP (G12, F5:H9, 3, FALSE)
If Als … geldt, dan pass, anders fail
=IF(Cell > < = waarde, “pass”, “fail”)
=IF(B20>=100.000, "aangenomen", "nee")
And Als … geldt EN … geldt, dan TRUE anders FALSE
=AND(Cell <>= waarde, Cell <>= waarde)
Count if Tel op als …
=COUNTIF (range, “Eis”)
=COUNTIF(K5:K12, ">18")
Sum if waardes optellen als …
=SUMIF(range, “eis”)
Average if gemiddelde uitrekenen als …
=AVERAGEIF(range, “eis”)
Trim verwijderd extra spaties
=TRIM(Cell)
=TRIM(A1)
Len Lengte van het woord
=LEN(Cell)
=LEN(A1)
Concatenate twee cellen samenvoegen
= Cell1 & “ “ & Cell2
= A1 & “ “ & B1
Left, Mid, Right Geeft woorden in 1 cel apart vanaf links rechts of
midden
=LEFT(cell, aantal letters)
=LEFT(A1, 3)
