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

Het is een veelvoorkomende en eenvoudige taak om gegevens in Excel te sorteren, wat u kan helpen uw gegevens opnieuw te ordenen op basis van het type sortering dat u kiest. Normaal gesproken kunt u met de ingebouwde sorteerfunctie getallen, tekstreeksen, datums en tijden in een of meer kolommen sorteren; U kunt gegevens ook snel en gemakkelijk sorteren op een aangepaste lijst die u hebt gemaakt, of op celopmaak (zoals tekstkleur, achtergrondkleur of pictogram).

Naast deze eenvoudige sortering is er nog veel meer nuttige en complexe sortering in uw dagelijkse werk die u misschien nodig heeft. Deze tutorial introduceert een aantal verschillende soorten sortering voor het oplossen van uw probleem in Excel.

Toegang tot de sorteeropties in Excel

Om de sorteerfunctie toe te passen, biedt Excel verschillende manieren om toegang te krijgen tot de sorteeropties.

1.1 Sorteerknoppen in het lint

De snelste manier om de sorteerfunctie toe te passen, is door de sorteerknoppen in het lint te gebruiken.

Klik op een cel in een kolom met waarden die moeten worden gesorteerd en klik vervolgens op Data tab, in de Sorteren en filteren sectie, klik op Sorteer A tot Z or Sorteer Z naar A om de gegevens in oplopende of aflopende alfabetische volgorde te sorteren. Zie screenshot:

Note: Als er lege rijen in het gegevensbereik zijn, kan na het toepassen van deze knoppen alleen het gegevensbereik van de geselecteerde cel die boven of onder de lege rijen met succes worden gesorteerd.

1.2 Dialoogvenster Sorteren

In het Sorteren en filteren groep van de Data tabblad, er is nog een Sorteer knop, zie screenshot:

Nadat je hierop hebt geklikt Sorteer knop, een Sorteer dialoogvenster verschijnt zoals hieronder afgebeeld:

Vervolgens in de Sorteer dialoogvenster, kunt u desgewenst overeenkomstige regels kiezen voor het sorteren van gegevens.

Tips: U kunt ook sneltoetsen gebruiken om dit te openen Sorteer dialoogvenster, druk a.u.b. op Alt+A+S+S toetsen achter elkaar op het toetsenbord.

1.3 Sorteeropties in het menu Filter

Als je filters hebt toegepast op je gegevensbereik, kun je naast de filteropties ook sorteeropties vinden. Als u op het filterpictogram in een willekeurige kolom klikt, ziet u de sorteeropties in de uitgevouwen lijst zoals hieronder afgebeeld:

Note: Deze sorteeropties veranderen op basis van de gegevens in de kolom: Als er tekst in de kolom staat, wordt dit weergegeven: Sorteer A tot Z, Sorteer Z naar A; Als er getallen in de kolom staan, wordt dit weergegeven: Sorteer kleinste naar grootste, Sorteer van groot naar klein; Als er datums in de kolom staan, wordt dit weergegeven Sorteer Oudste naar Nieuw, Sorteer van nieuwste naar oudste.

1.4 Rechtsklik sorteeropties

In Excel kunt u ook rechtsklik-sorteeropties gebruiken om gegevens snel en gemakkelijk te sorteren, klik met de rechtermuisknop op een cel in een kolom met waarden die moeten worden gesorteerd, en in het contextmenu ziet u dat er zes sorteeropties beschikbaar zijn, selecteer alstublieft degene die je nodig hebt, zie screenshot:

Basisgebruik van sorteren

Deze Excel-sorteerfunctie kan u helpen om eenvoudig te sorteren, zoals het sorteren van getallen, tekenreeksen, datums in oplopende of aflopende volgorde, het sorteren van cellen op basis van lettertype of achtergrondkleur. In dit gedeelte wordt ingegaan op enkele basisgebruiken van deze functie.

2.1 Sorteer gegevens op tekst, cijfers of datums

Ga als volgt te werk om een ​​reeks gegevens te sorteren op basis van teksten, getallen of datums in oplopende of aflopende volgorde:

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

2. In de Sorteer dialoogvenster voert u de volgende bewerkingen uit:

• In het Kolom sectie, selecteer de kolomnaam waarop u wilt sorteren;
• In het Sorteer op sectie, selecteer Celwaarden keuze;
• In het Bestelling sectie, geef de sorteervolgorde op. (Als u tekstreeksen wilt sorteren, selecteert u a.u.b. A tot Z or Z tot A; Om de nummerlijst te sorteren, selecteer Kleinste tot grootste or Grootste tot kleinste; Om datumcellen te sorteren, selecteert u Van oud naar nieuw or Nieuw naar oud.)
• Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK knop, worden de geselecteerde gegevens gesorteerd op basis van de kolom die u in één keer hebt opgegeven.

2.2 Sorteer gegevens op celkleur, letterkleur, celpictogram

Als u het gegevensbereik wilt sorteren op basis van de celkleur, letterkleur of voorwaardelijke opmaakpictogram, kan de sorteerfunctie deze taak snel oplossen.

Stel dat u een gegevensbereik hebt dat is opgemaakt met enkele celkleuren zoals hieronder afgebeeld, als u de gegevens opnieuw moet rangschikken op basis van de celkleur, bijvoorbeeld, wilt u de lichtrode rijen bovenaan plaatsen, gevolgd door licht gele en lichtblauwe rijen, om de rijen op celkleur te sorteren, gaat u als volgt te werk:

1. Selecteer het gegevensbereik dat u wilt sorteren op basis van de celkleur en klik vervolgens op Data > Sorteer naar de Sorteer dialoog venster.

2. In de Sorteer dialoogvenster, geef de volgende bewerkingen op:

2.1) Stel de eerste celkleur als volgt bovenaan in:

• In het Kolom sectie, selecteer Naam of andere kolommen waar u de gekleurde cellen hebt. In dit voorbeeld heb ik gekleurde cellen in alle kolommen, je kunt elke kolomnaam kiezen;
• Onder de Sorteer op sectie, selecteer alstublieft Cel kleur keuze;
• In het Bestelling sectie, kies een celkleur die u bovenaan of onderaan wilt plaatsen;

