Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come calcolare “large if” o “small if”, recuperando l'ennesimo valore più grande (o più piccolo) in base a criteri.
Funzioni GRANDE E PICCOLO
La funzione LARGE viene utilizzata per calcolare l'n-esimo valore più grande (k) in un array, mentre la funzione SMALL restituisce l'n-esimo valore più piccolo.
Per creare un "Se grande", utilizzeremo la funzione GRANDE insieme alla funzione SE in una formula di matrice.
GRANDE SE
Combinando GRANDE (o PICCOLO) e SE in una formula di matrice, possiamo essenzialmente creare una funzione "SE GRANDE" che funziona in modo simile a come funziona la formula SOMMA.SE incorporata. Passiamo attraverso un esempio.
Abbiamo un elenco dei voti ottenuti dagli studenti in due diverse materie:
Supponiamo che ci venga chiesto di trovare i primi tre voti ottenuti per ogni materia in questo modo:
Per fare ciò, possiamo annidare una funzione SE con il soggetto poiché i nostri criteri all'interno della funzione LARGE in questo modo:
=GRANDE(SE(=, ),)
=GRANDE(SE($C$2:$C$10=$F3,$D$2:$D$10),G$2)
Quando si utilizza Excel 2022 e versioni precedenti, è necessario inserire la formula premendo CTRL + MAIUSC + INVIO per ottenere le parentesi graffe attorno alla formula.
Come funziona la formula?
La formula funziona valutando ogni cella nel nostro intervallo di criteri come VERO o FALSO.
Trovare il valore del voto più alto (k=1) in Math:
=GRANDE(SE($C$2:$C$10=$F3,$D$2:$D$10),G$2)
=GRANDE(SE({VERO; FALSO; VERO; FALSO; VERO; FALSO; VERO; FALSO}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Successivamente, la funzione IF sostituisce ogni valore con FALSE se la sua condizione non è soddisfatta.
=GRANDE({0,81;FALSO;FALSO;0,42;FALSO;0,63;FALSO;0,58;FALSO},1)
Ora la funzione LARGE salta i valori FALSE e calcola il più grande (k=1) dei valori rimanenti (0,81 è il valore più grande tra 0,42 e 0,81).
PICCOLO SE
La stessa tecnica può essere applicata anche con la funzione SMALL invece.
=PICCOLO(SE($C$2:$C$10=$F3,$D$2:$D$10),G$2)
SE GRANDE con più criteri
Per utilizzare LARGE IF con più criteri (in modo simile a come funziona la formula SUMIFS incorporata), nidifica semplicemente più funzioni SE nella funzione LARGE in questo modo:
=GRANDE(SE(=, SE(=, )),)
=GRANDE(SE($D$2:$D$18=$H3,SE($B$2:$B$18=$G3,$E$2:$E$18)),I$2)
Un altro modo per includere più criteri è moltiplicare i criteri insieme come mostrato in questo articolo
Suggerimenti e trucchi:
- Ove possibile, fare sempre riferimento alla posizione (k) da una cella di supporto e bloccare il riferimento (F4) in quanto ciò faciliterà le formule di riempimento automatico.
- Se stai utilizzando Excel 2022 o successivo, puoi inserire la formula senza Ctrl + Maiusc + Invio.
- Per recuperare i nomi degli studenti che hanno ottenuto il massimo dei voti, abbinalo a INDEX MATCH
