L'ordinamento dei dati integrato in Excel è sorprendente, ma non è dinamico. Se ordini i dati e poi li aggiungi, dovrai ordinarli di nuovo.
Ordina i dati in ordine alfabetico
In questo post, ti mostrerò vari modi per ordinare i dati in ordine alfabetico usando le formule. Ciò significa che puoi aggiungere dati e li ordinerà automaticamente per te.
Quando i dati sono tutti testo senza duplicati
Supponiamo di avere un dato come mostrato di seguito:
In questo esempio, tutti i dati sono in formato testo (senza numeri, spazi o duplicati). Per ordinare questo, userò una colonna di supporto. Nella colonna accanto ai dati, utilizza la seguente formula CONTA.SE:
=CONTA.SE($A$2:$A$9;"<="&A2)
Questa formula confronta un valore di testo con tutti gli altri valori di testo e restituisce il relativo rango. Ad esempio, nella cella B2, restituisce 8, poiché sono presenti 8 valori di testo inferiori o uguali al testo "USA" (ordine alfabetico).
Ora per ordinare i valori, usa la seguente combinazione di funzioni INDICE, CONFRONTA e RIGHE:
=INDICE($A$2:$A$9,CONFRONTA(RIGHE($B$2:B2),$B$2:$B$9,0))
Questa formula estrae semplicemente i nomi in ordine alfabetico. Nella prima cella (C2), cerca il nome del paese con il numero più basso (l'Australia ne ha 1). Nella seconda cella, restituisce il Canada (che ha il numero 2) e così via…
Allergico alle colonne di aiuto??
Ecco una formula che farà lo stesso senza la colonna helper.
=INDICE($A$2:$A$9,CONFRONTA(RIGHE($A$2:A2),CONTA.SE($A$2:$A$9,"<="&$A$2:$A$9),0))
Questa è una formula di matrice, quindi usa Ctrl + Maiusc + Invio invece di Invio.
Lascio a te la decodifica.
Provalo tu stesso… Scarica il file di esempio
Questa formula funziona bene se hai testo o valori alfanumerici.
Ma fallisce miseramente se:
- Hai duplicati nei dati (prova a mettere US due volte).
- Ci sono spazi vuoti nei dati.
- Hai un mix di numeri e testo (prova a mettere 123 in una delle celle).
Quando i dati sono un mix di numeri, testo, duplicati e spazi vuoti
Ora questo è un po' complicato. Userò 4 colonne di supporto per mostrarti come funziona (e poi ti darò una formula enorme che lo farà senza le colonne di supporto). Supponiamo di avere un dato come mostrato di seguito:
Puoi vedere che ci sono valori duplicati, spazi vuoti e numeri. Quindi userò le colonne di supporto per affrontare ciascuno di questi problemi.
Colonna di supporto 1
Inserisci la seguente formula COUNTIF nella colonna Helper 1
=CONTA.SE($A$2:$A$9;"<="&A2)
Questa formula fa quanto segue:
- Restituisce 0 per gli spazi.
- In caso di duplicati restituisce lo stesso numero.
- Testo e numeri vengono elaborati parallelamente e questa formula restituisce lo stesso numero per testo e numero (ad esempio 123 e India ottengono entrambi 1).
Colonna di supporto 2
Inserisci la seguente funzione IS nella colonna Helper 2:
=--VALNUMERO(A2)
Colonna di supporto 3
Inserisci la seguente formula nella colonna Helper 3:
=--ISBLANK(A2)
Colonna di supporto 4
Inserisci la seguente formula nella colonna Helper 4
=SE(VAL.NUMERO(A2),B2,SE(VAL.VUOTO(A2),B2,B2+$C$10))+$D$10
L'idea di questa formula è di separare spazi, numeri e valori di testo.
- Se la cella è vuota, restituisce il valore nella cella B2 (che sarebbe sempre 0) e aggiunge il valore nella cella D10. In poche parole, restituirà il numero totale di celle vuote nei dati
- Se la cella è un valore numerico, restituirà il rango comparativo e aggiungerà il numero totale di spazi. Ad esempio, per 123 restituisce 2 (1 è il rango di 123 nei dati e c'è 1 cella vuota)
- Se è testo, restituisce il rango comparativo e aggiunge il numero totale di valori numerici e spazi. Ad esempio, per l'India, aggiunge il rango comparativo del testo nel testo (che è 1) e aggiunge il numero di celle vuote e il numero di valori numerici.
Risultato finale - Dati ordinati
Ora useremo queste colonne di supporto per ottenere l'elenco ordinato. Ecco la formula:
=IFERROR(INDICE($A$2:$A$9,CONFRONTA(PICCOLO($E$2:$E$9,RIGHE($F$2:F2)+$D$10),$E$2:$E$9,0)) ,"")
Questo metodo di ordinamento ora diventa infallibile. Ti ho mostrato il metodo per 8 elementi, ma puoi estenderlo a tutti gli elementi che desideri.
Provalo tu stesso… Scarica il file di esempio
Una formula per ordinare tutto (senza colonne di supporto)
Se riesci a gestire formule estreme, ecco una formula all-in-one che ordinerà i dati in ordine alfabetico (senza alcuna colonna di supporto).
Ecco la formula:
=SEERRORE(INDICE($A$2:$A$9,CONFRONTA(PICCOLO(NOT($A$2:$A$9="")*SE(VAL.NUMERO($A$2:$A$9),CONTA.SE($A$2: $A$9,"<="&$A$2:$A$9),CONTA.SE($A$2:$A$9,"<="&$A$2:$A$9)+SOMMA(--VAL.NUMERO($A $2:$A$9))),RIGHE($A$2:A2)+SOMMA(--ISBLANK($A$2:$A$9))),NOT($A$2:$A$9="")*SE (VAL.NUMERO($A$2:$A$9),CONTA.SE($A$2:$A$9,"<="&$A$2:$A$9),CONTA.SE($A$2:$A$9,"<=" &$A$2:$A$9)+SOMMA(--VALNUMERO($A$2:$A$9))),0)),"")
Inserisci questa formula in una cella e trascinala verso il basso per ottenere l'elenco ordinato. Inoltre, poiché questa è una formula di matrice, usa Ctrl + Maiusc + Invio invece di Invio.
Questa formula ha utilità nel mondo reale. Cosa ne pensi? Mi piacerebbe imparare da te. Lascia le tue impronte nella sezione commenti!
