Oorspronkelijk geplaatst op: 4 augustus 2015

Je wilt een item opzoeken maar het probleem is dat er dubbele waarden in kolom B staan. Als je VERT.ZOEKEN gebruikt, wordt alleen de 1-ste overeenkomst gevonden. Maar wat als je de 2-de of 3-de overeenkomst wil vinden. De oplossing is dat je een extra kolom toevoegt (Kolom A) en de dubbele items, Kolom B, van een volgnummer voorziet.

Het volgnummer kun je op de volgende manier genereren:
We gaan er van uit dat er maximaal 3 dubbele waarden voorkomen. In het gele gedeelte, Kolom H, vul je de parameters in:
[H1] Type je de waarde die je wilt zoeken. Bijvoorbeeld: ITEM 3
[H2] laat je leeg
[H3] zet je, 1
[H4] zet je, 2
[H5] zet je, 3

In A2 zet je de formule om de volgnummers te genereren.
[A2] =ALS(AANTAl.ALS($B$2:$B$14;$H$1)>1;ALS(B2=$H$1;AANTAL($A$1:A1)+1;””);””)
Vervolgens doorvoeren naar beneden tot A14

Tenslotte,
[H8] = H1
[H9] =ALS.FOUT(INDEX($C$2:$C$14;VERGELIJKEN(H$2&$H1;INDEX($A$2:$A$14&$B$2:$B$14;);0));””)
Formule doorvoeren naar beneden tot H12.

De truc is eigenlijk dat je het volgnummer en het item samenvoegt tot één zoekwaarde d.m.v. het Ampersand teken. Dat gebeurt in het rood gemerkte gedeelte:
H$2&$H1 wordt dus in feite 1ITEM 3. Hetzelfde gebeurt met de waarden in de kolommen A en B: $A$2:$A$14&$B$2:$B$14

Oorspronkelijk geplaatst op: 12 augustus 2015

Formule in [G1]: =ALS(EN(REST(RIJ(A1);5) > 0;REST(RIJ(A1);5) < 4);A1;””)
Bijvoorbeeld 3 rijen weergeven 2 rijen overslaan.

Stel, je hebt verkoopcijfers of iets dergelijks en je wilt bepaalde maanden van een bepaalde verkoper optellen. In dit voorbeeld de maanden Februari en Mei. Tabel staat hier onder.
Let eerst op kolom I. In I1 zet je Id, en in I3 Totaal. In I2 kun je de id van de verkoper typen maar handiger is om daar een keuzelijst van te maken. Als je unieke waarden hebt in kolom A is dat een fluitje van een cent. Kies voor:

Oorspronkelijk geplaatst op: 13 augustus 2015

Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan > Lijst | Bron > selecteer de waarden in kolom A

Nu kun je gewoon een waarde in I2 kiezen middels de keuzelijst.

Formule gaat in cel I4:

=SOM(SOMMEN.ALS(INDEX($B$2:$G$10;VERGELIJKEN($I$2;$A$2:$A$10;0);0);$B$1:$G$1;{“februari”\”Mei”}))

Let even speciaal op het rode gedeelte in de formule. Daar geef je de maanden op die je wilt tellen, gescheiden door een backslash.Let ook op de Accolades { }

Oorspronkelijk geplaatst op: 14 augustus 2015

Oorspronkelijk geplaatst op: 17 augustus 2015

ADO betekent “ActiveX Data Objects“. Kun je verder vergeten. Wat kun je er mee? Gegevens ophalen uit een ander (Excel)bestand. Dat andere bestand fungeert dan als een soort database. Wat mij betreft kun je net zo goed het bestand openen en de gegevens die je nodig hebt filteren en vervolgens kopiëren om er verder mee te werken.

Wie persé die voorziening wil gebruiken hier een voorbeeld.

‘***********************************************
‘Geef een verwijzing op naar:
‘Microsoft ActiveX Data Objects Library
‘Te bereiken via: Alt+F11 | Extra | Verwijzingen
‘***********************************************

