Skip to content

Excellerend valideren van spreadsheets

20 februari 2016

 

gdp

Spreadsheets zijn een handig hulpmiddel om gegevens te verzamelen en hiermee berekeningen uit te voeren. Spreadsheets zijn relatief eenvoudig in gebruik. In Microsoft Excel zijn de meest gebruikte statistische functies beschikbaar. Meervoudige berekeningen kunnen op transparante wijze worden gepresenteerd, zodat het voor anderen eenvoudig te achterhalen is welke berekeningen zijn uitgevoerd. Maar een fout is zo gemaakt. Audits hebben aangetoond dat 94% van de spreadsheets fouten bevatten. Het is daarom een goed gebruik om de ‘business impact’ van elke spreadsheet te analyseren en aan de hand van deze analyse een onafhankelijke controle uit te voeren op de berekende gegevens in de spreadsheet. Zeker waar aan de hand van de resultaten besluiten worden genomen waarop de GxP regelgeving van toepassing is.

Sommige berekeningen worden routinematig uitgevoerd. Dan worden standaard spreadsheets geprogrammeerd, die vervolgens meerdere malen worden gebruikt. Bij het gebruik van deze gestandaardiseerde templates moet er zekerheid zijn dat de functionaliteit juist is, bruikbaar is voor het beoogde gebruik en voldoet aan de daarbij geldende regelgeving. Daarom moeten Excel templates en andere ondersteunende spreadsheets worden gevalideerd voor het beoogde gebruik. Ze moeten ook worden beheerd in een document management systeem. Deze blog geeft richtlijnen voor het waarborgen dat spreadsheets aan deze criteria voldoen.

Spreadsheet validatie levenscyclus

Omdat Excel als standaard office pakket voor iedereen beschikbaar is worden veel spreadsheets met de beste bedoelingen door gebruikers ontworpen. Totdat men zich gaat realiseren dat er data integriteit risico’s verbonden zitten aan het gebruik van spreadsheets. Dan ontstaat de realisatie dat validatie van de functionaliteit en beheersing van de toepassing van spreadsheets gewenst is. Dat is niet het moment dat gebruikers buitenspel worden gezet en QA de verantwoordelijkheid van spreadsheets overneemt. Juist het betrokken houden van de gebruikers bij de beheersing van de data integriteit kan begrip voor de voordelen en meerwaarde van het valideren van spreadsheets opleveren.

Veel spreadsheet validatieprojecten mislukken als gevolg van een gebrek aan communicatie tussen de spreadsheet gebruikers en de mensen die verantwoordelijk zijn voor de validatie van de spreadsheets. Het kan niet genoeg worden benadrukt hoe belangrijk het is om de volledige support te krijgen van de spreadsheet-gebruikers. De belangrijkste redenen hiervoor zijn:

  • De gebruikers kunnen het beste de gebruiksvriendelijkheid van de spreadsheet bepalen. Zij weten hoe de gegevens ten behoeve van de invoer worden verzameld. Bijvoorbeeld: hoeveel metingen in de spreadsheet kunnen worden vastgelegd en wat het bereik is van de meetresultaten.
  • Gebruikers weten ook welke besluiten worden genomen naar aanleiding van de berekende resultaten, welke stappen in de berekening cruciaal zijn voor de besluitvorming en dus herleidbaar moeten worden gepresenteerd. Met andere woorden: gebruikers weten hoe de spreadsheet moet worden ontworpen om de beste ondersteuning te bieden aan het bedrijfsproces. Gebruikers voelen zich hier het meest verantwoordelijk voor.
  • De gebruikers hebben het meeste inzicht in de functionaliteit van de spreadsheet. De spreadsheet is immers een oplossing voor hen om noodzakelijke berekeningen op een gecontroleerde wijze uit te voeren om daarmee te voldoen aan de eisen van data integriteit en data kwaliteit. Omdat gebruikers de intentie van de berekening begrijpen is het voor hen eenvoudiger te verifiëren of formules juist zijn geprogrammeerd.
  • Als alternatief kan de functionaliteit worden uitgeschreven in een spreadsheet specificatie. Voor eenvoudige spreadsheets is het voldoende dat de functies zijn omschreven in werkvoorschriften. Voor complexere spreadsheets is een functionele spreadsheet specificatie wenselijk. In deze spreadsheet specificatie worden het gebruikersdoel en alle berekeningen gedefinieerd.

Ontwikkeling van spreadsheets

