De functie VERT.ZOEKEN wordt gebruikt om gegevens te zoeken.
VERT.ZOEKEN eist dat de te zoeken waarde zich in de meest linkse kolom bevindt. Vervolgens wordt een veld dat meer naar rechts en in dezelfde rij ligt als resultaat gegeven.
Door de functies VERT.ZOEKEN en KIEZEN te combineren kun je een formule maken die van rechts naar links zoekt. Op die manier kun je elke waarde opzoeken onafhankelijk van de kolom.
Wat gebeurt hier? KIEZEN zorgt er voor dat VERT.ZOEKEN als het ware voor de gek wordt gehouden. We kunnen elke bedrijfsnaam (in kolom A) opzoeken door Id in C4 (ANTON) te gebruiken. VERT.ZOEKEN “denkt” dat kolom C kolom A is en omgekeerd.
Functie spreekt voor zich. Kies Formules | Functie invoegen | Door gebruiker gedefinieerd Geef een getal of celverwijzing op. Klaar! Voorbeeld:
In [A1] =dhIsPrime(99991)
Function dhIsPrime(ByVal lngX As Long) As Boolean
' Find out whether a given number is Prime.
' Treats negative numbers and positive numbers
' the same.
' From "VBA Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.
' In:
' lngX:
' Number to test if Prime
' Out:
' Return Value:
' Returns TRUE if the number is Prime
Dim intI As Integer
Dim dblTemp As Double
dhIsPrime = True
lngX = Abs(lngX)
If lngX = 0 Or lngX = 1 Then
dhIsPrime = False
ElseIf lngX = 2 Then
' dhIsPrime is already set to True.
ElseIf (lngX And 1) = 0 Then
dhIsPrime = False
Else
For intI = 3 To Int(Sqr(lngX)) Step 2
dblTemp = lngX / intI
If dblTemp = lngX \ intI Then
dhIsPrime = False
Exit Function
End If
Next intI
End If
End Function
Function IsPrime(n As Double) As Boolean
Dim i As Double
If n < 2 Then
IsPrime = False
Exit Function
End If
If n = 2 Then
IsPrime = True
Exit Function
End If
If n / 2 = Int(n / 2) Then
IsPrime = False
Exit Function
End If
For i = 3 To Sqr(n) Step 2
If n / i = Int(n / i) Then
IsPrime = False
Exit Function
End If
Next i
IsPrime = True
End Function
Zoals gebruikelijk staat de N voor de grote onbekende. M.a.w. je kunt een getal kiezen.
In dit voorbeeld nemen we het getal 3.
Stel je hebt een heel lange lijst met data. Bijvoorbeeld voetbalwedstrijden. In kolom A en B staan de ploegen en in kolom C een waardering van de wedstrijd uitgedrukt in een cijfer. Dezelfde ploeg mag/kan meerdere keren voorkomen in dezelfde kolom.
Nu wil je van een ploeg de waardering van alleen de laatste 3 wedstrijden optellen ook al komt die ploeg 6 keer voor in de lijst. In onderstaand voorbeeld willen we de waardering van de laatste 3 wedstrijden van Feyenoord optellen.
Ik vond het wel een verbazingwekkende formule. Voor alternatieve toepassingen, zelf creatief denken.
Cel A23 Vul een club in.
Cel B23: Let op ! ! ! Formule is gesplitst vanwege layout problemen.
Soms krijg je een data bestand waar veel fouten in zitten. Indien dat maar 20 rijen zijn kun je dat handmatig bijwerken. Maar als het tienduizend rijen zijn wordt dat een tijdrovend karwei. Vooral als er veel dubbele waarden in voorkomen.
Opzet van het blad is simpel.
Kolom A: Originele tekst Kolom B: De te zoeken tekst Kolom C: De vervangende tekst Kolom D: Nog niks, want hier komt de verbeterde tekst
Het enige waar je op moet letten is dat je de data in kolom B en C laat voorafgaan én eindigen met een spatie. Dat is niet zo moeilijk als je eerst even 2 hulpkolommen F en G maakt met de volgende formules die je doorvoert naar beneden.
In [F2] =" " & B2 & " "
In [G2] =" " & C2 & " "
Vervolgens die 2 kolommen kopiëren naar B en C en kiezen voor “Waarden Plakken” anders krijg je daar formules te staan en dat moet niet. Tenslotte onderstaande code in een module gooien en gaan met die banaan. Het enige tijdrovende is wellicht het opstellen van de 2 lijsten met de te zoeken en de te vervangen waarden. Dat weegt echter niet op tegen de tijdwinst die je behaalt als je alles handmatig zou moeten gaan corrigeren.
Sub Zoek_En_Vervang()
Dim arrZoek As Variant
Dim arrVervang As Variant
Dim arrOrigineel As Variant
Dim i, u As Long
'Originele lijst met artikelen
arrOrigineel = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
'De te zoeken waarden
arrZoek = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value
'De vervangende waarden
arrVervang = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).Value
'De zoek en vervang actie
For i = LBound(arrOrigineel, 1) To UBound(arrOrigineel, 1)
For u = LBound(arrZoek, 1) To UBound(arrZoek, 1)
arrOrigineel(i, 1) = Trim(Replace _
(" " & arrOrigineel(i, 1) & " ", _
arrZoek(u, 1), arrVervang(u, 1), , , vbTextCompare))
Next
Next
'Resultaten in kolom D plaatsen
Range("D2").Resize(UBound(arrOrigineel, 1)).Value = arrOrigineel
End Sub
Voorbeeld met daar onder 3 formules die mogelijk zijn om een postcode te valideren. De drie rode postcodes hebben een verboden lettercombinatie. In de Nederlandse postcodes zijn de lettercombinaties SA, SD en SS verboden, omdat deze historisch gezien verband houden met de Tweede Wereldoorlog
In [B1] =AND(LEN(A1)=7;ISNUMBER(VALUE(LEFT(A1;4)));MID(A1;5;1)=" ";CODE(UPPER(MID(A1;6;1)))>=65;CODE(UPPER(MID(A1;6;1)))<=90;CODE(UPPER(MID(A1;7;1)))>=65;CODE(UPPER(MID(A1;7;1)))<=90;NOT(OR(RIGHT(A1;2)="SS";RIGHT(A1;2)="SA";RIGHT(A1;2)="SD")))
In [C1] =AND(LEN(A1)=7;ISNUMBER(VALUE(LEFT(A1;4)));MID(A1;5;1)=" ";UPPER(RIGHT(A1;2))<>"SS";UPPER(RIGHT(A1;2))<>"SA";UPPER(RIGHT(A1;2))<>"SD";SUMPRODUCT(--(ABS(CODE(MID(UPPER(RIGHT(A1;2));{1;2};1))-77,5)<13))=2)
In [D1] =AND(LEN(A1)=7;ISNUMBER(VALUE(LEFT(A1;4)));MID(A1;5;1)=" ";AND(CODE(UPPER(MID(A1;6;1)))>=65; CODE(UPPER(MID(A1;6;1)))<=90);AND(CODE(UPPER(MID(A1;7;1)))>=65; CODE(UPPER(MID(A1;7;1)))<=90);NOT(OR(UPPER(RIGHT(A1;2))="SS";UPPER(RIGHT(A1;2))="SA";UPPER(RIGHT(A1;2))="SD")))
Onderstaande code bevat 2 code blokken om te controleren of een URL een verwijzing is naar een afbeelding: “IsImageURL” om de status van de URL te verkrijgen c.q. of het om een afbeelding gaat en “CheckURLs” om elke URL in bereik $A$1:$A$15 te doorlopen. Te beginnen bij cel “$A$1”. Nieuwe module in je werkboek opnemen middels Alt+F11 en kiezen voor Invoegen | Module en dan de code plakken.
Code “CheckURLs” uitvoeren. Indien het om een ongeldige verwijzing gaat (dus geen afbeelding), komt er een foutbeschrijving in kolom “B” te staan.
Deze methode is gebaseerd op bestandsextensies en dat kan een beperking zijn. De “IsImageURL” functie kijkt of het einde van de URL eindigt op een gebruikelijke afbeelding extensie zoals (.jpg, .jpeg, .png, .gif, .bmp, .webp).
Function IsImageURL(URL As String) As Boolean
' List of common image file extensions
Dim imageExtensions As Variant
Dim extension As String
Dim i As Integer
' Define common image extensions
imageExtensions = Array(".jpg", ".jpeg", ".png", ".gif", ".bmp", ".webp", ".svg")
' Get the file extension from the URL
extension = LCase(Right(URL, 5)) ' Check last 5 characters to cover .jpeg, .webp, etc.
' Check if the URL ends with an image extension
For i = LBound(imageExtensions) To UBound(imageExtensions)
If InStr(extension, imageExtensions(i)) > 0 Then
IsImageURL = True
Exit Function
End If
Next i
IsImageURL = False
End Function
Sub CheckURLs()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
' Set reference to Sheet1
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A1:A15")
' Loop through each cell in the range
For Each cell In rng
If Not IsEmpty(cell) Then
' Write TRUE/FALSE in the adjacent column (e.g., column B)
cell.Offset(0, 1).Value = IsImageURL(cell.Value)
End If
Next cell
End Sub
Onlangs kreeg ik een werkblad toegestuurd met een vreemd datumformaat: Okt 14th 2002 03:50 PM Het werd door Excel als tekst gezien. Om het te converteren naar een geldig datumformaat gebruikte ik deze formule. De =ALS constructie is nodig om te bepalen of de dag uit 1 of 2 cijfers bestaat. Bijvoorbeeld 4th of 14th. Let op ! ! ! Zet de celeigenschap op General.
Let op ! ! ! Dit is een Matrixformule en MOET ingevoerd worden met Ctrl+Shift+Enter dus NIET met ENTER.
Vervolgens formule naar beneden kopiëren bijvoorbeeld van F3 tot F10.
Om het gemakkelijk te maken kun je in kolom H een lijst opnemen met alle landennamen die voorkomen. Die lijst is met behulp van gegevensvalidatie gekoppeld aan cel F1. Onderstaand venster kun je bereiken d.m.v.
Gegevens | Gegevensvalidatie | Instellingen
Bij “Toestaan” kies je voor “Lijst” en bij “Bron” geef je het bereik op dus: $H$2:$H$22 Klik OK.
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 de kleurmarkering naar 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.
