Excellerend
Kwartaaltip 2015-4
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 BANK: NL72ABNA0536825491 KVK: 24389967
Draaitabellen IV Draaitabel over meerdere tabbladen In de vorige kwartaaltip besprak ik de mogelijkheid om data van twee tabbladen samen te vatten in één draaitabel. Die manier had wel een paar nadelen, vandaar dat ik op zoek ben gegaan naar een tweede manier. Die wordt in deze kwartaaltip behandeld. Het is wel een wat technischere manier, maar ik probeer het zo gebruiksvriendelijke te vertellen. Microsoft Query In Excel ingebakken zit een subprogramma dat het mogelijk maakt om gegevens naar Excel over te halen vanuit verschillende bronnen. Zo´n bron kan een Accesstabel zijn of een database op een SQL-server, een XML-file of ergens vanaf het web. Microsoft Query maakt het mogelijk om een vraag af te vuren op een tabel met gegevens om die in een (draai-)tabel te presenteren. Je zult dus wel de basale dingen moeten weten van het maken van een query. Ik zal dat ook stap voor stap behandelen, maar wat je mag onthouden is dat het resultaat van een query –in Excel- bestaat uit een tabel met kolommen en rijen met gegevens. En deze kolommen en rijen kunnen samengesteld zijn uit kolommen en rijen vanuit verschillende andere tabellen. Genoeg theorie, op naar de praktijk. Op de tab Gegevens, in de groepering “Ext. geg. ophalen” klik je op de onder ‘Van andere bronnen’ en je kies de onderste optie:
Pagina 1 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
Excel komt dan eerst met een venster waarin je moet aangeven uit wat voor soort tabel je de gegevens haalt. In dit geval een Excelbestand want we hebben de tabbladen ‘Begroot’ en ’Realiteit’ in een Excelbestand staan:
Dubbelklik op ‘Excel files*’ en er verschijnt een irritant klein venster om het Excelbestand in op te zoeken:
Ik ga via het rechter venster naar de map kwartaaltips en selecteer links het bestand waarin de tabbladen staan en klik op OK. Staan de tabbladen in het bestand waarin je aan het werk bent, dan zie je in het midden van je scherm een ander venstertje:
Negeer die maar en klik in het bovenste venster op OK. Excel komt dan met een Wizard Query, stap ‘Kolommen selecteren’:
Pagina 2 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
We zien hierin drie tabbladen staan met een dollarteken er achter. Wanneer je op de + klikt voor ‘begroot$’ zie je ook de vier velden van de tabel die herkend wordt:
Omdat we alle velden willen overhalen, kun je ‘begroot$’ selecteren, en dan in het midden op de knop > klikken zodat de vier kolommen in het rechter scherm te zien zijn. Selecteer hier NIET het tabblad ‘realiteit$’ want dan zet je de gegevens uit twee tabellen naast elkaar, en we willen er één lange tabel van maken. Klik op de knop Volgende. Excel komt dan met een dialoogvenster waarin we een filter kunnen gebruiken om slechts een deel van de data over te halen, bijvoorbeeld records van na een bepaalde datum, of records van een bepaalde verkoper.
Pagina 3 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
In ons geval willen we de hele tabel overhalen en klikken we op Volgende. Excel geeft dan de mogelijkheid om de data te sorteren op meerdere velden. Ook dat doen we hier niet. Klik op Volgende:
We komen dan bij de stap ‘Voltooien’. Vink de optie aan om de query te bewerken in Microsoft Query:
Klik op Voltooien.
Pagina 4 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
Je ziet dan een scherm dat er ongeveer zo uit ziet. Je kunt hier de tabel vanuit Excel in herkennen.
Weliswaar weten we dat dit de cijfers zijn van het blad ‘begroot’, maar uit de data in de tabel zelf kunnen we dat niet afleiden. We zullen dat daarom toevoegen aan de selectie als een aparte kolom en dat gebeurt in (een accent van) de querytaal SQL. Klik hiervoor bovenin het scherm op de knop SQL. De query die gebruikt wordt om de hele tabel over te halen ziet er zo uit:
Wat hierboven staat is in woorden: Selecteer van de tabel op het tabblad begroot de kolom Datum, daarna van het tabblad begroot de kolommen Verkoper, Product en Bedrag. Iedere kolom is van de andere gescheiden door een komma. Er een kolom naast plaatsen gaat door een komma te typen en dan alleen de tekst begroot tussen enkele quotes. Maar ik wil ook de naam “scenario” meegegeven voor deze kolom. De query wordt daarom (zonder lijntje):
Pagina 5 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
De naam van de nieuwe kolom wordt tussen blokhaken neergezet. Wanneer je nu op OK klikt, ziet de query en het voorlopige resultaat er zo uit:
Er is een kolom bijgekomen met de naam “Scenario”, en de inhoud ervan is op iedere regel de tekst “Begroot”. Query uitbreiden met union Klik weer op de knop SQL om de query handmatig aan te passen. Sleep de muis van rechtsonder naar linksboven zodat alles blauw wordt en kopieer dit (Ctrl+C) . Klik rechtsonder en druk op Enter om op een nieuwe regel te typen: “Union” (zonder de dubbele quotes). Geef dan weer een enter en plak te gekopieerde query:
Pagina 6 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
De functie “UNION” plakt de resultaten van de twee queries aan elkaar vast en zorgt ervoor dat het resultaat één tabel is. Vervang nu in de geplakte query overal “Begroot” door “Realiteit”:
Klik op OK. Klik op de knop ‘Gegevens retourneren’:
In Excel zien we nu één tabel met daarin gegevens van het tabblad begroot en van het tabblad realiteit:
De lege regel bovenaan krijg ik hier niet weg, maar wel straks in de draaitabel als je bij de Datums de lege warden er uit filtert. Zet een draaitabel op het resultaat van de query:
Pagina 7 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
In plaats van een cellenbereik zoals $A$3:$G$1500 staat er nu de naam van een query! Dit zorgt ervoor dat als er meerdere regels bijkomen, het bereik voor de draaitabel toch de volledige tabel blijft. Klik op OK. Ik maak de volgende draaitabel (in de klassieke weergave) waarbij opvalt dat we een draaitabelveld “Scenario” hebben met twee waarden: ‘Begroot’ en ‘Realiteit’.
Als ik nu een cel selecteer in de draaitabel zodat de hulptab “Hulpmiddelen voor raaitabellen” verschijnt kun je een “Berekend item…” invoegen:
Pagina 8 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
Geef het berekende item de naam “Winst”, selecteer het veld ‘Scenario’ en dubbelklik eerst op het item ‘Realiteit’, dan op het minteken en dubbelklik dan op ‘Begroot’ zodat in het vak bij Formule de formule Realiteit - Begroot verschijnt.
Klik op OK. Een veld met een berekend item kan niet in het Rapportfiltergebied linksboven geplaatst worden, maar je kunt wel alleen het berekend item selecteren. Plaats het veld Verkoper onder Scenario en je ziet de winst per dag per verkoper:
Pagina 9 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
Maar wat ik eigenlijk wil, is kunnen rapporteren per maand. In een draaitabel kun je datums groeperen door met de rechter muisknop er op te klikken en te kiezen voor ‘Groeperen’. Maar met een berekend item kan dat niet meer. We zullen dus het maandnummer in de tabel moeten krijgen! Query bewerken Om de query te kunnen bewerken, dien je eerst een cel in de tabel (niet de draaitabel!) te selecteren. Op het tabblad Gegevens klik je op ‘Eigenschappen’. Klik dan op de knop bij nummer 1: . Klik op de tab Definitie bij 2 en dan onderaan op de knop Query bij 3. Klik vervolgens de waarschuwing bij 4 weg want we gebruiken de Wizard toch niet.
Pagina 10 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
Omdat de Querytaal Engels is, gaan we een Engelstalige functie toevoegen om vanuit het veld Datum het maandnummer te achterhalen. Typ een , achter [Scenario] en dan de functie Month(Datum) as [Maand]. Doe dit bij zowel de begroting als bij de realiteit.
Het rode onderstreepte komt er bij. Klik op OK en in het resultaat zie je het maandnummer erbij. Klik weer op de knop om naar Excel te gaan.
Ververs de draaitabel om het veld Maand ook te kunnen gebruiken. Excelfunctie Mocht je nu denken dat het toevoegen van zo’n functie teveel werk is, te technisch of het lukt gewoonweg niet, is er dan nog een manier? Ja, de Excelmanier. Ga naar het resultaat van de query:
Ga op regel 1 in de aangrenzende kolom staan en typ “Jaar”. Zodra je een enter geeft, zal Excel kolom G toevoegen aan de tabel. Zet daarna in cel G3 de formule “=Jaar(“ en klik op cel A3 en geef een enter. Pagina 11 van 12
www.excellerend.nl
Excellerend
Kwartaaltip 2015-4
Excel voert de functie =Jaar([@Datum]) door voor alle regels en ook de nieuwe regels als er regels bijkomen op het tabblad Realiteit of Begroting. Het veld Jaar is nu ook te gebruiken in de draaitabel. En dat allemaal vanwege de vraag van Karlijn. Ik ga in 2016 nog één tip wijden aan draaitabellen, en ga me dan weer richten op de toepassing van functies en functionaliteiten. Einde kwartaaltip 4-2015
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 97 46 of via e-mail:
[email protected]. Wilt u geen kwartaaltips meer ontvangen? Klik dan op:
[email protected] Hebt u een verzoek voor een kwartaaltip? Klik dan op:
[email protected] en vermeld tevens uw Excelversie. Richard Meijles
Pagina 12 van 12
www.excellerend.nl
