De VBA code genereert een lijst met bestanden in een map. Je kunt zelf een map (directory) kiezen waarvan je de bestanden wil zien.

Voorbeeld weergave:

Option Compare Text
Option Explicit

Function Excludes(Ext As String) As Boolean
Dim X, NumPos As Long
    'Function purpose:  To exclude listed file extensions from hyperlink listing
    
    'Enter/adjust file extensions to EXCLUDE from listing here:
    X = Array("exe", "bat", "dll", "zip")
    
    On Error Resume Next
    NumPos = Application.WorksheetFunction.Match(Ext, X, 0)
    If NumPos > 0 Then Excludes = True
    On Error GoTo 0
End Function

Sub HyperlinkFileList()
'Macro purpose:  To create a hyperlinked list of all files in a user
'specified directory, including file size and date last modified
'NOTE:  The 'TextToDisplay' property (of the Hyperlink object) was added
'in Excel 2000.  This code tests the Excel version and does not use the
'Texttodisplay property if using XL 97.

Dim fso As Object, ShellApp As Object, File As Object
Dim SubFolder As Object, Directory As String
Dim Problem As Boolean, ExcelVer As Integer

    'Turn off screen flashing
    Application.ScreenUpdating = False

    'Create objects to get a listing of all files in the directory
    Set fso = CreateObject("Scripting.FileSystemObject")

    'Prompt user to select a directory
    Do
        Problem = False
        Set ShellApp = _
            CreateObject("Shell.Application").Browseforfolder(0, _
            "Please choose a folder", 0, "c:\\")
        On Error Resume Next
        
        'Evaluate if directory is valid
        Directory = ShellApp.self.path
        Set SubFolder = fso.GetFolder(Directory).Files
        If Err.Number <> 0 Then
            If MsgBox("You did not choose a valid directory!" _
                & vbCrLf & "Would you like to try again?", _
                vbYesNoCancel, "Directory Required") <> vbYes Then _
                Exit Sub
            Problem = True
        End If
        On Error GoTo 0
    Loop Until Problem = False

    'Set up the headers on the worksheet
    With ActiveSheet
        With .Range("A1")
            .Value = "Listing of all files in:"
            .ColumnWidth = 40
            
            'If Excel 2000 or greater, add hyperlink with file name
            'displayed.  If earlier, add hyperlink with full path displayed
            
            'Using XL2000+
            If Val(Application.Version) > 8 Then
                .Parent.Hyperlinks.Add Anchor:=.Offset(0, 1), _
                    Address:=Directory, TextToDisplay:=Directory
            
            'Using XL97
            Else
                .Parent.Hyperlinks.Add Anchor:=.Offset(0, 1), _
                    Address:=Directory
            End If
        End With
        With .Range("A2")
            .Value = "File Name"
            .Interior.ColorIndex = 15
            With .Offset(0, 1)
                .ColumnWidth = 15
                .Value = "Date Modified"
                .Interior.ColorIndex = 15
                .HorizontalAlignment = xlCenter
            End With
            With .Offset(0, 2)
                .ColumnWidth = 15
                .Value = "File Size (Kb)"
                .Interior.ColorIndex = 15
                .HorizontalAlignment = xlCenter
            End With
        End With
    End With

    'Adds each file, details and hyperlinks to the list
    For Each File In SubFolder
        If Not Excludes(Right(File.path, 3)) = True Then
            With ActiveSheet
                
                'If Excel 2000 or greater, add hyperlink with file name
                'displayed.  If earlier, add hyperlink with full path displayed
                
                'Using XL2000+
                If Val(Application.Version) > 8 Then
                    .Hyperlinks.Add _
                        Anchor:=ActiveSheet.Range("A65536").End(xlUp).Offset(1, _
                        0), Address:=File.path, _
                        TextToDisplay:=File.Name
                
                'Using XL97
                Else
                    .Hyperlinks.Add _
                        Anchor:=ActiveSheet.Range("A65536").End(xlUp).Offset(1, _
                        0), Address:=File.path
                End If
                
                'Add date last modified, and size in KB
                With .Range("A65536").End(xlUp)
                    .Offset(0, 1) = File.datelastModified
                    With .Offset(0, 2)
                        .Value = _
                            WorksheetFunction.Round(File.Size / _
                            1024, 1)
                        .NumberFormat = "#,##0.0"
                    End With
                End With
            End With
        End If
    Next
End Sub

