dinsdag 12 juli 2016

Overzicht: de meest gebruikte Excel functies

De meest gebruikte Excel functies bijeen in één overzicht.


Excel sanity check

De hierna opgesomde regels zijn gebaseerd op een document van de ICAEW (Twenty principles for good spreadsheet practice), maar geheel ingericht en vormgegeven op basis van de kennis en ervaring van ExcelProServices. Als u de lijst doorleest, merkt u dat het geen sinecure is om een goed en betrouwbaar spreadsheet op te zetten. De lijst begint dan ook al, nog voor u de eerste cel aan gaat klikken, met het bepalen van ontwerp en standaarden. Dat lijkt veel werk (is het ook), maar dat zijn uren die u ruimschoots gaat terugverdienen. Het is een gegeven in de IT industrie dat wijzigingen in de uitvoering het dubbel kosten dan dat die wijziging al in het voorgaande stadium was ontdekt. Meer weten? Neem contact op met ExcelProServices op.
  1. Documentatie
Begin met een ontwerp: wat moet het sheet doen? Wat is de invoer, welke zijn de rekenregels, hoe ziet de uitvoer er uit? Voor wie is de uitvoer bedoeld? Test uw rekenregels eventueel via een simpel informeel spreadsheet.
Neem in ieder geval een eerste tabblad op met daarin versienummers, auteurs, datum en wijzigingen historie. Denk ook aan een tabblad genaamd “handleiding”.
Tijdens de ontwikkeling van een sheet kunt u gebruik maken van de opmerkingen eigenschap van een cel om een toelichting in te plaatsen.
  1. Is een spreadsheet wel de juiste oplossing?
Regelmatig wordt voor van alles een spreadsheet gebruikt. Een spreadsheet is echter een manier om eenvoudig complexe berekeningen uit te voeren. Het is géén tekstwerker of tekentool.
Daarnaast zijn sommige problemen dermate complex of groot dat wellicht een database of zelfs aparte maatwerk software een betere oplossing is.
Een veel voorkomend probleem is dat een spreadsheet vak eenvoudig begint, maar na verloop van tijd uitgroeit tot een onbegrijpelijk gedrocht waarvan onderhoud veel tijd kost, of erger nog, waarvan niemand meer kan garanderen dat het op de juiste manier werkt. Schroom in die gevallen niet om uw opdrachtgever tot andere inzichten te brengen.
  1. Ken uw eindgebruiker
De vraag is voor wie de resultaten van het sheet bedoeld zijn. Is dat uzelf, een (Excel)collega of een lid van de directie? Gaat die gebruiker zelf met de sheet aan de gang (kennisniveau!) of krijgt hij alleen de resultaten in bijvoorbeeld PDF formaat? Kern van het punt is dat de gegevens gepresenteerd moeten worden in een vorm die de bedoelde eindgebruiker begrijpt. Een directeur wil geen complexe (deel)berekeningen zien maar alleen eenvoudig tabellen of grafieken. Een technisch onderlegd persoon zal misschien wél willen weten hoe de resultaten tot stand gekomen zijn.
  1. Definieer een standaard
Definieer voor uw spreadsheets een standaard. Dat wil zeggen, bepaal bijvoorbeeld welke kleuren gebruikt worden voor invoer, uitvoer, variabelen etc. Bijvoorbeeld een blauw font is invoer, een groen font is een berekend veld en rood is een foutwaarde.
Bepaal ook hoe de naamgeving van variabelen moet luiden.
Zorg er voor dat de afdrukopmaak altijd netjes verzorgd is, ook voor tijdelijke werkbladen. Zorg voor een passende pagina-indeling met een kop (bv naam van het sheet en werkblad) en pagina nummers.
Zorg er ten allen tijde voor dat er netjes gewerkt wordt. Een sheet dat “even snel” gemaakt wordt, gaat vanzelf een eigen leven leiden en is na verloop van tijd niet meer te onderhouden.
  1. Leid personeel op
