Meccano by Hornby // PerfectXL Spreadsheet Validation

PerfectXL Risk Eliminator

Array-formules zijn verleden tijd

Lang geleden was Excel nog lang niet zo geavanceerd als tegenwoordig, en de zogenaamde array-formules (ook wel matrixformules genoemd) bestaan al heel lang. Desondanks zijn er maar weinig mensen bekend met deze formules, en er zijn nog minder mensen die ze goed begrijpen. Misschien wel door het sterk afwijkende karakter van de formules of door de vreemde manier van activeren (CTRL-SHIFT-ENTER in plaats van gewoon ENTER).

Wat is een array-formule?

Een array-formule is kort gezegd een formule die heel slim meerdere berekeningen als een matrix met elkaar combineert. Bijvoorbeeld SUM(A1:A9*B1:B9;0) is de som van (A1*B1)+(A2*B2) etcetera. Het resultaat van de array-formule wordt alleen weergegeven als je dubbelklikt op de cel met de formule om deze te selecteren en vervolgens op CTRL+SHIFT+ENTER drukt. Daarom worden array-formules ook wel CSE-formules genoemd.

Het gevaar van array-formules

Formules die zo ingewikkeld zijn dat veel mensen ze niet begrijpen, kun je beter niet gebruiken, tenzij het echt niet anders kan. STel je voor dat je spreadsheet later in handen komt van een collega die niet met array-formules om kan gaan! Als hij per ongeluk een aanpassing maakt in de formule, maar niet op CTRL-Shift-Enter klikt, dan is je functionaliteit verdwenen met alle gevolgen van dien.

Alternatieven

Gelukkig zijn er vrijwel altijd alternatieven. Gebruik bijvoorbeeld een kolom extra voor een tussenberekening (A1*B1, zoals in het voorbeeld). Ruimtegebrek heb je sinds Excel 2010 zelden of nooit. Of gebruik nieuwe, slimme ingebouwde Excel functies zoals SUMIFS, MAXIF, en AVERAGEIFS, waarmee je ook vanalles kunt combineren.

Wanneer gebruik je array-formules wel?

Gebruik array-formules alleen als je zeker weet dat niemand anders er ooit aan hoeft te sleutelen (maar hoe weet je dat?) en als het heel belangrijk is om het geheugengebruik van de spreadsheet klein te houden. Want efficiënt, dat zijn ze zeker.

Hoe kan PerfectXL helpen?

Als je een spreadsheet analyseert met PerfectXL en je gaat naar Risico Overzicht, dan vindt je daar het onderdeel ‘complexe formules’. Dit onderdeel toont formules die complex zijn en daardoor moeilijk te onderhouden en potentieel riskant zijn voor toekomstige gebruikers. In dit onderdeel kun zien of er wel of geen array formules aanwezig zijn en waar deze zich bevinden. 

If you analyze your spreadsheet with PerfectXL and go to the risk overview, you will see a section called, “complex formulas.” This shows formulas which are complex and therefore difficult to maintain and understand, and potentially risky for future users. In this category you are able to see whether or not your spreadsheet contains array functions, and where those array functions are.

Hoe kan PerfectXL jou helpen?

Stel je vragen aan ons, we helpen je graag verder. We antwoorden binnen 48 uur.