Gegevens kun je het beste filteren met de ingebouwde filter- en sorteerfuncties van Excel. Te bereiken via:
Data | Sort & Filter

Als je niet zoveel gegevens hebt kun je middels een paar formules filteren. Dat biedt soms extra mogelijkheden.
Let op: Als je heel veel rijen met gegevens hebt, bijvoorbeeld 10000, kun je beter gebruik maken van de ingebouwde filter- en sorteerfuncties van Excel. Dat is veel sneller.

In dit voorbeeld gebruik ik data met 831 rijen. Bestand kun je hier downloaden

Om te beginnen selecteer je de hele tabel van B1:F831 en kies in de menubalk voor Format as Table.

Hiermee krijgen alle kolommen de naam die bovenaan staat. Die namen kunnen we gebruiken in de formule. In het bereik H1:H10 zet je de volgende namen.

In J1 en K1 zet je de volgende gegevens. Selecteer medewerker en Medewerker_Aantal. Maar Let op:
Selecteer cel J2 en kies voor Gegevens | Gegevens validatie en vul in bij Toestaan: Lijst en bij Bron =$H$2:$H$10
In K2 zet je de formule: =COUNTIF(Medewerker;J2)
Je ziet in de formule de naam Medewerker staan. Die naam hebben we eerder gemaakt.
J2 is nu een uitklaplijst en je kunt medewerkers kiezen.

Zet nu in het bereik J4:N4 de juiste namen. Nu komt de belangrijkste formule. Selecteer cel J5 en voer de volgende formule in:
=IF(ROWS(J$5:J5)<=$K$2;INDEX(INDIRECT(J$4);SMALL(IF(Medewerker=$J$2;ROW(Medewerker)-ROW($C$2)+1);ROWS(J$5:J5)));””)

Invoeren met de toetscombinatie Ctrl+Shift+Enter NIET alleen Enter. Vervolgens formule naar rechts kopieren tot N en dan naar beneden (zo’n 250 rijen, dat volstaat voor dit voorbeeld).
Telkens als je nu een andere naam kiest in J2 krijg je andere gegevens te zien.

Met Excel kun je een tekstbestand lezen en het resultaat in het werkblad zetten. Dit doe je door VBA te gebruiken. Visual Basic for Applications (VBA) is een eenvoudige programmeertaal. Je hoeft geen verstand te hebben van die programmeertaal. Gewoon onderstaand verhaal opvolgen. Het zijn 3 uiterst simpele voorbeelden.

Code als volgt invoegen:

1. Kopieer de 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. Plaats nu de cursor in een van de subs en druk op F5.
8. Om het simpel te houden wordt het resultaat afgedrukt in het Venster Direct (Dus niet in het werkblad.)
9. Let op: daar waar staat “c:\temp\JOUWBESTAND.txt” moet je de naam van jouw bestand invullen. De extensie is .txt

Sub Test_1()
Dim FileNum As Integer
Dim DataLine As String

FileNum = FreeFile()
Open "c:\temp\JOUWBESTAND.txt" For Input As #FileNum

While Not EOF(FileNum)
    Line Input #FileNum, DataLine ' read in data 1 line at a time
    Debug.Print DataLine
    ' depending on what processing you need to do for each case
Wend
Close #FileNum
End Sub

Sub Test_2()
Dim hf As Integer: hf = FreeFile
Dim lines() As String, i As Long

Open "c:\temp\JOUWBESTAND.txt" For Input As #hf
    lines = Split(Input$(LOF(hf), #hf), vbNewLine)
Close #hf

For i = 0 To UBound(lines)
    Debug.Print "Line"; i; "="; lines(i)
Next
End Sub

Let op: Het laatste voorbeeld Test_3 vereist een verwijzing naar Microsoft Scripting Runtime. Dat doe je als volgt:
Druk op Alt+F11 als je nog niet in de Visual Basic Editor zit. Klik vervolgens op:
Extra | Verwijzingen
Zoek in de lange lijst naar Microsoft Scripting Runtime en vink aan.

Sub Test_3()
'Let op: Vereist een verwijzing naar Microsoft Scripting Runtime.
'Extra | Verwijzingen -> Microsoft Scripting Runtime zoeken en aanvinken
Dim fso As FileSystemObject: Set fso = New FileSystemObject

Set txtStream = fso.OpenTextFile("c:\temp\JOUWBESTAND.txt", ForReading, False)

Do While Not txtStream.AtEndOfStream
    strNextLine = txtStream.ReadLine
    Debug.Print strNextLine
Loop
txtStream.Close
End Sub

In sommige formules kun je een matrix gebruiken. De onderdelen in die matrix moeten van elkaar gescheiden worden.
Normaliter gelden in Nederland de backslash \ en de puntkomma ; als scheidingsteken.

{1;2} stelt een matrix voor met 1 kolom en 2 rijen (1 en 2 onderelkaar)

{1\2} stelt een matrix voor met 1 rij en 2 kolommen ( 1 en 2 naastelkaar)

Kortom de \ is de kolom separator en de ; is de rij separator.

Wil je kijken wat er op jouw computer is ingesteld draai dan onderstaande macro.

Option Explicit
Sub ArraySeparators()
  Dim strsep As String
  'Set Application.International(xlColumnSeparator) = "\"
    strsep = "Alternate Array Separator =" & _
    Application.International(xlAlternateArraySeparator) & vbCrLf
    strsep = strsep & "Column Separator =" & _
    Application.International(xlColumnSeparator) & vbCrLf
    strsep = strsep & "Decimal Separator =" & _
    Application.International(xlDecimalSeparator) & vbCrLf
    strsep = strsep & "List Separator =" & _
    Application.International(xlListSeparator) & vbCrLf
    strsep = strsep & "Row Separator =" & _
    Application.International(xlRowSeparator) & vbCrLf
    strsep = strsep & "Thousands Separator =" & _
    Application.International(xlThousandsSeparator) & vbCrLf
  MsgBox (strsep)
End Sub

Resultaat:

Je kunt tabellen filteren om zodoende bepaalde records te tonen of om totalen te berekenen. Hieronder wat voorbeelden om dat met formules te doen.

We willen telkens het totaal van de kolom Verkoop berekenen.

VOORBEELD 1

Records:
Verhoeven+Hema, Verhoeven+Aldi, Bakema+Hema, Bakema+Aldi

Formule:
=SUMPRODUCT(((A2:A17=”Verhoeven”)+(A2:A17=”Bakema”));((B2:B17=”Hema”)+(B2:B17=”Aldi”));D2:D17)

Resultaat:
€ 440,-

VOORBEELD 2

Records:
Verhoeven+Aldi, Bakema+Aldi

Formule:
=SUMPRODUCT((A2:A17=”Verhoeven”)*(B2:B17=”Aldi”)*(D2:D17))+SUMPRODUCT((A2:A17=”Bakema”)*(B2:B17=”Aldi”)*(D2:D17))

Resultaat:
€ 160,-

VOORBEELD 3

Records: Verhoeven+Hema, Bakema+Aldi

Formule:
=SUM(SUMIFS(D2:D17;A2:A17;{“Verhoeven”\“Bakema”};B2:B17;{“Hema”\“Aldi”}))

Resultaat:
€ 240,-

Let op: In de formule worden twee matrices (Array) gebruikt namelijk: {“Verhoeven”\“Bakema”} én {“Hema”\“Aldi”}. Een matrix in een formule wordt ingesloten door accolades { } en de onderdelen worden gescheiden door een backslash.

VOORBEELD 4

Records:
Verhoeven+Hema+Limburg, Bakema+Aldi+Limburg

Formule:
=SUM(SUMIFS(D2:D17;A2:A17;{“Verhoeven”\”Bakema”};B2:B17;{“Hema”\”Aldi”};C2:C17;”Limburg”))
Resultaat:
€ 90,-

VOORBEELD 5

Records: Verhoeven+Hema+Limburg, Verhoeven+Aldi+Limburg, Bakema+Hema+Limburg, Bakema+Aldi+Limburg

Formule:
=SUMPRODUCT(((A2:A17=”Verhoeven”)+(A2:A17=”Bakema”));((B2:B17=”Hema”)+(B2:B17=”Aldi”));–(C2:C17=”Limburg”);D2:D17)

Resultaat: € 240,-

VOORBEELD 6

Records:
Verhoeven+Hema+Limburg, Verhoeven+Aldi+Limburg, Verhoeven+Hema+Utrecht, Verhoeven+Aldi+Utrecht
Bakema+Hema+Limburg, Bakema+Aldi+Limburg, Bakema+Hema+Utrecht, Bakema+Aldi+Utrecht
Polman+Hema+Limburg, Polman+Aldi+Limburg, Polman+Hema+Utrecht, Polman+Aldi+Utrecht

Formule:
=SUMPRODUCT(D2:D17;–ISNUMBER(MATCH(A2:A17;{“Verhoeven”\”Bakema”\”Polman”};0));–ISNUMBER(MATCH(B2:B17;{“Hema”\”Aldi”};0));–ISNUMBER(MATCH(C2:C17;{“Limburg”\”Utrecht”};0)))

Resultaat:
€ 900,-

VOORBEELD 7

Records:
Verhoeven+Hema+Limburg, Verhoeven+Aldi+Limburg, Verhoeven+Hema+Utrecht, Verhoeven+Aldi+Utrecht
Bakema+Hema+Limburg, Bakema+Aldi+Limburg, Bakema+Hema+Utrecht, Bakema+Aldi+Utrecht
Polman+Hema+Limburg, Polman+Aldi+Limburg, Polman+Hema+Utrecht, Polman+Aldi+Utrecht

Formule:
=SOMPRODUCT(D2:D17;–ISGETAL(VERGELIJKEN(A2:A17;$Q$109:$Q$111;0));–ISGETAL(VERGELIJKEN(B2:B17;{“Hema”\”Aldi”};0));–ISGETAL(VERGELIJKEN(C2:C17;{“Limburg”\”Utrecht”};0)))

Resultaat
€ 900,-

Let op: In de formule wordt nu een verwijzing gebruikt. Zie het rode gedeelte. In dat gedeelte kun je namen invullen waardoor de formule flexibeler wordt.

In kolom A staan dubbele waarden en in kolom B niet. Bijvoorbeeld in kolom A staan de bedrijfsnamen en in kolom B de adressen. Een bedrijf kan meerdere adressen hebben omdat ze ook nog in een ander land zitten. De bedrijfsnaam kan dus 2 of meerdere keren voorkomen maar het adres niet.

Als de bedrijfsnaam meerdere keren voorkomt in kolom A willen we de hele rij markeren. Zoiets:

Option Explicit
Sub Dubbele_Waarden_In_Kolom_A()
'Start rijnummer van je gegevens.
Const lngStartRij As Long = 2

Dim lngLaatsteRij As Long
Dim rngCell As Range
Dim strGegevens As String

    lngLaatsteRij = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    strGegevens = "A" & lngStartRij & ":A" & lngLaatsteRij
    
    For Each rngCell In Range(strGegevens)
        If Evaluate("COUNTIF(" & strGegevens & ",A" & rngCell.Row & ")") > 1 Then
            'Dubbele waarden in kolom A rood markeren.
            Range("A" & rngCell.Row & ":B" & rngCell.Row).Interior.Color = RGB(191, 255, 128)
        End If
    Next rngCell
End Sub

Bovenstaande code als volgt invoegen:

1. Kopieer de 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. Zorg dat je een lijst met gegevens in je werkblad hebt zoals in de screenshot hierboven.
9. Macro uitvoeren via Beeld | Macro’s

Option Explicit

'I presume you've got a lot of file names. 10, 100 or 1000?
'- Start up command prompt (cmd.exe) and run as administrator.
'- Go to directory where your files are. Let's presume C:\temp
'- Type dir /b
'- Look for the little black icon at the top left
'- Choose Edit | Mark. You can now select your files.
'- Choose Edit | Copy
'- Go to Excel and start up a new workbook and select A1
'- Hit Paste

'Now suppose your file name is something like: Fortitude.S01E01.WEB-DL.XviD-FUM.jpg
'and you want to replace the part "WEB-DL.XviD-FUM" with "IMG_" (without quotes.)
'- Go to B1 and enter formula =SUBSTITUTE(A1;"WEB-DL.XviD-FUM.jpg";"IMG_.jpg") Attention, I use semi colon and not comma because I have Dutch version.
'- Copy down
'- Now you have your correct file names in B1
'- Copy and Paste this code in a new module -> Alt+F11 | Insert | Module
'- Run the code with View | Macros |View macros | RenameFiles | Run
'- it will pause and you have to point to the directory where your files are.

Sub RenameFiles()
Dim xDir As String
Dim xFile As String
Dim xRow As Long
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            xDir = .SelectedItems(1)
            xFile = Dir(xDir & Application.PathSeparator & "*")
            Do Until xFile = ""
                xRow = 0
                On Error Resume Next
                xRow = Application.Match(xFile, Range("A:A"), 0)
                If xRow > 0 Then Name xDir & Application.PathSeparator & xFile As _
                    xDir & Application.PathSeparator & Cells(xRow, "B").Value
                xFile = Dir
            Loop
        End If
    End With
End Sub

We geven een zoekterm op en willen de kolomletter vinden waarin deze zoekterm zich bevindt. Laten we zoeken naar:

San Cristóbal

Vul die naam in onderstaande code in.

Sub Find_Column_Letter()
    Set cell = Cells.Find("San Cristóbal", , xlValues, xlPart, , , False)
    If Not cell Is Nothing Then
      ColLetter = Split(cell.Address, "$")(1)
      MsgBox ColLetter
    Else
      MsgBox "I cannot find that text on this sheet"
    End If
End Sub

De uitkomst is kolom E

Je kunt natuurlijk ook gewoon zoeken met toetscombinatie Ctrl+F en in het zoekvak de naam invullen.

Je wilt de laatste n boekingen zien. In dit geval n=10.

De tabel staat in A1:E831 en de kolomkoppen zijn Date, Employee, Customer, Country, Price. Je hebt formules nodig om de meest recente 10 bestellingen weer te geven. Toon ze in het bereik G2:K11.

Gebruik in een hulpcel (bijv. M2) deze formule om de meest recente datum te vinden:
=MAX(A2:A831)
Dit bevestigt alleen de meest recente datum in je lijst ter referentie (heeft verder geen nut).

We willen de 10 laatste bestellingen vinden. Voer deze formule in cel G2 in als matrixformule (bevestig met Ctrl+Shift+Enter, niet alleen met Enter). Er verschijnen automatisch accolades {}.

=IFERROR(INDEX(A:A; AGGREGATE(15; 6; ROW($A$2:$A$831)/($A$2:$A$831=LARGE($A$2:$A$831; ROWS(G$2:G2))); 1)); "")

Kopieer naar rechts en dan naar beneden.

Vind de hoogste waarde in elke rij en markeer die waarde. Ook als er 2 waarden even hoog zijn. Werkt ook als er een lege rij is.

Sub MarkeerHoogsteWaardeInRij()
  Dim rngRij As Range, Max As Double
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.ColorIndex = 7
  For Each rngRij In ActiveSheet.UsedRange.Rows
    rngRij.Replace Application.Max(rngRij.Value), "", SearchFormat:=False, ReplaceFormat:=True
  Next
  Application.ReplaceFormat.Clear
End Sub

Verticaal zoeken met behulp van een zogenaamde “wildcard”. We weten niet precies hoe de renner heet maar soms wel de voornaam of een gedeelte van de naam. Uiteindelijk willen we de RiderId van die renner kunnen opzoeken.

– Tabel is A1:C22.
– In kolom A staan de namen.
– In E2 vullen we een deel van de voornaam in, bijvoorbeeld “Rem” (zonder aanhalingstekens)
– In F2 komt de formule:

=VLOOKUP("*"&E2&"*";$A$1:$C$22;3;FALSE)

– In G2 komt de formule

=VLOOKUP("*"&E2&"*";$A$1:$C$22;1;FALSE)

De formules vinden Remco Evenepoel met RiderId 21 en dat is correct.

Maar stel nou dat je wel de voornaam van Almeida weet maar bent vergeten welk teken boven de a staat. Was dat nou een ` of een ‘ of een ~. Of was die derde letter wel een a? Dan kun je in E2 invullen “Jo?o” (zonder aanhalingstekens). Dus een vraagteken op die plaats. En ja hoor de goede data wordt gevonden en staat in F2 en G2. Een vraagteken staat voor elk willekeurig karakter.

Een alternatieve manier is:

In F2:

=LOOKUP(9,999999999999999E+307;SEARCH("*"&E2&"*";$A$2:$A$22);$C$2:$C$22)

In G2:

=LOOKUP(9,99999999999999E+307;SEARCH("*"&E2&"*";$A$2:$A$22);$A$2:$A$22)