Tagarchief: VERT.ZOEKEN

EK-2016: wie wordt kampioen?

13 juni 2016Excel, VBA (Visual Basic for Applications)Aselect, CODE, CSE, Grootste, Index, Namen, Teken, Vergelijken, VERT.ZOEKENGijs Verbruggen

Er is al veel over geschreven: Nederland is er niet bij!

Waar niet bij? Ik bedoel het EK-voetbal natuurlijk. Dus dan maar de focus op België? Of toch Duitsland? Of gastland Frankrijk? Tsja, wie zal op 10 juli aangewezen worden als de kampioen van Europa?

Daarom de WK-voorspeller van 2 jaar geleden maar eens opgepoetst; alle landen ingevoerd, schema nagekeken om tot de ontdekking te komen, dat de indeling van de achtste finales wel heel anders gaat dan bij het WK.

In dit artikel bespreek ik dan ook niet meer de werking en de nauwkeurigheid van de voorspeller (kijk daarvoor naar WK- voorspeller deel 1, deel 2 en deel 3), maar alleen naar de indeling van de ploegen na de groepswedstrijden.
O ja, België heeft volgens dit Excel-model trouwens ongeveer 10% kans om kampioen te worden!

Kleine lettertjes: als het model klopt tenminste en alle aannames de werkelijkheid goed benaderen.

Indeling achtste finales

Bij dit EK gaan de nummers 1 en 2 automatisch over naar de knock-out-fase. Van de 6 landen, die bij de groepswedstrijden op plaats 3 eindigen, gaan de beste 4 verder.
Voor de indeling van deze 4 is een ingewikkeld schema bedacht (zie hiernaast; bron is WIKIPEDIA)).
De indeling is dus afhankelijk van de groepen waaruit de 3e plaatsen komen (aangeduid met de letters A t/m F).

In het Voorbeeldbestand worden daarom de volgende stappen doorlopen om tot de juiste indeling van de achtste finales te komen (zie tabblad Groepsfase):

in de cellen X4 t/m X9 worden de punten van de landen opgehaald die derde zijn geworden in de 6 groepen
met behulp van de functie GROOTSTE (waarbij ook kan worden aangegeven of je echt de grootste wilt hebben of de een na grootste enz) worden de beste 4 resultaten opgehaald in de cellen X12 t/m X15
in de cellen Y12 t/m Y15 worden dan met behulp van de functies INDEX en VERGELIJKEN de corresponderende groepsletters opgehaald
nu de moeilijkste stap: in cel Y16 (met de naam Serie3) moeten de 4 groepsletters gesorteerd worden.
Om de functie GROOTSTE te kunnen gebruiken moeten we letters eerst vertalen naar getallen met de functie CODE (een A wordt dan 65, een B 66 etc).
Als we via GROOTSTE de getallen in de juiste volgorde hebben staan, wordt het getal weer terugvertaald naar een letter met de functie TEKEN. De letters worden aan elkaar ‘geplakt’ met behulp van het &-teken.
We moeten Excel nog wel aangeven, dat in de formule de functie GROOTSTE op een serie cellen moet worden losgelaten; daarom is de invoer van de formule niet met Enter afgesloten maar met Ctrl-Shift-Enter (we hebben dus een zogenaamde CSE-formule ingevoerd; zie ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT).
Het resultaat in cel Y16 (=Serie3) wordt in het tabblad Finales gebruikt om de juiste tegenstander van de poule-winnaars op te halen; bijvoorbeeld in cel B9 moet de tegenstander van de winnaar van groep B komen:
=VERT.ZOEKEN(Serie3;Series3;3;ONWAAR)
Series3 is de naam van de cellen met het Wikipedia-overzicht in het tabblad Groepsfase; de tegenstander van de B-winnaar staat in de derde kolom.

Verwerken resultaten

De eerste wedstrijden zijn ondertussen gespeeld. Geef in kolom M van het tabblad Groepsfase de juiste punten aan de betreffende ploeg.

Wanneer je de resultaten op deze manier in het spreadsheet verwerkt, blijken de kansen voor Duitsland en Frankrijk dankzij hun gewonnen wedstrijden al iets te zijn toegenomen.

Tabellen (deel 2)

23 mei 2016ExcelGegevensvalidatie, Indirect, Tabel, VERT.ZOEKENGijs Verbruggen

In mijn vorige artikel (Kunst en Excel) heb ik 11 voordelen van het gebruik van tabellen in Excel besproken.

Op een forum voor Excel-experts heb ik gevraagd of er ook nadelen zijn aan tabellen.

Daar zijn 2 reacties op gekomen, waarvan de tweede aanleiding is geweest voor dit artikel.

Nadelen Tabellen in Excel

De eerste reactie ging over de naam, die de tabel automatisch krijgt (Tabel1, de volgende Tabel2 etcetera). De naam moet dan nog aangepast worden om zodoende een zinvolle beschrijving te krijgen; tsja, hier zie ik zo gauw geen echt probleem (en ook geen oplossing).
Maar ook werd aangegeven, dat wanneer de tabel uit de werkmap wordt verwijderd de naam nog wel actief zou blijven. Zover ik kan nagaan, geldt dat wel voor cellen (of celbereiken) die een naam krijgen en daarna worden verwijderd, maar niet voor tabellen!

