Een voorkeurslijstje genereren met opmaak
- 15/11/2020
 | Voorbeeldbestanden bij dit artikel: |
Favoriete reisbestemmingen
De Excel product teams hebben het laatste jaar een aantal nieuwigheden geïntroduceerd, o.a. dynamic arrays. Het komt erop neer dat we anders gaan kijken naar Excel functies en hun argumenten, voor bestaande functies, en dat er ook een aantal nieuwe functies toegevoegd worden aan Excel. In dit artikel toon ik enkele van deze functies, net als een voorbeeld van wat ik bedoel met bestaande functies anders gaan gebruiken. In zekere zin vervangen die dynamic array formulas bestaande matrixformules (die je ingeeft in Excel met Ctrl + Shift + Enter), en gaan ze bestaande ellenlange formules drastisch verkorten en versimpelen !
Terwijl we allemaal in een verplichte lockdown zitten kunnen we gaan dromen van de favoriete reisbestemmingen… Stel dat je er een aantal in een tabel in Excel hebt gezet, en dat je dan met nummertjes gaat aangeven wat de voorkeuren zijn: 1 is de grootste voorkeur, dan 2, dan 3, enz. Een ex aequo is ook mogelijk, zoals 3 nummers-2. Het doel is dan om automatisch een lijstje te genereren van de gekozen reisbestemmingen, in volgorde. Geen VBA-code, geen events, geen PowerQuery, geen manuele interventies. Enkel Excel formules ! En… als toemaatje willen we de rode opmaak als rand bij de gegenereerde tabel: het aantal rijen is dynamisch want hangt af van hoeveel reisbestemmingen we kiezen. Hoe kunnen we dit allemaal doen ? Ik help jullie op weg. Bekijk eerst de afbeelding en download het voorbeeldbestand bovenaan de pagina.
In cel A1 start onze tabel, een tabel in de Excel zin van het woord, een ListObject met andere woorden. Dat zie je aan het blauwe icoontje rechts onderaan in cel E6, de laatste cel van de tabel. Excel verzorgt automatisch de opmaak voor ons (de even en oneven rijen bvb.) en zorgt ervoor dat formules doorgetrokken worden in alle rijen van de tabel.
Dan kan je in kolom C ("Invoer") nummers gaan zetten. In principe 1, 2, 3, … maar je kan evt. ook andere nummers nemen. Het kleinste getal krijgt de grootste voorkeur, dan het tweede kleinste, enz. Nummers mogen dubbel gebruikt worden. Kolom D ("Uniek") gaat dan aan de slag met kolom C en geeft allemaal unieke getallen. De formule houdt rekening met lege cellen in kolom D, alsook ex aequo's. De bedoeling zal dan zijn om te sorteren op kolom D, via een formule. Laten we eerst nog kolom E ("Uitvoer") bekijken: daar zetten we een bullet point voor de naam van het land. Niet via de celopmaak maar met een karakter dat zo'n bullet point vertegenwoordigt. Later in dit artikel krijg je een overzicht van de formules te zien.
De tabel is nu duidelijk. We springen dan naar de complexe formule in cel B9 voor de lijst van gekozen landen:
=VERT.ZOEKEN( SORTEREN( FILTER( Tbl_Land[Uniek]; Tbl_Land[Invoer] > 0 )); Tbl_Land[[Uniek]:[Uitvoer]]; 2; 0 ) De formule is complex maar van binnen naar buiten gelezen:
- Tbl_Land[Uniek]: de gelijknamige kolom uit de tabel (die ik "Tbl_Land" noemde)
- Tbl_Land[Invoer]: de gelijknamige kolom uit de tabel
- FILTER( Tbl_Land[Uniek]; Tbl_Land[Invoer] > 0 ): geef mij alle waarden uit de kolom "Uniek" waarbij de kolom "Invoer" groter is dan 0 - telkens voor dezelfde rij
- SORTEREN( VORIGE LIJST ): die lijst van waarden gaan we sorteren, oplopend van klein naar groot
- VERT.ZOEKEN( VORIGE GESORTEERDE LIJST; Tbl_Land[[Uniek]:[Uitvoer]]; 2; 0 ): we gaan nu elk van de (3) getallen opzoeken in de kolom "Uniek" en als resultaat met een VERT.ZOEKEN() halen we de "Output" op ("Output" is de 2de kolom uit het bereik Tbl_Land[[Uniek]:[Uitvoer]])
De namen van kolommen en tabellen hoef je allemaal niet zelf in te typen. Je kan met de muis de nodige selecties maken en Excel vult het voor jou in. In cel A9 hebben we een veel eenvoudigere functie: we zien het aantal items in de geretourneerde lijst in de cellen ernaast. De syntax is wellicht nieuw voor velen onder jullie: het is het karakter #. Dat karakter # vormt cel B9 om tot de "spill range" B9. Momenteel wordt de spill range B9:B11, omdat de gigantische formule waarden geeft in het bereik B9:B11. Morgen kan het alweer anders zijn, als we meer of minder favoriete reisbestemmingen hebben.
Dit is heel belangrijk: laat plaats onder de "spill range" want het zou kunnen dat je in de toekomst meer landen kiest. Dan moet Excel plaats hebben om ze te tonen!
Een dynamische rand
Met behulp van de RIJ() functie kunnen we dit probleem oplossen. Bekijk het eens rustig in het bestandje, dit is niet zo heel lastig. Lastig is wel dat je 3 verschillende regels bij voorwaardelijke opmaak moet instellen. Dat had handiger gekund Microsoft, maar goed.
Samenvatting
We hebben in dit artikel gezien dat er nieuwe functies aan Excel toegevoegd werden, naast FILTER() en SORTEREN() zijn er nog andere. Die bekijken we op een andere keer wel eens. Tevens hebben we de VERT.ZOEKEN() functie (die bestaat) toegepast op een dynamisch bereik, waardoor die VERT.ZOEKEN() ook niet 1 maar een dynamisch aantal resultaten gaat teruggeven. Tot slot hebben we opmaak toegevoegd aan een dynamisch bereik, ook hier weer zonder VBA-code en enkel ingebouwde Excel functionaliteiten. Door deze methode hoeven we nu niet meer manueel te gaan filteren, sorteren, werken met verborgen rijen, enz.
