In een jaaroverzicht staan in de bovenste rij de namen van de maand. Daaronder bijvoorbeeld wat verkoopcijfers. Staan er dubbele waarden in dat bereik?
Formule in N2 en naar beneden kopiëren.
Formule invoeren met Ctrl+Shift+Enter. Niet alleen met Enter. Indien je dit correct doet, plaatst Excel accolades om de formule { }
Let op ! ! ! Plaats die accolades niet zelf.
=ALS.FOUT(KLEINSTE(ALS($A$2:$L$8<>””;ALS(AANTAL.ALS
($A$2:$L$8;$A$2:$L$8)>1;$A$2:$L$8));SOM(AANTAL.ALS
($A$2:$L$8;N$1:N1))+1);””)
Aantal waarden in kolom A tellen en top N samenstellen. De waarden staan in cellen en zijn door komma gescheiden.
Keuzelijst in F2:
Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan: Lijst | Bron: 1;2;3;4;5;6;7;8;9;10
Formule in D2:
=SOMPRODUCT(ISGETAL(VIND.ALLES(“,”&$C2&”,”;”,”&Sheet2!$A$2:$A$20&”,”))+0)
Naar beneden kopiëren.
Formule in F3:
=AANTAL.ALS(D2:D20;”>=”&GROOTSTE(D2:D20;F2))
Formule in G5
=ALS(RIJEN($G$5:G5)<=F$3;GROOTSTE($D$2:$D$19;RIJEN($G$5:G5));””)
Naar beneden kopiëren.
Formule in F5:
=ALS.FOUT(INDEX($C$2:$C$19;KLEINSTE(ALS($D$2:$D$19=$G5;RIJ($D$2:$D$19)-RIJ($D$2)+1);AANTAL.ALS($G$5:G5;G5)));””)
Naar beneden kopiëren.
Formule in F5 invoeren met Ctrl+Shift+Enter. Niet alleen met Enter. Indien je dit correct doet, plaatst Excel accolades om de formule { }
Let op ! ! ! Plaats die accolades niet zelf.
Schrijf celinhoud naar bestand C:\temp\textfile.html
Stel, je hebt in cel A1 de volgende tekst staan en je wilt die tekst naar een bestand schrijven.
“Lorem ipsum dolor sit amet, consectetur adipisicing elit,[ . . . ] sunt in culpa qui officia deserunt mollit anim id est laborum.”
Function Schrijf_Naar_Bestand(strCelInhoud As String) As Boolean
Const LogFileName As String = "C:\temp\textfile.html"
Dim FileNum As Integer
'Volgende bestandsnummer
FileNum = FreeFile
'Maakt bestand aan indien niet aanwezig
Open LogFileName For Append As #FileNum
'Schrijft informatie weg aan het einde van het bestand
Print #FileNum, strCelInhoud
'Sluit het bestand
Close #FileNum
'Gelukt
Schrijf_Naar_Bestand = True
End Function1. Kopieer de bovenstaande 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. Plaats in een andere cel de volgende functie: =Schrijf_Naar_Bestand(A1)
Gegevens staan in één cel en vervolgens verdeeld over kolommen van links naar rechts. We willen de gegevens splitsen en elk item in een eigen cel plaatsen zodat het overzichtelijker wordt. Bijvoorbeeld, de gegevens staan allemaal in één rij namelijk rij 2, A2:E2
De oude situatie
De nieuwe situatie
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.
Option Explicit
Sub Tekst_Terugloop_Van_Cel_Naar_Rij()
Dim WsNieuw As Worksheet, rngBereik As Range
Dim lngRij As Long, lngKolom As Long, lngVolgende As Long
Dim lngTeller As Long, Data As Variant
'Nieuw werkblad invoegen
Set WsNieuw = Worksheets.Add
'rngBereik is waar de gegevens staan
With Worksheets("Sheet1")
Set rngBereik = .Range("A1").CurrentRegion
'Eerste rij met kolomtitels kopiëren
rngBereik.Rows(1).Copy WsNieuw.Range("A1")
lngVolgende = 2
With rngBereik
'Alle rijen doorlopen
For lngRij = 2 To .Rows.Count
lngTeller = 0
'Alle kolommen doorlopen
For lngKolom = 1 To .Columns.Count
'Gegevens in cel splitsen
Data = Split(.Cells(lngRij, lngKolom).Value, _
Chr(10))
'Gespliste gegevens wegschrijven naar rijen
WsNieuw.Cells(lngVolgende, lngKolom).Resize _
(UBound(Data) + 1).Value = Application.Transpose(Data)
lngTeller = WorksheetFunction.Max(lngTeller, _
UBound(Data) + 1)
Next lngKolom
lngVolgende = lngVolgende + lngTeller
Next lngRij
End With
End With
WsNieuw.Cells.EntireColumn.AutoFit
End SubRazend snelle manier om ontbrekende nummers te vinden in een reeks. De reeks staat in kolom A
De ontbrekende nummers verschijnen in kolom B.
Let op ! ! ! De reeks moet met 1 beginnen. Binnen 3 seconden voor een reeks met 400.000 nummers.
Voor:
Na:
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.
Option Explicit
Sub Ontbrekende_Nummers()
Dim lngX As Long, lngY As Long, lngZ As Long, varNummers As Variant
Dim varOntbreken As Variant
varNummers = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim varOntbreken(1 To varNummers(UBound(varNummers), 1), 1 To 1)
lngZ = 1
For lngX = 1 To UBound(varOntbreken)
If lngX <> varNummers(lngZ, 1) Then
lngY = lngY + 1
varOntbreken(lngY, 1) = lngX
Else
lngZ = lngZ + 1
End If
Next
Range("B1").Resize(UBound(varOntbreken)) = varOntbreken
End SubEen bereik met gegevens transponeren (= rijen en kolommen verwisselen) met gebruik van de functies INDEX, KOLOMMEN en RIJEN. Als je de functie TRANSPONEREN niet wil gebruiken is dit een goed alternatief.
De functie INDEX geeft als resultaat de waarde van een item in een tabel. De waarde van het item wordt gevonden op het snijpunt van het rijnummer en het kolomnummer.
Voorbeeld:
=INDEX(Tabel;Rijnummer;Kolomnummer)
Ofwel:
=INDEX(A1:E10;10;5) = Waarde
Stel, een tabel in het bereik B5:O10.
1 = het rijnummer en 2 is het kolomnummer.
De functie INDEX vindt in onderstaande tabel de waarde van het item op het snijpunt van de 1-ste rij en de 2e kolom. Die waarde = 48
Onder de tabel staan nog 4 voorbeelden.
De functie KOLOMMEN(Bereik) geeft het aantal kolommen in het opgegeven bereik. Het zelfde geldt voor de functie RIJEN(Bereik).
Voorbeeld:
Kolommen(C1:E4) geeft 3, namelijk kolom C, D en E.
Rijen(C1:E4) geeft 4, namelijk rij 1, 2, 3 en 4.
Deze 2 functies zijn ideaal om als een zogenaamde teller te fungeren. Ik bedoel daarmee, als je een waarde met telkens 1 wilt verhogen.
Bijvoorbeeld: 1 – 2 – 3 – 4 – 5 óf 22 – 23 – 24 – 25 – 26.
Hoe gaat dat in zijn werk? Plaats onderstaande formule in een cel Bijvoorbeeld in A10:
=KOLOMMEN($A$2:A2)
Door die formule 5x naar rechts te kopiëren, krijg je de volgende reeks:
A10=KOLOMMEN($A$2:A2), B10=KOLOMMEN($A$2:B2), C10=KOLOMMEN($A$2:C2), D10=KOLOMMEN($A$2:D2), E10=KOLOMMEN($A$2:E2)
Je ziet dat de eerste parameter van KOLOMMEN($A$2) een absolute verwijzing is (mét dollartekens). De tweede parameter verschuift telkens omdat het een relatieve verwijzing is (geen dollartekens). Namelijk A2, B2, C2, D2 en E2. Hierdoor wordt het bereik, en het aantal kolommen, steeds groter wat resulteert in de waarden
1 – 2 – 3 – 4 – 5
Je kunt met de functie INDEX dus iets opzoeken.
Je kunt echter ook je gegevens anders ordenen bijvoorbeeld van vertikaal naar horizontaal. Hiervoor combineer je de functies INDEX, KOLOMMEN en RIJEN.
Maar let op ! ! ! Het paradoxale is dat als je in dit voorbeeld de rijen telkens met 1 wil laten oplopen je de functie KOLOMMEN gebruikt en als je de kolommen met 1 wil laten oplopen je de functie RIJEN moet gebruiken. Kijk maar naar onderstaand voorbeeld.
in cel A14 plaats je de volgende formule:
=INDEX($A$1:$B$11;KOLOMMEN($A$1:A1);RIJEN($A$1:$A1))
Deze formule trek je eerst naar rechts en vervolgens 1 rij naar beneden.
Nog een voorbeeld hoe je gegevens kunt transponeren met de functie INDEX.
Bij transponeren verwissel je de rijen en kolommen.. De tabel die we transponeren staat in $A$3:$G$9.
Formule gaat in A20
=INDEX($A$3:$G$9;KOLOMMEN($A$3:A3);RIJEN($A$3:A3))
Vervolgens naar rechts slepen tot cel G20 en dan naar beneden slepen tot cel G26.
In kolom A staan de namen (aan elkaar). Er wordt gesplitst op basis van hoofdletter. Bijvoorbeeld: JopPannenkoek wordt Jop Pannenkoek.
Formule in B2:
=ZOEKEN(2^15;VIND.ALLES({“A”;”B”;”C”;”D”;”E”;”F”;”G”;”H”;”I”;”J”;”K”;”L”;
“M”;”N”;”O”;”P”;”Q”;”R”;”S”;”T”;”U”;”V”;”W”;”X”;”Y”;”Z”};RECHTS(A2;LENGTE(A2)-1)))+1
Formule in C2:
=LINKS(A2;B2-1)
Formule in D2:
=VERVANGEN(A2;1;B2-1;””)
B2, C2 én D2 selecteren en naar beneden trekken al naar gelang de hoeveelheid namen.
ODBC wordt gebruikt om gegevens uit een database naar Excel te halen. ODBC staat voor Open Database Connectivity. Kun je verder vergeten. Om te weten of het stuurprogramma (de driver) geïnstalleerd is kun je onderstaande functie gebruiken.
Public Function Get_Driver() As String
Const HKEY_LOCAL_MACHINE = &H80000002
Dim l_Registry As Object
Dim l_RegStr As Variant
Dim l_RegArr As Variant
Dim l_RegValue As Variant
Get_Driver = ""
Set l_Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
l_Registry.enumvalues HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", l_RegStr, l_RegArr
For Each l_RegValue In l_RegStr
If InStr(1, l_RegValue, "MySQL ODBC", vbTextCompare) > 0 Then
Get_Driver = l_RegValue
Exit For
End If
Next
Set l_Registry = Nothing
End FunctionAls je alle stuurprogramma’s op je computer wil tonen gebruik je onderstaande code
Public Function Alle_ODBC_Stuurprogramma_Tonen(strComputerNaam)
Const HKEY_LOCAL_MACHINE = &H80000002
'*************************************************************************
'Naam: Alle_ODBC_Stuurprogramma_Tonen
'Gemaakt: 07/08/2011
'Auteur: Dennis Hemken
'Doel: Geeft een lijst van alle geïnstalleerde stuurprogramma's
' in een nieuw excel document
'*************************************************************************
Dim objRegistry
Dim strRegPath
Dim strAODBCDriverNames
Dim strAValueTypes
Dim strODBCDriverName
Dim strValue
Dim objExcel
Dim objRange
Dim lngRow
Dim i
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
lngRow = 1
objExcel.Cells(lngRow, 1).Value = "Driver Name"
objExcel.Cells(lngRow, 2).Value = "Value"
objExcel.Cells(lngRow, 1).Font.Bold = True
objExcel.Cells(lngRow, 2).Font.Bold = True
strRegPath = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"
Set objRegistry = GetObject("winmgmts:\\" & _
strComputerNaam & "\root\default:StdRegProv")
objRegistry.EnumValues HKEY_LOCAL_MACHINE, _
strRegPath, strAODBCDriverNames, strAValueTypes
For i = 0 To UBound(strAODBCDriverNames)
lngRow = lngRow + 1
strODBCDriverName = strAODBCDriverNames(i)
objRegistry.GetStringValue HKEY_LOCAL_MACHINE, _
strRegPath, strODBCDriverName, strValue
objExcel.Cells(lngRow, 1).Value = strODBCDriverName
objExcel.Cells(lngRow, 2).Value = strValue
Next
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Columns.AutoFit
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Columns.AutoFit
End Function