Excel sorteren: gegevens sorteren op tekst, datum, getal of kleur

Het sorteren van gegevens in Excel is een veelvoorkomende en eenvoudige taak, waarmee u uw gegevens opnieuw kunt ordenen op basis van het type sortering dat u kiest. Met de ingebouwde sorteerfunctie kunt u normaal gesproken getallen, tekststrings, datums en tijden in één of meerdere kolommen sorteren. U kunt ook snel en eenvoudig sorteren op een door uzelf gemaakte aangepaste lijst of op celopmaak (zoals lettertypekleur, achtergrondkleur of pictogram).

Naast deze eenvoudige sorteringen zijn er in uw dagelijkse werk vaak meer nuttige en complexe sorteermogelijkheden nodig. In deze handleiding worden verschillende soorten sorteringen besproken om uw problemen in Excel op te lossen.

Toegang tot de sorteeropties in Excel

Excel biedt meerdere manieren om de sorteerfunctie en de bijbehorende opties te openen.

1.1 Sorteerknoppen in het lint

De snelste manier om de sorteerfunctie toe te passen is via de sorteerknoppen in het lint.

Klik op een willekeurige cel in een kolom met waarden die u wilt sorteren. Klik vervolgens op het tabblad Data, en in de sectie Sort & Filter klikt u op de knop Sort A to Z of Sort Z to A om de gegevens alfabetisch oplopend of aflopend te sorteren. Zie screenshot:

Opmerking: Als er lege rijen in het gegevensbereik staan, worden na het toepassen van deze knoppen alleen de gegevens boven of onder de lege rijen van de geselecteerde cel succesvol gesorteerd.

1.2 Sorteerdialoogvenster

In de groep Sort & Filter van het tabblad Data vindt u nog een Sort-knop, zie screenshot:

Na het klikken op deze Sort-knop verschijnt het Sorteerdialoogvenster zoals hieronder getoond:

In het Sorteerdialoogvenster kunt u vervolgens de gewenste sorteerregels kiezen.

Tips: U kunt ook sneltoetsen gebruiken om het Sorteerdialoogvenster te openen. Druk hiervoor achtereenvolgens op de toetsen Alt + A + S + S op uw toetsenbord.

1.3 Sorteeropties in het Filtermenu

Als u filters heeft toegepast op uw gegevensbereik, vindt u de sorteeropties samen met de filteropties. Door op het filterpictogram in een kolom te klikken, ziet u de sorteeropties in de uitgevouwen lijst zoals hieronder getoond:

Opmerking: Deze sorteeropties veranderen afhankelijk van de gegevens in de kolom: staat er tekst in de kolom, dan ziet u Sort A to Z en Sort Z to A; bij getallen ziet u Sort Smallest to Largest en Sort Largest to Smallest; bij datums ziet u Sort Oldest to Newest en Sort Newest to Oldest.

1.4 Rechtermuisknop sorteeropties

In Excel kunt u ook snel en eenvoudig sorteren via de rechtermuisknop. Klik met de rechtermuisknop op een cel in een kolom met waarden die u wilt sorteren. In het contextmenu ziet u zes sorteeropties. Kies de gewenste optie, zie screenshot:

Basisgebruik van sorteren

Met deze Excel Sorteerfunctie kunt u eenvoudig sorteren, zoals getallen, tekststrings of datums oplopend of aflopend sorteren, of cellen sorteren op basis van lettertype- of achtergrondkleur. In dit gedeelte bespreken we enkele basisgebruikstoepassingen van deze sorteerfunctie.

2.1 Gegevens sorteren op tekst, getallen of datums

Om een bereik te sorteren op tekst, getallen of datums in oplopende of aflopende volgorde, volgt u deze stappen:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sort, zie screenshot:

2. Voer in het Sorteerdialoogvenster de volgende handelingen uit:

• Selecteer in het gedeelte Kolom de kolomnaam waarop u wilt sorteren;
• Selecteer in het gedeelte Sorteren op de optie Celwaarden;
• Specificeer in het gedeelte Volgorde de sorteervolgorde. (Voor tekststrings kiest u A to Z of Z to A; voor getallen kiest u Smallest to Largest of Largest to Smallest; voor datums kiest u Oldest to Newest of Newest to Oldest.)
• Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. De geselecteerde gegevens worden direct gesorteerd op de door u opgegeven kolom.

2.2 Gegevens sorteren op celkleur, lettertypekleur, celpictogram

Als u het gegevensbereik wilt sorteren op celkleur, lettertypekleur of een pictogram van voorwaardelijke opmaak, kan de Sorteerfunctie deze taak snel uitvoeren.

Stel dat u een gegevensbereik heeft met celkleuren, zoals hieronder te zien is. Als u de gegevens wilt herschikken op basis van celkleur, bijvoorbeeld de lichtrode rijen bovenaan, gevolgd door lichtgeel en lichtblauw, sorteert u de rijen op celkleur als volgt:

1. Selecteer het gegevensbereik dat u wilt sorteren op celkleur en klik vervolgens op Data > Sort om naar het Sorteerdialoogvenster te gaan.

2. Geef in het Sorteerdialoogvenster de volgende instellingen op:

2.1) Stel de eerste celkleur bovenaan in als volgt:

• Selecteer in het gedeelte Kolom de kolomnaam waar de gekleurde cellen zich bevinden, bijvoorbeeld Name of een andere kolom. In dit voorbeeld zijn alle kolommen gekleurd, dus u kunt elke kolomnaam kiezen;
• Selecteer onder het gedeelte Sorteren op de optie Celkleur;
• Kies in het gedeelte Volgorde de celkleur die u bovenaan of onderaan wilt plaatsen;

2.2) Klik vervolgens op de knop Add Level om de tweede en andere regel toe te voegen. Herhaal bovenstaande stappen voor het instellen van de volgende celkleuren.

• Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Nadat u de instellingen heeft voltooid, klikt u op OK. Het gegevensbereik is nu gesorteerd op de door u opgegeven celkleur, zie screenshot:

Tips: Volgens bovenstaande stappen kunt u ook sorteren op lettertypekleur of celpictogram door te kiezen voor de Lettertypekleur of Pictogram voorwaardelijke opmaak in het Sorteerdialoogvenster.

2.3 Gegevens sorteren op meerdere kolommen

Als u een grote dataset heeft zoals hieronder getoond, en u wilt een meerlagige sortering uitvoeren voor meer overzicht, bijvoorbeeld eerst sorteren op de kolom Regio, dan op Staat en tot slot op Verkoop, hoe doet u dit in Excel?

