Op Sheet1 staan de namen van verkopers in Kolom C en in Kolom D staan bedragen. In Kolom C kan de naam van dezelfde medewerker meerdere keren voorkomen.

Op Sheet2 willen we de verkoper met het hoogste totaalbedrag weergeven en vervolgens de tweede verkoper etc. Een soort van Top 10 zeg maar. Bovendien geldt een criterium, we tellen alleen de bedragen waarvan in kolom E “Sold” staat.

Sheet1

Sheet2

De formules moet je invoeren met Ctrl+Shift+Enter (niet alleen Enter)

A2 =LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold");MIN(A1;SUM(IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);1))))

A3 =IFERROR(SUM(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23))>=A2;1));0)

A5 =IF($B5="";"";INDEX(Sheet1!$C$2:$C$23;SMALL(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold")=$B5;ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);COUNTIFS($B$5:B5;B5))))

invoeren met Ctrl+Shift+Enter (niet alleen Enter) en doorvoeren naar beneden

B5 =IF(ROWS($B$5:B5)>$A$3;"";LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23="");MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;"Sold");ROWS($B$5:B5)))

invoeren met Ctrl+Shift+Enter (niet alleen Enter) en doorvoeren naar beneden

We hebben een tabel zoals je in onderstaande afbeelding ziet. We willen dezelfde producten optellen en vermenigvuldigen. Bijvoorbeeld het aantal fusten “Rhönbräu Klosterbier” * het aantal items (liters in dit geval omdat voor fusten de eenheid liters geldt). We hebben in totaal 48 fusten “Rhönbräu Klosterbier”. In een aantal fusten zit 15 liter en in andere fusten zit 12 liter. In totaal maakt dat 648 liter “Rhönbräu Klosterbier”.

Formule in G2 is:
=SUMPRODUCT(--(A$2:A$11=F2);B$2:B$11;D$2:D$11)
Doorvoeren naar beneden.

Indien je de relatieve positie van de laatste waarde in een bereik wil bepalen gebruik je de volgende formule:

=MATCH(2;1/($A$5:$A$22<>""))
Invoeren met Ctrl+Shift+Enter (Niet alleen maar Enter)

Het maakt niet uit of er getallen of lege cellen in het bereik staan.  De formule bepaalt NIET het laatste rijnummer met een waarde

Gegevens in één cel splitsen en weergeven per cel. In Kolom A en B staan gegevens. Er staan telkens twee namen in één cel bijvoorbeeld in A2 . In B2 staan twee steden. Zie afbeelding.

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
7. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbelklik op de macro naam om te starten.

Sub Splitsen()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a) * 2, 1 To 2)
  For i = 1 To UBound(a)
    c = Split(a(i, 1), Chr(10))
    b(i * 2 - 1, 1) = c(0)
    b(i * 2, 1) = c(1)
    c = Split(a(i, 2), Chr(10))
    b(i * 2 - 1, 2) = c(0)
    b(i * 2, 2) = c(1)
  Next
  Range("D2:E2").Resize(UBound(b)).Value = b
End Sub

Met behulp van VBA code 8 tekens vanaf de linkerkant van een tekenreeks verwijderen. Tekenreeksen staan in kolom A.

Je moet wel opletten want een cijfer als 02 kapt Excel af en toont dat als 2. Hierdoor wordt de tekenreeks “AFT5985602” weergegeven als 2. Er zijn in dat geval 9 cijfers weggelaten. Daarom moet je de 2 aan het einde van de code niet veranderen

Option Explicit

Sub Verwijder_8_Tekens()
  Range("A2", Range("A" & Rows.Count).End(xlUp)).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(8, 1))
End Sub

In Kolom A namen van Employees waarbij dezelfde naam meerdere keren kan voorkomen. In Kolom B namen van Customers. Dezelfde Employee kan meerdere Customers hebben. Dat wil je weergeven vanaf cel A26. Zie afbeelding.

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel.

