Code Refactoring Technieken: Overzichtelijke Spreadsheets

Code refactoring technieken laten je opruimen wat al werkt, zonder het kapot te maken. Bij een spreadsheet betekent dat: een formule die prima rekent, maar eruitziet alsof hij om 23 uur in elkaar is geflanst vlak voor een deadline, omzetten in iets waar je volgend kwartaal weer met plezier in kijkt.

Biscuit heeft hem al voor je opgehaald. Hier is wat er echt toe doet.

Je formule werkt, dus waarom voelt het toch verkeerd?

Het klassieke signaal dat je moet refactoren: je opent een bestand van zes maanden geleden en staat drie minuten naar één cel te staren voor je snapt wat hij doet. Of je kopieert een formule naar een nieuwe kolom en hij breekt stilletjes, omdat er ergens een harde rijverwijzing in verstopt zat.

Refactoren verandert de uitkomst niet. Het verandert de structuur, zodat het resultaat correct blijft wanneer de omgeving verandert: nieuwe rijen, nieuwe kolommen, een hernoemd tabblad.

Sla dit over als: je bestand een eenmalige berekening is die verder niemand aanraakt. Refactoren kost iets (tijd, onderbreking), en als de winst nul is, is het rendement dat ook.

De moeite waard als: meerdere mensen het bestand gebruiken, je het regelmatig bijwerkt, of formules over meerdere tabbladen heen aan elkaar hangen.

Een paar waarschuwingssignalen om serieus te nemen:

• Een formule die breder is dan de kolom op 100% zoom

• Meer dan twee niveaus geneste IF

• Harde getallen die op meer dan drie plekken terugkomen

• Collega's die vragen wat een cel doet, in plaats van het zelf te lezen

Eén van deze is al genoeg reden om een refactorsessie in te plannen.

Extract Method: knip één formule op in losse, benoemde stappen

Dit is dé nuttigste refactortechniek, in spreadsheets net zo goed als in code. Het idee: als één formule drie dingen doet, splits je hem op in drie cellen, één per stap, en combineer je daarna de resultaten.

Voor (één cel die alles doet):

=IF(AND(A2<>"",DATEDIF(A2,TODAY(),"Y")>30),"Senior","Junior")

Na:

• Cel C2: =DATEDIF(A2,TODAY(),"Y"), genoemd "Leeftijd in jaren"

• Cel D2: =AND(A2<>"",C2>30), genoemd "Is senior?"

• Cel E2: =IF(D2,"Senior","Junior"), de uiteindelijke uitkomst

Voilà, zo ziet dat eruit in een echte cel. Elke stap is op zichzelf leesbaar. Verandert de grens van 30 naar 25, dan pas je één getal in één cel aan. Breekt de DATEDIF omdat een datum ontbreekt, dan zie je precies waar.

De prijs: je gebruikt meer kolommen. Op een breed werkblad kan dat zonde aanvoelen. Gebruik dan een apart tabblad voor hulpcellen als kolomruimte schaars is. Noem dat tabblad iets als "Hulp" of "Berekeningen", zodat de opzet voor iedereen die het bestand opent meteen duidelijk is.

Werkt identiek in Excel en Sheets, er zit geen versiebeperking op het gebruik van hulpcellen.

Replace Temp with Query: stop met het opslaan van wat je ook kunt berekenen

Een tijdelijke waarde in spreadsheettermen is een cel die je vult met een tussenresultaat waar verder niets rechtstreeks naar verwijst, je had hem alleen nodig om bij de volgende stap te komen.

Soms zijn die nuttig (zie hierboven). Maar heb je een keten van vijf hulpcellen waarvan alleen de laatste ertoe doet, en veranderen de tussenliggende nooit, dan kun je ze samenvoegen tot één formule.

Drie hulpcellen die naar één resultaat toewerken:

• =B2*C2, subtotaal

• =D2*0.2, btw-bedrag

• =D2+E2, totaal

Samengevoegd:

=(B2*C2)+(B2*C2*0.2)

Of met een benoemd bereik voor het btw-tarief:

=B2*C2*(1+BtwTarief)

De vuistregel: gebruikt maar één andere cel de uitkomst van een hulpcel, vraag je dan af of die formule kort genoeg is om inline te zetten. Zo ja, doe het. Zo nee, houd de hulpcel en geef hem een fatsoenlijke naam.

