Téléchargez l'exemple de classeur
Ce didacticiel montrera comment répertorier les nombres manquants dans une plage.
Liste des numéros ignorés dans la séquence
Pour trouver les numéros manquants dans une liste, nous pouvons utiliser la formule suivante :
| 1 | =PETIT(SI(COUNTIF($B$3:$B$8,LIGNE($1:$6))=0,LIGNE($1:$6),""),LIGNE(B1)) |
Remarques:
- C'est une formule matricielle ! Lorsque vous utilisez Excel 2022 et versions antérieures, vous devez entrer la formule en appuyant sur CTRL + MAJ + ENTRÉE. Vous saurez que la formule a été saisie correctement lorsque vous voyez des accolades autour de la formule.
- Cette formule ne fonctionne qu'avec des nombres entiers positifs (entiers).
Comment fonctionne la formule ?
Tout d'abord, la fonction ROW est utilisée pour générer un tableau de nombres :
| 1 | {1;2;3;4;5;6} |
En fait, vous pouvez même saisir manuellement ce tableau de nombres :
| 1 | =PETIT(SI(COUNTIF($B$3:$B$8,{1;2;3;4;5;6})=0,{1;2;3;4;5;6},""), RANG (B1)) |
Ensuite, la fonction COUNTIF comptera les valeurs « si » elles correspondent à une valeur trouvée dans le tableau.
Pour visualiser cela, nous allons d'abord convertir la plage $B$3:$B$8 en son tableau de valeurs {3;2;0;6;0;5} :
| 1 | =PETIT(SI(COUNTIF({3;2;0;6;0;5},{1;2;3;4;5;6})=0,{1;2;3;4;5;6 },""),LIGNE(B1)) |
Ensuite, nous allons effectuer l'opération COUNTIF :
| 1 | =PETIT(SI({0;1;1;0;1;1}=0,{1;2;3;4;5;6},""),LIGNE(B1)) |
Si une valeur n'est pas trouvée, 0 est affiché. La fonction SI vérifie si la valeur n'est PAS trouvée et si c'est le cas, renvoie la valeur du tableau, sinon elle renvoie un blanc :
| 1 | =PETIT({1;"";"";4;"";""},LIGNE(B1)) |
Enfin, la fonction SMALL renvoie la plus petite valeur manquante dans la cellule sélectionnée.
| 1 | =PETIT({1;"";"";4;"";""},{1}) |
| 1 | ={1} |
Conseil:
Pour éviter le #NUM ! erreur, enveloppez votre formule avec la fonction SIERREUR :
| 1 | =SIERREUR(PETIT(SI(COUNTIF(B3:B8,LIGNE(1:6))=0,LIGNE(1:6),""),LIGNE(B1)),"") |
Liste des numéros ignorés dans l'ordre dans Google Sheets
Tous les exemples expliqués ci-dessus fonctionnent de la même manière dans les feuilles Google que dans Excel.