'***********************************************
'Geef een verwijzing op naar:
'Microsoft ActiveX Data Objects Library
'Te bereiken via: Alt+F11 | Extra | Verwijzingen
'***********************************************

Sub Voorbeeld_ADO_Verbinding()
Dim arrMatrix As Variant
Dim objVerbinding As New ADODB.Connection
Dim objGegevensSet As New ADODB.Recordset
Dim strPad As String, strVerbind As String, strQuery As String

'Pad naar je bestand waar je gegevens van wil ophalen
strPad = "C:\temp\Copy_Of_Musea.xlsx"

strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"

objVerbinding.Open strVerbind

'Tabelnaam is de bladnaam bijv. [Copy_Of_Musea$]
'Let op het Dollarteken achter Musea. Dat MOET erachter.
strSQL = "SELECT * From [Copy_Of_Musea$]"
    
objGegevensSet.Open strSQL, objVerbinding

'Gegevens in matrix laden
arrMatrix = objGegevensSet.GetRows

'Gegevens in blad plakken
ActiveSheet.Range("A2").CopyFromRecordset objGegevensSet

'Gegevensset sluiten
objGegevensSet.Close

'Verbinding sluiten
objVerbinding.Close
End Sub

'***********************************************
'Geef een verwijzing op naar:
'Microsoft ActiveX Data Objects Library
'Te bereiken via: Alt+F11 | Extra | Verwijzingen
'***********************************************

Sub Voorbeeld_ADO_Verbinding()
Dim arrMatrix As Variant
Dim objVerbinding As New ADODB.Connection
Dim objGegevensSet As New ADODB.Recordset
Dim strPad As String, strVerbind As String, strQuery As String

'Pad naar je bestand waar je gegevens van wil ophalen
strPad = "C:\temp\Copy_Of_Musea.xlsx"

strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"

objVerbinding.Open strVerbind

'Tabelnaam is de bladnaam bijv. [Copy_Of_Musea$]
'Let op het Dollarteken achter Musea. Dat MOET erachter.
strSQL = "SELECT * From [Copy_Of_Musea$]"
    
objGegevensSet.Open strSQL, objVerbinding

'Gegevens in matrix laden
arrMatrix = objGegevensSet.GetRows

'Gegevens in blad plakken
ActiveSheet.Range("A2").CopyFromRecordset objGegevensSet

'Gegevensset sluiten
objGegevensSet.Close

'Verbinding sluiten
objVerbinding.Close
End Sub

Oorspronkelijk geplaatst op: 29 augustus 2015

Verkopers hebben goed hun best gedaan en van alles verkocht en geld verdiend. Nu wil je de totalen hebben van die verkopers maar slechts als ze voldoen aan een bepaald criterium. Bijvoorbeeld: Periode = 3.

In de kolom Verkopers, Kolom B, staan de namen. Er staan echter dubbele waarden in en je wilt de naam van de verkoper slechts één keer weergeven en daarachter het totaal van periode = 3. Kijk naar de opzet van het werkblad. Resultaten weergegeven vanaf E5.

FORMULES:

In [F2] voer je handmatig de periode in.

Invoeren met Ctrl+Shift+Enter, NIET met Enter.
[F3] =SOM(ALS(INTERVAL(ALS(Periode=$F$2;ALS(Verkoper<>””;VERGELIJKEN(Verkoper;Verkoper;0)));intRij);1))

