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

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

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

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}

Laatste cel in gebruik.
Als de kolom (of rij) waarvan je de laatste cel wilt bepalen en bestaat uit numerieke gegevens (al dan niet afgewisseld met lege cellen), kunt je de functie MATCH gebruiken:

=MATCH(1,0E+30;A:A) [ voor kolommen.]

=MATCH(1,0E+30;1:1) [ voor rijen; 1:1 betekent rij 1 ]

Geeft je de locatie van de laatste cel die in gebruik is (die een numerieke waarde bevat).

Opmerking. Deze formules gaan ervan uit dat er geen numerieke waarde zo groot als 1,0E+30 voorkomt onder de numerieke waarden van de kolom/rij die je test. MATCH vindt geen benaderende overeenkomst en retourneert de locatie (niet de ref) van de laatste cel met een numerieke waarde, wat precies is wat je zoekt.

Als je geïnteresseerd bent in het adres (ref) van de cel, breid dan de formule als volgt uit:

=ADDRESS(MATCH(1,0E+30;A:A);COLUMN(A:A)) [ for columns ]

=ADDRESS(ROW(1:1);MATCH(1,0E+30;1:1)) [ for rows ]

Als je geïnteresseerd bent in de waarde van de laatste cel die in gebruik is, omring dan de formules die de functie ADDRESS bevatten met INDIRECT:

=INDIRECT(ADDRESS(ROW(A1);COLUMN(A1)))
zal altijd naar cel A1 verwijzen, ongeacht waar je de formule kopieert.

Wat als het bereik waarin u geïnteresseerd bent uitsluitend uit tekstwaarden bestaat (afgewisseld met lege cellen)? Het bovenstaande schema zal dan niet werken. Hoewel er andere mogelijkheden zijn, zal ik gewoon de volgende (probleemloze) UDF’s posten:

Function LASTINCOLUMN(rng As Range)
' From J. Walkenbach, modified to return the row number [ Aladin Akyürek, Aug 8, 01 ]
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rng.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, _
WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Row
Exit Function
End If
Next i
End Function

Function LASTINROW(rng As Range) As Variant
' From J. Walkenbach, modified to return the row number [ Aladin Akyürek, Aug 8, 01 ]
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rng.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, _
WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Column
Exit Function
End If
Next i
End Function

Plaats de code in een “module”.

Gebruik in werkblad:

=LASTINCOLUMN(A:A)

=LASTINROW(1:1)

geeft je de locatie van de laatst gebruikte cel (ongeacht of deze een tekstwaarde of een numerieke waarde bevat).

COUNT (of COUNTA) wordt gebruikt om te tellen, SUM om op te tellen als er geen voorwaarde van toepassing is.

COUNTIF wordt gebruikt om te tellen, SUMIF om te sommeren als er sprake is van één enkele voorwaarde.

SUMPRODUCT of een matrixformule wordt gebruikt voor zowel tellen als optellen wanneer er twee of meer voorwaarden van toepassing zijn. Ik noem deze respectievelijk multivoorwaardelijke telling en multivoorwaardelijke som.

De syntaxis van COUNTIF:

COUNTIF(Bereik;Voorwaarde)

waarbij Voorwaarde wordt opgebouwd met vergelijkingsoperatoren en een constante (in een cel) of een berekening.

Voorbeelden:

=COUNTIF(A1:A10;”>”&B1)

telt de waarden in A1:A10 groter dan de waarde in B1;

=COUNTIF(A1:A10;”>” & AVERAGE(A1:A10))

telt de invoeren in A1:A10 groter dan het gemiddelde van A1:A10 (een berekening).

De syntaxis van SUMIF:

SUMIF(Bereik1;Voorwaarde;Bereik2)

Telt de cellen in Bereik2 op die gekoppeld zijn aan (in dezelfde rij als) de cellen van Bereik1 waarvoor de voorwaarde geldt. Merk op dat Bereik2 hetzelfde kan zijn als Bereik1.

Voorbeelden:

=SUMIF(A1:A10;D1;B1:B10)

telt elke invoer in B1:B10 op als de bijbehorende invoer in A1:A10 gelijk is aan de waarde in D1;

=SUMIF(E1:E10;”>” & AVERAGE(E1:E10);E1:E10)