Het ontwerp, de ontwikkeling en het formatteren van de spreadsheets dient te worden uitgevoerd met het oog op het faciliteren van gebruik, gegevensbeveiliging en validatie. Het verdient de voorkeur om vastgestelde bedrijfsrichtlijnen en standaarden voor spreadsheetontwerp te hebben. Typische ontwerprichtlijnen zijn:

  • Spreadsheets moeten zelfverklarend zijn. Dat betekent dat de gegevens in logische volgorde moeten worden gerangschikt. Dat betekent dat de verwerking van gegevens van links naar rechts en/of van boven naar beneden moet plaatsvinden. Met andere woorden, formules staan altijd rechts en/of onder de broncellen waarnaar ze verwijzen.
  • Voeg bij tabellen een lege rij of kolom tussen de invoervelden en de formules in de laatste rijen en kolommen van de tabel. Neem deze lege rij of kolom altijd mee in de formule. Waneer er een rij of kolom moet worden toegevoegd aan de invoervelden, voeg deze dan toe tussen de gevulde cellen en de lege rij/kolom. Door deze werkwijze te volgen zorgt je ervoor dat wanneer aanvullende rijen of kolommen worden ingevoegd, de formules automatisch worden aangepast en de nieuwe data zullen meenemen in de berekening. Deze regel alleen gebruiken wanneer er vooraf niet kan worden vastgesteld hoeveel rijen of kolommen door de gebruiker zullen worden gebruikt. Staat dit wel vast, dan kun je beter de spreadsheet beveiligen tegen het toevoegen van rijen en kolommen. Zie daarvoor hieronder bij wachtwoordbeveiliging.
  • Spreadsheets moeten een ‘intuïtieve’ lay-out hebben met duidelijk gescheiden delen, bijvoorbeeld aparte ruimtes voor het invoeren van gegevens gescheiden van de resultaten van de berekeningen. De resultaten zijn overzichtelijk gegroepeerd om besluitvorming te faciliteren. Gebruik duidelijke namen voor de variabelen en kolomkoppen om de betekenis ondubbelzinnig weer te geven. Van alle waarden moet de eenheid waarin ze worden weergegeven duidelijk zijn. Een consistente ‘look and feel’ voor alle spreadsheets (bijv. kleuren voor de invoervelden, consistent gebruik van opmerkingen en gegevensvalidatie et cetera) zal de gebruikers helpen bij het toepassen van de spreadsheets.
  • Er worden zo min mogelijk complexe formules gebruikt. Bij voorkeer worden meerdere afzonderlijke formules gebruikt voor elke stap in een sequentiële berekening.
  • Constanten die in formules worden gebruikt worden in afzonderlijke cellen gepresenteerd, om vervolgens in de formule naar te worden gerefereerd. Hierdoor kan bij wijziging van de constante de waarde eenvoudig worden gecorrigeerd, zonder alle formules afzonderlijk te moeten wijzigingen. De kans is dan groot dat een formule wordt vergeten.
  • Gebruik gegevensvalidatie om te voorkomen dat gebruikers ongeldige gegevens invoeren. Gegevensvalidatie is een functie van Excel waarmee u beperkingen kunt definiëren voor de gegevens die in een cel kunnen of moeten worden ingevoerd. U kunt gegevensvalidatie zodanig configureren dat alleen voorgeprogrammeerde waarden die worden weergegeven in een drop-down menu voor invoer kunnen worden geselecteerd. Dit is vooral kritisch als de waarde vervolgens wordt gebruikt in een IF of LOOKUP functie. Ook kunnen limieten worden gesteld aan numerieke invoerwaarden.
  • Gebruik van macro’s moet tot een minimum worden beperkt, vooral als ze niet kritisch zijn voor het juist en efficiënt gebruik van de spreadsheets. Macro’s kunnen tijd besparen bij het gebruik, maar een extra last geven bij validatie. Macro’s zijn minder transparant.
  • Gebruik geen verborgen regels of kolommen, omdat deze het lastig maken om de rekenfuncties te volgen. Wel kunnen cellen buiten het afdrukbereik van de spreadsheet worden gebruikt, om wel een overzichtelijk uitdraai te krijgen.
  • Beperk het gebruik van meerdere gescheiden werkbladen in een Excel werkmap. Het refereren van waarden buiten het werkblad (dus in andere werkbladen of zelfs in andere werkmappen) is lastig te valideren. Referenties buiten het werkblad worden alleen gebruikt om duplicatie van invoer te voorkomen. Stelregel is dat elke waarde maar 1 keer mag worden ingevoerd. Geef elk werkblad een logische naam, zodat referenties buiten het werkblad toch goed te volgen zijn.
  • Afronding zorgt voor het gebruik van het juiste aantal significante cijfers zoals weergegeven in de specificaties. Afronding wordt bij voorkeur gedaan bij de laatste stap van de berekening. Als een waarde eenmaal is afgerond, mag het niet opnieuw worden afgerond. Het is ook kritisch dat invoerwaarden de gespecificeerde nauwkeurigheid (aantal decimalen) hebben. Helaas is het niet eenvoudig om met behulp van gegevensvalidatie het aantal decimalen van de invoer te beperken. Hiervoor kun je de aangepaste gegevensvalidatie en de AFRONDEN functie gebruiken.
  • Zorg voor het juiste gebruik van celeigenschappen. Gebruik een numeriek formaat als getallen moeten worden ingevoerd. Met name het weergeven van numerieke waarden met het juiste aantal decimalen moet zorgvuldig worden geprogrammeerd in overeenstemming met de gespecificeerde nauwkeurigheid.

