Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come elencare i numeri mancanti in un intervallo.
Elenca i numeri saltati in sequenza
Per trovare i numeri mancanti in un elenco, possiamo utilizzare la seguente formula:
| 1 | =PICCOLA(SE(CONTA.SE($B$3:$B$8,RIGA($1:$6))=0,RIGA($1:$6),""),RIGA(B1)) |
Appunti:
- Questa è una formula di matrice! Quando si utilizza Excel 2022 e versioni precedenti, è necessario immettere la formula premendo CTRL + MAIUSC + INVIO. Saprai che la formula è stata inserita correttamente quando vedrai parentesi graffe attorno alla formula.
- Questa formula funziona solo con numeri interi positivi (interi).
Come funziona la formula?
Innanzitutto, la funzione ROW viene utilizzata per generare una matrice di numeri:
| 1 | {1;2;3;4;5;6} |
In effetti, potresti anche inserire manualmente questa matrice di numeri:
| 1 | =PICCOLA(SE(CONTA.SE($B$3:$B$8,{1;2;3;4;5;6})=0,{1;2;3;4;5;6},""), RIGA(B1)) |
Successivamente, la funzione CONTA.SE conterà i valori "se" corrispondono a un valore trovato nell'array.
Per visualizzarlo, per prima cosa convertiremo l'intervallo $B$3:$B$8 nel suo array di valori {3;2;0;6;0;5}:
| 1 | =PICCOLA(SE(CONTA.SE({3;2;0;6;0;5},{1;2;3;4;5;6})=0,{1;2;3;4;5;6 },""),RIGA(B1)) |
Successivamente eseguiremo l'operazione CONTA.SE:
| 1 | =PICCOLA(SE({0;1;1;0;1;1}=0;{1;2;3;4;5;6},""),RIGA(B1)) |
Se non viene trovato un valore, viene emesso 0. La funzione IF controlla se il valore NON è trovato e, in caso affermativo, restituisce il valore dall'array, altrimenti restituisce uno spazio vuoto:
| 1 | =PICCOLO({1;"";"";4;"";""},RIGA(B1)) |
Infine, la funzione PICCOLO restituisce il valore mancante più piccolo nella cella selezionata.
| 1 | =PICCOLO({1;"";"";4;"";""},{1}) |
| 1 | ={1} |
Consiglio:
Per evitare il #NUM! errore, avvolgi la tua formula con la funzione SEERRORE:
| 1 | =IFERROR(SMALL(SE(COUNTIF(B3:B8,ROW(1:6))=0,ROW(1:6),""),ROW(B1)),"") |
Elenca i numeri saltati in sequenza in Fogli Google
Tutti gli esempi spiegati sopra funzionano allo stesso modo nei fogli di Google come in Excel.