2.2) Klik vervolgens op de Voeg niveau toe knop om de tweede en andere regelniveaus toe te voegen, herhaal de bovenstaande stappen voor het instellen van de tweede en andere celkleuren.

• Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Klik na het voltooien van de instellingen op OK knop, en het gegevensbereik is gesorteerd op de celkleur die u hebt opgegeven, zie screenshot:

Tips: Volgens de bovenstaande stappen kunt u de gegevens ook sorteren op basis van de kleur van het lettertype of het celpictogram door de . te kiezen font Color or Pictogram voor voorwaardelijke opmaak in het dialoogvenster Sorteren.

2.3 Gegevens sorteren op meerdere kolommen

Als u een grote gegevensset heeft zoals hieronder afgebeeld, wilt u nu een gegevenssortering op meerdere niveaus uitvoeren om de gegevens duidelijker te lezen, bijvoorbeeld eerst sorteren op de kolom Regio en vervolgens de kolom Staat, als laatste kolom Verkoop. Hoe zou je deze sortering in Excel kunnen doen?

Voer de onderstaande stappen uit om gegevens op meerdere kolommen te sorteren:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sorteer naar de Sorteer dialoog venster.

2. In de Sorteer dialoogvenster, klik Voeg niveau toe knop tweemaal, omdat er drie kolommen zijn die u wilt gebruiken om te sorteren. Dan zie je er twee Dan door regelniveaus zijn toegevoegd aan de keuzelijst:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Van de Sorteer op en Dan door vervolgkeuzelijst, selecteer de kolomnamen waarop u wilt sorteren en selecteer vervolgens Celwaarden van het Sorteer op sectie afzonderlijk voor elke geselecteerde kolom, kies ten slotte de sorteervolgorde die u nodig hebt.

4. Dan klikken OK, en u krijgt het sorteerresultaat zoals hieronder afgebeeld:

2.4 Sorteer gegevens op basis van een aangepaste lijst

In plaats van gegevens in alfabetische of numerieke volgorde te sorteren, biedt deze sorteerfunctie ook een aangepaste lijstsortering voor u. U wilt bijvoorbeeld het onderstaande gegevensbereik sorteren op Status - Niet gestart, In uitvoering, Voltooid, die u hebt gedefinieerd. Hier zal ik introduceren hoe om te gaan met dit soort sortering.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sorteer naar de Sorteer dialoog venster.

2. In de Sorteer dialoogvenster, selecteer de kolom die u wilt sorteren uit de Kolom sectie en kies vervolgens Celwaarden van het Sorteer op sectie, in de Bestelling sectie, klik op Aangepaste lijst optie, zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. En een Aangepaste lijsten box zal verschijnen, voer de volgende handelingen uit:

• Klik NIEUWE LIJST in de Eigen lijsten doos;
• Voer vervolgens de tekstreeksen in de volgorde in waarop u moet sorteren in de Lijstitems doos; (Bij het invoeren van de teksten, druk a.u.b. op Enter toets om de items te scheiden.)
• Eindelijk, klik Toevoegen knop, wordt de nieuwe lijst toegevoegd aan de Aangepaste lijsten doos tegelijk.

4. Dan klikken OK om terug te keren naar de Sorteer dialoog venster. De nieuwe aangepaste lijst met items wordt nu weergegeven in de Bestelling keuzelijst.

5. En klik dan OK knop om het dialoogvenster te sluiten, als resultaat is het gegevensbereik gesorteerd op de aangepaste lijst die u hebt gedefinieerd, zie screenshot:

2.5 Gegevens van links naar rechts sorteren (horizontaal sorteren)

Normaal gesproken sorteert u de gegevenstabel altijd verticaal van boven naar beneden, maar soms wilt u de gegevens misschien sorteren op basis van de rijwaarden (sorteer van links naar rechts). In het onderstaande gegevensbereik wil ik het bijvoorbeeld sorteren op basis van de waarden in de rij Naam.

In dit geval heeft de sorteerfunctie een ingebouwde functionaliteit waarmee u van links naar rechts kunt sorteren. Gelieve te doen met de onderstaande stappen:

1. Selecteer uw gegevensbereik (exclusief kopteksten) die u wilt sorteren en klik vervolgens op Data > Sorteer om naar het dialoogvenster Sorteren te gaan.

2. In de pop-out Sorteer dialoogvenster, klik Opties knop, dan, in de Sorteer Opties dialoogvenster, selecteer Sorteer van links naar rechts optie, zie screenshot:

3. Klikken OK om terug te gaan naar het dialoogvenster Sorteren, nu, in de Rij sectie, geef het rijnummer op waarop u gegevens wilt sorteren en selecteer vervolgens Celwaarden in de Sorteer op kies tot slot de sorteervolgorde in de Bestelling sectie die je nodig hebt, zie screenshot:

4. Dan klikken OK, en uw gegevens worden gesorteerd zoals hieronder getoond screenshot:

2.6 Gegevens sorteren in hoofdlettergevoelige volgorde

Over het algemeen wordt het sorteren van gegevens niet uitgevoerd in hoofdlettergevoelige volgorde, zoals hieronder wordt weergegeven. Maar wat als u de sortering hoofdlettergevoelig wilt maken, zoals hieronder wordt getoond, hoe zou u deze taak in Excel kunnen oplossen?

Ga als volgt te werk om hoofdlettergevoelig te sorteren in Excel:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Data > Sorteer naar de Sorteer dialoog venster.

2. In de Sorteer dialoogvenster, in de Kolom sectie, geef de kolomnaam op waarop u gegevens wilt sorteren en selecteer vervolgens Celwaarden in de Sorteer op kies tot slot de sorteervolgorde in de Bestelling sectie die je nodig hebt, zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Ga verder met het klikken op de Opties knop in dit dialoogvenster, en in de Sorteer Opties promptvenster, vink aan Hoofdlettergevoelig optie, zie screenshot:

4. Klikken OK > OK om de dialoogvensters te sluiten, worden de gegevens in hoofdlettergevoelige volgorde gesorteerd met eerst kleine letters en vervolgens hoofdletters als de tekst hetzelfde is. Zie screenshot:

Geavanceerd gebruik van sorteren

In uw dagelijkse werk kunt u andere, meer complexe, praktische, specifieke sorteerproblemen tegenkomen. In dit gedeelte worden meerdere soorten sorteertaken in Excel besproken.

