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.
Mooie vraag: je hebt een heleboel gegevens, maar hoe maak je daar nu informatie van.
Laten we er even eentje maken (zie 
… en de Lijst met draaitabelvelden ingevuld zoals hiernaast. Resultaat: een mooi overzicht van de verdeling van de omzet naar Soort en Product (zie tabblad Basis1):
NB het gemiddelde bedrag hebben we gekregen door het veld Bedrag opnieuw naar het waarden-blok te slepen en de Waardeinstellingen te veranderen van Som naar Gemiddelde.
in het nieuwe tussenvenster wordt automatisch het hele gegevensgebied gekozen en Excel ‘ziet’ dat er een kopregel is. Klik OK.
Stel dat we om een of andere reden niet alle gegevens willen zien maar bijvoorbeeld alleen het bedrag, dat bij de combinatie Soort=Een en Product=P2 hoort, dan kunnen we in Excel natuurlijk gewoon verwijzen naar cel G6 (zie het tabblad Draai in het 
Maar wat gebeurt er als de draaitabel wordt gewijzigd omdat er nieuwe soorten of producten bijkomen of als de structuur van de draaitabel wordt gewijzigd (wissel Soort en Product maar eens om van Rijlabels naar Kolomlabels en vice versa)?
in de menutab Hulpmiddelen voor draaitabellen, die nu tevoorschijn komt, kiezen we de tab Opties. In het vak Draaitabel staat nog een keer Opties: zet daar het vinkje bij DraaitabelOphalen genereren aan. Ga dan opnieuw naar punt 1.
De draaitabel geeft per dag het aantal records en de omzet weer (de automatisch gegenereerde namen Aantal van Datum en Som van Bedrag zijn overschreven door Aantal respectievelijk Subtotaal).
Op basis van de gegevens in tabblad Basis3 is een draaitabel gemaakt met in de Rijlabels de Datum, aan het vak Waarden is nog een keer de Datum toegevoegd en ook het Bedrag.
Wanneer je in het tabblad Formules de Einddatum verandert in bijvoorbeeld 29-2-2016, zal het resultaat daarvan meteen zichtbaar zijn in de draaitabel (na Vernieuwen).