In de tweede reactie werd aangegeven, dat tabellen niet bij een gegevensvalidatie kunnen worden gebruikt.
Ja, daar heeft Microsoft een steek laten vallen: standaard kan alleen een celbereik als keuzelijst worden opgegeven. Wel is het mogelijk om een ‘gewone’ naam als bron daarvoor op te geven, maar dan moet je in de tabel de gewenste reeks een aparte naam geven en er voor zorgen, dat bij uitbreiding van de lijst deze naam ook wordt aangepast.
Maar voor dit probleem hebben we gelukkig wel een oplossing!

Gegevensvalidatie

In het Voorbeeldbestand heb ik een tabblad Param opgenomen, waar 2 tabellen staan:

in het eerste blok staan afkortingen en omschrijvingen voor alle afdelingen die kunnen worden gebruikt.
De tabel heeft de naam tblAfd gekregen (hoe je een tabel maakt en de naam kunt aanpassen is in het artikel Kunst en Excel besproken).
daarnaast staat een overzicht van de mogelijke kostensoorten; deze tabel heeft de naam tblKst

Deze twee tabellen gaan we gebruiken voor een gegevensvalidatie:

de invoer in cel B3 van het tabblad GegVal moet beperkt worden tot die drie afkortingen voor de afdelingen, die in Param zijn opgenomen (dus D, C en O).
kies de menuoptie Gegevens en dan binnen het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie:
in het vervolgscherm kiezen we bij Toestaan de optie Lijst.
dan komt de optie Bron tevoorschijn; klik op en selecteer dan de cellen B3 tot en met B5 in het tabblad Param.
Klik OK. Aan cel B3 in GegVal is nu een keuzepijltje toegevoegd; als je daar op klikt kun je één van de drie mogelijkheden kiezen.

Op dezelfde manier kan ook de invoer in cel C3 beperkt worden tot de drie mogelijke kostensoorten uit Param (de cellen E3 t/m E5).

Door de cellen B3 en C3 te kopiëren kan de gegevensvalidatie uitgebreid worden naar andere cellen.

Een groot nadeel is, dat als er nieuwe opties bijkomen voor de afdelingen en/of de kostensoorten de betreffende gegevensvalidaties moeten worden aangepast.

Gegevensvalidatie met Tabellen

In plaats van een verwijzing naar de cellen B3 t/m B5 in het tabblad Param (als bron geven we dan op =Param!$B$3:$B$5) zouden we de tabelverwijzing =tblAfd[Afd] willen gebruiken (dus de kolom Afd in de tabel tblAfd).
Als we dat doen krijgen we een foutmelding, maar die kunnen we omzeilen door de functie INDIRECT te gebruiken.

Deze functie zet een tekst om in een Excel-verwijzing. Als voorbeeld: INDIRECT(“B”&A1) geeft een verwijzing naar de cel B1 als A1 de waarde 1 bevat, maar verwijst naar B2 als de waarde van A1 gelijk is aan 2 etc.

Door als bron voor de gegevensvalidatie in te voeren =INDIRECT(“tblAfd[Afd]”) krijgen we toch het gewenste resultaat.

Bekijk in het tabblad Boekingen1 de gegevensvalidatie van de cellen in kolom C en E. Deze maken gebruik van deze methode.

Voor de opvoer van boekingen in dit tabblad is ook gebruik gemaakt van een tabel. Een voordeel is dat bij de opvoer van een nieuwe regel (record) onderaan de gegevensvalidatie ook direct wordt overgenomen.
Hetzelfde geldt voor de formules in de kolommen D en F, waar met behulp van VERT.ZOEKEN de omschrijving van de gekozen afdeling of kostensoort wordt opgehaald.

In het tabblad Boekingen2 worden alleen de omschrijvingen van de afdelingen en de kostensoorten ingevoerd. Ook de invoer hiervan is via gegevensvalidatie ´beveiligd´.

Wanneer er nu een afdeling aan het tabblad Param wordt toegevoegd (bijvoorbeeld H met als omschrijving HRM) dan zullen alle gegevensvalidaties automatisch deze mogelijkheid meenemen. Uiteraard geldt dit ook voor de uitbreiding van het aantal kostensoorten.

LET OP: in het tabblad GegVal wordt HRM niet als keuzemogelijkheid getoond, omdat daar de gegevensvalidatie ‘hard’ aan de cellen B3 t/m B5 van Param is gekoppeld.

MS-Query: een alternatief voor Vert.Zoeken

19 maart 2016ExcelExterne gegevens, MS-Query, VERT.ZOEKENGijs Verbruggen

Ik had in de kop van dit artikel diverse bijvoeglijke naamwoorden willen gebruiken (stabiel, minder foutgevoelig, flexibel) maar dan werd deze te lang.
Ja, waar Vert.Zoeken, ondanks zijn wijdverbreid gebruik, zijn beperkingen en valkuilen kent (google maar eens op Vert.Zoeken) is een Excel-systeem gebaseerd op Microsoft Query (een standaard-onderdeel van Excel) veel robuuster.
De leercurve is waarschijnlijk iets groter dan bij een gewone Excel-functie, maar je krijgt er wel een krachtig hulpmiddel in je Excel-gereedschapskist voor terug!

