Tag Archief van: tabel

Decoratieve afbeelding met een filter

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!).

Schermafdrukken van de knop Filter op de tab Gegevens. Eronder een voorbeeldlijst waar je de filterknoppen ziet en waar op de provincie Gelderland is gefilterd.

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.

Afbeelding met tekst, schermopname, Lettertype, nummer Automatisch gegenereerde beschrijving

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.

2 schermafdrukken: een kleiner overloopgebied en een groter overloopgebied.

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’.

Schermafbeelding met een overloopgebied waarin een cel niet leeg is: je ziet de foutmelding #OVERLOOP! Via het pictogram dat erbij staat kun je de probleemcel opzoeken.

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).

Schermafbeelding waarin niet "Gelderland" is getypt in de filterfunctie, maar verwezen wordt naar een cel waar "Gelderland" staat.

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.
    Nieuwe lijt met een datum erin. Die datum is in het

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) ②.

Schermafbeelding van dezelfde lijst maar dan is er eerst een tabel van gemaakt. In de filterfunctie zijn dan de celverwijzingen vervangen door tabelverwijzingen.

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!

2 afbeeldingen met een nieuwe naam erbij. In het filter met het cellenbereik is die naam niet automatisch opgenomen, in de tabel wel.

Afbeelding met een cellenbereik en een tabel en de tekst 20 voordelen van een Excel-tabel

Maak altijd een tabel!

In mijn trainingen en tijdens één-op-één-spreekuurgesprekken zie ik dat in Excel heel vaak data staan in tabelvorm ①. Er staan kolomomschrijvingen boven elke kolom en daaronder staan de gegevens.
Filterknoppen erbij en je kunt dan heel simpel sorteren, selecties maken ② en als je dat wilt de data analyseren met een draaitabel (pivot table) ③.

Voorbeeld van een cellenbereik in tabelvorm zonder filterknoppen (1), met filterknoppen (2) en weergegeven in een draaitabel (3).

Op zich werkt dat prima, maar het is slimmer hier ook een Excel-tabel van te maken!
Dit heeft een groot aantal voordelen. Hierover heb ik jaren geleden al eens een videofilm over gemaakt die nog steeds actueel (https://www.youtube.com/watch?v=YOGNJyaDIKU).

Hoe maak je een tabel?

Er zijn 2 manieren. Het grote verschil is dat manier 1 Excel de tabelstijl bepaalt en bij manier 2 bepaal je die zelf. In beide situaties kun je altijd achteraf de tabelstijl nog wijzigen.

  1. Selecteer een cel in het cellenbereik en kies Invoegen > Tabel ①.
    Controleer het cellenbereik en kijk of er inderdaad kopteksten (=kolomomschrijvingen) boven elke kolom staan) en kies OK ③.
    Voor deze manier is ook een sneltoets: Ctrl+L (voor de mensen met de Engelstalige Excel is dat Ctrl+t).
  2. Selecteer een cel in het cellenbereik en kies Start > Opmaken als tabel > maak een keuze voor de tabelstijl ②.
    Controleer het cellenbereik en kijk of er inderdaad kopteksten (=kolomomschrijvingen) boven elke kolom staan) en kies OK ③.

Schermafbeeldingen van de knop Invoegen > Tabel (1), Start > Opmaken als tabel (2) en het dialoogvenster dat in beide situaties volgt (3).

Waarom is het maken van een tabel slimmer?

