Guarda il video - Come ordinare per cognome in Excel
Se lavori con set di dati di nomi, ordinarli è una delle attività comuni che dovresti svolgere spesso.
È abbastanza facile ordinare i dati in ordine alfabetico in base al nome completo, dove Excel utilizza il primo carattere del nome per ordinare.
Ma cosa succede se vuoi? ordina i dati per cognome in Excel?
Anche se non è così semplice, può comunque essere fatto (molto dipende anche dal modo in cui sono strutturati i dati dei nomi).
Indipendentemente dal metodo utilizzato, in qualche modo dovrai estrarre il cognome dal nome completo e inserirlo in una colonna separata. È quindi possibile utilizzare questa colonna per ordinare i dati in base al cognome in ordine alfabetico.
In questo tutorial di Excel, ti mostrerò come ordinare una colonna con nomi in base al cognome.
Quindi iniziamo!
Estrai e ordina per cognome usando Trova e sostituisci
Il primo passo per ordinare in base al cognome è ottenere il cognome in una colonna separata.
Puoi farlo sostituendo tutto prima del cognome con uno spazio vuoto in modo che ti rimanga solo il cognome.
Supponiamo di avere un set di dati come mostrato di seguito e di voler ordinare questi dati in ordine alfabetico utilizzando il cognome.
Di seguito sono riportati i passaggi per ordinare in base al cognome:
- Seleziona il set di dati inclusa l'intestazione (in questo esempio, sarebbe A1:A10)
- Copialo nella colonna adiacente (se la colonna adiacente non è vuota, inserisci una nuova colonna e poi copia questi nomi)
- Rinominare l'intestazione della colonna copiata. In questo esempio, chiamerò "Cognome"
- Seleziona tutti i nomi copiati (non selezionare l'intestazione)
- Tieni premuto il tasto Ctrl e poi premi il tasto H. Si aprirà la finestra di dialogo Trova e sostituisci.
- Nel campo Trova cosa, inserisci * (simbolo di asterisco seguito da uno spazio)
- Lascia vuoto il campo Sostituisci con
- Fare clic su Sostituisci tutto. Questo sostituirebbe istantaneamente tutto il nome e rimarrai solo con i cognomi.
I passaggi precedenti manterrebbero il cognome e rimuoverebbero tutto ciò che lo precede. Funziona bene anche quando hai un secondo nome o un prefisso (come Mr. o Ms).
Una volta che hai i cognomi nella colonna adiacente, puoi facilmente ordinare il set di dati (compresi i nomi completi) in ordine alfabetico in base al cognome.
Di seguito sono riportati i passaggi per ordinare in base al cognome:
- Seleziona l'intero set di dati con le intestazioni (inclusi i nomi completi e i cognomi estratti). Puoi anche includere altre colonne che vuoi ordinare insieme ai nomi
- Fare clic sulla scheda Dati
- Fare clic su Ordina
- Nella finestra di dialogo Ordina, assicurati che "I miei dati hanno intestazioni" sia selezionato.
- Nell'opzione "Ordina per", seleziona il nome della colonna che ha solo il cognome
- In "Ordina per", seleziona "Valori cella"
- Nell'opzione Ordina, seleziona "dalla A alla Z"
- Fare clic su OK
I passaggi precedenti ordinano l'intero set di dati selezionato in base al cognome.
Una volta fatto, puoi eliminare la colonna che ha il cognome.
Suggerimento professionale: In qualsiasi momento, se ritieni di aver bisogno dei dati originali, devi trovare un modo per annullare l'ordinamento di questo set di dati. Per fare ciò, in una colonna adiacente (sinistra o destra), avere numeri di serie prima dell'ordinamento. Ora, se hai bisogno dei dati originali, li ottieni ordinando in base ai numeri.Estrai e alfabetizza per cognome usando la formula
Mentre il metodo mostrato sopra (usando Trova e sostituisci) è quello che preferisco per ottenere tutti i cognomi e ordinare in base ad esso, una limitazione è che i dati risultanti sono statici.
Ciò significa che se aggiungo più nomi alla mia lista, dovrò ripetere la stessa procedura per ottenere i cognomi.
Se questo è qualcosa che non vuoi, puoi usare il metodo della formula per ordinare i dati per cognome.
Supponiamo di avere il set di dati come mostrato di seguito.
Di seguito la formula che estrarrà il cognome dal nome completo:
=DESTRA(A2,LEN(A2)-TROVA(" ",A2))
La formula sopra si basa sul modello con un nome completo (che contiene solo il nome e il cognome in questo esempio). Il modello è che ci sarebbe uno spazio tra il nome e il cognome.
La funzione TROVA viene utilizzata per ottenere la posizione del carattere spazio. Questo valore viene quindi sottratto dalla lunghezza totale del nome per ottenere il numero totale di caratteri nel cognome.
Questo valore viene quindi utilizzato nella funzione RIGHT per ottenere il cognome.
Una volta che hai la colonna del cognome, puoi ordinare questi dati (questo è trattato nel primo metodo in dettaglio).
La formula sopra funzionerebbe quando hai solo nomi e cognomi.
Ma cosa succede se hai anche un secondo nome. Oppure potrebbe esserci un saluto prima del nome (come Mr o Ms.)
In tal caso, è necessario utilizzare la formula seguente:
=DESTRA(A2,LEN(A2)-TROVA("@",SOSTITUISCI(A2," ","@",LEN(A2)-LEN(SOSTITUISCI(A2,"","")))))
La formula sopra trova la posizione dell'ultimo carattere spazio e poi la usa per estrarre il cognome.
Ti consiglio di usare la seconda formula in tutti i casi, ed è più infallibile e può gestire tutti i casi (purché il cognome sia alla fine del nome).
Nota: queste due formule si basano sulla condizione che sia presente un solo carattere di spazio tra ogni elemento del nome. Nel caso ci siano spazi doppi, o spazi iniziali/finali, questa formula darà risultati errati. In tal caso, è meglio utilizzare la funzione TRIM per eliminare prima gli spazi iniziali, finali e doppi, quindi utilizzare la formula sopra.
Anche se questo può sembrare un metodo complicato, il vantaggio dell'utilizzo di una formula è che rende i risultati dinamici. Se aggiungi più nomi alla tua lista, tutto ciò che devi fare è copiare la formula e ti darà il cognome.
Utilizzo del testo in colonne
Text to Columns è di nuovo un modo semplice e facile per dividere le celle in Excel.
Puoi specificare il delimitatore (come virgola o spazio) e usarlo per dividere il contenuto della cella. Una volta che hai gli elementi divisi in colonne separate, puoi usare la colonna che ha il cognome per alfabetizzare i dati.
Supponiamo di avere un set di dati come mostrato di seguito:
Di seguito sono riportati i passaggi per utilizzare Text to Column per ordinare in base al cognome:
- Seleziona la colonna che ha il nome (esclusa l'intestazione)
- Fare clic sulla scheda Dati
- Nel gruppo "Strumenti dati", fai clic sull'opzione Testo in colonne. Si aprirà la procedura guidata Testo su colonne
- Nel passaggio 1 della "Conversione guidata testo in colonne", seleziona "Delimitato" e fai clic su Avanti
- Nel passaggio 2, seleziona "Spazio" come Delimitatore (e deseleziona qualsiasi altra cosa se selezionato) e quindi fai clic sul pulsante Avanti.
- Nel passaggio 3, seleziona la colonna del nome nell'anteprima dei dati, quindi seleziona l'opzione "Non importare colonne (salta)". Ciò garantisce che il nome non faccia parte del risultato e che tu ottenga solo il cognome.
- Anche nel passaggio 3, cambia la cella di destinazione con quella adiacente ai dati originali. Questo ti assicurerà di ottenere il cognome separatamente e che i dati sui nomi originali siano intatti.
- Fare clic su Fine
Una volta ottenuto il risultato, puoi ordinare in base al cognome.
Puoi anche utilizzare il testo in colonne per separare nomi e cognomi quando hai una virgola come separatore.
Utilizzo di Flash Fill
Un altro modo rapido e veloce per ottenere i cognomi è utilizzare la funzione Flash Fill.
Flash Fill è stato introdotto in Excel 2013 e aiuta a manipolare i dati identificando i modelli. Perché funzioni, devi mostrare Flash Fill il risultato che ti aspetti un paio di volte.
Una volta identificato il modello, farà rapidamente il resto del lavoro per te.
Supponiamo di avere il set di dati dei nomi di seguito.
Di seguito sono riportati i passaggi per utilizzare Flash Fill per ottenere il cognome e quindi ordinarlo utilizzandolo:
- Nella cella B2, inserisci il testo "Maury". Questo è il risultato che ti aspetti nella cella.
- Vai alla cella successiva e inserisci il cognome per il nome nella cella adiacente (Elliot in questo esempio).
- Seleziona entrambe le celle
- Passa il cursore sulla parte in basso a destra della selezione. Noterai che il cursore si trasforma in un'icona più.
- Fare doppio clic su di esso (o fare clic e trascinarlo verso il basso). Questo ti darà qualche risultato nelle celle (non è probabile che sia il risultato che desideri)
- Fare clic sull'icona Opzioni di riempimento automatico.
- Fare clic su Riempimento flash
Questo ti darà il risultato che sarà probabilmente il cognome in tutte le celle.
Dico probabile, poiché Flash Fill potrebbe non funzionare in alcuni casi. Poiché dipende dall'identificazione di un modello, potrebbe non essere sempre in grado di farlo. O a volte, il modello che decifra potrebbe non essere quello giusto.
In questi casi, dovresti inserire un risultato previsto in una o due celle in più e quindi eseguire i passaggi 4-7.
Una volta che hai tutti i cognomi in una colonna, puoi ordinare i dati in base a questi cognomi.
Quindi questi sono quattro modi diversi che puoi usare per ordinare i dati in base al cognome. Il metodo migliore sarebbe utilizzare la tecnica Trova e sostituisci, ma se vuoi rendere i tuoi risultati dinamici, il metodo della formula è la strada da percorrere.
Spero che tu abbia trovato utile questo tutorial.
