Marcya ha un lungo elenco di numeri ordinati nella colonna A di un foglio di lavoro.

Questi numeri sono presumibilmente consecutivi, ma non sa se sia vero. Esaminare manualmente l’elenco è sia noioso che soggetto a errori, quindi Marcya si chiede se esiste un modo per evidenziare in qualche modo i “numeri persi” (quelli che non sono consecutivi con quello precedente) o per compilare un elenco di numeri che sono stati persi nella lista.

Ci sono molti modi in cui puoi capire dove mancano i numeri. Il primo è quello che uso abbastanza spesso: aggiungo una colonna helper accanto alla colonna A. Supponendo che i tuoi numeri inizino nella cella A1, lo metto nella cella B2:

=IF(A2<>A1+1,"Error","")

Copia la formula verso il basso del numero di celle necessario e vedrai facilmente la parola “Errore” accanto a qualsiasi valore non consecutivo al valore appena sopra. Se preferisci saperne di più sull’errore, potresti usare una formula più dettagliata:

=IF(A2=A1,"Duplicate",IF(A2<>A1+1,"Gap",""))

Un altro approccio consiste nell’utilizzare la formattazione condizionale sulle celle nella colonna A. Segui questi passaggi, sempre assumendo che i tuoi valori inizino nella cella A1:

  1. Seleziona l’intervallo A2 fino all’ultimo valore nella colonna A.

  2. Con la scheda Home della barra multifunzione visualizzata, fare clic sull’opzione Formattazione condizionale nel gruppo Stili. Excel visualizza una tavolozza di opzioni relative alla formattazione condizionale.

  3. Scegli Evidenzia regole celle, quindi scegli Altre regole dal sottomenu risultante. Excel visualizza la finestra di dialogo Nuova regola di formattazione.

(Vedi figura 1.)

  1. Nell’area Seleziona un tipo di regola nella parte superiore della finestra di dialogo, scegli Usa una formula per determinare quali celle formattare.

  2. Nella casella Formato valori dove questa formula è vera, inserisci questa formula: = A2 <> A1 + 1. Fare clic su Formato per visualizzare la finestra di dialogo Formato celle.

  3. Utilizzando i controlli nella finestra di dialogo, specificare un formato che si desidera utilizzare per evidenziare le celle che non sono consecutive.

  4. Fare clic su OK per chiudere la finestra di dialogo Formato celle. La formattazione specificata nel passaggio 7 dovrebbe ora apparire nell’area di anteprima per la regola.

  5. Fare clic su OK.

Infine, se vuoi compilare un elenco dei numeri mancanti in una serie consecutiva, puoi usare una formula di matrice. Metti quanto segue nella riga 1 di una colonna vuota:

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135, MIN($A$1:$A$135)+ROW($1:$135)-1)=0,  MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")

Ricorda che questa è una formula a matrice singola, quindi devi inserirla come una singola riga usando Ctrl + Maiusc + Invio. È quindi possibile copiare la formula verso il basso di un certo numero di celle, finché non restituisce altri valori. Inoltre, la formula presume che la tua serie sia compresa nell’intervallo A1: A135; in caso contrario, sarà necessario modificare la formula per riflettere l’intervallo effettivo.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (4315) si applica a Microsoft Excel 2007, 2010, 2013 e 2016.