3.1 Sorteer gegevens op frequentie

Ervan uitgaande dat u een lijst met gegevens in een kolom hebt, en nu wilt u deze kolom sorteren in aflopende volgorde van de frequenties van het voorkomen van de gegevens, zoals hieronder afgebeeld. Hier zal ik twee methoden introduceren om deze taak op te lossen.

 Sorteer gegevens op basis van frequentie met een hulpkolom

In Excel is er geen directe manier om gegevens te sorteren op basis van het aantal exemplaren. Hier kunt u een hulpformule maken om de frequentie van het voorkomen van elke tekst te krijgen en vervolgens sorteren op de hulpkolom om het gewenste sorteerresultaat te krijgen.

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

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

Note: In de bovenstaande formule, A2: A16 is de lijst met gegevens die u op frequentie wilt sorteren, en A2 zijn de eerste gegevens van deze lijst.

2. Blijf vervolgens de formulecellen selecteren en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshots:

3. En klik vervolgens op het Sorteer knop, nu is de originele kolom gesorteerd op frequentie zoals in de volgende screenshots:

 Sorteer gegevens op frequentie met een handige functie

Als je Kutools for Excel, Met Geavanceerd sorteren functie kunt u de gegevens snel en gemakkelijk sorteren op basis van de frequentie van voorkomen zonder hulpkolom.

Na het installeren van Kutools for Excel, doe dit als volgt:

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

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren uit de Kolom sectie en kies vervolgens Frequentie in de Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op in de Bestelling sectie, zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK knop, de gegevenslijst is in één keer gesorteerd op aantal exemplaren, zie screenshot:

3.2 Gegevens sorteren op tekenlengte

Als u een lijst met gegevens hebt die tekstreeksen met verschillende lengtes bevatten, moet u de gegevens nu mogelijk sorteren op tekenlengte om de kolom er netjes en opgeruimd uit te laten zien. In dit gedeelte wordt besproken hoe u gegevens kunt sorteren op het aantal tekens.

 Sorteer gegevens op basis van tekenlengte met een hulpkolom

Om de kolom op lengte van tekens te sorteren, moet u de LEN-functie gebruiken om het aantal tekens voor elke cel te berekenen en vervolgens de functie Sorteren toepassen om de gegevenslijst te sorteren, doe dit als volgt:

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

2. Blijf vervolgens de formulecellen selecteren en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. Dan klikken Sorteer knop, zijn de tekenreeksen gesorteerd op tekenlengte. U kunt de helperkolom B naar behoefte verwijderen. Zie screenshot:

 Sorteer gegevens op basis van tekenlengte met een eenvoudige optie

Om gegevens snel en gemakkelijk op het aantal tekens te sorteren, raad ik hier een handig hulpmiddel aan: Kutools for Excel, Met Geavanceerd sorteren functie, kunt u deze taak gemakkelijk aan.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer de gegevenslijst die u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren in de Kolom sectie, en kies Tekstlengte van het Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op die u nodig hebt in de Bestelling sectie. Zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben headers iis gecontroleerd. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK knop, zijn de tekstreeksen in de lijst gesorteerd op tekenlengte, zoals hieronder afgebeeld:

3.3 Sorteer volledige namen op achternaam

Bij het sorteren van een lijst met volledige namen gebruikt Excel het eerste teken van de naam om te sorteren, maar wat als u gegevens op achternaam wilt sorteren? Hier zal ik enkele trucs voor je meenemen om gemakkelijk volledige namen op achternaam in Excel te sorteren.

 Sorteer volledige namen op achternaam met een hulpkolom

Als u volledige namen sorteert op basis van de achternamen, moet u de achternamen extraheren in een nieuwe kolom en vervolgens de functie Sorteren toepassen om de volledige namen alfabetisch te sorteren op basis van de gescheiden achternamen. Gelieve te doen met de volgende stappen:

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

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

2. Ga door met het selecteren van de formulecellen en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. Dan klikken Sorteer knop, en u krijgt de volledige namen die zijn gesorteerd op basis van de achternamen. Als u klaar bent, kunt u de helperkolom naar behoefte verwijderen. Zie screenshot:

 Sorteer volledige namen op achternaam met een snelle methode

Als u niet bekend bent met de formule, om de volledige namen op achternaam te sorteren zonder enige formule, de Geavanceerd sorteren kenmerk van Kutools for Excel kan je een plezier doen.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer de cellen met de volledige naam die u wilt sorteren en klik op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren in de Kolom sectie, en kies Achternaam van het Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op zoals u nodig hebt in de Bestelling sectie. Zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK knop, is de kolom met de volledige naam in één keer op achternaam gesorteerd.

3.4 E-mailadressen sorteren op domein

Als u een lijst met e-mailadressen hebt die op domein moeten worden gesorteerd in plaats van alleen op de eerste letter van het adres, hoe zou u dat dan in Excel kunnen doen? In dit gedeelte worden enkele snelle methoden besproken voor het sorteren van e-mailadressen op domein.

 Sorteer e-mailadressen op domein met een helperkolom

Hier kunt u een formule maken om de e-maildomeinen naar een andere kolom te extraheren en vervolgens de oorspronkelijke e-mailadressen sorteren op basis van de nieuwe domeinkolom.

1. Typ de volgende formule in een lege cel naast uw e-mailadres, in dit voorbeeld voer ik deze formule in cel C2 in, sleep vervolgens de vulgreep naar het bereik waarop u deze formule wilt toepassen. Alle e-maildomeinen worden dan uit de adressen gehaald, zie screenshot:

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

2. Houd de formulecellen geselecteerd en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de Sorteer waarschuwing dialoogvenster, vink aan Breid de selectie uit, zie screenshot:

3. En klik dan Sorteer om de gegevens te sorteren op e-maildomeinen in oplopende of aflopende volgorde zoals u hebt opgegeven.

4. Na het sorteren kunt u de helperkolom naar behoefte verwijderen.

 Sorteer e-mailadressen op domein met enkele klikken

