Formula generica
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Sommario
Per ordinare dinamicamente i dati che contengono solo valori numerici, è possibile utilizzare una colonna helper e una formula creata con le funzioni RANK e COUNTIF. Nell'esempio mostrato, la formula in D5 è:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
dove "vendite" è l'intervallo denominato C5: C11.
Spiegazione
Nota: questa formula è l'impostazione per una formula in grado di estrarre e visualizzare i dati utilizzando un ordinamento predefinito in una colonna helper. Un esempio qui.
Il nucleo di questa formula è la funzione RANK, che viene utilizzata per generare una classifica dei valori di vendita, dove il numero più alto è classificato al primo posto:
=RANK(C5,sales)
Qui, RANK utilizza l'intervallo denominato "vendite" (C5: C11) per comodità. Per impostazione predefinita, RANK assegnerà 1 al valore più alto, 2 al secondo valore più alto e così via. Funziona perfettamente fintanto che i valori numerici sono univoci. Tuttavia, per gestire valori numerici che contengono duplicati, è necessario utilizzare la funzione CONTA.SE per interrompere i legami. Questo viene fatto aggiungendo il risultato di questo snippet al valore restituito da RANK:
COUNTIF($C$5:C5,C5)-1
Si noti che l'intervallo viene immesso come riferimento misto che si espanderà man mano che la formula viene copiata nella tabella. Come scritto, questo riferimento includerà la riga corrente, quindi sottraiamo 1 per "azzerare" la prima occorrenza. Ciò significa che l'espressione restituirà zero per ogni valore numerico fino a quando non verrà rilevato un duplicato. Nella seconda istanza, l'espressione restituirà 1, nella terza istanza restituirà 2 e così via. Ciò rompe efficacemente i legami e consente alla formula di generare un elenco sequenziale di numeri senza spazi vuoti.
Una volta che la formula è a posto, i dati possono essere ordinati dalla colonna helper. Può anche essere recuperato con INDICE utilizzando i valori nella colonna helper.
Nota: questa formula è adattata da un esempio nell'eccellente libro Control + Shift + Enter, di Mike Girvin.
