Guarda il video – Come ordinare per cognome In Excel
Se si lavora con i nomi set di dati, l’ordinamento è uno dei compiti più comuni che si dovrebbe fare spesso.
È abbastanza facile ordinare i dati in ordine alfabetico in base al nome completo, in cui Excel utilizza il primo carattere del nome per ordinare.
Ma cosa succede se si desidera ordinare i dati in base al cognome in Excel?,
Anche se non è così semplice, può ancora essere fatto (molto dipende anche dal modo in cui i dati dei nomi sono strutturati).
Non importa quale metodo usi, dovrai, in qualche modo, estrarre il cognome dal nome completo e metterlo 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 basati sul cognome.
Quindi iniziamo!,
Questo tutorial copre:
Estrai e ordina per cognome usando Trova e sostituisci
Il primo passo per ordinare per cognome è ottenere il cognome in una colonna separata.
Puoi farlo sostituendo tutto prima del cognome con uno spazio vuoto in modo da avere solo il cognome lasciato.
Supponiamo di avere un set di dati come mostrato di seguito e di voler ordinare questi dati in ordine alfabetico usando il cognome.,
di Seguito sono riportati i passaggi per ordinare per cognome:
- Selezionare il set di dati, inclusa l’intestazione (in questo esempio, sarebbe A1:A10)
- Copia nella colonna adiacente (se la colonna adiacente non è vuota, inserire una nuova colonna e quindi copiare questi nomi)
- Rinomina la copia dell’intestazione di colonna. In questo esempio, il nome è ‘Cognome’
- Selezionare tutti i nomi copiati (non selezionare l’intestazione)
- Tenere premuto il tasto Control e quindi premere il tasto H. Si aprirà la finestra di dialogo Trova e sostituisci.,
- Nel campo Trova, immettere ” * ” (asterisco simbolo seguito da un carattere di spazio)
- Lasciare campo Sostituisci con vuoto
- fare Clic su Sostituisci Tutto. Questo sostituirebbe istantaneamente tutto il nome e ti rimarrà solo il cognome.
I passaggi precedenti manterrebbero il cognome e rimuoverebbero tutto prima di esso. Questo funziona bene anche quando hai nomi secondari o prefissi (come Mr. o Ms).,
Una volta che hai i cognomi nella colonna adiacente, puoi facilmente ordinare il set di dati (inclusi i nomi completi) in ordine alfabetico in base al cognome.
Di seguito sono riportati i passaggi per ordinare per cognome:
- Selezionare l’intero set di dati con intestazioni (inclusi i nomi completi e i cognomi estratti)., Puoi anche includere altre colonne che vuoi ordinare insieme ai nomi
- Fai clic sulla scheda Dati
- Fai clic su Ordina
- Nella finestra di dialogo Ordina, assicurati che ‘I miei dati hanno intestazioni’ sia selezionato.,
- In “Ordina per” opzione, selezionare il nome della colonna che ha solo il cognome
- Nel ‘Ordinamento’, selezionare ‘i Valori di Cella’
- In Ordine di opzione, selezionare ‘a ‘ – ‘ Z’
- fare Clic su OK
La procedura di cui sopra sarebbe sorta l’intero set di dati selezionato in base al cognome.
Una volta fatto, è possibile eliminare la colonna che ha il cognome.,
Estrai e alfabetizza per cognome Usando la Formula
Mentre il metodo mostrato sopra (usando Find e Replace) è quello che preferisco ottenere tutti i cognomi e ordinare in base ad esso, una limitazione di esso è che i dati risultanti in statico.,
Ciò significa che se aggiungo più nomi alla mia lista, dovrò fare di nuovo lo stesso processo per ottenere i cognomi.
Se questo è qualcosa che non vuoi, puoi usare il metodo formula per ordinare i dati per cognome.
Supponiamo di avere il set di dati come mostrato di seguito.
Di seguito è riportata la formula che estrarrà il cognome dal nome completo:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
La formula precedente si basa sul modello con un nome completo (che contiene solo il nome e il cognome in questo esempio)., Il modello è che ci sarebbe un carattere di spazio tra il nome e il cognome.
La funzione FIND viene utilizzata per ottenere la posizione del carattere dello 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 GIUSTA per ottenere il cognome.
Una volta che hai la colonna cognome, puoi ordinare questi dati (questo è coperto nel primo metodo in dettaglio).
La formula sopra funzionerebbe quando hai solo nome e cognome.
Ma cosa succede se hai anche un secondo nome., O può esserci un saluto prima del nome (come Mr o Ms.)
In tal caso, è necessario utilizzare la seguente formula:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
La formula sopra trova la posizione dell’ultimo carattere di spazio e quindi 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 ci sia un solo carattere di spazio tra ogni elemento del nome., Nel caso in cui 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 precedente.
Anche se questo può sembrare un metodo complicato, il vantaggio dell’utilizzo di una formula è che rende dinamici i risultati. Se aggiungi più nomi alla tua lista, tutto ciò che devi fare è copiare la formula e ti darà il cognome.
Usare Text to Columns
Text to Columns è di nuovo un modo semplice e facile per dividere le celle in Excel.,
È possibile specificare il delimitatore (come virgola o spazio) e utilizzarlo per dividere il contenuto della cella. Dopo aver diviso gli elementi in colonne separate, è possibile utilizzare la colonna con il cognome per alfabetizzare i dati.
si Supponga di disporre di un set di dati, come mostrato di seguito:
di Seguito sono riportati i passaggi per utilizzare il Testo di Colonna per ordinare in base al cognome:
- Selezionare la colonna che ha il nome (esclusa la intestazione)
- fare Clic sulla scheda Dati
- In la ‘di Dati Strumenti di gruppo, fare clic sul Testo per Colonne opzione., Questo consentirà di aprire guidata Testo in Colonne
- Nel Passaggio 1 della ‘Conversione Guidata Testo in Colonne”, seleziona “Delimitato” e fare clic su Avanti
- Nel Passo 2, selezionare “Spazio” come Delimitatore (e deselezionare qualsiasi altra cosa, se selezionato) e quindi fare clic sul pulsante Avanti.
- Nel passaggio 3, selezionare la colonna nome nell’anteprima dei dati e quindi selezionare l’opzione ‘Non importare colonne (salta)’. Questo assicura che il nome non è una parte del risultato e si ottiene solo il cognome.,
- Anche nel passaggio 3, modificare la cella di destinazione in quella adiacente ai dati originali. Questo farà in modo di ottenere il cognome separatamente e dati nomi originali è intatto.
- Fare clic su Fine
Una volta ottenuto il risultato, è possibile ordinare per cognome.
Puoi anche inviare messaggi di testo a colonne per separare nome e cognome quando hai una virgola come separatore.,
Utilizzando Flash Fill
Un altro modo rapido e veloce per ottenere i cognomi è utilizzando la funzione di riempimento Flash.
Flash Fill è stato introdotto in Excel 2013 e aiuta a manipolare i dati identificando i modelli. Perché questo funzioni, è necessario mostrare Flash Riempire il risultato che ci si aspetta 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 sotto.,
Di seguito sono riportati i passaggi per utilizzare Flash Fill per ottenere il cognome e quindi ordinarlo:
- Nella cella B2, inserisci il testo ‘Maury’. Questo è il risultato che ci si aspetta nella cella.
- Vai alla cella successiva e inserisci il cognome per il nome nella cella adiacente (Elliot in questo esempio).
- Selezionare entrambe le celle
- Posizionare il cursore sulla parte in basso a destra della selezione. Si noterà 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)
- Fai clic sull’icona delle opzioni di riempimento automatico.
- Clicca su Flash Fill
Questo ti darà il risultato che probabilmente sarà il cognome in tutte le celle.
Dico probabilmente, poiché il riempimento flash potrebbe non funzionare in alcuni casi. Poiché dipende dall’identificazione di un modello, potrebbe non essere in grado di farlo sempre. O a volte, il modello che decifra potrebbe non essere quello giusto.,
In questi casi, è necessario immettere un risultato previsto in una o due celle 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 usare la tecnica Trova e sostituisci, ma se vuoi rendere dinamici i tuoi risultati, il metodo formula è la strada da percorrere.
Spero che tu abbia trovato utile questo tutorial.,
Si può anche come il seguente Excel tutorial:
- Come Ordinare per Colore in Excel
- Come Ordinare i Fogli di lavoro in Excel
- Come Ordinare i Dati in Excel con VBA
- Automaticamente Ordinare i Dati in Ordine Alfabetico con la Formula
- Come fare di Più a Livello di Ordinamento dei Dati in Excel