Door het toepassen van voorwaardelijke opmaak kun je snel verschillen opmerken in een bereik met waarden. Wat moet je doen?
Selecteer de gegevens waarop je voorwaardelijke opmaak wil toepassen.
Vervolgens klik je op: Voorwaardelijke opmaak | Markeerregels voor cellen | Tekst met
In het onderstaande vak kun je de naam van een land invoeren maar dat is niet handig want dat beperkt de boel tot dat land. Beter is om naar een cel te verwijzen in dit geval G2. Telkens als je nu de waarde in G2 verandert, verandert ook de kleurmarkering al naar gelang het land dat je invoert. Let op ! ! ! In het rechtervak kun je nog een mooie opmaak kiezen. Experimenteren maar.
Het probleem is dat je veel dubbele waarden in kolom A (Soort) hebt.
Met de functie VERT.ZOEKEN kun je daarom niet goed uit de voeten. Echter, je kunt kolom A en Kolom B samenvoegen in je formule d.m.v. het & teken. Met de functies INDEX en VERGELIJKEN kun je toch de juiste waarden opzoeken.
Stel dat je een kolom H hebt met prijzen van artikelen en je wilt de prijzen omschrijven (labelen) (Kolom G) op basis van de volgende voorwaarden:
Een manier is om een label (Kolom G) te kiezen dat overeenkomt met de conditie PRIJS (Kolom H). Om flexibel te zijn werken we met celverwijzingen en niet met ‘hard coded’ data
In het argument index_num evalueer je elke voorwaarde en retourneert TRUE als aan de voorwaarde is voldaan, anders FALSE. De waarde in cel E8 voldoet bijvoorbeeld aan alle 9 voorwaarden, want €3360,50 is groter dan alle andere waarden dus krijgen we dit tussenresultaat:
Stel je hebt onderstaande tabel en je wilt de naam van de Employee opzoeken die uit Switzerland komt en je wilt dat met VERT.ZOEKEN doen. Probleem, VERT.ZOEKEN neemt altijd een waarde in de meest linkse kolom om vervolgens in de kolom die meer naar rechts staat te kijken. Eigenlijk zou kolom C dus de meest linkse moeten zijn. en kolom A meer rechts moeten staan ten opzichte van kolom C. Maar we willen de indeling van de tabel niet gaan veranderen door kolommen te gaan verschuiven.
Met de functie CHOOSE kun je kolommen als het ware virtueel verschuiven, We willen kolom C naar A verschuiven en Kolom A naar C verschuiven. Kolom B kan blijven staan. Met de volgende formule kan dat:
Uitleg: – Eerst maak je in E2 een drop down met de landen die in kolom C staan. Ga Naar: Data | Data Validation | Data Validation | en kies voor Allow | List en bij Source zet je $C$2:$C$9. Klik OK.
– In E2 kun je nu een land kiezen bijvoorbeeld Zwitserland. Bij CHOOSE zie je als eerste argument staan {3\2\1} Dat is een matrix en de cijfers worden gescheiden door de backslash \ Als tweede argument zie je 3 bereiken A2:A9;B2:B9;C2:C9. Wat CHOOSE nu doet is het eerste bereik wordt de 3e kolom, het tweede bereik de 2e kolom en het derde bereik de 1-ste kolom. Dat derde bereik wordt dus de meest linkse kolom. Je snapt het al je kunt nu wél de functie VERT.ZOEKEN gebruiken. Met CHOOSE kun je als het ware kolommen verschuiven en er zijn veel meer mogelijkheden met die functie. 2 simpele voorbeelden:
Put this example in A2: SmithJohn James12346578 Lastname always come first, the number is always last, in between always one or multiple firstnames
We want to split Lastname, Firstname(s), Number. And the firstname(s) should be only Initials.
The goal is to get the final result, Number & Initials & Lastname in G2: In [G2]: 12345678 JJ Smith
When your done, you can hide the columns, B, C, D, E, F if you want.
Formula in B2:
=LOOKUP(10^9;--RIGHT(A2;{1;2;3;4;5;6;7;8;9}))
Formula in C2:
=TRIM(LEFT(A2; LEN(A2)-LEN(B2)))
Formula in D2:
=LEFT(C2; FIND(" "; C2 & " ")-1)
Formula in E2:
=TRIM(SUBSTITUTE(C2; D2; ""))
Formula in F2:
=GetInitials(E2)
Formula in G2:
=B2 & ” ” & UPPER(F2) & ” ” & D2
1. Kopieer de onderstaande code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
Function GetInitials(rng As Range) As String
Dim FullName As String
Dim i As Integer
Dim Initials As String
FullName = rng.Value
Initials = Left(FullName, 1)
For i = 2 To Len(FullName)
If Mid(FullName, i - 1, 1) = " " Then
Initials = Initials & Mid(FullName, i, 1)
End If
Next i
GetInitials = UCase(Initials)
End Function
De onderstaande zogenaamde “User Defined function” oftewel een zelfgemaakte functie, splitst de gegevens die in 1 cel staan en meerdere regels omvat.
Opzet: – Je gegevens staan in kolom A. In dit geval alleen in A1. – Je krijgt dan als resultaat zoals je het op de afbeelding ziet.
1. Kopieer de onderstaande code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
Function Split_It_Up(rngBereik As Range, RijNummer)
'Altijd herberekenen
Application.Volatile
a = InStr(1, rngBereik.Value, Chr(10), 1)
b = InStr(a + 1, rngBereik.Value, Chr(10), 1)
c = InStr(b + 1, rngBereik.Value, Chr(10), 1)
d = InStr(c + 1, rngBereik.Value, Chr(10), 1)
e = InStr(d + 1, rngBereik.Value, Chr(10), 1)
If RijNummer = 1 Then Split_It_Up = Left(rngBereik.Value, a - 1)
If RijNummer = 2 Then Split_It_Up = Mid(rngBereik.Value, a + 1, (b) - (a + 1))
If RijNummer = 3 Then Split_It_Up = Mid(rngBereik.Value, b + 1, (c) - (b + 1))
If RijNummer = 4 Then Split_It_Up = Mid(rngBereik.Value, c + 1, (d) - (c + 1))
If RijNummer = 5 Then Split_It_Up = Mid(rngBereik.Value, d + 1, (e) - (d + 1))
If RijNummer = 6 Then Split_It_Up = Right(rngBereik.Value, Len(rngBereik.Value) - e)
End Function
Zet de volgende formule in C1: =Split_It_Up($A1;KOLOM(A1)) En naar rechts slepen (en eventueel naar beneden)
Alle bestanden in een bepaalde map weergeven op en werkblad. Deze is eenvoudig
1. Kopieer de onderstaande code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel 8. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbeklik op de macro naam om te starten.
Sub Alle_Bestandsnamen_Weergeven() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim ws As Worksheet
'Verwijzing naar Scripting.FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Werkblad toevoegen
Set ws = Worksheets.Add
'Verkrijg het map object dat geassocieerd is met de directory
Set objFolder = objFSO.GetFolder("C:\windows") '<<< Geef een directory op
ws.Cells(1, 1).Value = "De bestanden gevonden in " & objFolder.Name & " zijn:"
'Doorloop de bestanden collectie
For Each objFile In objFolder.Files
ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
Next
'Opruimen!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Een ander voorbeeld om de gegevens in een cel te splitsen en de losse items in naast gelegen cellen te plaatsen. Selecteer A1 (oranje cell)
1. Kopieer de onderstaande code 2. Open een nieuwe werkmap 3. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen 4. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen 5. Druk op M om een standaard module in te voegen 6. Daar waar de cursor knippert voeg je de code in middels Ctrl + V 7. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel 8. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbeklik op de macro naam om te starten.
Sub SplitsGegevens()
'Splits data in een cel naar naast gelegen cellen
Dim SplitsData As Variant
Dim intTotaal As Integer
'Gegevens van een cel in matrix opslaan
SplitsData = Split(ActiveCell.Value, Chr(10))
'Bovengrens matrix bepalen
intTotaal = UBound(SplitsData)
'Naar naast gelegen cellen schrijven
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), _
Cells(ActiveCell.Row, ActiveCell.Column + 1 + intTotaal)).Value = SplitsData
End Sub
Selecteer ergens een cel in het bereik A1:D49 en toets Ctrl+L om een tabel te creëren met de naam Table1. Of toets Ctrl+Shift+F3, Create names for values in the top row (Top Row aanvinken)
[H2] >>> Data Validation | Data Validation | List | Source:=$F$4:$F$21 [H2] is weliswaar geen combobox maar je kunt wel waarden kiezen. Als je geen waarden ziet, moet je even naar boven scrollen.
[D2]=SUM(IF(COUNTIF(A$2:A2;A2)<>1;0;COUNTIF(A$2:A2;A2));D1)
(Doorvoeren naar beneden)
[F2]=MAX(Table1[Dummy])
[F4]=IF(ROWS(F$4:F4)>$F$2;"";INDEX(Table1[Steden];MATCH(ROWS(F$4:F4);Table1[Dummy];0)))
(Doorvoeren naar beneden)
[I2]=COUNTIF(Table1[Steden];$H$2)
Onderstaande formules zijn Matrixformules. Invoeren met Ctrl+Shift+Enter en vervolgens (Doorvoeren naar beneden)
Selecteer ergens een cel in het bereik A1:C4000 en toets Ctrl+L (of Ctrl+L) om een tabel te creëren met de naam Table1. Nu kun je gemakkelijk naar die Table1 verwijzen in de onderstaande formules.
[Z2:Z444] een lijst met plaatsnamen van Nederland. [E2] >>> Gegevensvalidatie | Gegevensvalidatie | Lijst | Bron:=$Z$2:$Z$444
[F2]=COUNTIF(Table1[Steden];E2)
[E4]=IF(ROWS(E$4:E4)<=F$2;$E$2;"") (Doorvoeren naar beneden)
Onderstaande formules zijn Matrixformules. Invoeren met Ctrl+Shift+Enter: Er verschijnen dan accolades { } om de formule. Niet zelf typen (want dan werkt de formule niet) en vervolgens (Doorvoeren naar beneden)