Zorg er voor dat al het personeel dat met spreadsheets werkt goed wordt opgeleid in het juiste niveau. Niet iedereen hoeft expert te zijn. Ook de eenvoudige gebruiker moet toch een bepaalde basiskennis hebben. Veelal wordt van een medewerker verwacht dat hij of zij wel met Excel kan werken. Niet is minder waar. Ook “experts” kennen niet alle ins en outs van Excel. En bovendien, wat je niet weet dat bestaat kun je ook niet gebruiken. Leidt daarom op in drie niveaus: basis, gevorderd en expert.
  1. Ken het doel van het sheet
Bij het ontwerpen van het sheet is natuurlijk al het doel vastgesteld. Evenals de doelgroep. Leg deze uitgangspunten duidelijk vast in de handleidingwerkblad van uw sheet. Zo wordt iedere gebruiker er altijd aan herinnerd waar hij mee bezig is. Doel en scope gaan meestal wijzigen gedurende de leeftijd van het sheet. Pas het sheet regelmatig aan op de nieuw ontstane scope en of doelgroep (midlife update).
  1. Zorg voor backups en versienummering
Tijdens het ontwikkelen van een sheet kan het maar zo gebeuren dat gegevens overschreven worden. Maak daarom vóórdat u die ingewikkelde import, formule of kopieslag doet, eerst een back-up. Mislukt het, dan kunt u altijd teruggrijpen op een solide versie. Merk op dat dit principe van zeer groot belang is bij het doen van bewerkingen via macro’s. Op bewerkingen door macro’s uitgevoerd zit namelijk géén control-Z garantie. Let op: dit is dus wat anders dan de auto-save functionaliteit.
Gaat een sheet eenmaal in productie, zorg dan voor een versienummer (b.v. mysheet-v2.xlsx) in de sheetnaam. Op die manier kan ook bij de gebruiker geen verwarring ontstaan bij de juiste versie. Uiteraard documenteert u de verschillend tussen de versies in het daartoe opgerichte tabblad.
  1. Ontwerp op duurzaamheid
Zet een spreadsheet zodanig op dat het gemaakt is op eventuele groei. Gebruik bereiken (b.v. SOM(B2:B5) in plaats van B2+B3+B4+b5), definieer bereiknamen in plaats van cel referenties te gebruiken (b.v. BTW_LAAG in plaats van 6%), vermijd koppelingen naar andere spreadsheets, maak een aparte tabblad met variabelen. En let op het gebruik van het $-teken: de absolute of relatieve adressering.
  1. Onderscheid de verschillende gegevensgroepen
Zorg dat het in uw spreadsheet duidelijk is wat een gegeven is. Dat wil zeggen, laat duidelijk zien via kleurtjes of in aparte tabs van invoer is, wat berekende gegevens zijn, welke hulptabellen zijn, welke constanten zijn. Als het sheet een eindgebruikers groep kent, die niet erg excel-fähig zijn, kunt u bijvoorbeeld de niet-relevante sheets (bv hulptabellen, tussenresultaten) verbergen. De meer geavanceerde sheets regelen dat via een aparte macro die beschermd is met een wachtwoord.
  1. Wees consistent, ook in formule bereiken
Let er op dat bij het doorkopiëren van een celbereik ook de bereiken als gebruikt in de formule goed meekomen. Bijvoorbeeld, een optelling in een aantal kolommen, zorg dat het bereik overal gelijk is. Of als u een formule in een kolom naar beneden door kopieert, controleer dat ieder cel in het resultaat dezelfde formule heeft. Bij afwijkingen verschijnt meestal inde linker bovenhoek van de afwijkende cel een groen driehoekje. Negeer die driehoekjes niet.
En als het dan toch moet afwijken, documenteer die cel door het aanbrengen van een ‘Opmerking’ in de cel.
  1. Houdt formules eenvoudig
