Excel: Tips voor sustainable spreadsheets

Na het lezen van dit artikel weet je:
•    Hoe verstandig om te gaan met getallen in Excel
•    Hoe tabbladen het beste gelinkt kunnen worden
•    Hoe errors goed afgehandeld moeten worden

Het gebruik van vaste waarden
Als je binnen een Excelsheet gebruikt maakt van een constante (bijvoorbeeld: het belastingpercentage, of een marge) dan ben je vaak geneigd om die direct in de formule te zetten, zoals je kunt zien in afbeelding Fixed1. Hoewel dit natuurlijk makkelijk is en snel gaat, is het toch verstandiger om de waardes in een aparte cel te zetten, het liefst zelfs op een apart tabblad. Dit is beter om twee redenen:
•    Het is duidelijker wat er precies bedoeld wordt, als de waarde apart staat met een beschrijving erbij, weet een toekomstige lezer waarom die waarde gebruikt wordt.
•    Als de waarde ooit verandert, bijvoorbeeld omdat de belasting verhoogd wordt, hoeft dit maar op 1 plek in de sheet aangepast te worden. Dat is veel minder werk, en minder foutgevoelig dan alle getallen aanpassen met een ctrl-h.

Linken naar een ander tabblad
Als je met meerdere tabbladen werkt, komt het vaak voor dat je data van het ene tabblad nodig hebt voor een berekening op een ander blad. In het voorbeeld in STORE willen we controleren of de prijzen die afgerekend zijn via de kassa overeenkomen met de prijzen in dit spreadsheet. Vaak doen mensen dat met een formule waarin meerdere koppelingen tegelijk worden gelegd.

In het voorbeeld wordt zowel data opgehaald uit CheckoutInput met een SUMIF, als uit Storeitems met een VLOOKUP. Dit is niet zo verstandig, aangezien het nu lastig te controleren is welke data wordt binnengehaald. Misschien staat in CheckputInput wel alles op 0. Maar omdat er hier meteen mee gerekend wordt, is dat niet meer te zien. Daarom adviseren wij om eerst alle data binnen te halen met een simpele link, een link waarin maar van 1 plek data verzameld wordt. Zo kun je eerst de binnengekomen data bekijken en bepalen of deze klopt voldoet aan je verwachting. Zie store2 en store3. Dit vermindert de kans op fouten zeker.

Data ophalen met een conditionele formule of met een directe link
Een simpele link, eentje met een referentie naar maar 1 ander tabblad, kun je nog op twee verschillende manieren uitvoeren. Of je maakt gebruik van een conditionele formule, bijvoorbeeld een VLOOKUP of een SUMIF, dat zie je in figuur VLOOKUP. Die halen precies de data op die je nodig hebt. Alternatief kun je een directe link gebruiken, zoals in DIRECT. De functionaliteit is hetzelfde, maar let op, met een directe link kunnen makkelijker problemen optreden. Als de data in Storeitems gesorteerd wordt bijvoorbeeld, dan kloppen de linkjes niet meer. Daarom is het altijd verstandiger om een koppeling te maken die de juiste data opzoekt.

Het afvangen van fouten
Wist u dat sinds Excel de handige formule IFERROR bestaat? Dit is een samenvoeging van de formules ISERROR en IF, en stelt u in staat om fouten veel makkelijker te verwerken. Waar u vroeger nog deze formule gebruikte

=IF(ISERROR(VLOOKUP(A2,StoreItems!A:F,3,FALSE)),0,VLOOKUP(A2,StoreItems!A:F,3,FALSE))

Kunt u nu toe met het veel simpelere

=IFERROR(VLOOKUP(A2,StoreItems!A:F,3,FALSE)),0)

Deze formule is naast veel fijner leesbaar, ook nog eens efficiënter, omdat Excel maar 1 keer data hoeft op te halen.

Let op met lege argumenten!
Een constructie die vaak tot verwarring leidt, is het gebruik van lege argumenten in een formule, zoals de formule in Empty1

=IFERROR(VLOOKUP(A2,StoreItems!A:F,3,FALSE),””)

Hier wordt in plaats van een 0, een lege tekst weergegeven als ere en fout optreedt. Het nadeel hiervan is dat een fout eigenlijk wegval in plaats van opvalt. De aandacht dat gebruiker wordt er helemaal niet naartoe getrokken, hij wordt juist een beetje weggepoetst. Dit leidt soms tot problemen, omdat je er pas op een later moment achterkomt dat er iets mis is. Daarom adviseren wij om in plaats van een lege tekst, juist een fouttekst weer te geven:

=IFERROR(VLOOKUP(A2,StoreItems!A:F,3,FALSE),”Let op, waarde niet gevonden!”)

Zo vallen fouten op, en worden ze eerder gecorrigeerd.

Let om met het combineren van IF formules
In experimenten met spreadsheetgebruikers hebben we gezien dat een ander punt van fouten vaak het combineren van meerdere IF statements is. In afbeelding Cond1 zie je hiervan een erg extreem voorbeeld, dat wel uit een echte sheet komt. Om zo’n formule uit te pluizen moet je als het ware met een kan koffie in een stille ruimte gaan zitten, want dat kost echt wel een paar uur. Je moet alle opties opschrijven en uitzoeken wat het resultaat zal zijn. Fouten worden dan ook veel sneller gemaakt in dit soort ingewikkelde formules, dan in een simpele formule met maar  1 IF. Daarom is het beter om zulke formules uit te splitsen in meerdere stappen, zodat de lezer wat meer ‘aan de hand’ genomen wordt en de stappen 1 voor 1 kan doorlopen. Dat is sneller, en veel minder foutgevoelig.

Denk aan de toekomst!
Onthoud dat een spreadsheet door  gemiddeld 13 mensen gebruikt wordt en hou dit in gedachten als je een formule maakt. Snap je die over een paar maanden zelf nog? En denk je dat je collega’s er ook mee overweg zullen kunnen? Wetenschappelijk onderzoekt toont aan dat spreadsheets die met deze gedachten ontwikkeld zijn, veel simpeler zijn en minder fouten bevatten, dan spreadsheets die ‘ad hoc’ gebouwd worden. Bezint eer ge begint is dus ook voor spreadsheets een goed uitgangspunt.

Felienne Hermans, eigenaar van Infotron heeft jarenlang onderzoek gedaan aan de TU Delft naar het bouwen en onderhouden van spreadsheets. In die tijd heeft ze een passie ontwikkeld voor het ondersteunen gebruikers om het meeste uit Excel te halen, waarbij de kwaliteit gewaarborgd blijft. Hiervoor zet ze nu met Infotron software en advies in de markt.

__________________________________________________________________________________
Klassiekers uit het archief van Financieel-Management.nl:
10 veel voorkomende fouten in Excel
10 veel voorkomende fouten in Excel – deel 2

Bekijk ook de inspirerende video’s:
Excel Video #1: Winstmaximalisatie met de oplosser
Excel Video #2: Bekijk alle opties met ‘wat-als’ analyses
Excel Video #3: Overzicht in een handomdraai met een ‘histogram’
Excel Video #4: Elimineer fouten met snijpuntoperatoren
Excel Video #5: Boek tijdswinst met draaitabellen
Excel Video #6: Foutloos vermenigvuldigen met ‘Productmat’
Excel Video #7: Effectief Formules controleren
Excel Video #8: Optimale rooster- of werkplanning
Excel Video #9: Sneller rekenen met ‘plakken speciaal’
Excel Video #10: Snel foutmeldingen opsporen en herstellen

Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus Investeringsanalyses met Excel
Cursus VBA Excel