Aquesta guia us mostrarà com escriure alfabèticament a Excel mitjançant la funció Ordenar i filtrar, i fórmules per organitzar i donar format a les dades en ordre.
Microsoft Excel és un programari eficaç per organitzar dades importants, com ara finances, dades de vendes, informació dels clients, etc. L'alfabetització de les dades en fulls de treball és una de les millors maneres d'organitzar, accedir i fer referència a aquestes dades ràpidament. Ordenar alfabèticament un rang de cel·les significa ordenar un rang alfabèticament a Excel. Les dades es poden ordenar de dues maneres, ja sigui en ordre ascendent o en ordre descendent.
Per exemple, suposem que teniu un gran conjunt de dades d'informació de comandes del client i voleu trobar l'historial de comandes d'un client concret. Repassar tota la llista es convertirà en tota una tasca. En lloc de perdre el temps desplaçant-se, podeu ordenar alfabèticament les columnes per trobar les vostres dades ràpidament.
Hi ha diverses maneres que podeu utilitzar per classificar les dades alfabèticament a Excel, depenent del tipus de conjunt de dades en què esteu treballant. Excel us permet ordenar una sola columna o una sola fila o un rang seleccionat o un full de treball sencer, així com opcions per agrupar diverses columnes o files mentre les ordeneu. Aprenem a classificar les dades alfabèticament als fulls de càlcul d'Excel per files o columnes.
Per què alfabetitzar les dades a Excel
L'alfabetització de les dades tindrà nombrosos avantatges:
- Fa que les dades siguin més sensibles i en facilita la lectura.
- Fa que sigui més fàcil per a un usuari cercar un valor determinat o un nom de client en un full de dades d'Excel.
- Ajuda a l'usuari a identificar visualment els registres duplicats per evitar errors d'entrada de dades.
- Permet als usuaris agrupar fàcilment les columnes o llistes perquè els usuaris les puguin mirar una al costat de l'altra.
L'ordre alfabètic (ordenar alfabèticament) és una manera senzilla i habitual d'ordenar ràpidament la informació. És especialment útil per a conjunts de dades molt grans. En total, hi ha 4 maneres d'ordenar les dades alfabèticament a Excel: botó A-Z o Z-A, funció d'ordenació, funció de filtre i fórmules.
Alfabetització d'una columna a Excel
La manera més ràpida d'ordenar alfabèticament a Excel és mitjançant la funció d'ordenació integrada.
Primer, obriu el full de càlcul que conté el conjunt de dades. Seleccioneu les cel·les o la columna que voleu ordenar i assegureu-vos de no seleccionar cap cel·la en blanc. A continuació, aneu a la pestanya "Dades" i feu clic a "A-Z" per ordenar ascendent o "Z-A" per ordenar descendent al grup Ordenar i filtrar.
Si hi ha cel·les en blanc entremig, Excel assumeix que totes les dades s'han ordenat i s'atura a sobre de la cel·la negra. Si voleu ordenar tota la columna, seleccioneu qualsevol cel·la de la columna i utilitzeu l'opció anterior.
També es pot accedir a les mateixes opcions a l'eina "Ordenar i filtrar" a la secció Edició de la pestanya "Inici".
Un cop feu clic al botó, excel·leu la vostra llista alfabèticament immediatament.
Com ordenar i mantenir les files juntes a Excel
Podeu ordenar una columna fàcilment si només teniu una columna al vostre full de treball, però es complica una mica quan el vostre full de treball conté diverses columnes al costat de la columna que voleu ordenar. A més, de vegades, treballem en fulls de càlcul on hem de mantenir intactes les files corresponents després d'ordenar la columna.
Per exemple, si tenim un full de càlcul de la llista de notes dels estudiants. Quan ordenem la columna dels noms dels estudiants per ordre alfabètic, també volem que les notes introduïdes a les files al costat també es moguin en conseqüència.
En aquests casos, podeu utilitzar el botó "A-Z" o "Z-A" per ordenar una de les columnes en ordre alfabètic i organitzarà automàticament les dades en altres columnes, mantenint les files juntes. Vegem com funciona amb un altre exemple.
Primer, seleccioneu la primera columna segons la qual voleu ordenar altres columnes. A continuació, seleccioneu "A-Z" o "Z-A" a la pestanya Dades o a la pestanya Inici per ordenar les dades en una columna i Excel reorganitzarà automàticament les altres columnes en conseqüència.
Un cop feu clic al botó d'ordenació, apareixerà una finestra de diàleg d'avís d'ordenació. Seleccioneu el botó d'opció "Amplia la selecció" i feu clic al botó "Ordenar".
Això mantindrà les files juntes mentre s'ordenen les columnes, sense que no coincideixin cap dada (com es mostra a continuació).
Si seleccioneu l'opció "Continua amb la selecció actual", Excel ordenarà només la columna seleccionada.
Com ordenar alfabèticament per diverses columnes
Si voleu ordenar alfabèticament un conjunt de dades per més d'una columna, podeu fer-ho amb la funció Ordenar d'Excel.
Per exemple, volem ordenar la taula següent alfabèticament primer per país i després pel nom:
Primer, seleccioneu tota la taula que voleu ordenar per ordre alfabètic, després aneu a la pestanya "Dades" i al grup Ordenar i filtrar, feu clic a "Ordenar".
O feu clic a la icona "Ordenar i filtrar" a la secció d'edició de la pestanya "Inici" i seleccioneu "Ordenació personalitzada" per accedir a la mateixa funció.
Apareixerà el quadre de diàleg Ordenar i mostrarà la primera columna (paràmetre) en què s'ordenarà la taula. Al quadre desplegable "Ordenar per", seleccioneu la columna principal amb la qual voleu ordenar les dades alfabèticament primer, "País" en el nostre cas, i al menú desplegable "Ordenar" trieu "A a Z" o "Z a A". .
A continuació, feu clic al botó "Afegeix nivell" per afegir el segon nivell d'ordenació. Seleccioneu la segona columna (en el nostre cas Nom) amb la qual voleu ordenar les dades alfabèticament al quadre "Ordenar per" i seleccioneu "A a Z" o "Z a A". Si la vostra taula té capçaleres a la part superior, marqueu la casella de selecció "Les meves dades tenen capçaleres" perquè pugui ometre les capçaleres mentre les ordena. Afegiu més nivells d'ordenació si cal i feu clic a "D'acord".
La taula està ordenada alfabèticament: primer per país i després per nom, tal com es mostra a continuació.
Com ordenar les files alfabèticament a Excel
De vegades és possible que vulgueu ordenar alfabèticament les files a Excel, en lloc de les columnes. Això es pot fer mitjançant la funció Ordenar d'Excel. Per exemple, suposem que teniu un full de càlcul on la columna de la primera fila de la B a la T conté els noms de les ciutats on el nombre de botigues disponibles amb diferents tipus de comerç minorista i la columna A conté el tipus de comerç minorista. Les cel·les s'utilitzen per fer un seguiment de quantes botigues a cada ciutat en diferents categories de minoristes.
Primer, seleccioneu totes les files que vulgueu ordenar alfabèticament; Si no voleu moure les etiquetes de fila, deixeu-les fora de la vostra selecció. Ara, aneu a la pestanya "Dades" i feu clic a "Ordenar" al grup Ordenar i filtrar.
Al quadre de diàleg Ordena, feu clic al botó "Opcions".
Apareixerà un altre petit quadre de diàleg que us donarà l'opció de seleccionar "Ordenar d'esquerra a dreta" o "Ordenar de dalt a baix". Seleccioneu l'opció "Ordena d'esquerra a dreta" per ordenar alfabèticament per fila i feu clic a "D'acord" per tornar al quadre de diàleg Ordena.
Ara, seleccioneu la fila que voleu ordenar alfabèticament a la llista desplegable "Ordenar per" (la fila 1 en aquest exemple). A l'altre camp, manteniu els valors de la cel·la al quadre "Ordenar per" i seleccioneu "A a Z" (ordre ascendent) al quadre Ordre. A continuació, feu clic a "D'acord" per tancar el quadre de diàleg.
Ara, la primera fila de la nostra taula s'ordena en ordre alfabètic (creixent) i les files restants es reorganitzen en conseqüència, tal com es mostra a continuació.
Com ordenar alfabèticament una columna mitjançant un filtre
Una altra manera ràpida d'ordenar alfabèticament a Excel és utilitzar la funció Filtre. Un cop apliqueu filtres a les columnes, es pot accedir a les opcions d'ordenació de totes les columnes amb només un clic del ratolí.
Per afegir un filtre a les columnes, seleccioneu una o més columnes i feu clic a l'opció "Filtrar" al grup Ordenar i filtrar a la pestanya "Dades". Si voleu aplicar el filtre a tota la taula, seleccioneu qualsevol cel·la de la taula i feu clic a l'opció "Filtre".
També podeu accedir a l'opció Filtre fent clic a l'eina "Ordenar i filtrar" al grup d'edició de la pestanya "Inici" i seleccionant "Filtrar".
Apareixerà una petita fletxa desplegable a cadascuna de les capçaleres de columna. Feu clic a la fletxa de qualsevol columna que vulgueu ordenar alfabèticament i seleccioneu "Ordena de la A a la Z" o "Ordena de la Z a la A".
Això organitzarà la columna que vulgueu en l'ordre alfabètic que hàgiu triat i, si trieu "Ordenar de la A a la Z", apareixerà una petita fletxa cap amunt al botó de filtre que indica l'ordre d'ordenació (ascendent):
Ordenació avançada en una comanda personalitzada
Ordenar alfabèticament no és ideal per a totes les dades alfabètiques. De vegades, les dades es poden ordenar alfabèticament, però no és la manera més eficaç d'ordenar-les.
Imaginem una situació en què podeu tenir una llista que contingui els noms de mesos o dies laborables, ordenar alfabèticament no és útil en aquesta situació. Té més sentit ordenar aquesta llista cronològicament. Si ordeneu de la A a la Z, ordenarà els mesos per ordre alfabètic, primer arribarà abril, després agost, febrer, juny, etc. Però això no és el que vols. Afortunadament, és molt fàcil organitzar cronològicament a Excel mitjançant l'opció d'ordenació personalitzada avançada.
Comenceu seleccionant la taula que voleu ordenar. A continuació, seleccioneu "Ordenació personalitzada" a "Ordenar i filtrar" a la secció Edició de la pestanya Inici.
Al quadre de diàleg Ordena, trieu la columna que conté mesos de l'any a la secció de columnes, ordeneu per "Valors", com és habitual, i a la secció Ordre, trieu "Llista personalitzada".
Al diàleg Llistes personalitzades, podeu crear una llista pròpia. Hi ha algunes llistes personalitzades predeterminades que inclouen dies de la setmana, mesos abreujats, mesos de l'any, etc. Trieu l'opció d'ordenació adequada segons les vostres necessitats (en el nostre cas gener, febrer, ..) i feu clic a "D'acord". A continuació, feu clic a "D'acord" de nou per ordenar la vostra llista cronològicament.
Notareu que heu ordenat correctament la llista segons l'ordre del mes.
Com ordenar alfabèticament a Excel mitjançant fórmules d'Excel
Si sou un fan de les fórmules, podeu utilitzar les fórmules d'Excel per ordenar una llista alfabèticament. Les dues fórmules que es poden utilitzar per ordenar les dades alfabèticament són COUNTIF i VLOOKUP.
Per exemple, tenim una llista de noms que volem ordenar alfabèticament mitjançant una fórmula.
Per ordenar-ho, afegirem una columna temporal anomenada "Ordre d'ordenació" a la taula existent.
A la cel·la (A2) al costat de les dades, utilitzeu la següent fórmula COUNTIF:
=COMPTARSI($B$2:$B$20,"<="&B2) La fórmula anterior compara un valor de text de la cel·la B2 amb tots els altres valors de text (B3:B20) i retorna el seu rang relatiu. Per exemple, a la cel·la B2, retorna 11, ja que hi ha 11 valors de text que són inferiors o iguals al text "Nancy" per ordre alfabètic. El resultat és l'ordre d'ordenació del nom de l'empleat a la cel·la B2.
A continuació, utilitzeu la nansa d'emplenament arrossegueu aquesta fórmula per omplir-la en tot el rang. Això dóna l'ordre d'ordenació de cadascun dels noms de la llista.
Ara, hem d'organitzar les dades que es mostren a la captura de pantalla anterior en funció del número d'ordre d'ordenació, i per fer-ho utilitzarem una funció VLOOKUP.
La sintaxi:
=CERCA V(;A:B;2;0)Aquí "número d'ordenació" representa els números en ordre ascendent de l'1 al 20. Per a l'ordre descendent, els números han de ser del 20 a l'1.
Aquí tenim la columna D per als noms ordenats. Per a la cel·la D2, introduïu la següent fórmula BUSCAR V:
=CERCA V(1;A:B;2;0)De la mateixa manera, per a la segona i la tercera cel·la, heu d'utilitzar la fórmula com:
=CERCA V(2;A:B;2;0) i =CERCA V(3;A:B;2;0) etcètera…
Després d'introduir aquesta fórmula VLOOKUP a cada cel·la al costat de les dades, la llista s'organitza alfabèticament.
En lloc d'introduir manualment la fórmula esmentada (1-20) a cada cel·la, també podeu utilitzar la funció de fila per facilitar la vostra feina. La funció Fila () retorna el número de fila de la cel·la actual. Així, amb l'ajuda de la funció de fila, la fórmula serà:
=CERCAV(FILERA()-1,A:B,2,0)Aquesta fórmula us donarà el mateix resultat que la fórmula anterior.
A continuació, utilitzeu el mànec d'emplenament per arrossegar aquesta fórmula per omplir-la a tot el rang.
Com ordenar alfabèticament les entrades pel cognom
De tant en tant, sovint hem d'ordenar alfabèticament fulls de dades amb els cognoms. En aquests casos, tot i que els noms comencen amb els noms de pila, els heu d'ordenar alfabèticament pel cognom. Podeu fer-ho amb fórmules de text d'Excel.
Per fer-ho, primer, haureu d'extreure el nom i els cognoms dels noms complets en dues columnes diferents. A continuació, invertiu els noms, ordeneu-los i, a continuació, torneu-los a la seva forma original.
Amb un nom complet a A2, introduïu les fórmules següents en dues cel·les diferents (B2 i C2) i després copieu les fórmules per les columnes (utilitzant el controlador d'emplenament) fins a l'última cel·la amb dades:
Per extreure el primer nom, introduïu aquesta fórmula a la cel·la C2:
=ESQUERRA(A2,CERCAR(” “,A2)-1)
Per extreure el cognom, introduïu aquesta fórmula a la cel·la D2:
=DRETA(A2,LEN(A2)-CERCAR(" ",A2,1))
I després, a la cel·la E2, es van concatenar els noms i cognoms en ordre invers separats per una coma:
=D2&", "&C2
Hem separat noms i cognoms i els hem invertit. Ara els hem d'ordenar alfabèticament i invertir-los. Com podeu veure més amunt, quan extreim noms mitjançant la fórmula, les columnes C, D i E en realitat contenen fórmules, però tenen un format per semblar valors. Per tant, hem de convertir la fórmula en valors.
Per fer-ho, primer seleccioneu totes les cel·les de la fórmula (E1:E31) i premeu Ctrl + C per copiar-los. Feu clic amb el botó dret a les cel·les seleccionades, feu clic a la icona "Valors" a "Opcions d'enganxar" i premeu la tecla "Enter".
A continuació, seleccioneu qualsevol cel·la de la columna resultant i feu clic a l'opció "A a Z" o "Z a A" a la pestanya "Dades" o "Inici" segons les vostres necessitats.
Un quadre de diàleg d'avís d'ordenació apareixerà un cop feu clic al botó. Trieu l'opció "Ampliar la selecció" i feu clic al botó "Ordenar".
Com podeu veure, tota la columna està alfabetitzada pel cognom.
Ara, si voleu revertir-lo al "Format de Cognoms" original, només heu d'introduir les mateixes fórmules però amb referències de cel·les diferents.
Heu de tornar a separar el nom (E2) en dues parts mitjançant les fórmules següents:
A G2, extreu el primer nom:
=DRETA(E2,LEN(E2)-CERCAR(" ",E2))
A G2, traieu el cognom:
=ESQUERRA(E2,CERCAR(" ",E2)-2)
I uneix les dues parts per obtenir el nom complet original:
=G2&" "&H2
Cada vegada que introduïu la fórmula a la primera cel·la, assegureu-vos de copiar la fórmula a la columna (utilitzant el controlador d'emplenament) fins a l'última cel·la amb dades.
Ara tot el que heu de fer és convertir les fórmules a la conversió de valors una vegada més, com hem fet anteriorment, i ja heu acabat. això fet i.