Monthly Archives: January 2025

Unieke waarden met twee criteria

31/01/2025Excel formulaadmin

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

Waarden optellen terwijl Id telkens verandert

30/01/2025Excel formula, Excel VBAadmin

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:

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

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

=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.

In K3:

=SUMIFS(B:H;A:G;J3)

(Dit is GEEN array formule, dus gewoon invoeren met alleen Enter).
Doorvoeren naar beneden

Download bestand

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

Top-N, unieke lijst en bedragen optellen

29/01/2025Excel formulaadmin

Op Sheet1 staan medewerkers in Kolom A. In Kolom B datums en in Kolom C staan bedragen. In Kolom A kan de naam van dezelfde medewerker meerdere keren voorkomen.

Op Sheet2 willen we de medewerker met het hoogste totaalbedrag weergeven en vervolgens de tweede medewerker etc. Een soort van Top 10 zeg maar. Bovendien geldt er een datum limiet. Bijvoorbeeld alleen resultaten ophalen tussen 29-1-2006 en 11-3-2006

Sheet1

Sheet2

Namen maken:

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

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

3. Definieer Amount en verwijs naar Sheet1!$C$2:$C$20 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

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

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

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

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

Top-N berekenen en bedragen optellen

29/01/2025Excel formulaadmin

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)

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

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

=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

=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

Verpakkingseenheid (doos) * item (fles) totaliseren

29/01/2025Excel formulaadmin

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

Het liggend streepje, the underscore _

29/01/2025Excel VBAadmin

Een onderstrepingsteken “_” wordt doorgaans wordt gebruikt in een VBA-coderegel.

Een underscore (wordt ALTIJD direct voorafgegaan door een spatie) in een VBA-coderegel geeft aan dat de huidige instructie doorgaat op de volgende regel. Omdat VBA geen tekstterugloop heeft, wordt het gebruikt om een enkele regel code over twee regels te verdelen, om de code leesbaarder te maken.

Dus in plaats van dit:

Sub-Macro1()
  Msgbox "Een underscore (wordt ALTIJD direct voorafgegaan door een spatie) in een VBA-coderegel geeft aan dat de huidige instructie doorgaat op de volgende regel."
Einde sub

Sub-Macro1()

Msgbox "Een underscore (wordt ALTIJD direct voorafgegaan door een spatie) in een VBA-coderegel geeft aan dat de huidige instructie doorgaat op de volgende regel."

Einde sub

Kunnen we dit schrijven:

Sub-Macro1()
  Msgbox "Een underscore (wordt ALTIJD direct voorafgegaan door een spatie)" _
         & "en geeft in een VBA-coderegel aan dat de" _
         & "huidige instructie doorgaat op de volgende regel."
Einde sub

Sub-Macro1()

Msgbox "Een underscore (wordt ALTIJD direct voorafgegaan door een spatie)" _

& "en geeft in een VBA-coderegel aan dat de" _

& "huidige instructie doorgaat op de volgende regel."

Einde sub

Nog een voorbeeld:

Sub Macro2()
Dim oRng As Range
  Set oRng = ActiveDocument.SelectContentControlsByTitle("RichText Container").Item(1).Range.Paragraphs(3).Range
End Sub

Sub Macro2()

Dim oRng As Range

Set oRng = ActiveDocument.SelectContentControlsByTitle("RichText Container").Item(1).Range.Paragraphs(3).Range

End Sub

Kunnen we zo schrijven:

Sub Macro2()
Dim oRng As Range
  Set oRng = ActiveDocument.SelectContentControlsByTitle("RichText Container") _
           .Item(1).Range.Paragraphs(3).Range
End Sub

Sub Macro2()

Dim oRng As Range

Set oRng = ActiveDocument.SelectContentControlsByTitle("RichText Container") _

.Item(1).Range.Paragraphs(3).Range

End Sub

Nogmaals, je moet een spatie invoegen vóór de underscore. Anders retourneert de VBA-compiler een fout.

Positie laatste waarde in bereik bepalen

29/01/2025Excel formulaadmin

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

C5:

=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

29/01/2025Excel VBAadmin

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

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)

Range("D2:E2").Resize(UBound(b)).Value = b

End Sub

8 Tekens vanaf de linkerkant van een tekenreeks verwijderen

29/01/2025Excel VBAadmin

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

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

Voorbeeld data

Resultaat

Splits data en zet in één cel gescheiden door komma

29/01/2025Excel VBAadmin

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

Function aconcat(a As Variant, Optional sep As String = "") As String

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

De bovenstaande code is een functie die gebruikt wordt in de formule in B26

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.

Dream come true

https://whydonate.com/en/fundraising/cafe-restoration-project

Monthly Archives: January 2025

Unieke waarden met twee criteria

Waarden optellen terwijl Id telkens verandert

Top-N, unieke lijst en bedragen optellen

Top-N berekenen en bedragen optellen

Verpakkingseenheid (doos) * item (fles) totaliseren

Het liggend streepje, the underscore _

Positie laatste waarde in bereik bepalen

Gegevens in één cel splitsen

8 Tekens vanaf de linkerkant van een tekenreeks verwijderen

Splits data en zet in één cel gescheiden door komma