Bij het gebruik van een gevalideerde spreadsheet mag de gebruiker de functionaliteit van de spreadsheet niet kunnen wijzigen. Dit kan alleen maar worden voorkomen door gebruik van wachtwoordbeveiliging. Ook hier gelden een paar regels:

  • In principe moeten alle cellen behalve de invoervelden worden beveiligd tegen wijzigingen door de gebruiker. Voor de duidelijkheid worden invoervelden met kleurcodering gemarkeerd.
  • Alle cellen met rekenfuncties moeten worden beveiligd tegen ongewenste wijziging.
  • Cellen met constanten die in rekenfuncties worden gebruikt moeten worden beveiligd tegen ongewenste wijziging.
  • Bij voorkeur kan de gebruiker alleen de invoervelden selecteren, d.w.z. de cursor kan alleen op invoervelden worden geplaatst. Dit neemt alleen wel de mogelijkheid weg om resultaten te elektronisch kopiëren naar andere documenten of systemen. Het valideren van de spreadsheet betekent dus niet dat alle problemen rondom de data integriteit van hybride systemen is opgelost.
  • Met name als referenties tussen meerdere werkbladen worden gebruikt moeten werkmappen worden beveiligd.
  • Tenslotte kun je nog een wachtwoord voor schrijfbevoegdheid instellen. Daarmee zorg je ervoor dat alleen mensen die dit wachtwoord kennen, de werkmap kunnen overschrijven. Deze beveiliging is noodzakelijk als de template voor iedereen op een vaste netwerk locatie of het bureaublad van de PC beschikbaar is. Door de beperkte schrijfbevoegdheid is te waarborgen dat de gevalideerde versie beschikbaar blijft en niet per ongeluk wordt overschreven.

 

Zoals altijd is de validatie gebaseerd op het gebruik en de wettelijke eisen die door regelgeving aan deze toepassing worden gesteld. U zult Excel op zich niet valideren, alleen het gebruik van de spreadsheet. Bedenk ook dat het creëren van SOP’s en training van spreadsheetgebruikers een deel van de validatie moet zijn.

Wanneer spreadsheets worden gebruikt voor meerdere numerieke berekeningen in de vorm van zelf ontwikkelde templates, is het belangrijk om het werkblad te beveiligen tegen onbedoelde wijzigingen, om de betrouwbaarheid van de spreadsheet te controleren door vergelijking met bekende resultaten van bekende gegevens, en te zorgen dat de spreadsheet kan omgaan met onverwachte gegevensinvoer behoeften. Spreadsheets ontwikkeld in het ORA laboratorium moeten worden beschouwd als in-house ontwikkelde software die voor gebruik moet worden gekwalificeerd, net als instrumenten worden gekwalificeerd voor gebruik.

Validatie van Spreadsheets

De validatie moet gebaseerd zijn op wat de spreadsheet doet en wat is toegestaan of niet is toegestaan bij het gebruik ervan. Je valideert niet Excel – je valideert het gebruik. Excel is commercieel of-the-shelf (COTS) software, dat is gevalideerd door het veelvuldig gebruik. Maar de functionaliteit van elke individuele spreadsheet moet worden gecontroleerd. Voor eenmalig gebruik is controle van elke spreadsheet de enige optie. Wanneer de functionaliteit van geprogrammeerde templates is gevalideerd is het niet meer noodzakelijk om de functionaliteit van elke individuele spreadsheet te valideren.

