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.
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.
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.
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.
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.
Let op: Plaats die accolades { } niet handmatig. 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.
Een dynamische lijst maken. Dit betekent dat, naar mate je de lijst uitbreidt en dus langer maakt, de lijst zich als het ware aanpast.
We hebben namen van landen in Kolom A. Sommige landen staan er dubbel in of zelfs driedubbel. In Kolom C willen we slechts unieke namen van landen.
Aan de slag. Zorg dat je gegevens hebt zoals in de afbeelding. Vervolgens dien je een aantal namen met daaraan gekoppeld formules te maken. Doe dat zoals hieronder beschreven:
Formulas > Name manager > New Name: = RowVector Refers to:
=ROW(Items)-ROW(INDEX(Items;1;1))+1
Klik: OK
Formulas > Name manager > New Name: = Items Refers to:
=Sheet1!$A$4:INDEX(Sheet1!$A$4:$A$20;Lrow)
Klik: OK
Formulas > Name manager > New Name: = Lrow Refers to:
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.
Ook 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.
Let op: Plaats die accolades { } niet handmatig. Doorvoeren naar beneden
Een paar winkels (Kolom A) hebben goede (of slechte) zaken gedaan en je ziet de resultaten per dag (Kolommen B:G in de afbeelding. De opgave dit keer is om de totalen (Kolom F) te berekenen. Er zijn 2 criteria namelijk, bedrag >= €5000 en de datum moet liggen tussen 2-9-2016 en 5-9-2016.
De vertegenwoordigers hebben weer eens goed hun best gedaan maar wie heeft het meeste verkocht per maand en wie is de topverkoper van het jaar?
Formule in F2
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.
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:
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
' Ref: TinyURL.com - shorten that long URL into a tiny URL
DimTempUnion()AsVariant
DimiAsLong,Itm AsVariant,Ctr AsLong
Fori=LBound(Arg)ToUBound(Arg)
Arg(i)=Arg(i)
IfIsArray(Arg(i))Then
ForEachItm InArg(i)
Ctr=Ctr+1
ReDimPreserveTempUnion(1ToCtr)AsVariant
TempUnion(Ctr)=Itm
NextItm
Else
Ctr=Ctr+1
ReDimPreserveTempUnion(1ToCtr)AsVariant
TempUnion(Ctr)=Arg(i)
EndIf
Nexti
ArrayUnion=TempUnion
EndFunction
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
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.
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.
In K3:
=SUMIFS(B:H;A:G;J3)
(Dit is GEEN array formule, dus gewoon invoeren met alleen Enter). Doorvoeren naar beneden
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