Trouver les chiffres manquants dans une suite excel (2007)

[jeronimo83]

Bonjour à toutes et à tous,

Je suis nouveau sur le forum, 1er message !

J'ai un tableau excel 2007 avec en colonne A, la liste de mes numeros de factures qui doivent être par nature une suite de chiffres,
ex : A1 : 20000
A2 : 20001
A3 : 20002
....

Mon tableau fait plusieurs milliers de lignes, comment puis je faire pour identifier d'éventuelles factures manquantes dans cette suite.

NB : je ne me sers pas de VBA ni de macro.
Je suis à la recherche d'une formule excel.

Mille mercis pour votre aide

 

[hoerwind]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour et bienvenu sur le forum,

Pour rester simple, une petite colonne intermédiaire qui fait la soustraction du numéro de facture adjacent et de celui qui le précède.
Si le résultat égal 1, on ne fait rien, autrement on affiche un numéro d'ordre par la fonction : MAX(A1:Ax)+1
Ensuite par la fonction RECHERCHEV on récupère tous les numéros de factures manquants.

Si tu ne t'en sors pas, mets un petit exemple en pièce jointe, plus facile pour te montrer comment faire.

 

[jeronimo83]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour,

D'ores et déjà merci pour ton aide, voici mon fichier simplifié qui me pose problème

Bonjour et bienvenu sur le forum,

Pour rester simple, une petite colonne intermédiaire qui fait la soustraction du numéro de facture adjacent et de celui qui le précède.
Si le résultat égal 1, on ne fait rien, autrement on affiche un numéro d'ordre par la fonction : MAX(A1:Ax)+1
Ensuite par la fonction RECHERCHEV on récupère tous les numéros de factures manquants.

Si tu ne t'en sors pas, mets un petit exemple en pièce jointe, plus facile pour te montrer comment faire.

 

[JBOBO]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour,

En C7 à tirer vers le bas

=SI(B7-B6=1;"ok";"Il manque "&B7-B6-1&" Facture(s)")

 

[jeronimo83]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour,

Merci de ton aide mais peux tu me dire après cette étape comment faire pour trouver le numero de facture manquant.

En te remerciant

 

[JBOBO]

Re : Trouver les chiffres manquants dans une suite excel (2007)

re,

tjs en C7:

=SI(B7-B6=1;"ok !";SI(B7-B6=2;"manque "&B7-B6-1&" Facture, n° "&B6+1;SI(B7-B6>2;"manque "&B7-B6-1&" Factures, n° "&B6+1&" à "&B7-1)))

 

[jeronimo83]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Super, merci pour ton aide, c'est excellent.
Ouf, tu m'enlèves une grose épine du pied.

Bonne soirée.

 

[Jazzblack]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Formidable.
Un grand merci ... c'est très utile

 

[sand13]

Re : Trouver les chiffres manquants dans une suite excel (2007)

bonjour, ce topic m'intéresse mais est-il possible d'effectuer ce genre de contrôle avec des suites de numéros qui se trouvent dans le désordre ?

je précise qu'il ne m'est pas possible de trier les données mais je pensais qu'une fonction accouplée avec des fonctions min et max pour déterminer les deux bornes sur lesquelles s'appuyer ferait l'affaire ?

PS: en faisant =SI(MAX(B:B)-MIN(B:B)-NB(B:B)+1=0;"0";"1") associé avec une mise en forme conditionnelle je peux déjà être alerté par un trou dans ma suite, reste à savoir comment détecter lequel est manquant!

 

[Misange]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour

avec tes numéros de facture en A2:A12 par exemple, tu écris en B2 et tu tires vers le bas :
=SI(NB.SI(Feuil1!$A$2:$A$12;Feuil1!$A2+1)=1;"ok";"N°" & Feuil1!$A2+1 & " manquant")

Au besoin tu fais ensuite un copier coller valeur de la colonne B et tu la tries pour regrouper tous les N° manquants ensemble.

 

[ROGER2327]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour à tous

Un essai avec une fonction personnalisée en VisualBasic.​

ROGER2327
#6280

Jeudi 12 Sable 140 (Sainte Choupe, mère de famille - fête Suprême Quarte)
22 Frimaire An CCXXI, 6,7546h - bruyère
2012-W50-3T16:12:40Z

 

[job75]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Bonjour sand13, Misange, Roger,

Heureux de vous retrouver Roger et je prends la liberté d'utiliser votre fichier.

Une solution par formule matricielle en B2 :

=PETITE.VALEUR(SI(NB.SI(A:A;LIGNE(INDIRECT(MIN(A:A)&":"&MAX(A:A))))=0;LIGNE(INDIRECT(MIN(A:A)&":"&MAX(A:A))));LIGNES(B$2:B2))

A valider par Ctrl+Maj+Entrée et tirer vers le bas jusqu'à ce que des valeurs d'erreur apparaissent.

Edit : évidemment les nombres ne doivent pas dépasser 65536 sur Excel 2003 et 1048576 sur Excel 2007/2010.

A+

 

[job75]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Re,

Ceci est meilleur : la différence entre le maximum et le minimum ne doit pas dépasser 65535 ou 1048575 :

=MIN(A:A)-1+PETITE.VALEUR(SI(NB.SI(A:A;MIN(A:A)-1+LIGNE(INDIRECT("1:"&MAX(A:A)-MIN(A:A)+1)))=0;LIGNE(INDIRECT("1:"&MAX(A:A)-MIN(A:A)+1)));LIGNES(B$2:B2))

Fichier (2).

A+

 

[job75]

Re : Trouver les chiffres manquants dans une suite excel (2007)

Re,

Le calcul est plus rapide avec les noms définis plage et matrice :

=PETITE.VALEUR(SI(NB.SI(plage;matrice)=0;matrice);LIGNES(B$2:B2))

Toujours avec validation matricielle.

Fichier (3).

Edit : le -1 et le +1 pour la matrice étaient inutiles.

A+

 

[Si...]

Re : Trouver les chiffres manquants dans une suite excel (2007)

salut

...
NB : je ne me sers pas de VBA ni de macro.
Je suis à la recherche d'une formule excel.
...

donc Si... d'autres ne sont pas contre et pour le fun, avec une macro évènementielle (clic colonne 1).