Om gegevens op meerdere kolommen te sorteren, volgt u deze stappen:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sort om naar het Sorteerdialoogvenster te gaan.

2. Klik in het Sorteerdialoogvenster twee keer op de knop Add Level, omdat u drie kolommen wilt gebruiken voor sorteren. U ziet nu twee Then by-regels toegevoegd aan de lijst:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Selecteer in de vervolgkeuzelijsten Sort by en Then by de kolomnamen waarop u wilt sorteren. Kies vervolgens per geselecteerde kolom Cell Values in het gedeelte Sorteren op en bepaal tot slot de sorteervolgorde naar wens.

4. Klik vervolgens op OK en u krijgt het sorteerveld zoals hieronder getoond:

2.4 Gegevens sorteren op basis van een aangepaste lijst

In plaats van alfabetisch of numeriek te sorteren, biedt deze Sorteerfunctie ook sorteren op een aangepaste lijst. Stel dat u het onderstaande gegevensbereik wilt sorteren op Status - Not Started, In Progress, Completed, zoals u zelf heeft gedefinieerd. Hier leest u hoe u dit doet.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sort om naar het Sorteerdialoogvenster te gaan.

2. Selecteer in het Sorteerdialoogvenster de kolom waarop u wilt sorteren in het gedeelte Kolom, kies vervolgens Cell Values in het gedeelte Sorteren op, en klik in het gedeelte Volgorde op de optie Custom List, zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Er verschijnt een Custom Lists-venster. Voer de volgende handelingen uit:

• Klik op NEW LIST in het Custom lists-venster;
• Voer de tekstwaarden in de gewenste sorteervolgorde in het vak List entries in; (Druk op Enter om de items te scheiden.)
• Klik tot slot op de knop Add. De nieuwe lijst wordt direct toegevoegd aan het Custom lists-venster.

4. Klik vervolgens op OK om terug te keren naar het Sorteerdialoogvenster. De nieuwe aangepaste lijst wordt nu weergegeven in de vervolgkeuzelijst Volgorde.

5. Klik vervolgens op OK om het dialoogvenster te sluiten. Het gegevensbereik is nu gesorteerd volgens de door u gedefinieerde aangepaste lijst, zie screenshot:

2.5 Gegevens van links naar rechts sorteren (horizontaal sorteren)

Normaal gesproken sorteert u een tabel verticaal van boven naar beneden, maar soms wilt u misschien sorteren op basis van rijwaarden (dus van links naar rechts). Bijvoorbeeld: in het onderstaande gegevensbereik wil ik sorteren op de waarden in de rij Name.

In dit geval heeft de Sorteerfunctie een ingebouwde mogelijkheid om van links naar rechts te sorteren. Volg hiervoor deze stappen:

1. Selecteer het gegevensbereik (zonder kopteksten) dat u wilt sorteren en klik vervolgens op Data > Sort om naar het Sorteerdialoogvenster te gaan.

2. Klik in het geopende Sorteerdialoogvenster op de knop Options. Selecteer vervolgens in het dialoogvenster Sort Options de optie Sort left to right, zie screenshot:

3. Klik op OK om terug te keren naar het Sorteerdialoogvenster. Geef nu in het gedeelte Rij het rijnummer op waarop u wilt sorteren, selecteer Cell Values in het gedeelte Sorteren op en kies tot slot de gewenste sorteervolgorde in het gedeelte Volgorde, zie screenshot:

4. Klik vervolgens op OK en uw gegevens zijn gesorteerd zoals hieronder getoond:

2.6 Gegevens sorteren met hoofdlettergevoeligheid

Standaard wordt er niet hoofdlettergevoelig gesorteerd, zoals te zien is in de eerste screenshot hieronder. Maar wat als u hoofdlettergevoelig wilt sorteren, zoals in de tweede screenshot? Hoe pakt u dit aan in Excel?

Om hoofdlettergevoelig te sorteren in Excel, doet u het volgende:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sort om naar het Sorteerdialoogvenster te gaan.

2. Geef in het Sorteerdialoogvenster in het gedeelte Kolom de kolomnaam op waarop u wilt sorteren, selecteer Cell Values in het gedeelte Sorteren op en kies tot slot de gewenste sorteervolgorde in het gedeelte Volgorde, zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op de knop Options in dit dialoogvenster en vink in het Sort Options-venster de optie Case sensitive aan, zie screenshot:

4. Klik op OK > OK om de vensters te sluiten. De gegevens worden nu hoofdlettergevoelig gesorteerd, waarbij kleine letters eerst komen en daarna hoofdletters als de tekst verder gelijk is. Zie screenshot:

Geavanceerd gebruik van sorteren

In uw dagelijkse werk kunt u te maken krijgen met complexere, praktische en specifieke sorteervraagstukken. In dit gedeelte bespreken we verschillende soorten sorteertaken in Excel.

3.1 Gegevens sorteren op basis van frequentie

Stel dat u een lijst met gegevens in een kolom heeft en u wilt deze kolom sorteren op aflopende frequentie van voorkomen, zoals hieronder getoond. Hier introduceer ik twee methoden om deze taak uit te voeren.

Gegevens sorteren op frequentie met een hulpkolom

In Excel is er geen directe manier om te sorteren op basis van het aantal voorkomens. U kunt echter een hulpkolom met een formule maken om de frequentie te berekenen en vervolgens sorteren op deze hulpkolom.

1. Typ de onderstaande formule in een lege cel naast de originele gegevens, bijvoorbeeld in B2, en sleep de vulgreep naar beneden om de formule toe te passen, zie screenshot:

=COUNTIF($A$2:$A$16,A2)

Opmerking: In bovenstaande formule is A2:A16 de lijst met gegevens die u op frequentie wilt sorteren, en A2 is het eerste item in deze lijst.

2. Selecteer vervolgens de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshots:

3. Klik vervolgens op de knop Sort. De originele kolom is nu gesorteerd op frequentie, zie onderstaande screenshots:

Gegevens sorteren op frequentie met een handige functie

Als u Kutools voor Excel gebruikt, kunt u met de functie Geavanceerd sorteren de gegevens snel en eenvoudig sorteren op basis van het aantal voorkomens, zonder een hulpkolom te maken.

