Vai al contenuto

Creare ordine alfabetico automatico Excel

Microsoft_Excel_Ordine_Alfabetico_Automatico_Recap

In questo articolo scopriamo come creare un ordine alfabetico automatico Excel. Con le versioni di Excel pre-365, avremo bisogno di una colonna di appoggio per un passaggio intermedio, mentre con le ultime versioni per fortuna il processo si velocizza e semplifica di parecchio.

Questo metodo è utile nel caso volessimo creare un sistema con una convalida dati, dato che permette di avere sempre un ordine alfabetico anche se cambiano le estrazioni di partenza del database. Se vuoi scaricare il file collegato all'articolo, puoi cliccare sul pulsante sottostante:

Prima di cominciare, un appunto. Nel caso stessi utilizzando una semplice tabella o da ordinare rapidamente, possiamo utilizzare anche i Filtri propri di Microsoft Excel. In questo articolo vediamo però un caso con una combinazione di formule.

ORDINE_ALFABETICO_AUTOMATICO_EXCEL_365

Ecco quindi come creare un ordine alfabetico, utilizzando le formule disponibili in Excel 365 o, per versioni precedenti, le combinazioni con formule quali SE.ERRORE, VAL.NUMERO, O, CONTA.SE, INDICE, CONFRONTA e RIF.RIGA.

Ordine alfabetico automatico con Excel 365

ORDINE_ALFABETICO_AUTOMATICO_EXCEL_365

Ordinare un'intera tabella alfabeticamente è piuttosto semplice con Excel 365:

=DATI.ORDINA(TBL_PLAYERS)

DATI.ORDINA prende sempre come default l'ordine crescente, quindi partirà dal valore A fino ad arrivare al valore Z. Il vantaggio di utilizzare una tabella di partenza (TBL_PLAYERS) deriva dal fatto che, se dovessi aggiungere degli ulteriori valori, questi sarebbero inclusi nella mia formula.

In genere questo è un passaggio che viene eseguito per creare un menu a tendina collegato, come puoi vedere dalla cella B19:

ORDINE_ALFABETICO_AUTOMATICO_MENU_TENDINA_EXCEL_365

=D6#

