Gegevens sorteren in Excel doe je zo
Excel is een ware cijfergoeroe, maar je moet natuurlijk zelf ook weten welke mogelijkheden er zijn en hoe je de data in Excel op een optimale manier kunt weergeven. In dit artikel geven we praktische tips voor het sorteren en filteren van je data.
Wanneer je data in een tabel of een gegevensbereik hebt ingevoerd, wil je die niet altijd als een statisch blok bekijken. Het kan bijvoorbeeld handig zijn om gegevens in een andere volgorde te plaatsen (sorteren) of om overtollige informatie tijdelijk weg te halen (filteren). We stellen je een aantal toegankelijke methodes en functies in Excel voor, waarmee je zulke datamanipulaties snel en efficiënt uitvoert.
We baseren ons op Excel van Microsoft 365, maar de meeste tips kun je ook in andere Excel-edities gebruiken. We gaan er wel vanuit dat je over een of meer geschikte datareeksen of tabellen in Excel beschikt, waarop je de voorgestelde technieken kunt gebruiken.
Tip:Bestel ook onze cursus Excel in de praktijk
Standaardsortering
We beginnen met de eenvoudigste sorteringstechniek: op basis van één criterium en één kolom (of rij) in je datareeks.
Selecteer een willekeurige cel in het bereik, open het tabblad Gegevens en klik op de knop AZ of ZA in de rubriek Sorteren en filteren. Je merkt dat je gehele bereik wordt gesorteerd op basis van de kolom van de geselecteerde cel, hetzij van klein naar groot, hetzij in omgekeerde volgorde, zowel alfabetisch als numeriek. Heb je een complete kolom of rij geselecteerd, dan verschijnt meteen na het indrukken van de knop de vraag of je wilt Doorgaan met de huidige selectie of De selectie uitbreiden. Kies je eerste optie, dan zal de sortering zich alleen op die kolom of rij doorzetten – wat wellicht niet de bedoeling is.
Voor meer mogelijkheden druk je op de knop Sorteren, waarna een dialoogvenster verschijnt. Standaard wordt een sortering uitgevoerd op kolommen. Wil je dit anders, klik dan op Opties en selecteer Van links naar rechts sorteren. Merk op dat je hier ook Hoofdlettergevoelig kunt selecteren.
We gaan hier even uit van een sortering van boven naar beneden. Laat het vinkje staan bij Mijn gegevens bevatten kopteksten als je geselecteerde tabel kolomtitels bevat als Naam, Bedrag enzovoort. Bij Sorteren op kies je de kolom waarop je wilt sorteren, bij Sorteren op laat je Celwaarden geselecteerd. De Volgorde kan zowel dalend als oplopend zijn. Bevestig met OK.
Standaard sorteert Excel op één kolom, op basis van celwaarden.
Aangepaste sortering
Meestal zul je op celinhoud willen sorteren, maar er zijn ook andere opties. Je kunt een kolom of rij bijvoorbeeld volgens formatteringseigenschappen ordenen. Roep opnieuw het dialoogvenster bij Sorteren op. In het uitklapmenu bij Sorteren op kun je nu ook op Tekstkleur, Celkleur en Pictogram voor voorwaardelijke opmaak sorteren.
Lees ook: Zo gebruik je Office zonder abonnement
Je hoeft je evenmin te beperken tot één sorteringsniveau. Zo kun je bijvoorbeeld kolom 1 als primaire sorteringssleutel gebruiken, en wanneer meerdere waarden in deze kolom identiek zijn, kun je een andere kolom als secundaire sorteringssleutel inzetten. Je regelt op de gebruikelijke manier eerst de sortering van de eerste kolom, vanuit het dialoogvenster. Vervolgens druk je op de knop Niveau toevoegen en stel je de sortering voor de andere kolom in. Met de pijlknoppen kun je de prioriteit van de sorteringssleutels nog aanpassen.
Stel, je hebt in een rij of kolom eigen celwaarden ingevuld, bijvoorbeeld Mini, Klein, Normaal, Groot, Extra groot en je wilt de data nu volgens deze criteria sorteren. Open hiervoor opnieuw het sorteringsvenster. Bij Sorteren op selecteer je de betreffende kolom, Sorteren op laat je op Celwaarden staan en bij Volgorde kies je deze keer Aangepaste lijst. Er verschijnt een nieuw dialoogvenster waar je de celwaarden in de gewenste sorteringsvolgorde intikt, gescheiden door een komma. Bevestig met Toevoegen. Selecteer de nieuwe lijst en druk op OK (2x).
Een aangepaste sortering met eigen waarden als primaire sorteringssleutel.
Autosortering
Wanneer je wijzigingen aanbrengt in een gegevensbereik, dan moet je er wel steeds zelf aan denken om de gewenste sortering opnieuw uit te voeren. Het is ook mogelijk dat automatisch door Excel te laten doen. Zo’n operatie kan natuurlijk met een VBA-script (Visual Basic for Applications), maar het kan ook anders.
Stel, je hebt een bereik met bijvoorbeeld in de eerste kolom de namen van de medewerkers waarop je alfabetisch wilt sorteren, maar wel zo dat de sortering intact blijft wanneer je een naam wijzigt of toevoegt. We gaan er even vanuit dat de eigenlijke data (zonder eventuele kopteksten) zich in het bereik C2:E11 bevinden.
Tik dan de volgende formule in een willekeurige cel (bijvoorbeeld G2) in:
=SORTEREN(C2:E11;1;-1)
Het eerste cijfer 1 in deze formule verwijst naar de kolom waarop je wilt sorteren en -1 geeft aan dat je dit in aflopende volgorde wilt doen (1 is in oplopende volgorde).
Standaardfiltering
Vooral bij uitgebreide datasets dreigt het onoverzichtelijk te worden en is het handiger om tijdelijk bepaalde gegevens onzichtbaar te maken. Bij een klassieke gegevensreeks hoef je slechts een willekeurige cel te selecteren, waarna je in het menu Gegevens op de knop Filter drukt (druk nogmaals om de filterfunctie uit te zetten). Je merkt dat elke kolom nu een pijlknop krijgt. Klik op de pijl van de kolom waaruit je gegevens wilt filteren, verwijder het selectievakje bij Alles selecteren en plaats alleen een vinkje bij de gegevens je die zichtbaar wilt houden. Bevestig met OK.
In tegenstelling tot een klassieke gegevensreeks beschikt een echte tabel al over besturingselementen voor filtering in de tabelkoppen, met vergelijkbare filteropties als eerder beschreven. Dit is wel zo handig, en desgewenst kun je een datareeks als volgt naar een tabel converteren.
Selecteer het gewenste gegevensbereik, ga naar Invoegen, klik op Tabel bij Tabellen en laat het vinkje staan bij Mijn tabel bevat kopteksten. Bevestig met OK. Gaat je voorkeur naderhand toch weer uit naar een gegevensreeks, selecteer dan de tabel, rechtsklik op je selectie, kies in het contextmenu Tabel / Converteren naar bereik en bevestig met Ja.
Een tabel heeft ingebouwde filterfuncties.
Gecombineerde filtering
Je kunt ook meerdere filters tegelijk toepassen. Druk opnieuw op de knop Filter en op het pijlknopje bij de gewenste kolomkop. Afhankelijk van het datatype zie je in het venster Getalfilters of Tekstfilters staan.
Stel, je wilt alleen de data zien met een bovengemiddelde waarde in numerieke kolom 2 op voorwaarde dat in tekstkolom 3 een bepaalde tekststring voorkomt. Klik dan eerst op het pijlknopje in kolom 2 en kies Getalfilters / Boven gemiddelde. Je merkt dat de pijlknop in deze kolom nu een filterpictogram bevat en het volstaat de muispijl even hierboven te houden om het toegepaste filter te zien.
Klik nu op de pijlknop in kolom 3 en kies Tekstfilters / Aangepast filter. In het dialoogvenster van Aangepast AutoFilter kun je nu de gewenste filters samenstellen, bijvoorbeeld iets als: is gelijk aan ABCOf is gelijk aan CBA. Na je bevestiging wordt dit tweede filter toegepast bovenop het filter dat je al voor kolom 2 had ingesteld. Filters werken namelijk additief.
Om snel een filter te verwijderen van een bepaalde kolom klik je met rechts op een cel in die kolom en kies je Filteren / Filter wissen van [kolomnaam].
Filteren op meerdere criteria kan ook.
Geavanceerde filtering
Voor nog krachtigere filters gebruik je het dialoogvenster van de knop Geavanceerd in de rubriek Sorterenen filteren in het menu Gegevens. Dit vergt wel enige voorbereiding.
Allereerst kopieer je de kolomkoppen van je databereik (bijvoorbeeld A1:C1) naar een lege plek in je spreadsheet (bijvoorbeeld G1:H1). Het is nu de bedoeling dat je onder de gekopieerde kolomkoppen de gewenste criteria invult. Stel, je bent alleen geïnteresseerd in de data van de items met de naam Peter of Jan. Dan vul je de bijbehorende namen onder elkaar in de kolom Naam in (in ons voorbeeld in G2 en G3).
Vervolgens selecteer je je complete databereik (A1:C10 in ons voorbeeld), waarna je op het tabblad Gegevens op de knop Geavanceerd drukt bij Sorteren en filteren.
Er verschijnt nu een dialoogvenster waarin je Kopiëren naar een andere locatie selecteert. Het is immers de bedoeling dat je de resultaten van de filtering naar een ander, nog leeg bereik kopieert. Bij Lijstbereik verwijs je naar je complete databereik (bijvoorbeeld $A$1:$C$10). Dat kun je handmatig intikken of je drukt op het pijlknopje waarna je het bereik met de muis selecteert.
Over naar Criteriumbereik. Hier selecteer je alle kolomkoppen inclusief de ingetikte criteria - in ons voorbeeld $G$1:$I$3). Tot slot druk je op het pijlknopje bij Kopiëren naar en klik je op de linkerbovenhoek van het gewenste resultatenbereik. Let wel, wanneer je een vinkje plaatst bij Alleen unieke records, worden eventuele duplicaten niet mee gekopieerd in je resultatenlijst. Bevestig met OK.
In het eerdergenoemde voorbeeld gaat het om een zogeheten Booleaanse OR-relatie: je wilt de data die bij Peter óf Jan horen. Wil je een AND-relatie (zowel Peter als Jan), dan moet je criteria in verschillende kolommen intikken. Groente in de kolom Product bijvoorbeeld en >1000 in de kolom Omzet.
In dit geval moet aan meerdere criteria tegelijk worden voldaan (Booleaanse AND).
Filterfunctie
Schrikt enig tikwerk je niet af, dan kun je met de ingebouwde FILTER-functie ook complexe filtercriteria in een enkele formule kwijt. De syntax op zich is vrij eenvoudig: =FILTER(reeks;criteria;[indien_leeg]).
Om bij ons zelfde voorbeeld te blijven kan dit dus iets worden als: =FILTER($A$2:$C$10;$A$2:$A$10=″Peter″;″Niet gevonden″)
Merk op dat de kolomkoppen niet mee in het databereik zijn opgenomen. Staat de naam Peter niet in de B-kolom dan zal ‘Niet gevonden’ verschijnen. Voor een OR-relatie gebruik je de volgende syntax: =FILTER($A$2:$C$10;($A$2:$A$10=″Peter″)+($A$2:$A$10=″Jan″))
Om een AND-relatie te krijgen, gebruik je de operator * in plaats van +. Het bovenstaande voorbeeld (alleen groente met een omzet van meer dan 1000) vertaalt zich dan in:
=FILTER($A$2:$C$10;($B$2:$B$10=″Groente″)*($C$2:$C$10>1000))
Je moet natuurlijk wel de syntax goed beheersen, maar de snelste manier voor complexe filtering is zonder meer deze FILTER-functie.
De FILTER-functie oogt complex, maar de syntax heb je wel snel door.
Nog geen toegang tot Excel? Bestel Microsoft Office hier bij Bol.com.
