Se lavori con un set di dati di nomi, l'ordinamento è una delle attività comuni che devi eseguire frequentemente.
È facile ordinare i dati in ordine alfabetico per nome completo, in cui Excel utilizza il primo carattere del nome per ordinare.
Tuttavia, se vuoiOrdina i dati per cognome in ExcelsaliresequenzaCome fare?
Anche se non è così semplice, può comunque essere fatto (dipende anche molto da come sono strutturati i dati del nome).
Qualunque sia il metodo che usi, devi in qualche modo estrarre il cognome dal nome completo e inserirlo in una colonna separata.È quindi possibile utilizzare questa colonna per ordinare i dati in ordine alfabetico in base al cognome.
In questo tutorial di Excel, ti mostrerò come ordinare una colonna con nomi in base al cognome.
Quindi iniziamo!
Contenuti
Estrai e ordina per cognome usando Trova e sostituisci
Il primo passaggio per l'ordinamento per cognome è ottenere il cognome in una colonna separata.
Puoi farlo sostituendo tutto prima del cognome con spazi vuoti, così ti rimane solo il cognome.
Supponiamo di avere un set di dati come quello qui sotto e di voler ordinare i dati in ordine alfabetico per cognome.
Ecco i passaggi per ordinare per cognome:
- Seleziona il dataset che contiene il titolo (in questo caso sarà A1:A10)
- copialo nella colonna adiacente (se la colonna adiacente non è vuota, inserisci una nuova colonna, quindi copia i nomi)
- Rinominare le intestazioni delle colonne copiate.In questo esempio, lo chiamerò "Cognome"
- Seleziona tutti i nomi copiati (non selezionare i titoli)
- Tenere premuto il tasto Ctrl, quindi premere il tasto H.Si aprirà la finestra di dialogo Trova e sostituisci.
- Nel campo Trova cosa, inserisci * (un asterisco seguito da uno spazio)
- Lascia vuoto il campo Sostituisci con
- Fare clic su Sostituisci tutto.Questo sostituirà tutti i nomi in una volta e rimarrai solo con i cognomi.
I passaggi precedenti manterranno il cognome ed elimineranno tutto ciò che lo precede.Funziona anche se hai un secondo nome o un prefisso come Mr. o Ms.
Una volta che hai i cognomi nelle colonne adiacenti, puoi facilmente ordinare il set di dati (compresi i nomi completi) in ordine alfabetico per cognome.
Ecco i passaggi per ordinare per cognome:
- Seleziona l'intero set di dati con i titoli (compresi i nomi completi e i cognomi estratti).Puoi anche includere altre colonne da ordinare con il nome
- Fare clic sulla scheda Dati
- Fare clic per ordinare
- Nella finestra di dialogo Ordina, assicurati che i miei dati hanno intestazioni sia selezionata.
- Nell'opzione "Ordina per", seleziona il nome della colonna con solo il cognome
- In Ordina per, seleziona Valore cella
- Nell'opzione "Ordine", seleziona "dalla A alla Z"
- Fare clic su OK
I passaggi precedenti ordineranno l'intero set di dati selezionato in base al cognome.
Una volta fatto, puoi rilasciare la colonna con il cognome.
Suggerimenti per professionisti: Ogni volta che pensi di dover tornare ai dati originali, devi avere un modo per annullare l'ordinamento di quel set di dati.Per questo, nelle colonne adiacenti (sinistra o destra), ci sono numeri di sequenza prima dell'ordinamento.Ora, se hai bisogno di recuperare i dati originali, puoi ottenerli ordinandoli in base ai numeri.
Estrai e ordina in ordine alfabetico per cognome usando la formula
Mentre il metodo mostrato sopra (usando trova e sostituisci) è quello che preferisco ottenere tutti i cognomi e ordinare in base ad esso, una delle sue limitazioni è che i dati risultanti sono statici.
Ciò significa che se aggiungo più nomi all'elenco, dovrò ripetere lo stesso processo per ottenere il cognome.
Se questo non è ciò che desideri, puoi utilizzare un metodo di formula per ordinare i dati in base al cognome.
Supponiamo di avere il set di dati mostrato di seguito.
Ecco la formula per estrarre il cognome dal nome completo:
= DESTRA (A2, LEN (A2) -TROVA ("", A2))
La formula precedente si basa su uno schema con nomi completi (in questo esempio solo nomi e cognomi).Lo schema è che ci sarà 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.
Quindi usa quel valore nella funzione DESTRA per ottenere il cognome.
Una volta che hai la colonna del cognome, puoi ordinare questi dati (questo è dettagliato nel primo metodo).
La formula sopra funzionerà quando hai solo nome e cognome.
Ma cosa succede se hai anche un secondo nome.o può avere un saluto prima del nome (es. Mr. o Ms.)
In questo caso, è necessario utilizzare la seguente formula:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
La formula precedente trova la posizione dell'ultimo carattere spazio e quindi la utilizza per estrarre il cognome.
Ti suggerisco di utilizzare la seconda formula in tutti i casi, è più semplice e gestisce 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 spazio tra ogni elemento del nome.Questa formula darà risultati errati se sono presenti doppi spazi o spazi iniziali/finali.In questo caso, è meglio utilizzare la funzione TRIM per rimuovere prima gli spazi iniziali, finali e doppi, quindi utilizzare la formula precedente.
Anche se questo può sembrare un approccio complicato, il vantaggio dell'utilizzo di una formula è che rende i risultati dinamici.Se aggiungi più nomi all'elenco, tutto ciò che devi fare è copiare la formula e ti darà il cognome.
Usa il testo in colonne
Text to Columns è un altro modo semplice per dividere le celle in Excel.
Puoi specificare un delimitatore (come una virgola o uno spazio) e usarlo per dividere il contenuto di una cella.Dopo aver suddiviso gli elementi in colonne separate, è possibile utilizzare la colonna con i cognomi per alfabetizzare i dati.
Supponiamo di avere un set di dati simile a questo:
Di seguito sono riportati i passaggi per ordinare in base al cognome utilizzando il testo in colonna:
- Seleziona colonne con nomi (escluse le intestazioni)
- Fare clic sulla scheda Dati
- Nel gruppo Strumenti dati, fai clic sull'opzione Testo in colonne.Si aprirà la procedura guidata Testo in colonne
- Nel passaggio 1 della procedura guidata Converti testo in colonne, selezionare Delimitato e fare clic su Avanti
- Nel passaggio 2, seleziona "Spazio" come delimitatore (deseleziona qualsiasi altra cosa se selezionata) e fai clic sul pulsante Avanti.
- Nel passaggio 3, seleziona la colonna del nome nell'anteprima dei dati, quindi seleziona l'opzione "Non importare la colonna (salta)".Ciò garantisce che il nome non faccia parte del risultato, ma ottieni solo il cognome.
- Anche nel passaggio 3, modificare la cella di destinazione nella cella adiacente ai dati originali.Ciò assicurerà di ottenere solo il cognome e che i dati del nome originale siano completi.
- Fare clic su Fine
Una volta che hai i risultati, puoi ordinare per cognome.
Puoi anche utilizzare il testo in colonna per separare il nome e il cognome quando hai delle virgole come delimitatori.
Riempi di glitter
Un altro modo rapido per ottenere il cognome è utilizzare la funzione di riempimento flash.
Introdotto in Excel 2013, Flash Fill aiuta a manipolare i dati riconoscendo i modelli.Per fare ciò, devi visualizzare Flash Fill più volte per il risultato che ti aspetti.
Una volta riconosciuto il modello, fa rapidamente il resto per te.
Supponiamo di avere il seguente set di dati di nomi.
Di seguito sono riportati i passaggi per ottenere il cognome utilizzando Flash Fill e utilizzarlo per ordinare:
- Nella cella B2, inserisci il testo "Maury".Questo è ciò che ti aspetteresti in una cella.
- Vai alla cella successiva e inserisci il cognome del nome nella cella adiacente (in questo caso Elliot).
- seleziona due celle
- Passa il cursore sulla parte inferiore destra della selezione.Noterai che il cursore cambia in un'icona più.
- Fare doppio clic su di esso (o fare clic e trascinare verso il basso).Questo ti darà qualche risultato nella cella (improbabile quello che desideri)
- Fare clic sull'icona Opzioni di riempimento automatico.
- Fare clic su Riempimento rapido
Questo ti darà un risultato che potrebbe essere il cognome in tutte le celle.
Dico forse perché Flash Fill potrebbe non funzionare in alcuni casi.Poiché dipende dalla modalità di riconoscimento, potrebbe non farlo sempre.O a volte, il modello che decifra potrebbe non essere il modello corretto.
In questo caso, è necessario immettere il risultato previsto in una o più celle e quindi eseguire i passaggi 4-7.
Dopo aver inserito tutti i cognomi nella colonna, puoi ordinare i dati in base a quei cognomi.
Pertanto, puoi ordinare i dati per cognome in quattro modi diversi.Il modo migliore è utilizzare le tecniche di ricerca e sostituzione, ma se vuoi rendere dinamici i tuoi risultati, i metodi delle formule sono la strada da percorrere.
Spero che tu abbia trovato utile questo tutorial.
