Voorbeeldbestand: Filter2019.xlsx
Deze week kwam de vraag binnen of het mogelijk was om met behulp van een formule gegevens uit een tabel, die aan een bepaalde voorwaarde voldoen, te selecteren.
De eerste reactie was: hup, dat doen we even. Maar dat viel toch nog tegen.
Want niet iedereen heeft de beschikking over Excel 2021 of Excel 365. Daar ben je met één formule in een cel klaar. Maar niet getreurd: er zijn altijd alternatieven denkbaar.
Nieuwe Filter-functie
Meer specifiek was de vraag hoe de namen in een bestand met een formule zouden kunnen worden geselecteerd op basis van de selectie in kolom D (zie Voorbeeldbestand).
Vanaf Excel-2021 en -365 bestaan er zogenaamde Spill-functies (ik kwam ergens de Nederlandse benaming Overloop-functies tegen). Het speciale van dit soort functies is dat wanneer je een formule met zo’n functie in een cel plaatst, het resultaat over meerdere cellen terecht kan komen (vandaar spill en overloop).
In dit geval plaatsen we ergens de formule
=FILTER(Tabel1[Naam];Tabel1[Selectie]=Waar;”Niets gevonden”) en het resultaat is dan alle namen ‘met een vinkje’ in cellen onder elkaar.
‘Oude’ oplossing1
Maar heb je niet de beschikking over deze nieuwe functies dan zul je iets anders moeten bedenken.
Een oplossing is om op basis van de tabel met namen en selecties een draaitabel te maken. Plaats het veld Naam in het vak Rijen en Selectie in het vak Filters (of maak een slicer zoals in het Voorbeeldbestand). Uiteraard kiezen we bij Selectie dan alleen de optie Waar.
In het voorbeeld is ook een lege naam aangevinkt en deze komt in de rij (leeg) tevoorschijn.
NB Excel sorteert alle namen automatisch op alfabetische volgorde.
Wil je de oorspronkelijke volgorde aanhouden, plaats dan ook Nr in het vak Rijen.
In het voorbeeld hiernaast zijn ook de geselecteerde lege namen in de draaitabel uitgefilterd.
Maar helaas deze oplossing voldeed niet aan de verwachtingen: “Kan het ook met een formule zodat er na het aanvinken van de personen geen extra handeling (het vernieuwen van de draaitabel) meer nodig is?“
Oplossing2
Voor deze oplossing hebben we een hulpkolom nodig (zie kolom E in het Voorbeeldbestand). Daar geven we iedere geselecteerde naam een eigen volgnummer.
In de eerste regel (cel E3) plaatsen we de formule =ALS(D3;1;0)
Dus als cel D3 de waarde Waar bevat dan wordt het volgnummer 1 anders 0.
Cel E4 krijgt de formule =E3+D4
We maken hier gebruik van het feit dat Excel de waarde Waar bij een berekening omzet naar 1 (en Onwaar naar 0).
Cel E4 kan dan naar beneden gekopieerd worden.
NB we hadden in cel E3 dus ook de formule =D3+0 kunnen plaatsen.
En nu het resultaat.
In cel N3 plaatsen we de formule
=ALS.FOUT(INDEX($C$3:$C$32;VERGELIJKEN(B3;$E$3:$E$32;0));””)
Als eerste gaan we de waarde van de teller in B3 Vergelijken met de hulp-kolom E. De derde parameter is een 0, dus Excel zoekt een exacte match en dan ook nog de eerste die hij (of zij?) tegenkomt.
Op basis van de gevonden regel in de tabel haalt INDEX de overeenkomende naam op. Als het ergens mis gaat (bijvoorbeeld als het volgnummer niet voorkomt in de hulpkolom) dan is het resultaat “” (niets dus).
In de kolommen F en G staan 2 alternatieven voor de bepaling van de hulpvariabele: cel F3 bevat de formule =AANTAL.ALS($D$3:D3;WAAR). Deze kan naar beneden gekopieerd worden waarbij de eerste D3 blijft staan (deze is absoluut) en de tweede wordt D4, D5 et cetera (een relatieve verwijzing).
Oplossing3
Deze oplossing bouwt door op het vorige artikel op de website van G-Info over de Aggregaat-functie. Bekijk de formule in cel O3 van het Voorbeeldbestand:
=ALS.FOUT(INDEX(C$3:C$32;AGGREGAAT(15;6;(RIJ($D$3:$D$32)-RIJ($D$3)+ 1)/($D$3:$D$32 =WAAR);RIJ($A1))); “”)
Gebruik de optie Formule evalueren om de werking te onderzoeken.