1. Selecteer de gegevenslijst die u wilt sorteren en klik vervolgens op Kutools Plus > Sorteren > Geavanceerd sorteren, zie screenshot:

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Frequentie in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde, zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. De gegevenslijst is direct gesorteerd op aantal voorkomens, zie screenshot:

3.2 Gegevens sorteren op basis van tekenlengte

Als u een lijst met gegevens heeft met tekststrings van verschillende lengtes, wilt u deze mogelijk sorteren op tekenlengte om de kolom overzichtelijk te maken. In dit gedeelte leest u hoe u sorteert op het aantal tekens.

Gegevens sorteren op tekenlengte met een hulpkolom

Om een kolom te sorteren op tekenlengte gebruikt u de functie LEN om het aantal tekens per cel te berekenen. Vervolgens past u de Sorteerfunctie toe. Volg deze stappen:

1. Voer deze formule =LEN(A2) in een lege cel naast de originele gegevens in, bijvoorbeeld in B2, en sleep de vulgreep naar beneden om de formule toe te passen, zie screenshot:

2. Selecteer vervolgens de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. De tekststrings zijn nu gesorteerd op tekenlengte. U kunt kolom B verwijderen indien gewenst. Zie screenshot:

Gegevens sorteren op tekenlengte met een eenvoudige optie

Om snel en eenvoudig te sorteren op het aantal tekens, raad ik de handige tool Kutools voor Excel aan. Met de functie Geavanceerd sorteren voert u deze taak moeiteloos uit.

1. Selecteer de gegevenslijst die u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Tekstlengte in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde. Zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. De tekststrings in de lijst zijn nu gesorteerd op tekenlengte, zie screenshot:

3.3 Volledige namen sorteren op achternaam

Bij het sorteren van een lijst met volledige namen gebruikt Excel standaard het eerste teken van de naam. Maar wat als u wilt sorteren op achternaam? Hier leest u enkele handige trucs om eenvoudig op achternaam te sorteren in Excel.

Volledige namen sorteren op achternaam met een hulpkolom

Om volledige namen op achternaam te sorteren, extraheert u eerst de achternamen in een nieuwe kolom en gebruikt u vervolgens de Sorteerfunctie om de volledige namen alfabetisch op achternaam te ordenen. Volg deze stappen:

1. Voer of kopieer de onderstaande formule in een lege cel naast de originele gegevens, bijvoorbeeld in B2, en sleep de vulgreep naar beneden om de formule toe te passen, zie screenshot:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

2. Selecteer vervolgens de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. U krijgt nu de volledige namen gesorteerd op achternaam. U kunt de hulpkolom verwijderen indien gewenst. Zie screenshot:

Volledige namen sorteren op achternaam met een snelle methode

Bent u niet vertrouwd met formules, dan kunt u met de functie Geavanceerd sorteren van Kutools voor Excel de volledige namen op achternaam sorteren zonder formules.

1. Selecteer de cellen met volledige namen die u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Achternaam in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde. Zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. De kolom met volledige namen is nu direct gesorteerd op achternaam.

3.4 E-mailadressen sorteren op domein

Als u een lijst met e-mailadressen wilt sorteren op domein in plaats van op het eerste teken van het adres, hoe doet u dat in Excel? In dit gedeelte bespreken we enkele snelle methoden om e-mailadressen op domein te sorteren.

E-mailadressen sorteren op domein met een hulpkolom

U kunt een formule maken om de e-maildomeinen in een andere kolom te extraheren en vervolgens de originele e-mailadressen sorteren op basis van deze nieuwe domeinkolom.

1. Typ de volgende formule in een lege cel naast uw e-mailadres, bijvoorbeeld in C2, en sleep de vulgreep naar het bereik dat u wilt toepassen. Alle e-maildomeinen worden nu uit de adressen gehaald, zie screenshot:

=RIGHT(B2,LEN(B2)-FIND("@",B2))

2. Selecteer de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Vink in het dialoogvenster Sort Warning de optie Expand the selection aan, zie screenshot:

3. Klik vervolgens op de knop Sort om de gegevens op e-maildomein oplopend of aflopend te sorteren zoals u heeft opgegeven.

4. Na het sorteren kunt u de hulpkolom verwijderen indien gewenst.

E-mailadressen sorteren op domein met enkele klikken

Met Kutools voor Excel en de functie Geavanceerd sorteren kunt u e-mailadressen eenvoudig alfabetisch op domeinnaam sorteren met slechts enkele klikken.

1. Selecteer het volledige gegevensbereik dat u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Maildomein in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde. Zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik op OK. Het gegevensbereik is nu gesorteerd op e-maildomein, zie screenshot:

3.5 Eén kolom sorteren om overeen te komen met een andere

Als u twee kolommen met exact dezelfde of bijna dezelfde gegevens heeft, maar in verschillende volgorde, wilt u mogelijk de kolommen zo sorteren dat dezelfde waarden op dezelfde rijen staan. In dit gedeelte bespreek ik twee gevallen van deze sorteervraag.

Twee kolommen sorteren met exact dezelfde items om overeen te komen

Stel, ik heb twee kolommen met dezelfde items, maar in verschillende volgorde. Ik wil nu de tweede kolom sorteren zodat deze overeenkomt met de eerste kolom, zoals hieronder getoond.

1. Typ de onderstaande formule in een lege cel naast uw originele gegevens, sleep vervolgens de vulgreep om de formule toe te passen op alle cellen in de lijst. Hiermee wordt de positie van elke waarde in kolom B ten opzichte van kolom A weergegeven, zie screenshot:

=MATCH(B2,$A$2:$A$10,0)

2. Selecteer vervolgens kolom B en de nieuwe hulpkolom en klik op Data > Sort om naar het Sorteerdialoogvenster te gaan. Voer in het dialoogvenster Sort de volgende handelingen uit:

• Selecteer de hulpkolom waarop u wilt sorteren in het gedeelte Kolom;
• Kies vervolgens Cell Values in het gedeelte Sorteren op;
• Selecteer tot slot de optie Smallest to Largest in het gedeelte Volgorde.

3. Klik vervolgens op OK. Nu zijn de twee kolommen exact op elkaar afgestemd, zoals hieronder getoond. U kunt de formulekolom verwijderen indien gewenst.

Twee kolommen sorteren met niet exact dezelfde items om overeen te komen

Soms komen de items in twee kolommen niet exact overeen. Bijvoorbeeld: ik wil de gegevens in de tweede kolom sorteren zodat ze overeenkomen met de eerste kolom, zodat dezelfde waarden op dezelfde rijen staan, zoals hieronder getoond.