Als je Kutools for Excel, Met Geavanceerd sorteren functie kunnen de e-mailadressen met slechts enkele muisklikken alfabetisch op domeinnamen worden gesorteerd.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het hele gegevensbereik dat u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren in de Kolom sectie, en kies Mail domein van het Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op die u nodig hebt in de Bestelling sectie. Zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Klikken OK, en het gegevensbereik is gesorteerd op e-maildomeinen zoals hieronder afgebeeld:

3.5 Sorteer de ene kolom zodat deze overeenkomt met de andere

Als u twee kolommen heeft met gegevens die exact hetzelfde of bijna hetzelfde zijn, maar in verschillende volgorden, moet u nu mogelijk de twee kolommen sorteren zodat dezelfde waarden worden uitgelijnd op dezelfde rijen in de twee kolommen. In deze sectie zal ik twee gevallen introduceren voor dit type sortering.

 Sorteer twee kolommen met exact dezelfde items om te matchen

Ik heb bijvoorbeeld twee kolommen die dezelfde items bevatten, maar in verschillende volgorden, hier wil ik de tweede kolom sorteren zodat deze overeenkomt met de eerste kolom, zoals hieronder wordt weergegeven.

1. Typ de onderstaande formule in een lege cel naast uw originele gegevens en sleep vervolgens de vulgreep om deze formule toe te passen op alle cellen in de lijst, en dit zal de positie van elke waarde in kolom B tegen in kolom A retourneren, zie screenshot :

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

2. Selecteer vervolgens de kolom B en de nieuwe helperkolom en klik op Data > Sorteer naar de Sorteer dialoogvenster, in de Sorteer dialoogvenster voert u de volgende bewerkingen uit:

• Selecteer de helperkolom waarop u gegevens wilt sorteren op basis van de Kolom sectie;
• Kies dan Celwaarden in de Sorteer op sectie;
• Selecteer ten slotte Kleinste tot grootste optie in het Bestelling pagina.

3. En klik dan OK knop. Nu krijgt u de twee kolommen die precies overeenkomen zoals hieronder afgebeeld. U kunt de formulekolom naar behoefte verwijderen.

 Sorteer twee kolommen met niet exact dezelfde items om overeen te komen

Soms zijn de items in twee kolommen niet precies hetzelfde. Ik wil bijvoorbeeld de gegevens in de tweede kolom sorteren zodat ze overeenkomen met die in de eerste kolom, zodat dezelfde waarden op dezelfde rijen worden uitgelijnd als hieronder wordt weergegeven.

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

2. Voer de onderstaande formule in cel B2 in en sleep vervolgens de vulgreep om deze formule te vullen met cellen die u nodig hebt. En nu kunt u zien dat de gegevens in kolom C zijn gesorteerd om overeen te komen met de gegevens in 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 je een lijst met waarden hebt die enkele duplicaten bevat, wil je nu alleen de unieke waarden extraheren en alfabetisch sorteren zoals hieronder afgebeeld, hier zal ik enkele formules introduceren voor het omgaan met dit type sortering.

1. Voer de onderstaande formule in een lege cel in - C2, en druk vervolgens op Ctrl + Shift + Enter toetsen 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)),"")

Note: In de formule, A2: A12 is de gegevenslijst waaruit u unieke waarden wilt extraheren, C1 is de cel boven de formule die u plaatst. Verander ze naar uw behoefte.

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

3.7 Sorteer meerdere rijen of kolommen afzonderlijk tegelijk

Het is gemakkelijk voor ons om een ​​reeks gegevens te sorteren op basis van één rij of kolom, maar soms wilt u misschien elke rij of kolom in het bereik afzonderlijk alfabetiseren, hoe zou u deze taak in Excel kunnen bereiken?

 Sorteer meerdere rijen afzonderlijk

Stel dat u een reeks gegevens hebt die alfabetisch in elke rij opnieuw moet worden gerangschikt, zoals de onderstaande schermafbeelding, om dit probleem op te lossen, zijn hier twee snelle trucs voor u.

Sorteer meerdere rijen onafhankelijk tegelijk met formule

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

2. En kopieer of voer dan de onderstaande formule in een lege cel in - H2, en druk vervolgens op Ctrl + Shift + Enter toetsen samen 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 naar andere cellen van de eerste rij te kopiëren, sleep in dit voorbeeld naar cel K2, zie screenshot:

4. Ga door met het selecteren van de formulecellen in de eerste rij (H2:K2) en sleep de vulgreep naar beneden om de formule naar andere rijen te kopiëren. Nu kunt u zien dat de waarden in elke rij afzonderlijk in oplopende volgorde zijn gesorteerd.

Sorteer meerdere rijen onafhankelijk tegelijk met VBA-code

De volgende VBA-code kan u ook helpen om de gegevens in elke rij gemakkelijk alfabetisch te sorteren. Gelieve te doen met de volgende stappen:

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

2. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

3. Dan klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: sorteer meerdere rijen onafhankelijk tegelijk

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 toets om deze code uit te voeren, zijn de gegevens in elke rij onmiddellijk in oplopende volgorde gesorteerd, zie screenshot:

 Sorteer meerdere kolommen afzonderlijk

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

Sorteer meerdere kolommen onafhankelijk tegelijk met formule

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

2. En voer vervolgens de onderstaande formule in een lege cel in - F3, en druk op Ctrl + Shift + Enter toetsen samen om het eerste resultaat te krijgen, 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. Ga door met het selecteren van 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 in oplopende volgorde gesorteerd, zoals hieronder afgebeeld:

Sorteer meerdere kolommen tegelijk onafhankelijk met VBA-code

Om de gegevens onafhankelijk van elkaar in meerdere kolommen te sorteren, kan de volgende VBA-code u ook een plezier doen, doe dit als volgt:

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Dan klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: sorteer meerdere kolommen onafhankelijk tegelijk

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 toets om deze code uit te voeren, en er verschijnt een promptvenster, selecteer het gegevensbereik dat u wilt sorteren, zie screenshot:

4. En klik dan OK, elke kolom is snel afzonderlijk gesorteerd.

3.8 Gegevens willekeurig sorteren in Excel

Het is gebruikelijk dat we de gegevens alfabetisch sorteren in oplopende of aflopende volgorde, maar heeft u ooit geprobeerd om gegevens willekeurig in een geselecteerd bereik te sorteren? In deze sectie zal ik het hebben over hoe je een reeks cellen in willekeurige volgorde kunt schudden.

 Sorteer een kolom met gegevens willekeurig met een hulpkolom