Indien je een unieke lijst wil genereren is dit de manier om dat te doen. De waarden staan in kolom B. Daar staan ook dubbele of zelfs drie-dubbele waarden in:

In D2 zet je de volgende formule:

=IFERROR(INDEX($B$2:$B$1000;SMALL(IF(FREQUENCY(IF($B$2:$B$1000<>””;MATCH($B$2:$B$1000;$B$2:$B$1000;0));ROW($B$2:$B$1000)-ROW(INDEX($B$2:$B$1000;1;1))+1);ROW($B$2:$B$1000)-ROW(INDEX($B$2:$B$1000;1;1))+1);ROWS($A$2:A2)));””)

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.

Nog een mogelijkheid met een kortere maar minder robuuste formule:

In F2 zet je de volgende formule:
=IFERROR(INDEX($B$2:$B$1000; MATCH(0;COUNTIF($F$1:F1; $B$2:$B$1000);0));””)

Ook dit is weer een array formule, dus invoeren met: Ctrl+Shift+Enter
Doorvoeren naar beneden.

Tenslotte, in kolom H staan tekst en cijfers door elkaar. In kolom I zet je alweer een array formule:

=IFERROR(INDEX($H$2:$H$30;SMALL(IF(FREQUENCY(IF($H$2:$H$30<>””;MATCH($H$2:$H$30;$H$2:$H$30;0));ROW($H$2:$H$30)-ROW(INDEX($H$2:$H$30;1;1))+1);ROW($H$2:$H$30)-ROW(INDEX($H$2:$H$30;1;1))+1);ROWS($A$2:A2)));””)

Ook dit is weer een array formule, dus invoeren met: Ctrl+Shift+Enter
Doorvoeren naar beneden.

De wielrenners in de Tour de France hebben weer goed hun best gedaan. Sommige renners hebben een etappe gewonnen maar andere renners hebben meer dan 1 etappe gewonnen. Welke renners zijn dat? Ze staan in kolom D. Hiervoor is een ingewikkelde formule gebruikt en die plaats je in D2:

=IFERROR(INDEX($A$2:$A$20;SMALL(IF(FREQUENCY(IF($A$2:$A$20<>””;IF($B$2:$B$20=”yes”;IF($C$2:$C$20=”yes”;MATCH($A$2:$A$20;$A$2:$A$20;0))));ROW($A$2:$A$20)-ROW(INDEX($A$2:$A$20;1;1))+1);ROW($A$2:$A$20)-ROW(INDEX($A$2:$A$20;1;1))+1);ROWS($1:1)));””)

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.

Best een moeilijke klus. In de kolommen A, C, E, G staan de Id’s van de verkopers. In de kolommen B, D, F, H staan de verkoopcijfers. Je wil de verkoopcijfers van elke Id (verkoper) optellen. Natuurlijk is er een probleem, de Id’s en verkoopcijfers kunnen telkens veranderen.

Ten eerste voeg je onderstaande code toe:

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

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
    For i = LBound(Arg) To UBound(Arg)
        Arg(i) = Arg(i)
        If IsArray(Arg(i)) Then
            For Each Itm In Arg(i)
                Ctr = Ctr + 1
                ReDim Preserve TempUnion(1 To Ctr) As Variant
                TempUnion(Ctr) = Itm
            Next Itm
        Else
            Ctr = Ctr + 1
            ReDim Preserve TempUnion(1 To Ctr) As Variant
            TempUnion(Ctr) = Arg(i)
        End If
    Next i
ArrayUnion = TempUnion
End Function

Vervolgens heb je de volgende formules nodig. Eerst maak je 2 benoemde bereiken. Ga naar:
Formulas | Name manager | New
en geef de naam Ivec. Vervolgens:
In het vak Refers to zet je de volgende formule:
=ROW(INDIRECT(“1:”&COLUMNS(PNdata)))

Herhaal dit:
Naam: PNdata
Refers to:
=arrayunion(Sheet1!$A$2:$A$12;Sheet1!$C$2:$C$12;Sheet1!$E$2:$E$12;Sheet1!$G$2:$G$12)

In J1: =SUM(IF(FREQUENCY(IF(PNdata<>””;MATCH(“~”&PNdata;PNdata&””;0));Ivec);1))

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.

In J3:
=IF(ROWS($J$3:J3)<=$J$1;MIN(IF(ISNUMBER(MATCH(PNdata;$J$2:J2;0));””;PNdata));””)
Doorvoeren naar beneden.

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.