Function aconcat(a As Variant, Optional sep As String = "") As String
' © Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Plaats de volgende Formules:
A26=IFERROR(INDEX($A$2:$A$23;MATCH(0;INDEX(COUNTIF($A$25:A25;$A$2:$A$23)+(A$2:A$23="");0);0));"")
Invoeren met gewoon Enter en doorvoeren naar beneden.

B26 =IF(A26="";"";MID(aconcat(IF($A$2:$A$23=A26;", "&$B$2:$B$23;""));3;999))
Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden.

Anders dan de naam doet vermoeden gaan we bij het gebruik van SUMPRODUCT eerst vermenigvuldigen en dan pas optellen. Hieronder vind je een lijst met films uit 2018 en wat bijzonderheden in de diverse kolommen. We willen a.d.h.v. een aantal criteria de juiste resultaten filteren en de bruto inkomsten berekenen. Criteria zijn:
– De distributor is 20th Century Fox
– Genre is Action
– TicketsSold is meer dan 30.000.000 (30 miljoen)

In het bereik B15:B17 plaatsen we de criteria en in B19 komt de formule.

Formule:

In [B19] =SUMPRODUCT(($C$3:$C$12=$B$15)*($D$3:$D$12=$B$16)*($F$3:$F$12>$B$17)*$E$3:$E$12)

Dit resulteert in een reeks TRUE en FALSE:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

zoals je weet resulteert:
TRUE in 1
en
FALSE in 0

En dan krijg je:
{0;0;0;0;1;0;0;0;0;0}
{1;1;0;1;1;0;0;1;1;0}
{1;1;1;1;1;0;0;0;0;0}

Dan gaan we vermenigvuldigen. Je ziet dat er maar 1 combinatie is met drie enen (1*1*1) = 1 (rode gedeelte) en die correspondeert met de 5e waarde namelijk 324512774 en dat is tegelijk het eindresultaat omdat we geen verdere waarden hoeven op te tellen.

{700059566;678815482;608581744;417719760;324512774;269622130;235506359;220159104;216648740;213767512}

Verander je de Distributor in Walt Disney dan krijg je:
{1;1;0;0;0;0;0;0;1;1}
{1;1;0;1;1;0;0;1;1;0}
{1;1;1;1;1;0;0;0;0;0}

We gaan weer eerst vermenigvuldigen. Je ziet dat er nu 2 combinatie zijn met drie enen (1*1*1) = 1 (rode gedeelte) en die corresponderen met de 1e waarde en 2e waarde. Die tellen we op en krijgen als resultaat 1.378.875.048

{700059566;678815482;608581744;417719760;324512774;269622130;235506359;220159104;216648740;213767512}

Als je met Visual Basic for Applications werkt, kun je verwijzingen instellen naar andere, zogenaamde, bibliotheken. Dit zijn geen boeken maar een soort van kleine programma’s. Indien je nog niet in de Visual Basic Editor bent, druk je op Alt+F11.
Vervolgens op Tools | References. Je krijgt dan een menu waarin je talloze bibliotheken ziet zoals:

– Microsoft Scripting Runtime
– Microsoft Internet Controls
– Microsoft HTML Object Library
– Microsoft XLM, v6.0

Hoe vaak komt een woord voor in een tekst? Ik zou niet weten waarom je dat zou willen weten. Maar met Excel kun je zoiets berekenen.
In afbeelding 1 zie je in Cel A1 en A2 een paar zinnen uit de welbekende  Lorem Ipsum tekst.

AFBEELDING 1

In Afbeelding 2 zie je een gedeelte van het resultaat.

AFBEELDING 2

De VBA code die je kunt gebruiken.
LET OP, de eerste code is hoofdletter gevoelig. Dat betekent dat bijvoorbeeld het woord “nulla” anders is als het woord “Nulla”. Beide woorden komen daarom 2x voor.

Option Explicit