Het mooie van Excel is dat u in een cel zeer complexe berekeningen kunt uitvoeren met bijvoorbeeld geneste formules:=ALS(OF(UUR(L937+H938)>UUR(G937);EN(UUR(L937+H938)=Einddtijd+1;UUR(G937)=Eindtijd;1));DATUMWAARDE(TEKST(G937;”dd-mmm-jj”))+ALS(UUR(G937)=Eindtijd;1;0) +ALS(UUR(G937)=Eindtijd; TIJD(UUR(G937)+Eindtijd-1;0;0);TIJD(UUR(G937)+1;0;0));L937)
Erg handig, maar als u na een jaar moet uitvogelen wat hier bedoeld wordt, tja, dat kon wel eens lastig worden. Overweeg om in dergelijke gevallen hulpkolommen te gebruiken die u bij geconstateerde juiste werking verbergt (hulpkolommen zorgen ook voor extra informatie en dus verwarring). Maar let op, de keerzijde van deze medaille is dat bij uitbreidingen u niet moet vergeten om de hulpkolommen eerst zichtbaar te maken om ze door te kopiëren. Bent u zeker van uw zaak (en Excel kennis), dan kunt u bij aangetoond juist werken, de formules stap voor stap in elkaar vlechten en de hulpkolommen verwijderen.
Alternatief: maak in user-defined functie in Visual Basic (macro).  In VBA kunt u namelijk makkelijker documenteren.
  1. Neem geen constanten in formules op
Een fout die snel gemaakt is: even snel een kolom met de waarde inclusief BTW aanmaken: =C36*21%.Als een van die gegevens wijzigt, dan heb je de poppen aan het dansen, want waar zijn die cellen allemaal naar toe gekopieerd? Maak in dergelijke gevallen gebruik van de mogelijkheid om een cel een zinvolle naam te geven. Zet de constante in dergelijke gevallen in een aparte cel en geef die cel een naam. Bijvoorbeeld: cel X1=21%. Geef cel X1 de naam ‘BTW_tarief’ (of nog beter BTW_HOOG) en stel de formule op als  =C36*BTW_TARIEF. Uiteraard staan dergelijke constanten in een apart tabblad genaamd ‘Variabelen’ of  ‘Constanten’. De gedefinieerde namen kunt u controleren in menutab ‘Formules’ onder ‘Namen beheren’. Via deze optie kunt u ook direct constanten definiëren zonder dat ze aan een specifieke cel gekoppeld zijn.
  1. Vermijdt dubbel werk, dubbele berekeningen
Ofschoon Excel een kei is in het uitvoeren van berekeningen, moet u er toch voor zorgen om een resultaat maar op één plaats te bepalen. Als dezelfde berekening op meerdere plaatsen voorkomt, introduceert u een zeer grote fout kans. Immers, bij een wijziging in de formule, dient u alle voorkomens van die formule op te sporen en overeenkomstig aan te passen. Iets wat gegarandeerd mis gaat (want het moet altijd ‘effe snel’ gebeuren).
Deze check geldt overigens niet alleen voor formules, maar ook voor gegevenstabellen. Wat vaak voorkomt is dat de brondata afkomstig is uit een extern systeem en in Excel nader geanalyseerd gaan worden. Maak in zo’n geval een aparte tab ‘BronData’ aan, beschouw die als read-only en refereer in alle andere (reken)tabs naar die tabel door gebruik te maken van indirecte adressering. Bijvoorbeeld =’Brondata’!A1 of tabelbewerking.
  1. Gebruik de ‘out-of-the-box’ Excel installatie
Excel kent vele tientallen gebruiker specifieke instellingen, te bereiken via de ‘opties’ knop in de Bestands-menu. Veel van die instellingen zorgen voor een bijzonder gedrag van uw spreadsheet en zijn aan uw account gebonden. Blijf daar in principe af!! Als u dan toch een van die eigenschappen wilt aanpassen, documenteer dat dan duidelijk in het daartoe ingerichte tabblad.
Andere venijnige opties zijn te vinden in het Formule-men als ‘Formules weergeven’ en ‘Berekeningsopties handmatig’.
  1. Bouw controle mechanismen in
Een sheet met veel vervolgberekeningen (een resultaat is van andere resultaten afhankelijk) is lastig te volgen. Bouw daarom controles in waarin tussenresultaten getoond worden. Die tussenstanden hoeven het beeld niet te vertroebelen mits duidelijk herkenbaar weergegeven.
Een andere vorm van eenvoudige maar zeer effectieve controles, is gebruik maken van de ‘Voorwaardelijke opmaak’. Deze eigenschap is slechts een weergave van data en tast uw gegevens niet aan. In slechts 2 klikken kunt u een kolomsignalering voor dubbele waarden ingeven! Voorwaardelijke opmaak in combinatie met Filteren is een zeer sterke en nuttige eigenschap.
  1. Beveilig het sheet tegen abusievelijk wijzigen
