Marcya a une longue liste de nombres triés dans la colonne A d’une feuille de calcul.

Ces chiffres sont censés être consécutifs, mais elle ne sait pas si c’est vrai. L’examen manuel de la liste est à la fois fastidieux et sujet aux erreurs, alors Marcya se demande s’il existe un moyen de mettre en évidence les «numéros manqués» (ceux qui ne sont pas consécutifs à celui d’avant) ou de compiler une liste de numéros manqués dans la liste.

Il existe plusieurs façons de déterminer où il manque des nombres. Le premier est celui que j’utilise assez souvent: j’ajoute une colonne d’aide à côté de la colonne A. En supposant que vos numéros commencent dans la cellule A1, je mets ceci dans la cellule B2:

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

Copiez la formule autant de cellules que nécessaire et vous verrez facilement le mot « Erreur » à côté de toute valeur qui n’est pas consécutive à la valeur juste au-dessus. Si vous préférez en savoir un peu plus sur l’erreur, vous pouvez utiliser une formule plus détaillée:

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

Une autre approche consiste à utiliser une mise en forme conditionnelle sur les cellules de la colonne A. Suivez ces étapes, en supposant à nouveau que vos valeurs commencent dans la cellule A1:

  1. Sélectionnez la plage A2 jusqu’à la dernière valeur de la colonne A.

  2. Avec l’onglet Accueil du ruban affiché, cliquez sur l’option Mise en forme conditionnelle dans le groupe Styles. Excel affiche une palette d’options liées à la mise en forme conditionnelle.

  3. Choisissez Mettre en évidence les règles des cellules, puis choisissez Plus de règles dans le sous-menu résultant. Excel affiche la boîte de dialogue Nouvelle règle de mise en forme.

(Voir la figure 1.)

  1. Dans la zone Sélectionner un type de règle en haut de la boîte de dialogue, choisissez Utiliser une formule pour déterminer les cellules à mettre en forme.

  2. Dans la zone Valeurs de format où cette formule est vraie, entrez cette formule: = A2 <> A1 + 1. Cliquez sur Format pour afficher la boîte de dialogue Format des cellules.

  3. À l’aide des commandes de la boîte de dialogue, spécifiez un format que vous souhaitez utiliser pour mettre en évidence les cellules qui ne sont pas consécutives.

  4. Cliquez sur OK pour fermer la boîte de dialogue Format de cellule. La mise en forme que vous avez spécifiée à l’étape 7 doit maintenant apparaître dans la zone d’aperçu de la règle.

  5. Cliquez sur OK.

Enfin, si vous souhaitez compiler une liste des nombres manquants dans une série consécutive, vous pouvez utiliser une formule matricielle. Placez ce qui suit dans la ligne 1 d’une colonne vide:

=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)),"")

N’oubliez pas qu’il s’agit d’une formule matricielle unique, vous devez donc la saisir sur une seule ligne en utilisant Ctrl + Maj + Entrée. Vous pouvez ensuite copier la formule dans un certain nombre de cellules, jusqu’à ce qu’elle ne renvoie plus de valeurs. En outre, la formule suppose que votre série dans la plage A1: A135; si ce n’est pas le cas, vous devrez modifier la formule pour refléter la plage réelle.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (4315) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.