Lijsten uit een tabel rapporteren

Voorbeeldbestanden bij dit artikel:
  • Switchen tussen add-in en basisbestand
  • 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 !




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links