Valori massimi e minimi con LARGE e SMALL

MAX restituisce il valore massimo e MIN il valore minimo. Ma cosa succede se vuole ottenere il secondo valore più alto o il terzo più basso?

È esattamente ciò che fanno LARGE e SMALL. Consentono di estrarre dall'intervallo l'ennesimo valore più grande o più piccolo, costituendo la base dei report sui primi o sugli ultimi N valori.

LARGE(range, k) restituisce il k-esimo valore più grande. Il secondo argomento, k, indica la posizione: 1 è il valore più grande, 2 il secondo più grande e così via.

Quindi LARGE(A2:A20,1) equivale a MAX(A2:A20), mentre LARGE(A2:A20,2) restituisce il secondo valore più grande.

SMALL(range, k) funziona come LARGE, ma partendo dal basso. SMALL(A2:A20,1) equivale a MIN(A2:A20), mentre SMALL(A2:A20,3) restituisce il terzo valore più piccolo.

Usi SMALL per individuare le prestazioni peggiori, le opzioni più economiche o le date più lontane nel passato di un set di dati.

Per elencare i primi tre valori, disponga tre formule LARGE con k = 1, 2, 3. Un approccio più ordinato consiste nel fare riferimento a un numero di supporto, così da poter copiare la formula verso il basso.

Se la cella D2 contiene 1, D3 contiene 2 e D4 contiene 3, questa singola formula, copiata verso il basso, produce l'elenco completo dei primi tre valori.

Può generare automaticamente k con la funzione ROW, senza dover usare una colonna di supporto. ROW()-1 restituisce 1 nella prima riga, 2 in quella successiva e così via.

Copiata verso il basso, questa formula crea un elenco ordinato senza alcuna configurazione aggiuntiva. Adatti l'offset alla posizione iniziale dell'elenco.

Nelle versioni moderne di Excel e Google Sheets può generare i primi 5 valori con un'unica formula, usando SEQUENCE come argomento k. SEQUENCE(5) produce 1, 2, 3, 4, 5.

LARGE restituisce quindi cinque valori contemporaneamente, espandendoli automaticamente verso il basso. Una formula, un elenco completo ordinato.

Una domanda frequente in ambito aziendale è: quanto contribuiscono i miei primi 3 clienti? Racchiuda LARGE in SUM usando SEQUENCE (o una costante di matrice) per sommare i valori più alti.

La formula seguente somma i tre valori più grandi dell'intervallo, fornendo in una sola cella il contributo complessivo dei primi 3 valori.

Se k è minore di 1 o maggiore del numero di valori numerici nell'intervallo, LARGE e SMALL restituiscono l'errore #NUM!.

Ad esempio, chiedere il 30° valore più grande in un elenco con soli 19 numeri genera un errore. Nei report, prevenga questo caso racchiudendo la funzione in IFERROR.

Un valore da solo è meno utile che sapere chi lo ha ottenuto. Combini LARGE con INDEX e MATCH per recuperare il nome associato al valore più alto.

Questa combinazione individua il dato di vendita più alto, ne trova la riga e restituisce il nome corrispondente dalla colonna A, così il report visualizza "Venditrice migliore: Maria."

Come le altre funzioni statistiche, LARGE e SMALL considerano solo i numeri. Il testo e le celle vuote nell'intervallo vengono ignorati, quindi non influiscono sull'individuazione dell'ennesimo valore più grande.

Può quindi indicare senza problemi una colonna che include una riga di intestazione. Faccia però attenzione ai numeri memorizzati accidentalmente come testo, perché vengono ignorati e possono modificare il valore restituito da LARGE o SMALL.

Ha le vendite mensili nell'intervallo B2:B20. La direzione vuole conoscere i tre mesi migliori e i tre peggiori.

• I tre migliori: =LARGE($B$2:$B$20,{1,2,3})
• I tre peggiori: =SMALL($B$2:$B$20,{1,2,3})

Inserisca ciascuna formula in una cella da cui possa espandersi: otterrà subito una panoramica dei valori migliori e peggiori per il dashboard.

Verifichi la comprensione di LARGE e SMALL.

Ora sa andare oltre MAX e MIN:

• LARGE(range,k) restituisce il k-esimo valore più grande; SMALL(range,k) il k-esimo più piccolo.
• k = 1 equivale a MAX / MIN; aumenti k per ottenere i valori successivi.
• Usi SEQUENCE o una matrice come {1,2,3} per creare o sommare un elenco dei primi N valori con un'unica formula.
• Abbini INDEX-MATCH per associare un'etichetta al valore e racchiuda la formula in IFERROR per gestire i valori di k fuori intervallo.