1. Voeg een nieuwe lege kolom in tussen de twee kolommen.

2. Voer de onderstaande formule in cel B2 in en sleep de vulgreep om de formule toe te passen op de benodigde cellen. Nu ziet u dat de gegevens in kolom C zijn gesorteerd om overeen te komen met kolom A.

=IF(ISNA(MATCH(A2,$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH(A2,$C$2:$C$8,0)))

3.6 Unieke waarden sorteren en extraheren uit een lijst met gegevens

Als u een lijst met waarden heeft waarin dubbele voorkomen, en u wilt alleen de unieke waarden extraheren en alfabetisch sorteren zoals hieronder getoond, introduceer ik hier enkele formules om dit te doen.

1. Voer de onderstaande formule in een lege cel in – C2, en druk vervolgens op Ctrl + Shift + Enter om het eerste resultaat te krijgen, zie screenshot:

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")

Opmerking: In de formule is A2:A12 de lijst met gegevens waaruit u unieke waarden wilt extraheren, en C1 is de cel boven de formule. Pas deze aan naar uw situatie.

2. Sleep vervolgens de vulgreep om de waarden te extraheren totdat er lege cellen verschijnen. Alle unieke waarden worden geëxtraheerd en oplopend gesorteerd, zie screenshot:

3.7 Meerdere rijen of kolommen onafhankelijk tegelijk sorteren

Het is eenvoudig om een gegevensbereik te sorteren op basis van één rij of kolom, maar soms wilt u elke rij of kolom in het bereik afzonderlijk alfabetisch sorteren. Hoe doet u dat in Excel?

Meerdere rijen individueel sorteren

Stel dat u een gegevensbereik heeft dat alfabetisch per rij moet worden herschikt, zoals hieronder getoond. Hier zijn twee snelle trucs om dit probleem op te lossen.

Meerdere rijen onafhankelijk tegelijk sorteren met een formule

1. Kopieer de rijlabels naar een andere locatie waar u het gesorteerde resultaat wilt krijgen.

2. Kopieer of voer vervolgens de onderstaande formule in een lege cel in – H2, en druk op Ctrl + Shift + Enter om het eerste resultaat te krijgen, zie screenshot:

=INDEX($B2:$E2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$E2, "<="&$B2:$E2),0))

3. Selecteer vervolgens de formulecel H2 en sleep de vulgreep naar rechts om de formule te kopiëren naar andere cellen van de eerste rij, bijvoorbeeld tot K2, zie screenshot:

4. Selecteer de formulecellen in de eerste rij (H2:K2) en sleep de vulgreep naar beneden om de formule naar andere rijen te kopiëren. Nu ziet u dat de waarden in elke rij afzonderlijk oplopend zijn gesorteerd.

Meerdere rijen onafhankelijk tegelijk sorteren met VBA-code

De volgende VBA-code kan u ook helpen om de gegevens in elke rij eenvoudig alfabetisch te sorteren. Volg deze stappen:

1. Selecteer de gegevens die u in elke rij wilt sorteren.

2. Houd de toetsen ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

3. Klik vervolgens op Invoegen > Module en plak de onderstaande code in het modulevenster.

VBA-code: Meerdere rijen onafhankelijk tegelijk sorteren

Sub SortIndividualR()
'Updateby Extendoffice
    Dim xRg As Range, yRg As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRg = Selection
    If xRg.Count = 1 Then
        MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Application.ScreenUpdating = False
    For Each yRg In xRg.Rows
        yRg.Sort Key1:=yRg.Cells(1, 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        Orientation:=xlSortRows
    Next yRg
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.ScreenUpdating = True
End Sub

4. Druk vervolgens op F5 om deze code uit te voeren. De gegevens in elke rij zijn nu direct oplopend gesorteerd, zie screenshot:

Meerdere kolommen individueel sorteren

Om de gegevens in elke kolom afzonderlijk te sorteren, kunnen de volgende twee methoden u helpen.

Meerdere kolommen onafhankelijk tegelijk sorteren met een formule

1. Kopieer de kolomlabels naar een andere locatie waar u het gesorteerde resultaat wilt krijgen.

2. Voer vervolgens de onderstaande formule in een lege cel in – F3, druk op Ctrl + Shift + Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep naar beneden om deze formule naar andere rijen te kopiëren, zie screenshot:

=INDEX(A$3:A$6,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$6,"<="&A$3:A$6),0))

3. Selecteer de formulecellen in de eerste rij (F3:F6) en sleep de vulgreep naar rechts om de formule naar andere kolommen te kopiëren. Nu zijn de waarden in elke kolom afzonderlijk oplopend gesorteerd, zoals hieronder getoond:

Meerdere kolommen onafhankelijk tegelijk sorteren met VBA-code

Om de gegevens in meerdere kolommen onafhankelijk te sorteren, kunt u ook de volgende VBA-code gebruiken. Doe het als volgt:

1. Houd de toetsen ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik vervolgens op Invoegen > Module en plak de onderstaande code in het modulevenster.

VBA-code: Meerdere kolommen onafhankelijk tegelijk sorteren

Sub SortIndividualJR()
'Updateby Extendoffice
    Dim xRg As Range
    Dim yRg As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Kutools for excel", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

3. Druk vervolgens op F5 om deze code uit te voeren. Er verschijnt een prompt waarin u het gegevensbereik selecteert dat u wilt sorteren, zie screenshot:

4. Klik vervolgens op OK. Elke kolom wordt nu afzonderlijk gesorteerd.

3.8 Gegevens willekeurig sorteren in Excel

Het is gebruikelijk om gegevens alfabetisch oplopend of aflopend te sorteren, maar heeft u ooit geprobeerd gegevens in een geselecteerd bereik willekeurig te sorteren? In dit gedeelte leest u hoe u een bereik met cellen in willekeurige volgorde schudt.

Een kolom gegevens willekeurig sorteren met een hulpkolom

Normaal gesproken kunt u de functie RAND gebruiken om willekeurige getallen te genereren en vervolgens de gegevens sorteren op basis van deze willekeurige lijst. Doe het als volgt:

1. Voer deze formule in: =RAND() in een lege cel naast uw gegevens en sleep de vulgreep naar beneden om een lijst met willekeurige getallen te genereren, zoals hieronder getoond:

2. Selecteer de formulecellen en klik vervolgens op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. De gegevenslijst wordt direct geschud, zie screenshot:

