Deze handleiding laat u zien hoe u in Excel alfabetiseert met behulp van de functie Sorteren en filteren, en formules om de gegevens in volgorde te rangschikken en op te maken.
Microsoft Excel is een effectieve software voor het organiseren van belangrijke gegevens, zoals financiën, verkoopgegevens, klantinformatie, enz. Het alfabetiseren van de gegevens in werkbladen is een van de beste manieren om deze gegevens snel te ordenen, te openen en ernaar te verwijzen. Het alfabetiseren van een bereik van cellen betekent het alfabetisch sorteren van een bereik in Excel. Gegevens kunnen op beide manieren worden gesorteerd, ofwel in oplopende volgorde of in aflopende volgorde.
Stel dat u bijvoorbeeld een grote dataset met bestelinformatie van klanten heeft en dat u de bestelgeschiedenis van een bepaalde klant wilt vinden. Het doorspitten van de hele lijst wordt een hele klus. In plaats van tijd te verspillen met scrollen, kunt u uw kolommen alfabetiseren om uw gegevens snel te vinden.
Er zijn verschillende manieren waarop u gegevens in Excel kunt alfabetiseren, afhankelijk van het type gegevensset waaraan u werkt. Met Excel kunt u een enkele kolom of een enkele rij of een geselecteerd bereik of een heel werkblad sorteren, evenals opties om meerdere kolommen of rijen samen te groeperen terwijl u ze sorteert. Laten we leren hoe u gegevens in Excel-spreadsheets alfabetisch kunt rangschikken op rijen of kolommen.
Waarom gegevens alfabetiseren in Excel
Het alfabetiseren van de gegevens heeft tal van voordelen:
- Het maakt de gegevens zinvoller en gemakkelijker leesbaar.
- Het maakt het voor een gebruiker gemakkelijker om een bepaalde waarde of klantnaam op te zoeken in een Excel-gegevensblad.
- Het helpt de gebruiker om dubbele records visueel te identificeren, zodat we fouten bij het invoeren van gegevens kunnen voorkomen.
- Hiermee kunnen gebruikers de kolommen of lijsten eenvoudig groeperen, zodat gebruikers ze naast elkaar kunnen bekijken.
Alfabetisch sorteren (alfabetisch sorteren) is een gemakkelijke, gebruikelijke manier om uw informatie snel te sorteren. Het is vooral handig voor zeer grote datasets. In totaal zijn er 4 manieren om gegevens in Excel te alfabetiseren: A-Z- of Z-A-knop, Sorteerfunctie, filterfunctie en formules.
Een kolom alfabetiseren in Excel
De snelste manier om alfabetisch in Excel te sorteren, is door de ingebouwde sorteerfunctie te gebruiken.
Open eerst de spreadsheet die de dataset bevat. Selecteer de cellen of kolom die u wilt sorteren en zorg ervoor dat u geen lege cellen selecteert. Ga vervolgens naar het tabblad 'Gegevens' en klik op 'A-Z' om oplopend te sorteren of op 'Z-A' om aflopend te sorteren in de groep Sorteren en filteren.
Als er lege cellen tussen staan, gaat Excel ervan uit dat alle gegevens zijn gesorteerd en stopt boven de zwarte cel. Als u de hele kolom wilt sorteren, selecteert u gewoon een cel in de kolom en gebruikt u de bovenstaande optie.
Dezelfde opties zijn ook toegankelijk via de tool 'Sorteren en filteren' in het gedeelte Bewerken van het tabblad 'Home'.
Zodra u op de knop klikt, alfabetiseert Excel uw lijst onmiddellijk.
Hoe u rijen samen sorteert en bewaart in Excel
U kunt een kolom eenvoudig sorteren als u maar één kolom in uw werkblad heeft, maar het wordt een beetje ingewikkeld wanneer uw werkblad meerdere kolommen bevat naast de kolom die u wilt sorteren. Soms werken we ook aan spreadsheets waarin we de overeenkomstige rijen intact moeten houden na het sorteren van de kolom.
Als we bijvoorbeeld een spreadsheet hebben met de cijferlijst van studenten. Als we de kolom met namen van studenten in alfabetische volgorde sorteren, willen we ook dat de tekens die in de rijen ernaast staan, dienovereenkomstig worden verplaatst.
In dergelijke gevallen kunt u de knop 'A-Z' of 'Z-A' gebruiken om een van de kolommen in alfabetische volgorde te sorteren en de gegevens worden automatisch in andere kolommen gerangschikt, waarbij de rijen bij elkaar blijven. Laten we eens kijken hoe dat werkt met een ander voorbeeld.
Selecteer eerst de eerste kolom waarop u andere kolommen wilt sorteren. Selecteer vervolgens 'A-Z' of 'Z-A' onder het tabblad Gegevens of het tabblad Start om de gegevens in één kolom te sorteren, en Excel zal de andere kolommen automatisch dienovereenkomstig herschikken.
Zodra u op de sorteerknop klikt, verschijnt een dialoogvenster Sorteerwaarschuwing. Selecteer het keuzerondje voor 'De selectie uitbreiden' en klik op de knop 'Sorteren'.
Hierdoor blijven de rijen bij elkaar tijdens het sorteren van kolommen, zonder dat gegevens niet overeenkomen (zoals hieronder weergegeven).
Als u de optie 'Doorgaan met de huidige selectie' selecteert, sorteert Excel alleen de geselecteerde kolom.
Alfabetisch sorteren op meerdere kolommen
Als u een gegevensset alfabetisch op meer dan één kolom wilt sorteren, kunt u dat doen met de functie Sorteren van Excel.
We willen bijvoorbeeld de volgende tabel eerst alfabetisch rangschikken op Land en vervolgens op Voornaam:
Selecteer eerst de hele tabel die u wilt sorteren op alfabetische volgorde, ga vervolgens naar het tabblad 'Gegevens' en klik in de groep Sorteren en filteren op 'Sorteren'.
Of klik op het pictogram 'Sorteren en filteren' in het gedeelte Bewerken van het tabblad 'Start' en selecteer 'Aangepast sorteren' om dezelfde functie te gebruiken.
Het dialoogvenster Sorteren verschijnt en toont de eerste kolom (parameter) waarin de tabel wordt gesorteerd. Selecteer in de vervolgkeuzelijst 'Sorteren op' de primaire kolom waarop u de gegevens eerst wilt alfabetiseren, in ons geval 'Land', en kies in de vervolgkeuzelijst 'Order' ofwel 'A tot Z' of 'Z tot A' .
Klik vervolgens op de knop 'Niveau toevoegen' om het tweede sorteerniveau toe te voegen. Selecteer de tweede kolom (in ons geval Voornaam) waarop u de gegevens wilt alfabetiseren in het vak 'Sorteren op' en selecteer 'A tot Z' of 'Z tot A'. Als uw tabel kopteksten bovenaan heeft, vinkt u het selectievakje 'Mijn gegevens hebben kopteksten' aan, zodat deze de kopteksten kan overslaan tijdens het sorteren. Voeg indien nodig meer sorteerniveaus toe en klik op 'OK'.
De tabel is alfabetisch gerangschikt: eerst op land en vervolgens op voornaam, zoals hieronder weergegeven.
Hoe rijen alfabetisch te sorteren in Excel
Soms wilt u misschien rijen in Excel alfabetiseren in plaats van kolommen. Dit kan worden gedaan met behulp van de sorteerfunctie van Excel. Stel dat u bijvoorbeeld een spreadsheet heeft waarin de kolom B tot en met T op de eerste rij de plaatsnamen bevat waar het aantal winkels dat beschikbaar is onder verschillende winkeltypes en kolom A het type winkelier bevat. De cellen worden gebruikt om bij te houden hoeveel winkels in elke stad in verschillende winkelcategorieën.
Selecteer eerst alle rijen die u alfabetisch wilt rangschikken; Als u rijlabels niet wilt verplaatsen, laat u ze uit uw selectie. Navigeer nu naar het tabblad 'Gegevens' en klik op 'Sorteren' in de groep Sorteren en filteren.
Klik in het dialoogvenster Sorteren op de knop 'Opties'.
Er verschijnt nog een klein dialoogvenster, waarin u de optie 'Sorteer van links naar rechts' of 'Sorteer van boven naar beneden' kunt selecteren. Selecteer de optie 'Van links naar rechts sorteren' om in plaats daarvan op rij te alfabetiseren en klik op 'OK' om terug te gaan naar het dialoogvenster Sorteren.
Selecteer nu de rij die u wilt alfabetiseren in de vervolgkeuzelijst 'Sorteren op' (rij 1 in dit voorbeeld). Bewaar in het andere veld Celwaarden in het vak 'Sorteren op' en selecteer 'A tot Z' (oplopende volgorde) in het vak Volgorde. Klik vervolgens op 'OK' om het dialoogvenster te sluiten.
Nu is de eerste rij in onze tabel gesorteerd in alfabetische (oplopende) volgorde, en de overige rijen worden dienovereenkomstig herschikt, zoals hieronder wordt weergegeven.
Een kolom alfabetisch sorteren met behulp van een filter
Een andere snelle manier om alfabetisch in Excel te sorteren, is door de functie Filter te gebruiken. Nadat u filters op kolommen hebt toegepast, zijn de sorteeropties voor alle kolommen met slechts één muisklik toegankelijk.
Om een filter aan kolommen toe te voegen, selecteert u een of meer kolommen en klikt u op de optie 'Filter' in de groep Sorteren en filteren op het tabblad 'Gegevens'. Als u het filter op de hele tabel wilt toepassen, selecteert u een cel in de tabel en klikt u op de optie 'Filter'.
U kunt de optie Filter ook openen door op de tool 'Sorteren en filteren' in de groep Bewerken van het tabblad 'Home' te klikken en 'Filter' te selecteren.
Er verschijnt een kleine vervolgkeuzepijl in elk van de kolomkoppen. Klik op de pijl van een kolom die u alfabetisch wilt sorteren en selecteer 'Sorteer A naar Z' of 'Sorteer Z naar A'.
Hiermee wordt de gewenste kolom in de door u gekozen alfabetische volgorde gerangschikt, en als u 'Sorteren van A tot Z' kiest, verschijnt er een kleine pijl omhoog op de filterknop die de sorteervolgorde aangeeft (oplopend):
Geavanceerd sorteren in een aangepaste volgorde
Alfabetisch sorteren is niet ideaal voor alle alfabetische gegevens. Soms kunnen gegevens worden gealfabetiseerd, maar dit is niet de meest effectieve manier om ze te sorteren.
Stel je een situatie voor waarin je een lijst hebt met de namen van maanden of weekdagen, alfabetisch sorteren is in die situatie niet handig. Het is logischer om die lijst chronologisch te sorteren. Als u van A tot Z sorteert, worden de maanden in alfabetische volgorde gerangschikt, april komt eerst, dan augustus, februari, juni, enzovoort. Maar dit is niet wat je wilt. Gelukkig is het heel eenvoudig om chronologisch in Excel te rangschikken met behulp van de optie Geavanceerde aangepaste sortering.
Begin met het selecteren van de tabel die u wilt sorteren. Selecteer vervolgens 'Aangepast sorteren' onder 'Sorteren en filteren' in het gedeelte Bewerken van het tabblad Start.
Kies in het dialoogvenster Sorteren de kolom met maanden van het jaar in de kolomsectie, sorteer op 'Waarden', zoals gebruikelijk, en in de sectie Volgorde, kies 'Aangepaste lijst'.
In het dialoogvenster Aangepaste lijsten kunt u uw eigen lijst maken. Er zijn enkele standaard aangepaste lijsten met dagen van de weken, afgekorte maanden, maanden van het jaar, enzovoort. Kies de juiste sorteeroptie volgens uw behoefte (in ons geval januari, februari, ..) en klik op 'OK'. Klik vervolgens nogmaals op ‘OK’ om uw lijst chronologisch te sorteren.
U zult merken dat u de lijst met succes hebt gesorteerd volgens de maandvolgorde.
Alfabetisch sorteren in Excel met Excel-formules
Als je een fan bent van formules, dan kun je Excel-formules gebruiken om een lijst alfabetisch te maken. De twee formules die kunnen worden gebruikt voor het alfabetiseren van gegevens zijn AANTAL.ALS en VERT.ZOEKEN.
We hebben bijvoorbeeld een lijst met namen die we willen alfabetiseren met behulp van formule.
Om dit te sorteren, voegen we een tijdelijke kolom met de naam 'Sorteervolgorde' toe aan de bestaande tabel.
Gebruik in de cel (A2) naast de gegevens de volgende AANTAL.ALS-formule:
=AANTAL.ALS($B$2:$B$20,"<="&B2) De bovenstaande formule vergelijkt een tekstwaarde in cel B2 met alle andere tekstwaarden (B3:B20) en retourneert de relatieve rangorde. In cel B2 wordt bijvoorbeeld 11 geretourneerd, omdat er 11 tekstwaarden zijn die in alfabetische volgorde lager zijn dan of gelijk zijn aan de tekst 'Nancy'. Het resultaat is de sorteervolgorde van de naam van de werknemer in cel B2.
Gebruik vervolgens de vulgreep en sleep deze formule om deze in het hele bereik te vullen. Dit geeft de sorteervolgorde van elk van de namen in de lijst.
Nu moeten we de gegevens in de bovenstaande schermafbeelding rangschikken op basis van het sorteervolgordenummer, en om dit te doen gaan we een VERT.ZOEKEN-functie gebruiken.
De syntaxis:
=VERT.ZOEKEN(,A:B,2,0)Hier staat ‘sorteernummer’ voor de getallen in oplopende volgorde van 1 – 20. Voor aflopende volgorde moeten de getallen van 20 – 1 zijn.
Hier hebben we kolom D voor gesorteerde namen. Voer voor cel D2 de volgende VERT.ZOEKEN-formule in:
=VERT.ZOEKEN(1,A:B,2,0)Evenzo moet u voor de tweede en derde cel de formule gebruiken als:
=VERT.ZOEKEN(2,A:B,2,0) en =VERT.ZOEKEN(3,A:B,2,0) enzovoorts…
Na het invoeren van deze VERT.ZOEKEN-formule in elke cel naast de gegevens, wordt de lijst alfabetisch gerangschikt.
In plaats van de bovengenoemde formule (1-20) handmatig in elke cel in te voeren, kunt u ook de rijfunctie gebruiken om uw werk gemakkelijker te maken. De functie Rij () retourneert het rijnummer van de huidige cel. Dus met behulp van de rijfunctie wordt de formule:
=VERT.ZOEKEN(RIJ()-1,A:B,2,0)Deze formule geeft hetzelfde resultaat als de bovenstaande formule.
En gebruik vervolgens de vulgreep om deze formule te slepen om deze in het hele bereik te vullen.
Hoe u vermeldingen alfabetiseert op achternaam
Af en toe moeten we datasheets vaak alfabetisch sorteren op achternaam. In dergelijke gevallen, ook al beginnen de namen met de voornaam, moet u ze alfabetiseren op achternaam. U kunt dit doen met Excel-tekstformules.
Om dat te doen, moet u eerst de voor- en achternaam van de volledige namen in twee verschillende kolommen extraheren. Draai vervolgens de namen om, sorteer ze en keer ze terug naar hun oorspronkelijke vorm.
Met een volledige naam in A2 voert u de volgende formules in twee verschillende cellen (B2 en C2) in en kopieert u de formules vervolgens door de kolommen (met de vulgreep) tot de laatste cel met gegevens:
Om de voornaam te extraheren, voert u deze formule in cel C2 in:
=LINKS(A2,ZOEKEN(” “,A2)-1)
Om de achternaam te extraheren, voert u deze formule in cel D2 in:
=RECHTS(A2,LEN(A2)-ZOEKEN(" ",A2,1))
En vervolgens, in cel E2, de voor- en achternaam in omgekeerde volgorde aaneengeschakeld, gescheiden door een komma:
=D2&", "&C2
We hebben de voor- en achternaam gescheiden en omgedraaid. Nu moeten we ze alfabetiseren en terugdraaien. Zoals u hierboven kunt zien, bevatten kolommen C, D en E, wanneer we namen extraheren met behulp van de formule, formules, maar ze zijn opgemaakt om eruit te zien als waarden. We moeten de formule dus naar waarden converteren.
Om dat te doen, selecteert u eerst alle formulecellen (E1:E31) en drukt u op Ctrl + C om ze te kopiëren. Klik met de rechtermuisknop op de geselecteerde cellen, klik op het pictogram 'Waarden' onder 'Opties plakken' en druk op de toets 'Enter'.
Selecteer vervolgens een cel in de resulterende kolom en klik op de optie 'A tot Z' of 'Z tot A' op het tabblad 'Gegevens' of 'Home', afhankelijk van uw behoeften.
Er verschijnt een dialoogvenster Sorteerwaarschuwing zodra u op de knop klikt. Kies de optie 'Selectie uitbreiden' en klik op de knop 'Sorteren'.
Zoals u kunt zien, is de hele kolom gealfabetiseerd op achternaam.
Als u het nu terug wilt zetten naar de oorspronkelijke indeling 'Voornaam Achternaam', hoeft u alleen maar dezelfde formules in te voeren, maar met verschillende celverwijzingen.
Je moet de naam (E2) weer in twee delen scheiden met behulp van de onderstaande formules:
Extraheer in G2 de voornaam:
=RECHTS(E2,LEN(E2)-ZOEKEN(" ",E2))
Trek in G2 de achternaam:
=LINKS(E2,ZOEKEN(" ",E2)-2)
En voeg de twee delen samen om de originele volledige naam te krijgen:
=G2&" "&H2
Telkens wanneer u de formule in de eerste cel invoert, moet u ervoor zorgen dat u de formule door de kolom kopieert (met de vulgreep) tot de laatste cel met gegevens.
Het enige dat u nu hoeft te doen, is de formules nog een keer naar waardeconversie converteren, zoals we hierboven hebben gedaan, en u bent klaar. dit gedaan en.