Hieronder belanden we via het onderwerp Externe gegevens al snel bij MS-Query.

Externe gegevens

De meeste rapportage-afdelingen in bedrijven zijn voor de aanlevering van gegevens afhankelijk van andere afdelingen, die op hun beurt weer verschillende systemen gebruiken.
Wat gebeurt er dan vaak in de stresserige maand-afsluiting: blokjes data uit diverse overzichten worden gekopieerd en in eigen sheets geplakt met alle risico’s van dien.

Met behulp van de optie Externe gegevens ophalen (zie ook mijn artikel Excel en het WorldWideWeb) kan dit beter gestructureerd worden, waardoor een stabieler rapportage-systeem ontstaat.
Laten we eens een voorbeeld nemen. We krijgen periodiek een overzicht aangeleverd met daarin de verkoopdatum, een productcode en het betreffende bedrag (zie het tabblad Basis1 in het Voorbeeldbestand). Het gebied met gegevens heeft de naam Omzet gekregen.
In plaats van Kopiëren en Plakken gaan we deze gegevens op een alternatieve manier ophalen:

maak een nieuwe werkmap aan (handigste: gebruik Ctrl-N)
selecteer cel B2 (klik met de muis in die cel)
kies in de menutab Gegevens in het blok Ext. geg. ophalen de optie Best. Verbind.

Tsja, naamgeving: we gaan geen bestaande verbinding gebruiken, maar kiezen toch deze optie.
Linksonder in de pop-up kiezen we de optie Bladeren naar meer …
zoek in het directory-overzicht het bestand MSQ_basis.xlsx op (eerst downloaden natuurlijk) en klik Openen
in het tussenscherm

zien we de namen van de drie tabbladen uit het Voorbeeldbestand (met een $-teken als toevoeging) en de naam van het gebied met gegevens. Klik op Omzet en op OK.
Ook in het volgende tussenscherm Gegevens importeren kiezen we OK.
Et voilà: de gegevens zijn opgehaald en, overzichtelijk opgemaakt, in een Excel-tabel geplaatst.
Doe het voorgaande vanaf punt 2 nog een keer, maar plaats de cursor in cel F2 en selecteer als tabel Basis1$.
De resultaten zullen hetzelfde zijn, maar of dat ook zo blijft?
Sla de werkmap met de twee, hiervoor aangemaakte, tabellen op met de naam Resultaat.xlsx en sluit deze.
Open MSQ_basis.xlsx en voeg op het tabblad Basis1 een regel toe en sluit dit bestand weer (natuurlijk de wijzigingen opslaan!).
Open het bestand Resultaat.xlsx en ververs de 2 tabellen (klik met de muis rechts en kies Vernieuwen).
De eerste tabel zal niet veranderen (de grootte van het gebied Omzet is niet veranderd) terwijl de tweede tabel ook de mutatie in de bron weergeeft.

NB Externe gegevens en ook MS-Query zijn ‘stiefkinderen’ van Microsoft: deze functies ondersteunen het gebruik van Excel-tabellen als bron niet volledig. De namen van dit soort tabellen zul je dan ook niet in het selectiescherm tegenkomen.

LET OP1 in de praktijk is het aan te bevelen om bij de selectie altijd de bladnamen te kiezen (en dus geen zelf gedefinieerde namen). Consequentie daarvan is dat ieder blok bij elkaar horende gegevens op een apart tabblad moeten staan.

LET OP2 Excel zal bij het ophalen van gegevens lege kolommen links en rechts en lege rijen boven en onder weglaten. Maar … dit werkt niet altijd goed, dus het is verstandig om een blok gegevens in cel A1 te laten beginnen.

Gegevens ‘verrijken’

Maar in een rapportage willen we meestal codes, zoals hiervoor bij het product, vermijden of aanvullen met een omschrijving.

We gaan onze geïmporteerde gegevens ‘verrijken’ met aanvullende gegevens. Daartoe plaatsen we ergens in de werkmap een overzicht van de gebruikte codes met daarnaast een omschrijving (en eventueel nog andere gegevens); ik heb dit overzicht de naam PrCd gegeven.

Naast de tabel, die we geïmporteerd hebben, zetten we in de eerste rij een opzoek-formule; in cel E3 komt dan de formule
=VERT.ZOEKEN([@ProdCode];PrCd;2;ONWAAR)

Aangezien we te maken hebben met een tabel, zal Excel deze formule direct naar beneden naar alle regels kopiëren. Nog mooier: wijzigt het aantal regels in de bron-sheet en we vernieuwen de tabel dan wordt ook de kolom met onze formule automatisch aangepast!

Nog een verticaal-zoeken er naast en we weten ook bij welk hoofdproduct de regel hoort:

Ook al worden de kolommen van de tabel verplaatst (bijvoorbeeld de kolom HfdProd verslepen naar de positie van bedrag), dan blijft deze indeling gehandhaafd bij het Vernieuwen van de tabel.