Normaal gesproken kunt u een RAND-functie gebruiken om willekeurige getallen te krijgen en vervolgens de gegevens sorteren op basis van deze willekeurige lijst, doe dit 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 vullen, zoals hieronder afgebeeld:

2. Blijf de formulecellen selecteren en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. En klik dan Sorteer knop, de gegevenslijst is in één keer geschud, zie screenshot:

 Sorteer cellen, rijen of kolommen willekeurig met een geweldige functie

Kutools for Excel ondersteunt een krachtige functie - Sorteer bereik willekeurigMet deze functie kunt u gegevens willekeurig maken in een celbereik, in elke kolom/rij van een selectie, of hele rijen of kolommen tegelijk willekeurig maken.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het gegevensbereik dat u willekeurig wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Sorteer bereik willekeurig. Zie screenshot:

2. In de Sorteer / selecteer bereik willekeurig dialoogvenster onder het Sorteer tabblad, kies een van de volgende opties die u nodig hebt:

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

3. Dan klikken Ok knop, worden de gegevens onmiddellijk gerandomiseerd.

3.9 Sorteer een lijst met datums op maand , dag of maand en dag

Bij het sorteren van datums in Excel, sorteert de sorteerfunctie de lijst met datums standaard op jaar, maand en dag, maar in bepaalde gevallen moet u de datums mogelijk ordenen op maand of dag, of maand en dag terwijl u de jaar. Hoe dit probleem in Excel op te lossen?

 Sorteer een lijst met datums op maand of dag met een hulpkolom

Om de datums alleen op maand of dag te sorteren, kunt u de maand- of dagnummers uit de datums halen en de datums vervolgens sorteren op de geëxtraheerde maand- of dagnummerskolom.

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

=MONTH(B2)        (extract month number)
= DAG (B2)              (uittreksel dagnummer)

2. Nadat u de maand- of dagnummers hebt geretourneerd, blijft u de formulecellen selecteren en klikt u vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. Dan klikken Sorteer knop, zijn de datums nu gesorteerd op maand, waarbij de jaren en dagen worden genegeerd. Zie screenshot:

 Sorteer een lijst met datums op maand of dag met enkele klikken

Als je Kutools for Excel, Met Geavanceerd sorteren functie kunt u met enkele klikken een lijst met datums sorteren op maand of dag.

Na het installeren van Kutools for Excel, voer dan de volgende stappen uit:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren in de Kolom sectie, en kies Maand or Dag van het Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op die u nodig hebt in de Bestelling pagina.

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK knop, de gegevens zijn gesorteerd op basis van de maand of dag zoals u hebt opgegeven terwijl u het jaar negeert, zie screenshot:

 Sorteer een lijst met datums op maand en dag met een hulpkolom

Als u nu een lijst met datums op maand en dag alleen zonder jaar wilt sorteren, kan de TEKST-functie helpen om een ​​datum naar een tekstreeks in de opgegeven indeling te converteren en vervolgens de sorteerfunctie naar behoefte toe te passen.

1. Voer de onderstaande formule in een lege cel naast uw gegevens in en kopieer deze formule vervolgens naar de onderkant van uw gegevens, de maand en dag zijn geëxtraheerd als tekstreeks zoals hieronder afgebeeld:

=TEXT(B2,"MMDD")

2. Houd de formulecellen geselecteerd en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. Dan klikken Sorteer knop, en nu worden uw gegevens alleen op maand en dag gesorteerd.

3.10 Sorteer een lijst met datums op basis van dagen van de week

Een lijst met datums sorteren op basis van dagen van de week, wat inhoudt dat u datums sorteert van maandag tot zondag of van zondag tot zaterdag. In deze sectie zal ik twee methoden introduceren om deze taak in Excel te voltooien.

 Sorteer een lijst met datums op dagen van de week met een hulpkolom

Door datums te sorteren op dagen van de week, hebt u ook een hulpkolom nodig om een ​​getal terug te geven dat overeenkomt met de dagen van de week, en vervolgens de datums te sorteren op de hulpkolom.

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

=WEEKDAY(B2)           (Start from Sunday (1) to Saturday(7))
= WEEKDAG (B2,2)       (Start van maandag (1) tot zondag (7))

2. Houd de formulecellen geselecteerd en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. En klik vervolgens op het Sorteer knop om het sorteerresultaat te krijgen dat je nodig hebt, zie screenshot:

 Sorteer een lijst met datums op dagen van de week met een eenvoudige optie

Dankzij Kutools for Excel's Geavanceerd sorteren functie, kunt u dit probleem zo snel mogelijk oplossen zonder hulpformule.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren in de Kolom sectie, en kies Dag van de week van het Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op die u nodig hebt in de Bestelling pagina.

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK knop, zijn de gegevens naar behoefte gesorteerd op dagen van de week. Zie screenshot:

3.11 Sorteer een lijst met datums op basis van kwartaal

In dit gedeelte wordt besproken hoe u een lijst met datums per kwartaal kunt sorteren, waarbij het jaar wordt genegeerd. Er worden twee trucs voor u gegeven.

 Sorteer een lijst met datums per kwartaal met een hulpkolom

Net als de bovenstaande oplossingen, moet u een formulehulpkolom maken om het kwartaalnummer uit de gegeven datums te extraheren en vervolgens de datums sorteren op basis van deze nieuwe hulpkolom.

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

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

2. Blijf de formulecellen selecteren en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. En klik vervolgens op het Sorteer knop, is het gegevensbereik gesorteerd op kwartaal, zoals hieronder wordt weergegeven:

 Sorteer een lijst met datums per kwartaal met een handige functie

Als je Kutools for Excel geïnstalleerd, de Geavanceerd sorteren functie kan u helpen om deze taak met meerdere klikken op te lossen.

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, selecteer de kolom die u wilt sorteren in de Kolom sectie, en kies Quarter van het Sorteer op vervolgkeuzelijst, geef ten slotte de sorteervolgorde op die u nodig hebt in de Bestelling pagina.

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Klik ten slotte op OK knop, en het geselecteerde bereik wordt naar behoefte gesorteerd op kwartaal.