die kan worden ingekort tot

=SUMIF(E1:E10;”>” & AVERAGE(E1:E10))

Tel alle getallen in E1:E10 op en bepaal of deze groter zijn dan het gemiddelde van E1:E10.

Wat doet SUMPRODUCT?

• Vermenigvuldigt als eerste de corresponderende items in de matrix (Array) en telt de uitkomsten vervolgens op.

Syntax:
SUMPRODUCT(array1;array2;array3; …)

• Array1, array2, array3, … zijn 2 to 30 arrays (matrices) waarvan de componenten vermenigvuldigt worden en dan worden opgeteld.
• De array (Matrix) argumenten moeten dezelfde dimensies/grootte/omvang/afmeting hebben. Wordt aan die voorwaarde niet voldaan geeft SUMPRODUCT de #VALUE! foutmelding.
• SUMPRODUCT behandelt array items die NIET numeriek zijn als waren ze 0 (nul).

Example

=SUMPRODUCT(A1:B3;D1:E3)

De formule lijkt aan te geven dat we eerst optellen, maar nee, we gaan eerst vermenigvuldigen en de resultaten daarna optellen:
32 + 47 + 86 + 67 + 15 + 93.

Als je bovenstaande formule hebt ingevoerd kun je A1:B3 selecteren en dan op F9 drukken. Vervolgens D1:E3 selecteren en weer op F9 drukken.
=SUMPRODUCT({3\4;8\6;1\9};{2\7;6\7;5\3}) is gelijk aan 156

Opmerking
Het voorgaande voorbeeld geeft hetzelfde resultaat als de formule SUM(A1:B3*D1:E3) die als Array (matrix) is ingevoerd d.m.v. Ctrl+Shift+Enter. Het gebruik van matrices biedt een algemenere oplossing voor bewerkingen die vergelijkbaar zijn met SUMPRODUCT. U kunt bijvoorbeeld de som van de kwadraten van de elementen in A1:B3 berekenen met de formule SUM(A1:B3^2) die als matrix is ingevoerd.

Een ander voorbeeld.

Stel dat A1:E24 de voorbeeldgegevens van de maandelijkse verkoop (voorbeeld afkomstig van Walkenbach) bevat in een werkblad met de naam Data:

={“Month”\”Sales Rep”\”Region”\”Contacts”\ “Sales”;”Jan”\”Bob”\”North”\58\283800; “Jan”\”Frank”\”North”\35\507200; “Jan”\”Paul”\”South”\25\107600; “Jan”\”Randy”\”South”\47\391600; “Jan”\”Mary”\”South”\39\226700; “Feb”\”Bob”\”North”\44\558400; “Feb”\”Jill”\”North”\46\350400; “Feb”\”Frank”\”North”\74\411800; “Feb”\”Paul”\”South”\29\154200; “Feb”\”Randy”\”South”\45\258000; “Feb”\”Mary”\”South”\52\233800; “Mar”\”Bob”\”North”\30\353100; “Mar”\”Jill”\”North”\44\532100; “Mar”\”Frank”\”North”\57\258400; “Mar”\”Paul”\”South”\13\286000; “Mar”\”Randy”\”South”\14\162200; “Mar”\”Mary”\”South”\36\134300; “Apr”\”Bob”\”North”\54\595500; “Apr”\”Jill”\”North”\44\480100; “Apr”\”Frank”\”North”\79\555500; “Apr”\”Paul”\”South”\36\328200; “Apr”\”Randy”\”South”\31\154200; “Apr”\”Mary”\”South”\22\200600}

Opmerking 1. Kopieer dit inclusief het =-teken. Activeer A1 in een werkblad dat u Data noemt, plak het in de formulebalk en druk op Enter. Activeer A1, selecteer het bereik A1:E24, ga naar de formulebalk en voer in met CtrlL+Shift+Enter. Kopieer het bereik A1:E24, activeer vervolgens alleen A1 en voer Bewerken|Plakken speciaal -> Waarden uit.

Merk op dat in de maand januari de verkoper Bob 58 klanten (contacten) had in de regio Noord, waarmee hij een omzet van $ 283.800 behaalde. Ook zijn er elke maand verschillende verkopers actief in een regio.