Door een draaitabel ‘los te laten’ op de tabel kan snel een overzicht per Fiets en Brommer en/of per maand gemaakt worden. Na aanlevering van nieuwe brongegevens (bestand met dezelfde naam in dezelfde directory plaatsen) hoeven dan alleen de Excel-tabel en de draaitabel vernieuwd te worden!

MS-Query

Hoewel op de vorige manier het rapportageproces al flink gestructureerd en geautomatiseerd is, levert het gebruik van Microsoft Query nog verbeteringen en extra mogelijkheden op.

We gaan uit van hetzelfde Voorbeeldbestand maar gaan de tabbladen Basis2 (qua opzet gelijk aan Basis1, maar met meer records) en ProdCd gebruiken. Dit laatste werkblad bevat het totale productenpalet met codes en omschrijvingen.

Voer de volgende handelingen uit:

maak een nieuwe werkmap aan (Ctrl-N)
selecteer cel B2
kies in de menutab Gegevens in het blok Ext. geg. ophalen de optie Van andere bronnen en klik op Van MS Query
nu kunnen we aangeven welke soort bron we gaan gebruiken; we kiezen in dit voorbeeld Excel-files
Welke mogelijkheden er zijn, hangt af van de individuele Excel-implementatie.
LET OP zorg dat de onderste optie (Query’s maken/bewerken) uitgevinkt is; de Wizard is meestal te beperkt in zijn mogelijkheden.
In de volgende stap zoeken we het Voorbeeldbestand MSQ_Basis.xlsx weer op, waarna de volgende pop-up verschijnt:
NB is alleen Omzet zichtbaar? Zorg via Opties dat ook de systeemtabellen staan aangevinkt.
Voeg Basis2$ en ProdCd$ toe (via dubbelklikken) en kies Sluiten.
Nu komen we in het echte MS-Query-scherm (gebruikers van Access zal dit bekend voorkomen)
Voordat we de query gaan maken moeten we de relatie tussen de twee tabellen aangeven; in dit geval wordt het verband tussen de 2 tabellen gevormd door de ProdCode: klik op ProdCode in de tabel Basis2$, hou de muis-knop ingedrukt en sleep naar ProdCode in de andere tabel en laat dan de muisknop los. Als alles goed is gegaan staat er een lijntje tussen de 2 velden.
Nu gaan we aangeven welke velden we in het resultaat willen hebben: dubbelklik in de tabel Basis2$ op Datum en op ProdCode. Idem in de tabel ProdCd$ op ProdOmschr en HfdProd en als laatste in de tabel Basis2$ op Bedrag.
Het resultaat van de query is direct zichtbaar (zo niet: druk op de button met het uitroepteken en de 2 pijltjes):
Wil je weten hoe deze query met SQL zou moeten worden gemaakt? Druk op de button SQL.
Nu moet het resultaat nog in Excel terecht komen; klik op het ‘deurtje’ (Gegevens retourneren) en klik in het tussenscherm, dat verschijnt, op OK.

That’s it! De MS-Query is klaar, de resultaten overzichtelijk gerangschikt en met één muisklik (klik rechts in de tabel en kies Vernieuwen; sorry, dus 2 klikken) is het overzicht geactualiseerd. Eventueel aangepaste opmaak (waarom Excel in dit geval ook een tijd laat zien bij de datum?) blijft gehandhaafd.

Wanneer je niet geïnteresseerd bent in een detailoverzicht kun je natuurlijk ook direct een draaitabel maken van de query-resultaten: nadat je MS-Query ‘via de achterdeur’ hebt verlaten (stap 10) kies je in het tussenscherm de optie Draaitabelrapport.

Zonder verdere handelingen (alleen de draaitabel Vernieuwen) worden na aanlevering van nieuwe brongegevens de resultaten zichtbaar.

Query aanpassen

Af en toe zal het nodig zijn om de query aan te passen. Waar en hoe de aanpassing moet worden doorgevoerd is helaas niet altijd even makkelijk te vinden en consistent.
Soms is het makkelijker en sneller om de query gewoon opnieuw te maken!

Heb je detail-gegevens binnengehaald in een tabel en wil je de query nog eens bekijken of aanpassen: klik rechts in de tabel en kies bij Tabel de optie Query bewerken.

Heb je direct een draaitabel aangemaakt: klik in de draaitabel, kies op het tabblad Hulpmiddelen voor draaitabellen binnen Opties bij Gegevens de optie Vern. en daarbinnen Eigenschappen van verbinding.
Nog niet klaar: kies het tabblad Definitie en nu kun je de Query bewerken.

In het algemeen kunnen alle koppelingen naar MS-Query bereikt worden via de optie Verbindingen in de menutab Gegevens.

Als je daarop klikt krijg je alle verbindingen te zien, selecteer de gewenste en kies Eigenschappen.

Vooral het Definitie-blad is van belang: niet alleen kun je daar naar Query bewerken maar ook kun je de naam van de bron en de directory aanpassen.

Heb je genoeg kennis van SQL: ook die is aan te passen (in het blok Tekst van opdracht).

Waarschuwing

LET OP LET OP LET OP LET OP LET OP LET OP LET OP LET OP LET OP

