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")))
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
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.
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
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 heeft een verwijzing nodig. In de VBE, Extra | Verwijzingen Microsoft VBScript Regular Expressions 5.5
Handige functie om de HTML tags uit een webpage te verwijderen.
Function stripHTML(strHTML)
'Strips the HTML tags from strHTML
Dim objRegExp, strOutput
Set objRegExp = New Regexp
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "<(.|\n)+?>"
'Replace all HTML tag matches with the empty string
strOutput = objRegExp.Replace(strHTML, "")
'Replace all < and > with < and >
strOutput = Replace(strOutput, "<", "<")
strOutput = Replace(strOutput, ">", ">")
'Return the value of strOutput
stripHTML = strOutput
Set objRegExp = Nothing
End Function
Heb je ooit geprobeerd een waarde op te zoeken die links ligt t.o.v. de zoekkolom (rechts)? Dan heb je gemerkt dat de functie V.LOOKUP niet werkt. Voor die gelegenheid heb je de functies INDEX en MATCH nodig.
De functie MATCH zoekt de waarde in C6 en geeft als resultaat de positie van die waarde (en die is 5). Die positie wordt in de functie INDEX gebruikt om de 5e waarde in kolom A op te zoeken (en dat is bloemkool . . . lekker ! ! !).
Stel, je wilt gegevens uit je MySql database lezen vanuit Excel. Dan moet je eerst een verbinding tot stand brengen en dat is moeilijk. Volg deze stappen. Surf naar: https://dev.mysql.com/downloads/connector/odbc/ Hier staat dat je een zogenaamde Connector/ODBC kunt downloaden en daar begint het al. Moet je de 32-bits of de 64-bits versie hebben. Ligt aan je Excel versie dus check dat eerst via:
File | Account | About Excel
Bij oudere Excel versie:
Al naar gelang je versie, download je de juiste driver op de website. Gebruik de uitklaplijst bij: Select Version om te kiezen voor 32-bit of 64-bit.
Vervolgens verschijnt er nog een venster. Registreren is onnodig. Scroll naar beneden en klik gewoon op: “No thanks just start my download”
Na het downloaden, installeer je de driver. Daarna voer je de volgende stappen uit:
1. Je moet de ODBC-gegevensbron eerst openen. Dat kan op twee manieren: a. Klik op de knop Start, dan Configuratiescherm | Systeem en beveiliging | Systeembeheer. Of: b. Voor de 32-bit versie > > > kies voor: Uitvoeren > > > vul in: c:\windows\sysWOW64\odbcad32.exe Voor de 64-bit versie > > > kies voor: Uitvoeren > > > vul in: c:\windows\system32\odbcad32.exe
Nu kom je in bovenstaand overzicht. Daar kunnen 2 versies staan. Wederom 32-bit of 64-bit. Dubbelklik op de versie die overeenkomt met jouw Excel versie.
2. In het volgende venster klik je op Toevoegen
Kies het juiste stuurprogramma voor de gegevensbron die je toevoegt. Wij kiezen voor:
MySQL ODBC 5.3 ANSI Driver of de Unicode Driver.
Klik vervolgens op Voltooien.
3. Je komt nu in het venster MySql Connector/ODBC Data Source Configuration. Typ in het vak Naam van gegevensbron een naam voor de gegevensbron. Je kunt ook een beschrijving typen waaraan je later kunt zien waarvoor deze gegevensbron wordt gebruikt. Bij TCP/IP server geef je de naam van je server * op evenals User en Password. Klik op de knop Test om de verbinding te controleren. Kies je database en dan OK.
* Indien je Xampp lokaal geïnstalleerd hebt is standaard de servernaam “Localhost” en de User is “root”. Password is niet nodig. Bij jou kunnen die gegevens dus iets anders zijn.
Vandaag een zogenaamde MATRIX formule die de waarde van elke n-th rij optelt. De n staat normaal voor de onbekende. In dit voorbeeld staat de n voor de 3e rij. We gaan dus de waarde van elke 3e rij optellen.
1. De functie RIJ geeft het rijnummer van een cel.
2. De functie REST geeft de rest van een deling. Bijvoorbeeld, REST(1;3) geeft 1, want 1:3 geeft een rest van 1. De 3e rij echter, geeft een ander resultaat, REST(3;3) geeft een rest van 0. Het resultaat van de functie REST geeft 0 voor telkens de 3e rij.
Opmerking: je kunt ook elke 4e rij of 5e rij optellen.
3. Verander de functie zoals in het onderstaande voorbeeld.
4. Je ziet nu dat elke 3e rij de waarde WAAR geeft en dat gegeven gebruiken we om de naast liggende waarden op te tellen. Om waarden op te tellen gebruik je de functie SOM. Maar we willen de formule in één cel zetten die vervolgens al het werk doet.
Selecteer daarom cel A10. Nu type je de formule zoals aangegeven. Je moet de formule invoeren als zogenaamde MATRIX. In plaats van op Enter te drukken voer je de formule in door middel van Ctrl + Shift + Enter.
Opmerking: De formule balk geeft aan dat het hier om een MATRIX formule gaat. Dat kun je zien aan de accolades { } . Deze accolades moet je niet zelf typen want dan werkt de formule niet.
Eigenlijk ziet de MATRIX er als volgt uit. De getallen 5, 66, 21 worden dus opgeteld, met als resultaat 92.
Met onderstaande code kun je automatisch data lezen uit je MySQL database. Voorop gesteld dat je bovenstaande berichten hebt gelezen en uitgevoerd.
Sub SelecteerDataVanMySQL()
Dim SQLStr As String
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim Table As String
Dim rs As ADODB.Recordset
Dim rngKolom As Integer
Dim rngRij As Integer
Dim myArray()
Dim K As Integer
Dim R As Integer
'Set variable
Set rs = New ADODB.Recordset
'Clear the range
Range("a5:bb60000").ClearContents
'Connection properties
Server_Name = "YOUR SERVER NAME"
Database_Name = "YOUR DATABASE NAME"
User_ID = "YOUR USER ID"
Password = "YOUR PASSWORD"
Table = "YOUR TABLE NAME"
Field = "YOUR FIELD NAME"
'Create a mysql query string
SQLStr = "SELECT * FROM " & Table & " WHERE " & Field & " = 2"
'Connect to the database
Set Cn = New ADODB.Connection
Cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & _
Server_Name & ";Database=" & Database_Name & ";Uid=" & _
User_ID & ";Pwd=" & Password & ";"
'Create a recordset
rs.Open SQLStr, Cn, adOpenStatic
'Store rs in array variable
myArray = rs.GetRows()
rngKolom = UBound(myArray, 1)
rngRij = UBound(myArray, 2)
For K = 0 To rngKolom
'Transfer recordset data to worksheet
Range("A5").Offset(0, K).Value = rs.Fields(K).Name
For R = 0 To rngRij
Range("A5").Offset(R + 1, K).Value = myArray(K, R)
Next
Next
'Close the connection
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
