Trouver les chiffres manquants dans une suite excel (2007)

jeronimo83

XLDnaute Nouveau
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

XLDnaute Barbatruc
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

XLDnaute Nouveau
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.
 

Pièces jointes

  • Facturation .xlsx
    8.8 KB · Affichages: 1 408

sand13

XLDnaute Occasionnel
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!
 
Dernière édition:

Misange

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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
 

Pièces jointes

  • XLD_161608.xlsm
    16 KB · Affichages: 563

job75

XLDnaute Barbatruc
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 :

Code:
=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+
 

Pièces jointes

  • Manquants(1).xls
    26.5 KB · Affichages: 375
Dernière édition:

job75

XLDnaute Barbatruc
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 :

Code:
=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+
 

Pièces jointes

  • Manquants(2).xls
    26.5 KB · Affichages: 254
Dernière édition:

job75

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

Re,

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

Code:
=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+
 

Pièces jointes

  • Manquants(3).xls
    33 KB · Affichages: 323
Dernière édition:

Discussions similaires

Réponses
15
Affichages
2 K

Statistiques des forums

Discussions
311 720
Messages
2 081 900
Membres
101 834
dernier inscrit
Jeremy06510