Invoeren met Ctrl+Shift+Enter, NIET met Enter
[E5] =ALS(RIJEN($E$5:E5<=$F$3;INDEX(Verkoper;KLEINSTE(ALS(INTERVAL(ALS(Periode=$F$2;IF(Verkoper<>””;
VERGELIJKEN(Verkoper;Verkoper;0)));intRij);intRij);RIJEN($E$5:E5)));””)

Invoeren met Enter
[F5]=ALS(E5<>””;AANTALLEN.ALS(Periode;$F$2;Verkoper;E5);””)

Invoeren met Enter
[G5]=ALS($E5=””;””;SOMMEN.ALS(Bedrag;Verkoper;$E5;Periode;$F$2))

Namen geven: Ga naar Formules | in de groep Gedefinieerde namen |  Namen beheren

Periode
=Sheet1!$A$2:$A$40

Verkoper
=Sheet1!$B$2:$B$40

Bedrag
=Sheet1!$C$2:$C$40

intRij
=ROW(Verkoper)-ROW(INDEX(Verkoper;1;1))+1

Oorspronkelijk geplaatst op: 25 september 2015

Simpel voorbeeld.
Totalen berekenen van diverse verkopers. In dit voorbeeld: John, Jams, Super en Band. Beetje gekke namen maar dat boeit verder niet.
Formule komt in C11.

=SOMPRODUCT((($A$2:$A$7=A11)+($A$2:$A$7=B11));$C$2:$C$7)

Naar beneden kopiëren. Klaar

Oorspronkelijk geplaatst op: 29 september 2015

Je hebt een aantal artikelen die besteld worden door verschillende winkeliers. Je wil weten hoeveel de verkoop van 1 bepaald produkt heeft opgebracht. In dit voorbeeld: Tofu.

Formule in E4;
=SOMPRODUCT(–(A2:A23=E2);B2:B23:C2:C23)

Deze formule vermenigvuldigt kolom A met kolom B met kolom C en het resultaat optelt.

In feite krijg je dus:

Rij 5: 1 * € 18,60 * 9 = € 167,40
Rij 8: 1 * € 18,60 * 35 = € 651,00
Rij 14:1 * € 18,60 * 25 = € 465,00
Rij 21: 1 * € 18,60 * 21 = € 390,60
Totaal:                           € 1674,00

Maar hoe krijg je het voor elkaar dat alleen de rijen met “Tofu” worden berekend en wat doet die 1 eigenlijk? Daarvoor zorgt dit gedeelte:
– –(A2:A23=E2)
In kolom A wordt gekeken welk produkt voldoet aan het criterium in cel E2 (Tofu). Normaliter krijg je dan een reeks van FALSE en/of TRUE. De twee minnen (– –) aan het begin zorgen er echter voor dat als Tofu gevonden wordt er een 1 (i. p. v. TRUE) wordt gegenereerd. Zoniet dan wordt een 0 (i.p.v. FALSE) gegenereerd.

Oorspronkelijk geplaatst op: 29 september 2016

Klant BERGS heeft diverse producten gekocht. We willen het totaal berekenen door van al zijn gekochte producten het subtotaal (Kolom E) op te tellen.

Formule in G5.
=SUMPRODUCT(($A$2:$A$10=$G$2)*($E$2:$E$10))

In G2 kun je een validatielijst maken met alle namen van de klanten.

Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan > Lijst | Bron > (type in het vak ->) ALFKI;BERGS;FAMIA
Let op de puntkomma tussen de klantnamen.

Oorspronkelijk geplaatst op: 29 september 2016

Je hebt een lijst waarin dubbele waarden voorkomen. Je wilt een lijst maken met unieke waarden maar één waarde wil je uitsluiten/negeren.

Formule C7
=IFERROR(INDEX($A$7:$A$28;SMALL(IF(FREQUENCY(IF($A$7:$A$28<>””;IF(1-ISNUMBER(SEARCH($B$4;$A$7:$A$28));MATCH($A$7:$A$28;$A$7:$A$28;0)));ROW($A$7:$A$28)-ROW($A$7)+1);ROW($A$7:$A$28)-ROW($A$7)+1);ROWS(C$7:C7)));””)

Let op: Invoeren met: Ctrl+Shift+Enter

Formule B3
=SUM(IF(FREQUENCY(IF($A$7:$A$28<>””;IF(1-ISNUMBER(SEARCH($B$4;$A$7:$A$28));MATCH($A$7:$A$28;$A$7:$A$28;0)));ROW($A$7:$A$28)-ROW($A$7)+1);1))

Let op: Invoeren met: Ctrl+Shift+Enter