De relatie die in stap 6 hierboven is gelegd, is een zogenaamde INNER JOIN. Dat betekent dat alleen die records uit de tabellen worden meegenomen, waarbij de relatievelden (in dit geval de ProdCode) in allebei de tabellen voorkomen. Zou in Basis2 ook een record voorkomen met bijvoorbeeld een ProdCode gelijk aan 121 dan wordt die niet getoond in het resultaat.

Om dit te voorkomen moet je een OUTER JOIN/relatie aanmaken:

kies op één van bovengenoemde manieren de optie Query bewerken
dubbelklik op het relatielijntje
verander optie 1 in optie 2 en kies Toevoegen:
NB misschien moet je 3 kiezen; dit in het geval de relatie van ‘rechts naar links’ getekend is
Na het sluiten van het vorige scherm is de lijn veranderd in een pijl (van Basis2$ naar ProdCd$)
ga ‘via de achterdeur’ terug naar Excel.

Dit was (heel erg in het kort) uitleg over het gebruik van MS-Query. Nog vragen, maar ook voor opmerkingen: neem contact op met G-Info.

SOMPRODUCT: meer dan SOM en PRODUCT

29 januari 2016ExcelCSE, ISGETAL, Som, SOMPRODUCT, VERT.ZOEKEN, VIND.ALLESGijs Verbruggen

SOMPRODUCT: één van de vele functies van Excel. Waar kan die voor gebruikt worden?
In dit artikel zal ik laten zien dat het meer kan zijn dan een optelling van getallen nadat die vermenigvuldigd zijn, zoals de formule hiernaast weergeeft.
Nog sterker: ik denk dat in de praktijk het (meer oneigenlijke) gebruik van de functie vaker wordt toegepast dan zoals de functie oorspronkelijk bedoeld was.

Standaard-gebruik van SOMPRODUCT

Laten we als simpel voorbeeld een bedrijfje nemen met 4 producten met verschillende prijzen. Tijdens de verkoop in de winkel wordt het aantal verkochte producten netjes bijgehouden. Op het einde van de dag willen we natuurlijk wel snel weten wat de omzet is geweest.
In het Voorbeeldbestand in het tabblad Vb1 heb ik de fictieve verkoopaantallen opgenomen. Om de omzet te bepalen moeten we de aantallen nog vermenigvuldigen met de bijbehorende prijs en alle bedragen optellen.
De standaardmethode is dan als volgt:

Voeg een kolom toe waar de prijs van het product komt te staan. Dit kan makkelijk met een zoek-formule in cel G4 (in het voorbeeld staan Product en Aantal in de kolommen E en F):
=VERT.ZOEKEN(E4;B$4:$C$7;2;ONWAAR)
Kopieer deze formule naar beneden.
De omzet komt in kolom H: in iedere regel wordt de cel in kolom F vermenigvuldigd met de cel in G.
Dan onderaan in H27 een SOM-formule en we zijn klaar.

Met de Excel-functie SOMPRODUCT kunnen we de stappen 2 en 3 vervangen door één formule (in cel H28):
=SOMPRODUCT(F4:F26;G4:G26)

Wat doet deze formule: de inhoud van cel F4 wordt vermenigvuldigd met die in cel G4, F5 met G5 enzovoort tot en met de vermenigvuldiging van F26 en G26; de resultaten van de vermenigvuldigingen (PRODUCT) worden opgeteld (SOM).

NB bij het ‘nette’ gebruik van SOMPRODUCT worden de arrays gescheiden door een ; (punt-komma). Het is echter ook toegestaan om de matrices met elkaar te vermenigvuldigen. De formule wordt dan:
=SOMPRODUCT(F4:F26*G4:G26) (zie cel H29)
We zullen hieronder zien, dat deze vorm allerlei mogelijkheden biedt voor filtering van resultaten.
Een ander voordeel is, dat de formule geëvalueerd kan worden:

selecteer cel H29
kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
er verschijnt een nieuw scherm:
Klik op Evalueren en Excel zal de onderstreepte bewerking gaan uitvoeren (in dit geval de vermenigvuldiging van de 2 arrays).
Je ziet dan de tussenresultaten (zoals we zelf ook hadden berekend in de cellen H4 t/m H26) omgeven door accolades ten teken dat het resultaat ook weer een array/matrix is. Bij de volgende evaluatiestap worden de tussenresultaten opgeteld.

Met wat creativiteit kan de standaard-formule ook nog anders gebruikt worden.
Stel dat we in de winkel niet de aantallen, maar de omzet bijhouden en we willen weten hoeveel stuks er over de toonbank zijn gegaan, dan doen we iets vergelijkbaars als hiervoor: een kolom met de prijs toevoegen en daarachter geen vermenigvuldiging maar een deling (omzet/prijs) en dan die aantallen onderaan optellen (zie Voorbeeldbestand in het tabblad Vb1).
Ook dit kan sneller met SOMPRODUCT:
=SOMPRODUCT(K4:K26;1/(L4:L26))

Verbaasd? Weet je nog bij wiskunde: delen door een getal is hetzelfde als vermenigvuldigen met het omgekeerde.
Dus we vermenigvuldigen de omzet (kolom K) met het omgekeerde van de prijs (dus 1 gedeeld door kolom L).

SOMPRODUCT voor gevorderden 1

