dinsdag 12 juli 2016

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.

Geen opmerkingen:

Een reactie posten