Hieronder heb ik de voordelen op een rij gezet. Hier zie je 20 voordelen op een rij!

  1. Je ziet direct welke cellen de tabel vormen door de opmaak. Die stijl kan worden aangepast of verwijderd.
    Schermafbeelding van de gegevens in een Excel-tabel. Je ziet direct de eerste 3 voordelen.
  2. De filterknoppen komen er automatisch bij.
  3. De om en om gekleurde rijen zorgen (zeker bij brede tabellen) dat het eenvoudiger is om de regels per rij te lezen. Die om-en-om-kleuren blijven ook als er gesorteerd wordt!
  4. De kolomomschrijvingen blijven altijd zichtbaar, ook als je naar onderen scrolt. Je hoeft dus geen titels te blokkeren.
    Schermafbeelding waarin je ziet dat de kolomomschrijvingen altijd zichtbaar blijven.
  5. Voeg je een kolom toe dan komt die automatisch bij die tabel gevoegd en krijgt die een filterknop.
  6. Als er extra gegevens worden toegevoegd onderaan, breidt de tabel vanzelf uit.
    Zou dit niet gebeuren, dan kun je dat altijd aanpassen door de tabelgrootte te wijzigen via het speciale tabblad Tabelontwerp in het lint.
    Schermafbeeldingen waarbij je ziet dat er een extra kolom bij komt en een extra rij. Zie je de knop in het lint om de tabelgrootte handmatig te wijzigen.
  7. Het tabelbereik is dynamisch, wat voordelen heeft op verschillende gebieden.
    Bijvoorbeeld als je er een grafiek van maakt: nieuwe toegevoegde gegevens (ook onderaan!) komen vanzelf in de grafiek erbij. Je hoeft het grafiekgebied niet aan te passen.
  8. Het dynamische tabelbereik is ook handig voor draaitabellen. Bij een gewoon cellenbereik moet je het gebied waarop de draaitabel is gebaseerd eerst aanpassen als je er rijen/kolommen aan toevoegt en daarna vernieuw je de draaitabel.
    Bij een Excel-tabel hoef je alleen maar te vernieuwen, want het tabelbereik is automatisch aangepast!.
  9. Het is mogelijk een totaalrij in te schakelen via de tab Tabelontwerp.
    Dat is een extra rij onder het laatste item van de tabel. Daarin zal in eerste instantie alleen bij de laatste kolom een berekening gemaakt worden (optelling van die laatste kolom bij numerieke gegevens of een telling bij niet-numerieke gegevens).
    Je kunt nu op die totaalrij bij elke tabelkolom kiezen voor een berekening.
    Wanneer je de filterknoppen gebruikt laat deze totaalrij de berekening zien voor de cellen die je hebt gefilterd!
    Schermafbeelding van een totaalrij.
  10. Maak je een berekening in de tabel, dan worden er speciale tabelformules gebruikt.Schermafbeelding met tabelformules en dat een formule in een kolom automatisch doorgevoerd wordt naar de andere cellen in de kolom.
    Dus niet de ‘normale’ celverwijzing =D4, maar een verwijzingen naar dezelfde rij, andere kolom: =[@Verkopen]
    Als je bijvoorbeeld buiten de tabel een optelling wilt hebben van een kolom, wordt de kolomnaam gebruikt: =SOM(Tabel2[Verkopen])
  11. De berekening wordt automatisch doorgevoerd naar de andere rijen in de tabel.
  12. Invoegen en verwijderen via de rechtermuisknop van tabelrijen en tabelkolommen gebeurt alleen in de tabel. Als er dus iets buiten de tabel staat, wordt dat niet beïnvloed.
    Schermafbeelding waarin je ziet hoe je alleen in de tabel een rij kunt invoegen
  13. Filteren met slicers is mogelijk.
    Dit is sneller dan met de filterknoppen en het is ook nog visueler. Hierover heb ik eerder een blog gemaakt: https://www.toels-pc.nl/2024/01/08/filteren_met_slicers
    Schermafbeelding waarin je ziet dat er met een slicer gefilterd is op regio Zuid
  14. Automatisch controle op dubbele kolomnamen. Komt een naam dubbel voor, dan wordt er een getal achter gezet. Uiteraard is het slim dit zelf te wijzigen!
    Dubbele kolomnamen geven bijvoorbeeld bij draaitabellen een probleem.
  15. Er zin speciale selecties mogelijk in de tabel: alle rijen van een kolom (a), of alle kolommen van een rij (b) en alle data van de tabel (c).
    Je moet dan letten op de zwarte pijlvorm van je muis en dan klikken.
    Schermafbeeldingen met muisaanwijzers op de plaats waar je een hele tabelkolom, tabelrij en de hele tabel kunt selecteren
  16. De tabel heeft altijd een naam Tabel#. Je kunt die naam aanpassen via Tabelontwerp > Tabelnaam.
    Via het naamvak kun je altijd snel naar de tabel gaan.
    Schermafbeelding van de tabelnaam en het naamvak
  17. Tijdens het typen van nieuwe data in de tabel kun je altijd met de Tab-toets naar de volgende rij. Een totaalrij zal automatisch opschuiven.
  18. De opmaak wordt automatisch doorgevoerd naar nieuwe cellen.
  19. Je kunt de lijst exporteren naar een SharePoint-lijst of Visio-diagram via Tabelontwerp > Exporteren.
    Schermafbeelding van de knop Exporteren
  20. Er komen steeds meer nieuwe functies, die met name flexibiliteit hebben als ze verwijzen naar een tabel. Voorbeelden zijn =SORTEREN, =UNIEK en =FILTER. Maar ook bestaande functies zijn flexibeler (zie de =SOM(Tabel2[Verkopen] van voordeel 10.
    In een volgend blog zal ik hier een voorbeeld van geven.

Overtuigd?

Mocht je toch achteraf nog spijt hebben, dan kun je een tabel altijd weer terugzetten naar een normaal cellenbereik via de tab Tabelontwerp. Gelukkig kun je daarna ook altijd weer terug!