De basis van Excel kennen de meeste accountants wel. Maar het rekenprogramma heeft de laatste tijd veel nieuwe functies gekregen, die bij velen nog onbekend zijn. Jammer!
In deze tip haal ik er twee functies uit, die je goed kunt gebruiken bij iets wat al langer bestaat: gegevensvalidatie. Je moet wel over Excel365 beschikken of versie 2021, anders heb je die nieuwe functies niet.
Hoe maak je een flexibele altijd gesorteerde keuzelijst?
In deze afbeelding zie je een tabel met de naam Verkopen ①. Als er een keuze gemaakt wordt voor een categorie ② wordt met de functie SOM.ALS berekend hoeveel de totale verkopen in die categorie zijn ③.
Ik heb van het cellenbereik een tabel gemaakt, omdat dit grote voordelen heeft (zie deze tip: https://www.accountancyvanmorgen.nl/2024/01/02/exceltip-een-echte-excel-tabel-maken/ )
Basis: hoe maken de meesten mensen de keuzelijst?
Die keuzelijst ② maak je met gegevensvalidatie, iets wat al langer bestaat in Excel.
- Maak een lijst van alle items die in de categoriekolom van de tabel voorkomen. Je kunt het natuurlijk overtypen, maar dit werkt ook.
- Selecteer de hele kolom en kopieer die.
- Plak dat ‘ergens’. Ik doe dat meestal op een apart werkblad, maar het kan ook op hetzelfde werkblad.
- Gebruik direct daarna Gegevens > Dubbele waarden verwijderen om te zorgen dat de items maar één keer voorkomen.
- Extra: gebruik vervolgens Gegevens > Sorteren A tot Z om ze in alfabetische volgorde te zetten.
- Selecteer de cel waar je uit de lijst wilt kunnen kiezen (hier E2) en kies Gegevens > Gegevensvalidatie.
- Kies bij ① voor Lijst.
- Geef bij ② dat ‘ergens’ aan, dus waar je de categorieënlijst hebt staan.
Nieuw: flexibele gesorteerde keuzelijst maken?
Hoe maak je die gegevensvalidatie nu zo flexibel, dat een nieuwe categorie in de tabel ook automatisch wordt opgenomen in de lijst? Het liefst in alfabetische volgorde, want dat maakt het zoeken eenvoudiger.
Hiervoor gebruik je de nieuwe functie UNIEK. Die is heel eenvoudig in gebruik.
- Selecteer de startcel waar je de brongegevens voor de gegevensvalidatie wilt hebben.
- Typ: =UNIEK(
- Selecteer de kolom met de categorieën uit de tabel
- Eindig met Enter (het haakje sluiten wordt vanzelf geplaatst).
In mijn voorbeeld ziet dat er dan zo uit:
- Je ziet de lijst direct staan. Omdat je op Enter hebt gedrukt is de 2e uit de lijst geselecteerd. Je ziet de formule in de formulebalk ①.
- Je ziet ook dat er een blauwe lijn om de lijst staat.
Korte uitleg van UNIEK
De functie UNIEK() is een nieuwe functie die als uitkomst een zogenaamd ‘dynamisch bereik’ heeft. Dat betekent dat de uitkomst in feite maar in één cel staat maar overloopt in andere cellen. Dat ‘overloop-bereik’ is het blauw omrande gebied. De enige plaats waar de formule staat is de cel linksboven (in dit voorbeeld A2).
Als je A2 selecteert zie je ook dat dat de enige cel is waar de formule in de formulebalk zwart is ③: in de andere cellen is die grijs ②.
Flexibele sortering van de categorieën
Als je wilt dat de keuzelijst altijd in alfabetische volgorde staat, moet je de functie SORTEREN ook nog toevoegen aan UNIEK. Doe dat natuurlijk wel in de eerste cel van het dynamische bereik.
Aanpassing van de gegevensvalidatie
Om nu een flexibele gegevensvalidatie te krijgen moet je verwijzen naar het overloopgebied. Dat moet op een speciale manier: je verwijst naar de startcel en zet er een # achter, zoals in deze afbeelding.
Controle van de flexibiliteit
Als je een van de categorieën wijzigt in bijvoorbeeld Poloshirts (of onderaan iets toevoegt met een nieuwe categorie), dan zul je zien dat de cel met de gegevensvalidatie die nieuwe categorie ook toont. En ook nog in alfabetisch volgorde.!
Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken.
De nieuwe functies komen onder andere aan de orde in de expert-training Geheimen van Excel.
