Formula generica
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Sommario
Per classificare gli elementi in un elenco utilizzando uno o più criteri, è possibile utilizzare la funzione CONTA.SE. Nell'esempio mostrato, la formula in E5 è:
=COUNTIFS(groups,C5,scores,">"&D5)+1
dove "gruppi" è l'intervallo denominato C5: C14 e "punteggi" è l'intervallo denominato D5: D14. Il risultato è una classifica per ogni persona nel proprio gruppo.
Nota: sebbene i dati siano ordinati per gruppo nello screenshot, la formula funzionerà correttamente con i dati non ordinati.
Spiegazione
Sebbene Excel abbia una funzione RANK, non esiste una funzione RANKIF per eseguire una classificazione condizionale. Tuttavia, puoi facilmente creare un RANK condizionale con la funzione COUNTIFS.
La funzione CONTA.SE può eseguire un conteggio condizionale utilizzando due o più criteri. I criteri vengono inseriti in coppie intervallo / criteri. In questo caso, il primo criterio limita il conteggio allo stesso gruppo, utilizzando l'intervallo denominato "gruppi" (C5: C14):
=COUNTIFS(groups,C5) // returns 5
Di per sé, questo restituirà il totale dei membri del gruppo nel gruppo "A", che è 5.
Il secondo criterio limita il conteggio solo ai punteggi maggiori del "punteggio corrente" di D5:
=COUNTIFS(groups,C5,scores,">"&D5) // returns zero
I due criteri lavorano insieme per contare le righe in cui il gruppo è A e il punteggio è più alto. Per il primo nome nell'elenco (Hannah), non ci sono punteggi più alti nel gruppo A, quindi CONTA.SE restituisce zero. Nella riga successiva (Edward), ci sono tre punteggi nel gruppo A maggiori di 79, quindi COUNTIFS restituisce 3. E così via.
Per ottenere un rango corretto, aggiungiamo semplicemente 1 al numero restituito da COUNTIFS.
Inversione dell'ordine dei ranghi
Per invertire l'ordine delle classifiche e classificarle in ordine (cioè il valore più piccolo è classificato # 1) basta usare l'operatore minore di ():
=COUNTIFS(groups,C5,scores,"<"&D5)+1
Invece di contare i punteggi maggiori di D5, questa versione conterà punteggi inferiori al valore di D5, invertendo effettivamente l'ordine di classifica.
Duplicati
Come la funzione RANK, la formula in questa pagina assegnerà ai valori duplicati lo stesso rango. Ad esempio, se a un valore specifico viene assegnato un rango 3 e ci sono due istanze del valore nei dati da classificare, entrambe le istanze riceveranno un rango 3 e il grado successivo assegnato sarà 5. Per imitare il comportamento della funzione RANK.AVG, che in tal caso assegnerebbe un rango medio di 3,5, puoi calcolare un "fattore di correzione" con una formula come questa:
=(COUNTIFS(groups,C5)+1-(COUNTIFS(group,C5,scores,">"&D5)+1)-(COUNTIFS(groups,C5,scores,"<"&D5)+1))/2
Il risultato di questa formula sopra può essere aggiunto al rango originale per ottenere un rango medio. Quando un valore non ha duplicati, il codice precedente restituisce zero e non ha alcun effetto.