Maar nu begint het pas leuk te worden!
Nu we ongeveer weten hoe SOMPRODUCT werkt, gaan we nog eens wat andere arrays toevoegen.

In het Voorbeeldbestand heb ik op tabblad Vb2 hetzelfde verkoopoverzicht staan. Waar we hiervoor een totale omzet hebben berekend, gaan we nu de omzet per product uitrekenen zonder gebruik van een hulpkolom.

De formule: =SOMPRODUCT(($E$4:$E$26=I4)*($F$4:$F$26)*($G$4:$G$26)) in cel J4 volstaat om het resultaat 1.590 te berekenen.
De laatste 2 matrices kennen we al van de totalen-berekening (nu absoluut gemaakt zodat de formule naar beneden naar de andere drie producten kan worden gekopieerd).
Met de eerste array is iets ‘geks’ aan de hand: we gebruiken niet de inhoud zelf van de cellen E4 t/m E26 maar het resultaat van een vergelijking met cel I4: als de inhoud van cel E4 gelijk is aan de inhoud van cel I4 dan wordt het resultaat WAAR anders ONWAAR.
Voor Excel is WAAR gelijk aan 1 en ONWAAR gelijk aan 0; dus bij vermenigvuldigen wordt alleen de omzet meegeteld als de vergelijking met I4 WAAR is.

Bekijk met Formule evalueren de tussenstappen: ……
etc

SOMPRODUCT voor gevorderden 2

Maar we hoeven het natuurlijk niet bij één filter/selectie te houden.
In het Voorbeeldbestand op het tabblad Vb3 staat een iets uitgebreider omzetoverzicht. Aangezien we nu ook informatie over de regio hebben kunnen we daar ook op selecteren/filteren bij het berekenen van de totale omzet:
in cel H4 komt dan =SOMPRODUCT(($B$3:$B$1002=$H$2)*($C$3:$C$1002=$H$3)*$E$3:$E$1002)

Op dezelfde manier turven we ook het aantal keren, dat er voor de regio Noord het product Een is verkocht: =SOMPRODUCT(($B$3:$B$1002=$H$2)*($C$3:$C$1002=$H$3))

Tik in cel H2 Zuid in en de resultaten staan in de cellen H4 en H5!

Voor Draaitabel-adepten (net als ik!) is dit allemaal spielerei en moeilijk gedoe: met 8 muisklikken (en nog een paar extra om de opmaak te regelen) heb je al een totaaloverzicht met alle gewenste uitsplitsingen (zie tabblad Vb3).
Maar ik moet toegeven dat de mogelijkheden van SOMPRODUCT soms verder gaan: je kunt de formules op iedere willekeurige plaats gebruiken en bent dus niet gebonden aan het draaitabel-stramien.
En ook de filteringsmogelijkheden gaan verder:

In het omzetoverzicht is een verkoopdatum opgenomen; door een juiste filtering bepalen we de totalen per maand (ik weet het: met groeperen kan dat ook simpel in een draaitabel!). De formule voor het aantal wordt dan:
=SOMPRODUCT(1*(MAAND($D$3:$D$1002)=$H$7))

LET OP omdat we maar 1 selectie/filtering hebben, hebben we dus ook maar 1 array met daarin de waardes WAAR en ONWAAR. Bij de vorige voorbeelden werden die door Excel naar 1 en 0 vertaald bij de vermenigvuldiging. In dit geval forceren we een berekening door de waarden van de array met 1 te vermenigvuldigen. Een andere berekening is 0 erbij optellen (zie cel I9) of om het tegenovergestelde ervan te pakken (min-teken ervoor zetten; is hetzelfde als vermenigvuldigen met -1; zie cel J9). Doe dat laatste 2x om het juiste teken te krijgen!
Bekijk de tussenstappen met Formule evalueren.

In het tabblad Vb3 zijn nog andere voorbeelden opgenomen, die met een draaitabel niet of moeizaam kunnen worden opgelost. Via de juiste formules is het ook mogelijk om op stukken tekst te filteren. In cel H12 staat:
=SOMPRODUCT(ISGETAL(VIND.ALLES(H11;$B$3:$B$1002))*$E$3:$E$1002)

Als de tekst uit H11 voorkomt in de cellen in kolom B (VIND.ALLES levert dan een getal op, namelijk de positie waar de tekst gevonden is) dan levert ISGETAL de waarde WAAR op, anders ONWAAR. Bij de vermenigvuldiging met de waardes uit kolom E worden deze automatisch vertaald in 1 en 0. Ook hier: bekijk de tussenstappen met Formule evalueren.

LET OP de functie VIND.ALLES is hoofdletter-gevoelig.

SOM voor gevorderden

Voor de liefhebbers: de eigenschap van SOMPRODUCT, dat berekeningen worden uitgevoerd op reeksen/arrays/matrices kunnen we ook toepassen bij veel ‘gewone’ Excel-functies, zoals SOM.

In I16 wordt SOM gebruikt ipv SOMPRODUCT. Maar om Excel te laten weten, dat er een matrix-berekening moet worden uitgevoerd moet je na het invoeren van de formule NIET op Enter drukken, maar op Ctrl-Shift-Enter (soms afgekort als CSE-invoer).
Excel plaatst dan automatisch accolades rond de formule.