In K3: =SUMIFS(B:H;A:G;J3)
(Dit is GEEN array formule, dus gewoon invoeren met alleen Enter).
Doorvoeren naar beneden.

Credits gaan naar: 
Code: Juan Pablo González
Spec: Aladin Akyurek
Source: https://tinyurl.com/y3b9r9qg
May 4, 2003

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.

Bijvoorbeeld in A1:
=ExtendedEuclideanWithSteps(1190;672;33;11;TRUE)

Function ExtendedEuclideanWithSteps(a As Long, b As Long, ByRef x As Long, ByRef y As Long, Optional showSteps As Boolean = False) As Long
    ' Extended Euclidean Algorithm with step-by-step explanation
    Dim temp As Long
    Dim x1 As Long, x2 As Long, y1 As Long, y2 As Long
    Dim q As Long, r As Long
    Dim stepCount As Integer
    Dim result As String
    Dim originalA As Long, originalB As Long
    
    ' Store original values
    originalA = a
    originalB = b
    
    ' Ensure positive numbers
    a = Abs(a)
    b = Abs(b)
    
    ' Handle zero cases
    If a = 0 And b = 0 Then
        x = 0: y = 0
        ExtendedEuclideanWithSteps = 0
        Exit Function
    End If
    
    If a = 0 Then
        x = 0: y = Sgn(b)
        ExtendedEuclideanWithSteps = b
        Exit Function
    End If
    
    If b = 0 Then
        x = Sgn(a): y = 0
        ExtendedEuclideanWithSteps = a
        Exit Function
    End If
    
    ' Initialize coefficients and step counter
    x1 = 1: x2 = 0
    y1 = 0: y2 = 1
    stepCount = 0
    
    If showSteps Then
        result = "Extended Euclidean Algorithm Steps:" & vbCrLf & vbCrLf
        result = result & "Find GCD(" & originalA & ", " & originalB & ") and coefficients x, y" & vbCrLf
        result = result & "such that: " & originalA & "x + " & originalB & "y = GCD" & vbCrLf & vbCrLf
        result = result & "Step " & stepCount & ":" & vbCrLf
        result = result & "a = " & a & ", b = " & b & vbCrLf
        result = result & "x1 = " & x1 & ", x2 = " & x2 & vbCrLf
        result = result & "y1 = " & y1 & ", y2 = " & y2 & vbCrLf & vbCrLf
    End If
    
    ' Extended Euclidean Algorithm
    Do While b <> 0
        stepCount = stepCount + 1
        q = a \ b
        r = a Mod b
        
        If showSteps Then
            result = result & "Step " & stepCount & ":" & vbCrLf
            result = result & a & " = " & b & " * " & q & " + " & r & vbCrLf
        End If
        
        ' Update coefficients
        temp = x2
        x2 = x1 - q * x2
        x1 = temp
        
        temp = y2
        y2 = y1 - q * y2
        y1 = temp
        
        If showSteps Then
            result = result & "New coefficients: x1 = " & x1 & ", x2 = " & x2 & vbCrLf
            result = result & "                 y1 = " & y1 & ", y2 = " & y2 & vbCrLf & vbCrLf
        End If
        
        ' Update a and b
        a = b
        b = r
    Loop
    
    ' Set results
    x = x1
    y = y1
    ExtendedEuclideanWithSteps = a
    
    If showSteps Then
        result = result & "Final Result:" & vbCrLf
        result = result & "GCD(" & originalA & ", " & originalB & ") = " & a & vbCrLf
        result = result & "Coefficients: x = " & x & ", y = " & y & vbCrLf
        result = result & "Verification: " & originalA & "*" & x & " + " & originalB & "*" & y & " = "
        result = result & (originalA * x + originalB * y) & " = " & a
        
        MsgBox result
    End If
End Function

In $A$3:$A$93 namen waaronder veel dubbele namen, We willen in C3 een lijst met unieke namen en die lijst sorteren van A naar Z. Niet al te moeilijk. We hebben slechts één formule nodig.

In [C3] =IFERROR(INDEX($A$3:$A$93; MATCH(SMALL(IF(COUNTIF($C$2:C2; $A$3:$A$93)=0; COUNTIF($A$3:$A$93; "<"&$A$3:$A$93); ""); 1); COUNTIF($A$3:$A$93; "<"&$A$3:$A$93); 0));"")

Invoeren met Ctrl+Shift+Enter (NIET alleen Enter)
En doorvoeren naar beneden.