Maandelijks archief: maart 2016

MS-Query: een alternatief voor Vert.Zoeken

Ik had in de kop van dit artikel diverse bijvoeglijke naamwoorden willen gebruiken (stabiel, minder foutgevoelig, flexibel) maar dan werd deze te lang.
MS-QueryJa, 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.
MSQ1Laten 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:

  1. maak een nieuwe werkmap aan (handigste: gebruik Ctrl-N)
  2. selecteer cel B2 (klik met de muis in die cel)
  3. kies in de menutab Gegevens in het blok Ext. geg. ophalen de optie Best. Verbind.
    MSQ2
    Tsja, naamgeving: we gaan geen bestaande verbinding gebruiken, maar kiezen toch deze optie.
  4. Linksonder in de pop-up kiezen we de optie Bladeren naar meer …
  5. zoek in het directory-overzicht het bestand MSQ_basis.xlsx op (eerst downloaden natuurlijk) en klik Openen
  6. in het tussenscherm
    MSQ3
    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.
  7. MSQ4Ook in het volgende tussenscherm Gegevens importeren kiezen we OK.
  8. Et voilà: de gegevens zijn opgehaald en, overzichtelijk opgemaakt, in een Excel-tabel geplaatst.
  9. Doe het voorgaande vanaf punt 2 nog een keer, maar plaats de cursor in cel F2 en selecteer als tabel Basis1$.
  10. 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.

MSQ5We 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:
MSQ6

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:

  1. maak een nieuwe werkmap aan (Ctrl-N)
  2. selecteer cel B2
  3. kies in de menutab Gegevens in het blok Ext. geg. ophalen de optie Van andere bronnen en klik op Van MS QueryMSQ7
  4. nu kunnen we aangeven welke soort bron we gaan gebruiken; we kiezen in dit voorbeeld Excel-files
    MSQ8Welke 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.
  5. In de volgende stap zoeken we het Voorbeeldbestand MSQ_Basis.xlsx weer op, waarna de volgende pop-up verschijnt:
    MSQ9NB is alleen Omzet zichtbaar? Zorg via Opties dat ook de systeemtabellen staan aangevinkt.
    Voeg Basis2$ en ProdCd$ toe (via dubbelklikken) en kies Sluiten.
  6. Nu komen we in het echte MS-Query-scherm (gebruikers van Access zal dit bekend voorkomen)
    MSQ10Voordat 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.
  7. 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.
  8. Het resultaat van de query is direct zichtbaar (zo niet: druk op de button met het uitroepteken en de 2 pijltjes):
    MSQ11
  9. Wil je weten hoe deze query met SQL zou moeten worden gemaakt? Druk op de button SQL.
  10. MSQ12Nu 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.

MSQ13Wanneer 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.

MSQ14Zonder 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.

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

MSQ16Als 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:

  1. kies op één van bovengenoemde manieren de optie Query bewerken
  2. dubbelklik op het relatielijntje
  3. verander optie 1 in optie 2 en kies Toevoegen:
    MSQ17NB misschien moet je 3 kiezen; dit in het geval de relatie van ‘rechts naar links’ getekend is
  4. Na het sluiten van het vorige scherm is de lijn veranderd in een pijl (van Basis2$ naar ProdCd$)
  5. 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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Draaitabel.Ophalen

Draaitabel ophalenMooie vraag: je hebt een heleboel gegevens, maar hoe maak je daar nu informatie van.

Zoals in het plaatje is weergegeven kun je op diverse manieren met de data gaan “stoeien”.  Eén van de meest gehanteerde methodes is het gebruik van draaitabellen. En terecht: menige beroepsgroep zou ontzettend blij zijn met zo’n krachtig, breed inzetbaar hulpmiddel!

Maar draaitabellen kennen (natuurlijk) ook nadelen. Ik kan er nu twee bedenken:

  1. de mogelijkheden en inzetbaarheid zijn ontzettend uitgebreid; waar begin je als je er iets over wilt vertellen!
  2. draaitabellen hebben altijd een vast stramien: als je een verdeling naar product in de rijen hebt staan kun je de volgorde van de producten nog wel makkelijk aanpassen, maar als het eerste resultaat in cel C10 komt dan komt de volgende in cel C11 etc.

Nadeel 1 kunnen we niet met één artikel door G-Info oplossen, maar aan het tweede kunnen we wel wat doen.

