Excel wordt door velen gebruikt als analyse gereedschap om inzicht te krijgen in bedrijfs- of projectgegevens. Vaak wordt in een centraal bedrijfssysteem (bv SAP, Oracle, Unit4, Afas, etc.) een selectie van gegevens gemaakt die vervolgens gedownload wordt naar Excel. Het doel daarvan is veelal het uitvoeren van specifieke analyses om het maken van overzichten die in het moeder systeem niet of met veel moeite mogelijk zijn. Immers, Excel biedt u volledige vrijheid en veiligheid in het verwerken van de gegevens in uw eigen private omgeving. Het Excel bestand kunt u overal naar hartenlust bewerken en voorzien van doorsnedes en overzichten die u belangrijk vindt.
Stel, na enig gepuzzel hebt u die overzichten gemaakt die u het verlangde inzicht geven in de bedrijfsgegevens. U wilt die gegevens echter ook met regelmatige tussenpozen zien. Dan is nu uw eerste dashboard geboren. Een dashboard is typisch een terugkerend verschijnsel met een samenvatting van frequente geordende gegevens. Een eenmalig overzicht noemen we geen dashboard maar een rapportage.
Dashboards in Excel zijn een specifieke toepassing van een samenvatting van elementaire gegevens die u op regelmatige basis wilt zien. Voorbeelden: omzetcijfers, ziekteverzuim, offertetrechter, KPI getallen, etc. Vanuit alle regelmatige cijferbronnen is wel een destillaat te maken naar een overzichtelijke presentatie die in één oogopslag weergeeft hoe “het” ervoor staat. De lezer hoeft dus niet allerlei cijfertabellen te interpreteren, maar ziet direct grafisch, bijvoorbeeld met een grafiek of metertje, of het goed of fout is.
Maar hoe ziet dan zo’n “dashboard” er uit? Een goede vraag die niet algemeen te beantwoorden is. Wat voor de een begrijpelijk is, kan in een andere situatie totaal niet aanspreken. Wel kan gesteld worden dat Excel op dit gebied erg veel mogelijkheden biedt. De grafieken module in Excel is fenomenaal! Maar niet simpel. Het ontwikkelen van een aansprekend dashboard is veel werk. Niet alleen van de Excel techneut, maar ook voor diegene die het dashboard moet gaan gebruiken.
In dit artikel laat ik u zien hoe je op gestructureerde wijze tot een dashboard kunt komen en ik geef een voorbeeld van een aantal mogelijkheden. Tevens zal dit artikel u duidelijk maken dat het maken van een dashboard een complexe materie is, maar niet onmogelijk. Mits netjes opgezet, kan een dashboard u veel tijd en geld besparen en u de juiste inzichten in uw bedrijfsprocessen bieden.
Maar hoe ziet dan zo’n “dashboard” er uit? Een goede vraag die niet algemeen te beantwoorden is. Wat voor de een begrijpelijk is, kan in een andere situatie totaal niet aanspreken. Wel kan gesteld worden dat Excel op dit gebied erg veel mogelijkheden biedt. De grafieken module in Excel is fenomenaal! Maar niet simpel. Het ontwikkelen van een aansprekend dashboard is veel werk. Niet alleen van de Excel techneut, maar ook voor diegene die het dashboard moet gaan gebruiken.
In dit artikel laat ik u zien hoe je op gestructureerde wijze tot een dashboard kunt komen en ik geef een voorbeeld van een aantal mogelijkheden. Tevens zal dit artikel u duidelijk maken dat het maken van een dashboard een complexe materie is, maar niet onmogelijk. Mits netjes opgezet, kan een dashboard u veel tijd en geld besparen en u de juiste inzichten in uw bedrijfsprocessen bieden.
Wilt u meer informatie? Mail mij.
Soorten tabbladen
Een dashboard spreadsheet kun je ruwweg in 4 onderdelen splitsen (eventueel nog ondersteund met VBA programmering):
Een dashboard spreadsheet kun je ruwweg in 4 onderdelen splitsen (eventueel nog ondersteund met VBA programmering):
- Tabblad(en) met externe gegevens;
- Tabblad(en) met formules en berekeningen;
- Tabblad(en) met parameters, sturing, etc;
- Tabblad(en) met rapportages.
Waarom die splitsing? Dat heeft alles te maken met het onderhoud en beheer van het spreadsheet. Aangezien we het hebben over een “terugkerend” verschijnsel, moeten we er voor zorgen dat de analyses, formules, overzichten snel te maken zijn, ook door niet-excel-helden. Immers, als een analist iedere maand het dashboard wiel opnieuw moet uitvinden (met alle risico’s op fouten van dien), dan wordt zo’n dashboard een kostbare aangelegenheid. Ook valt het dan al gauw buiten de afdelingsroutine omdat er specifiek iemand ingevlogen moet worden. Die specialist hoort meestal niet tot het team welke de rapportage moet verzorgen (bijvoorbeeld projectbureau of secretariaten). Beter is het om het sheet zodanig op te stellen dat de nieuwe gegevens eenvoudig en foutbestendig ingelezen kunnen worden en van daaruit het dashboard eenvoudig zichtbaar wordt of aangemaakt kan worden.
Groep 1, ruwe invoer
De eerder genoemde indeling is zodanig gemaakt dat er een scheiding ligt tussen brondata, berekeningen en uitvoer. Bij deze opzet wordt de brondata in eigen tabbladen geplaatst waarin verder absoluut geen wijzigingen of berekeningen mogen plaatsvinden! Dit is de pure data. Mocht er eventueel een conversie noodzakelijk zijn, dan valt die in de tabbladen groep 2, de formules en berekeningen. Wel mag in deze tabs eventueel een signalering geplaatst worden met behulp van voorwaardelijke opmaak omdat daarmee geen gegevens aangepast worden.
Nieuwe gegevens invoeren is in principe vrij eenvoudig en slechts een kwestie van de gegevens in deze tabbladen overschrijven of aanvullen. Afhankelijk van het foutbestendigheidsniveau dat men wil, kan dit een kwestie zijn van simpelweg copy/paste tot een complex stuk VBA programmatuur waarin de externe gegevens worden ingelezen en in de juiste tab geplaatst worden. Hierbij moet u bijvoorbeeld denken aan gegevens die telkens in een andere kolomvolgorde worden aangeleverd of gegevens die niet in een basis Excel formaat beschikbaar zijn. Uiteraard is het wel belangrijk dat een eenmaal gekozen indeling (kolomvolgorde) gehandhaafd blijft. Excel mag dan ogenschijnlijk wel gemakkelijk afhankelijk formules aanpassen (als je bijvoorbeeld een kolom invoegt), maar dat kent zijn beperkingen. Dergelijke doorvoeren vinden niet plaats in gebruikte VBA programmering of in argumenten van bijvoorbeeld zoek functies.
De eerder genoemde indeling is zodanig gemaakt dat er een scheiding ligt tussen brondata, berekeningen en uitvoer. Bij deze opzet wordt de brondata in eigen tabbladen geplaatst waarin verder absoluut geen wijzigingen of berekeningen mogen plaatsvinden! Dit is de pure data. Mocht er eventueel een conversie noodzakelijk zijn, dan valt die in de tabbladen groep 2, de formules en berekeningen. Wel mag in deze tabs eventueel een signalering geplaatst worden met behulp van voorwaardelijke opmaak omdat daarmee geen gegevens aangepast worden.
Nieuwe gegevens invoeren is in principe vrij eenvoudig en slechts een kwestie van de gegevens in deze tabbladen overschrijven of aanvullen. Afhankelijk van het foutbestendigheidsniveau dat men wil, kan dit een kwestie zijn van simpelweg copy/paste tot een complex stuk VBA programmatuur waarin de externe gegevens worden ingelezen en in de juiste tab geplaatst worden. Hierbij moet u bijvoorbeeld denken aan gegevens die telkens in een andere kolomvolgorde worden aangeleverd of gegevens die niet in een basis Excel formaat beschikbaar zijn. Uiteraard is het wel belangrijk dat een eenmaal gekozen indeling (kolomvolgorde) gehandhaafd blijft. Excel mag dan ogenschijnlijk wel gemakkelijk afhankelijk formules aanpassen (als je bijvoorbeeld een kolom invoegt), maar dat kent zijn beperkingen. Dergelijke doorvoeren vinden niet plaats in gebruikte VBA programmering of in argumenten van bijvoorbeeld zoek functies.
Groep 2, berekeningen
In de groep-2 tabbladen staan berekeningen, analyses, indikkingen enzovoort. Deze groep kunnen optisch wel dezelfde gegevens tonen als in de invoer, maar zou je het spreadsheet omschakelen naar formules weergeven (ctrl-t), dan zie je uitsluitend formules en geen constante waarden als in de invoer. Volledige ontkoppeling dus. Tabbladen als deze kunnen er meer zijn, indien noodzakelijk voor verschillende gebruiksdoelen of analyses.
In de groep-2 tabbladen staan berekeningen, analyses, indikkingen enzovoort. Deze groep kunnen optisch wel dezelfde gegevens tonen als in de invoer, maar zou je het spreadsheet omschakelen naar formules weergeven (ctrl-t), dan zie je uitsluitend formules en geen constante waarden als in de invoer. Volledige ontkoppeling dus. Tabbladen als deze kunnen er meer zijn, indien noodzakelijk voor verschillende gebruiksdoelen of analyses.
Groep 3, parameters en sturing
Een andere groep van tabbladen, of soms slechts één blad, zijn de parameter bladen. Parameter bladen kunnen in meerdere smaken voorkomen: van constanten (bedrijfsnaam, boekjaar, versienummer, margepercentages, etc.) tot aan rapportage parameters (rapporteer van Q1 t/m Q3 uit 2015, met/zonder fysieke uitvoer, alleen objecten A t/m F enzovoort).
Een andere groep van tabbladen, of soms slechts één blad, zijn de parameter bladen. Parameter bladen kunnen in meerdere smaken voorkomen: van constanten (bedrijfsnaam, boekjaar, versienummer, margepercentages, etc.) tot aan rapportage parameters (rapporteer van Q1 t/m Q3 uit 2015, met/zonder fysieke uitvoer, alleen objecten A t/m F enzovoort).
Groep 4, Rapportage
De rapportages sheets zijn waar het uiteindelijk om begonnen is. Deze tabs bevatten de gewenste dashboards i de vorm van tabellen, grafieken, metertjes etc. etc. Voor het gemak noem ik ze maar grafieken in deze paragraaf. De vormgeving verschilt per gebruiksgroep. Mogelijk dat het hoger management slechts in eenvoudige 3-kleuren meters geïnteresseerd is terwijl de verkoopstaf een tabel met passende indicaties wil zien. Maatwerk is het credo!
Het maken van de grafiekjes is het meest tijdrovende deel van de exercitie. Ten eerste de opzet. Welke grafiekjes wil je hebben? Heeft iemand in het bedrijf al iets bedacht of heb je op internet iets zinvols gevonden (zoek maar eens op “Excel dashboard”)? Dan speelt ook een rol in hoeverre je de grafieken flexibel wil maken. Ofwel, zijn ze statisch dan wel gestuurd door parameters of een selectie criterium? Of wil men een grote variatie aan grafieken hebben? Allemaal factoren die het leven voor een Excel-ontwikkelaar aangenaam kunnen maken (lees: duur voor de opdrachtgever). Hier speelt het gebruiksdoel ook een rol. Maak je grafieken voor jezelf, de afdeling of een breed gepubliceerd jaarverslag?
Veel dezelfde grafieken maakt het leven weer eenvoudiger: je hoeft maar een keer uit te vinden en daarna is het kopiëren. Uiteraard geldt, hoe complexer hoe duurder. Meer features leidt op een bepaald moment onherroepelijk tot het gebruik van VBA programmering (macro’s). Op zich geen probleem, maar macro’s brengen een potentieel beveiligingsrisico met zich mee die niet op ieder bedrijfsnetwerk getolereerd worden. En in het algemeen werken macro’s niet lekker in een Apple omgeving.
Dit is ook de meest lastige stap in het dashboard verhaal. Het inladen en maken van basisoverzichten is vrij simpel, maar het opzetten van een unieke en duidelijke grafiek is lastig. Heb je een grafiek gevonden die aanspreekt, dan tref je in het gunstigste geval een bruikbaar spreadsheet erbij. Soms zie je alleen maar screenshots. Nogmaals, veel en geduldig werk.
Als je dashboard eenmaal klaar is volgt het testen. Test vooral met echte waarden!. Maak daarnaast ook een specifieke testset aan met daarin minimum waarden, maximum waarden, gemiddelden en onjuiste waarden. Bijvoorbeeld, je bereik loopt van €250 tot €1200. Neem dan in de testset de volgende waarden op: 0, 100,248 t/m 255, 800 t/m 810, 1195 t/ 1205 en 100000.
De rapportages sheets zijn waar het uiteindelijk om begonnen is. Deze tabs bevatten de gewenste dashboards i de vorm van tabellen, grafieken, metertjes etc. etc. Voor het gemak noem ik ze maar grafieken in deze paragraaf. De vormgeving verschilt per gebruiksgroep. Mogelijk dat het hoger management slechts in eenvoudige 3-kleuren meters geïnteresseerd is terwijl de verkoopstaf een tabel met passende indicaties wil zien. Maatwerk is het credo!
Het maken van de grafiekjes is het meest tijdrovende deel van de exercitie. Ten eerste de opzet. Welke grafiekjes wil je hebben? Heeft iemand in het bedrijf al iets bedacht of heb je op internet iets zinvols gevonden (zoek maar eens op “Excel dashboard”)? Dan speelt ook een rol in hoeverre je de grafieken flexibel wil maken. Ofwel, zijn ze statisch dan wel gestuurd door parameters of een selectie criterium? Of wil men een grote variatie aan grafieken hebben? Allemaal factoren die het leven voor een Excel-ontwikkelaar aangenaam kunnen maken (lees: duur voor de opdrachtgever). Hier speelt het gebruiksdoel ook een rol. Maak je grafieken voor jezelf, de afdeling of een breed gepubliceerd jaarverslag?
Veel dezelfde grafieken maakt het leven weer eenvoudiger: je hoeft maar een keer uit te vinden en daarna is het kopiëren. Uiteraard geldt, hoe complexer hoe duurder. Meer features leidt op een bepaald moment onherroepelijk tot het gebruik van VBA programmering (macro’s). Op zich geen probleem, maar macro’s brengen een potentieel beveiligingsrisico met zich mee die niet op ieder bedrijfsnetwerk getolereerd worden. En in het algemeen werken macro’s niet lekker in een Apple omgeving.
Dit is ook de meest lastige stap in het dashboard verhaal. Het inladen en maken van basisoverzichten is vrij simpel, maar het opzetten van een unieke en duidelijke grafiek is lastig. Heb je een grafiek gevonden die aanspreekt, dan tref je in het gunstigste geval een bruikbaar spreadsheet erbij. Soms zie je alleen maar screenshots. Nogmaals, veel en geduldig werk.
Als je dashboard eenmaal klaar is volgt het testen. Test vooral met echte waarden!. Maak daarnaast ook een specifieke testset aan met daarin minimum waarden, maximum waarden, gemiddelden en onjuiste waarden. Bijvoorbeeld, je bereik loopt van €250 tot €1200. Neem dan in de testset de volgende waarden op: 0, 100,248 t/m 255, 800 t/m 810, 1195 t/ 1205 en 100000.
Beveiliging
In verband met beveiliging kunnen sommige tabbladen verborgen of met een wachtwoord geblokkeerd zijn. Op die manier kunnen onbevoegden en onbedoelden geen gegevens abusievelijk wijzigen. Afhankelijk van uw situatie kunt u alle tabbladen, behalve sturing en rapportage blokkeren of verbergen. Via bijvoorbeeld een wachtwoord constructie kun je in de onderhoudsmode geraken.
Mocht het sheet verspreid worden, dan kan het zeker lonen om de sheet uitgebreid te beveiligen. Maar dat is een ander artikel in deze serie.
In verband met beveiliging kunnen sommige tabbladen verborgen of met een wachtwoord geblokkeerd zijn. Op die manier kunnen onbevoegden en onbedoelden geen gegevens abusievelijk wijzigen. Afhankelijk van uw situatie kunt u alle tabbladen, behalve sturing en rapportage blokkeren of verbergen. Via bijvoorbeeld een wachtwoord constructie kun je in de onderhoudsmode geraken.
Mocht het sheet verspreid worden, dan kan het zeker lonen om de sheet uitgebreid te beveiligen. Maar dat is een ander artikel in deze serie.
Geen opmerkingen:
Een reactie posten