In dit artikel weer een potpourri aan Excel tips & trucs, zoals hoe u datums in headers kunt gebruiken in een Excel-tabel, hoe u een custom report kunt maken gebaseerd op een draaitabel en hoe u foutmeldingen bij het printen kunt voorkomen.
Datums in de headers gebruiken in een Excel-tabel
Vraag: In heb mijn kruistabel omgezet naar een Excel tabel via Invoegen -> Tabel. Nu blijkt dat de kopteksten waarin datums voorkomen geconverteerd zijn naar teksten. Nu werken de opzoekformules niet meer naar behoren. Kan ik dit voorkomen?
Antwoord: U kunt dit niet voorkomen omdat dit inherent is aan het werken met echte Excel-tabellen. Er mogen in de kopteksten ook geen formules voorkomen. In uw opzoekformule kunt u de opzoekperiode via een hulpcel wel vinden. Stel u heeft de volgende kruistabel welke reeds omgezet is naar de tabel met als naam TblSales.
U kunt de omzet behorende bij een bepaalde regio en bepaalde periode als volgt opzoeken:
In cellen B1 en C1 vult u de gewenste zoekwaarden in.
Hulpcel B3 bevat de formule: =TEKST(B2;”mmm-jj”) en de omzet kan worden berekend aan de hand van de volgende opzoekformule:
=INDEX(TblSales[#Alles];VERGELIJKEN(B1;TblSales[regio];0);VERGELIJKEN(B3;TblSales[#Kopteksten];0))
Door gebruik te maken van gestructureerde referenties in de brontabel is de opzoekformule zeer dynamisch geworden. Zodra u nieuwe regio’s en/of perioden toevoegt, blijft de formule stabiel en de resultaten passen zich automatisch aan.
Custom rapport maken gebaseerd op draaitabel
Vraag: Als ik een draaitabel maak, kan ik geen kolommen of rijen handmatig toevoegen. Hoe kan ik een eigen rapport maken met de gegevens uit de draaitabel?
Antwoord: u kunt u eigen indeling van het rapport samenstellen en met de functie DRAAITABELOPHALEN (GETPIVOTDATA) de parameters in die functie dynamisch maken. U dient te controleren of deze functie geactiveerd is. Zorg ervoor dat u de cursor zich in de draaitabel bevindt. Uit het lint selecteert u: Hulpmiddelen voor draaitabellen -> Analyseren -> Draaitabel -> Opties. Het uitklapvenster vertoont de volgende opties:
Zorg ervoor dat DraaitabelOphalen genereren is aangevinkt.
De draaitabel moet zodanig zijn vormgegeven dat daaruit de gewenste waarden in uw eigen rapport naar binnen gehaald kunnen worden. Als u in een cel in uw rapport een koppeling aanbrengt met de gewenste cel in de draaitabel, dan wordt deze als volgt ingevoerd (voorbeeld):
=DRAAITABEL.OPHALEN(“Amount”;PtCrall!$A$4;”Crew”;”Clinical”;”DataType”;”actual”;”ProjectID”;”Q1″;”Jaren”;2016)
U kunt de parameters in deze functie verder dynamiseren door te verwijzen naar variabele cellen of bereiknamen. De formule kan er uiteindelijk als volgt uitzien:
=ALS.FOUT(DRAAITABEL.OPHALEN(“Amount”;StartPtCrewQtr_CRall;”Crew”;$B10;”DataType”;C$4;”ProjectID”;C$7;”Jaren”;CurYear)/1000;0)
Het verdere voordeel van de functie DRAAITABELOPHALEN ten opzichte van opzoekfuncties is dat hij heel snel reageert op veranderingen door middel van slicers. Bij custom rapporten met veelvuldig gebruik van opzoekfuncties worden veranderingen trager doorgevoerd.
Foutmeldingen onderdrukken bij het printen
Foutmeldingen die op het werkblad voorkomen zoals #DEEL/0! Of #N/B zien er slordig en misleidend uit als u het rapport afdrukt. U kunt dergelijke foutmeldingen vermijden door als volgt te handelen:
Uit het lint kiest u -> Pagina-indeling -> Pagina-instelling -> Titels afdrukken
Het volgende dialoogvenster verschijnt:
U kunt bij Fouten in cellen als: <leeg> selecteren en op OK klikken.
Het is ook mogelijk om – – te selecteren.
Auteur: Tony De Jonker, a.de.jonker@kpnmail.nl
Dit artikel is verschenen in cm: 2016, afl. 9
Lees hier meer Excel tips & trucs van Tony De Jonker.