Sub Hoe_Vaak_Komt_Dat_Woord_Voor()
'Zet Data in meerdere cellen van Kolom A.
'Data alleen in A1 geeft foutmelding
'Data in Kolom A, resultaat komt in de Kolommen C:D.

Dim x As Long, Cnt As Long, Txt As String, Arr() As String
    Txt = " " & Join(Application.Transpose(Range([A1], Cells(Rows.Count, "A").End(xlUp)))) & " "
    
    For x = 2 To Len(Txt)
        If Mid(Txt, x, 1) = "'" And Not Mid(Txt, x - 1, 3) Like "[A-Za-z0-9]'[A-Za-z0-9]" Then
            Mid(Txt, x) = " "
        ElseIf Mid(Txt, x, 1) Like "[!A-Za-z0-9']" Then
            Mid(Txt, x) = " "
        End If
    Next
    
    Arr = Split(Application.Trim(Txt))
    
    With CreateObject("scripting.dictionary")
        
        For x = 0 To UBound(Arr)
            .Item(Arr(x)) = .Item(Arr(x)) + 1
        Next
        
        Cnt = .Count
        Range("C2").Resize(Cnt) = Application.Transpose(.Keys)
        Range("D2").Resize(Cnt) = Application.Transpose(.items)
    End With
    
    Range("C2:D" & Cnt).Sort Range("C2"), xlAscending, Range("D2"), , xlDescending, Header:=xlNo, MatchCase:=False
End Sub

LET OP, de tweede code is NIET hoofdletter gevoelig. Dat betekent dat bijvoorbeeld het woord “nulla” hetzelfde is als het woord “Nulla” en daarom 4x voor komt.

Sub Hoe_Vaak_Komt_Dat_Woord_Voor_Met_RegExp()
'Data in Kolom A, resultaat komt in de Kolommen F:G.
'*****************************************************
'Geef een verwijzing op naar:
'Microsoft Forms 2.0 Object Library
'Te bereiken via: Alt+F11 | Tools | References
'*****************************************************

Dim regEx As Object, matches As Object, x As Object, d As Object
Dim obj As New DataObject
Dim tx As String, z As String

    Range("A1", Cells(Rows.Count, "A").End(xlUp)).Copy
    obj.GetFromClipboard
    tx = obj.GetText
    Application.CutCopyMode = False
    tx = Replace(tx, "'", "___")
        
    Set regEx = CreateObject("VBScript.RegExp")
    
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "\w+"
    End With
    
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare
        
    Set matches = regEx.Execute(tx)
            
        For Each x In matches
            z = CStr(x)
            If Not d.Exists(z) Then
                d(z) = 1
            Else
                d(z) = d(z) + 1
            End If
        Next
                    
    If d.Count = 0 Then MsgBox "Nothing found": Exit Sub
    Range("D:E").ClearContents
    
    'put the result in col D:E
        With Range("F2").Resize(d.Count, 2)
            .Cells = Application.Transpose(Array(d.Keys, d.items))
            .Replace What:="___", Replacement:="'", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
        End With
    'Sort
    Range("F2:G" & d.Count).Sort Range("F2"), xlAscending, Range("G2"), , xlDescending, Header:=xlNo, MatchCase:=False
End Sub

In Kolom A staan bedrijven. Sommige bedrijven staan er meerdere keren in. We hebben dus te maken met dubbele waarden in kolom A

We willen in dit voorbeeld de HOOGSTE waarde van Hasbro, Inc.
In D2 kun je een validatielijst maken met alle namen van de bedrijven zodat je gemakkelijk een bedrijf kunt selecteren.

Om dat te doen maak je ergens op je werkblad eerst een unieke lijst met alle bedrijven, Bijvoorbeeld in F2:F14.

Data | Data validation | Data validation | Allow > List | Source > en verwijs naar F2:F14

In [D5] =MAX(INDEX((A2:A21=D2)*B2:B21;0))