Opmerking 2. Geef de volgende namen aan de respectieve bereiken via het naamvak of via Invoegen|Naam|Definieer:

MONTHS for A2:A24
REPS for B2:B24
REGIONS for C2:C24
CONTACTS for D2:D24
SALES for E2:E24

Stel dat we een overzicht willen van contacten en verkopen per verkoper. SUMIF is hiervoor uitermate geschikt. De voorwaarde voor het berekenen van het totaal (som) is de naam van de verkoper. Om dit overzicht te verkrijgen, maak je in een werkblad (dat u SUMIF kunt noemen) het volgende in B4:B12:

={“Sales Rep”;”Bob”;”Frank”;”Paul”;”Randy”;”Mary”;”Jill”;”Ron”;”Tim”}
en voer in met CtrlL+Shift+Enter. Dit is een unieke lijst met de namen van kolom B van de Sheet ‘Data’.

en de volgende lijst met labels in C4:D4

={“Contacts”\”Sales”}

In C5 enter: =SUMIF(REPS;B5;CONTACTS)
In D5 enter: =SUMIF(REPS;B5;SALES)

Selecteer C5:D5 en kopieer naar beneden. Je krijgt dan:

={“Sales Rep”;”Contacts”;”Sales”\ “Bob”;560;4403700\ “Frank”;644;4373400\ “Paul”;392;2992300\ “Randy”;380;3394000\ “Mary”;476;2730300\ “Jill”;548;4153900\ “Ron”;51;297244\ “Tim”;73;400305}

Terug naar SUMPRODUCT.

Maak in nog een ander werkblad (dat je SUMPRODUCT kunt noemen) de volgende lijst met maanden in B10:B21:

={“Jan”; “Feb”; “Mar”; “Apr”; “May”; “Jun”; “Jul”; “Aug”; “Sep”; “Oct”; “Nov”; “Dec”}

De volgende lijst met labels in C9:E9:

{“Sales Reps”\”Contacts”\”Sales”}

in B1 het volgende label: Regio.

Het laatste blad is bedoeld om een overzicht per regio per maand te maken.

Als we “North” invoeren in B2, zal het overzicht de regio “North” omvatten. Als we niets invoeren in B2 (we laten het leeg), zal het overzicht alle regio’s samen omvatten. Nu de SUMPRODUCT-formules:

Voer in C10 in en kopieer naar beneden tot en met de maand december (C21):

=IF(LEN($B$2);SUMPRODUCT((MONTHS=B10)*(REGIONS=$B$2));
COUNTIF(MONTHS;B10))

Als B2 ‘North’ is, wordt het SUMPRODUCT-gedeelte van deze IF-formule uitgevoerd, wat resulteert in een telling die aangeeft dat er 2 verkopers actief zijn geweest in de maand ‘jan’ in de regio ‘North’.

Het SUMPRODUCT-gedeelte bestaat hier uit twee booleaanse termen: (MONTHS=B10) en (REGIONS=$B$2) die met AND worden gecombineerd, dat wil zeggen * tussen deze termen is een booleaanse AND die vergelijkbaar is met de logische functie AND [ Opmerking: AND(MONTHS=B10,REGIONS=$B$2) is niet toegestaan, omdat AND in Excel geen constante array oplevert, maar slechts één enkel logisch resultaat. ]

Wat het SUMPRODUCT-gedeelte hierboven doet, is een multiconditionele telling. Een rij van een maand en een regio wordt geteld als de maandwaarde “Jan” is en als de regiowaarde “noord” is. Hoe werkt het?

Elke cel in het MONTHS-bereik wordt geëvalueerd om te zien of het “Jan” is, wat leidt tot de array:

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

en elke cel in het REGIONS bereik wordt geëvalueerd en leidt tot de array (matrix):

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

En we weten dat TRUE altijd wordt gedefinieerd als 1 en FALSE wordt gedefinieerd als 0. Zodat we krijgen

=IF(LEN($B$2);SUMPRODUCT({1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}*{1;1;0;0;0;1;1;1;0;0;0;1;1;1;0;0;0;1;1;1;0;0;0});COUNTIF(MONTHS;B10))

Door deze twee arrays te vermenigvuldigen ontstaat de volgende array:

