Formula generica
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Sommario
Per ordinare dinamicamente i dati con numeri e testo in ordine alfabetico è possibile utilizzare una formula per generare un rango numerico in una colonna helper, quindi utilizzare INDICE e CONFRONTA per visualizzare i valori in base al rango. Nell'esempio mostrato la formula in C5 è:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
dove "dati" è l'intervallo denominato B5: B13.
Spiegazione
Questa formula genera prima un valore di rango utilizzando un'espressione basata su COUNTIF:
=COUNTIF(data,"<="&B5)
che è spiegato più dettagliatamente qui. Se i dati contengono tutti i valori di testo o tutti i valori numerici, la classificazione sarà corretta. Tuttavia, se i dati includono testo e numeri, è necessario "spostare" il rango di tutti i valori di testo per tenere conto dei valori numerici. Questo viene fatto con la seconda parte della formula qui:
+(COUNT(data)*ISTEXT(B7))
Qui, usiamo la funzione COUNT per ottenere un conteggio di valori numerici nei dati, quindi moltiplichiamo il risultato per il risultato logico di ISTEXT, che verifica se il valore è testo e restituisce TRUE o FALSE. Questo annulla efficacemente il risultato COUNT quando stiamo lavorando con un numero nella riga corrente.
Gestione dei duplicati
Se i dati contengono duplicati, la formula può essere modificata come mostrato di seguito per assegnare una classificazione sequenziale ai valori che appaiono più di una volta:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Questa versione regola la logica della funzione CONTA.SE iniziale e aggiunge un'altra CONTA.SE con un riferimento in espansione per incrementare i duplicati.
Visualizza i valori ordinati
Per recuperare e visualizzare i valori ordinati in ordine alfabetico utilizzando il valore di rango calcolato, E5 contiene la seguente formula INDICE e CONFRONTA:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
dove "data" è l'intervallo denominato B5: B13 e "rank" è l'intervallo denominato C5: C13.
Per ulteriori informazioni su come funziona questa formula, vedere l'esempio qui.
Trattare con spazi vuoti
Le celle vuote genereranno un rango pari a zero. Supponendo che tu voglia ignorare le celle vuote, questo funziona bene perché la formula INDICE e CONFRONTA sopra inizia da 1. Tuttavia, vedrai # N / A errori alla fine dei valori ordinati, uno per ogni cella vuota. Un modo semplice per gestire questo è avvolgere la formula INDICE e CONFRONTA in SE.ERRORE in questo modo:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