Draaitabel maken

Om gegevens uit een draaitabel te kunnen gebruiken moet je natuurlijk wel een draaitabel hebben.
DraaitabelLaten we er even eentje maken (zie Voorbeeldbestand):

  1. in het tabblad Basis1 staat een fictief omzetoverzicht. Per dag ligt de omzet vast, gesplitst naar soort en product.
    De gegevens zijn willekeurig door Excel gegenereerd met de formules van het tabblad Formules en daarna ‘hard’ gemaakt door de cellen te kopiëren en op dezelfde plaats te plakken als Waarden.
  2. selecteer een willekeurige cel in dit overzicht en kies dan de optie Draaitabel in de menutab Invoegen
  3. Excel zal automatisch het totale gebied met gegevens selecteren (in dit geval B2 t/m E1001).
    In het voorbeeld heb ik voor de locatie van de draaitabel een bestaand werkblad gekozen (namelijk Basis1) en de draaitabel op cel H3 geplaatst …
  4. Draaitabel… 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):
    Draai3
    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.

De volgende opmerkingen bij dit overzicht:

  1. de kolomnamen zijn groot waardoor alle kolommen automatisch extra breed worden (nog een nadeel van draaitabellen!).
    Oplossing: klik op zo’n naam en wijzig die in bijvoorbeeld Aantal of Gemid.
    Zie de tweede draaitabel op het tabblad Basis1.
  2. de sortering bij Soort is niet logisch.
    Oplossing1: versleep de diverse items naar de juiste plaats door met de cursor “de rand vast te pakken”
    Oplossing2: maak een eigen sortering aan en pas die toe (zie artikel doorvoeren-en-sorteren)
  3. wanneer de brongegevens uitgebreid worden met een nieuwe regel dan zal de draaitabel niet automatisch veranderen (ook niet na het Vernieuwen daarvan).
    Oplossing1: ga binnen de menutab Hulpmiddelen voor draaitabellen naar Opties en kies dan binnen Gegevens voor Andere Gegevensbron en zorg dat alle gegevens zijn geselecteerd.
    Oplossing2: zorg dat de brongegevens als een Excel-tabel zijn opgeslagen; zie hierna (Excel-tabel en draaitabel)
  4.  qua structuur is het niet ‘netjes’ om basisgegevens en afgeleide overzichten in hetzelfde tabblad weer te geven. In dit geval heb ik het wel even gedaan om snel het resultaat van de draaitabel te kunnen vergelijken met de bron.

Excel-tabel en draaitabel

Twee soorten tabellen, die niet door elkaar gehaald moeten worden!
In het tabblad Basis2 van het Voorbeeldbestand staan dezelfde gegevens als in blad Basis1. Maar ik heb daar een Excel-tabel van gemaakt:

  1. selecteer een willekeurige cel in dit overzicht en kies dan de optie Tabel in de menutab Invoegen
  2. Draaitabelin het nieuwe tussenvenster wordt automatisch het hele gegevensgebied gekozen en Excel ‘ziet’ dat er een kopregel is. Klik OK.
  3. het resultaat is een tabel, waarbij Excel automatisch de regels een ´zebra´-achtergrond geeft en de koppen van filter-knoppen voorziet:Draaitabel
  4. via de menutab Hulpmiddelen voor tabellen is de naam van de tabel gewijzigd van de standaardwaarde Tabel1 naar tblOmzet.

Wanneer we nu een draaitabel hiervan maken (zie tabblad Draai) en we vullen de brongegevens op het tabblad Basis2 aan met een nieuwe regel dan zal de draaitabel automatisch de nieuwe regel meenemen.
Wel nog Vernieuwen kiezen, bijvoorbeeld door rechts te klikken op de draaitabel!

Gegevens van een draaitabel gebruiken

Genoeg over de voorbereidingen; dit artikel zou gaan over het ophalen van gegevens uit een draaitabel.

DraaitabelStel 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 Voorbeeldbestand).
DraaitabelMaar 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)?
Dan staan de gegevens van Een/P2 waarschijnlijk niet meer in dezelfde cel en klopt de verwijzing niet meer. Dit is een veel voorkomende fout in Excel-sheets!