Cellen, rijen of kolommen willekeurig sorteren met een geweldige functie

Kutools voor Excel biedt een krachtige functie – Willekeurig sorteren, waarmee u gegevens in een bereik van cellen, in elke kolom/rij van een selectie, of volledige rijen of kolommen in één keer kunt randomiseren.

1. Selecteer het gegevensbereik dat u willekeurig wilt sorteren en klik vervolgens op Kutools Plus > Sorteren > Willekeurig sorteren. Zie screenshot:

2. Kies in het Sorteren/selecteren willekeurig dialoogvenster onder het Sorteren tabblad een van de volgende opties die u nodig heeft:

• Volledige rijen: Schud de volledige rijen in het geselecteerde bereik willekeurig.
• Volledige kolommen: Schud de volledige kolommen in het geselecteerde bereik willekeurig.
• Cellen in elke rij: Schud cellen in elke rij afzonderlijk.
• Cellen in elke kolom: Schud cellen in elke kolom afzonderlijk.
• Alle cellen in het bereik: Randomiseer alle cellen in het geselecteerde bereik.

3. Klik vervolgens op Ok. De gegevens zijn direct gerandomiseerd.

3.9 Een lijst met datums sorteren op maand, dag of maand en dag

Bij het sorteren van datums in Excel worden de datums standaard gesorteerd op jaar, maand en dag. In sommige gevallen wilt u echter sorteren op alleen maand, dag, of maand en dag, waarbij het jaar wordt genegeerd. Hoe lost u dit op in Excel?

Een lijst met datums sorteren op maand of dag met een hulpkolom

Om datums alleen op maand of dag te sorteren, extraheert u de maand- of dagnummers uit de datums en sorteert u vervolgens op deze nieuwe kolom.

1. Voer de onderstaande formule in een lege cel naast uw gegevens in en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren, zie screenshot:

=MONTH(B2) (maandnummer extraheren)
=DAY(B2) (dagnummers extraheren)

2. Nadat u de maand- of dagnummers heeft verkregen, selecteert u de formulecellen en klikt u op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. De datums zijn nu gesorteerd op maand, waarbij jaren en dagen worden genegeerd. Zie screenshot:

Een lijst met datums sorteren op maand of dag met enkele klikken

Met Kutools voor Excel en de functie Geavanceerd sorteren kunt u een lijst met datums met slechts enkele klikken sorteren op alleen maand of dag.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Maand of Dag in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde.

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. De gegevens zijn nu gesorteerd op maand of dag zoals u heeft opgegeven, waarbij het jaar wordt genegeerd, zie screenshot:

Een lijst met datums sorteren op maand en dag met een hulpkolom

Wilt u een lijst met datums sorteren op alleen maand en dag zonder het jaar, dan kunt u de functie TEKST gebruiken om een datum om te zetten naar een tekststring in het gewenste formaat en vervolgens de Sorteerfunctie toepassen.

1. Voer de onderstaande formule in een lege cel naast uw gegevens in en kopieer deze formule naar beneden tot het einde van uw gegevens. De maand en dag zijn nu als tekststring geëxtraheerd, zie screenshot:

=TEXT(B2,"MMDD")

2. Selecteer de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. Uw gegevens zijn nu alleen gesorteerd op maand en dag.

3.10 Een lijst met datums sorteren op dag van de week

Om een lijst met datums te sorteren op dag van de week, dus van maandag tot zondag of van zondag tot zaterdag, introduceer ik in dit gedeelte twee methoden om deze taak in Excel uit te voeren.

Een lijst met datums sorteren op dag van de week met een hulpkolom

Om datums te sorteren op dag van de week heeft u ook een hulpkolom nodig die een getal retourneert dat overeenkomt met de dag van de week. Sorteer vervolgens de datums op deze hulpkolom.

1. Voer of kopieer de onderstaande formule in een lege cel in en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren, zie screenshot:

=WEEKDAY(B2)(Start vanaf zondag (1) tot zaterdag (7))
=WEEKDAY(B2,2)(Start vanaf maandag (1) tot zondag (7))

2. Selecteer de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort om het gewenste sorteerveld te krijgen, zie screenshot:

Een lijst met datums sorteren op dag van de week met een eenvoudige optie

Met de functie Geavanceerd sorteren van Kutools voor Excel kunt u dit probleem snel oplossen zonder hulpkolom.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Dag van de week in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde.

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. De gegevens zijn nu gesorteerd op dag van de week zoals u heeft opgegeven. Zie screenshot:

3.11 Een lijst met datums sorteren op kwartaal

In dit gedeelte leest u hoe u een lijst met datums sorteert op kwartaal, waarbij het jaar wordt genegeerd. Twee methoden worden besproken.

Een lijst met datums sorteren op kwartaal met een hulpkolom

Net als bij bovenstaande oplossingen maakt u een hulpkolom met een formule om het kwartaalnummer uit de opgegeven datums te halen en sorteert u vervolgens de datums op deze nieuwe hulpkolom.

1. Voer of kopieer de onderstaande formule in een lege cel in en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren, zie screenshot:

=ROUNDUP(MONTH(B2)/3,0)

2. Selecteer de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. Het gegevensbereik is nu gesorteerd op kwartaal, zie screenshot:

Een lijst met datums sorteren op kwartaal met een handige functie

Als u Kutools voor Excel heeft geïnstalleerd, kunt u met de functie Geavanceerd sorteren deze taak met slechts enkele klikken uitvoeren.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Kwartaal in de vervolgkeuzelijst Sorteren op. Geef tot slot de gewenste sorteervolgorde op in het gedeelte Volgorde.

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik tot slot op OK. Het geselecteerde bereik is nu gesorteerd op kwartaal zoals u heeft opgegeven.

3.12 Gegevens sorteren op maandnamen of weekdagen

Als u een lijst met maandnamen als tekst heeft, worden deze alfabetisch gesorteerd in plaats van op maandvolgorde van januari tot december. Wilt u sorteren van januari tot december, dan kunt u een aangepaste sortering in de Sorteerfunctie gebruiken.

1. Selecteer het gegevensbereik dat u wilt sorteren op maandnamen en klik op Data > Sort om naar het Sorteerdialoogvenster te gaan.

2. Selecteer in het Sorteerdialoogvenster in het gedeelte Kolom de kolomnaam met de maandnamen, kies in het gedeelte Sorteren op de optie Celwaarden en selecteer tot slot in het gedeelte Volgorde de optie Custom List, zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Selecteer in het geopende Custom List-dialoogvenster de volledige maandnamen (Januari, Februari, Maart, …) of korte namen (Jan, Feb, Mrt…) afhankelijk van hoe de maanden in uw werkblad staan, zie screenshot:

4. Klik vervolgens op OK > OK om de vensters te sluiten. Uw gegevens zijn nu gesorteerd op maandnaam in chronologische volgorde, zie screenshot:

Tips: Om op weekdagen te sorteren, selecteert u de volledige namen (Zondag, Maandag, Dinsdag, …) of korte namen (Zo, Ma, Di…) in het dialoogvenster Aangepaste lijsten.

3.13 Gegevens sorteren op oneven of even getallen

Het is eenvoudig om getallen oplopend of aflopend te sorteren in Excel, maar heeft u ooit geprobeerd getallen van oneven naar even of andersom te sorteren? In dit gedeelte bespreek ik enkele methoden om dit te doen.

Gegevens sorteren op oneven of even getallen met een hulpkolom

Om de lijst met getallen te sorteren op oneven of even getallen, maakt u een formule om deze te identificeren en past u vervolgens de Sorteerfunctie toe.

1. Voer naast de lijst met getallen deze formule in: =ISODD(A2) in een lege cel en sleep de formule naar andere cellen. U ziet nu TRUE en FALSE, waarbij TRUE staat voor oneven en FALSE voor even getallen.

2. Selecteer de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. Alle even getallen zijn nu bij elkaar gesorteerd, gevolgd door de oneven getallen of andersom. Zie screenshot:

Gegevens sorteren op oneven of even getallen met een handige functie

Met de functie Geavanceerd sorteren van Kutools voor Excel kunt u snel getallen van oneven naar even of andersom sorteren.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Oneven en even getallen in het gedeelte Sorteren op. Geef vervolgens de sorteervolgorde op (A to Z sorteert van even naar oneven, Z to A van oneven naar even), zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat de optie My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. U krijgt het volgende resultaat:

3.14 Gegevens sorteren op absolute waarden

Als er zowel positieve als negatieve getallen in de kolom staan, worden deze standaard oplopend of aflopend gesorteerd in Excel. In sommige gevallen wilt u echter het minteken negeren en sorteren op absolute waarde. Hoe doet u dit in Excel?

Gegevens sorteren op absolute waarden met een hulpkolom

Om getallen op absolute waarde te sorteren, maakt u normaal gesproken een formule om alle waarden eerst om te zetten naar absolute waarden en past u vervolgens de Sorteerfunctie toe.

1. Voer in een lege cel naast uw gegevens, bijvoorbeeld B2, deze formule in: =ABS(A2), en sleep de vulgreep om de formule naar andere cellen te kopiëren. Nu zijn alle waarden omgezet naar absolute waarden:

2. Selecteer de formulecellen en klik op Data > Sort A to Z of Sort Z to A naar wens. Kies in het pop-upvenster Sort Warning voor Expand the selection, zie screenshot:

3. Klik vervolgens op de knop Sort. Alle getallen zijn nu gesorteerd op absolute waarde, zie screenshot:

Gegevens sorteren op absolute waarden met een handige optie

Wilt u geen hulpkolom maken, dan raad ik Kutools voor Excel aan. Met de functie Geavanceerd sorteren kunt u direct en eenvoudig sorteren op absolute waarde.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik op Kutools Plus > Sorteren > Geavanceerd sorteren.

2. Selecteer in het dialoogvenster Geavanceerd sorteren de kolom waarop u wilt sorteren in het gedeelte Kolom en kies Absolute waarde in het gedeelte Sorteren op. Geef vervolgens de sorteervolgorde op, zie screenshot:

Tips: Als uw gegevens kopteksten bevatten, zorg er dan voor dat My data has headers is aangevinkt. Als uw gegevens geen kopteksten hebben, vink deze optie dan uit.

3. Klik vervolgens op OK. Alle getallen worden nu gesorteerd van kleinste naar grootste of van grootste naar kleinste waarde, waarbij het minteken wordt genegeerd.

Gegevens automatisch sorteren bij het invoeren of wijzigen van gegevens

De Sorteerfunctie in Excel is niet dynamisch; u moet de gegevens telkens opnieuw sorteren na elke wijziging of wanneer er nieuwe gegevens worden toegevoegd. In dit gedeelte bespreek ik hoe u uw gegevens automatisch sorteert telkens wanneer er een nieuwe waarde wordt toegevoegd.

4.1 Automatisch numerieke waarden sorteren in een kolom met formules

Om een lijst met getallen automatisch oplopend of aflopend te sorteren, gebruikt u formules op basis van de functies GROOTSTE, KLEINSTE en RIJ.

1. Voer of kopieer de onderstaande formule in een lege cel naast uw brongegevens in en sleep de vulgreep naar beneden om de formule toe te passen. De getallen worden nu van klein naar groot gerangschikt, zie screenshot:

=IFERROR(SMALL($A$2:$A$100,ROWS(B$2:B2)),"")

Opmerking: In deze formule is A2:A100 de getallenlijst die u automatisch wilt sorteren, inclusief enkele lege cellen voor nieuwe invoer. B2 is de cel waar u de formule invoert.

2. Wanneer u nu de brongegevens wijzigt of nieuwe gegevens invoert, wordt de gesorteerde lijst automatisch bijgewerkt, zie onderstaande demo:

4.2 Automatisch tekstwaarden sorteren in een kolom met formules

Wilt u tekstwaarden in een kolom automatisch sorteren, dan werkt bovenstaande formule niet. Gebruik in dat geval onderstaande formule:

1. Voer of kopieer de onderstaande formule in een lege cel naast uw gegevenskolom in, druk op Ctrl + Shift + Enter om de eerste tekst te krijgen, selecteer vervolgens de formulecel en sleep de vulgreep naar beneden om de formule toe te passen, zie screenshot:

=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")

2. Vanaf nu worden bij het invoeren of wijzigen van waarden in kolom A de tekststrings in kolom B automatisch oplopend gesorteerd, zie onderstaande demo:

4.3 Automatisch een mix van numerieke en tekstwaarden sorteren in een kolom met VBA-code

Als er zowel numerieke als tekstwaarden in een kolom staan, kunt u onderstaande VBA-code gebruiken om de lijst automatisch te sorteren.