Bij validatie wordt geverifieerd of aan alle bovengenoemde ontwerpcriteria is voldaan. De rekenfuncties kunnen worden gecontroleerd met behulp van testdata. Hiervoor worden data gebruikt die aan de invoercriteria voldoen, maar ook data die daar juist niet aan de gegevensvalidatie of het celformaat (alfabetische waarden in numerieke cellen) voldoen. Het handmatig controleren van spreadsheetberekeningen door het invoeren van extreme en onverwachte gegevens om de robuustheid van de spreadsheet te beoordelen is vaak wel omslachtig. Efficiënter is om de code van elke formule te controleren: de source code review. In Excel biedt het tabblad formules hiervoor enkele tools. Bron- en doelcellen kunnen met behulp van pijlen worden weergegeven. Ook kunnen alle formules worden weergegeven en afgedrukt als validatiebewijs. Tevens worden met deze afdruk ook de gebruikte formules in de gevalideerde versie van de spreadsheet vastgelegd. Hierdoor is de spreadsheet altijd te reconstrueren. Het is wel belangrijk dat je voldoende bewijst dat de spreadsheet zal doen wat je ervan verwacht. Elke eis in de spreadsheet specificatie moet worden gevalideerd.

De filosofie is om je tijd te besteden aan het testen en niet aan het schrijven van testscripts. Voor spreadsheets met geringe complexiteit en waarbij alleen gebruik wordt gemaakt van simpele statistische rekenfuncties, kan worden volstaan met een standaard testprotocol. Dit protocol omvat de standaard controle van alle ontwerpeisen. Voor een controle van de overeenstemming met het beoogde doel wordt eenvoudigweg verwezen naar het betreffende werkvoorschrift: zijn de rekenfuncties in de spreadsheet geschikt om de werkwijze zoals beschreven in het werkvoorschrift te ondersteunen? Dit standaard testprotocol kan worden gebruikt voor elke spreadsheet van eenvoudige complexiteit. Is de spreadsheet wel complex: meerdere gerelateerde werkbladen, gebruik van macro’s, gebruik van geneste functies of functies die gebruik maken van dynamische broncellen (broncellen die zelf een functie omvatten), dan is het vastleggen en controleren met behulp van een spreadsheetspecificatie wenselijk. Wanneer een werkmap meerdere werkbladen bevat die niet onderling gerelateerd zijn dan kan per werkblad de complexiteit worden bepaald en de geschikte mate van validatie worden toegepast. Hierdoor wordt voldaan aan de principes van lean validatie of risico gebaseerde validatie.

Van de validatie moet objectief bewijs worden geleverd. Objectief bewijs bestaat uit gegevens die door een tweede persoon kunnen worden gereproduceerd als deze kwalificatie instructies op dezelfde manier uitvoert. Het zijn niet woorden “pass / fail”, “ja / nee”, “zoals verwacht,” of “true / false”. Dit zijn conclusies. Objectief bewijs toont de functionaliteit aan. Het kunnen onder meer afdrukken, screenshots, of schriftelijke beschrijvingen zijn van wat er is gebeurd of wat werd waargenomen door de tester. Dat maakt het valideren aan de hand van source code review zoveel efficiënter dan het testen aan de hand van testdata. Schrijf bij code review het referentienummer van de eis uit spreadsheet specificatie bij de formule waarmee deze eis wordt vervuld. Zo is objectief bewijs beschikbaar dat elke eis is geverifieerd.

Beheer van gevalideerde versies van spreadsheets

De gevalideerde spreadsheets moeten worden beheerd in overeenstemming met de GxP eisen voor documentbeheer. Vaak betekent dat dat de template wordt beheerd in een elektronisch document beheerssysteem. De spreadsheet zelf geeft in de kop- of voetnoot duidelijk aan wat de naam van de spreadsheet is en om welke versie van de template het gaat. Wanneer het nodig is om een kopie van de template te bewaren op een netwerk directory of op een bureaublad, dan moet bewijs van de installatie van de juiste gecontroleerde versie worden geleverd: de installatie kwalificatie. Wijzigingsbeheer verloopt ook volgens de GxP principes voor documentbeheer. Bij een wijziging van een contante waarde in de spreadsheet hoeft de functionaliteit niet opnieuw te worden gevalideerd. Dit is ook een reden waarom constanten in afzonderlijke cellen moeten worden geprogrammeerd, en dat formules die gebruik maken van de constante naar deze broncel verwijzen. Wordt een rekenfunctie (formule) gewijzigd, dan moet dit natuurlijk wel opnieuw worden gevalideerd. Een volledige validatie is dan niet nodig, je kunt je beperken tot de gewijzigde functie en natuurlijk een controle of de wachtwoordbeveiliging opnieuw is geactiveerd.

Advertenties

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit / Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit / Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit / Bijwerken )

Google+ photo

Je reageert onder je Google+ account. Log uit / Bijwerken )

Verbinden met %s

%d bloggers liken dit: