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
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)
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.
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
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.
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 1-11-2013 en 31-12-2013
Sheet1
Sheet 2
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.
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.
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
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
