Vermijd lange formules

“Hoe meer woorden, hoe minder zinvol [Prediker 6:11]”. Veel Excel gebruikers zijn meer gericht op cijfers dan op mooie uitspraken, maar dit citaat uit de bijbel past perfect bij dit onderwerp. Ook Thomas Jefferson, president van de Verenigde Staten aan het begin van de 19e eeuw sprak erover: “The most valuable of all talents is that of never using two words when one will do”.

Excel Guidelines voor Professionals - Vermijd lange formules :: PerfectXL

Minder is meer

Lange formules zijn slecht te lezen, slecht te begrijpen, slecht te controleren en slecht te verbeteren. “Maar het kan vaak niet anders”, horen we je al denken. Binnen jouw formule moet er nu eenmaal eerst iets worden opgezocht, dan vermenigvuldigd met die en die uitzondering, dan moet het nog worden afgerond en tenslotte moet er iets heel anders gebeuren als opgezochte waarde niet bestaat. Etcetera.

Wanneer is een formule te lang?

Wij hanteren als stelregel dat een formule te lang is als deze meer dan één regel beslaat. Ja, je kunt de formulebalk in Excel zo naar beneden slepen dat ook een formule over meer dan één regel in zijn geheel zichtbaar blijft, maar goed leesbaar is hij dan niet meer.

We kennen drie manieren om lange formules te voorkomen:

  • Zoek even door naar functies die beter op de gewenste berekening aansluiten. Bijvoorbeeld de functie SUBTOTAL geeft mogelijkheden voor (gefilterde) optellingen, waar je anders veel meer termen voor nodig hebt. Veel gebruikers denken daar niet aan.
  • Deel de formule op in deelberekeningen en plaats deze deelberekening in aparte cellen, rijen of kolommen. Het geheel wordt er een stuk overzichtelijker op. Onderzoek wijst helaas niet uit dat er daarmee ook minder fouten gemaakt worden, omdat de ketting van formules er navenant langer door wordt, hetgeen ook weer risico’s met zich meebrengt. Beetje twijfelachtig advies dus.
  • Kijk naar je datastructuur! Heel vaak zijn lange formules nodig omdat er in een eerder stadium al van alles is misgegaan. Problemen van slecht geordende inputdata en onvolledige tabellen moeten dan worden “gecompenseerd” door lange formules.

De aanpak van een software ontwikkelaar

Lukt het nu echt niet om je formules in te korten? Dan is er nog een noodgreep mogelijk: maak de opmaak van de formule zoals echte programmeurs dit zouden doen. Start door het formuleblok flink te vergroten. Splits door middel van ALT-Enter de formule op in logische blokken en gebruik spaties om geneste onderdelen van de formule mooi onder elkaar te zetten. Zo ziet de formule er al een stuk leesbaarder uit.

Excel Guidelines voor Professionals - Vermijd lange formules - Software ontwikkelaars :: PerfectXL

Wat kan PerfectXL voor je doen?

Ben je benieuwd naar de langste formule in jouw spreadsheet? Maak een account aan op PerfectXL, upload je spreadsheet en op de eerste pagina van de Excelsheetvalidatie staat direct de langste formule uit je spreadsheet. Voor velen een momentje van lichte schaamte.

PerfectXL zal je in het “Risk overview” ook wijzen op problemen als ‘Conditional complexity’ (teveel geneste IF’s), ‘Many different operations’ en ‘Referencing many cell groups’. Stuk voor stuk voorbeelden van slecht te lezen, slecht te begrijpen, slecht te controleren en slecht te verbeteren formules. Dankzij deze inzichten weet je waar de risico’s in je spreadsheet zich bevinden en kun je zelf bepalen of en hoe je deze wilt oplossen.

Meld je aan voor de Guidelines!