Waar de grens ligt: een formule van meer dan 80 tekens is meestal te lang om inline te zetten. Dan werkt de Extract Method van hierboven beter. Bewaar hulpcellen zolang ze echt bijdragen aan leesbaarheid, haal ze weg zodra ze alleen maar ruis zijn.

Encapsulate Field: bescherm de invoerwaarden die niet mogen veranderen

Bij spreadsheets betekent encapsulatie dat je vaste invoerwaarden, tarieven, drempels, referentiewaarden, op één plek zet in plaats van verspreid als harde getallen in formules.

Dit is de formule die iedereen vergeet en die iedereen zoekt.

Een btw-tarief dat als 0.2 hardcoded staat in 47 formules door een hele werkmap is een onderhoudsval. Verandert het tarief, dan moet je alle 47 vinden. Mis je er drie, dan zit er een rekenfout in.

Oplossing:

1. Maak een tabblad Instellingen met een cel genaamd BtwTarief = 0.2

2. Verwijs ernaar in elke formule: =B2*BtwTarief

3. Verandert het tarief, dan pas je één cel aan. De rest volgt vanzelf.

Dit geldt ook voor datumverwijzingen (vandaag als vast ankerpunt), valutafactoren en categoriedrempels. Komt een waarde vaker dan twee keer voor en kan hij ooit veranderen, dan hoort hij in een benoemd bereik.

Zo maak je benoemde bereiken:

• Excel: tabblad Formules > Naammanager > Nieuw

• Google Sheets: Gegevens > Benoemde bereiken

De naamgeving is belangrijk. BtwTarief is duidelijk. BT is dat niet. tmp_3 ook niet. Kies namen die iemand die de formule niet zelf schreef in drie seconden begrijpt.

Duplicatie elimineren: het DRY-principe voor formules

DRY, herhaal jezelf niet is een van de meest aangehaalde principes in softwareontwikkeling, en het geldt rechtstreeks voor spreadsheets. Heb je dezelfde VLOOKUP met kleine variaties over 15 kolommen gekopieerd, dan heb je een onderhoudsprobleem in de maak.

Twee aanpakken, afhankelijk van je situatie:

Excel 365 / Google Sheets: gebruik XLOOKUP of INDEX-MATCH met array-uitbreiding om meerdere kolommen in één formule op te halen.

=XLOOKUP(A2,Opzoektabel[ID],Opzoektabel[[Naam]:[Status]])

Dit geeft zowel Naam als Status in één keer terug, geen gekopieer meer over kolommen.

Oudere Excel (2016 of eerder): maak één MATCH-oproep in een hulpcel en gebruik INDEX dat naar die positie verwijst:

• G2: =MATCH(A2,Opzoektabel[ID],0), één MATCH voor alle kolommen

• H2: =INDEX(Opzoektabel[Naam],G2)

• I2: =INDEX(Opzoektabel[Status],G2)

Je verwijst overal naar hetzelfde MATCH-resultaat. Verandert de opzoekkolom, dan werk je één formule bij.

Een versie-opmerking die het waard is: XLOOKUP kwam in 2019 beschikbaar voor Excel 365 en zakelijke abonnees. Werkt jouw team met Excel 2016, dan is INDEX-MATCH de aangewezen aanpak. Sheets ondersteunt XLOOKUP sinds 2022 op alle abonnementen.

Replace Conditional: geneste IF's vervangen door iets overzichtelijkers

Geneste IF's zijn de plek waar spreadsheets onleesbaar worden. Drie niveaus diep en je bent de meeste lezers kwijt, inclusief jezelf over zes maanden.

Voor (geneste IF-keten):

=IF(A2>10000,"A",IF(A2>5000,"B",IF(A2>1000,"C","D")))

Optie 1: IFS (Excel 2016+ / Sheets)

=IFS(A2>10000,"A",A2>5000,"B",A2>1000,"C",TRUE,"D")

Platter, maar nog steeds vier voorwaarden in één formule. Prima te doen.

Optie 2: VLOOKUP met een opzoektabel (de onderschatte aanpak)

Maak een kleine referentietabel:

• Vanaf 10.001: Categorie A

• 5.001 tot 10.000: Categorie B

• 1.001 tot 5.000: Categorie C

• 0 tot 1.000: Categorie D

Dan: =VLOOKUP(A2,DrempeTabel,2,TRUE)

De TRUE (benaderende overeenkomst) loopt de gesorteerde tabel af en geeft de laatste passende categorie terug. De logica staat nu in de tabel, niet verstopt in de formule. Verandert een drempel? Pas één rij in de tabel aan.

