Filteren, maar dan anders …

In mijn trainingen en tijdens één-op-één-spreekuurgesprekken zie ik dat een van de meest gebruikte onderdelen van Excel het werken met lijsten/tabellen is. Het is een hele mooie manier om gegevens weer te geven, je kunt goed sorteren en je kunt selecteren (wat Excel dan ‘filteren’ noemt). De meeste mensen schakelen hiervoor de filterknoppen in (tab Gegevens > Filter).

Maar bij het filteren werk je altijd IN de lijst zelf. Filter je bijvoorbeeld op de kolom waar de provincienaam staat op Gelderland, dan worden de rijen waarin dat woord niet voorkomt verborgen.
Hieronder zie je een voorbeeld van zo’n lijst ①. Je ziet dat de filterknop is ingeschakeld ②.
Aan de trechter bij de provinciekolom ③, zie je dat er in die kolom is gefilterd. Als je de muis erboven houdt, zie je ook waarop is gefilterd ④. De rijen waar Gelderland niet staat, zijn verborgen ⑤ (je mist rijnummers!).

Er zijn 2 problemen met deze manier van filteren.
Als naast je tabel/lijst ook gegevens staan en die staan toevallig in verborgen rijen, dan zie je die ook niet meer!
En bij gebruik van de filterknoppen is het niet mogelijk tegelijk een lijst op je scherm te krijgen van de gegevens van Gelderland en een andere lijst met Friesland-gegevens.
Met de functie Filter kun je dat wel. Je laat de originele lijst intact en zet het resultaat met het filter ergens anders neer. En er is een connectie met de basislijst, dus als daar iets wijzigt, zie je dat automatisch ook in de gefilterde lijst.

Je bent nog beter af als je voor het filteren van je cellenbereik een tabel maakt. Daarover heb ik eerder een blog geschreven (Maak altijd een tabel).
In de blog Filteren met slicers heb je gezien dat er nog een manier is om te filteren, maar die werkt ook met verborgen rijen!

De lijst uit de afbeelding bij ① gebruik ik nu om de functie Filter te beschrijven.

De functie Filter() in een cellenbereik

Het gebruik van deze functie is vrij eenvoudig. Je moet opgeven welke cellen de lijst vormen (A2:C19) en je moet opgeven op welke kolom je wilt filteren (B2:B19) en wat hiervoor het gewenste selectiecriterium is (=”Gelderland”).
Het resultaat is dat je alle gegevens uit die tabel van de provincie Gelderland ziet. Je moet er nog wel zelf de kolomomschrijvingen boven zetten.

Er is wel iets bijzonders: je ziet dat er een blauwe rand om de cellen met het resultaat. Die rand geeft het ‘overloopgebied’ aan.
Technisch gezien staat de berekening alleen in de linkerbovenhoek van het blauw omrande gebied. De uitkomst loopt over in de andere cellen die nodig zijn.
Dat overloopgebied is flexibel: als in de lijst iets wijzigt (naam 13 wordt verwijderd of Naam 5 wordt toch Gelderland), dan zal het overloopgebied automatisch wijzigen.

Foutmelding #OVERLOPEN!

De cellen in het overloopgebied moeten echt leeg zijn, anders krijg je een foutmelding #OVERLOPEN! (Engels: #SPILL!).
Je ziet met een gestreepte rand hoe groot het overloopgebied is. In dit voorbeeld zie je direct wat de niet lege cel is. Maar als het overloopgebied groot is, zie je het misschien niet zo snel. Door te klikken op het getoonde pictogram kun je de cel(len) met de problemen laten selecteren. Haal je die leeg, dan werkt het weer!
Excel noemt het overloopgebied een ‘dynamisch gebied’.

Meer flexibiliteit

Je maakt het geheel natuurlijk nog flexibeler door in de filterberekening “Gelderland” niet zelf te typen, maar door te verwijzen naar een cel waar je de provincienaam kunt invullen. Als je die provincienaam dan ook nog met gegevensvalidatie laat kiezen uit een lijst wordt het natuurlijk helemaal fraai (zie de blog over Gegevensvalidatie).

LET OP

Er zijn met deze functie wel enkele dingen waar je rekening mee moet houden.

• Zorg dat je bij de verwijzingen naar de lijst/tabel de kolomomschrijving niet meeneemt (dus niet A1:C19): je geeft dus alleen de rijen met de gegevens op (A2:C19). Dat doe je natuurlijk ook bij de kolomgegevens waarop je wilt filteren.
• Dat betekent dus ook dat je de kolomomschrijving zelf boven de kolommen moet zetten boven de filterresultaten.
• De opmaak van de cellen van de basislijst wordt niet overgenomen in de gefilterde lijst.
  De cellen in het overloopgebied hebben een eigen opmaak. Hieronder zie je daarvan een duidelijk voorbeeld. Je moet dus de opmaak van de cellen in het overloopgebied wellicht aanpassen.
  

De functie Filter() met een tabel

Wanneer je van je cellenbereik een tabel hebt gemaakt, werkt de filterfunctie op dezelfde manier. Je verwijst dan echter niet naar de cellen met de gegevens, maar je gebruikt de tabelverwijzingen. Die krijg je automatisch als je bij het maken over de cellen sleept met je muis.

Hieronder zie je een afbeelding hiervan. Van de celen A1:C19 is met Invoegen > Tabel een tabel gemaakt ①. Die heeft de naam Tabel1 gekregen. Die naam zie je terug als je de cellen selecteert in de functie Filter bij de lijst . Selecteer je de cellen met de provincienamen, dan wordt dit automatisch Tabel1[Provincie] (=de kolom Provincie van Tabel 1) ②.

Voordeel bij een tabel

Stel dat er een nieuwe naam bij komt in de basislijst ①. Als het een cellenbereik moet je in de filterfunctie het cellenbereik B2:B19 aanpassen naar B2:B20 om die nieuwe gegevens ook mee te nemen ②!
Bij een tabel is dat niet nodig ③! Dit is weer een voorbeeld van waarom je met een tabel beter af bent!