Veelal komt het voor dat een spreadsheet ontwikkeld en beheerd wordt door persoon A (Excel technicus) en gebruikt wordt door niet-excel-technische personen. Als u dan gebruik heeft gemaakt van complexe berekeningen, dan zou het toch jammer zijn als die abusievelijk overschreven worden. Maak daarom in dergelijke sheets gebruik van de cel/sheet beschermingseigenschap in menu ‘Controleren’.  Bijvoorbeeld, mag een gebruiker bepaalde getallen niet zien,  verberg ze dan en beveilig het sheet (alternatief: maak de kleur van de cel inhoud dezelfde als de achtergrond kleur).
Als er veel te beschermen is, overweeg dan om gebruik te maken van een aparte macro die het (ont)sluiten of (on)zichtbaar maken, al dan niet met een wachtwoord beschermd, voor zijn rekening neemt.
  1. Controleer en review
Nadat u bovenstaande punten geheel hebt geabsorbeerd, bent u nu in staat om een spreadsheet te controleren. Controle kan op 2 manieren:
  1. a) Visueel
  • Gebruik de samenvattingen op de Statusbalk
Selecteer in Excel een set cellen. Op de statusbalk kunt u aan laten geven wat de som van die set is of hoeveel cellen het betreft. Zo kunt u snel zien of een resultaat klopt.
  • Visuele controle via ‘Formules weergeven’
Zet ‘Formules weergeven’ aan. Het beeld dat verschijnt zal in eerste instantie erg onoverzichtelijk zijn, maar dat is niet erg. Het gaat er namelijk niet om dat u op deze manier controleert of alle formules goed staan (ofschoon dat wel kan natuurlijk), maar het gaat in eerste instantie om het patroon van formules dat u waarneemt. Mocht namelijk in een cel per ongelijk de formule door een harde waarde zijn overschreven, dan ziet u dat nu direct. Nier vergeten om na afloop het ‘Formules weergeven’ weer uit te zetten.
  • Visuele controle door alles te selecteren. Hierdoor ziet u ook de cellen die via tekstkleur ‘verborgen’ zijn
Bij sommige spreadsheets verbergt men waarden door ze dezelfde kleur als de achtergrond te geven. Als gebruiker lijkt het dan of u met lege cellen te maken hebt. Door cellen te selecteren, zie u direct of er van dit type verborgen cellen aanwezig zijn.
  • Is er gebruik gemaakt van opmerkingen velden?
  • Bij VBA: staat er voldoende commentaar in de code?
  • Externe links.
  1. b) Technisch
  • Vergelijken van ontwerp met opgebouwde rekenregels.
  • Controle via tussenresultaten of vierkantstellingen
Bij optellingen in een tabel kunt u in een hulpveld een dwars- of vierkantstelling maken. Bijvoorbeeld u hebt meerdere sheets die berekeningen geven met andere inzichten zoals groepering naar jaar of naar product. In beide gevallen moet de totaaltelling gelijk zijn.
  • Het aanbrengen van Voorwaardelijke opmaak
Voorwaardelijke opmaak is een snelle en niet-destructieve manier om (continue) gegevens te bewaken.
  1. Testen, testen en nog een testen
En ja, dan is het sheet ‘klaar’. U gaat testen, het moeilijkste onderdeel in het proces. Bij het testen is het van het allergrootste belang dat u kunt beschikken over echte gegevens. Testen met zelf samengestelde data leidt onherroepelijk tot een onbetrouwbare productie!
Sommige spreadsheets krijgen gegevens aangeleverd uit een extern systeem. Probeer meerdere sets of doorsnedes uit dat systeem te krijgen om mee te testen. De gegevens uit een andere set willen qua samenstelling of bereik nog wel eens wisselen doordat bijvoorbeeld daar opeens een onjuiste datum (bijvoorbeeld jaarovergang) of nieuwe code in staat.
In sommige gevallen kan het juist wel handig zijn om data als een aparte testset op te nemen. In de set neemt u dan per relevant veld extreme en normale waarden op. De resultaten van de testset kunt u van tevoren goed beredeneren en vastleggen.
Over testen zijn boeken vol geschreven en die ga ik hier niet herhalen. Belangrijk is dat eerstes u het gebruiksprotocol volgt, dus het sheet gebruikt op de manier dat een gewone gebruiker dat ook zou doen. Daarna volgt de stress test: ga willekeurig door elkaar functies uitvoeren. Maak sleutel-cellen leeg of zet er een absurde waarde in.
Probeer het testen echter wel zo gestructureerd als mogelijk te houden: van linker naar rechter sheet, van linksboven tot rechtsonder. Stel een testprotocol of testscript op. Dat stelt u in staat uw werk te ordenen en systematisch het sheet door te nemen.

Beveiliging en Excel

Er zijn vele redenen waarom je een Excel spreadsheet zou willen beveiligen. Dat kan variëren van een beveiliging voor jezelf tegen abusievelijk overschrijven, afwijkingen signaleren of, aan het andere eind van het spectrum, een integrale beveiliging als in een specifiek formulier voor een externe partij.
De eerste vraag die gesteld moet worden is “Wat is het doel van de beveiliging?”. Het doel gaat dadelijk de middelen bepalen. Middelen zijn er in overvloed, maar ieder doel kent zijn eigen specifieke middelen. Middelen variëren van eenvoudig cel opmaak tot volledige afscherming met behulp van wachtwoorden.
Dit artikel biedt de lezer een zeer globaal inzicht in de relatie doel tot middel en geeft een uitwerking van een aantal methoden. Wilt u meer details weten of de cursus volgen, neem dan contact op met de auteur: info at ExcelProServices punt nl

Doel

Welke doelen kan iemand hebben om over te gaan tot het aanbrengen van enige vorm van beveiliging? Hierna zijn een aantal veel voorkomende, maar niet uitputtende lijst van, voorbeelden genoemd.
Beveiligen op consistentie
In dit geval wil je dat de gegevens consistent zijn. Bijvoorbeeld dat optellingen kloppen, dat bij opzoeken er geen wezen zijn, dat data in een bepaald bereik ligt, dat bereiken in functieargumenten volledig zijn.
Beveiligen tegen ongewenste invoer
In bepaalde cellen wil je alleen gedefinieerde waarden hebben. Bijvoorbeeld een discrete keuzelijst, een getal bereik tussen grenzen, begindatum vóór einddatum
Invoer alleen in juiste cellen
Een gebruiker mag alleen in bepaalde cellen waarden invoeren. Cellen met formules moeten beschermd worden tegen (abusievelijk) overschrijven.
Beschermen tegen dataroof
Je wilt niet dat een (andere) gebruiker ziet hoe bepaalde waarden berekend worden of hoe bepaalde constanten gedefinieerd zijn. Kan zijn dat je uit concurrentie, intellectuele of privacy overwegingen die berekeningen niet wilt laten zien. 
Extern formulier
Het sheet is een invulformulier welke door externe partijen (buiten je afdeling of bedrijf) gevuld dient te worden. Die externe partij mag geen toegang tot formules, keuzelijsten of berekeningen hebben. Slechts een bepaald aantal cellen mag met een gedefinieerd domein gevuld worden.
Macro’s.
Macro’s bevatten VBA programmeer code. De ontwikkeling van die code is onderhevig aan het auteursrecht en je wilt waarschijnlijk niet dat iedereen met jouw intellectuele prestaties aan de haal gaan. Deze dienen dus afgeschermd te worden.

Middelen

Er staan de eigenaar van een spreadsheet veel middelen ter beschikking om een bepaalde vorm van beveiliging uit te voeren. De onderstaande tabel geeft een opsomming op hoofdlijnen. De mogelijkheden worden daarna stuk-voor-stuk uitgewerkt.
Cel,bereikCeleigenschappen; opmaak, opvulling, bescherming,Voorwaardelijke opmaak, Toegevoegde formules
WerkbladVerbergen kolommen en rijen, Verbergen volledige werkblad, wachtwoord
SheetVerbergen (eenvoudig, diep verbergen), Wachtwoord tegen onbevoegd openen, Versleuteling
Macro’s (VBA)Wachtwoord

Dashboards maken met Excel

Excel dashboard voorbeeldenExcel 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.
Wilt u meer informatie? Mail mij.
Soorten tabbladen
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.
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.
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).
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.
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.