De extra moeite waard: de opzoektabel maakt de logica controleerbaar en zichtbaar voor iedereen, ook voor wie geen formules leest.

Sla over als je haast hebt: IFS is prima voor drie voorwaarden of minder.

De SWITCH-functie is een andere optie, beschikbaar vanaf Excel 2019 en in Sheets, handig als je exacte waarden matcht in plaats van bereiken. Hij leest overzichtelijker dan IFS bij exacte matches.

Het Substitute Algorithm: als herschrijven slimmer is dan repareren

Soms is de formule niet fout, hij is gewoon de verkeerde aanpak. Je erft een formule van 200 tekens die tekst aan elkaar plakt met CONCATENATE en &, en je moet er nog een veld bij zetten. De juiste zet is niet om hem te verlengen. Het is om hem te herschrijven met TEXTJOIN.

Voor:

=A2&" | "&B2&" | "&C2&" | "&D2

Na:

=TEXTJOIN(" | ",TRUE,A2:D2)

Als je tabel morgen van vorm verandert, blijft deze formule meelopen. TEXTJOIN handelt lege cellen af, werkt met arrays, en breekt niet als je kolom E toevoegt.

Dezelfde logica geldt voor:

• VLOOKUP vervangen door XLOOKUP als je exacte match plus afhandeling van ontbrekende waarden nodig hebt

• IFERROR-omwikkeling vervangen door de ingebouwde foutafhandeling van nieuwere functies

• Array-formules met Ctrl+Shift+Enter vervangen door native dynamische arrays in Excel 365

Hetzelfde principe schaalt op: onderhoud je een grote datapijplijn in een spreadsheet die is uitgegroeid tot een kluwen van INDIRECT-verwijzingen, externe koppelingen en benoemde bereiken die naar benoemde bereiken verwijzen, dan is de juiste refactorzet soms om de logica naar een echt hulpmiddel te trekken (SQL, Python, Power Query) en de spreadsheet alleen nog te gebruiken om te tonen. Dat is een grotere beslissing, maar hoort in dezelfde categorie thuis.

Voor je begint, en wanneer je moet stoppen

Code refactoring technieken werken alleen als je niets kapotmaakt wat je niet kunt herstellen. Bij een spreadsheet betekent dat drie dingen op orde hebben voor je begint:

1. Een back-up. Bewaar voor elke refactorsessie een gedateerde kopie van de werkmap. Geen Opslaan als in dezelfde map, maar een aparte plek (cloudback-up, mail naar jezelf, wat dan ook werkt). De ene keer dat het misgaat, ben je blij dat je dit deed.

2. Weet wat de huidige uitkomst hoort te zijn. Noteer voor je iets verandert de belangrijkste waarden: totalen, samenvattende cijfers, kerncellen. Vergelijk ze na elke wijziging met dezelfde cellen. Een controle van tien seconden die regressies meteen opvangt.

3. Eén wijziging tegelijk. Doe niet in dezelfde sessie een Extract Method, drie benoemde bereiken en een platgeslagen geneste IF. Gaat er iets stuk, dan wil je weten welke wijziging de oorzaak was. Doe één ding, controleer, ga dan verder.

Deze drie stappen kosten ongeveer vijf minuten om op te zetten. Ze hebben ruimschoots meer dan vijf minuten bespaard op elke refactorsessie waarin iets onverwachts gebeurde.

Het risico bij refactoren is perfectionisme. Je kunt een hele middag besteden aan het herschikken van een werkmap die al functioneerde, en eindigen met iets marginaal netters ten koste van een halve dag.

Zet vooraf een tijdslot: 45 minuten hiervoor. Geef voorrang aan de formules die actief problemen veroorzaken, die mensen verkeerd lezen, die breken bij normaal gebruik, die een nieuwe functie blokkeren die je moet toevoegen.

De rest: laat het liggen. Een formule die werkt, die niemand aanraakt, die het afgelopen jaar geen fout heeft veroorzaakt, is prima zo. Goed genoeg is een geldig eindpunt.

Een bruikbaar denkkader: refactoren gaat niet over een perfecte werkmap. Het gaat erom de volgende wijziging makkelijker te maken. Vraag jezelf voor elke wijziging af: maakt dit de volgende stap sneller of veiliger? Zo ja, doe het. Zo nee, stop.

Goed uitgevoerde refactoring valt niemand op. Dat is precies het doel.