Lijsten uit een tabel rapporteren
- 14/11/2021
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Formules, formules, formules, … Iedereen moet ze gebruiken in Excel. En in andere software producten ook, zoals TM1. Terwijl we de relevante formule kunnen opzoeken in (online) hulpbronnen, is het geen slecht idee om je eigen lijst van formules samen te stellen. Misschien ben je enkel geïnteresseerd in sommige formules en niet alle formules ? Misschien wil je het rapport een eigen twist geven ? Dit is wat ik hier wil tonen: een onderhoudbaar, herbruikbare, rapportering.
Je kan opwerpen dat het opstellen van het rapport een eenmalige taak is. Formules veranderen toch niet vaak ? Neen dat zullen ze niet vaak doen. Echter, bepaalde formules worden toegevoegd, andere formules worden stopgezet. Derhalve de lijst is geen vaste lijst. Kijk maar even naar Excel en de nieuwe functies die we gezien hebben de laatste jaren.
Als je een mooie layout wil, kan dit gevolgen hebben voor de onderhoudbaarheid. Nodeloos om te zeggen dat het werk zo veel mogelijk wensen te beperken, en Excel het werk voor ons laten doen. Het idee is dat een tabel bevat de functies en de informatie, waarbij zoekfuncties ze ophalen en in een mooie one-pager tonen.
We zien bij wijze van voorbeeld de lijst van TM1 functies die in Turbo Integrator gebruikt kunnen worden. De functies worden onderverdeeld in categorieën. Binnen elke categorie hebben we een lijst van functie namen, alfabetisch gerangschikt. Elke categorie heeft 2 kolommen. In de layout van de rapportering hebben we 2 blokken: links en rechts. Het is simpelweg een Excel bestand waar bepaalde cellen opgevuld worden - niet op de gewone manier van namen intypen (of kopiëren/plakken) maar eerder via formules die de namen ophalen uit een tabel op een ander werkblad.
De tabel ziet eruit als volgt. Alle cellen bevatten tekst (kopiëren/plakken waar mogelijk, en opkuisen van de teksten voor de consistentie).
Het kostte me de nodige tijd om met de lijst van functies te komen, de echte lijst is veel langer zoals je je wel kan inbeelden. De lijst bijgewerkt houden is ook een uitdaging !
Aan functienamen refereren en ze ophalen
In de (verborgen) kolommen G, H, J en K, zet ik de nummers van de functies zoals ze voorkomen in de kolom "Naam_cheat" in de tabel. Lijn per lijn heeft elke formule dezelfde horizontale "afstand" in kolommen:
- Kolom A kijkt in kolom G
- Kolom B kijkt in kolom H
- Kolom D kijkt in kolom J
- Kolom E kijkt in kolom K
Dit is belangrijk: het zorgt ervoor dat we gewoon de formule kunnen kopiëren/plakken naar alle andere "cellen" en de relatieve celverwijzingen zullen blijven werken. De formule zelf is INDEX/VERGELIJKEN, dus niet zo schokkend.
Echter, 1 groot probleem wordt zichtbaar. Wat als nieuwe functies toegevoegd worden, andere worden verwijderd ? De opzet is niet erg dynamisch. Zelfs als we cellen ophogen in de verborgen kolommen (de vorige cel +1 doen), dan nog is het verre van ideaal.
In een volgend artikel zal ik de beperking wegnemen door het gebruik van dynamisch matrix functies.
Wat is al wel mooi, is het voordeel dat we hebben van 2 eerder kleine VBA procedures:
- Een Selection Change event: klik op een formule naam en aan de rechterkant zullen de details getoond worden
- Een Double click event: dubbelklik op een formule naam en je zal naar de rij van de functie in de tabel gebracht worden
Indien geïnteresseerd, neem gerust een kijkje in de VBA code in het werkboek. Naast de 2 besproken VBA events zijn er nog andere nuttige procedures. Sommige daarvan moeten misschien licht aangepast worden want dit werkboek is maar een klein stuk uit mijn volledige cheat sheets.
Deze topic wordt vervolgd !