3.12 Sorteer gegevens op basis van maandnamen of weekdagnamen

Stel dat je een lijst met maandnamen als tekst hebt, dan zullen ze bij het sorteren van de maandnamen alfabetisch gerangschikt worden in plaats van te sorteren op maandvolgorde van januari tot december. Als u de maandnamen van januari tot december moet sorteren, kan een aangepaste sortering binnen de sorteerfunctie u een plezier doen.

1. Selecteer het gegevensbereik dat u wilt sorteren op basis van maandnamen en klik vervolgens op Data > Sorteer naar de Sorteer dialoog venster.

2. In de Sorteer dialoogvenster, in de Kolom sectie, selecteer de kolomnaam bevat de maandnamen, in de Sorteer op sectie, kies Celwaarden, ten slotte, in de Bestelling sectie, selecteer Aangepaste lijst, zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Toen kwam het eruit Aangepaste lijst dialoogvenster, selecteer volledige maandnamen (januari, februari, maart, ...) of korte namen (jan, februari, maart ...) op basis van hoe de maanden in uw werkblad worden weergegeven, zie screenshot:

4. En klik dan OK > OK om de dialoogvensters te sluiten, en nu zijn uw gegevens gesorteerd op maandnaam in chronologische volgorde, zoals hieronder afgebeeld:

Tips: Om te sorteren op weekdagnamen, selecteert u de volledige namen (zondag, maandag, dinsdag, …) of korte namen (zo, ma, di…) in het Aangepaste lijsten dialoogvenster zoals je nodig hebt.

3.13 Sorteer gegevens op oneven of even getallen

Het is misschien gemakkelijk voor ons om getallen in oplopende of aflopende volgorde in Excel te sorteren, maar heb je ooit geprobeerd de getallen van oneven naar even of even naar oneven in een lijst te sorteren? In deze sectie zal ik enkele methoden introduceren om deze taak uit te voeren.

 Sorteer gegevens op oneven of even getallen met een hulpkolom

Als u de lijst met getallen sorteert op oneven of even getallen, moet u een formule maken om de oneven of even getallen te identificeren en vervolgens de functie Sorteren toepassen.

1. Voer naast de lijst met getallen deze formule in = ISODD (A2) in een lege cel en sleep om de formule naar andere cellen te kopiëren, nu kun je zien dat WAAR en ONWAAR worden weergegeven, WAAR geeft oneven getallen aan en ONWAAR geeft even getallen aan.

2. Blijf de formulecellen selecteren en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. Dan klikken Sorteer knop, zijn alle even getallen bij elkaar gesorteerd en vervolgens gevolgd door de oneven getallen of omgekeerd. Zie screenshot:

 Sorteer gegevens op oneven of even getallen met een handige functie

Dankzij Kutools for Excel's Geavanceerd sorteren functie kunt u de getallen snel sorteren van oneven naar even getallen of omgekeerd.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, klik op de kolom waaronder u wilt sorteren Kolom sectie en kies vervolgens Oneven en even aantal van het Sorteer op sectie, geef dan de sorteervolgorde op (A tot Z sorteer van even naar oneven getallen, en Z tot A sorteer van oneven naar even getallen), zie screenshot:

Tips: Als er koppen in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is nagekeken. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. En klik vervolgens op OK knop, krijgt u de volgende resultaten:

3.14 Gegevens sorteren op basis van absolute waarden

Als er zowel positieve als negatieve getallen in de kolom staan, worden de getallen bij het sorteren standaard in oplopende of aflopende volgorde in Excel gerangschikt. Maar in bepaalde gevallen wilt u misschien het minteken negeren bij het sorteren van gegevens, dat betekent dat u de getallen op absolute waarden sorteert. Hoe deze taak in Excel te bereiken?

 Sorteer gegevens op absolute waarden met een hulpkolom

Als u getallen op absolute waarden wilt sorteren, moet u normaal gesproken een formule maken om alle waarden eerst naar absolute waarden te converteren en vervolgens de functie Sorteren toepassen om de getallen te sorteren.

1. Voer deze formule in een aangrenzende lege cel in, bijvoorbeeld B2 = ABS (A2)en sleep vervolgens de vulgreep om deze formule naar andere cellen te kopiëren. Nu kunt u zien dat alle waarden zijn geconverteerd naar absolute waarden:

2. Houd de formulecellen geselecteerd en klik vervolgens op Data > Sorteer A tot Z or Sorteer Z naar A zoals je nodig hebt, in de pop-up Sorteer waarschuwing promptvenster, selecteer Breid de selectie uit, zie screenshot:

3. Dan klikken Sorteer knop, en alle nummers zijn gesorteerd op de absolute waarden zoals hieronder getoond screenshot:

 Sorteer gegevens op absolute waarden met een handige optie

Als je moe bent van het maken van een helperkolom, zal ik het hier aanbevelen Kutools for Excel, Met Geavanceerd sorteren functie kunt u de getallen direct en eenvoudig op absolute waarden sorteren.

Na het installeren van Kutools for Excel, voer dan de volgende stappen uit:

1. Selecteer het gegevensbereik dat u wilt sorteren en klik vervolgens op Koetools Plus > Sorteer > Geavanceerd sorteren.

2. In de Geavanceerd sorteren dialoogvenster, klik op de kolom waaronder u wilt sorteren Kolom sectie en kies vervolgens absoluut waarde van de Sorteer op sectie, specificeer dan de sorteervolgorde, zie screenshot:

Tips: Als er kopteksten in uw gegevens staan, zorg er dan voor dat Mijn gegevens hebben kopteksten is aangevinkt. Als uw gegevens geen headers hebben, schakelt u deze uit.

3. Dan klikken OK, worden alle getallen gesorteerd van de kleinste naar de grootste of van de grootste naar de kleinste waarden, waarbij het minteken wordt genegeerd.

Gegevens automatisch sorteren bij het invoeren of wijzigen van gegevens

De sorteerfunctie in Excel is niet dynamisch, u zult de gegevens opnieuw moeten sorteren na elke wijziging of wanneer nieuwe gegevens worden toegevoegd. In dit gedeelte zal ik bespreken hoe u uw gegevens automatisch kunt sorteren telkens wanneer een nieuwe waarde wordt toegevoegd aan uw gegevensbereik.

