Quan teniu grans quantitats de dades, pot ser aclaparador si no s'ordenen correctament al vostre llibre de treball. Apreneu diferents mètodes per ordenar les dades a Excel per ser més productius i per facilitar la gestió dels vostres fulls de càlcul.
Les instruccions d'aquest article s'apliquen a Excel 2019, 2016, 2013, 2010; Excel per a Microsoft 365, Excel Online i Excel per a Mac.
Seleccioneu les dades per ordenar
Abans que les dades es puguin ordenar, Excel ha de conèixer l'interval exacte que s'ha d'ordenar. Excel seleccionarà àrees de dades relacionades sempre que les dades compleixin aquestes condicions:
- No hi ha files ni columnes en blanc a una àrea de dades relacionades.
- Les files i columnes en blanc es troben entre àrees de dades relacionades.
Excel determina si l'àrea de dades té noms de camp i exclou la fila dels registres que s'han d'ordenar. Permetre que Excel seleccioni l'interval que s'ha d'ordenar pot ser arriscat, sobretot amb grans quantitats de dades difícils de comprovar.
Per assegurar-vos que es seleccionen les dades correctes, ress alteu l'interval abans de començar l'ordenació. Si el mateix rang s'ordenarà repetidament, el millor és donar-li un nom.
Clau d'ordenació i ordre d'ordenació a Excel
L'ordenació requereix l'ús d'una clau d'ordenació i un ordre d'ordenació. La clau d'ordenació són les dades de la columna o columnes que voleu ordenar i s'identifiquen per l'encapçalament de la columna o el nom del camp. A la imatge següent, les claus d'ordenació possibles són l'identificador de l'estudiant, el nom, l'edat, el programa i el mes iniciat.
Ordena les dades ràpidament
Per fer una ordenació ràpida, seleccioneu una sola cel·la a la columna que conté la clau d'ordenació. A continuació, trieu com voleu ordenar les dades. Així és com:
- Seleccioneu una cel·la a la columna que conté la clau d'ordenació.
- Selecciona Inici.
- Seleccioneu Ordenar i filtrar per obrir el menú desplegable d'opcions d'ordenació.
- Trieu com voleu ordenar les dades. Seleccioneu l'ordre ascendent o descendent.
Quan s'utilitza Ordenar i filtrar, les opcions d'ordenació de la llista desplegable canvien en funció del tipus de dades de l'interval seleccionat. Per a les dades de text, les opcions són Ordenar de la A a la Z i Ordenar de la Z a la A. Per a les dades numèriques, les opcions són Ordenar del més petit al més gran i Ordenar del més gran al més petit.
Ordenar diverses columnes de dades a Excel
A més de realitzar una ordenació ràpida basada en una sola columna de dades, la funció d'ordenació personalitzada d'Excel us permet ordenar en diverses columnes definint diverses claus d'ordenació. En les ordenacions de diverses columnes, les claus d'ordenació s'identifiquen seleccionant els encapçalaments de les columnes al quadre de diàleg Ordena.
Com amb una ordenació ràpida, les claus d'ordenació es defineixen identificant els encapçalaments de les columnes o els noms dels camps, a la taula que conté la clau d'ordenació.
Exemple d'ordenar en diverses columnes
A l'exemple següent, les dades de l'interval A2 a E12 s'ordenen en dues columnes de dades. Les dades s'ordenen primer per nom i després per edat.
Per ordenar diverses columnes de dades:
- Ress alteu l'interval de cel·les que voleu ordenar. En aquest exemple, es seleccionen les cel·les A2 a E12.
- Selecciona Inici.
- Seleccioneu Ordenar i filtrar per obrir la llista desplegable.
- Seleccioneu Ordenació personalitzada per obrir el quadre de diàleg Ordena.
- Coloqui una marca al costat de Les meves dades tenen capçaleres.
- Sota l'encapçalament de la columna, seleccioneu la Ordena per fletxa avall i trieu Nom a la llista desplegable per ordenar primer les dades per la columna Nom.
- A l'encapçalament Sort On, deixeu la configuració com a Valors de cel·la. L'ordenació es basa en les dades reals de la taula.
- Sota l'encapçalament de la comanda, seleccioneu la fletxa cap avall i trieu Z a A per ordenar les dades del nom en ordre descendent.
- Seleccioneu Afegeix un nivell per afegir una segona opció d'ordenació.
- Sota l'encapçalament de la columna, seleccioneu A continuació, amb la fletxa avall i trieu Edat per ordenar els registres amb noms duplicats per la columna Edat.
- Sota l'encapçalament de la comanda, trieu del més gran al més petit de la llista desplegable per ordenar les dades d'edat en ordre descendent.
- Seleccioneu D'acord per tancar el quadre de diàleg i ordenar les dades.
Com a resultat de definir una segona clau d'ordenació, que es mostra a l'exemple següent, els dos registres amb valors idèntics per al camp Nom s'ordenen en ordre descendent mitjançant el camp Edat. Això resulta en el registre de l'estudiant Wilson J., 21 anys, abans del rècord de Wilson P., 19 anys.
La primera fila: encapçalaments de columnes o dades
L'interval de dades seleccionat per ordenar a l'exemple anterior incloïa els encapçalaments de columna a sobre de la primera fila de dades. Aquesta fila conté dades que són diferents de les dades de les files posteriors. Excel va determinar que la primera fila conté els encapçalaments de les columnes i va ajustar les opcions disponibles al quadre de diàleg Ordena per incloure-les.
Excel utilitza el format per determinar si una fila conté encapçalaments de columnes. A l'exemple anterior, els encapçalaments de les columnes són un tipus de lletra diferent de les dades de la resta de files.
Si la primera fila no conté encapçalaments, Excel utilitza la lletra de la columna (com ara la columna D o la columna E) com a opcions a l'opció Columna del quadre de diàleg Ordena.
Excel utilitza aquesta diferència per determinar si la primera fila és una fila d'encapçalament. Si Excel comet un error, el quadre de diàleg Ordena conté una casella de selecció Les meves dades tenen capçaleres que anul·la aquesta selecció automàtica.
Ordenar dades per data o hora a Excel
A més d'ordenar les dades de text alfabèticament o els números de més gran a més petit, les opcions d'ordenació d'Excel inclouen l'ordenació dels valors de la data. Les ordres de classificació disponibles per a les dates inclouen:
- Ordre ascendent: del més antic al més recent.
- Ordre descendent: del més recent al més antic.
Quadre de diàleg Ordenació ràpida i ordenació
Les dates i les hores que tenen el format de dades numèriques, com ara la data prestada a l'exemple anterior, utilitzeu el mètode d'ordenació ràpida per ordenar en una sola columna. Per a ordenacions que incloguin diverses columnes de dates o hores, utilitzeu el quadre de diàleg Ordena de la mateixa manera que per ordenar diverses columnes de dades de nombre o de text.
Ordenar per data Exemple
Per fer una ordenació ràpida per data en ordre ascendent, del més antic al més recent:
- Ress alteu l'interval de cel·les que voleu ordenar. Per seguir l'exemple anterior, ress alteu les cel·les G2 a K7.
- Selecciona Inici.
- Seleccioneu Ordenar i filtrar per obrir la llista desplegable.
- Seleccioneu Ordenació personalitzada per obrir el quadre de diàleg Ordena.
- Sota l'encapçalament de la columna, seleccioneu la Ordena per fletxa avall i trieu Prestat per ordenar primer les dades per la data del préstec.
- A l'encapçalament Sort On, trieu Valors de cel·la. L'ordenació es basa en les dades reals de la taula.
- A l'encapçalament Ordre d'ordenació, trieu De més antic a més recent de la llista desplegable.
- Seleccioneu D'acord al quadre de diàleg per tancar-lo i ordenar les dades.
Si els resultats de l'ordenació per data no surten com s'esperava, les dades de la columna que conté la clau d'ordenació poden contenir dates o hores emmagatzemades com a dades de text en lloc de com a números (les dates i les hores només són dades numèriques amb format).).
Dades mixtes i classificació ràpida
Quan s'utilitza el mètode d'ordenació ràpida, si els registres que contenen dades de text i números es barregen, Excel ordena les dades de nombre i de text per separat col·locant els registres amb dades de text a la part inferior de la llista ordenada.
Excel també pot incloure els encapçalaments de les columnes als resultats de l'ordenació, interpretant-los com una fila més de dades de text en lloc dels noms de camp de la taula de dades.
Avís d'ordenació possible
Si s'utilitza el quadre de diàleg Ordena, fins i tot per ordenar en una columna, és possible que Excel mostri un missatge que us adverteix que s'han trobat dades emmagatzemades com a text i us ofereix l'opció de:
- Ordena com a nombre qualsevol cosa que sembli un número.
- Ordena els números i els números emmagatzemats com a text per separat.
Si trieu la primera opció, Excel intentarà col·locar les dades de text a la ubicació correcta dels resultats de l'ordenació. Trieu la segona opció i Excel col·locarà els registres que contenen dades de text a la part inferior dels resultats de l'ordenació, tal com fa amb les ordenacions ràpides.
Ordenar dades per dies de la setmana o per mesos a Excel
També podeu ordenar les dades per dies de la setmana o per mesos de l'any mitjançant la mateixa llista personalitzada integrada que utilitza Excel per afegir dies o mesos a un full de treball mitjançant el mànec d'emplenament. Aquestes llistes permeten ordenar per dies o mesos cronològicament i no per ordre alfabètic.
Com amb altres opcions d'ordenació, els valors d'ordenació per llista personalitzada es poden mostrar en ordre ascendent (de diumenge a dissabte o de gener a desembre) o descendent (de dissabte a diumenge o de desembre a gener).
A la imatge de d alt, es van seguir els passos següents per ordenar la mostra de dades de l'interval A2 a E12 per mesos de l'any:
- Ress alteu l'interval de cel·les que voleu ordenar.
- Selecciona Inici.
- Seleccioneu Ordenar i filtrar per obrir la llista desplegable.
- Seleccioneu Ordenació personalitzada per obrir el quadre de diàleg Ordena.
- Sota l'encapçalament de la columna, trieu Inicia del mes de la llista desplegable per ordenar les dades per mesos de l'any.
- A l'encapçalament Sort On, seleccioneu Valors de cel·la. L'ordenació es basa en les dades reals de la taula.
- A l'encapçalament de la comanda, seleccioneu la fletxa avall al costat de l'opció predeterminada de la A a la Z per obrir el menú desplegable.
- Tria Llista personalitzada per obrir el quadre de diàleg Llistes personalitzades.
- A la finestra de l'esquerra del quadre de diàleg, seleccioneu gener, febrer, març i abril.
- Seleccioneu D'acord per confirmar la selecció i tornar al quadre de diàleg Ordenar.
- La llista escollida (gener, febrer, març, abril) es mostra sota l'encapçalament de la comanda.
- Seleccioneu D'acord per tancar el quadre de diàleg i ordenar les dades per mesos de l'any.
Per defecte, les llistes personalitzades només es mostren en ordre ascendent al quadre de diàleg Llistes personalitzades. Per ordenar les dades en ordre descendent mitjançant una llista personalitzada després d'haver seleccionat la llista desitjada de manera que es mostri sota l'encapçalament Ordre del quadre de diàleg Ordena:
- Seleccioneu la fletxa avall al costat de la llista que es mostra, com ara gener, febrer, març, abril per obrir el menú desplegable.
- Al menú, seleccioneu l'opció de llista personalitzada que es mostra en ordre descendent, com ara desembre, novembre, octubre i setembre.
- Feu clic a D'acord per ordenar les dades en ordre descendent mitjançant la llista personalitzada.
Ordena per files per reordenar les columnes a Excel
Com es mostra amb les opcions d'ordenació anteriors, les dades normalment s'ordenen mitjançant encapçalaments de columnes o noms de camp. El resultat és la reordenació de files senceres o registres de dades. Una opció d'ordenació menys coneguda i, per tant, menys utilitzada a Excel és ordenar per fila, la qual cosa té l'efecte de reordenar l'ordre de les columnes d'esquerra a dreta en un full de treball.
Un dels motius per ordenar per fila és fer coincidir l'ordre de les columnes entre diferents taules de dades. Amb les columnes en el mateix ordre d'esquerra a dreta, és més fàcil comparar registres o copiar i moure dades entre les taules.
Personalitza l'ordre de columnes
No obstant això, molt poques vegades és fer que les columnes en l'ordre correcte sigui una tasca senzilla a causa de les limitacions de les opcions d'ordre ascendent i descendent dels valors. Normalment, cal utilitzar un ordre d'ordenació personalitzat i Excel inclou opcions per ordenar per cel·la o color de lletra o per icones de format condicional.
La manera més senzilla d'indicar a Excel l'ordre de les columnes és afegir una fila a sobre o a sota de la taula de dades que contingui números que indiquen l'ordre de les columnes d'esquerra a dreta. L'ordenació per files es converteix llavors en una simple qüestió d'ordenar les columnes de més petita a més gran per la fila que conté els números.
Un cop feta l'ordenació, la fila de números afegida es pot suprimir fàcilment.
Ordenar per files Exemple
A la mostra de dades utilitzada per a aquesta sèrie a les opcions d'ordenació d'Excel, la columna d'identificació de l'estudiant sempre ha estat la primera a l'esquerra, seguida del nom i després de l'edat.
En aquest cas, com es mostra a la imatge de d alt, s'han afegit números a les columnes per preparar el full de treball per reordenar les columnes de manera que la columna Programa estigui primer a l'esquerra seguida de Mes d'inici, Nom, Edat, i identificador d'estudiant.
A continuació s'explica com canviar l'ordre de les columnes:
- Insereix una fila en blanc a sobre de la fila que conté els noms dels camps.
- En aquesta nova fila, introduïu els números següents d'esquerra a dreta començant a la columna A: 5, 3, 4, 1, 2.
- Ress alteu l'interval que voleu ordenar. En aquest exemple, ress alteu A2 a E13.
- Selecciona Inici.
- Seleccioneu Ordenar i filtrar per obrir la llista desplegable.
- Seleccioneu Ordenació personalitzada per obrir el quadre de diàleg Ordena.
- Seleccioneu Opcions per obrir el quadre de diàleg Opcions d'ordenació.
- A la secció Orientació, seleccioneu Ordena d'esquerra a dreta per ordenar l'ordre de les columnes d'esquerra a dreta al full de treball.
- Seleccioneu D'acord per tancar el quadre de diàleg Opcions d'ordenació.
- Amb el canvi d'Orientació, l'encapçalament de la columna del quadre de diàleg Ordena canvia a Fila.
- Seleccioneu Ordena per fletxa avall i trieu Fila 2. Aquesta és la fila que conté els números personalitzats.
- A l'encapçalament Sort On, trieu Valors de cel·la.
- Sota l'encapçalament de la comanda, trieu del més petit al més gran de la llista desplegable per ordenar els números de la fila 2 en ordre ascendent.
- Seleccioneu D'acord per tancar el quadre de diàleg i ordenar les columnes d'esquerra a dreta segons els números de la fila 2.
- L'ordre de les columnes comença amb Programa seguit de Mes d'inici, Nom, Edat i ID de l'estudiant.
Utilitza les opcions d'ordenació personalitzada d'Excel per reordenar les columnes
Tot i que les ordenacions personalitzades estan disponibles al quadre de diàleg Ordena a Excel, aquestes opcions no són fàcils d'utilitzar quan es tracta de reordenar columnes en un full de treball. Les opcions per crear un ordre d'ordenació personalitzat disponibles al quadre de diàleg Ordenar són ordenar les dades per color de cel·la, color de lletra i icona.
A menys que cada columna ja tingui aplicat un format únic, com ara diferents tipus de lletra o colors de cel·la, aquest format s'ha d'afegir a cel·les individuals de la mateixa fila perquè cada columna es torni a ordenar.
Per exemple, per utilitzar el color de la lletra per reordenar les columnes:
- Seleccioneu cada nom de camp i canvieu-ne el color de la lletra. Per exemple, canvieu Programa a vermell, Mes Inici a verd, Nom a blau, Edat a taronja i ID d'estudiant a morat.
- Al quadre de diàleg Ordena, seleccioneu Ordena per i trieu Fila 2.
- A l'encapçalament Ordenar, trieu Color de la font.
- Sota l'encapçalament de la comanda, configureu manualment l'ordre dels colors dels noms dels camps perquè coincideixi amb l'ordre de columnes desitjat.
- Després d'ordenar, restableix el color de la lletra per a cada nom de camp.
