Formula generica
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Sommario
Per ottenere il nome dell'ennesimo valore più grande, è possibile utilizzare INDICE e CONFRONTA con la funzione GRANDE. Nell'esempio mostrato, la formula nella cella H5 è:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
dove nome (SI5: SI16) e punteggio (RE5: SI16) sono intervalli denominati.
Spiegazione
In poche parole, questa formula utilizza la funzione GRANDE per trovare l'ennesimo valore più grande in un insieme di dati. Una volta ottenuto tale valore, lo inseriamo in una formula INDICE e CONFRONTA standard per recuperare il nome associato. In altre parole, usiamo l'ennesimo valore più grande come una "chiave" per recuperare le informazioni associate.
La funzione LARGE è un modo semplice per ottenere l'ennesimo valore più grande in un intervallo. Fornisci semplicemente un intervallo per il primo argomento (array) e un valore per n come secondo argomento (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Lavorando dall'interno verso l'esterno, il primo passo è ottenere il "primo" valore più grande nei dati con la funzione LARGE:
LARGE(score,F5) // returns 93
In questo caso, il valore in F5 è 1, quindi chiediamo il primo punteggio più grande (cioè il punteggio più alto), che è 93. Ora possiamo semplificare la formula per:
=INDEX(name,MATCH(93,score,0))
All'interno della funzione INDICE, la funzione CONFRONTA è impostata per individuare la posizione di 93 nel punteggio dell'intervallo denominato (D5: D16):
MATCH(93,score,0) // returns 3
Poiché 93 appare nella terza riga, MATCH restituisce 3 direttamente a INDICE come numero di riga, con il nome come matrice:
=INDEX(name,3) // Hannah
Infine, la funzione INDICE restituisce il nome nella terza riga, "Hannah".
Si noti che stiamo prendendo i valori per n dall'intervallo F5: F7, al fine di ottenere il 1 °, 2 ° e 3 ° punteggio più alto quando la formula viene copiata.
Recupera gruppo
La stessa formula di base funzionerà per recuperare tutte le informazioni associate. Per ottenere il gruppo per i valori più grandi, puoi semplicemente modificare l'array fornito in INDICE con il gruppo di intervalli denominato :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Con il valore 1 in F5, LARGE otterrà il punteggio più alto e la formula restituirà "A".
Nota: con Excel 365, è possibile utilizzare la funzione FILTRO per elencare dinamicamente i risultati in alto o in basso.
Con XLOOKUP
La funzione XLOOKUP può essere utilizzata anche per restituire il nome dell'ennesimo valore più grande come questo:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE restituisce il valore più grande, 93, direttamente a XLOOKUP come valore di ricerca:
=XLOOKUP(93,score,name) // Hannah
Con il punteggio dell'intervallo denominato (D5: D16) come matrice di ricerca e il nome (B5: B16) come matrice di ritorno, XLOOKUP restituisce "Hannah" come prima.
Gestione delle cravatte
Valori duplicati nei dati numerici creeranno un "pareggio". Se si verifica un pareggio nei valori classificati, ad esempio, se il primo e il secondo valore più grande sono uguali, LARGE restituirà lo stesso valore per ciascuno. Quando questo valore viene passato alla funzione MATCH, MATCH restituirà la posizione della prima corrispondenza, quindi vedrai restituito lo stesso (primo) nome.
Se c'è la possibilità di pareggi, potresti voler implementare una sorta di strategia di spareggio. Un approccio consiste nel creare una nuova colonna di valori helper che sono stati modificati per rompere i legami. Quindi utilizzare i valori della colonna helper per classificare e recuperare le informazioni. Ciò rende chiara ed esplicita la logica utilizzata per rompere i legami.
Un altro approccio consiste nel rompere i pareggi solo in base alla posizione (cioè il primo pareggio "vince"). Ecco una formula che adotta questo approccio:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne che in Excel 365.
Qui, usiamo MATCH per trovare il numero 1 e costruiamo un array di ricerca utilizzando la logica booleana che (1) confronta tutti i punteggi con il valore restituito da LARGE:
score=LARGE(score,F5)
e (2) utilizza un controllo dell'intervallo in espansione se il nome è già nell'elenco classificato:
COUNTIF(H$4:H4,name)=0
Quando un nome è già nell'elenco, viene "cancellato" dalla logica e viene trovato il valore successivo (duplicato). Notare che l'intervallo di espansione inizia dalla riga precedente, per evitare un riferimento circolare.
Questo approccio funziona in questo esempio perché non ci sono nomi duplicati nella colonna del nome. Tuttavia, se i nomi duplicati si verificano in valori classificati, l'approccio deve essere modificato. La soluzione più semplice è assicurarsi che i nomi siano univoci.
Appunti
- Per ottenere il nome dell'ennesimo valore con criteri, (ovvero limitare i risultati al gruppo A o B), sarà necessario estendere la formula per utilizzare una logica aggiuntiva.
- In Excel 365, la funzione FILTRO è un modo migliore per elencare dinamicamente i primi o gli ultimi risultati. Questo approccio gestirà automaticamente i pareggi.
