Nationale Controllersdag 2016
Tips & Trucs voor data-analyse met Excel
Agenda
Tips & Trucs voor data-analyse • Ingrediënten van een effectieve data-analyse • Werken met tabellen
• Effectieve dwarsdoorsnedes met draaitabellen – slicers – tijdlijn • Dynamische sommeringen maken door SOMMEN.ALS te koppelen aan variabele tijdvakken
Tips & Trucs voor data-analyse met Excel
Effectieve data-analyse Verzamelen van brondata •
ERP – Databases -Excel spreadsheets- Internet
Vaststellen juistheid en volledigheid • •
Balanscijfers – resultaatcijfers – saldibalans Periode - Afsluitperiode
Onderzoeken grootte van de databron • •
Aantal kolommen – rijen Alle records versus subsets importeren
Bestuderen lay-out data-bron • •
CSV – tekstbestand Kruistabel- Databasetabel – andersoortige tabel
Importeren – opschonen data • •
Tools: Manueel – Automatisch Copy/Paste – Importmacro – Power Query – Power Pivot
Tips & Trucs voor data-analyse met Excel
Werken met Tabellen Uitgangspunten voor een Excel tabel • • • • • • • • • • • • •
Unieke kopteksten Geen samengevoegde cellen Geen formules in kopteksten Korte en duidelijke kopteksten Geen datum-notatie in kopteksten Geen lege rijen Geen lege kolommen Database structuur Consistent gebruik van kolommen (datum – tekst – cijfers – formules) Gebruik van codes waarmee groeperingen gemaakt kunnen worden Verrijkte kolommen rechts plaatsen van de inputkolommen Omzetten naar een Excel Tabel via Invoegen – Tabel Naam geven Excel tabel
Tips & Trucs voor data-analyse met Excel
Werken met Tabellen Voordelen van een Excel tabel • • • • • •
Makkelijk leesbare records door speciale lay-out Bij het naar beneden rollen zijn de kopteksten steeds in beeld Makkelijk te begrijpen formules dankzij gestructureerde verwijzingen Bij toevoeging nieuwe records wordt de opmaak en formules van de voorgaande rij meegenomen Formules worden automatisch doorgekopieerd Basis voor dynamische grafieken – lijsten – draaitabellen
Nadelen van een Excel tabel • • • •
De kopteksten mogen geen formules bevatten De kopteksten mogen geen datum-format bevatten Bij bouwen formules zijn de tabelnamen niet oproepbaar via F3 toets Heeft enige gewenning nodig bij aanduiden Tabel-onderdelen
Gestructureerde verwijzingen
Gestructureerde verwijzingen
Tips & Trucs voor data-analyse met Excel
Draaitabellen Tips & Trucs met draaitabellen • • • • • • • • • • • •
Zorg dat de draaitabel altijd gebaseerd is op een Excel tabel Kies de tabel vormgeving voor een leesbare table Gebruik liever slicers in plaats van rapportfilters Door het gebruik van slicers ziet u de onderlinge samenhang tussen de data Gebruik de ingebouwde opmaak-optie zodat bij verversing de draaitabel de opmaak behoudt Bouw een event macro om de draaitabel automatisch te verversen Maak een dashboard gebaseerd op verschillende draaitabellen en draaigrafieken door de slicers te koppelen aan alle draaitabellen Maak diverse tijddoorsnedes door het gebruik van een tijdlijn Maak zonodig gebruik van berekende items en velden om geheugen te sparen Gebruik groeperingen voor het oprollen naar een hoger niveau Koppel verschillende Excel tabellen aan elkaar door het gebruik vanhet datamodel Maak gebruik van voorwaardelijke opmaak om afwijkende cijfers te benadrukken
Tips & Trucs voor data-analyse met Excel
SOMMEN.ALS Tips & Trucs met SOMMEN.ALS • • • • • •
Zorg ervoor dat u altijd de functie invoert via het functie-wizard dialoogvenster Wijs naar de cellen in de tabel Wijs naar de kopteksten in de table zodat gestructureerde verwijzingen ontstaan Maak zonodig verrijkte kolommen in de basistabel om berekeningen te kunnen completeren Gebruik zonodig bereiknamen voor de variabelen Voor aanduiding van dynamische tijdreeksen maakt u gebruik van “>=“ of “<=“ gevolgd door & en datumfunctie.
Tips & Trucs voor data-analyse met Excel
Meer info U kunt vragen mailen naar: •
[email protected]
Website: www.AlwaysExcel.com
