Formula generica
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Sommario
Per ottenere il nome dell'ennesimo valore più grande con criteri, è possibile utilizzare INDICE e CONFRONTA, la funzione GRANDE e un filtro creato con la funzione SE. Nell'esempio mostrato, la formula nella cella G5, copiata verso il basso, è:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
dove nome (SI5: SI16), gruppo (DO5: DO16) e punteggio (RE5: RE16) sono intervalli denominati. La formula restituisce il nome associato al 1 °, 2 ° e 3 ° valore più alto nel Gruppo A.
Nota: questa è una formula di matrice che deve essere inserita con CTRL + MAIUSC + INVIO, tranne che in Excel 365.
Spiegazione
La funzione LARGE è un modo semplice per ottenere l'ennesimo valore più grande in un intervallo:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
In questo esempio, possiamo utilizzare la funzione GRANDE per ottenere un punteggio più alto, quindi utilizzare il punteggio come una "chiave" per recuperare il nome associato con INDICE e CONFRONTA. 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.
La svolta tuttavia in questo caso è che dobbiamo distinguere tra i punteggi nel gruppo A e nel gruppo B. In altre parole, dobbiamo applicare dei criteri. Lo facciamo con la funzione IF, che viene utilizzata per "filtrare" i valori prima che vengano valutati con LARGE. Come esempio generico, per ottenere il valore più grande (ovvero il primo valore) in intervallo2 dove intervallo 1 = "A", puoi utilizzare una formula come questa:
LARGE(IF(range="A",range2),1)
Nota: l'utilizzo di IF in questo modo rende questa una formula di matrice.
Lavorando dall'interno verso l'esterno, il primo passo è ottenere il "1 °" valore più grande nei dati associati al Gruppo A con la funzione LARGE:
LARGE(IF(group="A",score),F5)
In questo caso, il valore in F5 è 1, quindi chiediamo il punteggio più alto nel gruppo A. Quando la funzione IF viene valutata, verifica ogni valore nel gruppo di intervalli denominato . Il punteggio dell'intervallo denominato viene fornito per value_if_true. Questo genera un nuovo array, che viene restituito direttamente alla funzione LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Notare che gli unici punteggi che sopravvivono al filtro provengono dal Gruppo A. GRANDE quindi restituisce il punteggio rimanente più alto, 93, direttamente alla funzione CONFRONTA come valore di ricerca. Possiamo ora semplificare la formula per:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Ora possiamo vedere che la funzione MATCH è configurata utilizzando lo stesso array filtrato che abbiamo visto sopra. La funzione IF filtra nuovamente i valori indesiderati e la parte MATCH della formula si risolve in:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Poiché 93 appare nella 3a posizione, MATCH restituisce 3 direttamente alla funzione INDICE:
=INDEX(name,3) // Hannah
Infine, la funzione INDICE restituisce il nome nella terza riga, "Hannah".
Con XLOOKUP
La funzione XLOOKUP può essere utilizzata anche per risolvere questo problema, utilizzando lo stesso approccio spiegato sopra:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Come sopra, LARGE è configurato per funzionare con un array filtrato da IF e restituisce un risultato di 93 a XLOOKUP come valore di ricerca:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
L'array di ricerca viene creato anche utilizzando IF come filtro sui punteggi dal Gruppo A. Con l'array di ritorno fornito come nome (B5: B16). XLOOKUP restituisce "Hannah" come risultato finale.
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.