L'utilizzo dell'hash (#) indica che l'intervallo di partenza è un array dinamico - quindi questo si potrà espandere o ridurre a seconda del numero di persone inserite nell'intervallo di partenza.

Attenzione a un aspetto importante: non è possibile saltare questo passaggio intermedio inserendo direttamente nel menu a tendina la formula =DATI.ORDINA(TBL_PLAYERS) dato che Excel ci restituisce un errore. In questi casi dovremo infatti:

  • Definire la tabella di partenza
  • Mettere in ordine i valori (altrove) con DATI.ORDINA
  • Riprendere la cella di partenza dell'array con DATI.ORDINA per creare il menu a tendina

In questo caso, se volessimo prenderli da Z a A, basterebbe inserire un -1 alla fine che indica l'ordine decrescente.

La formula sarebbe quindi leggermente diversa:

=DATI.ORDINA(TBL_PLAYERS[GIOCATORI];;-1)

I due punti e virgola sono necessari perchè DATI.ORDINA permette di ordinare all'interno di un array, in questo caso il parametro non ci interessa perchè è soltanto una colonna.

Se vuoi approfondire l'argomento, puoi dare un'occhiata a questo articolo.

Ordine alfabetico automatico senza duplicati con Excel 365

ORDINE_ALFABETICO_AUTOMATICO_EXCEL_365_DUPLICATI

I duplicati possono essere un problema quando creiamo dei menu dinamici per le nostre dashboard. In questo caso possiamo utilizzare una combinazione di funzioni per estrarre i valori univoci e ordinarli:

=DATI.ORDINA(UNICI(TBL_PLAYERS_DUP))

Prima di tutto, prendiamo i valori univoci con la funzione UNICI - poi, li mettiamo in ordine con DATI.ORDINA. Non sempre sarà necessario, ma potrebbe essere utile se la nostra tabella di partenza ha dei valori che si ripetono (ad esempio nazioni o prodotti).

Ordine alfabetico automatico solo testo con Excel 365

ORDINE_ALFABETICO_AUTOMATICO_EXCEL_365_TESTO

La nostra tabella di partenza potrebbe essere pasticciata, con un mix di numeri, testo e altro. In questo caso sfruttiamo altre funzioni per agevolarci nella creazione del nostro ordine alfabetico:

=DATI.ORDINA(FILTRO(TBL_VAL;VAL.TESTO(TBL_VAL)))

FILTRO ci permette di considerare i valori della tabella quando sono effettivamente dei valori testuali: se il valore è un testo, VAL.TESTO restituisce VERO, altrimenti restituisce FALSO.

Questo escamotage potrebbe tornare utile anche in altre circostanze, per esempio per verificare la coerenza di una singola colonna dove sono stati inseriti dei dati a mano.

Ordine alfabetico automatico di una tabella con Excel 365

ORDINE_ALFABETICO_AUTOMATICO_EXCEL_365_TABELLA

Possiamo anche ordinare una intera tabella a seconda dei valori di una colonna, in questo caso la G:

=DATI.ORDINA(TBL_ZA;1;-1)

La formula è scritta così perchè i parametri sono 3:

  • TBL_ZA è la nostra tabella di partenza
  • 1 indica che ci interessa la prima colonna della tabella (quindi G)
  • Il valore -1 indica che desideriamo i valori in ordine decrescente

Allo stesso modo, se volessimo considerare solamente una colonna possiamo fare così:

ORDINE_ALFABETICO_AUTOMATICO_EXCEL_365_COLONNA

=DATI.ORDINA(TBL_ZA[G];;-1)

In questo caso stiamo già segmentando una sola colonna, in ordine decrescente.

Se non hai a disposizione le ultime funzioni di Excel, puoi continuare nella lettura e sfruttare la seconda parte della guida!

Ordine alfabetico Excel: come impostare il file

Microsoft_Excel_Ordine_Alfabetico_Automatico_Iniziale

Primo step: creiamo una colonna di controllo, in questo caso la colonna E, per determinare quali valori testuali debbano apparire per primi nell'ordine alfabetico.

La formula utilizza la funzione CONTA.SE per creare un ordine relativo all'interno dei testi, dando a ciascun valore un numero partendo da 1 fino al 10. Come notiamo, Aldridge è il primo valore dell'ordine alfabetico e quindi la formula restituisce 1, Antetokounmpo è il secondo e ha il numero 2 etc.

Formula pratica:

CONTA.SE($B$7:$B$16;"<="&B7)

Questo ci permette di avere un riferimento numerico per ognuno dei valori testuali. In questo caso, non abbiamo considerato i duplicati perchè richiedono una gestione diversa. Puoi fare riferimento a questo articolo per scoprire come ordinare dei valori duplicati.

Ordine alfabetico automatico Excel: come completare il file

Microsoft_Excel_Ordine_Alfabetico_Automatico_Nomi

Secondo step: utilizziamo la colonna di controllo per avere un riferimento e mettere in ordine alfabetico i valori. In questo caso, vogliamo che i valori in ordine alfabetico siano nella colonna E.

Prendiamo i valori precedenti che sappiamo essere in ordine dal valore 1 al valore 10. Per evitare di scrivere i singoli numeri sul foglio, procedimento spesso pratico ma poco "elegante", possiamo utilizzare la funzione RIF.RIGA. La funzione restituisce il numero della riga che stiamo considerando, quindi partendo da A1 darà come risultato 1. Trascinandola in basso, darà il risultato 2, poi il 3 e così via.

Per trovare quindi il nome del giocatore basterà creare un semplice INDICE CONFRONTA per recuperare i dati che si trovano sulla sinistra rispetto alla nostra ricerca. Inseriamo anche il SE.ERRORE per restituire un valore vuoto ("") nel caso vi sia un errore nella tabella di partenza.

Formula pratica:

SE.ERRORE(INDICE($B$7:$B$16;CONFRONTA(RIF.RIGA(A1);$C$7:$C$16;0));"")

Ordine alfabetico Excel: come evitare numeri e celle vuote

Analizziamo un altro caso, nel quale nell'elenco di partenza abbiamo anche delle celle vuote e dei numeri che non vogliamo includere nel nostro ordine alfabetico.

Microsoft_Excel_Ordine_Alfabetico_Automatico_Iniziale_Vuoti_Numeri

Primo step: purtroppo non possiamo utilizzare le formule precedenti perchè il risultato non sarebbe corretto. Benchè la base di partenza sia la stessa, dobbiamo spiegare a Excel che vogliamo restituire un valore vuoto se il valore di partenza è un numero oppure una cella vuota. In questo caso ci torna utile utilizzare le funzioni SE, VAL.NUMERO e O, mentre per il resto la formula ricalca quella precedente.

Formula pratica:

SE(O(VAL.NUMERO(G7);G7="");"";CONTA.SE($G$7:$G$16;"<="&G7))

La funzione VAL.NUMERO determina se la cella G7 è un numero: se è un numero, allora restituisce vuoto. SE non è un numero, ma è vuota (condizione inserita con la funzione O), allora restituisce vuoto. Se non rientra nei due casi precedenti, Excel procede a creare l'ordine alfabetico come quello del caso precedente.

Microsoft_Excel_Ordine_Alfabetico_Automatico_Iniziale_Numeri_Vuoti_Finale

Per il resto, la soluzione finale ricalca perfettamente quella di partenza: utilizzeremo INDICE, CONFRONTA, SE.ERRORE e RIF.RIGA per ottenere il nostro ordine alfabetico pulito da eventuali errori del file di partenza.

14 commenti su “Creare ordine alfabetico automatico Excel”

  1. Ciao Marco,
    la tua formula magica, sembra funzionare con le mie esigenze. Vorrei porti un quesito:
    nella colonna "C" ho il dato "cognome e nome"
    nella colonna "B" ho il dato "titolo"

    quindi per ogni persona corrisponde un titolo diverso:
    dott pinco pallino
    ing. cicillo cacace
    geom bianchi mario
    etc etc

    io vorrei ordinare secondo un dato criterio ovvero:
    in ordine di titolo e successivamente in ordine di cognome
    prima tutti gli ing., poi tutti i dott poi tutti i geom poi tutti gli arc e così via.

    Provato a fare con un tasto in vba e registrare la macro ordine personalizzato ma è impossibile da fare perchè può contenere soltanto 255 caratteri ed io ne ho qualcuno in più.

    Che soluzione proponi?

    1. Ciao Matteo,

      domanda interessante, ci ho pensato un po' e ti propongo una soluzione. Non è con le funzioni ma dovrebbe andare.

      Prima di tutto, sto dando per assodato che tu abbia l'elenco codificato correttamente. Quindi ad esempio sempre ING., ARCH., GEOM. e simili. Altrimenti devi uniformarli.

      STEP 1

      Colonna D: qui estrai la prima parola per ognuno dei valori della colonna B.

      Quindi mi immagino di passare da "ING. Fumagalli" a "ING.". Questo puoi farlo facendo SINISTRA(cella;TROVA(" ";cella)-1), ne parlo in questo articolo su nome e cognome (https://www.marcofilocamo.it/trovare-nome-cognome-Excel)

      STEP 2

      Colonna D: adesso devi creare un ordine personalizzato. Home -> Ordina e Filtra -> Ordinamento personalizzato.

      Clicca in Ordine -> Elenco personalizzato.

      Ora devi riportare esattamente le parole inserite in precedenza nell'ordine che vuoi. Ovvero "Ing., Arch., Geom.". Poi clicchi su OK e dovrebbe funzionare.

      Spero ti sia utile!

      Marco

  2. Ciao Marco
    mi è stato molto utile il tuo articolo per mettere in ordine alfabetico, grazie del tuo tempo dedicato.
    Vorrei chiederti come posso mette in ordine alfabetico saltando le celle che sono vuote o hanno dei numeri.
    Ti ringrazio anticipatamente.

    1. Ciao Carmelo,

      ti ringrazio per il tuo commento perchè mi hai dato un ottimo spunto per rivedere e ampliare l'articolo!

      Come potrai vedere, adesso puoi scaricare un file aggiornato che tiene in considerazione anche questa eventualità.

      Spero di esserti stato utile!

      A presto

      Marco

  3. Buongiorno, io avrei la necessita di gestire una tabella Excel in cui una delle colonne aveva come formula il =sortCEll, che su office 365 pare sparito. So che è stato sostituito da Datiordina ma usando la formula non mi restituisce i nominativi in ordine alfabetico.
    IL mio scopo è quello di dire poi ad una ulteriore formula di cercare i tre nomi e dirmi se sono duplicati indipendentemente dall'ordine in cui sono inseriti.
    per esempio nella colonna A scrivo tizio nella B caio nella C sempronio, vorrei che mi dicesse se seppur in colonne diverse ripeto il terzetto.

    GRAZIE

    1. Ciao Gianni,

      grazie per la tua domanda (e per la fiducia) ma faccio un po' di fatica a seguirti.

      Riesci a darmi qualche indicazione aggiuntiva per aiutarti?

      A presto,

      Marco

  4. Ciao Marco, ho una lista di nomi in una riga nelle altre telefono e data nascita etc. quando cerco di mettere in ordine alfabetico mi da errore in quanto vuole le celle di stessa dimensione.
    come fare?
    grazie
    Lorenzo

    1. Ciao Lorenzo,

      grazie per la domanda!

      Ho fatto un test rapido e dovrebbe andare con =SE(G7="";"";CONTA.SE($G$7:$G$16;"<="&G7)), hai fatto un tentativo? In questo caso ignori VAL.NUMERO che, per l'esempio dell'articolo, toglieva i numeri per restituire unicamente i valori testuali. Spero ti sia utile! A presto, Marco

  5. ciao Marco...utilissima questa funzione....però applicandola ad un menu a tendina, quando apro quest'ultima il cursore va a posizionarsi verso la fine dell'elenco dove ci sono spazi vuoti.
    Soluzioni?
    Grazie.

    1. Ciao Luca,

      grazie per la domanda - se hai Excel 365 potresti farlo usando altre funzioni (ad esempio DATI.ORDINA oppure con la funzione FILTRO per togliere gli spazi vuoti) che ti eliminano alla base i valori vuoti.

      In linea di massima se lo inserisci una volta e poi ti posizioni con il cursore nel menu a tendina selezionando una delle prime voci, dovresti vedere prima quelle e poi gli spazi vuoti!

      Spero di esserti stato utile.

      A presto,

      Marco

    1. Ciao Giovanni,

      grazie per la domanda - ho aggiornato l'articolo, con Excel 365 evitare le parole che si ripetono è decisamente più semplice dato che ti è sufficiente usare UNICI!

      A presto,

      Marco

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *