Samenvatting M&S Blok 1 uitleg met voorbeelden

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)