4.1 Automatisch numerieke waarden sorteren in een kolom met formules

Om een ​​lijst met getallen automatisch in oplopende of aflopende volgorde te sorteren, kunt u formules gebruiken op basis van de functies LERGE, SMALL en ROW.

1. Voer de onderstaande formule in of kopieer deze naar een lege cel naast uw brongegevens en sleep vervolgens de vulgreep naar de cellen waarop u deze formule wilt toepassen, en de getallen worden dan gerangschikt van de kleinste naar de grootste waarden, zie screenshot:

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

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

2. Nu, bij het wijzigen van de brongegevens of het invoeren van nieuwe gegevens, wordt de gesorteerde lijst automatisch vernieuwd, zoals hieronder getoonde demo:

4.2 Tekstwaarden automatisch sorteren in een kolom met formules

Als u de tekstwaarden in een kolom automatisch wilt sorteren, werkt de bovenstaande formule niet, hier kan een andere formule u helpen, doe dit als volgt:

1. Voer de onderstaande formule in of kopieer deze naar een lege cel naast uw gegevenskolom en druk vervolgens op Ctrl + Shift + Enter toetsen samen om de eerste tekst te krijgen en selecteer vervolgens de formulecel en sleep de vulgreep naar de cellen die u wilt sorteren, 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, bij het invoeren van waarde of het wijzigen van de originele gegevens in kolom A, worden de tekstreeksen in kolom B automatisch in oplopende volgorde gesorteerd, zie onderstaande demo:

4.3 Sorteer automatisch de mix van numerieke en tekstwaarden in een kolom met VBA-code

Als er zowel numerieke als tekstwaarden in een kolom staan, kan de onderstaande VBA-code u een plezier doen om de lijst met gegevens automatisch te sorteren.

1. Klik met de rechtermuisknop op de bladtab waar u gegevens automatisch wilt sorteren en selecteer vervolgens Bekijk code vanuit het contextmenu, in de pop-out Microsoft Visual Basic voor toepassingen venster, kopieer en plak de volgende code in de lege ruimte Module venster, zie screenshot:

VBA-code: automatisch sorteren wanneer gegevens in oplopende volgorde worden ingevoerd of gewijzigd:

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

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

2. Sla vervolgens het codevenster op en sluit het. Wanneer u nu nieuwe gegevens invoert of originele gegevens wijzigt in kolom A, worden de gegevens automatisch in oplopende volgorde 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 last hebben van andere sorteereisen. In deze sectie zal ik enkele andere sorteertypes introduceren, zoals het sorteren van gegevens in een cel, het sorteren van gegevens in een kolom of rij in omgekeerde volgorde, enzovoort.

5.1 Sorteer gegevens binnen een cel

Om tekstreeksen in een enkele cel in alfabetische volgorde te sorteren, bijvoorbeeld om de tekst "HDAW" te sorteren als "ADHW"; Of om meerdere woorden te sorteren, gescheiden door komma's in een cel, zoals het sorteren van "word, outlook, excel, access" als "access, excel, outlook, word". In deze sectie wordt uitgelegd hoe u dit type sortering in Excel kunt oplossen.

 Sorteer de tekenreekswaarde binnen een cel in alfabetische volgorde

Als u de tekenreekswaarde binnen een cel in alfabetische volgorde sorteert, moet u een door de gebruiker gedefinieerde functie maken. Doe alsjeblieft als volgt:

1. Houd ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: tekstwaarde in 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 vervolgens op en sluit deze, ga terug naar je werkblad. En voer dan deze formule in =Sorteer celinhoud (A2) in een lege cel waar het resultaat moet worden geretourneerd en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren, en dan worden alle tekstwaarden in de cellen in alfabetische volgorde gesorteerd, zie screenshot:

 Sorteer tekstreeksen gescheiden door een scheidingsteken in een cel in alfabetische volgorde

Als er meerdere woorden zijn die door specifieke scheidingstekens in een cel worden gescheiden, om ze in alfabetische volgorde binnen de cel te sorteren, kan de onderstaande door de gebruiker gedefinieerde functie u helpen.

1. Houd ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: sorteer tekstreeksen gescheiden door komma's in cel

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 vervolgens deze code op en sluit deze, keer terug naar uw werkblad en voer deze formule in =SortCellWithSeparator(A2,",",TRUE) in een lege cel waar het resultaat moet worden geretourneerd en sleep vervolgens de vulgreep naar beneden om de formule naar andere cellen te kopiëren, alle tekstreeksen in de cellen worden in alfabetische volgorde gesorteerd, zie screenshot:

Note: Als uw tekenreeksen worden gescheiden door andere scheidingstekens, hoeft u alleen de komma in deze formule te wijzigen in uw eigen scheidingsteken.

5.2 De volgorde van de gegevens in een kolom of rij omkeren / omdraaien

Soms moet u de gegevensvolgorde ondersteboven in een verticaal gegevensbereik of van links naar rechts in een horizontaal gegevensbereik omkeren. In deze sectie worden drie methoden geïntroduceerd om deze taak in Excel op te lossen.

 Keer de volgorde van de gegevens in een kolom of rij om met formules

De volgende formules kunnen helpen om de volgorde van de gegevens in een kolom of rij om te draaien, doe dit als volgt:

Draai de volgorde van de gegevens in een kolom om

Voer de volgende formule in of kopieer deze naar een lege cel waar u het resultaat wilt plaatsen en sleep vervolgens de vulgreep naar de cellen om de celwaarden in omgekeerde volgorde terug te geven, zie screenshot:

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

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

De volgorde van de gegevens op een rij omdraaien

Pas de onderstaande formule toe om de volgorde van gegevens horizontaal op een rij om te draaien:

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

Note: In de bovenstaande formule, A1 is de eerste cel in de rij, en 1:1 is het rijnummer dat uw gegevens hebben gevonden. Als de gegevens in rij 10 staan, moet u deze wijzigen in 10:10.

Sleep vervolgens de vulgreep naar de cellen totdat alle waarden zijn geëxtraheerd en u krijgt alle gegevens horizontaal omgekeerd, zie screenshot:

 Keer de volgorde van de gegevens om in meerdere kolommen of rijen met VBA-code