1. Klik met de rechtermuisknop op het bladtap waar u automatisch wilt sorteren en kies View Code in het contextmenu. Kopieer en plak in het geopende Microsoft Visual Basic for Applications-venster de onderstaande code in het lege modulevenster, zie screenshot:

VBA-code: automatisch sorteren bij invoer of wijziging van gegevens in oplopende volgorde:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Opmerking: In bovenstaande code worden de ingevoerde gegevens automatisch gesorteerd in kolom A. A1 is de koptekst en A2 is de eerste cel van de gegevenslijst.

2. Sla het codevenster op en sluit het. Wanneer u nu nieuwe gegevens invoert of bestaande gegevens in kolom A wijzigt, worden de gegevens automatisch oplopend gesorteerd. Zie onderstaande demo:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Andere gevallen van sorteren

U kunt in uw dagelijkse werk met andere sorteervragen te maken krijgen. In dit gedeelte bespreek ik enkele andere sorteertypen, zoals sorteren binnen een cel, gegevens in een kolom of rij omkeren, en meer.

5.1 Gegevens binnen een cel sorteren

Om een tekststring binnen één cel alfabetisch te sorteren, bijvoorbeeld "HDAW" sorteren als "ADHW"; of meerdere woorden gescheiden door komma's in een cel sorteren, zoals "word, outlook, excel, access" als "access, excel, outlook, word". In dit gedeelte leest u hoe u dit oplost in Excel.

Tekenreekswaarde binnen een cel alfabetisch sorteren

Om een tekenreekswaarde binnen een cel alfabetisch te sorteren, maakt u een zelfgedefinieerde functie. Doe het als volgt:

1. Houd ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module en plak de onderstaande code in het modulevenster.

VBA-code: Tekstwaarde binnen cel sorteren

Function SortCellContents(xRange As Range)
'Updateby Extendoffice
Dim xArr
Dim xF1, xF2 As Integer
Dim xStrValue As String
Dim xStrT As String
If xRange.Count <> 1 Then
    Exit Function
End If
xStrValue = xRange.Value
ReDim xArr(1 To Len(xStrValue))
For xF1 = 1 To UBound(xArr)
    xArr(xF1) = Mid(xStrValue, xF1, 1)
Next
For xF1 = 1 To UBound(xArr)
    For xF2 = xF1 To UBound(xArr)
        If Asc(xArr(xF2)) < Asc(xArr(xF1)) Then
            xStrT = xArr(xF2)
            xArr(xF2) = xArr(xF1)
            xArr(xF1) = xStrT
        End If
    Next xF2
Next xF1
SortCellContents = Join(xArr, "")
End Function

3. Sla deze code op en sluit het venster. Ga terug naar uw werkblad en voer de formule =SortCellContents(A2) in een lege cel in waar u het resultaat wilt hebben. Sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren. Alle tekstwaarden in de cellen worden nu alfabetisch gesorteerd, zie screenshot:

Tekststrings gescheiden door een scheidingsteken binnen een cel alfabetisch sorteren

Als er meerdere woorden zijn die in een cel door een specifiek scheidingsteken zijn gescheiden, kunt u onderstaande zelfgedefinieerde functie gebruiken om ze alfabetisch te sorteren binnen de cel.

1. Houd ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module en plak de onderstaande code in het modulevenster.

VBA-code: Tekststrings gescheiden door komma binnen cel sorteren

Function SortCellWithSeparator(CellAddress As Range, DelimiterChar As String, IncludeSpaces As Boolean) As String
'Updateby Extendoffice
Dim xRg As Range
Dim xString As String
Dim xF1, xF2 As Integer
Dim xSArr
Dim xStrT As String
Dim xStrValue As String
Set xRg = CellAddress
xStrValue = WorksheetFunction.Substitute(xRg.Value, " ", "")
xSArr = Split(xStrValue, DelimiterChar)
    For xF1 = 0 To UBound(xSArr)
        For xF2 = xF1 + 1 To UBound(xSArr)
                If xSArr(xF2) < xSArr(xF1) Then
                    xStrT = xSArr(xF2)
                    xSArr(xF2) = xSArr(xF1)
                    xSArr(xF1) = xStrT
                End If
        Next xF2
    Next xF1
xStrValue = ""
For xF1 = 0 To UBound(xSArr)
    xStrValue = xStrValue & xSArr(xF1) & DelimiterChar
Next xF1
SortCellWithSeparator = xStrValue
SortCellWithSeparator = Left(SortCellWithSeparator, Len(SortCellWithSeparator) - 1)
If IncludeSpaces = True Then SortCellWithSeparator = WorksheetFunction.Substitute(SortCellWithSeparator, ",", ", ")
End Function

3. Sla deze code op en sluit het venster. Ga terug naar uw werkblad, voer de formule =SortCellWithSeparator(A2,",",TRUE) in een lege cel in waar u het resultaat wilt hebben en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren. Alle tekststrings in de cellen worden nu alfabetisch gesorteerd, zie screenshot:

Opmerking: Als uw tekststrings door een ander scheidingsteken zijn gescheiden, vervangt u de komma in deze formule door uw eigen scheidingsteken.

5.2 De volgorde van gegevens in een kolom of rij omkeren

Soms wilt u de volgorde van gegevens omkeren: van boven naar beneden in een verticale reeks of van links naar rechts in een horizontale reeks. In dit gedeelte worden drie methoden besproken om dit in Excel te doen.

De volgorde van gegevens in een kolom of rij omkeren met formules

De volgende formules helpen u de volgorde van gegevens in een kolom of rij om te keren. Doe het als volgt:

De volgorde van gegevens in een kolom omkeren

Voer of kopieer de volgende formule in een lege cel in waar u het resultaat wilt hebben en sleep de vulgreep naar beneden om de celwaarden in omgekeerde volgorde te krijgen, zie screenshot:

=OFFSET($A$10,-(ROW(A1)-1),0)

Opmerking: In bovenstaande formule is A1 de eerste cel en A10 de laatste cel in de kolom.

De volgorde van gegevens in een rij omkeren

Gebruik onderstaande formule om de volgorde van gegevens horizontaal in een rij om te keren:

=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)

Opmerking: In bovenstaande formule is A1 de eerste cel in de rij en1:1 het rijnummer waar uw gegevens staan. Als de gegevens in rij10 staan, vervangt u dit door10:10.

Sleep vervolgens de vulgreep naar rechts tot alle waarden zijn geëxtraheerd. U krijgt nu alle gegevens horizontaal omgekeerd, zie screenshot:

