Excellerend
Kwartaaltip 2015-3
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 BANK: NL72ABNA0536825491 KVK: 24389967
Draaitabellen III Draaitabel over meerdere tabbladen In de voorgaande twee kwartaaltips heb ik wat mogelijkheden laten zien van een draaitabel die gegevens samenvat vanuit één tabel. Maar wat moet je nu doen wanneer je de begroting op tabblad één hebt staan, en de realisatie op tabblad twee? Je kunt de data kopiëren en onder elkaar zetten, maar dan moet de volgorde van de kolommen wel weer overeenkomen. Dat is niet altijd zo, maar daarnaast, is het nou wel nodig om handmatige handelingen uit te voeren in zo’n geval? Excel kent twee manieren om hiermee om te gaan. We duiken hiermee wel de diepte in als het gaat om de functionaliteiten van Excel! Maar laat je hierdoor niet bang maken. Ik vond het een leuk onderzoek om te doen en neem jullie graag mee op reis. Het Excelbestand dat ik gebruik staat ook in de e-mail, en anders is het via mijn website te downloaden: www.excellerend.nl\kwartaaltips.html, zoek dan naar 2015, 3e kwartaal. Methode 1 In Excel 2003 was er voor het maken van een draaitabel een Wizard die je in drie stappen door het proces van het bouwen heen leidde. Die dialoogvensters zijn via het lint niet meer direct beschikbaar. Veel van de oude toetsenbordcombinaties (In mijn Nederlandstalige versie van Excel is het scherm opvraagbaar via Alt+A, A, D) zijn er ‘onder water’ nog wel, maar ze werken niet allemaal meer. Onbetrouwbaar dus. Maar de dialoogvensters zijn wel oproepbaar door het lint aan te passen! Lint aanpassen Klik op Bestand > Opties (Ik gebruik Excel 2010). En volg de volgende stappen.
Pagina 1 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
1) Klik op Lint aanpassen; 2) Kies bovenin uit de dropdownlijst ‘Opdrachten die niet op het lint staan’; 3) Kies uit de alfabetisch gesorteerde lijst die dan verschijnt de opdracht ‘Wizard Draaitabel en draaigrafiek’; 4) Klik in het rechter menu op de + bij Invoegen en bij Tabellen totdat je “Draaitabel” en
“Tabel” ziet staan. Op het lint ziet dat gedeelte er zo uit: 5) Selecteer ‘Tabellen’ (zoals hierboven blauw weergegeven) en klik onderaan op ‘Nieuwe groep’. Klik op de knop ‘Naam wijzigen…’ en geef in het dialoogvenster dat dan verschijnt de groep de naam “Draaitabel multi” en kies eventueel een leuk icoontje uit:
Pagina 2 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
Klik op OK. Selecteer vervolgens in het rechtermenu de nieuwe map en controleer of linksonder nog steeds ‘Wizard Draaitabel en draaigrafiek’ geselecteerd is. Klik dan op de knop ziet er zo uit:
midden in het scherm, en klik weer op OK. Het lint is nu aangepast en
Maken draaitabel over twee tabbladen heen In een Excelbestand heb ik twee tabbladen: “realiteit” en “begroot”. De tabellen op die tabbladen moeten identiek zijn van opbouw, maar mogen wel een verschillend aantal regels hebben en ook de waarden in de kolommen mogen afwijken. De kolomtitels moeten identiek zijn:
Tabblad begroot
Tabblad realiteit
Het maakt nu niet uit welke cel je geselecteerd hebt. Klik op de knop die je aangemaakt hebt: “Wizard Draaitabel en draaigrafiek” en onderstaand dialoogvenster verschijnt. Kies de optie ‘Meervoudige samenvoegingsbereiken’:
Pagina 3 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
Klik op Volgende en kies de optie ‘De paginavelden zelf maken’:
Klik op Volgende om de twee bereiken te selecteren:
Pagina 4 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
Als je de eerste tabel hebt geselecteerd, klik je op de knop ‘Toevoegen’. Selecteer dan de tweede tabel en klik weer op ‘Toevoegen’. Vink het rondje aan bij 1 paginaveld:
Selecteer het bereik van het tabblad begroot (zoals hierboven) en typ handmatig in ‘Veld een:’ de waarde “Begroot”. Selecteer vervolgens het bereik van het tabblad realiteit en typ handmatig in ‘Veld een:’ “Realiteit”. Klik op Volgende. Pagina 5 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
In de laatste stap kies je een bestaand of nieuw tabblad en een cel om de draaitabel in te plaatsen:
Klik op Voltooien. De draaitabel ziet er dan (deels) zo uit, maar daar ben ik nog niet tevreden mee.
Bij het paginaveld linksboven zijn twee scenario’s te zien: Begroot en Realiteit. Door over de tekst in cel A1 te typen, krijgt het paginaveld een andere naam mee:
Pagina 6 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
In de draaitabel worden nu nog aantallen getoond van alle drie velden die geen datum bevatten. Ik wil alleen de som zien van het geld. Via de rechter muisknop in de draaitabel ga ik via “Opties voor draaitabel…” naar het tabblad ‘Weergave’ om de klassieke indeling te kiezen:
Op het tabblad ‘Totalen en filters’ zet ik de rijtotalen uit en klik op OK. Klik in de draaitabel op de driehoek naast Kolom en vink de vierkantjes voor Verkoper en Product af. Klik op OK. Klik met de rechter muisknop op een veld met een getal, kies ‘Waardeveldinstellingen’ en zet de samenvatting op ‘Som’. Klik weer op OK.
Wissel nu rechtsonder de velden Kolom en Scenario om:
Pagina 7 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
De draaitabel ziet er nu –deels- zo uit:
Ik wil nu nog drie dingen doen. Een berekend Item toevoegen dat het verschil laat zien tussen de Begroting en de Realiteit, ik wil de cijfers per datum groeperen op maand en ik wil dwarsdoorsnedes maken op verkoper en op product. Om met het groeperen te beginnen klik ik met de rechter muisknop op cel A4 waar Rij staat
en kies voor groeperen: . In het dialoogvenster dat dan verschijnt kies ik voor maanden en eventueel pas ik de begin- en einddatum aan als ik weet dat er in de toekomst nog meer datums bijkomen:
Pagina 8 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
Wanneer ik nu het verschil wil berekenen tussen de begroting en de realiteit, kan ik via de hulptab ‘Hulpmiddelen voor draaitabellen’ en berekend item invoeren:
Maar helaas stuit ik dan op een technische beperking van Excel waar ik niet omheen kom:
Ook krijg je het op deze manier niet voor elkaar om de gerealiseerde en begrootte omzet per verkoper of product te kunnen zien. De eerste techniek kan dus veel, maar niet alles wat ik wil. Pagina 9 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2015-3
Ik had gedacht beide technieken wel in één kwartaaltip te kunnen plaatsen, maar de tweede manier is pittiger en zal ook wel tien pagina’s bevatten. Met 20 pagina’s wordt het meer een cursus dan een tip. Die tweede manier houden jullie dus van me tegoed. Hopelijk hou ik jullie zo ook nieuwgierig naar de vierde kwartaaltip van 2015!
Einde kwartaaltip 3-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 10 van 10
www.excellerend.nl