De bovenstaande formules werken alleen goed voor een enkele kolom of rij, als er meerdere kolommen of rijen zijn met gegevens die moeten worden teruggedraaid, kunnen de volgende VBA-codes u helpen.

Draai de volgorde van de gegevens in een celbereik verticaal om

1. Maak eerst een back-up van uw originele gegevens en houd vervolgens de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: een reeks cellen verticaal in omgekeerde volgorde spiegelen

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 toets om deze code uit te voeren, en er verschijnt een promptvenster waarin u wordt gevraagd om het gegevensbereik te selecteren dat u verticaal wilt omkeren, zie screenshot:

4. Dan klikken OK knop, wordt het gegevensbereik verticaal omgekeerd, zoals hieronder getoonde screenshots:

Draai de volgorde van de gegevens in een celbereik horizontaal om

Pas de onderstaande VBA-code toe om het gegevensbereik in horizontale volgorde om te keren:

VBA-code: een reeks cellen verticaal in omgekeerde volgorde spiegelen

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 rijen horizontaal omgekeerd, zie screenshots:

 Keer/draai de volgorde van de gegevens in kolommen of rijen om met slechts één klik

Als je Kutools for Excel, Met Verticaal bereik omdraaien en Horizontaal bereik omdraaien functies, kunt u het celbereik verticaal en horizontaal omkeren met slechts één klik.

Na het installeren van Kutools for Excel, doe dit als volgt:

Draai de volgorde van de gegevens in een celbereik verticaal om

1. Selecteer het gegevensbereik dat u wilt omkeren en klik vervolgens op Kutools > RANGE > Verticaal bereik omdraaien > Alles / Draai alleen waarden om, zie screenshot:

Tips: Als jij kiest Alles optie, alle celopmaak wordt tegelijkertijd omgekeerd; Als jij kiest Draai alleen waarden om, worden alleen de celwaarden omgekeerd.

2. En dan wordt het gegevensbereik in één keer verticaal omgekeerd.

Draai de volgorde van de gegevens in een celbereik horizontaal om

1. Selecteer het gegevensbereik en klik op Kutools > RANGE > Horizontaal bereik omdraaien > Alle / Alleen waarden omdraaien, zie screenshot:

Tips: Als jij kiest Alles optie, alle celopmaak wordt tegelijkertijd omgekeerd; Als jij kiest Draai alleen waarden om, worden alleen de celwaarden omgekeerd.

2. Vervolgens worden alle celwaarden in de selectie onmiddellijk horizontaal omgekeerd, zoals hieronder getoonde schermafbeeldingen:

5.3 Sorteer werkbladtabbladen in alfabetische volgorde of tabkleur in Excel

Stel dat er een groot aantal werkbladen in uw werkmap zijn, als u de bladtabs in alfabetische volgorde moet sorteren, of de bladtabs moet sorteren op basis van de tabkleur, de normale methode - slepen en neerzetten van bladtabs zal veel tijd verspillen. In deze sectie zal ik enkele trucs bespreken om de bladtabs in oplopende of aflopende volgorde te rangschikken.

 Sorteer werkbladtabbladen in alfabetische volgorde met VBA-code

De volgende VBA-code kan helpen om de bladtabs alfabetisch in oplopende of aflopende volgorde te sorteren, doe dit als volgt:

1. Houd ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende macro in het Module Venster.

VBA: sorteer alle bladen in alfabetische volgorde

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 de F5 toets om deze macro uit te voeren, klik in het volgende promptvenster op Ja, alle werkbladen worden in oplopende alfabetische volgorde gesorteerd; Klik Nee, worden alle werkbladen naar behoefte in aflopende alfabetische volgorde gesorteerd.

 Sorteer werkbladtabbladen in alfabetische / alfanumerieke volgorde met een krachtige functie

Kutools for Excel biedt een krachtige functie - Sorteer bladen, met deze functie kunt u de werkbladen in alfabetische of alfanumerieke volgorde sorteren, de bladen sorteren op basis van de tabkleur of de bladtabs omkeren als u dat wilt.

Na het installeren van Kutools for Excel, voer dan de volgende stappen uit:

1. Open de werkmap waarvan u de bladtabs wilt sorteren en klik vervolgens op Koetools Plus > Werkblad > Sorteer bladen, zie screenshot:

2. In de Sorteer bladen dialoogvenster, selecteert u een sorteringstype dat u nodig hebt in het rechterdeelvenster, zoals Alpha Sorteren, Alfanumeriek sorteren. Zie screenshot:

3. Dan klikken Ok knop, worden alle werkbladen gesorteerd op basis van het sorteertype dat u hebt opgegeven. Zie schermafbeeldingen:

 Sorteer werkbladtabbladen op basis van tabbladkleur met VBA-code

Het is gebruikelijk dat we de werkbladtabs alfabetisch sorteren in Excel, maar heeft u ooit geprobeerd de bladtabs te sorteren op basis van de tabbladkleur? De onderstaande VBA-code kan helpen om dit probleem op te lossen, doe dit als volgt:

1. Houd ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende macro in het Module Venster.

VBA: sorteer alle bladen op basis van tabbladkleur

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 toets om deze code uit te voeren, worden de bladen met dezelfde tabkleur samen gesorteerd. Zie schermafbeeldingen:

 Sorteer werkbladtabbladen op basis van tabbladkleur met een geweldige functie

met Kutools for Excel's Sorteer bladen functie kunt u de werkbladtabbladen ook snel en gemakkelijk op kleur sorteren.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Klik op Kutools Plus > Werkblad > Sorteer bladen om het te gaan Sorteer bladen dialoogvenster, in de Sorteer bladen dialoogvenster, klik Kleur sorteren knop in het rechterdeelvenster, zie screenshot:

2. Dan klikken Ok knop, worden de bladen met dezelfde tabbladkleur samen gerangschikt zoals hieronder getoonde screenshots:

---

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

• Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
• Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
• Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
• Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
• Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
• Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
• Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
• Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
• Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...

Lees verder... Alle functies Gratis proefversie van 30 dagen... Aankoop... 

---

Office-tabblad - brengt interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

• Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
• Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
• Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!

Lees verder... Alle functies Gratis proefversie van 30 dagen... Aankoop...