Gelukkig heeft Microsoft daar iets op bedacht (vanaf versie Excel 2007?). Even een voorbeeld:

  1. klik in het tabblad Draai in cel C30
  2. tik het =-teken in en klik dan met de muis op cel M10 en druk op Enter
  3. als in cel C30 nu de formule =M10 staat, moet je nog iets aan de instellingen van de draaitabel veranderen:
    * klik ergens in de draaitabel
    * Draaitabelin 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.
    Wil je deze optie niet gebruiken dan weet je nu dus ook waar je hem uit kunt zetten.
  4. in cel C30 staat nu de formule =DRAAITABEL.OPHALEN(“Som”;$B$3)
    Ofwel: haal het bedrag op dat hoort bij het veld Som (in ons geval is dat eigenlijk het veld Bedrag, maar we hebben Som van Bedrag gewijzigd in Som) uit de draaitabel, die begint in cel B3.
  5. de vorige formule haalt het totaalbedrag op. Voer de vorige procedure eens uit in cel C31 maar klik dan op cel D10 ipv M10. Excel zal dan automatich de volgende formule genereren: =DRAAITABEL.OPHALEN(“Som”;$B$3;”Product”;”P1″)
    Aan het ophalen wordt dus een voorwaarde meegegeven: haal niet alle bedragen op, maar alleen die bedragen waarbij het Product gelijk is aan P1 (let op de aanhalingstekens overal).
    NB1 Dit soort formules kun je ook met de hand intikken, maar het is lastig om dan geen fouten te maken.
    NB2 verwissel Soort en Product en kijk wat de resultaten in C30 en C31 zijn; als het goed is blijven die hetzelfde. We zijn dus niet meer afhankelijk van de structuur van de draaitabel!

Dat in cel D35 de volgende formule staat, zal dan geen verrassing meer zijn:
=DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;”Drie”;”Product”;”P3″)

Maar we gaan nog een stapje verder en maken de formule dynamisch: in cel E35 staat de formule =DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;E33;”Product”;E34)

Bij de voorwaarden staat geen letterlijke tekst meer maar een verwijzing naar de cellen E33 en E34.  Bij het wijzigen van de inhoud van die cellen zal de formule dus ook andere waarden ophalen.

LET OP als je in cel E33 de tekst Vijf intikt levert de formule een foutmelding op! Draaitabel.Ophalen kan alleen maar gegevens ophalen die in de draaitabel voorkomen.

Voorbeeld 2 voor Draaitabel.Ophalen

In tabblad DraaiDatum van het Voorbeeldbestand staat nog een voorbeeld van de functie Draaitabel.Ophalen.

DraaitabelDe 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).

 

Willen we alleen het resultaat van een specifieke dag zien dan gebruiken we weer de functie Draaitabel.Ophalen (zie tabblad DraaiDatum):Draaitabel

Maar omdat de datum 2-1-2015 niet voorkomt krijgen we een foutmelding; in cel G5 staat dan ook een iets uitgebreidere formule:
=ALS.FOUT(DRAAITABEL.OPHALEN(“SubTotaal”;$A$3;”Datum”;G3);0)

Dus als de functie Draaitabel.Ophalen een fout genereert dan wordt het resultaat gelijk aan 0 (nul).

Voorbeeld 3 voor Draaitabel.Ophalen

Een van de vele mogelijkheden van een draaitabel is dat je rubrieken kunt groeperen; helemaal interessant is dit voor datums.

In het tabblad DraaiMnd van het Voorbeeldbestand is een indeling naar Jaar, kwartaal en maand te zien. Hoe is dit gemaakt?
Draai1tabelOp 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.
Maar nu komt het: wanneer je nu in de draaitabel op één van de datums met de muis rechts klikt, krijg je de optie Groeperen: Excel ‘ziet’ dat het datums zijn en heeft al de optie Maanden geselecteerd; klik ook nog op Kwartalen en Jaren en OK.

Standaard zal Excel geen subtotalen voor de nieuwe groepen bepalen: klik rechts op het jaar 2015 en zet de optie Subtotaal Jaren aan; zo ook voor de kwartalen.

Omdat het tabblad Basis3 nog formules bevat, waarbij alle velden willekeurig worden gevuld,  zal het Vernieuwen van de draaitabel ook iedere keer andere resultaten opleveren.

Draai1tabelWanneer 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).

Bekijk nu alle formules die in het tabblad DraaiMnd in het blok G3:I13 staan.
Hopelijk spreken die voor zichzelf! Zo niet: neem contact op met G-Info.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: