N = een waarde. Bijvoorbeeld N = 5.

Je wilt de top 6 productprijzen laten zien.

Formules:

[B2] =GROOTSTE(A6:A22;B1)
[B3] =AANTAL.ALS(A6:A22;”>=”&B2)
[D6] =ALS(RIJEN(D$6:D6)>$B$3;””;RIJEN(D$6:D6))
[E6] =ALS(D6=””;””;GROOTSTE($A$6:$A$22;D6))
[F6] =ALS(D6=””;””;INDEX($B$6:$B$22;AGGREGAAT(15;6;(RIJ($A$6:$A$22)-RIJ($A$6)+1)/($A$6:$A$22=E6);AANTAL.ALS($E$6:E6;E6))))

Voorbeeldje, tijdens het importeren van de gegevens is iets fout gegaan. Nu zit je met de gebakken peren. In kolom A allemaal abracadabra. Kortom: garbage data. Oftewel, verprutste gegevens.

Je wilt alleen de cijfers eruit halen.

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. Ga terug naar het Excel werkblad en selecteer cel B2 en type =Cijfers(A2)
8. Formule doorvoeren naar beneden

Function Cijfers(ByVal strVuilnis As String, Optional Extra As Boolean) As String
  Dim lngX As Long
  For lngX = 1 To Len(strVuilnis)
    If Mid(strVuilnis, lngX, 1) Like "[!0-9]" Then Mid(strVuilnis, lngX) = " "
  Next
  strVuilnis = Application.Trim(strVuilnis)
  If Len(Replace(strVuilnis, " ", "")) < 12 Then
    If Not Extra Then Cijfers = Replace(strVuilnis, " ", "")
  Else
    If Extra Then
      Cijfers = Mid(strVuilnis, InStrRev(strVuilnis, " ") + 1)
    Else
      Cijfers = Replace(Left(strVuilnis, InStrRev(strVuilnis, " ")), " ", "")
    End If
  End If
End Function

Een andere doortrapte methode is:

Function Alleen_Cijfers(strTekst As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "\D"
    .Global = True
    Alleen_Cijfers = .Replace(strTekst, "")
End With
End Function

Selecteer cel B2 en type =Alleen_Cijfers(A2)

In bereik A2:A11 (oranje screenshot) staan wat getallen die gedeeltelijk overeenkomen met de gegevens in bereik B15:B24 (groene screenshot).
Indien er een overeenkomst gevonden wordt willen we de bijpassende naam in het bereik D15:D24 (groene screenshot) laten verschijnen in het bereik B2:B11 (oranje screenshot) .

Formule in D15:
=ALS.FOUT(INDEX($B$2:$B$11;KLEINSTE(ALS(ISGETAL(VIND.SPEC($A$2:$A$11;B15));RIJ($A$2:$A$11)-1);1));””)

Invoeren met Ctrl+Shift+Enter en naar beneden doorvoeren.

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.