LET OP alleen CSE-invoer kan gebruikt worden voor matrix-berekeningen; zelf plaatsen van accolades werkt niet!

SOMPRODUCT voor zeer gevorderden

Ik kwam deze week toevallig een voorbeeld tegen van SOMPRODUCT waarbij ik toch even op mijn hoofd moest krabben.
Ik heb de formule aangepast voor de Nederlandse versie van Excel en nog wat opmaak toegevoegd:

=”06 “&TEKST(SOMPRODUCT(10^(8-RIJ(INDIRECT(“1:8″)))*ZOEKEN(CODE(HOOFDLETTERS(DEEL(LINKS(SPATIES.WISSEN(SUBSTITUEREN(C3;” “;””))&HERHALING(“0”;8);8);RIJ(INDIRECT(“1:8″));1)));{48;49;50;51;52;53;54;55;56;57;64;68;71;74;77;80;84;87};{0;1;2;3;4;5;6;7;8;9;2;3;4;5;6;7;8;9}));”000 000 00”)

Deze formule zet een telefoonnummer in letters (in reclames werd je er ooit mee dood gegooid; nu zie ik ze niet meer) om naar een echt telefoonnummer:
dus 06 DIZ PK ROI wordt 06 349 757 64.

Gebruik Formule evalueren om de formule te doorgronden op het tabblad Vb4 van het Voorbeeldbestand.

Kom je er niet uit: neem contact op met G-Info.

Weekdagen en weeknummers

18 januari 2016Access, ExcelDatePart, Format, Gegevensvalidatie, Kiezen, Notatie, Opmaak, VERT.ZOEKEN, Weekdag, WeeknummerGijs Verbruggen

Eén van de eerste artikelen op de site van G-Info ging over datums en de mogelijkheden van de diverse opmaak-opties (klik hier voor het betreffende artikel).

Vorige week kreeg ik een vraag over weekdagen en weeknummers in Access. Toen bleek, dat het probleem niet met opmaak was op te lossen, viel het me weer op, hoe ingewikkeld het kan zijn om dit soort vraagstukken met behulp van formules te tackelen.
Ook de (soms) grote verschillen tussen Excel en Access maken het er niet makkelijker op (hoezo, allebei een onderdeel van Microsoft Office!) .

Daarom maar eens wat mogelijkheden (en onmogelijkheden) op een rijtje gezet.

Opmaak in Excel

Zoals gezegd heb ik daar in een eerder artikel al eens over geschreven.
In het Voorbeeldbestand heb ik een overzicht gemaakt met de dagen van de laatste week van 2016 en de eerste 2 van 2016.
De datums staan in kolom F; in de kolommen G t/m J staan verwijzingen naar de dagen in kolom F, maar met behulp van celopmaak (Ctrl-1 indrukken) is de lay-out van de cellen veranderd.

Voordeel hiervan is, dat makkelijk en snel de opmaak aangepast kan worden aan je wensen.
Nadeel is echter, dat je niet rechtstreeks formules of filters op deze cellen kunt los laten; de inhoud van die cellen blijft een datum (intern voor Excel een getal). Ook is het niet mogelijk om op deze cellen een Voorwaardelijke opmaak toe te passen.

NB wanneer je een Filter aanbrengt op het overzicht zul je zien, dat de filtermogelijkheden voor de kolommen F t/m J allemaal hetzelfde zijn: Excel ziet, dat het datums betreft en geeft daarom de mogelijkheden om te filteren op jaar, maand of dag. Het is NIET mogelijk om te filteren op bijvoorbeeld maandag.

Experimenteer met de aangepaste opmaak via Celeigenschappen (Ctrl-1). Gebruik combinaties van de letters d, m en j; 1 letter, 2 letters etc. Het resultaat zie je direct in het vak Voorbeeld.

Datum-formules in Excel

Wil je kunnen rekenen, filteren, sorteren met de diverse onderdelen van een datum dan voldoet de opmaak niet meer. Je zult dan formules moeten gebruiken, waarmee de datum ‘ontleed’ wordt.

In het Voorbeeldbestand zijn in de kolommen K, L en M formules gebruikt om het jaar, de maand en de dag van een datum op te halen; in cel K4 bijvoorbeeld staat de formule =JAAR(F4).

Niets bijzonders, handig, recht-toe-recht-aan.
Willen we echter de weekdag ophalen (is het een maandag, dinsdag?) , dan blijkt er in Excel geen functie te zijn, die dat rechtsreeks oplevert; wel kunnen we het volgnummer van de dag in de week opvragen met de functie Weekdag.
Daar zit echter een addertje onder het gras: er is in de wereld geen overeenstemming over welk nummer bijvoorbeeld de maandag moet krijgen: 1 zoals in Europa gebruikelijk is of moet het 2 zijn (een nieuwe week begint op zondag)?
Aan de functie Weekdag moet dus altijd kenbaar worden gemaakt wat de eerste dag van de week is (als je dat niet meegeeft, dan wordt de standaard genomen en krijgt zondag volgnummer 1).
In cel N4 staat de formule =WEEKDAG(F4;KzWkDag)

Er wordt dus gezocht naar de weekdag, die hoort bij de datum in cel F4, waarbij als Type_getal de waarde 2 wordt meegegeven (maandag krijgt volgnummer 1).
Type_getal verwijst naar de cel met de naam KzWkDag (cel C3). Via Gegevensvalidatie kunnen daar alleen de voor deze functie toegestane waarden 1, 2, 3 en 11 t/m 17 ingevoerd worden.
Met VERT.ZOEKEN is in cel D3 te zien wat een bepaalde waarde inhoudt. Op het tabblad Parameters is de bron voor de Gegevensvalidatie en het verticaal zoeken te vinden in de kolommen B en C.

NB met een formule kunnen we dus het volgnummer van een weekdag afleiden, willen we echter een omschrijving dan wordt het iets ingewikkelder.
In cel Q4 staat een voorbeeld:
=KIEZEN(N4;”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)
Let wel op: de volgorde van de dagen is afhankelijk van het Type_getal, dat in cel N4 is gebruikt!

Met opmaak is het niet mogelijk om in Excel het weeknummer te zien; wel is daar een functie voor. In het Voorbeeldbestand wordt die in kolom O gebruikt.
In cel O4 komt dan de formule =WEEKNUMMER(F4;KzWkNr)

Ook deze functie kent een 2e paramater (het Type_resultaat). Welke keuze hier gemaakt moet worden is nog iets ingewikkelder dan bij Weekdag. Want niet alleen is het van belang op welke dag een nieuwe week begint, ook moeten we aangeven wanneer we met weeknummer 1 beginnen.
Voor dat laatste onderscheidt Excel 2 systemen: bij Systeem 1 is Nieuwjaar het begin van week 1, bij Systeem 2 (het Europese systeem) begint week 1 met die week, waarin de eerste donderdag valt (en de week begint dan automatisch op maandag).

Om Systeem 2 te kiezen moet als 2e paramater voor Weeknummer het getal 21 meegegeven worden.

Opmaak in Access

Net als in Excel kunnen we in Access de datum “in stukken knippen” door een opmaak daaraan mee te geven.
In een query, die gebaseerd is op een tabel met een veld Datum, kunnen we bijvoorbeeld een nieuw veld toevoegen (met de naam JrF) om het jaar af te leiden:
JrF: Format([datum];”yyyy”)

LET OP Access verwacht Engelse functienamen en ook de Engelse afkorting voor het jaar.

We kunnen ook minder letters aan de functie meegeven, maar het resultaat is soms iets anders dan in Excel:
y: dit levert het volgnummer van de dag in het jaar
yy: het jaar in 2 cijfers
yyy: het jaar in 2 cijfers en direct daarachter het volgnummer van de dag in het jaar (dus een combinatie van yy en y)

Voor het maandnummer kan een vergelijkbare formule gebruikt worden:
MndF: Format([datum];”m”)
mm: het maandnummer altijd in 2 cijfers
mmm: de afkorting voor de maand (bijvoorbeeld jan)
mmmm: een volledige maand-aanduiding (bijvoorbeeld januari)

Voor de dag van de maand gebruiken we dan:
DagF: Format([datum];”d”)
dd: het dagnummer altijd in 2 cijfers
ddd: de afkorting voor de dag van de week (bijvoorbeeld ma)
dddd: een volledige dag-aanduiding (bijvoorbeeld maandag)

Binnen Access is het op deze manier ook mogelijk om het kwartaalnummer automatisch te genereren:
KwF: Format([datum];”q”)

Ook kunnen we de w als parameter gebruiken, maar ook nu dienen we nog extra parameters mee te geven:
WkF: Format([datum];”w”;2)
Deze formule levert het volgnummer van de dag in de week op. Maar net als in Excel moeten we dan wel opgeven op welke dag een nieuwe week begint. Geven we als 3e parameter een 0 (nul) mee, dan worden systeeminstellingen gebruikt, 1 dan begint de week op zondag etc.

Als laatste nog:
Wk2F: Format([datum];”ww”;2;2)
Dit levert het weeknummer in het jaar; net als in Excel is het wel van belang welk systeem daarbij gebruikt moet worden.
Als de 4e parameter 0 (nul) is dan worden de systeeminstellingen gebruikt, 1 dan begint de eerste week op Nieuwjaar, 2 dan is de eerste week afhankelijk van de eerste donderdag en 3 dan is de eerste week die week, die volledig in het nieuwe jaar ligt.

LET OP de functie Format retourneert een tekst; wil je rekenen met de uitkomst hiervan neem dan de waarde van het resultaat: Val(Format([datum];”d”))

Datum-formules in Access

Willen we direct rekenen met de dagen, maanden, weken etc (zonder gebruik te maken van de Val-functie) dan kent Access nog de functie DatePart.

Als we bijvoorbeeld in een query als veld opgeven
JrNr: DatePart([datum];”yyyy”)
dan resulteert dit in een getal met 4 cijfers, dat het jaar aangeeft.

Alle instellingen, die hiervoor bij Access-opmaak zijn toegelicht, zijn hier toepasbaar.
Dus bijvoorbeeld DatePart([datum];”ww”;1;2) levert het weeknummer (als getal) op, waarbij een week begint op zondag en de eerste donderdag bepalend is voor de weeknummer 1.