Zo sorteert u Excel-gegevens op maanden en dagen en houdt u geen rekening met jaren
Excel sorteert datums in principe als volgt: eerst worden ze gesorteerd op jaar, dan op maand en tenslotte op dag. In de meeste gevallen is deze sorteervolgorde ook logisch. Soms is echter een sortering zonder jaartal vereist. Een klassiek voorbeeld is een verjaardagslijst. Gewoonlijk moet het jaar hier worden weggelaten en alleen op dag en maand worden gesorteerd.
U kunt deze formule gebruiken om uw Excel-lijst chronologisch te sorteren
Als u de tabel wilt sorteren op maanden en dagen in de vorm van een verjaardagslijst zonder rekening te houden met de jaren en de eerste datum staat in cel A2, gebruik dan de volgende formule in de hulpkolom:
= TEKST (A2; "MM.DT.")
Je past de formule als volgt toe:
Maak een hulpkolom waarin u de resultaten wilt weergeven (in het voorbeeld "Formule").
Klik op de eerste cel van de hulpkolom.
In het functieveld (fx) de formule: = TEKST (A2; "MM.DT.").
Zorg ervoor dat de functie verwijst naar de juiste cel met de juiste datums (in het voorbeeld staat de datum in cel B2).
Druk op Enter of druk op het kleine groene vinkje en dat is alles!
Kopieer deze formule vervolgens ver genoeg naar beneden in de hulpkolom.
De volgende afbeelding toont het gebruik van deze formule in een voorbeeldtabel (de datumwaarde staat in cel B2):
Deze hulpkolom gebruikt u vervolgens in het dialoogvenster "Gegevens". Selecteer vervolgens "Sorteren" om de gewenste volgorde in te stellen.
Als u wilt sorteren op dagen en vervolgens op maanden, ziet de formule er als volgt uit:
= TEKST (A2; "DD.MM.")
Voorzichtigheid! Formule levert tekst in plaats van getallen in Excel
Merk op dat beide formules tekst produceren waarmee u niet kunt rekenen. Dus wanneer u analyses uitvoert, moet u verwijzen naar de cellen met de oorspronkelijke datumwaarden.
Waarom doen deze problemen zich voor bij het sorteren van gegevens in Excel?
Het probleem met deze taak is dat Excel altijd rekening houdt met het jaartal bij het sorteren van datums. Dus 31 december 2009 is voor 1 augustus 2010. Daarom hebt u een hulpkolom nodig waarin u de datumwaarden dienovereenkomstig ontleedt.
Een andere aanpak helpt bij problemen met sorteren in Excel
Als je problemen hebt met deze formule en de daaropvolgende sortering, is er een andere procedure. Hiervoor heb je ook nog een extra hulpkolom nodig waarin je de sorteercriteria handmatig samenstelt. Ga hiervoor als volgt te werk:
-
Noteer in cel C4 de volgende formule:
= MAAND (B4) + DAG (B4) / 100
Deze formule koppelt maand en dag aan het gewenste sorteercriterium.
-
Kopieer de formule naar cel C13.
-
Markeer nu het gebied A3: C13.
-
Roep het item "Sorteren" op via het menu "Gegevens" onder "Sorteren en filteren" en dus het gelijknamige dialoogvenster.
-
Selecteer in het veld "Sorteren op" het item "Kolom sorteren".
-
Selecteer in het veld "Sorteren op" de "Celwaarden".“ gemaakt.
-
In het veld "Bestellen" selecteer de invoer "Op grootte (oplopend)" (zie afbeelding).
-
Als u nu het dialoogvenster bevestigt met een klik op de knop "OK", wordt de verjaardagslijst in oplopende volgorde gesorteerd volgens de hulpkolom C. Het resultaat ziet u in de volgende afbeelding:
Opmerking voor Excel-professionals over de formule voor de sorteervolgorde
De functie MAAND () wordt gebruikt om de maand af te lezen van de datumwaarde.
De functie DAG () bepaalt ook de dag op basis van de datumwaarde.
Door de dagwaarde te delen door 100 wordt de maand als primair sorteercriterium gebruikt. De dagspecificatie kan dus nooit groter zijn dan 1 en kan dus ook de kleinste maandwaarde 1 niet beïnvloeden. De dagspecificatie neemt dus een overeenkomstig ondergeschikte positie in voor de sorteerlogica.
Als u een gefilterde lijst gebruikt, kunt u de filters alleen gebruiken om de gegevens weer te geven die u wilt weergeven. Maar hoe filter je op een specifieke datum of een reeks data?
Hoe te filteren op een enkele datum in Excel
In de volgende afbeelding ziet u een lijst met historische bestelnummers die u wilt filteren:
De autofilters zijn al ingesteld in de lijst (de kleine grijze pijlen). Kolom C van de lijst bevat de datumwaarden voor de datarecords. Ga als volgt te werk om de lijst te filteren zodat alleen de records die op de datum in cel D1 vallen, worden weergegeven:
Filter enkele datum uit tabel
-
Klik op het filter in kolom C (klein pijltje naar beneden).
-
Excel tot versie 2003: Selecteer in de filterlijst het item "Door gebruiker gedefinieerd". Gebruik de instelling “Komt overeen” uit de eerste lijst.
-
Excel 2007 of later: Selecteer het item "Gelijk aan" uit de filterlijst onder "Datumfilter".
-
Vul in alle Excel-versies de gewenste datum in achter het invoerveld "komt overeen met" (in het voorbeeld 13 mei 2009).
-
Bevestig uw keuze met de OK-toets.
-
Excel past het gewenste filter direct toe. De volgende afbeelding laat het resultaat zien:
Hoe te filteren in Excel-lijsten met één jaar
Als je datums in een lijst hebt staan, kun je niet gemakkelijk op jaar filteren. Het aangepaste filter "bevat 2011" vindt geen datum uit 2011. Dit komt doordat Excel datumwaarden interpreteert als continue waarden vanaf 1 januari 1900 en deze alleen weergeeft in de vorm die wij via de opmaak kennen.
Kleine truc om toch op jaar te filteren in Excel
Toch kunt u uw lijsten ook filteren op bepaalde jaren. Hiervoor moet u echter een extra kolom opnemen waarin u de jaren uit de data haalt. In de getoonde werkmap ziet u een lijst gefilterd op het jaar 2011:
Om deze filtering op basis van het jaar 2011 te doen, voert u de volgende stappen uit:
-
Voeg de nieuwe kolom "Jaar" toe door de tekst "Jaar" in cel D3 in te voeren.
-
Voer de volgende formule in cel D4 in om het jaar van de eerste rij van de lijst te berekenen:
= JAAR (A4)
-
Kopieer de formule naar alle rijen van de lijst om alle jaren te vinden.
-
Klik op een cel in de lijst.
-
Gebruik de opdracht "Filter" in het gebied "Gegevens" om het autofilter voor de lijst te activeren (kleine pijlen).
-
Gebruik het Auto Filter-symbool in de nieuwe kolom "Jaar" om het item "2011" te selecteren en klik op "Ok".
Bij gebruik van Excel-versies vóór 2007 vindt u de opdracht voor het integreren van de autofilter onder "Gegevens" en "Filter".
Een datum "van tot" sorteren en filteren in Excel
Ongeacht of het de datum is waarop de bestelling is geplaatst of de bestelling is voltooid, het filteren van bepaalde datumbereiken is essentieel voor professionele resourceplanning. Door uw gegevens te filteren, ziet u in één oogopslag hoeveel bestellingen of taken er binnen een bepaalde periode vallen.
Voorbeeld: Filter in Excel-tabel op een specifieke datum
U wilt bijvoorbeeld de periode van 30 november 2022-2023 tot 5 december 2022-2023 filteren in de getoonde tabel.
Met de Auto Filter functie kunt u dit doen met de volgende stappen:
-
Klik op een cel in de lijst.
-
Selecteer de opdracht "Filter" op het tabblad "Gegevens" in de groep "Sorteren en filteren" op het lint.
(Excel versie 2003: Activeer de menuopdracht Data> Filter> Auto Filter)
-
Het pictogram Automatisch filteren verschijnt rechts van de kolomtitel (kleine pijl). Klik op het icoon naast "Datum".
-
Selecteer in het menu dat verschijnt de opdracht "Datumfilter" en vervolgens de functie "Tussen …".
-
Selecteer in de eerste keuzelijst "is na of gelijk aan" en vul ernaast "30-11-2020". Selecteer in de tweede lijst "is voor of gelijk aan" en vul ernaast 12/05/2020. Gebruik de optie "En".
-
Klik op de knop OK om de lijst dienovereenkomstig te filteren.
-
Het resultaat is een lijst die beperkt is tot de periode van 30 november 2022-2023 en 5 december 2022-2023. Alle gegevensrecords die niet binnen deze periode vallen, worden verborgen. Als u de filtercriteria wilt wijzigen in een ander datumbereik, roept u de opdracht Automatisch filteren opnieuw aan. Stel vervolgens nieuwe datumwaarden in voor de ondergrens en de bovengrens.
De screenshots zijn gemaakt met Excel versie 2016.
FAQ
Hoe kan ik een verjaardagslijst sorteren in Excel?
Gebruik hiervoor eenvoudig een hulpkolom die u ook invoert. In deze hulpkolom kunt u de datum weergeven zonder het jaartal. U kunt dan de sorteerfunctie gebruiken om de verjaardagen in exact dezelfde volgorde te sorteren.
Sorteren op nummer in Excel werkt niet. Wat kan ik doen?
Als alle opties voor het sorteren op nummer niet werken, ligt het probleem soms bij tabelsjablonen die op de achtergrond actief zijn. Probeer eerst alle opmaak van de betreffende cellen geleidelijk te wissen. Vaak zit hier het probleem. Als dat niet werkt, kopieer dan gewoon de hele tabel en plak deze in een nieuw blad met behulp van de plakfunctie "Waarden en bronopmaak".
Excel herkent de datum niet. Wat is de oplossing?
Soms staat Excel dwars en wil een ingevoerde datum gewoon niet als zodanig herkennen en weergeven. Als niets werkt, maakt u een nieuwe kolom naast de oude. Klik met de rechtermuisknop op de nieuwe kolom en selecteer "Cellen opmaken …". Selecteer in het geopende menu "Datum" en klik op "Ok". Selecteer en kopieer nu de oude kolom die niet werkte en plak de gekopieerde waarden in de nieuwe kolom met behulp van de plakfunctie "Waarden (W)". Verwijder vervolgens de oude kolom.