{1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

Waarbij TRUETRUE = 1, TRUEFALSE = 0 en FALSE*FALSE = 0 [dat wil zeggen dat Excel TRUE omzet naar 1, FALSE naar 0 en de vermenigvuldiging uitvoert]

Vervolgens wordt de resulterende reeks van 1-en en 0-en opgeteld, wat 2 oplevert, het aantal dat we zoeken.

Voer in D10 in en kopieer naar beneden tot en met de maand december:

=IF(LEN($B$2);SUMPRODUCT((MONTHS=B10)*(REGIONS=$B$2);(CONTACTS));SUMIF(MONTHS;B10;CONTACTS))

Het SUMPRODUCT-gedeelte van de voorgaande IF-formule produceert een totaal van alle klanten/contacten per regio en per maand, wat een multiconditionele som is. Dat wil zeggen dat het alle waarden in CONTACTS optelt als de bijbehorende MONTHS-waarden “Jan” zijn en de REGIONS-waarden “north”.

Als B2 ‘North’ is, wordt het SUMPRODUCT-gedeelte uitgevoerd, wat resulteert in een totaal (som) dat aangeeft dat er in de maand ‘jan’ in de regio ‘North’ 93 klanten/contacten zijn bediend/gefactureerd.

We hebben de resultaatmatrix van (MONTHS=B10)*(REGIONS=$B$2) al gezien:

=IF(LEN($B$2);SUMPRODUCT({1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};{58;35;25;47;39;44;46;74;29;45;52;30;44;57;13;14;36;54;44;79;36;31;22});SUMIF(MONTHS;B10;CONTACTS))

Resulteert in:

{1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

Vervolgens wordt vermenigvuldigd met:

{58; 35; 25; 47; 39; 44; 46; 74; 29; 45; 52; 30; 44; 57; 13; 14; 36; 54; 44; 79; 36; 31; 22}

bestaande uit waarden in CONTACTS, krijgen we de array (zie de MS-uitleg aan het begin):

{58; 35; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

Die uiteindelijk wordt opgeteld, wat resulteert in 93.

Wanneer je het numerieke gedeelte van een reeks tekenreeksen wilt verwijderen, zoals

={“vbn52”;
“vb263”;
“s45”}

Gebruik de formule:

=SUBSTITUTE(A1; RIGHT(A1; SUMPRODUCT((LEN(A1) – LEN(SUBSTITUTE(A1; {0\1\2\3\4\5\6\7\8\9};””)))));””)

Resultaat:

vbn, vb, s

=SUBSTITUTE(A1; RIGHT(A1; SUMPRODUCT((LEN(A1) – LEN(SUBSTITUTE(A1; {0\1\2\3\4\5\6\7\8\9};””)))));””)

Sommige mensen waren verbaasd over de werking van de laatste formule.

Het resultaat van de SUBSTITUTE-matrix is een argument voor de functie LEN, en dat is op zijn beurt onderdeel van een matrixexpressie die een argument is voor SUMPRODUCT. SUMPRODUCT reduceert dit vervolgens tot één getal. Als [A1] ‘VBN1234567890’ bevat, werkt de formule als volgt.

SUBSTITUTE(A1;{0,1,2,3,4,5,6,7,8,9};””) retourneert de matrix
{“VBN123456789”, “VBN234567890”, “VBN134567890”, “VBN124567890”, “VBN123567890”, “VBN123467890”, “VBN123457890”, “VBN123456890”, “VBN123456790”, “VBN123456780”}

LEN(SUBSTITUTE(A1; {0,1,2,3,4,5,6,7,8,9};””)) retourneert de matrix
{12, 12, 12, 12, 12, 12, 12, 12, 12, 12}

(LEN(A1) – LEN(SUBSTITUTE(A1; {0,1,2,3,4,5,6,7,8,9};””))) retourneert de matrix
{1,1,1,1,1,1,1,1,1,1}

SUMPRODUCT((LEN(A1) – LEN(SUBSTITUTE(A1; {0,1,2,3,4,5,6,7,8,9};””)))) retourneert
10

Full credits go to Aladin Akyurek, member of the mrexcel.com board 

Een boer heeft in totaal 35 konijnen en kippen in zijn stal. En die konijnen en kippen hebben samen 94 poten. Hoeveel kippen en hoeveel konijnen zijn er?

Wat weten we zeker?

• Er zijn 35 dieren dus:
  x = aantal kippen
  y = aantal konijnen
  x + y = 35 = aantal dieren
• Dan:
  Elke kip heeft 2 poten en elk konijn heeft 4 poten. Dat maakt:
  2x + 4y = 94 =aantal poten

Er zijn twee vergelijkingen op te lossen. is We noemen dat een verzameling van twee of meer vergelijkingen die dezelfde variabelen gebruiken (x en y). De oplossing van die twee vergelijkingen is de combinatie van x en y die aan de 2 vergelijkingen tegelijk moeten voldoen. De stappen:

x + y = 35 =aantal dieren
2x + 4y = 94 =aantal poten
Eerst vergelijking herschrijven:
x = 35 – y
Deze vergelijking substitueren/invoegen:
2(35 – y) + 4y = 94 berekenen: (2 * 35 – 2y) + 4y = 94 wordt:
70 – 2y + 4y = 94. Gelijke termen combineren -2y + 4y = +2y berekenen:
70 + 2y = 94 nu aan beide kanten 70 aftrekken wordt:
70 + 2y -70 = 94 -70 resulteert in 2y = 24 resultaat y=12.

Tenslotte: x = 35 – y eindresultaat: x = 35 – 12 = 23
23 kippen en 12 konijnen
Aantal poten: 2x + 4y = 94 wordt (2 * 23) + (4 * 12) wordt 46 + 48 = 94 poten.

Samengevat:

Ooit deze handige manier om overzicht te maken van de totalen van medewerkers (Client) gezien op het mrexcel.com forum. Credits voor de expert op dat forum onderaan de post.

Op Sheet1 staan Client in Kolom A. In Kolom B Date en in Kolom C staan Fee. In Kolom A kan de naam van dezelfde Client meerdere keren voorkomen.

Op Sheet2 willen we de Client met het hoogste totaalbedrag weergeven en vervolgens de tweede Client etc. Een soort van Top 10 zeg maar. Bovendien geldt er een datum limiet. Bijvoorbeeld alleen resultaten ophalen tussen 06-08-2023 en 12-11-2025. Die periode mag je aanpassen naar je wensen.

Sheet1

Sheet 2

Namen maken:

1. Definieer Client en verwijs naar Sheet1!$A$2:$A$831 in de Name Manager.

2. Definieer Date en verwijs naar Sheet1!$B$2:$B$831 in de Name Manager.

3. Definieer Fee en verwijs naar Sheet1!$C$2:$C$831 in de Name Manager.

4. Definieer Ivec en verwijs naar =ROW(Employee)-ROW(INDEX(Employee,1,1))+1 in de Name Manager.

De formules op Sheet2:

In A3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Client="");IF(ISNUMBER(Date);IF(Date>=A2;IF(Date<=B2;MATCH(Client;Client;0)))));Ivec);1))

In A5 control+shift+enter, not just enter; and copy down:

=IF($B5="";"";INDEX(Client;SMALL(IFERROR(IF(SUMIFS(Fee;Client;IF(FREQUENCY(IF(1-(Client="");IF(ISNUMBER(Date);IF(Date>=$A$2;IF(Date<=$B$2;MATCH(Client;Client;0)))));Ivec);Client);Date;">="&$A$2;Date;"<="&$B$2)=$B5;Ivec);0);COUNTIFS($B$5:B5;B5))))

In B5 control+shift+enter, not just enter; and copy down:

=IF(ROWS($B$5:B5)>$A$3;"";LARGE(SUMIFS(Fee;Client;IF(FREQUENCY(IF(1-(Client="");IF(ISNUMBER(Date);IF(Date>=$A$2;IF(Date<=$B$2;MATCH(Client;Client;0)))));Ivec);Client);Date;">="&$A$2;Date;"<="&$B$2);ROWS($B$5:B5)))

In A2 en B2 van Sheet2 kun je datums zetten waarbinnen gezocht moet worden.

Credits Aladin Akyurek, member www.mrexcel.com
Hij is een expert op het gebied van Excel en het samenstellen van verbluffende formules.

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.

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