De volgorde van gegevens in meerdere kolommen of rijen omkeren met VBA-code

Bovenstaande formules werken alleen goed voor één kolom of rij. Als u meerdere kolommen of rijen wilt omkeren, kunt u onderstaande VBA-codes gebruiken.

De volgorde van gegevens in een bereik van cellen verticaal omkeren

1. Maak eerst een back-up van uw originele gegevens en houd vervolgens ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module en plak de onderstaande code in het modulevenster.

VBA-code: Een bereik van cellen verticaal omkeren

Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

3. Druk vervolgens op F5 om deze code uit te voeren. Er verschijnt een prompt waarin u het gegevensbereik selecteert dat u verticaal wilt omkeren, zie screenshot:

4. Klik vervolgens op OK. Het gegevensbereik is nu verticaal omgekeerd, zie screenshots:

De volgorde van gegevens in een bereik van cellen horizontaal omkeren

Wilt u het gegevensbereik horizontaal omkeren, gebruik dan onderstaande VBA-code:

VBA-code: Een bereik van cellen verticaal omkeren

Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

Na het uitvoeren van deze code zijn de gegevens in de rijen horizontaal omgekeerd, zie screenshots:

De volgorde van gegevens in kolommen of rijen omkeren met slechts één klik

Met Kutools voor Excel en de functies Keer reeks verticaal om en Keer reeks horizontaal om kunt u een bereik van cellen verticaal of horizontaal omkeren met slechts één klik.

De volgorde van gegevens in een bereik van cellen verticaal omkeren

1. Selecteer het gegevensbereik dat u wilt omkeren en klik op Kutools > Bereik > Keer reeks verticaal om > Alles / Alleen inhoud omkeren, zie screenshot:

Tips: Als u de optie Alles kiest, worden alle celopmaak en waarden omgekeerd; kiest u Alleen inhoud omkeren, dan worden alleen de celwaarden omgekeerd.

2. Het gegevensbereik wordt nu direct verticaal omgekeerd.

De volgorde van gegevens in een bereik van cellen horizontaal omkeren

1. Selecteer het gegevensbereik en klik op Kutools > Bereik > Keer reeks horizontaal om > Alles / Alleen inhoud omkeren, zie screenshot:

Tips: Als u de optie Alles kiest, worden alle celopmaak en waarden omgekeerd; kiest u Alleen inhoud omkeren, dan worden alleen de celwaarden omgekeerd.

2. Alle celwaarden in de selectie worden nu direct horizontaal omgekeerd, zie screenshots:

5.3 Werkbladtabs alfabetisch of op tabkleur sorteren in Excel

Stel, u heeft een groot aantal werkbladen in uw werkboek. Als u de bladtabs alfabetisch of op tabkleur wilt sorteren, kost de normale methode (bladtabs slepen) veel tijd. In dit gedeelte bespreek ik enkele trucs om de bladtabs snel oplopend of aflopend te rangschikken.

Werkbladtabs alfabetisch sorteren met VBA-code

Met onderstaande VBA-code kunt u de bladtabs alfabetisch oplopend of aflopend sorteren. Doe het als volgt:

1. Houd ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module en plak de onderstaande macro in het modulevenster.

VBA: Alle werkbladen alfabetisch sorteren

Sub SortWorkBook()
'Updateby Extendoffice
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Click Yes to sort sheets in ascending order;" & Chr(10) & "Click No will sort in descending order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. Druk vervolgens op F5 om deze macro uit te voeren. In het volgende venster klikt u op Yes om alle werkbladen oplopend alfabetisch te sorteren, of op No om aflopend te sorteren.

Werkbladtabs alfabetisch / alfanumeriek sorteren met een krachtige functie

Kutools voor Excel biedt een krachtige functie – Alle tabbladen sorteren, waarmee u werkbladen alfabetisch of alfanumeriek kunt sorteren, sorteren op tabkleur of de tabs omkeren.

1. Open het werkboek waarvan u de bladtabs wilt sorteren en klik op Kutools Plus > Werkblad > Alle tabbladen sorteren, zie screenshot:

2. Selecteer in het dialoogvenster Alle tabbladen sorteren aan de rechterkant het gewenste sorteertype, zoals Alfabetisch sorteren of Exact sorteren. Zie screenshot:

3. Klik vervolgens op Ok. Alle werkbladen worden nu gesorteerd op het door u opgegeven sorteertype. Zie screenshots:

Werkbladtabs sorteren op tabkleur met VBA-code

Het is gebruikelijk om werkbladtabs alfabetisch te sorteren in Excel, maar heeft u ooit geprobeerd te sorteren op tabkleur? Met onderstaande VBA-code lost u dit eenvoudig op. Doe het als volgt:

1. Houd ALT + F11 ingedrukt om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik op Invoegen > Module en plak de onderstaande macro in het modulevenster.

VBA: Alle werkbladen sorteren op tabkleur

Sub SortWorkBookByColor()
'Updateby20140624
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
    For i = 1 To Application.ActiveWorkbook.Worksheets.Count
        If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
            n = n + 1
            ReDim Preserve xArray1(1 To n)
            ReDim Preserve xArray2(1 To n)
            xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
            xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
        End If
    Next
    For i = 1 To n
        For j = i To n
            If xArray1(j) < xArray1(i) Then
                temp = xArray2(i)
                xArray2(i) = xArray2(j)
                xArray2(j) = temp
                temp = xArray1(i)
                xArray1(i) = xArray1(j)
                xArray1(j) = temp
            End If
        Next
    Next
    For i = n To 1 Step -1
        Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
    Next
End If
Application.ScreenUpdating = True
End Sub

3. Druk vervolgens op F5 om deze code uit te voeren. De werkbladen met dezelfde tabkleur worden nu bij elkaar gesorteerd. Zie screenshots:

Werkbladtabs sorteren op tabkleur met een geweldige functie

Met de functie Alle tabbladen sorteren van Kutools voor Excel kunt u werkbladtabs ook snel en eenvoudig op kleur sorteren.

1. Klik op Kutools Plus > Werkblad > Alle tabbladen sorteren om het Alle tabbladen sorteren dialoogvenster te openen. Klik in het Alle tabbladen sorteren dialoogvenster op de knop Kleursortering aan de rechterkant, zie screenshot:

2. Klik vervolgens op Ok. De werkbladen met dezelfde tabkleur worden nu bij elkaar geplaatst, zie screenshots: