Klein maar krachtig

  • 04/04/2021
Voorbeeldbestanden bij dit artikel:
  • Klein maar krachtig

    • Europese voetbalploegen en hun inkomsten

    Dag allemaal! Vandaag wil ik jullie graag een klein bestand tonen, zodat je alle informatie kan opnemen, maar toch een heel krachtig bestandje dat boordevol Excel tips and tricks zit. Eerlijk waar, er zitten een heleboel Excel goodies in die de gemiddelde Excel gebruiker nog niet kent of gebruikt. Iedereen zal in staat zijn om een paar tricks te leren.

    Laten we eens kijken naar hoeveel inkomsten de top 20 Europese voetbalploegen genereerden tijden het seizoen 2019/2020. Ik zal jullie omzeggens elke stap uitleggen die ik deed om te komen tot het resultaat wat je ziet in het bestand; zelfs meer, dingen die je nu nog niet ziet maar er toch zijn.

    Ondanks het feit dat het over voetbal gaat in het voorbeeld, zullen de gebruikte methoden en logica toegepast kunnen worden voor bvb. de financiële rapporten van de baas of zelfs in eender welke belangrijke Excel spreadsheet die je hebt.

    Je kan het Excel bestand downloaden bovenaan deze pagina. De onderstaande afbeeldingen zullen dienen ter ondersteuning bij het lezen van de tekst.

    Stap 1: de ruwe data

    Zoals je je wel kan inbeelden ben ik gestart met een leeg Excel bestand en deed ik de invoer voor de landnamen, voetbalploegen en gegenereerde omzetten. Zo simpel mogelijk. Geen opmaak is nodig op dit moment van ingave ! Ik voerde de getallen in in miljoen euro maar met 1 cijfer na de komma, zodat ik het niveau van detail van de aangeleverde internet data aanhield. Nadien zou ik die decimalen toch verbergen. Je zou kolom A leeg kunnen laten om niet te moeten starten tegen de linker marge, weet je, het zou een klein claustrophobisch effect kunnen geven ;-) Voor nu zijn de totalen per land nog niet belangrijk. Je kan eenvoudig een fake totaal van 100 ingeven of een simpel totaal met de SOM formule, dat maakt nu nog niet veel uit. Nadien, en dat is alvast een spoiler waarschuwing !, zullen we een AGGREGAAT formule toevoegen.

    Stap 2: opmaak

    Ik voegde de gele opmaak en onderbroken randen toe voor de cellen van de landnamen. Daarna voegde ik de opmaak toe voor de namen van de voetbalploegen, dat is, de getalopmaak om het visuele effect te hebben van "een lijst van teams". Ik maak slechts 1 cel op, gewoonlijk de eerste cel bovenaan. Nu komen we zo aan de eerste krachtige truc in Excel: stijlen. Met een geformatteerde cel geselecteerd, creëer een nieuwe stijl en geef die een naam. Zo maakte ik 6 aangepaste stijlen:

    1. Stijl_Heading_Naam: geel met een onderbroken rand, vet en ingesprongen
    2. Stijl_Heading_Getal: geel zonder een rand, vet, geen decimalen
    3. Stijl_Heading_Percentage: een percentage notatie voor de getalopmaak, geen decimalen, schuin, tussen haakjes
    4. Stijl_Lijn_Naam: een liggende streepje wordt ingevoegd als het eerste karakter. Het behoort niet tot de inhoud van de cel en je typt het niet !
    5. Stijl_Lijn_Getal: geen achtergrondkleur noch rand, but geen decimalen. Niet te veel poespas, het is gewoon een getal
    6. Stijl_Total_Getal: groen met een onderbroken rand, vet

    Het is nu onze taak de (relevante) stijlen toe te passen op alle cellen die je nodig hebt. Je kan de cellen selecteren en de naam van de stijl kiezen in de Start tab van het lint, in de groep Stijlen, of gewoon de opmaak van een soortgelijke cel kopiëren/plakken. Altijd goed om een alternatieve methode te kennen: als je een dubbelklik doet op de verfborstel van Opmaak kopiëren/plakken, dan kan je opmaak toepassen op meerdere bereiken, 1 na 1.

    De belangrijkste voordelen van het gebruiken van deze aanpak:

    • aanpasbaarheid: het wordt nu kinderlijk simpel om de opmaak van een specifieke stijl en alle cellen in het bestand (met die stijl toegepast) onmiddellijk aan te passen. Ik ben er zeker van dat jouw baas op kantoor misschien niet de gele opvulkleur leuk vindt: verander die overal naar lichtgroen in een kwestie van seconden! Of gebruik Excel's ingebouwde kleurpaletten.
    • onderhoudbaarheid: het bestand wordt veel beter onderhoudbaar en verwordt niet tot een gedrocht of een kleurboek waar menig kleuter jaloers op zou zijn!

    Stap 3: voeg lege rijen in

    Dit is een zeer belangrijke stap om jouw bestand bestand te maken tegen de toekomst en de onderhoudbaarheid te vergroten. We doen dit in het licht van de formules die verderop toegevoegd worden voor de cellen met subtotalen (stap 4). Voor elk blok met voetbalploegen binnen hetzelfde land, voeg een lege rij toe bovenaan en onderaan. Verklein de rijhoogte van deze 2 rijen zodat het niet afleidt voor de gebruikers. In de formules zullen we de bereiken uitrekken tot 1 rij boven en tot 1 rij onder de cellen met inkomsten. Zodoende, wanneer we een lege rij invoegen voor een nieuwe voetbalploeg dan zullen de formulebereiken automatisch mee aangepast zijn. We moeten zelf de bereiken niet meer gaan bijwerken. Heel veel Excel gebruikers zouden een rij invoegen onder de laatste voetbalploeg, en dat wordt nu opgevangen door onze formules.

    Zoals gezegd, de belangrijkste voordelen van deze aanpak:

    • onderhoudbaarheid: je hoeft typisch gezien niet meer de formules aan te passen op een later moment wanneer je consistent deze techniek toepast. Bereiken voor formules zullen automatisch uitbreiden en inkrimpen.

    Stap 4: formules om op tellen

    We werken in Excel, dat weten we, dus we kunnen en zullen formules toevoegen. Formules om omzetten op te tellen tot landtotalen, bijvoorbeeld. Hoe verhoudt het VK zich tot Spanje ? Jazeker, een simpele SOM formule zou dit kunnen doen maar onthoud dat jouw spreadsheet veel complexer zou kunnen zijn/worden in termen van groepen en niveau's: je kan mijn techniek toepassen op een 5 niveau's diep geneste hiërarchie aan gegevens. Denk maar eens aan een gedetailleerd financieel rekeningenstelsel met vele verdiepingen. Laat mij jullie kennis maken met de AGGREGAAT functie in Excel: een veelzijdige functie die, welja, onderliggende data kan aggregeren. Som is maar 1 van de mogelijke uitkomsten, je kan ook gemakkelijk overschakelen naar gemiddelde, maximum, minimum, aantallen, ... met een kleine inspanning en zonder de formule te veranderen naar GEMIDDELDE, MAX, MIN, AANTALARG, etc. Zei ik niet dat het een krachtige en veelzijdige functie is ?

    Kijk aub eens naar de functies in de gele cellen. Voor meer informatie lees je gerust mijn eerder artikels over de AGGREGAAT functie en de SUBTOTAAL functie in Excel.

    Om de flexibiliteit van de functie uit te buiten, heb ik de 2 argumenten van de functie niet hard neergezet. In plaats daarvan heb ik 2 namen gebruikt, wat op zichzelf weer een nieuwigheid is voor nogal wat Excel gebruikers die deze functionaliteit nooit eerder gebruikt hebben. Door een naam centraal te definiëren, en eraan te refereren in de formulas, kan je gemakkelijk "spelen" met jouw bestand. Onmiddellijk verander je een heleboel sommen in gemiddelden of aantallen of wat nog meer.

    Het leidt geen twijfel dat opnieuw de belangrijkste voordelen zijn:

    • aanpasbaarheid: verender (de uitkomsten van) formules in seconden, voor een heel werkboek.
    • maak indruk op jouw bazen en collega's

    Stap 5: het algemeen totaal

    Nu hebben we een punt bereik waar je opnieuw het extra geleverde werk uit stap 4 gaat exploiteren. Je had gewoon simpele SOM formules kunnen maken voor de relevante bereiken en het zou nog gewerkt hebben ook. Echter, je zal nu nog meer begrijpen waarom ik de AGGREGAAT formule toepas: het algemeen totaal over alle voetbalploegen zal ook gemaakt worden met een AGGREGAAT formule ! Door dit te doen zal de AGGREGAAT formule, zelfs al toegepast over ALLE rijen met inkomsten, met gemak de cellen overslaan die zelf een AGGREGAAT formule bevatten ! Dit betekent dat wij niet moeten wakker liggen van dubbeltellingen. Het algemeen totaal, berekend over alle cellen die invoer en subtotalen bevat, houdt enkel rekening met de ruwe data. Hoe goed is dat ? Niet meer dit type van formules: =D2 + D14 + D22 + D31 + D40. Want je weet best hoe dat gaat: een collega voegt een nieuw land toe volgend voetbalseizoen en vergeet het algemeen totaal aan te passen. Boem ! Nu zijn we ingedekt.

    We zullen de cel van het algemeen totaal ook een naam geven: totaal. Deze naam zal later terugkomen.

    Voordelen:

    • onderhoudbaarheid
    • geen duh-momenten meer ten aanzien van de baas omdat je vergeten bent om die nieuwe groep op te tellen in het financiële rekeningenstelsel.
    • maak indruk op jouw bazen en collega's

    Stap 6: bereid een visuele voorstelling voor

    Een beeld zegt meer dan 1000 woorden, we weten het allemaal. Daarom gaan we een grafiek toevoegen die de data weergeeft. We hebben de data links georganiseerd zodat data-invoer en presentatie gemakkelijk waren (in een PowerPoint slide bijvoorbeeld), maar eerlijk waar, dit maakt het niet gemakkelijk voor de grafiek. We moeten de data wat herwerken voor onze grafiek.

    Laat mij toe jou te introduceren tot dynamische array formules. Zij zijn vrij nieuw binnen Excel en ik ben er zeker van dat ik ze al eens een paar keer besproken heb in mijn blog. Je zal een relatief recente Excel versie nodig hebben om ze te kunnen gebruiken. Kijk eens naar de cellen T5:W5. Die zullen uitbreiden en inkrimpen afhankelijk van het aantal cellen dat ze moeten weergeven.

    Als we 1 stap vooruit denken: wanneer we de grafiek toevoegen en refereren aan de cellen in dit bereik (wat verborgen zou kunnen zijn), dan moeten we dat bereik ook best een naam geven om het dynamische aspect te vatten. Het probleem is met name dat grafiekreeksen op dit moment nog niet overweg kunnen met dynamische bereiken van zulke nieuwe formules. Echter, grafieken kunnen wel reeksen plotten op basis van namen, en namen kunnen dynamische bereiken capteren ! Probleem opgelost dus. Een kleine zijstap laat ons m.a.w. toe om volledig dynamische grafieken te tekenen. Ik vind aangepaste labels ook altijd een pluspunt net als het beschikbaar stellen van informatie direct op de grafiek.

    Laten we deze dynamische bereiken maken:

    • Percentages: het verwijst naar voetbal!V5#, wat op dit moment overeenkomt met V5:V9
    • Labels: het verwijst naar voetbal!W5#, wat op dit moment overeenkomt met W5:W9

    Stap 7: voeg een visuele voorstelling toe

    Voor deze toepassing verkies ik het gebruik van een treemap. Het is standaard onderdeel van Excel en er is geen noodzaak aan eigen aanpassingen. We gebruiken de namen die we in de vorige stap gecreëerd hebben om de gegevens en de labels te plotten. Je kan de treemap lezen van links naar rechts en van boven naar onder.

    Merk ook de gele afgeronde rechthoek op rechtsboven in de grafiek. Het is simpelweg een opgemaakte vorm. Wanneer we op de vorm klikken en daarna in de formulebalk klikken, dan kunnen we typen: =Totaal. Ja, dat is de naam die we eerder gecreëerd hebben.

    De voordelen van de visuele voorstelling omvatten:

    • aantrekkelijkheid: de grafiek is niet moeilijk te lezen en interpreteren. De waarden zijn van groot naar klein gerangschikt. Het ziet er goed uit. De labels vertellen ons de landnamen, de waarde in absolute en relatieve groottes, mooi opgemaakt.
    • Relatieve verhoudingen zijn duidelijker dan met enkel data in een tabel.
    • Evolutie van de absolute getallen over tijd: voor een overzicht over 5 seizoenen zou je typisch gezien 5 zulke grafieken hebben maar denk ook na wat 100% voorstelt. Het algemeen totaal kan veranderen over seizoenen dus een visuele indicatie voor het publiek is nodig.

    Stap 8: de datum van de meest recente update

    Als dit een werkboek voorstelt wat je regelmatig zal bijwerken, zoals in mijn geval het werkboek dat aan de basis ligt van dit werkboek, dan heeft het zin om de datum van de laatste bijwerking in het werkboek vast te leggen. Dat doe ik rechtsonder, cel Q44. Opnieuw een truc: je moet de datum niet zelf invoeren, een sneltoets combinatie is Ctrl + ;. Wil je een statische versie van zowel datum als tijd, dan kan je dit zeer snel doen als volgt:

    1. Selecteer cel Q44
    2. Druk: Ctrl + ;
    3. Type een spatie
    4. Druk: Ctrl + Shift + ;
    5. Druk Enter

    Stap 9: andere ideeën

    Dit artikel is klaarblijkelijk langer uitgevallen dan ik voor ogen had, en ik wil hier afsluiten met nog een aantal gedachten voor jullie.

    • Je zou de "Groeperen" functionaliteit in Excel kunnen gebruiken om rijen te groeperen, en dan rijen open klappen/samen vouwen - afhankelijk van het publiek.
    • Verzeker jezelf ervan dat de grafiek nog steeds de gegevens plot als je rijen verbergt, want in Excel is er een instellingen die zegt dat verborgen cellen wel of niet geplot dienen te worden.
    • Ga eens naar de grafiekeigenschappen en kijk eens naar de andere customizaties. Ze kunnen jouw grafiek/werkboek omvormen tot een meer visuele ervaring, maar overdrijf er zeker niet in.
    • Je zou een kleine VBA procedure kunnen maken om de actieve (geselecteerde) rij te dupliceren. Ik heb dat zelf in mijn Persoonlijk.xlsb macro werkboek gedaan. Het is zeer nuttig en bespaart mij veel tijd.

    I hoop van ganser harte dat jullie een aantal nieuwe technieken geleerd hebben en dat je die zal toepassen in jullie spreadsheets !




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links