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 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:

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

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.

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.

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)

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))))

Bepaalt het restgetal dat overblijft nadat getal is gedeeld door deler. Het restgetal en deler zijn beide positief of negatief.

Syntaxis

REST(getal;deler)

getal     is het getal waarvoor je het restgetal wilt bepalen.

deler     is het getal waardoor je getal wilt delen.

De REST functie is handig als je tijden moet aftrekken (om het aantal gewerkte uren te berekenen) en de begintijd  GROTER is dan de eindtijd. Je krijgt dan namelijk een negatief getal.

Stel in C2 eindtijd en in B2 begintijd. De formule is dan:
=REST(C2-B2;1)*24
Let op: Je moet de uitkomst met 24 vermenigvuldigen omdat het hier om tijden gaat.

Tussen de functie REST en de functie INTEGER bestaat de volgende relatie:

REST(n, d) = n – d * INTEGER(n / d)

Hoe werkt dat:     n – d * INTEGER(n / d)

Stel n = -0,83333 en d = 1


Berekeningsstappen

-0,83333/1 = -0,83333
INTEGER  (-0,83333) = -1
1 * -1 = -1
-0,83333 - -1 = 0,16667 (2x min wordt plus)

Dit is dus hetzelfde als:
=MOD(-0,83333;1) = 0,16667

En dat is wat de REST functie doet. Als de tijd negatief is wordt er 1 bij het eindresultaat opgeteld.

[D2]=REST(C2-B2;1)*24

[D7]=SOM(D2:D5)

Verkorte versie:

[C7]=SOMPRODUCT(REST(C2:C5-B2:B5;1)*24)

De celopmaak moet je bij de celeigenschappen op standaard of Getal zetten voor de juiste weergave. In Excel is 1 dag gelijk aan het getal 1 en het uur is 1/24 deel. Vandaar dat er met 24 vermenigvuldigd wordt

Soms staan alle venster van de VBE (Visual Basic Editor) kriskras door elkaar. Met geen mogelijkheid is er enige orde in aan te brengen. Deze truc van Erlandsen werkt perfect om dat te herstellen naar de oorspronkelijke waarden.

Hiervoor moet je wel effe in de Registry duiken en daarbij hoort een waarschuwing.

WAARSCHUWING: Indien je de Registry editor verkeerd gebruikt, kun je serieus in de problemen geraken omdat je besturingssysteem in de soep draait.

Na dit gezegd te hebben kunnen we verder.

-Sluit Excel en de VBE.
-Uitvoeren > > >  RegEdit. In Windows 8.1 met de muis helemaal naar rechtsboven zodat de Charms-Balk tevoorschijn komt en bij het vergrootglas Regedit invoeren.
-Navigeer naar HKEY_CURRENT_USER/Software/Microsoft/VBA/7.0/Common
-Verwijder de waarde Dock.
-Excel maakt de volgende keer een nieuwe waarde aan.
–Regedit sluiten
-Open Excel en de VBE, gelukkig, de venster zijn weer op orde.

Probleem:

Gegevens in [ A2 ] in het volgende formaat:
Achternaam Voornaam Nummer

Bijvoorbeeld:
Smith John 12345678

Eindresultaat moet zijn:
[ B2 ] = Voornaam
[ C2 ] = Achternaam
[ D2 ] = Nummer

Formule: [ B2 ]:

=TRIM(MID(A2; SEARCH(" "; A2) + 1; SEARCH(" "; A2; SEARCH(" "; A2) + 1) - SEARCH(" "; A2) - 1))

Formule: [ C2 ]:

=TRIM(LEFT(A2; SEARCH(" "; A2) - 1))

Formule: [ D2 ]:

=TRIM(RIGHT(A2; LEN(A2) - SEARCH(" "; A2; SEARCH(" "; A2) + 1)))